Hibernate 5.2, How to select rows with a given column value?

Question

I am not new to SQL, but I am completely new to Hibernate. I expected that selecting a lost of objects from a table, given a column value, would be a very simple task. But I can't get it done.

Both my mentor, and this question below suggested the same thing, which lead me to write the code below: JPA: How to get entity based on field value other than ID?

Criteria criteria = session.createCriteria(Competition.class);
List<Competition> list = (List<Competition>) criteria
    .add(Restrictions.eq("Event_EventID", 1)).list();

This works, but the method Session.createCriteria() is deprecated. Since I am new to hibernate, I might as well learn it the way it's intended to work, so I'd prefer not to use deprecated methods.

THe official Hibernate documentation simply says to use the JPA Criteria. I have found extremely verbose JPA variants, that I simply find unacceptable. This example is what you would need for ONLY getting a full list of objects from all the records, without any WHERE clause... (from https://www.tutorialspoint.com/jpa/jpa_criteria_api.htm)

EntityManagerFactory emfactory = Persistence.createEntityManagerFactory( "Eclipselink_JPA" );
   EntityManager entitymanager = emfactory.createEntityManager( );
   CriteriaBuilder criteriaBuilder = entitymanager.getCriteriaBuilder();
   CriteriaQuery<Object> criteriaQuery = criteriaBuilder.createQuery();
   Root<Employee> from = criteriaQuery.from(Employee.class);

   //select all records
   System.out.println(“Select all records”);
   CriteriaQuery<Object> select = c riteriaQuery.select(from);
   TypedQuery<Object> typedQuery = entitymanager.createQuery(select);
   List<Object> resultlist = typedQuery.getResultList();

If this is the correct way to do it, I see no advantage from my standard Preparedstatement-approach. This is more verbose, and more complicated than anything I have seen.

Now, I've tried to look into specific questions about how to select lists of objects, not using deprecated methods in 5.2, and avoiding the JPA have-to-scroll-to-see-the-complete-code thing, but this is as close as I got: Hibernate 5.2 version -> A lot of Query methods deprecate?

This made me write the following code:

session.beginTransaction();
List<Competition> list = (List<Competition>) session.createQuery(
    "FROM Competition WHERE Event_EventID = 1").getResultList();

While this works, and is simple, it opens up for SQL injections if i were to have something else than ints as variable values. I've also been told in code reviews that when using Hibernate, no SQL syntax should be necessary.

Needless to say, I am a bit frustrated that something that should be so simple, is in fact really hard to figure out. It is either deprecated, extremely verbose, or prone to SQL injections. Does anyone know if I have missed something obvious, or if Hibernate is simply poorly designed and/or documented in this case?

Answer 1

While this works, and is simple, it opens up for SQL injections if i were to have something else than ints as variable values.

No, because you can pass parameters to a query, just like you would with a prepared statement (and Hibernate actually uses a prepared statement with parameters). You can actually do more, since parameters can be named:

String jpql = "select e from Event e where e.name = :name";
List<Event> events = 
    em.createQuery(jpql, Event.class)
      .setParameter("name", someNameComingFromAnywhere)
      .getResultList();

I've also been told in code reviews that when using Hibernate, no SQL syntax should be necessary.

And this is not SQL. This is JPQL (in fact, HQL, the hibernate-version of JPQL).

Here's the documentation.

You should, BTW, consider using the standard JPA API rather than the proprietary Hibernate API.

Answer 2

First of all this is how I would initialize entityManager: Calling Persistence.createEntityManagerFactory > 1 time

This is an example of how to select rows:

You have your entity with is mapped to a table e.g Customer table:

@NamedQueries({
    @NamedQuery(name = Customer.NAMED_QUERY_GET_BY_STATUS, query = "Select e from Customer e where e.status =:status")
 })
@Entity
@Table(name = Customer.TABLE_NAME)
public class Customer {

public static final String TABLE_NAME = "Customer";
public static final String NAMED_QUERY_GET_BY_STATUS = "Customer.getByStatus";

@Id
private Long id;

@Column(name = "status", nullable = false)
private String status;

}

Then use the named queries you have defined in your entity classes to get the rows you want from the table:

public static void main(String[] args){
        EntityManager em = null;
        try{
         JpaUtil.getEntityManager();
            em.getTransaction().begin();
            List<Customer> allNewCustomers = em.createNamedQuery(NAMED_QUERY_GET_BY_STATUS).setParameter("status", "NEW").getResultList();
            em.getTransaction().commit();
        } catch (Exception e){
            //logException
        } finally {
            if (em != null) {
                em.close();
            }
        }
    }

Edit: Its actually not to confusing once you understand the basics. The whole object of ORM is to map you java objects to tables. You can do this using annotations e.g. @Table, @Column etc. The specification for this is provided by JPA 2.0 specs. These specification are implemented by 3rd party libraries e.g. (Hibernate, TopLink etc). At times these libraries offer you additional features specific to the implementation (hibernate, toplink )on top JPA 2.0 spec. Unless really required you want to stay away from these additional implementation specific libs and ONLY use their implementation for JPA 2.This way if you dont like one implementation e.g hibernate you can switch it out for another e.g. TopLink.

To ensure that you only use JPA 2 Spec implementations only use classes from javax.persistence packages and stay away from org.hibernate.

Once you have defined your object class and mapped it to you database using annotations you need to configure JPA using a file called persistence.xml (which needs to be located on your project classpath). This file does two main things 1. defines a persistence unit (which a list of mapped objects to tables) and 2. configure properties (which can include DB connections and creating tables on database automatically ).

This is a good example: Hibernate: Automatically creating/updating the db tables based on entity classes