Hibernate jpql query correct usage

Question

I am creating a Jersey web service with Hibernate and Dropwizard.

I wanted to ask for a correct and effective way of using them for connecting and using DB.

I have already defined the DB itself. I wanted to ask about the correct way of adding records and query the DB.

Regarding querying the DB, right now I got some simple JPQL queries in the annotaion @NamedQueries which is a part of the class which represents the database table. For example:

@XmlRootElement
@Entity(name = "Persons")
@Table(name = "Persons")
@NamedQueries(
        { 
        @NamedQuery(name = "Person.findAll", query = "select p from Persons p"),
        @NamedQuery(name = "Person.findByEmail", query = "select p from Persons p " + "where p.personEmail like :email") 
        })

public class Person implements Serializable
{ ... }

When I want to query I am doing something like this:

public Person getPersonByEmail(String email)
{
        Person personByName = (Person) namedQuery("Person.findByEmail").setParameter("email", email).uniqueResult();
        return personByName;
}

Is this a correct and effective way of querying the DB? should I stack queries like that in the @NamedQueries annotation?

Regarding to addition to the DB, I'm doing it like this for now:

public void insertPersonToDB()
    {
        Session session = sessionFactory.openSession();
        Transaction tx = null;

        try
        {
            tx = session.beginTransaction();
            Person pers = new Person("param1","param2");
            short personID = (short) session.save(pers);
            tx.commit();
        }
        catch (HibernateException e)
        {
            if (tx != null) tx.rollback();
            e.printStackTrace();
        }
        finally
        {
            session.close();
        }
}

Is this also a correct and effective way of adding records to the DB?

Answer 1

When I want to query I am doing something like this:

public Person getPersonByEmail(String email){
        Person personByName = (Person) namedQuery("Person.findByEmail").setParameter("email", email).uniqueResult();
        return personByName;
}

Is this a correct and effective way of querying the DB? should I stack queries like that in the @NamedQueries annotation?

The other approach is using createQuery(jpqlQuery) :

public Person getPersonByEmail(String email){
        Person personByName = (Person) em.createQuery(
        "select p from Persons p where p.personEmail like :email"). 
        setParameter("email", email).getSingleResult();
        return personByName;
}

We call sometimes, queries created with createQuery() dynamic queries, since not compiled.
You can also use Criteria (full object way for creating your query) but it's more complex to write, so it's really useful for "true" dynamic queries where some clauses of the query are added at runtime (add a condition or a join for example).

Generally, if your request has no requirement to be created in a dynamic way (with SQL clause added at runtime), you will often be advised to use @NameQuery rather than createQuery(java.lang.String jpqlString) since considered as more performant. Personally, I think that it is a false good advice.

For example, in Pro EJB 3: Java Persistence API, you are said that Named queries are a way of organising your static queries in a manner that is more readable, maintainable as well as performant.

For performance, it is theoretic.
It refers to the translation from HQL to SQL.
With NamedQuery, you are sure it's performed a single time.
But with createQuery(java.lang.String jpqlString), the effective performance is the same or almost, if you do well the things.

First, transforming a HQL query to SQL represents almost nothing if you compare it to the cost for transmitting the query to the database, executing it and retrieving the result (sql mapping to object mapping)
Second, most of query engines (Hibernate for example) will cache the translated SQL but if you build your query as you should not : string concatenation for setting parameters of the query.

For the remain, createQuery(java.lang.String jpqlString) help to create a good design since it's obviously more easy to read and modify a query from where it's called than to the root of an entity.
No indirection to read it in its globability and also to see how are set the parameters in the query.
On the contrary, modifying a JPQL query among many other JPQL queries at the root of the class, which may look like and have a very near name is error-prone.

The single real advantage for NamedQuery is at compile-time, syntax errors in the query may be detected and so stops the build fast.

Edit : second part of the question

Regarding to addition to the DB, I'm doing it like this for now:

public void insertPersonToDB()
    {
        Session session = sessionFactory.openSession();
        Transaction tx = null;

        try
        {
            tx = session.beginTransaction();
            Person pers = new Person("param1","param2");
            short personID = (short) session.save(pers);
            tx.commit();
        }
        catch (HibernateException e)
        {
            if (tx != null) tx.rollback();
            e.printStackTrace();
        }
        finally
        {
            session.close();
        }
}

Is this also a correct and effective way of adding records to the DB?

Almost correct. You should log in the catch HibernateException for debugging purpose and you should also log and throws an exception if the rollback fails.

public void insertPersonToDB()
    {
        Session session = sessionFactory.openSession();
        Transaction tx = null;

        try{
            tx = session.beginTransaction();
            Person pers = new Person("param1","param2");
            short personID = (short) session.save(pers);
            tx.commit();
        }
        catch (HibernateException e){
              try {
                     if (tx != null) {
                       tx.rollback();                         
                       logger.error(e);
                      }
                  } catch(Exception e2) {
                     logger.error(e2);
                     throw new RunTimeException("rollback failed", e2);
                  }
             } 
        }
        finally{
            session.close();
        }
}