Hibernate query for selecting multiple values

Question

In hibernate I can do following

Query q = session.createQuery("from Employee as e");
List<Employee> emps = q.list();

Now if I want to fetch int and String how can I do it?

Query q = session.createQuery("SELECT E.firstName,E.ID FROM Employee E");
List ans = q.list();

Now what will be the structure of list?

Check out [Adisesha's answer](https://stackoverflow.com/questions/2605385/using-sql-column-names-in-hibernate-createsqlquery-result/62484364#62484364). It's more elegant than the answers here IMO, just that you would need to use aliases with HQL. — jumping_monkey, Jun 20 '20 at 10:27

score 51 · Accepted Answer · answered Nov 28 '12 at 14:32

51

This is fine. Only thing you need to understand is that it will return list of Object [] as below:

     Query q = session.createQuery("select e.id, e.firstName from Employee e");
     List<Object[]> employees= (List<Object[]>)q.list();
     for(Object[] employee: employees){
         Integer id = (Integer)employee[0];
         String firstName = (String)employee[1];
         .....
     }

answered Nov 28 '12 at 14:32

Yogendra Singh

33,927
6
63
73

What if i have a many to many relation and the intermediate table is not modeled as class. how can i use that table in hql query? – user93796 Nov 28 '12 at 14:59
@user93796 Hibernate also allows you to run standard `SQL`, which you can create as: `Query q = session.createSQLQuery("select id, first_Name from Employee");` where `createSQLQuery` is used to run SQL; id & first_Name are database columns and Employee is database table. – Yogendra Singh Nov 28 '12 at 15:07
@user93796 Not sure that my previous message was clear. What I meant was: if you want to use un-mapped tables in the query then `SQL` is better option. – Yogendra Singh Nov 28 '12 at 15:39
thanks for helping.Let me be bit clear. I have a many to many realtion between table A and table B .Table C maps A to B.A hase set of B .And B has Set of A.Now i want to select all A which has B b1 as one of the entity in its set.Eg consider stackoverflow.I want to select all post which has java as one of its tag. In such case A table is Post , B is tags and C is relation between A and B.How will i fetch n posts of java tags using HQL – user93796 Nov 28 '12 at 17:29
@user93796: This is way different question than the one you posted earlier. Any way, try something like: `select p from Post p join p.tags t where t.tagName in ('Java')`. This should do your job provided you have done the mapping properly. – Yogendra Singh Nov 28 '12 at 17:34
t.tagName is a Set.So how can it be t.tagSet in ('Java') ? should it be other way round? – user93796 Nov 28 '12 at 17:38
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/20260/discussion-between-yogendra-singh-and-user93796) – Yogendra Singh Nov 28 '12 at 17:39
@YogendraSingh, how to access if I have multiple records? I can get list of it, but can't handle individually, my query is like above. – Niraj Jul 14 '16 at 12:44
@niraj: It is list of array. Array has individual fields..access them as mentioned in the example. – Yogendra Singh Jul 14 '16 at 16:45

score 10 · Answer 2 · answered Nov 28 '12 at 14:31

10

You will get a list of arrays of Objects (each one with two elements)

List< Object[] > employees = q.list();

for ( Object[] employee : employees ) {
    // employee[0] will contain the first name
    // employee[1] will contail the ID
}

answered Nov 28 '12 at 14:31

Matteo

14,696
9
68
106

is the order of the array determined by the order of the query? – Fernando Castilla Ospina Jul 18 '17 at 20:18

score 4 · Answer 3 · answered Nov 28 '12 at 14:30

4

List<Object[]> is the structure.

So you get each element like this:

List ans = q.list();
for(Object[] array : ans) {
    String firstName = (String) array[0];
    Integer id = (Integer) array[1];
}

answered Nov 28 '12 at 14:30

Jaanus

16,161
49
147
202

score 3 · Answer 4 · answered Nov 28 '12 at 14:30

You should use a new object to hold these values, just like this:

"SELECT NEW EmpMenu(e.name, e.department.name) "
                + "FROM Project p JOIN p.students e " + "WHERE p.name = :project "
                + "ORDER BY e.name").setParameter("project", projectName).getResultList()

I've got this example from http://www.java2s.com/Tutorial/Java/0355__JPA/EJBQLCreatenewObjectInSelectStatement.htm

score 3 · Answer 5 · answered Mar 26 '13 at 07:28

Query qry=session.createQuery("select e.employeeId,e.employeeName from Employee e where e.deptNumber=:p1");
qry.setParameter("p1",30);
List l2=qry.list();
Iterator itr=l2.iterator();
while(itr.hasNext()){
Object a[]=(Object[])itr.next();
System.out.println(a[0]+"/t"a[1]);
}

score 1 · Answer 6 · answered Aug 25 '16 at 09:42

Without iterators:

@SuppressWarnings( "unchecked" ) 
public List<Employee> findByDepartment(long departmentId){ 

    SQLQuery query = session.createSQLQuery("SELECT {emp.*} " +
                                             " FROM employee emp " + 
                                            +"WHERE emp.department_id = :departement_id");
    query.setLong("department_id",  departmentId);
    query.addEntity("emp",  Employee.class);                        
    return (List<Employee>) = query.list();
}

Hibernate query for selecting multiple values

6 Answers6

Linked