SQL Query to retrieve the value of addScalar value

Question

Query like this :

query1 = with codeList (bId, code) as  ( select id, listagg(code, ',') ........)

select {A.*}, {B.*}, CL.code as codeListName from 

tableA A left join tableB B on A.id = B.id  
left join  codeList CL on A.id = CL.bId

Result of the query would be like List of following values:

a.id, a.name, a.code, b.id, b.desc, b.addr , name

Can you let me know how to get the values using

query.addEntity("A", TableA.class).addJoin("B", "A.tableB").addScalar("codeListName", StryingType.Instance)

/// how to get the "codeListName" here? this addScalar is not working. Is this correct approach?

SQLQuery query = session.createSQLQuery(query1);

List<Object[]> rows = query .list();

SternK · Answer 1 · 2021-05-30T20:46:03.263

As it's stated in the documentation:

To avoid the overhead of using ResultSetMetadata, or simply to be more explicit in what is returned, one can use addScalar():

For your case, the query will look like this:

List<Object[]> result = session.createNativeQuery(
    "select A.id as a_id, ..., CL.code as code_list_name "
  + "from tableA A "
  + "left join tableB B on A.id = B.id "
  + "left join codeList CL on A.id = CL.bId" )
.addScalar( "a_id", LongType.INSTANCE )
.addScalar( "code_list_name", StringType.INSTANCE )
// ...
.list();

for(Object[] row : result) {
    Long aId = (Long) row[0];       // id
    String codeListName = (String) row[1]; // code_list_name
    // ...
}

Although it still returns an Object arrays, this query will not use the ResultSetMetadata anymore since it explicitly gets the columns as respectively a BigInteger and a String from the underlying ResultSet. This also means that only these two columns will be returned, even though the query is still using * and the ResultSet contains more than the three listed columns.

It is possible to leave out the type information for all or some of the scalars.

SQL Query to retrieve the value of addScalar value

1 Answers1