How to get data from raw query in spring boot?

Question

I have a query:

Query q = em.createNativeQuery("select DISTINCT id, rating, random() as ordering FROM table\n" +
                " WHERE id not in (1,2) ORDER BY ordering LIMIT 10");
List data = q.getResultList();

Every element of this list is array like object:

I want to retrieve that "8" and "16" and compose a comma separated string (to use it in my query in "not in" section in future):

for (Object x : data) {
    System.out.println(Arrays.asList(x).get(0));
}

But it produces strings:

[Ljava.lang.Object;@ee93cd3
[Ljava.lang.Object;@62f3c3e1

I don't know how to get that IDs ("8" and "16")

Map it to a POJO using `ResultTransformer` – XtremeBaumer Sep 23 '19 at 07:11 — XtremeBaumer, Sep 23 '19 at 07:11

MyTwoCents · Answer 1 · 2019-09-23T08:31:16.747

0

in this line

List<Object[]> data = q.getResultList();

data is list of Object of form

[ [1,233, 0.000333], [1,233, 0.000333] ]

for (Object[] x : data) {
    // x is [1,233, 0.000333]
    System.out.println(x[0]);
}

edited Sep 23 '19 at 08:31

answered Sep 23 '19 at 07:24

MyTwoCents

7,284
3
24
52

thx, but this code gives me a compilation error, can't cast to BigInteger, I found a correct answer List – socm_ Sep 23 '19 at 07:48

Ganesan · Accepted Answer · 2019-09-23T08:05:37.507

0

1.I think this is what you are looking for... Convert JPA query.getResultList() to MY Objects.

or

List<Object[]> rows = q.getResultList(); for (Object[] row : rows) { System.out.println(row[0]); }

edited Sep 23 '19 at 08:05

answered Sep 23 '19 at 07:41

Ganesan

16
5

thx, your link is very helpful, but your code gives me compilation error the correct answer is : List – socm_ Sep 23 '19 at 07:45
You should add the answer code to this answer as well, not just links – TreantBG Sep 23 '19 at 07:48
@Ganesan kind of, but your answer is more short and mb helpful for others who came from google ) – socm_ Sep 23 '19 at 08:18

score 0 · Answer 3 · answered Sep 23 '19 at 09:35

If I understood it correctly, you are looking for comma separated string of ID's. If so, then follow these steps might help you to solve the issue.

Create a constructor in table which has only one parameter ID. (If you want you can add more parameters as well but make sure the value which you want it must be in constructor as well as in query.)
Write sql query and execute it.
It returns result and gather it in List which contains the object of the table.
Get the string

dataList.stream().map(obj -> obj.getId()).collect(Collectors.joining(", "))

This will give you the comma separated string.

How to get data from raw query in spring boot?

3 Answers3