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();

Answer 1

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.