Database Integration with Java

The other week I saw a presentation on InfoQ regarding Spring Data, so I thought I’d have a look at how Java connects to databases.

JDBC

The most basic way of connecting to a database in Java is to use JDBC (Java Database Connectivity). This involves creating suitable objects, establishing a connection to the database, executing a SQL statement and parsing the results:

try {
      Connection connection = null;
      ResultSet resultset = null;
      try {
        Class.forName("net.sourceforge.jtds.jdbc.Driver").newInstance();
        connection = DriverManager.getConnection("jdbc:jtds:sqlserver://localhost:1433/A_DB",
                                                 "aaron",
                                                 "********");
        Statement statement = connection.createStatement();
        resultset = statement.executeQuery("SELECT "
                                           + "firstName, "
                                           + "lastName, "
                                           + "gender "
                                           + "FROM person");
        Person person;
        while (resultset.next()) {
          person = new Person();
          person.setFirstName(resultset.getString("firstName"));
          person.setLastName(resultset.getString("lastName"));
          person.setGender(resultset.getString("gender"));
          System.out.println(person);
        }

      } finally {
        if (resultset != null) {
          resultset.close();
        }
        if (connection != null) {
          connection.close();
        }
      }
    } catch (ClassNotFoundException ex) {
      Logger.getLogger(ConnectingUsingJDBC.class.getName()).log(Level.SEVERE, null, ex);
    } catch (InstantiationException ex) {
      Logger.getLogger(ConnectingUsingJDBC.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IllegalAccessException ex) {
      Logger.getLogger(ConnectingUsingJDBC.class.getName()).log(Level.SEVERE, null, ex);
    } catch (SQLException ex) {
      Logger.getLogger(ConnectingUsingJDBC.class.getName()).log(Level.SEVERE, null, ex);
    }

Legacy systems contain a lot of code just like this, or variations of. As you can see, there’s a lot of “boilerplate” code: connecting to the database; closing the connections and cleaning up afterwards; mapping the fields in the recordset to the attributes of the class, etc. However, it is standard with the JDK, which means you don’t need any third part library. You can also remove some of the boilerplate using the latest features such as multiple catches and try-with-resources:

try {
      Connection connection = null;
      ResultSet resultset = null
      Class.forName("net.sourceforge.jtds.jdbc.Driver").newInstance();
      try(connection = DriverManager.getConnection("jdbc:jtds:sqlserver://localhost:1433/A_DB",
                                                 "aaron",
                                                 "********");
        Statement statement = connection.createStatement();
        resultset = statement.executeQuery("SELECT "
                                           + "firstName, "
                                           + "lastName, "
                                           + "gender "
                                           + "FROM person")){
        Person person;
        while (resultset.next()) {
          person = new Person();
          person.setFirstName(resultset.getString("firstName"));
          person.setLastName(resultset.getString("lastName"));
          person.setGender(resultset.getString("gender"));
          System.out.println(person);
        }

      } finally {
        if (resultset != null) {
          resultset.close();
        }
        if (connection != null) {
          connection.close();
        }
      }
    } catch (InstantiationException | IllegalAccessException | SQLException | ClassNotFoundException ex) {
      Logger.getLogger(ConnectingUsingJDBC.class.getName()).log(Level.SEVERE, null, ex);
    }

Try-with-resources saves having to tidy up as it closes the resources for you. The multiple catch gain saves you some lines, but only if you do the same thing for every exception. This is now 34 lines as opposed to 53, but you still get the feeling that there’s an easier way to do this. And there is…

Hibernate

Look up the word “Object Relational Mapping” on Wikipedia and you get this:

“Object-relational mapping (ORM) is a programming technique for converting data between incompatible type systems in object-oriented programming languages. This creates, in effect, a “virtual object database” that can be used from within the programming language.”

In the list of ORM software for Java you’ll find a reference to Hibernate.

Essentially, what Hibernate does is map objects onto the result set fields, reducing the code even further, or, really, moving it somewhere else:

    Configuration config = new Configuration();
    config.configure();
    Session session = config.buildSessionFactory().openSession();

    Transaction tx = session.beginTransaction();
    try {
      List result = session.createQuery("from person").list();

      for (Person person : (List<Person>) result) {
        System.out.println(person);
      }
    } finally {
      tx.commit();
      session.close();
    }

The connection details are moved to an XML configuration file that, by default, is named hibernate.cfg.xml and contained in the root resources folder:

<?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">net.sourceforge.jtds.jdbc.Driver</property>
    <property name="hibernate.connection.url">jdbc:jtds:sqlserver://localhost:1433/A_DB</property>
    <property name="hibernate.connection.username">aaron</property>
    <property name="hibernate.connection.password">********</property>
    <mapping resource="jobtype.hbm.xml"/>
  </session-factory>
</hibernate-configuration>

Notice that it refers to a “mapping resource”, in this case another XML file called “jobtype.hbm.xml”. This is where the details of the mapping are kept and it’s in the root folder again:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
  <class name="org.laughing.lemon.Person" table="person">
    <id column="ID" name="ID" type="int">
      <generator class="assigned"/>
    </id>
    <property column="firstName" name="firstName" type="string"/>
    <property column="lastName" name="lastName" type="string"/>
    <property column="gender" name="gender" type="string"/>
  </class>
</hibernate-mapping>

Notice that it’s mapped the class name to the table and the column names to the attribute names of the Person class. This, in turn, has to extend the Java Serializable interface:

import java.io.Serializable;

public class Person implements Serializable {

  private int ID;
  private String firstName;
  private String lastName;
  private String gender;

You still have to do the work (you can, in theory, leave the mapping if the column names and attribute names are the same and it will work out the mappings automatically, but better safe…), but it is now done in configuration files rather the Java. You also have to introduce some transaction handling for the first time, which was done automatically for you in the JDBC.

The problem with this is that it’s not a standard. There’s still a fair bit of messing around if I wanted to switch to different ORM software. To impose some kind of standardisation, Java now has JPA, the Java Persistence API.

JPA

This is not a library or framework of any kind, but a standard or specification that ORM’s now adhere to, usually by implementing a plug-in or extension, which is what Hibernate does. Thus Hibernate JPA:

    EntityManagerFactory entityManagerFactory =
          Persistence.createEntityManagerFactory("org.laughing.lemon");
    EntityManager entityManager = entityManagerFactory.createEntityManager();
    
    List<person> result = 
         entityManager.createQuery("from org.laughing.lemon.Person", Person.class).getResultList();
    for (Person person : result) {
      System.out.println(person);
    }

    entityManager.close();

Very similar to the Hibernate implementation and, like Hibernate, it takes the connection information from an XML file, in this case “persistence.xml”:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
  <persistence-unit name="org.laughing.lemon" transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
    <class>org.laughing.lemon.Person</class>
    <properties>
      <property name="javax.persistence.jdbc.url" value="jdbc:jtds:sqlserver://localhost:1433/A_DB"/>
      <property name="javax.persistence.jdbc.password" value="********"/>
      <property name="javax.persistence.jdbc.driver" value="net.sourceforge.jtds.jdbc.Driver"/>
      <property name="javax.persistence.jdbc.user" value="aaron"/>
    </properties>
  </persistence-unit>
</persistence>

Notice that the persistence unit has a name that then gets used in the Java application. The mapping is done in the POJO class:

import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="person")
public class Person implements Serializable {

  @Id
  @Column(name="id")
  private int ID;
  @Column(name="firstname")
  private String firstName;
  @Column(name="lastname")
  private String lastName;
  @Column(name="gender")
  private String gender;

Conclusion

So far, I’ve shown three seperate ways that a Java application can connect to a database. JDBC is the most direct and basic, with Hibernate and JPA abstracting the database table to Java objects.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s