Hibernate with nativeSQL returns different result than SQL. How to achieve the same result with SQL?-CodePudding

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"
      },....

CodePudding user response：

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)

CodePudding user response：

Change method type List<Client> to Client in your getClients() method because you wanna get single data and use LIMIT 1 after DESC in native query. if you want to get more than one data use getResultList() but in your case want single data then use getSingleResult()

public 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 LIMIT 1\n"  
                    "     ) p;",
                    Client.class)
            .getSingleResult();
    return results;
}