How to use CockroachDB with Hibernate
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.
Disclaimer: This post is sponsored by Cockroach Labs.
CockroachDB is an open-source relational database designed to be highly scalable and reliable in distributed environments. It’s available on AWS and Google Cloud or as an on-premise installation. This makes it an interesting candidate for microservice-based applications.
If you want to give it a try or if you’re already using it, I have great news for you. Since version 5.4.19, Hibernate ORM includes a CockroachDB dialect. It makes the database very easy to use and enables the CockroachDB team to support database-specific features with Hibernate in the future. In older releases, you had to rely on CockroachDB’s PostgreSQL compatibility and Hibernate’s PostgreSQL dialect. That worked reasonably well. But a database-specific dialect seems to be the better solution.
Let’s take a look at how to connect Hibernate with your CockroachDB and some database-specific mapping and query recommendations.
Connecting Hibernate to a CockroachDB database
After you installed your database locally or set up an instance at one of the supported cloud providers, you need to configure Hibernate to connect to it.
As explained earlier, CockroachDB is compatible with PostgreSQL. Due to that, you can use PostgreSQL’s JDBC driver and connection information when defining your datasource. The only CockroachDB-specific part of your configuration is the dialect. You should set it to the CockroachDB dialect that fits the version of your database.
In the following example, I use the user root and an empty password to connect to a CockroachDB single-node cluster that runs in a Docker container on my local machine. In a production environment, you would use a similar configuration to connect to a multi-node cluster with encrypted network communication and better user authentication.
<persistence>
<persistence-unit name="my-persistence-unit">
<properties>
<property name="hibernate.dialect" value="org.hibernate.dialect.CockroachDB201Dialect" />
<property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver" />
<property name="javax.persistence.jdbc.url" value="jdbc:postgresql://localhost:26257/defaultdb?sslmode=disable" />
<property name="javax.persistence.jdbc.user" value="root" />
<property name="javax.persistence.jdbc.password" value="" />
</properties>
</persistence-unit>
</persistence>
This is the only CockroachDB-specific thing you need to do to build a basic Hibernate-based application.
3 Things You Should Know
Hibernate’s CockroachDB dialect handles all the database-specific details. You can now use your standard entity mappings and queries to build your persistence layer. But as for all databases, you should know a few things to create an efficient persistence layer.
Primary Key Generation
When your application adds many records to the database, Hibernate’s primary key generation strategy often becomes a performance-critical mapping detail. Using JPA and Hibernate, you can define the strategy by annotating your primary key attribute with @GeneratedValue. The provided GenerationType enum specifies the strategy, and you can choose between an auto-incremented column and a sequence.
@Entity
public class Book {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
private Long id;
...
}
If you’re a regular reader of this blog, you know that I always recommend using GenerationType.SEQUENCE with Hibernate. It’s the most efficient because Hibernate can split the generation of a new primary key value from the execution of the insert statement. Hibernate also provides proprietary optimizations to reduce the number of database interactions.
Unfortunately, the CockroachDB documentation discourages the usage of sequences. Their standard indexing of sequential values isn’t as efficient as it is for UUIDs. But their generation of UUIDs is based on a default value, similar to an autoincremented column, and I couldn’t use it with Hibernate. Your current best option is to use a database sequence and a hash-sharded index on the primary key column. This index provides better performance than the standard one.
Mapping Views
Database views are a popular feature to reduce query complexity and restrict access to parts of your data. You can map them in the same way as you map a database table. As long as your Java class is not final, has the same name as your database view, and each attribute maps a column with the same name, you only need to annotate the class with @Entity and the primary key attribute with @Id. Everything else gets handled by Hibernate automatically.
But there is one crucial difference between a database view and a table. You often can’t change the data in a view. If that’s the case for your view, you should tell Hibernate about it. You should annotate your entity class with @Immutable, use field-based access, and don’t provide any setter methods.
Here you can see a simple example that maps the database view BookAuthorView.
@Entity
@Immutable
public class AuthorBookView {
@Id
private Long bookId;
private String authorName;
private String title;
public Long getBookId() {
return bookId;
}
public String getAuthorName() {
return authorName;
}
public String getTitle() {
return title;
}
}
Using this mapping, you can use the AuthorBookView entity in your queries in the same way as any other entity. But Hibernate will ignore it in its dirty checks. Due to that, it will not try to insert, update, or remove any records in that view.
Using CockroachDB-specific Query Features
Even if you use a database-specific dialect, JPQL and the Criteria API don’t support all query features offered by your database. To use any of CockroachDB’s more advanced features, like window functions, you need to use a native SQL query.
You can create a native SQL query by calling the createNativeQuery method on your EntityManager and providing the SQL statement as a String.
em.createNativeQuery("SELECT DISTINCT name, "
+ "COUNT(*) OVER b AS \"number of books\", "
+ "(AVG(pages) OVER b) AS \"average pages per book\" "
+ "FROM author "
+ "JOIN book_author ON author.id = book_author.authors_id "
+ "JOIN book ON book.id = book_author.book_id "
+ "WINDOW b AS (PARTITION BY name)").getResultList();
Hibernate sends the provided SQL statement to the database. So, it’s your responsibility to ensure that all RDBMS supported by your application can handle the provided statement. This makes it harder to support multiple RDBMS. But it also enables you to use all proprietary features supported by your database.
Conclusion
Hibernate’s dialect and PostgreSQL’s JDBC driver enable you to use CockroachDB in the same way as any other database with Hibernate. You only need to configure a database-specific dialect and the connection URL to your database.
In addition to this, you should pay special attention to your primary key mappings. Hibernate prefers sequence-based primary key values, which require a special index in your CockroachDB schema.
