The JPA specification and Hibernate, as its hottest implementation, declare to offer database portability. Meaning you don’t want to vary your persistence code or mapping annotations when connecting your utility to a distinct RDBMS. They obtain that by offering an automatic object-relational mapping, an API that abstracts from the executed SQL statements, and their very own question language.
Primarily based on this, builders typically assume they solely want to vary their JDBC driver and database connection to modify to a distinct RDBMS. However sadly, that’s often not the case.
However JPA’s and Hibernate’s declare additionally isn’t completely flawed. The supplied abstractions disguise most database-specific issues and get you extraordinarily near a transportable persistence layer. However there are a number of pitfalls that always trigger issues whenever you join your utility to a distinct RDBMS for the primary time.
On this article, I’ll present you ways Hibernate achieves database portability, which options often trigger issues, and how one can clear up them.
How Hibernate achieves database portability
One of many major challenges when offering database portability is to deal with the small variations between most RDBMS’s SQL dialects and supported options. Hibernate solves this by providing an enormous set of various Dialect implementations.
The perfect half about that is that Hibernate can mechanically decide and use a Dialect. Nevertheless it’s nonetheless good to at the very least perceive the essential idea. So, let’s take a fast have a look at it.
Hibernate’s database Dialects
Every Dialect implementation gives the required info to regulate the mapping of your entity courses and the generated SQL statements to a particular database. It defines issues like:
- the identify of the column sort to which your entity’s attribute sort, e.g., Integer, will get mapped,
- the identify, parameters, and return sort of the supported database capabilities,
- how the database can generate main key values,
- the help for LIMIT and OFFSET clauses within the generated question statements,
- the help for timeouts and database locks
- and way more.
You could find Dialect implementations for nearly all generally used databases within the org.hibernate.dialect bundle.
Since Hibernate 6, every Dialect implementation handles all out there variations of the corresponding database. Older variations typically used separate Dialect implementations for various variations of the identical RDBMS, e.g., PostgreSQL95Dialect, PostgreSQL94Dialect, and PostgreSQL93Dialect. Every of those version-specific Dialects prolonged the implementation of the earlier model and added help for brand spanking new column sorts, capabilities, and different issues that had been added within the corresponding launch of the RDBMS.
Configuring or resolving the Dialect
Hibernate can decide the proper Dialect implementation mechanically throughout startup. It does that by iterating by all DialectResolver implementations out there on the classpath and calling their resolveDialect with the metadata info of the related database. If a resolver is aware of tips on how to deal with this info, it instantiates a Dialect implementation.
These DialectResolver implementations can be found for all of Hibernate’s commonplace Dialects. When you implement a customized Dialect, you both additionally want to offer an implementation of the DialectResolver interface or explicitly configure the Dialect implementation you need to use.
If the Dialect decision fails otherwise you need to override it, it’s worthwhile to configure the hibernate.dialect property in your persistence.xml configuration.
<persistence>
<persistence-unit identify="my-persistence-unit">
...
<properties>
<property identify="hibernate.dialect" worth="org.hibernate.dialect.PostgreSQLDialect" />
<property identify="jakarta.persistence.jdbc.driver" worth="org.postgresql.Driver" />
<property identify="jakarta.persistence.jdbc.url" worth="jdbc:postgresql://localhost:5432/myDb" />
<property identify="jakarta.persistence.jdbc.consumer" worth="postgres" />
<property identify="jakarta.persistence.jdbc.password" worth="postgres" />
</properties>
</persistence-unit>
</persistence>
Options that always break database portability
As I defined within the earlier part, Hibernate’s Dialect implementations deal with most variations between the assorted RDBMS. However there are a number of options that always trigger issues. That’s often both as a result of some databases don’t help a particular function or since you use a Hibernate function that doesn’t summary from the underlying SQL assertion.
Drawback 1: Producing main key values
You in all probability already know that you need to use autoincremented database columns or database sequences to let your database generate distinctive main key values. You may configure your most well-liked choice by annotating the first key attribute with a @GeneratedValue annotation and offering the GenerationType enum worth in your most well-liked sequence.
@Entity
public class Creator {
@Id
@GeneratedValue(technique = GenerationType.SEQUENCE, generator = "author_seq")
@SequenceGenerator(identify = "author_seq", sequenceName = "author_seq")
personal Lengthy id;
...
}
For efficiency causes, you need to use GenerationType.SEQUENCE. It tells Hibernate to make use of a database sequence to generate distinctive main key values. The principle good thing about this method is that it permits Hibernate to generate the first key worth with out executing an SQL INSERT assertion. And due to this separation, Hibernate can carry out extra efficiency optimizations. I clarify this in additional element within the Hibernate Efficiency Tuning course within the Persistence Hub.
Sadly, MySQL databases don’t help sequences. You want to use GenerationType.IDENTITY as a substitute.
When you solely have to help MySQL databases, you may annotate your main key attribute with @GeneratedValue(technique = GenerationType.IDENTITY) as a substitute. Nevertheless it will get a bit extra sophisticated in case your utility helps a number of RDBMS and also you need to use GenerationType.SEQUENCE for some or most of them.
In that case, many builders determine to make use of GenerationType.AUTO and let Hibernate decide the technique. However that causes extreme efficiency issues. In case your database doesn’t help sequences, GenerationType.AUTO makes use of the very inefficient GenerationType.TABLE and never as many builders anticipate to GenerationType.IDENTITY. So, you need to higher keep away from it and use the next resolution.
Answer: Override the technology technique for main keys
One of the simplest ways to make use of essentially the most environment friendly technology technique in your main key values for every database is to outline a default technique and override it when mandatory.
I often select GenerationType.SEQUENCE as my default technique. I annotate each main key attribute of my entity courses with @GeneratedValue(GenerationType.SEQUENCE).
@Entity
public class Creator {
@Id
@GeneratedValue(technique = GenerationType.SEQUENCE, generator = "author_seq")
@SequenceGenerator(identify = "author_seq", sequenceName = "author_seq")
personal Lengthy id;
...
}
Then I create an orm.xml mapping file and put it subsequent to my persistence.xml file. Hibernate will decide it up mechanically.
<entity-mappings>
<entity class="com.thorben.janssen.mannequin.Creator" identify="Creator">
<attributes>
<id identify="id">
<generated-value technique="IDENTITY"/>
</id>
</attributes>
</entity>
...
</entity-mappings>
Hibernate will merge the mapping definitions outlined through annotations and within the orm.xml file throughout the deployment. And if there’s a battle between these 2 definitions, it makes use of the mapping outlined within the orm.xml file. You may study extra about all of this in my article Mapping Definitions in JPA and Hibernate – Annotations, XML or each?
Utilizing this method, you may override the technology technique outlined by your @GeneratedValue annotation and set it to GenerationType.IDENTITY.
Drawback 2: Native queries
Native queries are one other function that always breaks database portability. And the explanation for that’s easy. Your JPA implementation, e.g., Hibernate, doesn’t parse and rework the SQL assertion you present when creating the native question. It merely executes it.
Creator a = (Creator) em.createNativeQuery("choose a.id, a.firstName, a.lastName, a.model from Creator a WHERE a.id = :id", Creator.class)
.setParameter("id", writer.getId())
.getSingleResult();
So, it’s as much as you to offer an SQL assertion that works on all databases your utility must help. That’s typically tougher than it’d sound. There are 3 widespread options to this drawback.
Answer 1: Don’t use native queries
The simplest method to keep away from this drawback is to keep away from native queries typically. So long as you solely use JPQL queries or the Standards API, Hibernate generates the SQL assertion and makes use of a Dialect implementation to regulate it to the RDBMS.
Avoiding native queries is likely to be the simplest method, nevertheless it’s not very sensible. Most purposes require at the very least a number of queries too advanced for JPQL or the Standards API. In these instances, you may strive one of many following options.
Answer 2: Present completely different units of SQL statements
Advert-hoc native queries make it comparatively simple to offer completely different SQL statements for every RDBMS. You solely have to outline a number of variations of a category that comprises a String fixed for each assertion.
public class NativeQueryStrings {
public static String AUTHOR__FIND_BY_ID = "choose a.id, a.firstName, a.lastName, a.model from Creator a WHERE a.id = :id";
public static String BOOK__FIND_BY_ID = "choose b.* from Ebook b WHERE b.id = :id";
}
Whenever you create a number of variations of the NativeQueryStrings class, please keep in mind that you’ll not want to regulate each assertion. Particularly the less complicated ones would possibly work on each RDBMS it’s worthwhile to help.
In that case, you may determine to not extract the question string into the NativeQueryStrings class. Otherwise you create a category containing every question’s default model and prolong that class to create a database-specific model.
In your persistence code, you then use these constants to create an ad-hoc native question.
Creator a = (Creator) em.createNativeQuery(NativeQueryStrings.AUTHOR__FIND_BY_ID, Creator.class)
.setParameter("id", writer.getId())
.getSingleResult();
Whenever you bundle your utility, you may decide the NativeQueryStrings class model that matches the RDBMS that you’ll use in manufacturing and embrace solely that one in your deployment.
Relying in your utility’s stack, you may also be capable to embrace all of these courses in your utility and configure which one you need to use at runtime. For instance, when you’re utilizing CDI, you are able to do this through the use of a function known as Options.
Answer 3: Use @NamedNativeQuery and override the assertion
When you don’t need to handle a number of courses that outline your ad-hoc queries, you may outline every native question as a @NamedNativeQuery.
@Entity
@NamedNativeQuery(identify = "Creator.selectByIdNative", question = "choose a.id, a.firstName, a.lastName, a.model from Creator a WHERE a.id = :id", resultClass = Creator.class)
public class Creator { ... }
After you outline the named question, you may reference it by its identify and instantiate it in your persistence code.
Creator a = em.createNamedQuery("Creator.selectByIdNative", Creator.class)
.setParameter("id", a.getId())
.getSingleResult();
Much like the beforehand mentioned technology methods for main keys, you may override the assertion of a @NamedNativeQuery within the orm.xml mapping file. That lets you change the question’s assertion with out altering your persistence code.
<entity-mappings>
<entity class="com.thorben.janssen.mannequin.Creator" identify="Creator">
...
<named-native-query identify="Creator.selectByIdNative"
result-class="com.thorben.janssen.mannequin.Creator">
<question><![CDATA[
select a.*
from Author a
WHERE a.id = :id
]]></question>
</named-native-query>
</entity>
</entity-mappings>
Drawback 3: Customized capabilities or saved procedures
Even when you don’t use native queries, customized capabilities and saved procedures typically trigger issues if it’s worthwhile to help a number of RDBMS. However this time, it’s not a JPA or Hibernate drawback.
The issue is the lacking standardization of saved procedures and the way you outline database capabilities. That makes it typically unimaginable to make use of the identical code to deploy a saved process or database operate on completely different RDBMS.
You solely have 2 choices to keep away from this drawback. You both present a database-specific definition of each customized operate and saved process otherwise you keep away from utilizing these database options completely.
Conclusion
RDBMS not solely differ within the options and efficiency they supply. Additionally they differ within the supported column sorts, the anticipated construction of the SQL statements, and the supported database capabilities.
Hibernate tries to deal with these variations mechanically. It makes use of a database-specific Dialect implementation that gives all of the required info to regulate the entity mappings and generated SQL statements.
However when you’re utilizing an API that doesn’t present an abstraction of the executed question statements or when you use a function that’s not supported by all databases, Hibernate can’t deal with these issues for you. Typical examples are the technology of distinctive main key values, native queries, and database capabilities.
In these instances, it’s worthwhile to determine when you can keep away from a particular function completely, e.g., solely use JPQL or Standards queries. Or it’s worthwhile to present database-specific variations your self. In that case, you may create an orm.xml file through which you regulate the mapping info you supplied as annotations.
