[Java] Access MySQL with Hibernate

In this post we will give a fair example on how to access a MySQL database via Hibernate in Java.
For this example we will be using MySQL server 5.5, Hibernate 3.3 and the Eclipse IDE.





Download here the example's source code.


Suppose we have access to a database,test, with a table, testtable:

Column name	Column type	Constraints	Description
id	int	AUTO_INCREMENT PRIMARY KEY	The object's ID
column1	varchar(20)	UNIQUE	A unique String for this object
column2	int	NOT NULL	A mandatory numeric field for this object

Created with the following SQL code:


create table testtable(id int AUTO_INCREMENT, column1 varchar(20) unique, column2 int not null, primary key(id));


And a Java class, PersistentObject, defining an object to be made persistent in said table:


package it.eng.test.hibernate.persistentobject;
import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;
import javax.persistence.Transient;
@Entity
@Table(name = "testtable")
@NamedQueries(value = {
  @NamedQuery(name = "getItem", query = "select i from PersistentObject i where i.column1 = ?"),
  @NamedQuery(name = "getItems", query = "select i from PersistentObject i"),
  @NamedQuery(name = "deleteItem", query = "delete from PersistentObject i where i.column2 = ?")
  })
public class PersistentObject implements Serializable{
  private static final long serialVersionUID = -2853745324178701257L;
  /*Required for Hibernate*/
  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  @Column(name = "id")
  private Long id;
  /*Our columns*/
  @Column(name = "column1")
  private String column1;
  @Column(name = "column2")
  private int column2;
  /*This field is part of our PersistentObject but will not be stored on the DB*/
  @Transient
  private Object nonPersistent;

  /*Getters & Setters, every persistent field must have one for Hibernate*/
  public Long getID(){
   return id;
  }

  public String getColumn1() {
   return column1;
  }
  public void setColumn1(String column1) {
   this.column1 = column1;
  }
  public int getColumn2() {
   return column2;
  }
  public void setColumn2(int column2) {
   this.column2 = column2;
  }
  public void setId(Long id) {
   this.id = id;
  }

  public Object getNonPersistent() {
   return nonPersistent;
  }
  public void setNonPersistent(Object nonPersistent) {
   this.nonPersistent = nonPersistent;
  }

  /*Required for Hibernate*/
  public PersistentObject(){
  }

  /*Our own constructor*/
  public PersistentObject(String column1, int column2, Object nonPersistent){
   this.column1 = column1;
   this.column2 = column2;
   this.nonPersistent = nonPersistent;
  }
}


 By using Java annotations, we are able to easily mark which class fields we want to make persistent and which queries we are going to create for our object:


@Entity
@Table(name = "testtable")
@NamedQueries(value = { @NamedQuery(name = "getItem", query = "select i from PersistentObject i where i.column1 = ?"),
@NamedQuery(name = "getItems", query = "select i from PersistentObject i"),
@NamedQuery(name = "deleteItem", query = "delete from PersistentObject i where i.column2 = ?") })


With this, we are saying that there is a table called testtable in our database on which the queries listed afterwards should operate. We wrote them in HQL, a modified version on SQL by Hibernate.


The first query for example is the equivalent of:



SELECT * FROM testtable WHERE column1 = VALUE;



and by executing it, we are returned the matching PersistentObject object(s).


The PRIMARY KEY is specified as required for Hibernate as an AUTO_INCREMENT integer:


@Id
@GeneratedValue(strategy = GenerationType.AUTO) @Column(name = "id")
private Long id;


Note that our class can also include fields which will not be stored; to define so, we simply mark them as @Transient.


Hibernate also requires us to create an empty constructor and getter and setter methods for each persistent field.

Now we need to create two more classes in order for Hibernate to function properly:


package it.eng.test.hibernate;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
public class HibernateServiceUtil {
 private static EntityManagerFactory emf;

    private static EntityManager em;

    /*The name of our PERSISTENCE_UNIT, as specified in the META-INF/persistence.xml file*/
 private static final String PERSISTENCE_UNIT = "TEST";

 /*Initialize our DB connection*/
 public static EntityManager initEntityManager() {
        if (emf == null) {
         /*
          * If we want to set those properties via code rather than write them in the XML file:
          *
         Map addedOrOverridenProperties = new HashMap();
         addedOrOverridenProperties.put("hibernate.connection.password", PWD);
         addedOrOverridenProperties.put("hibernate.connection.url", URL);
         addedOrOverridenProperties.put("hibernate.connection.username", USERNAME);       
         emf = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT, addedOrOverridenProperties);
         */
         emf = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT);
            em = emf.createEntityManager();
        }
        return em;
    }

    public void cleanup() {
        em.clear();
     emf.close();
    }
   
}


With this, we define a way to initialize Hibernate's Entity Manager for our persistence units. We are also required to create an XML file called persistence.xml and put it under the META-INF/ folder of our project:


<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0"
 xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">
 <persistence-unit name="TEST" transaction-type="RESOURCE_LOCAL">
  <class>it.eng.test.hibernate.persistentobject.PersistentObject</class>
  <properties>
   <property name="hibernate.archive.autodetection" value="class, hbm" />
   <property name="hibernate.show_sql" value="true" />
   <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver" />
   <!-- We can set those via code in the HibernateServiceUtil class -->
   <property name="hibernate.connection.password" value="12345" />
   <property name="hibernate.connection.url" value="jdbc:mysql://127.0.0.1/test" />
   <property name="hibernate.connection.username" value="root" />
   <!-- *** -->
   <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect" />
   <property name="hibernate.c3p0.min_size" value="5" />
   <property name="hibernate.c3p0.max_size" value="20" />
   <property name="hibernate.c3p0.timeout" value="300" />
   <property name="hibernate.c3p0.max_statements" value="50" />
   <property name="hibernate.c3p0.idle_test_period" value="3000" />
  </properties>
 </persistence-unit>
</persistence>


Where we define, among other things, the connection parameters for the database containing the table for our PersistentObject objects:


<property name="hibernate.connection.password" value="PWD" />
<property name="hibernate.connection.url" value="jdbc:mysql://URL/test" />
<property name="hibernate.connection.username" value="USERNAME" />


Alternatively, we could set those parameters at runtime by using the following code inside the HibernateServiceUtil class:


Map addedOrOverridenProperties = new HashMap();
addedOrOverridenProperties.put("hibernate.connection.password", PWD);
addedOrOverridenProperties.put("hibernate.connection.url", URL);
addedOrOverridenProperties.put("hibernate.connection.username", USERNAME);
emf = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT, addedOrOverridenProperties);

We also need a PersistentObjectHibernateUtil class where to code our queries in Java to perform at least the basic CRUD operations:


package it.eng.test.hibernate.persistentobject;
import java.util.List;
import it.eng.test.hibernate.HibernateServiceUtil;
import javax.persistence.EntityManager;
import javax.persistence.Query;
public class PersistentObjectHibernateUtil {
 private static EntityManager entityManager;
 public static void initPersistentObjectHibernateUtil() {
  entityManager = HibernateServiceUtil.initEntityManager();
 }
 /*Create and store our object on the DB*/
 public static PersistentObject savePersistentObject(String column1, int column2, Object nonPersistent) {
  entityManager.getTransaction().begin();
  PersistentObject p = new PersistentObject();
  p.setColumn1(column1);
  p.setColumn2(column2);
  p.setNonPersistent(nonPersistent);
  entityManager.persist(p);
  entityManager.getTransaction().commit();
  return p;
 }

 /*Store an already existing object on the DB*/
 public static void savePersistentObject(PersistentObject p) {
  entityManager.getTransaction().begin();
  entityManager.persist(p);
  entityManager.getTransaction().commit();
 }
 /*Implementation of our NamedQueries*/

 public static PersistentObject getItem(String column1){
  try{
   Query query = entityManager.createNamedQuery("getItem");
   /*place this value where the first ? appeared in the query*/
   query.setParameter(1, column1);
   PersistentObject result = (PersistentObject) query.getSingleResult();
   return result;
  }catch(Exception e){
   System.err.println("unable to get PersistentObject "+column1+" from DB");
   return null;
  }
 }

 @SuppressWarnings("unchecked")
 public static List<PersistentObject> getItems(){
  try{
   Query query = entityManager.createNamedQuery("getItems");
   return query.getResultList();
  }catch(Exception e){
   System.err.println("unable to get PersistentObjects from DB");
   return null;
  }
 }

 public static void deleteItem(int column2){
  try{
   Query query = entityManager.createNamedQuery("deleteItem");
   /*place this value where the first ? appeared in the query*/
   query.setParameter(1, column2);
   query.executeUpdate();
  }catch(Exception e){
   System.err.println("unable to delete PersistentObject "+column2+" from DB "+e.getMessage());
   e.printStackTrace();
  }
 }

 /*We can also overload those methods*/
 public static void deleteItem(PersistentObject p){
  try{
   entityManager.getTransaction().begin();
   Query query = entityManager.createNamedQuery("deleteItem");
   query.setParameter(1, p.getColumn2());
   query.executeUpdate();
   entityManager.getTransaction().commit();
  }catch(Exception e){
   System.err.println("unable to delete PersistentObject "+p.getColumn2()+" from DB "+e.getMessage());
   e.printStackTrace();
  }
 }

 /*Updates an object already stored*/
 public static void updatePersistentObject(PersistentObject p){
  try{
   if (p == null){
    System.err.println("cannot update PersistentObject because it is NULL");
    return;
   }
   entityManager.getTransaction().begin();
   entityManager.merge(p);
   entityManager.getTransaction().commit();
  }catch(Exception e){
   System.err.println("unable to update PersistentObject "+p.getColumn1()+" on DB");
  }
 }
}
As you can see, for every namedQuery previously defined and for basic operations, we define a method which tells Hibernate what to perform for us. For example, for the getItem query previously defined we use:


Query query = entityManager.createNamedQuery("getItem");
query.setParameter(1, VALUE);


to tell Hibernate to replace the first "?" in the query HQL with VALUE before executing it.

Finally, we are required to put an additional XML file, called hibernate.cfg.xml under our project's root:


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
  "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
  "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.connection.password">12345</property>
        <property name="hibernate.connection.url">jdbc:mysql://127.0.0.1/test</property>
        <property name="hibernate.connection.username">root</property>
        <property name="hibernate.default_schema">test</property>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
        <!-- JDBC connection pool (use the built-in) -->
  <property name="connection.pool_size">1</property>
  <!-- Enable Hibernate's automatic session context management -->
  <property name="current_session_context_class">thread</property>
  <!-- Disable the second-level cache -->
  <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
  <!-- Echo all executed SQL to stdout -->
  <property name="show_sql">true</property>
    </session-factory>
</hibernate-configuration>


where we define some Hibernate configuration parameters.

You can then export the project as a Runnable JAR from Eclipse or download here the already packaged and ready-to-go demo.