Hibernate

Our previous example on persistence had a simple case of a single class User, which had no dependencies on any other class.

But in a real situation, classes can have a chain of dependencies.

For example, our User class might have a property cart of type Cart. The Cart class itself might have a List of items, each of type Item. The Item class may further have a property vendor of type Vendor.

If we had to write a DAO for User, we will have to:
1. Consider all its dependencies
2. Create tables and schemas for all dependencies
3. Come up with foreign keys which correctly link the tables.
4. Come up with SQL queries which efficiently persist/update/query the tables

Additionally, we need to try and keep things database vendor independent - so that our DAOs can be ported from say MySQL to Oracle if need be.

None of this is easy.

Trying to do this yourself results in infrastructure code which is extra effort to maintain, and prone to critical bugs which can corrupt data.

Hibernate is an ORM (Object-Relational-Mapping) solution which solves most of these, and other issues.

It is Object-Oriented. This means it expects that you are trying to persist Objects to the database, and fully supports relations between objects, including collections, inheritance and polymorphism.

Hibernate defines strict rules for how Objects should be mapped to tables. Once you create tables following these rules, Hibernate can automatically generate efficient and vendor sensitive queries for persisting to and querying the database.

In one line:
Hibernate makes it easy to write DAOs, especially for classes with dependencies.

JDBC - Transactions

Sometimes you have multiple Database interactions which are related.

For example, suppose we are doing a money transfer between two accounts. It will involve deleting the amount from one account, adding the amount to the other account and updating the transfer log.

If any of these individual database interactions fail, then all three should be failed. Otherwise there will be an inconsistency between the accounts and the logs.

This is called a Transaction - when a series of database interactions all succeed or fail together.

JDBC supports transactions, through its commit() and rollback() methods.

By default, all JDBC query executions are auto-commit.

So to do a transaction, we first set the autocommit to false.
Then if all the individual query executions pass, we do a commit.
Otherwise we do a rollback.

Let us see this with an example.

We will write a method in UserDAO which persists several users at a time, as a transaction. Either all the users persist, or none of them.

Update your UserDAO.java, like this:

 package org.confucius;  
   
 import java.sql.Connection;  
 import java.sql.PreparedStatement;  
 import java.sql.ResultSet;  
 import java.sql.SQLException;  
 import java.util.ArrayList;  
 import java.util.Iterator;  
 import java.util.List;  
   
 import com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource;  
   
 public class UserDAO {  
      private static MysqlConnectionPoolDataSource dataSource = null;  
        
      // Static initialization block  
      static {  
           dataSource = new MysqlConnectionPoolDataSource();            
           dataSource.setUser("confucius");  
           dataSource.setPassword("changeit");  
           dataSource.setServerName("localhost");  
           dataSource.setPort(3306);  
           dataSource.setDatabaseName("confuciusDB");  
      }  
        
      public static void persistGroup (List<User> users) throws SQLException{  
     Connection conn = dataSource.getConnection();   
     conn.setAutoCommit(false);  
   
     String sqlQuery = "insert into users value(?, ?)";   
     PreparedStatement ps = conn.prepareStatement(sqlQuery);   
       
     try {  
       for (Iterator<User> iter = users.iterator(); iter.hasNext(); ){  
            User user = iter.next();  
         ps.setString(1, user.getFirstName());   
         ps.setString(2, user.getLastName());   
         ps.execute();   
       }  
       conn.commit();  
     }  
     catch (SQLException e){  
          conn.rollback();  
          throw e;  
     }  
       
     conn.setAutoCommit(true);  
       
     ps.close();   
     conn.close();   
      }  
        
        
 }  
   

Now, update TestPersistence.java, like this:

 package org.confucius;  
   
   
 import java.sql.SQLException;  
 import java.util.ArrayList;  
 import java.util.Iterator;  
 import java.util.List;  
   
 public class TestPersistence {  
   
      public static void main(String[] args) {  
           try {  
                User jack = new User();  
                jack.setFirstName("Jack");  
                jack.setLastName("Hill");  
                  
                User jill = new User();  
                jill.setFirstName("Jill");  
                jill.setLastName("WentUpTheHillToFetchAPailOfWaterFellDownBrokeCrown");  
                  
                List<User> users = new ArrayList<User>();  
                users.add(jack);  
                users.add(jill);  
                  
                UserDAO.persistGroup(users);  
                  
           } catch (SQLException e) {  
                e.printStackTrace();  
           }  
             
             
      }  
   
 }  
   

We try to persist two users - Jack and Jill.

However, we have given Jill a last name that exceeds 32 characters - so it will fail persist.

R-click on the TestPersistence.java in Eclipse- Navigator view. Select Run As-->Java Application.

You will see a DataTruncation exception in the console.

If you look at the users table in MySQL, you will not see Jack - because Jill failed to persist, Jack was not persisted either.

If you give Jill a shorter last name, and re-run the application, they will both be persisted.

Data Access Objects - DAOs

Code that deals with database interaction is typically encapsulated in its own class. These classes are called DAOs.

DAOs provide a clean interface to the database for the other classes.

Let us see this with an example. We will create a DAO class for the User class (from the previous post).

In your /src/org/confucius, create a class UserDAO.java, like this:

 package org.confucius;  
   
 import java.sql.Connection;  
 import java.sql.PreparedStatement;  
 import java.sql.ResultSet;  
 import java.sql.SQLException;  
 import java.util.ArrayList;  
 import java.util.List;  
   
 import com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource;  
   
 public class UserDAO {  
      private static MysqlConnectionPoolDataSource dataSource = null;  
        
      // Static initialization block  
      static {  
           dataSource = new MysqlConnectionPoolDataSource();            
           dataSource.setUser("confucius");  
           dataSource.setPassword("changeit");  
           dataSource.setServerName("localhost");  
           dataSource.setPort(3306);  
           dataSource.setDatabaseName("confuciusDB");  
      }  
        
      public static void persist (User user) throws SQLException{  
     Connection conn = dataSource.getConnection();   
       
     String sqlQuery = "insert into users value(?, ?)";   
     PreparedStatement ps = conn.prepareStatement(sqlQuery);   
      
     ps.setString(1, user.getFirstName());   
     ps.setString(2, user.getLastName());   
        
     ps.execute();   
        
     ps.close();   
     conn.close();   
      }  
        
        
      public static List<User> getUsers () throws SQLException{  
           List<User> users = new ArrayList<User>();  
             
           Connection conn = dataSource.getConnection();  
   
           String sqlQuery = "select firstName,lastName from users";  
           PreparedStatement ps = conn.prepareStatement(sqlQuery);  
        
           ResultSet rs = ps.executeQuery();  
             
           while (rs.next()){  
                String firstName = rs.getString("firstName");  
                String lastName = rs.getString("lastName");  
                  
                User user = new User();  
                user.setFirstName(firstName);  
                user.setLastName(lastName);  
                  
                users.add(user);  
           }  
             
           rs.close();  
           ps.close();  
           conn.close();  
             
           return users;  
      }  
        
        
        
 }  
   

There are two methods in this class - one for persisting a user to the database, other for getting a list of users from the database.

The static block initializes the datasource.

Now, update your TestPersistence.java class to use the UserDAO, like this:

 package org.confucius;  
   
   
 import java.sql.SQLException;  
 import java.util.Iterator;  
 import java.util.List;  
   
 public class TestPersistence {  
   
      public static void main(String[] args) {  
           try {  
                  
                List<User> users = UserDAO.getUsers();  
                  
                for (Iterator<User> iter = users.iterator(); iter.hasNext(); ){  
                     User user = iter.next();  
                     System.out.println("Got user: " + user.getFirstName() + " " + user.getLastName());  
                }  
   
           } catch (SQLException e) {  
                e.printStackTrace();  
           }  
      }  
 }  
   

As you can see, there is no reference to JDBC related code in this class. The UserDAO class has allowed for a clean separation of concerns.

R-click on TestPersistence.java in Eclipse-Navigator view, and select Run As--> Java Application. You will see the users printed to the console.

You can add more methods in the DAO class - for deleting users, getting users which match certain attributes, updating user information, etc.

In addition to providing a clean separation of database interaction, DAOs also provide convenience in refactoring code if your database is changed, or you decide to use another database interaction solution, like hibernate.

JDBC - ResultSet

Results of a SQL query are returned in a 'ResultSet'.

Update your TestPersistence.java to retrieve all the entries in the users table, lke this:

 package org.confucius;  
   
 import java.sql.Connection;  
 import java.sql.PreparedStatement;  
 import java.sql.ResultSet;  
 import java.sql.SQLException;  
   
 import com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource;  
   
 public class TestPersistence {  
   
      public static void main(String[] args) {  
           MysqlConnectionPoolDataSource dataSource = new MysqlConnectionPoolDataSource();            
           dataSource.setUser("confucius");  
           dataSource.setPassword("changeit");  
           dataSource.setServerName("localhost");  
           dataSource.setPort(3306);  
           dataSource.setDatabaseName("confuciusDB");  
             
           try {  
                Connection conn = dataSource.getConnection();  
   
                String sqlQuery = "select firstName,lastName from users";  
                PreparedStatement ps = conn.prepareStatement(sqlQuery);  
             
                ResultSet rs = ps.executeQuery();  
                  
                while (rs.next()){  
                     String firstName = rs.getString("firstName");  
                     String lastName = rs.getString("lastName");  
                     System.out.println("Got User: " + firstName + " " + lastName);  
                }  
                  
                rs.close();  
                ps.close();  
                conn.close();  
   
           } catch (SQLException e) {  
                e.printStackTrace();  
           }  
      }  
 }  
   

Executing the query returns a ResultSet.

We iterate over the ResultSet to get the query results.

R-click on the TestPersistence.java file in your Eclipse Navigator view.
Select Run As --> Java Application.

You will see the users printed to the console.

JDBC - Prepared Statements

In the previous post, we executed a SQL query.

A better way to do this is use "PreparedStatement".

PreparedStatements have several advantages:
1. They are precompiled, so execute faster.
2. They can be reused.
3. They reduce chances of data corruption by SQL injection - a way for a hacker to supply malicious input strings which distort the SQL query.

Let us rewrite TestPersistence.java to use PreparedStatement, like this:

 package org.confucius;  
   
   
 import java.sql.Connection;  
 import java.sql.PreparedStatement;  
 import java.sql.SQLException;  
   
 import com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource;  
   
 public class TestPersistence {  
   
      public static void main(String[] args) {  
           User user = new User();  
           user.setFirstName("Gina");  
           user.setLastName("Coles");  
             
           // Persist  
           MysqlConnectionPoolDataSource dataSource = new MysqlConnectionPoolDataSource();            
           dataSource.setUser("confucius");  
           dataSource.setPassword("changeit");  
           dataSource.setServerName("localhost");  
           dataSource.setPort(3306);  
           dataSource.setDatabaseName("confuciusDB");  
             
           try {  
                Connection conn = dataSource.getConnection();  
   
                String sqlQuery = "insert into users value(?, ?)";  
                PreparedStatement ps = conn.prepareStatement(sqlQuery);  
             
                ps.setString(1, user.getFirstName());  
                ps.setString(2, user.getLastName());  
                  
                ps.execute();  
                  
                ps.close();  
                conn.close();  
   
           } catch (SQLException e) {  
                e.printStackTrace();  
           }  
      }  
 }  
   

Here we have loaded the SQL query into the PreparedStatement, then supplied it parameters. The "?" marks in the SQL query string will get replaced by the parameter values.

R-click on TestPersistence.java in Eclipse Navigator, the select Run As --> Java Application.

Now if you look at the users table in mysql, you will see the new user persisted.

 mysql> select * from users;  
 +-----------+----------+  
 | firstName | lastName |  
 +-----------+----------+  
 | John   | Doe   |  
 | Gina   | Coles  |  
 +-----------+----------+  
 2 rows in set (0.00 sec)  
   

Persistence with JDBC

We will use the JDBC API to persist our User objects to the database.

First we need the MySQL JDBC adapter jar.

Update your ivy.xml to get the mysql-connector-java.jar, like this:

 <dependency org="mysql" name="mysql-connector-java" rev="5.1.18"/>  

Update your Eclipse classpath to include this jar.

We will create a Test class to demonstrate persistence with JDBC.

In your /src/org/confucius folder, create a class TestPersistence.java, like this:

 package org.confucius;  
   
   
 import java.sql.Connection;  
 import java.sql.SQLException;  
 import java.sql.Statement;  
   
 import com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource;  
   
 public class TestPersistence {  
   
      public static void main(String[] args) {  
           User user = new User();  
           user.setFirstName("John");  
           user.setLastName("Doe");  
             
           // Persist  
           MysqlConnectionPoolDataSource dataSource = new MysqlConnectionPoolDataSource();            
           dataSource.setUser("confucius");  
           dataSource.setPassword("changeit");  
           dataSource.setServerName("localhost");  
           dataSource.setPort(3306);  
           dataSource.setDatabaseName("confuciusDB");  
             
           try {  
                Connection conn = dataSource.getConnection();  
                Statement stmt = conn.createStatement();  
             
                String sqlQuery = "insert into users value(\"" + user.getFirstName() + "\",\"" + user.getLastName() + "\")";  
                stmt.execute(sqlQuery);  
                  
                stmt.close();  
                conn.close();  
   
           } catch (SQLException e) {  
                e.printStackTrace();  
           }                            
      }     
 }  
   

We create a DataSource, get a connection to the datasource, then execute a SQL query.

R-click on TestPersistence.java in Eclipse Navigator, then select Run As --> Java Application.

Then if you look at the users table in mysql, you will see the user persisted.

 mysql> select * from users;  
 +-----------+----------+  
 | firstName | lastName |  
 +-----------+----------+  
 | John   | Doe   |  
 +-----------+----------+  
 1 row in set (0.00 sec)  
   

Persistence - Database setup

Let us see how to persist objects to a database.

We will create a User.java class, and persist its instances.

In your /src/org/confucius, create a class User.java, like this:

 package org.confucius;  
   
 public class User {  
      private String firstName;  
      private String lastName;  
        
      public void setFirstName(String firstName) {  
           this.firstName = firstName;  
      }  
        
      public String getFirstName() {  
           return firstName;  
      }  
        
      public void setLastName(String lastName) {  
           this.lastName = lastName;  
      }  
        
      public String getLastName() {  
           return lastName;  
      }  
 }  
   

Let us create a database to persist this class to.

Open your MySql command line client, and give the following command to create the database 'confuciusDB':

 mysql> create database confuciusDB;  
 Query OK, 1 row affected (0.00 sec)  

Create a table 'users' using the following commands:

 mysql> use confuciusDB;  
 Database changed  
   
 mysql> create table users (firstName VARCHAR(32), lastName VARCHAR(32));  
 Query OK, 0 rows affected (0.10 sec)  

Create a user 'confucius' with password 'changeit', and grant all permissions.

 mysql> create user 'confucius'@'localhost' IDENTIFIED BY 'changeit';  
 Query OK, 0 rows affected (0.06 sec)  
   
 mysql> grant all privileges on *.* to 'confucius'@'localhost';  
 Query OK, 0 rows affected (0.00 sec)  

We are all set to persist User instances.