Use Where clause in Hibernate and select several columns

Question

I wan to use the code to select column using the latest Hibernate

public TerminalsModel getTerminalToken(String terminalToken) throws Exception {
        TerminalsModel terminal = null;
        try {
            session.getTransaction().begin();
            terminal = (TerminalsModel) session.get(TerminalsModel.class, terminalToken);
            session.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            session.getTransaction().rollback();
            throw new Exception("Error");
        }
        return terminal;
    }

I want to use this code to select only several columns:

Criteria cr = session.createCriteria(User.class)
    .setProjection(Projections.projectionList()
      .add(Projections.property("id"), "id")
      .add(Projections.property("Name"), "Name"))
    .setResultTransformer(Transformers.aliasToBean(User.class));

But I found that createCriteria is deprecated. What Java method should I use?

Is there a way to implement SQL query with WHERE without custom SQL statement and to get only one columns as a result? I want to leave everything to Java code for management.

Answer 1

Returning just one column in a JPA query is reasonably straight forward:

public List<Integer> getUserIdsByName(String name) {

    CriteriaBuilder builder = session.getCriteriaBuilder();
    CriteriaQuery<Integer> query = builder.createQuery( Integer.class );

    Root<User> root = query.from( User.class );
    query.select( root.get( "id" ) );
    query.where( builder.equal( root.get( "name" ) , name ) );

    return session.createQuery( query ).getResultList();
}

I'm not aware of the equivalent of the ResultTransformer in your example, but you can select multiple properties. This could be to return a simple array of values, or in this example using constructor parameters on a return type:

public List<UserIdAndName> getUserIdAndNameByName(String name) {

    CriteriaBuilder builder = session.getCriteriaBuilder();
    CriteriaQuery<UserIdAndName> query = builder.createQuery( UserIdAndName.class );

    Root<User> root = query.from( User.class );
    query.where( builder.equal( root.get( "name" ) , "9" ) );
    query.select( builder.construct( UserIdAndName.class , root.get( "tariff" ).get( "id" ) , root.get( "name" ) ) );

    return session.createQuery( query ).getResultList();
}