5 Things You Need to Know When Using Hibernate with Mysql

---

Take your skills to the next level!

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.

Join the Persistence Hub!

---

One of the benefits of using JPA and Hibernate is that it provides an abstraction of database-specific dialects and features. So, in theory, you can implement an application, connect it to one of the supported databases and it will run without any code changes.

Hibernate does that really well. But let’s be honest, you didn’t expect that your application will run perfectly with every supported database, did you?

Hibernate handles the most important things. But if you want your application to perform well, you still need to know which database(s) you want to use and adjust your configuration and code accordingly.

In one of the previous posts, I talked about 6 things you need to know if you want to use Hibernate with a PostgreSQL database. And today I want to have a closer look at the MySQL database.

1. Mappings: Primary Keys

The efficient handling and creation of primary keys are a basic but one of the most important parts of an application.

The @GeneratedValue annotation of the JPA specification allows you to define the strategy you want to use to create unique primary key values. You can choose between SEQUENCE, IDENTITY, TABLE, and AUTO.

In general, I recommend using the SEQUENCE strategy because it allows Hibernate to use JDBC batching and other optimization strategies that require the delayed execution of SQL INSERT statements.

But you can’t use this strategy with a MySQL database. It requires a database sequence, and MySQL doesn’t support this feature.

So, you need to choose between IDENTITY and TABLE. That’s an easy decision considering the performance and scalability issues of the TABLE strategy.

If you’re working with a MySQL database, you should always use GenerationType.IDENTITY. It uses an autoincremented database column and is the most efficient approach available. You can do that by annotating your primary key attribute with @GeneratedValue(strategy = GenerationType.IDENTITY).

@Entity
public class Author {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	@Column(name = "id", updatable = false, nullable = false)
	private Long id;

	...
}

2. Mappings: Problems with GenerationType.AUTO in Hibernate 5

When you use the GenerationType.AUTO, Hibernate selects the generation strategy based on the Hibernate dialect. That’s a common approach if you need to support multiple databases.

@Entity
public class Author {

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

	...
}

In older versions, Hibernate selected the GenerationType.IDENTITY for MySQL databases. That was a good choice. As explained earlier, it’s the most efficient approach available.

But that changed in Hibernate 5. It now selects the GenerationType.TABLE which uses a database table to generate primary keys. This approach requires a lot of database queries and pessimistic locks to generate unique values.

14:35:50,959 DEBUG [org.hibernate.SQL] - select next_val as id_val from hibernate_sequence for update
14:35:50,976 DEBUG [org.hibernate.SQL] - update hibernate_sequence set next_val= ? where next_val=?
14:35:51,097 DEBUG [org.hibernate.SQL] - insert into Author (firstName, lastName, version, id) values (?, ?, ?, ?)

You can avoid that by defining a @GenericGenerator which tells Hibernate to use the native strategy to generate the primary key values.

@Entity
public class Author {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO, generator = "native")
	@GenericGenerator(name = "native", strategy = "native")
	@Column(name = "id", updatable = false, nullable = false)
	private Long id;

	...
}

Hibernate will then use MySQL’s autoincremented database column to generate the primary key values.

14:41:34,255 DEBUG [org.hibernate.SQL] - insert into Author (firstName, lastName, version) values (?, ?, ?)
14:41:34,298 DEBUG [org.hibernate.id.IdentifierGeneratorHelper] - Natively generated identity: 1

3. Mappings: Read-only Views

With JPA and Hibernate, you can map views in the same way as any database table. As long as you follow Hibernate’s naming conventions, you just need to implement a class with an attribute for each column you want to map and annotate it with an @Entity annotation.

If the view is read-only, you should tell Hibernate about it with an @Immutable annotation. It will then ignore all changes to this entity.

@Entity
@Immutable
public class BookView {
  
  @Id
  @Column(name = "id", updatable = false, nullable = false)
  private Long id;

  @Column(name = "version")
  private int version;
 
  @Column
  private String title;
 
  @Column
  @Temporal(TemporalType.DATE)
  private Date publishingDate;
 
  @Column
  private String authors;
  
  ...
  
}

4. Queries: MySQL-specific functions and data types

As every other database, MySQL extends the SQL standard with a set of custom functions and data types. Examples for that are the JSON data type and the sysdate function.

These are not supported by JPA but thanks to Hibernate’s MySQL dialect, you can use them anyways.

Query q = em.createQuery("SELECT a, sysdate() FROM Author a ");
List<Object[]> results = q.getResultList();

If you find a function or data type that are not supported by Hibernate’s MySQL dialect, you can use an AttributeConverter to convert the data type to a supported one and the JPQL function function to call any function within a JPQL query.

But keep in mind that by using database specific functions or data types, you bind your application to a specific database. You will need to change these parts of your application if you need to support a different database.

5. Queries: Stored Procedures

Most database administrators like to use stored procedures to perform data-heavy operations within the database. In most cases, this approach is much faster than doing the same operations in the Java code.

But nevertheless, most Java developers don’t want to use stored procedures. There is, of course, the argument that the business logic gets distributed over multiple systems which makes it harder to test and understand. Another one is that before JPA 2.1, the specification didn’t provide direct support for stored procedure calls. You had to use native queries and the overall approach felt complicated.

That changed with JPA 2.1 and the introduction of the StoredProcedureQuery and the @NamedStoredProcedureQuery.

@NamedStoredProcedureQuery

The @NamedStoredProcedureQuery annotation allows you to define the stored procedure call once and to reference it by its name in your business code. The following code snippet shows a simple example that defines the call of the stored procedure calculate with the input parameters x and y and the output parameter sum.

@NamedStoredProcedureQuery(
	name = "calculate", 
	procedureName = "calculate", 
	parameters = {	@StoredProcedureParameter(mode = ParameterMode.IN, type = Double.class, name = "x"),
			@StoredProcedureParameter(mode = ParameterMode.IN, type = Double.class, name = "y"),
			@StoredProcedureParameter(mode = ParameterMode.OUT, type = Double.class, name = "sum") })

You can then provide the name of your @NamedStoredProcedureQuery to the createNamedStoredProcedureQuery of the EntityManager to instantiate a new StoredProcedureQuery.

StoredProcedureQuery query = em.createNamedStoredProcedureQuery("calculate");
query.setParameter("x", 1.23d);
query.setParameter("y", 4d);
query.execute();
Double sum = (Double) query.getOutputParameterValue("sum");

As you can see in the code snippet, you can set the value of the input parameters in the same way as you set any bind parameter values for a JPQL query. You just have to call the setParameter method on the StoredProcedureQuery with the name and the value of the input parameter.

StoredProcedureQuery

The programmatic definition of a stored procedure call is very similar to the annotation based approach I showed you in the previous example. You just need to call the createStoredProcedureQuery on the EntityManager with the name of the stored procedure you want to execute. This gives you a StoredProcedureQuery interface which you can use to register the input and output parameters of the procedure.

StoredProcedureQuery query = em.createStoredProcedureQuery("calculate");
query.registerStoredProcedureParameter("x", Double.class, ParameterMode.IN);
query.registerStoredProcedureParameter("y", Double.class, ParameterMode.IN);
query.registerStoredProcedureParameter("sum", Double.class, ParameterMode.OUT);

That’s all you need to do define the stored procedure call. You can then use it in the same way as the @NamedStoredProcedureQuery. You first set the input parameter values before you execute the stored procedure call.

query.setParameter("x", 1.23d);
query.setParameter("y", 4d);
query.execute();

Summary

As you’ve seen, Hibernate already supports most of the MySQL-specific features. But there are still a few things you need to keep in mind if you want to create a portable and well-performing application.

Especially the generation of unique primary key values and the changed behavior of GenerationType.AUTO in Hibernate 5 can create unexpected scalability issues when you deploy your application to production.