Magnus K Karlsson: JPA 2.2 MySQL 8 Mapping Boolean, Date, Enum and CLOB

Here I will show how to map Boolean, java.util.Date, Boolean and enum in JPA 2.2 (Java EE 8) and test it against MySQL 8 CE and in-memory database H2.

package se.magnuskkarlsson.clearca.ca.entity;

import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EnumType;
import javax.persistence.Enumerated;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Lob;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

@Entity
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(columnDefinition = "BIT")
    private Boolean enabled;

    @Temporal(TemporalType.TIMESTAMP)
    @Column
    private Date created;

    @Enumerated(EnumType.STRING)
    @Column
    private Status status;

    // https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html#data-types-storage-reqs-strings
    @Lob
    @Column(columnDefinition = "TEXT") // 2^16 = 65 536 = 65 KB
    private String data1;

    @Lob
    @Column(columnDefinition = "MEDIUMTEXT") // 2^24 = 16 777 216 = 16 MB
    private String data2;

    @Lob
    @Column(columnDefinition = "LONGTEXT") // 2^32 = 4 294 967 296 = 4 GB
    private String data3;

    // ----------------------- Logic Methods -----------------------

    // ----------------------- Helper Methods -----------------------

    public enum Status {

        ACTIVE, DISABLE;

    }

    // ----------------------- Get and Set Methods -----------------------

    public Long getId() {
        return id;
    }

    public boolean isEnabled() {
        return enabled;
    }

    public User setEnabled(boolean enabled) {
        this.enabled = enabled;
        return this;
    }

    public Date getCreated() {
        return created;
    }

    public User setCreated(Date created) {
        this.created = created;
        return this;
    }

    public Status getStatus() {
        return status;
    }

    public User setStatus(Status status) {
        this.status = status;
        return this;
    }

    public String getData1() {
        return data1;
    }

    public User setData1(String data1) {
        this.data1 = data1;
        return this;
    }

    public String getData2() {
        return data2;
    }

    public User setData2(String data2) {
        this.data2 = data2;
        return this;
    }

    public String getData3() {
        return data3;
    }

    public User setData3(String data3) {
        this.data3 = data3;
        return this;
    }

}

And src/test/resources/META-INF/persistence.xml with 2 persistence unit for integration testing.

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_2.xsd"
    version="2.2">

    <persistence-unit name="IT" transaction-type="RESOURCE_LOCAL">
        <class>se.magnuskkarlsson.clearca.ca.entity.User</class>
        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect" />
            <property name="hibernate.connection.driver_class" value="org.h2.Driver" />
            <property name="hibernate.connection.url" value="jdbc:h2:mem:" />
            <property name="hibernate.hbm2ddl.auto" value="create-drop" />

            <property name="hibernate.show_sql" value="true" />
            <property name="hibernate.format_sql" value="true" />
            <property name="hibernate.generate_statistics" value="false" />
            <property name="hibernate.cache.infinispan.statistics" value="false" />
        </properties>
    </persistence-unit>

    <persistence-unit name="IT-MySQL" transaction-type="RESOURCE_LOCAL">
        <class>se.magnuskkarlsson.clearca.ca.entity.User</class>
        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL57InnoDBDialect" />

            <!-- <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver" /> -->
            <property name="hibernate.connection.driver_class" value="com.mysql.cj.jdbc.Driver" />
            <property name="hibernate.connection.url"
                value="jdbc:mysql://localhost:3306/clearca?serverTimezone=UTC" />
            <property name="hibernate.connection.username" value="root" />
            <property name="hibernate.connection.password" value="root" />
            <property name="hibernate.hbm2ddl.auto" value="create-drop" />

            <property name="hibernate.show_sql" value="true" />
            <property name="hibernate.format_sql" value="true" />
            <property name="hibernate.generate_statistics" value="false" />
            <property name="hibernate.cache.infinispan.statistics" value="false" />
        </properties>
    </persistence-unit>

</persistence>

And maven dependency for JUnit testing

        <hibernate.version>5.3.14.Final</hibernate.version>
        <hibernate-validator.version>6.0.18.Final</hibernate-validator.version>
...
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>1.4.199</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.17</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>${hibernate.version}</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-envers</artifactId>
            <version>${hibernate.version}</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.hibernate.validator</groupId>
            <artifactId>hibernate-validator</artifactId>
            <version>${hibernate-validator.version}</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.glassfish</groupId>
            <artifactId>jakarta.el</artifactId>
            <version>3.0.2</version>
            <scope>test</scope>
        </dependency>

And junit code for integration Test

package se.magnuskkarlsson.clearca.ca.entity;

import java.util.Date;
import java.util.Locale;

import javax.persistence.EntityManager;
import javax.persistence.Persistence;

import org.junit.After;
import org.junit.AfterClass;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;

import com.github.javafaker.Faker;

import se.magnuskkarlsson.clearca.Configuration;

public class UserMySQLIT {

    private static EntityManager em;

    private final Faker faker = new Faker(new Locale("sv-SE")); // default Locale("en", "")

    @BeforeClass
    public static void oneTimeSetUp() throws Exception {
        new Configuration().getCryptoProvider();
        em = Persistence.createEntityManagerFactory("IT-MySQL").createEntityManager();
    }

    @Before
    public void setUp() throws Exception {
        em.getTransaction().begin();
    }

    @After
    public void tearDown() {
        em.getTransaction().commit();
    }

    @AfterClass
    public static void oneTimeTearDown() throws Exception {
    }

    // ----------------------- Test Methods -----------------------

    @Test
    public void getCert() throws Exception {
        User user = new User() //
                .setEnabled(true) //
                .setCreated(new Date()) //
                .setData1(faker.lorem().fixedString(65 * 1000)) //
                .setData2(faker.lorem().fixedString(16 * 1000 * 1000)) //
                .setData3(faker.lorem().fixedString(4 * 1000 * 1000 * 1000));

        em.persist(user);

        System.out.println("Persisted: " + user.getId());
    }

}

And when run.

...
Nov 17, 2020 8:38:10 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001005: using driver [com.mysql.cj.jdbc.Driver] at URL [jdbc:mysql://localhost:3306/clearca?serverTimezone=UTC]
...
Nov 17, 2020 8:23:42 AM org.hibernate.dialect.Dialect <init>
INFO: HHH000400: Using dialect: org.hibernate.dialect.MySQL57InnoDBDialect
...
    create table User (
       id bigint not null auto_increment,
        created datetime(6),
        data1 TEXT,
        data2 MEDIUMTEXT,
        data3 LONGTEXT,
        enabled BIT,
        status varchar(255),
        primary key (id)
    ) engine=InnoDB
    
==================================

Nov 17, 2020 8:20:46 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001005: using driver [org.h2.Driver] at URL [jdbc:h2:mem:]
...
Nov 17, 2020 8:20:46 AM org.hibernate.dialect.Dialect <init>
INFO: HHH000400: Using dialect: org.hibernate.dialect.H2Dialect
...
    create table User (
       id bigint generated by default as identity,
        created timestamp,
        data1 TEXT,
        data2 MEDIUMTEXT,
        data3 LONGTEXT,
        enabled BIT,
        status varchar(255),
        primary key (id)
    )

Magnus K Karlsson

November 17, 2020

JPA 2.2 MySQL 8 Mapping Boolean, Date, Enum and CLOB

No comments: