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"
],
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();