Why hibernate with nativeSQL returns different result than SQL

user_14405988 0
Viewed 136

I am trying to get results from two tables/entities with native SQL using outer apply. In SQL Server I am getting the right answer, however, using the same query, I am getting a different result in JPA. In SQL Server, I am getting only the last ID from the second table (as it should), whereas in JPA I am getting all the IDs from the second table. How to get in JPA only the last ID from the second table?

    public List<Client> getClients()
{
    List results = entityManager.createNativeQuery("SELECT top 17 c.ID, c.CustomerNumber, c.Name, c.Surname, c.Area, c.City, c.Address, c.PhoneNumber, c.CustomerTypeID, c.Enabled, c.DateCreated, p.Credit, p.Debit\n" +
                    "FROM tblCustomer c OUTER APPLY\n"+
                    "     (SELECT TOP 1 p.*\n" +
                    "      FROM tblPayments p\n" +
                    "      WHERE c.CustomerNumber = p.CustomerNumber\n" +
                    "      ORDER BY p.id DESC\n" +
                    "     ) p;",
                    Client.class)
            .getResultList();
    return results;
}

SQL Server Result

  {
    "id": 1023,
    "customerNumber": "000001",
    "name": "Pajazit",
    "surname": "Neziri",
    "area": "1",
    "city": "Çegran",
    "address": "Bake",
    "phoneNumber": "",
    "customerType": 2,
    "enabled": false,
    "dateCreated": "2016-10-25",
    "payments": [
      {
        "id": 16238,
        "customerNumber": "000001",
        "invoiceNumber": "9000001112018",
        "monthOfPayment": "11-2018",
        "dateOfPayment": "2018-11-18",
        "credit": 1200.0,
        "debit": 0.0,
        "paymentPeriod": "M",
        "operator": 0,
        "printed": "T",
        "paied": "T",
        "isInvoice": "T"
      },
      {
        "id": 20459,
        "customerNumber": "000001",
        "invoiceNumber": "900000112019",
        "monthOfPayment": "1-2019",
        "dateOfPayment": "2019-01-09",
        "credit": 0.0,
        "debit": 600.0,
        "paymentPeriod": "M",
        "operator": 0,
        "printed": "T",
        "paied": "T",
        "isInvoice": "T"
      },
      {
        "id": 20538,
        "customerNumber": "000001",
        "invoiceNumber": "2000001112018",
        "monthOfPayment": "11-2018",
        "dateOfPayment": "2019-01-09",
        "credit": 0.0,
        "debit": 600.0,
        "paymentPeriod": "M",
        "operator": 1,
        "printed": "T",
        "paied": "T",
        "isInvoice": "T"
      },
      {
        "id": 20557,
        "customerNumber": "000001",
        "invoiceNumber": "2000001122018",
        "monthOfPayment": "12-2018",
        "dateOfPayment": "2019-01-09",
        "credit": 0.0,
        "debit": 600.0,
        "paymentPeriod": "M",
        "operator": 1,
        "printed": "T",
        "paied": "T",
        "isInvoice": "T"
      },
      {
        "id": 20576,
        "customerNumber": "000001",
        "invoiceNumber": "2000001012019",
        "monthOfPayment": "01-2019",
        "dateOfPayment": "2019-01-09",
        "credit": 0.0,
        "debit": 600.0,
        "paymentPeriod": "M",
        "operator": 1,
        "printed": "T",
        "paied": "T",
        "isInvoice": "T"
      },....
spring-boot hibernate sql
2 Answers

You could use a JPQL query that looks very similar to your query.

select t from tblPayments t order by t.id desc

if you want to add conditions based on the search for the last record also input in the above query.

After you establish your Query object you could then call

query.getSingleResult() or call query.setMaxResults(1)
user_32483530

For some reason, using EntityManager, the response was never the same as the one from SQL Server. Then I tried with JPQL, again the answer was not the same as the one from SQL Server. Doing some more research, led me to find another way, using JDBC RowMapper and using this finally, I achieved the same result as SQL Server as per below:

    @GetMapping("/testi2")
public List<CustomerResponse> getCCP2() {
    String sql = "SELECT top 1 c.ID, c.CustomerNumber, c.Name, c.Surname, c.Area, c.City, c.Address, c.PhoneNumber, c.CustomerTypeID, c.Enabled, c.DateCreated, p.Id, p.Debit, p.Credit\n" +
            "FROM TblCustomer c OUTER APPLY\n" +
            "     (SELECT TOP 1 p.*\n" +
            "      FROM TblPayments p\n" +
            "      WHERE c.CustomerNumber = p.CustomerNumber\n" +
            "      ORDER BY p.id DESC \n" +
            "     ) p;";
    List<CustomerResponse> result = jdbcTemplate.query(
            sql,
            new BeanPropertyRowMapper(CustomerResponse.class));

    return result;
}

}

However, thank you @Sathiyaraj and @Faeemazaz Bhanej.

user_144059880
Related