How to get List of Table Entity with only selected columns in Hibernate using native SQL?

Question

I am trying to execute SQL query using session.createSQLQuery() method of Hibernate.

test table has 3 columns :

col1
col2
col3

Working

String sql = "SELECT * FROM test";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(Test.class);
List<Test> testEntityList  = query.list();

Not Working

String sql = "SELECT col1, col2 FROM test";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(Test.class);
List<Test> testEntityList  = query.list();

Error:

The column col3 was not found in this ResultSet.

I need to retrieve only a few specific columns from the table rather than the whole table.

How can I achieve this?

This error means that 'XYZ' is not a column in `test`. If you do `SELECT *`, do you see `XYZ` as a column? — Tyler Roper, Sep 07 '16 at 15:54
No Tyler column XYZ exists in the table and POJO too. For example there were 3 columns - name, age, and address. Now when I use sql query as "Select * from Tbl" this works fine . But When i only select - suppose two columns - select name, age from tbl then the error comes as column address not found. — Prem Singh Bist, Sep 08 '16 at 04:53
Ah misunderstood that this was merely a placeholder. Apologies. — Tyler Roper, Sep 08 '16 at 17:23

score 0 · Answer 1 · edited May 23 '17 at 12:08

You can use hibernate projections, see this answer Hibernate Criteria Query to get specific columns or you can do this by changing the return type to

List<Object[]> and parsing it to List<Test>

    List<Object[]> testEntityList  = query.list();
    List<Test> res = new ArrayList<Test>(testEntityList.size());

    for (Object[] obj : testEntityList) {
      Test test = new Test();
      test.setCol1(obj[0]);
      test.setCol2(obj[1]);
      res.add(test);
    }

How to get List of Table Entity with only selected columns in Hibernate using native SQL?

1 Answers1