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.

You're selecting the first name, but you expect to get User objects out of it? It doesn't work that way. A first name is not a User, it's a String. — Kayaman
– Kayaman, Commented Mar 21, 2017 at 15:58
now tried to change <User> with <String> and after with <Object> but result was same, how can it be fixed? — Gog1nA
– Gog1nA, Commented Mar 21, 2017 at 16:16
Don't put addEntity() in there because you're not retrieving an entity. — Kayaman
– Kayaman, Commented Mar 21, 2017 at 16:17
Thank you, it worked. removed addEntity and worked all casese. — Gog1nA
– Gog1nA, Commented Mar 21, 2017 at 16:29
You should mark my answer as correct then, to give some closure to this question (and sweet sweet StackOverflow points to me). — Kayaman
– Kayaman, Commented Mar 21, 2017 at 16:52

Kayaman · Accepted Answer · 2017-03-21 16:14:16Z

3

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();

answered Mar 21, 2017 at 16:14

Kayaman

73.8k6 gold badges91 silver badges128 bronze badges

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

2 Comments

Gog1nA Over a year ago

Thanks, at first it didn't work but after removing addEntity() it worked with List<String> and with List<User> it seems i didn't fully understand how addEntity() works and where it have to use. I added addEntity() because, without it result was massive of objects: [[300,"John","Doe","01-01-1980","05-11-2011","Development","Senior"] but after added addEntity() it looked like this: '[{"id":300,"firstname":"John","lastname":"Doe","birthdate":"01-01-1980","startdate":"05-11-2011","department":"Development","position":"Senior"}]'

Kayaman Over a year ago

Well that's the difference between retrieving an entity and just parts of the data for the entity.

jizhihaoSAMA · Accepted Answer · 2020-06-20 12:08:02Z

1

This one worked for me:

SQLQuery query = session.createSQLQuery(sql);
query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);

List first_names = query.list();

for (Object object : first_names) 
         {
          System.out.println("User Details are==========  " + object);  
        // or
          Map map = (Map) object;
          System.out.println("first_names : " + map.get("first_name"));
         }

edited Jun 20, 2020 at 12:08

jizhihaoSAMA

12.7k9 gold badges32 silver badges52 bronze badges

answered Dec 26, 2017 at 9:49

Angad Bansode

1,02310 silver badges18 bronze badges

Collectives™ on Stack Overflow

java get several columns with hibernate SQLQuery

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related