Spring Hibernate get selected columns

Question

I am trying to get some specific columns using hibernate, but this query always fails.

@Override
public Users findByEmail(String emailID) {
    // TODO Auto-generated method stub
    try{

        List<Users> users = new ArrayList<Users>();

        users = this.sessionFactory.getCurrentSession().createQuery("SELECT userID, firstName, lastName, imageURL, password, emailID, enabled FROM Users WHERE emailID = :email_ID")
                .setParameter("email_ID", emailID).list();

        if(users.size() > 0)
            return (Users)users.get(0);
        else
            return null;
    }
    catch(Exception exc)
    {
        log.error(exc.getMessage());
        return null;
    }
}

This query always fails, so I use the below query

    @Override
public Users findByEmail(String emailID) {
    // TODO Auto-generated method stub
    try{

        List<Users> users = new ArrayList<Users>();

        users = this.sessionFactory.getCurrentSession().createQuery("FROM Users WHERE emailID = :email_ID")
                .setParameter("email_ID", emailID).list();

        if(users.size() > 0)
            return (Users)users.get(0);
        else
            return null;
    }
    catch(Exception exc)
    {
        log.error(exc.getMessage());
        return null;
    }
}

How can I get only specific columns using hibernate rather than getting 20s of columns?

Answer 1

Whenever you will select multiple columns query will return List<Object[]>

List<Object[]> rows = this.sessionFactory.getCurrentSession()
                     .createQuery("SELECT userID, firstName, lastName,
                      imageURL, password,emailID, enabled 
                       FROM Users WHERE emailID = :email_ID")
                      .setParameter("email_ID", emailID).list();

Now Iterate

(Object[] row: rows) {
    System.out.println(" ------------------- ");
    System.out.println("userid: " + row[0]);
    System.out.println("firstName: " + row[1]);
    User u=new User();
    u.setUserId(row[0]);
    u.setFirstName(row[1]);
    u.setLastName(row[2]);

}

and same for other rows

Reference

Answer 2

You can try to use a result transformer

List<User> users = session
.createQuery(
"SELECT userID as userID, firstName as firstName FROM Users WHERE emailID = :email_ID")
.setParameter("email_ID", emailID)
.setResultTransformer(new AliasToBeanResultTransformer(User.class)).list();

You need to specify projection aliases: firstName as firstName, but you can try without it.