java get several columns with hibernate SQLQuery

Question

i'm using SQLQuery to select some data from database using hibernate. when trying to select * it works but when need only several columns it returns:Invalid column name searched other topics about this issue but didn't helped; here is my code:

User.java where is declared variables:

public class User implements Serializable {

private static final long serialVersionUID = -1798070786993154676L;
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "id_Sequence")
@Column(name = "ID", unique = true, nullable = false)
@SequenceGenerator(name = "id_Sequence", sequenceName = "ID_SEQ")

private Integer id;

@Column(name = "FIRST_NAME", unique = false, nullable = false, length = 100)
private String firstname;
@Column(name = "LAST_NAME", unique = false, nullable = false, length = 100)
private String lastname;
@Column(name = "DATE_OB", unique = false, nullable = false, length = 100)
private String birthdate;
@Column(name = "DATE_OW", unique = false, nullable = false, length = 100)
private String startdate;
@Column(name = "DEPARTMENT", unique = false, nullable = false, length = 100)
private String department;
@Column(name = "POSITION", unique = false, nullable = false, length = 100)
private String position;

//with getters and setter

part from where trying to select:

public static List<User> getAllUser()
{
    List<User> result = null;
    Session session = HibernateUtil.getSessionFactory().openSession();
    Transaction tx = null;
    try {
        tx = session.beginTransaction();

        String sql = "select first_name from employee";
        SQLQuery query = session.createSQLQuery   (sql).addEntity(User.class);
        result = query.list();



    } catch (HibernateException e) {
        if (tx != null)
            tx.rollback();
        e.printStackTrace();
    } finally {
        session.close();
    }
    return result;

}

As i guess i don't need to create separate hibernate-mapping file, because its already done in User class. Please help to find out what can be reason.

Answer 1

When you're loading data with a native query (as it's called in JPA world), you can either load full entities when querying all the columns, i.e. SELECT * FROM foo or you can load one or more separate columns, which result in getting a Object[] or some specific type (such as String).

Here you're trying to load just the first name as a User entity, presumably hoping that the other fields would be left blank. However that's not how it works. Either you load a full entity, or an array of specified column values.

Something like the following should work

SQLQuery query = session.createSQLQuery(sql);
List<String> names = query.list();