A guide to accessing databases in Java
Tags: java, database, jdbc, hibernate
Published: 2019-11-18 09:01:13

You can use this guide to discover, understand, and select the right Java library to access a database, like MySQL, Postgres, Oracle - or any other one.

Read this guide to understand which database library makes sense for your project and development team.

## Introduction

Whenever you want to connect to a database with your Java (server/desktop) application three questions pop up:

-   Are you approaching your application from a Java-first or database-first perspective? Which of the two do you want to write first, or already exists?

-   How do you execute SQL Statements, from small CRUD operations (like a good old *insert into*) to complex SQL reporting queries (analytics functions)?

-   How easily can you go from (read: *map between*) Java object to database table? From database table to Java object?

So imagine a Java class like this:

```java
    public class User {

        private Integer id;

        private String firstName;

        private String lastName;

        // Constructor/Getters/Setters....

    }
```


Now imagine you have, say, three of these users and want to save them to a database table ("*users*") that looks like this:

<table>
<caption>Users</caption>
<colgroup>
<col width="33%" />
<col width="33%" />
<col width="33%" />
</colgroup>
<tbody>
<tr class="odd">
<td><p><strong>id</strong></p></td>
<td><p><strong>first_name</strong></p></td>
<td><p><strong>last_name</strong></p></td>
</tr>
<tr class="even">
<td><p>1</p></td>
<td><p>hansi</p></td>
<td><p>huber</p></td>
</tr>
<tr class="odd">
<td><p>2</p></td>
<td><p>max</p></td>
<td><p>mutzke</p></td>
</tr>
<tr class="even">
<td><p>3</p></td>
<td><p>donald</p></td>
<td><p>trump</p></td>
</tr>
</tbody>
</table>

It turns out there are various ways to do that in Java-land, one might even say too many. Hence, let’s get out the machete and cut through the wood of various options available to you.

As always, however, it is very important to understand the basics, as every other option builds ON TOP of those basics. So make sure not to skip the very next section, if you are unsure about some of the mentioned concepts.

## Basic Java Database Access

### JDBC Drivers

To connect to your database, be it [MySQL](https://www.mysql.com/) or [H2](http://www.h2database.com/html/main.html), you need a driver, a *JDBC driver*.

JDBC drivers do a fair amount of work, from the basics of opening up connections to the database
to more advanced features like offering abilities to receive events from the database (Oracle).

So no matter what Java database library you are going to use in your project, you’ll need to go to the [database’s website](https://dev.mysql.com/downloads/connector/j/) or a [maven repository](http://mvnrepository.com/artifact/mysql/mysql-connector-java), fetch the latest JDBC driver and add it to your project.

That’s literally all you need to get started. In the case of you wanting to connect to a MySQL database "test" that is installed on your local machine, you would then write the following code:


**(Parental Advice: Don’t just blindly copy this code).**

```java
    try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/test")) {
        assertTrue(conn.isValid(1000));
        // Do something with the Connection
    }
```

### Connection Pools

Opening up and closing database connections takes some time. Especially in web applications you do not want to open up a fresh database connection for every user, rather you want to have a [small pool of connections](https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing)
that are always open and that are shared between users. That’s what connection pools are for.

Some JDBC drivers, like the Oracle one, come with their own connection pool ([UCP](https://docs.oracle.com/database/121/JJUCP/intro.htm#JJUCP8109)). For other databases you need to download a connection pool yourself, and again you are hammered with a plethora of options in Java land.

The ones which you will find in a lot of (older) applications are [Apache Commons DBCP](https://commons.apache.org/proper/commons-dbcp/) and [C3P0](https://www.mchange.com/projects/c3p0/).

Unfortunately, you will also often find them misconfigured, because they lack proper, sane defaults and they also have trouble with performance and handling error cases (see e.g. [database down](https://github.com/brettwooldridge/HikariCP/wiki/Bad-Behavior:-Handling-Database-Down)).

In general, I would therefore recommend new projects to use one of these two, newer connection pools:

[HikariCP](https://github.com/brettwooldridge/HikariCP) or [Vibur-dbcp](http://www.vibur.org/) . They are both rock solid, performant offer sane defaults and other nice convenience features. Additionally, for maximum performance, make sure to consult the [database-specific documentation](https://github.com/brettwooldridge/HikariCP/wiki/MySQL-Configuration) for either one of these two.

Independent from which of the options you choose, you will then, in your code, not open up connections yourself through the DriverManager, but rather you will ask the connection pool (DataSource) to give you one of its connections, like so:


**(Parental Advice: Don’t just blindly copy this code).**

```java
    // in the case of using Hikari Connection Pool
    DataSource ds = new HikariDataSource(config);
    try (Connection connection = ds.getConnection()) {
        // use the connection
    }
```

### Low Level Database Access (JDBC)

The most low-level way to accessing databases in Java, is through the JDBC API ([Java Database Connectivity](https://docs.oracle.com/javase/tutorial/jdbc/basics/index.html)). JDBC comes with every JDK and you don’t need any additional libraries to use it. And it should also be noted that *every* other Java database library builds on TOP of JDBC.

#### Getting Started

Once you have an open database connection (see above) you can execute SQL statements through that connection, like you would with, say, the MySQL console, or Oracle’s SQLDeveloper.

But plain JDBC is a bit cumbersome to use and doesn’t help you much
converting from Java Objects to said SQL. In fact, all of that is left to do yourself. Let’s have a look at what a basic SQL Insert statement with plain JDBC looks like:

**(Parental Advice: Don’t just blindly copy this code).**

```java
    public void insertWithJdbc() {
        User user = ... ;
        DataSource ds = ...;

        try (Connection conn = ds.getConnection()) {

            conn.setAutoCommit(false); // starts a new database transaction
            PreparedStatement statement = conn.prepareStatement("insert into users (first_name, last_name) values (?,?)");
            statement.setString(1, user.getFirstName());
            statement.setString(2, user.getLastName());
            statement.executeUpdate();

            // TODO insert some more users...
            conn.commit(); // commits the database transaction

        } catch (SQLException e) {
            // TODO print out error
            // TODO rollback transaction
        }
    }
```

As you can see, what you need to do is:

-   Open up a database connection

-   Start a database transaction

-   Create a PreparedStatement, which represents your SQL insert and has some placeholders

-   Fill those placeholders (repeatedly)

-   Finally, commit the transaction

#### Error handling & Resource Cleanup

But that’s only half of the story. Whenever a SQLException happens, you know that something is wrong, but you do not know what was or what wasn’t committed. And because you don’t know, you need to make sure to manually rollback the transaction.

Additionally, because you are a good developer, you also should make sure to close the PreparedStatement and/or connection explicitly, instead of hoping for it to be closed automatically (JDBC driver implementation dependent) later on in your code - and release any database resources along with that.

Fortunately that got a lot easier with the introduction of Java 7’s [try-with-resources](https://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html).

Experiment a bit yourself with rolling back the connection using the code example above. Also experiment with closing the connection and statements. If you are a bit lost, read through the corresponding [Oracle Documentation](https://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html).

See what the above example in 'proper' Java code would look like:


**(Parental Advice: Don’t just blindly copy this code).**

```java
    public void insertWithJDBCSafelyUpToJava7() {
        User user = new User();

        Connection conn = null;
        PreparedStatement statement = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost/test");
            conn.setAutoCommit(false); // starts a new database transaction
            statement = conn.prepareStatement("insert into users (first_name, last_name) values (?,?)");
            statement.setString(1, user.getFirstName());
            statement.setString(2, user.getLastName());
            statement.executeUpdate();
            conn.commit(); // commits the database transaction
        } catch (SQLException e) {
            // TODO do some application error handling here
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    // nothing you can really do here, apart from logging the error
                }
            }
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    // nothing you can really do here, apart from logging the error
                }
            }

            if (conn  != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    // nothing you can really do here, apart from logging the error
                }
            }
        }
    }


    public void insertWithJDBCSafelyFromJava7Onwards() {
      User user = new User();
      try (Connection cxn = DriverManager.getConnection("jdbc:mysql://localhost/test");
          PreparedStatement stmt = cxn.prepareStatement("insert into users (first_name, last_name) values (?,?)")) {
        try {
          cxn.setAutoCommit(false); // starts a new database transaction
          stmt.setString(1, user.getFirstName());
          stmt.setString(2, user.getLastName());
          stmt.executeUpdate();
          cxn.commit(); // commits the database transaction
        } catch (SQLException e) {
          // TODO do some application error handling here
          try {
            cxn.rollback();
          } catch (SQLException ex) {
            // nothing you can really do here, apart from logging the error
          }
        }
      }
    }
```

As you can see, starting with Java 7, the auto-closing of connections and statements makes cleanup a lot easier. If you have the misfortune of being on Android or Java 6 or below, you can use [Guava’s Closer](https://github.com/google/guava) to achieve code almost as clean with regard to resource management.

We haven’t talked about selecting data from tables, yet. Let’s have a look at that:

**(Parental Advice: Don’t just blindly copy this code).**

```java
    public void selectWithJdbc() {

        // check out the other examples above to understand where the statement variable comes from
        ResultSet resultSet = statement.executeQuery("select * from users");

        while (resultSet.next()) {
            String firstName = rs.getString("first_name");
            String lastName = rs.getString("last_name");

            System.out.println("first name : " + firstName);
            System.out.println("last name : " + lastName);
        }

    }
```

So that main takeaway is, you are basically working with bare metal when using JDBC.

You have the full power of SQL at your hand, but you need to make sure to somehow convert back and forth between your Java Objects and the SQL code yourself. Also, you need to make sure to properly maintain and close your database connections/statements.

That’s where convenience libraries like Spring (with its [JDBC Template](https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/core/JdbcTemplate.html)) come in and we will have a look at that in a later section.

Other than that, using JDBC directly is a perfectly valid use-case and especially beginners should get familiar with JDBC concepts, before diving right into heavier libraries like Hibernate.

#### If you want to practice Java database basics

I have written a short book, full with exercises and code-examples on how to make JDBC access as simple as possible. Check out [Java Database Connections & Transactions](https://www.marcobehler.com/books/1-java-database-connections-transactions?utm_campaign=database_guide&utm_medium=database_guide&utm_source=database_guide).

## Java First Libraries

Java developers are usually more comfortable with writing Java classes, than with writing SQL. Hence many (greenfield) projects are written with a Java-First approach, which means that you create your Java class before you create the corresponding
database table.

Then, you are left with the question: How do you map from your Java classes to said tables? One solution is to use so called ORMs, libraries for object-relational mapping.

### Hibernate

One very popular library that helps you do this, is [Hibernate](https://github.com/hibernate/hibernate-orm), which has been around since 2001. Let’s dive right in (and in case you enjoy videos, have a look at these [short and practical screencasts](https://www.marcobehler.com/courses/2-hibernate-jpa-getting-started), which illustrate all following concepts in a real-life scenario):

Imagine, you have the following table, which is basically the same table you used in the [plain JDBC section](#plain-jdbc).

```sql
    create table users (
        id integer not null,
        first_name varchar(255),
        last_name varchar(255),
        primary key (id)
    )
```

and the following corresponding Java class:

```java
    public class User {

        private Integer id;

        private String firstName;

        private String lastName;

        //Getters and setters are omitted for brevity
    }
```

Now, to get started, you obviously need to add the Hibernate libraries to your project. Then create a Hibernate specific configuration file [hibernate.cfg.xml](http://docs.jboss.org/hibernate/orm/5.0/userguide/html_single/Hibernate_User_Guide.html) to create a so-called *SessionFactory*, or create that SessionFactory directly in your Java code.

#### What’s a SessionFactory? Why would I need one?

A SessionFactory basically represents the mapping between your Java classes and your database tables (more on that in a second). In addition, a SessionFactory produces, who would have thought, Sessions. A session is basically a database connection (or more specifically a wrapper of the JDBC connection), with additional goodies on top.

But first, let’s look at the mapping. Hibernate obviously has no way of knowing which of your classes should be mapped how to database tables out of the box. Hence you need a mapping. Traditionally, you did the mapping in .xml files, but recently a lot of projects
switch to annotation style mappings. You’re going to learn about annotations in this guide, but if you want to have a look at the legacy XML mappings, have a look at [this link](https://docs.jboss.org/hibernate/orm/3.5/reference/en/html/mapping.html) for example.

Let’s say you are configuring Hibernate through plain Java, without a .cfg.xml file, then you’d have some code like this:

**(Parental Advice: Don’t just blindly copy this code).**

```java
    // Hibernate specific configuration class
    MetadataSources sources = new MetadataSources( standardRegistry );
    // add class using JPA/Hibernate annotations for mapping
    sources.addAnnotatedClass( User.class );
```

That let’s Hibernate know you have one User.class, which has annotations (most importantly, the @Entity annotation) that tell Hibernate HOW to map that class automatically to an appropriate database table.

Let’s have a look at the annotated User class.

**(Parental Advice: Don’t just blindly copy this code).**

```java
    @Entity
    @Table(name="users")
    public static class User {

        @Id
        private Integer id;

        @Column(name="first_name")
        private String firstName;

        @Column(name="last_name")
        private String lastName;

        //Getters and setters are omitted for brevity
    }
```

A lot could be said about the individual annotations, which is outside of the scope of this guide. Rather, we’ll focus on the big picture here.

#### Basic CRUD with Hibernate

Now that you have the mapping set up, the only thing left to do is to get a session (read: database connection) from your sessionFactory and save the user to the database. That’s right, you won’t write any SQL for that, Hibernate will do that for you:

**(Parental Advice: Don’t just blindly copy this code).**

```java
    Session session = sessionFactory.openSession();
    User user = new User();
    user.setFirstName("Hans");
    user.setLastName("Dampf");
    // this line will generate and execute the "insert into users" sql for you!
    session.save( user );
```

Similarly, there’s other methods that allow you to execute other SQL statements (like select, update, delete) without writing any SQL and without you having to map from ResultSets (like in the JDBC example) to your Java class, Hibernate will do that for you.

**(Parental Advice: Don’t just blindly copy this code).**

```java
    // "select from users where id = 1"
    User user = session.get( User.class, 1 );

    // "update users set...where id=1"
    session.update(user);

    // "delete from useres where id=1"
    session.delete(user);
```

#### HQL

There are of course times when you need more control and want to write your own SQL statements, especially for more complex queries than "select where id = 1".

Hibernate offers a query language, called HQL. HQL looks similar to SQL, but is focused on objects and actually SQL dialect independent. So, the same HQL statement works for all databases (with the drawback, that you lose database specific query capabilities).

Let’s see what that looks like:

**(Parental Advice: Don’t just blindly copy this code).**

```java
    List<User> users = session.createQuery("select from User u where u.firstName = 'hans'", User.class).list();

    session.createQuery("update User u set u.lastName = :newName where u.lastName = :oldName")
                .executeUpdate();
```

Note that in both examples, you access Java properties (u.lastName) in the query string, not sql columns. Hibernate will then convert these HQL statements to proper, database specific SQL statements.

#### Criteria

In addition to HQL, Hibernate offers another query language through the Criteria API, a type-safe, programmatic API. However, it is a bit trickier to setup, as your project needs an annotation processing plugin to generate a "Static Metamodel" of your annotated classes and the API itself also feels a bit cumbersome to use.

Also, there’s two versions of the Criteria API (1 and 2) and a fair amount of developers find version 1 actually easier to use than version 2, though it is being deprecated and will likely be completely inaccessible in the future.

In any case, let’s have a look at the HQL select query from above, in criteria form, which Hibernate will convert to a proper, database specific SQL statement.

**(Parental Advice: Don’t just blindly copy this code).**

```java
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<User> criteria = builder.createQuery( User.class );
    Root<User> root = criteria.from( User.class );
    criteria.select( root );
    criteria.where( builder.equal( root.get( User_.firstName ), "hans" ) );
    List<User> users = entityManager.createQuery( criteria ).getResultList();
```

As you can see, you basically trade in readability and simplicity for type-safety. Six Lines of code, for a simple "select \* from users where firstName = ?"

#### The Dark Side

Hibernate doesn’t just offer simple mapping and querying features. Of course, the mappings and queries will be much more complex in real-life scenarios, than the ones you found above. Additionally, it offers a whole range of other 'convenience' features, like cascading, lazy loading, caching and much more.

It truly is a complex piece of software, that you cannot fully grasp by just copy-and-pasting some online code tutorials. That will, sooner or later, lead to you claiming "Hibernate is doing random magic, that nobody understands" or a variant thereof. Because you lack the background knowledge of what Hibernate is actually doing.

In addition, working with Hibernate does not mean that you do not have to care about SQL statements anymore. The more complex the software gets, the more important it is to check the SQL statements that Hibernate generates, or write your own, optimized SQL/HQL/Criteria statements.

Which in turn means, to use Hibernate efficiently, you need good knowledge of Hibernate **and** SQL.

#### Recommended Reading & Watching

A good antidote for that is to read the [Java Persistence with Hibernate](https://www.manning.com/books/java-persistence-with-hibernate-second-edition) book and work through its examples. It has 608 pages, which already shows the complexity of it all, but your skills will benefit greatly from reading it.

If you want more advanced information on Hibernate, make sure to check out [Vlad Mihalcea’s site](https://vladmihalcea.com/). Vlad is truly an expert on Hibernate matters, not only runs a blog on Hibernate, but also offers books, videos etc.

You can also find [Hibernate specific screencasts](https://www.marcobehler.com/courses) on this site, which give you a smooth and time-saving quickstart into the Hibernate universe.

### JPA

#### History & Overview

If you have heard of Hibernate, chances are you have also heard of JPA, the Java Persistence API. But what is JPA? How does it compare to a library like Hibernate?

First ofo, JPA is merely a specification, not an implementation or library (In 2006, the JPA 1.0 *specification* was released, and the latest one, is JPA 2.2 from 2017). So JPA defines a standard, that a database library needs to implement.

And as you might have guessed, Hibernate isn’t the only Java database library out there. There are other very similar ones like EclipseLink, TopLink, which you will learn more about in the next section.

Now the interesting part is, that Hibernate, EclipseLink and TopLink *all* implement the JPA specification, which
means that - in theory - instead of writing Hibernate specific code, or EclipseLink specific code, you write JPA specific code, add a couple of configuration files and wanted database
libraries (also called: persistence provider) to your project and you can access your database.

#### Code

Let’s have a look at some code. Remember from the section above, that your Hibernate entry point is a SessionFactory, which creates Sessions. In JPA, these two are called EntityManagerFactory and EntityManager.

**(Parental Advice: Don’t just blindly copy this code).**

```java
    EntityManagerFactory factory = Persistence.createEntityManagerFactory( "org.hibernate.tutorial.jpa" );

    EntityManager entityManager = factory.createEntityManager();
    entityManager.getTransaction().begin();
    entityManager.persist( new Event( "Our very first event!", new Date() ) );
    entityManager.persist( new Event( "A follow up event", new Date() ) );
    entityManager.getTransaction().commit();
    entityManager.close();
```

If you look closer at the Hibernate source code, you will see the following:

```java
    package org.hibernate;

    public interface Session extends SharedSessionContract, EntityManager, HibernateEntityManager, AutoCloseable {
      // methods
    }

    // and

    public interface SessionFactory extends EntityManagerFactory, HibernateEntityManagerFactory, Referenceable, Serializable, java.io.Closeable {
        // methods
    }
```

So, a Hibernate Session *IS* an EntityManager. A SessionFactory *IS* an EntityManagerFactory. Certain methods like Hibernate’s "save" or "update" are now called "persist" in JPA, but that’s about it.

#### JPQL, Criteria etc.

Having EntityManagers is only half of the story. JPA also comes with its own query language, the JPQL (Java Persistence Query Language), which is *heavily* influenced, or rather, based on HQL, the Hibernate Query language.

The same goes for the Criteria 2.0 API, which is actually a JPA API, not a Hibernate-only one (like the Criteria 1.0 API).

So your JPQL or Criteria queries would work with all JPA implementations, be it Hibernate, EclipseLink or TopLink.

#### Drawbacks

In theory, JPA allows you to disregard what persistence provider library you are using. That only goes so far however.

As the JPA specification is a common denominator between all the libraries, the features it offers are necessarily only a *subset* of e.g. all the features that Hibernate offers. Otherwise every database library would be exactly the same.

Additionally, the specification process always takes time, so it lacks behind the features that implementations already might offer.

And in practice, there is rarely (read never) a need to switch persistence providers once an application reaches production. It is simply not a use-case to switch out Hibernate with EclipseLink in production, because one performs better than the other.

Finally, this will lead to new projects adopting one of the two stances:

-   Try to use JPA as much as possible, sprinkled in with Hibernate specific features, where the JPA specification is missing certain features

-   Or use plain Hibernate all the way.

Both ways are fine (and you could also swap out Hibernate with EclipseLink etc. here)

### EclipseLink, TopLink et al

If you have read that far, you already now that there’s more libraries like Hibernate out there. Two primarily come to mind: [EclipseLink](http://www.eclipse.org/eclipselink/) and the (older) [TopLink](http://www.oracle.com/technetwork/middleware/toplink/overview/index.html).

They lack behind in market-share compared to Hibernate, but you’ll also find projects using them in corporate settings.

There are also other projects like [BatooJPA](https://github.com/BatooOrg/BatooJPA), but you will find in most cases that these libraries are abandoned and not maintained anymore, because it is quite some effort to maintain a fully JPA compliant library.

You most certainly need an active community to support further development of your library and Hibernate probably has the most active community as of 2018.

### Summary

JPA implementations are mature and complex piecse of software.

The major caveat is to think one does not need to understand SQL in order to work with any of the JPA implementations. They offer you a fast start when trying to map basic classes to database tables.
But coupled with a lack of basic knowledge about how the ORMs work, it’s often a recipe for disaster later on in your project, when things get difficult.

***Main Takeaway***

Make sure you get a good foundation on how e.g. Hibernate works **AND** how SQL and your database works. Then you are going to be on the right way.

## Database First Libraries

Contrary to the Java-First approach, the main focus here is the database.

Doesn’t matter if you already have an existing (legacy) database or start a new project from scratch, where you would design and write the database schema BEFORE writing the corresponding Java classes.

### JooQ

[JooQ](https://www.jooq.org) is a popular library from Lukas Eder, and it is very well maintained. In addition, Lukas runs a very informative [blog](https://blog.jooq.org/)on everything centering around databases and Java.

By default, you start with your existing database schema and then let jOOQ’s [code generator](https://www.jooq.org/doc/3.11/manual/code-generation/codegen-configuration/) run. The code generator will create a bunch of classes representing the tables in your database, that let you write and execute type-safe SQL queries against those tables.

So it is not as "high-level" as Hibernate and indeed has a much smaller feature-set. But those features it executes amazingly well.

Imagine you setup JooQs code generator and let it run against the [Users](#example-table) table that was introduced at the beginning of this guide. JooQ will generate a User table class, which e.g. allows you to execute the following query against the database:

**(Parental Advice: Don’t just blindly copy this code).**

```java
    // Imagine an additional SUBSCRIPTIONS table exists
    Result<Record3<String, String, String>> result =
    create.select(USERS.FIRST_NAME, USERS.LAST_NAME, SUBSCRIPTIONS.ID)
          .from(USERS)
          .join(SUBSCRIPTIONS)
          .on(USERS.SUBSCRIPTION_ID.eq(SUBSCRIPTIONS.ID))
          .where(USERS.FIRST_NAME.eq("Hans"))
          .fetch();
```

At first, this looks similar to what [QueryDSL](#querydsl) can do and there is a [certain overlap](https://blog.jooq.org/2014/05/29/querydsl-vs-jooq-feature-completeness-vs-now-more-than-ever/).

JooQ however helps you not only build and execute SQL statements against your database schema, it also helps you with CRUD, mapping between Java POJO’s and database records and it also will help you access all of your database’s (vendor specific) features (think window functions, pivots, flashback queries, OLAP, stored procedures, vendor-specific functions etc.)


**(Parental Advice: Don’t just blindly copy this code).**

```java
    // Fetch a user
    UserRecord user : create.fetchOne(USERS, USERS.ID.eq(1));

    // Create a new user, if it doesn't exist yet
    if (user == null) {
        user = create.newRecord(USERS);
        user.setId(1);
        user.setFirstName("Hans");
        user.setLastName("Dampf");
    }

    // Mark the user as a hero and store him
    user.setActionHero(true);

    // Executes an update on existing users, or insert on new ones
    user.store();
```

Lastly, JooQ can, as is the case for most of the following database libraries, be mixed with a library like Hibernate, wherever it makes sense.

### MyBatis

[MyBatis](http://www.mybatis.org/mybatis-3/getting-started.html) is another popular and actively maintained database-first choice. MyBatis centers around the concept of a SQLSessionFactory (not to be confused with Hibernate’s SessionFactory), which you historically built from an XML file, but nowadays you can also do it with plain Java.

You can then execute SQL statements with that SQLSessionFactory. Those SQL statements either live in XML files, or you annotate interfaces with it.

Let’s see what the annotation example looks like:

**(Parental Advice: Don’t just blindly copy this code).**

```java
    package org.mybatis.example;
    public interface UserMapper {
      @Select("SELECT * FROM users WHERE id = #{id}")
      User selectUser(int id);
    }
```


**(Parental Advice: Don’t just blindly copy this code).**

```xml
    <?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper
      PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
      "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="org.mybatis.example.UserMapper">
      <select id="selectUser" resultType="User">
        select * from users where id = #{id}
      </select>
    </mapper>
```

That interface then allows you to write code like the following:

**(Parental Advice: Don’t just blindly copy this code).**

```java
    UserMapper mapper = session.getMapper(UserMapper.class);
    User user = mapper.selectUser(1);
```

MyBatis also has mapping functionalities built-in, i.e. it can convert from a table to a User object. But only in simple cases, where e.g. the column names match or similar. Otherwise you will have to specify the mappings yourself, in one of the XML configuration files.

Additionally, MyBatis has a pretty strong focus on its [Dynamic SQL capabilties](http://www.mybatis.org/mybatis-3/dynamic-sql.html), which is basically an XML-based way of creating complex, dynamic SQL strings (think if-else-when-oterhwise inside your SQL statements).

## Convenience Libraries

By now you already have met quite a few Java database libraries, from plain JDBC to Hibernate. There’s even more, that qualify as convenience libraries *on top* of whatever library you chose above. Let’s have a look at what that means.

### Spring

Almost a decade ago the Hibernate developers tried to argue, that noone needs to use Spring, certainly not in combination with Hibernate and that plain Hibernate is enough. These days however are long gone.

Let’s see how the [Spring Framework](https://spring.io/) makes database access more convenient, no matter if you are using plain JDBC, Hibernate or JPA.

#### Spring JDBC Template

One of the oldest helper classes in Spring Framework is the JDBCTemplate. It is basically a convenience wrapper for JDBC connections (refer back [to here](#plain-jdbc) for how plain JDBC works).

**(Parental Advice: Don’t just blindly copy this code).**

```java
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

    // ...

    jdbcTemplate.execute("CREATE TABLE users(" +
                "id SERIAL, first_name VARCHAR(255), last_name VARCHAR(255))");

    // ...

    jdbcTemplate.batchUpdate("INSERT INTO users(first_name, last_name) VALUES (?,?)", names);

    // ...

    jdbcTemplate.query(
                "SELECT id, first_name, last_name FROM users WHERE first_name = ?", new Object[] { "Josh" },
                (rs, rowNum) -> new User(rs.getLong("id"), rs.getString("first_name"), rs.getString("last_name"))
        ).forEach(user -> log.info(user.toString()));
```

By looking at the code, you can see that especially when querying the database and mapping to Java objects, the JDBC Template offers more convenience, than using JDBC ResultSets directly. Especially when it comes to handling errors, closing the connection and cleaning things up again, which will be done for you.

#### Spring Transactions

Another major feature of the Spring framework, is that it lets you declare database transactions throughout your code, with the [*@Transactional* annotation](https://docs.spring.io/spring/docs/4.2.x/spring-framework-reference/html/transaction.html).

What it all comes down to, is that instead of opening up transactions yourself and closing them again, Spring will do the transaction handling for you. And the transaction handling also integrates with every other database library out there, from Spring’s own JDBC library to every JPA implementation, to JooQ. So you can use Spring’s transaction handling *together* with those libraries.

Let’s look at the JPA example from before:

**(Parental Advice: Don’t just blindly copy this code).**

```java
    EntityManagerFactory factory = Persistence.createEntityManagerFactory( "org.hibernate.tutorial.jpa" );

    EntityManager entityManager = factory.createEntityManager();
    entityManager.getTransaction().begin();
    entityManager.persist( new Event( "Our very first event!", new Date() ) );
    entityManager.persist( new Event( "A follow up event", new Date() ) );
    entityManager.getTransaction().commit();
    entityManager.close();
```

After "Springifying", that code becomes:

**(Parental Advice: Don’t just blindly copy this code).**
```java
    @PersistenceContext
    private EntityManager entityManager;

    @Transactional
    public void doSomeBusinessLogic() {
        entityManager.persist( new Event( "Our very first event!", new Date() ) );
        entityManager.persist( new Event( "A follow up event", new Date() ) );
    }
```

There is obviously more to it than that, but if you want to brush up your database transaction knowledge and get a solid understanding of all the edge cases, you will find a ton of practical code examples [in this book](https://www.marcobehler.com/make-it-so-java-db-connections-and-transactions-html/) here.

#### Spring Data JPA

One of the newer libraries, [Spring Data JPA](https://projects.spring.io/spring-data-jpa/) will make it easier for you to implement JPA based repositories or DAOs. What does that mean?

A common pattern when programming database applications is to have a repository/dao class for every domain object. So you have your User class, and your UserRepository class. The UserRepository class allows you to execute all common crud (create-read-update-delete) database operations on the User.

And with Spring Data JPA it is enough to annotate your User class with JPA annotations and create an empty repository class that you extend from one of the Spring Data classes, and you get all basic CRUD operations for free, without you having to code anything:

**(Parental Advice: Don’t just blindly copy this code).**

```java
    public interface SimpleUserRepository extends JpaRepository <User, Long> {

        // JpaRepository contains all of these methods

        List<T> findAll();

        List<T> findAll(Sort sort);

        List<T> findAllById(Iterable<ID> ids);

        <S extends T> List<S> saveAll(Iterable<S> entities);

        // and many more...that you can execute without implementing, because Spring Data JPA will
        // automatically generate an implementation for you - at runtime
    }
```

### QueryDSL

When you go through the Hibernate/JPA section of this guide, you might notice something: Constructing complex or dynamic HQL/JPQL strings by hand is not the most type-safe, readable and enjoyable thing to do. And the Criteria API 2.0 is somewhat of a pain to use, where you need six lines of Java Code to generate a simple SQL select.

In comes [QueryDSL](http://www.querydsl.com/), which nowadays can do much more than just pimping your JPA implementation (it also works for NoSQL databases for example). In this guide, however, we will focus on the JPA part of it. Adding it to your Hibernate/JPA project, you’ll then be able to
execute queries like so (compare that with the [Criteria 2.0](#criteria-2.0) query from above):

**(Parental Advice: Don’t just blindly copy this code).**

```java
    QUser user = QUser.user;
    JPAQuery<?> query = new JPAQuery<Void>(entityManager);
    List<User> users = query.select(user)
      .from(user)
      .where(user.firstName.eq("Hans"))
      .fetch();
```

Where does the QUser class come from? QueryDSL will automatically create that for you, during compile time, with an annotation processing compiler plugin. You can then use these classes to execute type-safe queries against the database and they read much nicer than the JPA Criteria equivalent.

## Deciding On The Right (™) Library

By now, you might feel a bit overwhelmed. A ton of different libraries and then even more convenience options on top. But summing everything up, here’s a couple of rough guidelines ( and as you might have guessed, there is no 'one and only right way') :

-   No matter what database library you ultimately decide on, make sure you have a solid understanding of SQL and databases (which Java developers usually haven’t)

-   Choose a library which has a vibrant community, good documentation and regular releases

-   Learn your database libraries inside out, i.e. spend the time to read those 608 JPA pages

-   Your project will be fine with either JPA implementation (Hibernate is a good start)

-   It will also be fine with JooQ or any of the other mentioned database-first libraries

-   You can also combine those libraries, e.g. a JPA implementation and JoOQ or plain JDBC - or add more convenience with things like QueryDSL

That’s it for today. If you have any questions or if you found some spelling errors just post them to the comment section or e-mail me.

***Thanks for reading***

## Acknowledgements

A big thanks goes out to the following readers for their detailed feedback & corrections to this guide: [parms](https://www.reddit.com/user/parms), [lukaseder](https://www.reddit.com/user/lukaseder), [Stew Ashton](https://twitter.com/StewAshton).