Functions and benefits of configuring database dialects in Hibernate

At the underlying layer of Hibernate, SQL statements are still used to perform database operations. Although all relational databases support standard SQL statements, all databases have extended standard SQL statements, therefore, there are some differences in syntax details. Therefore, Hibernate needs to identify these differences based on the database.

For example, you only need to use the limit keyword for querying by page in the MySQL database. standard SQL does not support the limit keyword, for example, Oracle uses the in-row view for paging. For the same application, when we migrate between different databases, the access details of the underlying database will change, and Hibernate is also ready for this change. What we need to do now is: tell the underlying database of the Hibernate application to be used-this is the database dialect.

Once we have set a proper database dialect for Hibernate, Hibernate will be able to automatically cope with the detailed differences in access to the underlying database.

The dialects used by different databases are shown in table 5.1.

Table of different databases and their corresponding Dialects

Relational Database	Fang Yan
DB2	Org. hibernate. dialect. DB2Dialect
DB2 AS/400	Org. hibernate. dialect. DB2400Dialect
DB2 OS390	Org. hibernate. dialect. DB2390Dialect
PostgreSQL	Org. hibernate. dialect. PostgreSQLDialect
MySQL	Org. hibernate. dialect. MySQLDialect
MySQL with InnoDB	Org. hibernate. dialect. MySQLInnoDBDialect
MySQL with MyISAM	Org. hibernate. dialect. MySQLMyISAMDialect
Oracle (any version)	Org. hibernate. dialect. OracleDialect
Oracle 9i	Org. hibernate. dialect. Oracle9iDialect
Oracle 10g	Org. hibernate. dialect. Oracle10gDialect

Sybase	Org. hibernate. dialect. SybaseDialect
Sybase Anywhere	Org. hibernate. dialect. SybaseAnywhereDialect
Microsoft SQL Server	Org. hibernate. dialect. SQLServerDialect
SAP DB	Org. hibernate. dialect. SAPDBDialect
Informix	Org. hibernate. dialect. InformixDialect
HypersonicSQL	Org. hibernate. dialect. HSQLDialect
Ingres	Org. hibernate. dialect. IngresDialect
SS	Org. hibernate. dialect. ProgressDialect
Mckoi SQL	Org. hibernate. dialect. MckoiDialect
Interbase	Org. hibernate. dialect. InterbaseDialect
Pointbase	Org. hibernate. dialect. PointbaseDialect
FrontBase	Org. hibernate. dialect. FrontbaseDialect
Firebird	Org. hibernate. dialect. FirebirdDialect

If a system may run in multiple databases or use multiple databases at the same time, using Hibernate will bring you a lot of convenience, and many people who come to contact Hibernate will understand it. At the underlying layer of Hibernate, the dialect package is used to abstract the differences between various databases. Dialect class implements the same things for each database, and different databases correspond to an extension implementation of this class. Finally, DialectFactory is used to determine which class to create. Generally, we specify the hibernate. dialect attribute, and then directly create the class corresponding to this attribute. If this attribute is not specified, Hibernate determines the proper dialect. In DialectFactory, Map the dialects corresponding to various databases is initialized. The database product name is the key and the packaging object of the dialect is the value. When Hibernate automatically selects a dialect, the database product name is obtained through JDBC DatabaseMetadata, and the corresponding dialect is obtained based on the name. The code for initializing the Map in DialectFactory is as follows:

// TODO: this is the stuff it 'd be nice to move to a properties file or some other easily user-editable place
Private static final Map MAPPERS = new HashMap ();
Static {
// Detectors...
MAPPERS. put ("HSQL Database Engine", new VersionInsensitiveMapper ("org. hibernate. dialect. HSQLDialect "));
MAPPERS. put ("H2", new VersionInsensitiveMapper ("org. hibernate. dialect. H2Dialect "));
MAPPERS. put ("MySQL", new VersionInsensitiveMapper ("org. hibernate. dialect. MySQLDialect "));
MAPPERS. put ("PostgreSQL", new VersionInsensitiveMapper ("org. hibernate. dialect. PostgreSQLDialect "));
MAPPERS. put ("Apache Derby", new VersionInsensitiveMapper ("org. hibernate. dialect. DerbyDialect "));

Originally, this automatic selection method will bring us convenience. Unfortunately, we can know from the comments of the above Code that the initialization of this part is hard-coded at present, only later versions will be moved to the configuration file or other places that are easily compiled by users. Otherwise, we can easily add our configuration for Hibernate to automatically choose. The name of the database product obtained by the database driver we often use will be different from the hard code above, so we have to configure the database dialect by ourselves.

Because several data sources are used in our system, they often correspond to different types of databases, and the data sources are provided by containers, during the first deployment, I often forgot to modify the corresponding database dialect because of the Database Type Change, which caused a lot of inconvenience to the implementer. However, in addition to providing drivers with the same name as the above hard-coded database products for various databases, we can only do it ourselves.

Hibernate details: click here
Hibernate: click here

Hibernate Chinese manual PDF