0

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.

Improve this question
6
  • do not try to use the Neighborhood class as the enitity Commented Aug 4, 2017 at 2:22
  • see tutorialspoint.com/hibernate/hibernate_native_sql.htm Commented Aug 4, 2017 at 2:23
  • No, it does not work. I get the same exception. Commented Aug 4, 2017 at 2:25
  • Please show your modified code. Commented Aug 4, 2017 at 2:26
  • Are you columns names correct? maybe neighborhood_name ? Commented Aug 4, 2017 at 2:53

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Hi @Tehmina, thank you I think your second solution will work. I am actually getting an error but it is because I have incomplete information in question. Actually "educationYouth" is a seperate class. Class name is "EducationYouth". Do you know what modification will be required in that case?
Welcome @denis , simply remove all after q.addScalar("neighborhoodName"); and add the following code List<String> names = q.list(); System.out.println(names);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.