reactjsjsonjpanative-sql

How to generate metadata with native SQL // how to retrieve the generated data without column names?


I am trying to retrieve the generated data via Reactjs. However, the data that is generated with a native SQL query in spring boot is without metadata/column names on the JSON file; and I cannot use JPA since my SQL query is with OUTER APPLY. Can someone please show me a solution for generating data with column names or retrieving the data with Reactjs from a JSON file without column names?

Below is my native SQL query and the result of it:

    @GetMapping
public List<Client> getClients()
{
    List<Client> results = entityManager.createNativeQuery("SELECT top 17 c.CustomerNumber, c.Name, c.Surname, c.Area, c.City, c.Address, c.Enabled, c.CustomerTypeID, c.DateCreated, 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;")
            .getResultList();
    return results;
}`

And the result of this is:

// http://localhost:8080/api/v1/client

[
  [
    "000001",
    "Pajazit",
    "Neziri",
    "1",
    "Çegran",
    "Bake",
    "T",
    2,
    "2016-10-25T00:49:31.000+00:00"
  ],

Solution

  • You have to tell Hibernate what the result should be:

    List<Client> results = entityManager.createNativeQuery("SELECT top 17 c.CustomerNumber, c.Name, c.Surname, c.Area, c.City, c.Address, c.Enabled, c.CustomerTypeID, c.DateCreated, 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;", 
                        Client.class) // THIS WAS MISSING
                .getResultList();