MySQL 9 high-performance tips for using JPA + Hibernate

Although there are SQL standards, each relational database will eventually be unique, so you need to tune the data access layer to take advantage of the relational database in use.

In this article, we'll show you what we can do to improve performance when using MySQL with JPA and hibernate.

Do not use the auto identifier Generatortype

Each entity requires an identifier that identifies identifiers to identify the table records associated with the entity. JPA and hibernate allow automatic generation of entity identifiers based on three different policies:

IDENTITY

SEQUENCE

TABLE

As I explained in this article, the table identifier policy does not scale when you increase the number of database connections. Moreover, even if it is a database connection, the identifier generation response time is 10 times times larger than using identity or sequence.

If you use Auto GenerationType:

@Id

@GeneratedValue (strategy = Generationtype.auto)

Private Long ID;

Hibernate 5 will be returned to use the table builder, which is detrimental to performance.

As I explained in this article, you can easily resolve this issue by using the following mappings:

@Id

@GeneratedValue (strategy= Generationtype.auto, generator= "native")

@GenericGenerator (name = "Native", strategy = "native")

Private Long ID;

The local generator chooses the identity instead of the table.

Identity Builder disables JDBC batch Insert

MySQL 5.7 and 8.0 do not support sequence objects, so you need to use identity. However, as I explained in this article, the identity generator can prevent hibernate from using JDBC Bulk inserts.

JDBC Bulk Update and delete is not affected, only INSERT statements cannot be batched because the INSERT statement has been executed before persistence context is flushed, so hibernate knows what entity identifiers to assign to the persisted entity.

If you want to resolve this issue, you must perform a JDBC batch insert through a different framework, such as Jooq.

Accelerate integration testing with Docker and TMPFS

MySQL and mariadb are very slow when they have to discard the database schema and recreate it each time a new integration test is about to run. However, you can easily resolve this issue with the help of Docker and Tmpfs.

As I explained in this article, the integration test runs at the same speed as a memory database (such as H2 or HSQLDB) by mapping the data folders in memory.

Use JSON for unstructured data

Even when you are using an RDBMS, there must be many times when you want to store unstructured data:

Data from the client, such as JSON, needs to be parsed and inserted into our system.

can cache image processing results to save re-processing

Although this machine is not supported, you can easily map Java objects to JSON columns. You can even map the JSON column type to the Jackson Jsonnode.

More importantly, you don't even have to write these custom types to crawl from MAVEN central:

<dependency>

<groupId>com.vladmihalcea</groupId>

<artifactId>hibernate-types-52</artifactId>

<version>1.0.0</version>

</dependency>

It's cool, isn't it?

Using stored procedures to save a database

Moving all of your data into and out of a database is not very efficient when processing large amounts of data. However, it is much better to process the database side by calling the stored procedure.

For more details, see this article on how to invoke a MySQL stored procedure with JPA and hibernate.

Careful resultset flow

SQL flow is very meaningful in two-tier applications. If you are performing a resultset stream, you should also pay attention to the JDBC driver. On MySQL, you need to set the statement size to Integer.min_value.

However, paging is more appropriate for web-based applications. JPA 2.2 Even introduced support for the Java 1.8 stream method, but the execution plan might not be as efficient as using SQL-level paging.

Preparedstatements may be emulated

You might think that since Hibernate uses preparedstatements by default, all statements are executed like this:

In fact, it's more like doing this:

As I explained in this article, unless you set the Useserverprepstmts MySQL JDBC Driver property, Preparedstatements will simulate at the JDBC driver level to save an additional database.

Always End Database transactions

In a relational database, each statement is executed in a given database transaction. Therefore, the transaction is not optional.

However, you should always end a transaction that is currently running by committing or rolling back. Forgetting to end a transaction can cause persistent locking for a long time, and also prevents the MVCC cleanup process from reclaiming old tuples or index entries that are no longer needed.

Submission Date/time not that easy

There are two very complicated things in programming:

Processing encoding

Working with dates/times across multiple time zones

To solve the second problem, it is a good idea to save all timestamps in the UTC time zone. However, when using MySQL, you also need to set the Uselegacydatetimecode JDBC driver configuration property to False.

Conclusion

As you can see, there are a lot of things to remember when using MySQL with JPA and hibernate. Because MySQL is one of the most widely deployed RDBMS and is used by the vast majority of Web applications, it is useful to understand all of these techniques and tweak the data access layer to maximize its use.

http://www.kmjdad.com/
http://www.jnsjzyy.com/
http://www.czhkwl.com/
http://www.express-o2o.com/
http://www.gzjindao.com/
http://www.chumingchuanmeiyishu.com/
http://www.thcxb.com/
http://www.xingguangkeji.com/
http://www.gdrhsy.com/
http://www.clhuiji.com/
http://www.nxjianye.com/
http://www.tjmingsheng.com/
http://www.gangguan022.com/
http://www.zyjbp.com/
http://www.qianhangmy.com/
http://www.tzminbell.com/

MySQL 9 high-performance tips for using JPA + Hibernate