Getting data from database using HQL

Question

Hi, I have database Staff with 3 tables:

mysql> describe person;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| person_id  | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| date       | datetime     | YES  |     | NULL    |                |
| first_name | varchar(255) | YES  |     | NULL    |                |
| last_name  | varchar(255) | YES  |     | NULL    |                |
| position   | varchar(255) | YES  |     | NULL    |                |
| salary     | double       | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

mysql> describe department;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| department_id | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| dept_name     | varchar(255) | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+

mysql> describe department_person;
+---------------+------------+------+-----+---------+-------+
| Field         | Type       | Null | Key | Default | Extra |
+---------------+------------+------+-----+---------+-------+
| department_id | bigint(20) | NO   | PRI | NULL    |       |
| person_id     | bigint(20) | NO   | PRI | NULL    |       |
+---------------+------------+------+-----+---------+-------+

So, I want to compose HQL statement for getting data from person.position using person.lastName and I've used the following code to exctract this data but without luck:

public List<Person> findPosition(){
        Session session =
                HiberUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        List<Person> result = session.createQuery("select position from Person p where p.lastName= 'Anderson'").list();
        for(Person a : result) {
            Hibernate.initialize(a.getDepartmentList());
        }
        session.getTransaction().commit();
        return result;

    }

In this was it shows me this exception:

Hibernate: select person0_.position as col_0_0_ from person person0_ where person0_.last_name='Anderson'
Exception in thread "main" java.lang.ClassCastException: java.lang.String cannot be cast to main.Person
    at main.StaffDAO.findPosition(StaffDAO.java:100)
    at main.Main.main(Main.java:37)

Can you please help me in composing correct HQL statement?

SJuan76 · Accepted Answer · 2013-04-15 20:04:01Z

1

List<Person> result = session.createQuery("select position from Person p where p.lastName= 'Anderson'").list();

You are selecting an attribute that holds a VARCHAR(255) but expect it returned as a List<Person>. That simply does not compute.

You could do

List<Person> result = session.createQuery("select p from Person p where p.lastName= 'Anderson'").list();

answered Apr 15, 2013 at 20:04

SJuan76

24.9k7 gold badges52 silver badges95 bronze badges

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

Comments

Charles François Rey · Accepted Answer · 2013-04-15 20:05:46Z

1

Well, you're selecting a position in your query, and trying to put that in a list of Person. Instead you should select p from Person p where p.lastName= 'Anderson'.

answered Apr 15, 2013 at 20:05

Charles François Rey

521 silver badge4 bronze badges

Comments

elopez · Accepted Answer · 2013-04-15 20:10:30Z

0

select Person from Person p where p.lastName= 'Anderson'

answered Apr 15, 2013 at 20:10

elopez

1099 bronze badges

Collectives™ on Stack Overflow

Getting data from database using HQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related