CODEWITHSUNDEEP
javamysqlhibernate
Denis
Denis

Reputation: 1229

Get specific columns of table in hibernate using addEntity

I am familiar with Java but really new with ORM and Hibernate.

I am using the following query to get the resultset of all columns of a table in hibernate. 

(List<Neighborhood>)session.createSQLQuery("SELECT * FROM Neighborhood").addEntity(Neighborhood.class).list();

I want to get only two specific column from this table. I looked up over the internet but not able to find the solution which can be used as a modification of above statement.

I tried the below query but getting - SQLGrammarException: could not extract ResultSet

(List<Neighborhood>)session.createSQLQuery("SELECT neighborhoodName,educationYouth FROM Neighborhood").addEntity(Neighborhood.class).list();

Edit:

Two more attempted queries after @scaryWombat's suggestion. Both give the same exception

First

List list = session.createSQLQuery("SELECT neighborhoodName,educationYouth FROM Neighborhood").list();

Second

String sql = "SELECT neighborhoodName,educationYouth FROM Neighborhood";
SQLQuery query = session.createSQLQuery(sql);
query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
List results = query.list();

Edit2:

After @Tehmina's solution, I am getting one error - java.sql.SQLException: Column 'educationYouth' not found because educationYouth is an object of class name "EducationYouth". In the Neighborhood table there is no column with name educationYouth but all the column from EducationYouth class.

Upvotes: 0

Views: 789

Answers (1)

Tehmina
Tehmina

Reputation: 205

Try this

(List<Neighborhood>)session.createSQLQuery("SELECT * FROM Neighborhood").list();

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

(List<Neighborhood>)session.createSQLQuery("SELECT * FROM Neighborhood").addScalar("neighborhoodName", Hibernate.STRING).addScalar("educationYouth", Hibernate.STRING);

Or try this Hibernate automatically converts the data into appropriate type. The automatic conversion does not work in all cases and in that case we have an overloaded version of addScalar(): 

SQLQuery q = session.createSQLQuery("SELECT * FROM Neighborhood");        
    q.addScalar("neighborhoodName");    
    q.addScalar("educationYouth");   
     List<Object[]> rows = q.list();
for (Object[] row : rows) {
    System.out.println(row[0] + " " + row[1] );

Don't forget to check in the hibernate config file

<!--hibernate.cfg.xml -->
<property name="show_sql">true</property>

I hope it would resolve your error.

Upvotes: 1

Related Questions