Skip to content

9. Generating Databases from JPA Entities

It is possible to create database tables from JPA entities. We will demonstrate this now. The purpose is to verify that the database generated from the JPA entities is indeed the one we want.

9.1. Setting up the working environment

We will first work with an EclipseLink JPA implementation [1].

Then we delete the tables from the MySQL database [dbproduitscategories] using the client [MyManager] (see Section 23.5). We start by deleting the tables containing the foreign keys [1-3]:

Then we repeat the process with the three remaining tables [4-6]:

We do the same with the [dbproduits] table used by the [spring-jdbc-01 to 03] projects:

Additionally, you must import the two database generation projects:

  • in [1], import the [generic-create-dbproduits] project, which can be found in [<examples>/spring-database-generic/spring-jpa] [2];
  • in [4], import the [generic-create-dbproduitscategories] project, which can be found in [<examples>/spring-database-generic/spring-jpa] [5];

Note: Press Alt-F5 and regenerate all Maven projects;

9.2. Generating the [dbproduitscategories] database

 

9.2.1. Maven configuration

The project's [pom.xml] file is as follows:


<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>dvp.spring.database</groupId>
    <artifactId>generic-create-dbproduitscategories</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>generic-create-dbproduitscategories</name>
    <description>creation of the [dbproduitscategories] database using JPA annotations</description>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.2.3.RELEASE</version>
    </parent>

    <dependencies>
        <!-- spring-jpa-generic -->
        <dependency>
            <groupId>dvp.spring.database</groupId>
            <artifactId>spring-jpa-generic</artifactId>
            <version>0.0.1-SNAPSHOT</version>
        </dependency>
        <!-- Weaver Spring -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-instrument</artifactId>
            <scope>runtime</scope>
        </dependency>
    </dependencies>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <start-class>spring.data.console.Main</start-class>
        <java.version>1.7</java.version>
    </properties>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-surefire-plugin</artifactId>
                <version>2.18.1</version>
            </plugin>
        </plugins>
    </build>

</project>
  • lines 22–26: the dependency on the [spring-jpa-generic] project discussed in Section 6.4;
  • lines 28–32: the dependency on a weaver that will be used to enrich the JPA entities of the EclipseLink and OpenJpa implementations. Its dependency is not required in the [pom.xml] file, but its JAR will be the Java agent used. Including the dependency in the [pom.xml] file ensures that the JAR will be available;

In the end, the dependencies are as follows:

  

9.2.2. Spring Configuration

  

The [AppConfig] class configures the Spring project:


package console;

import generic.jpa.config.ConfigJpa;

import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Import;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

@Configuration
@Import({ ConfigJpa.class })
@EnableJpaRepositories(basePackages = { "console" })
public class AppConfig {

}
  • Line 10: The class retrieves the beans from the [ConfigJpa] class. Note that this class works with the JPA entities in the [dbproduitscategories] database (see Section 6.3);
  • line 11: we declare that the [console] package must be scanned to find [CrudRepository] instances;

In the [ConfigJpa] class, we find the following bean (varies depending on the JPA implementation used):


    // the JPA provider
    @Bean
    public JpaVendorAdapter jpaVendorAdapter() {
        // Note: JPA entities and the EclipseLink configuration are in the META-INF/persistence.xml file
        EclipseLinkJpaVendorAdapter eclipseLinkJpaVendorAdapter = new EclipseLinkJpaVendorAdapter();
        eclipseLinkJpaVendorAdapter.setShowSql(false);
        eclipseLinkJpaVendorAdapter.setDatabase(Database.MYSQL);
        eclipseLinkJpaVendorAdapter.setGenerateDdl(true);
        return eclipseLinkJpaVendorAdapter;
}

Line 8 is the important one here. It is present in all JPA implementations used. It specifies that if the tables associated with the JPA entities do not exist, they must be created. We will use this property to generate the tables.

9.2.3. The repositories

  

The [ProductsRepository] interface is as follows:


package console;

import generic.jpa.entities.dbproduitscategories.Product;

import org.springframework.data.repository.CrudRepository;

public interface ProductRepository extends CrudRepository<Product, Long> {

}

Instantiating this interface will trigger the instantiation of the JPA layer. Indeed, on line 7, the interface references the JPA entity [Product], which will force the instantiation of the JPA layer. We could have used any interface [CrudRepository] referencing one of the JPA entities. We can see that although the [repository] only references the JPA entity [Product], all tables for all JPA entities are generated.

9.2.4. The executable class

  

The [CreateDatabase] class is as follows:


package console;

import org.springframework.context.annotation.AnnotationConfigApplicationContext;

public class CreateDatabase {

    public static void main(String[] args) {
        // Simply instantiate the Spring context to create the tables in the [dbproduitscategories] database
        // You also need at least one Spring Data Repository, otherwise nothing will happen
        System.out.println("Processing...");
        new AnnotationConfigApplicationContext(AppConfig.class).close();
        System.out.println("Processing complete...");
    }

}
  • Line 11: We instantiate the Spring context and immediately close it. In this context, there is the [ProductsRepository] bean that references the JPA entity [Product]. This is sufficient to instantiate the JPA layer and thus generate the tables in the [productcategories] database.

We are in the following configuration:

 
  • the [JDBC] layer is configured for the MySQL database [dbproduitscategories];
  • the [JPA] layer is implemented with EclipseLink;
  • the [dbproduitscategories] database has no tables;

Note: Press Alt-F5 and regenerate all Maven projects;

We are using the following run configuration:

  • In [1-2], this run configuration requires a Java agent for the test to succeed. Depending on the situation, EclipseLink does not always need this agent, but here the execution fails if it is not present. This agent is not an EclipseLink agent but a Spring agent. It is provided by the dependency: 

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-instrument</artifactId>
            <scope>runtime</scope>
</dependency>

included in the project's [pom.xml] file. The agent is located at [<m2-repo>/org/springframework/spring-instrument/4.1.6.RELEASE/spring-instrument-4.1.6.RELEASE.jar], where <m2-repo> is the local Maven repository;

The execution yields the following result:

In [3], we can see that the tables have been generated. Now let’s check the database’s DDL (Domain Definition Language):

The SQL script for generating the tables can also be saved to a file [1].

 

The generated SQL script is as follows:


SET FOREIGN_KEY_CHECKS=0;

USE `dbproduitscategories`;

CREATE TABLE `categories` (
  `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `NAME` VARCHAR(30) COLLATE utf8_general_ci NOT NULL,
  `VERSIONING` BIGINT(20) DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE,
  UNIQUE KEY `NAME` (`NAME`) USING BTREE
) ENGINE=InnoDB
AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
;

CREATE TABLE `products` (
  `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `DESCRIPTION` VARCHAR(100) COLLATE utf8_general_ci DEFAULT NULL,
  `CATEGORY_ID` BIGINT(20) NOT NULL,
  `NAME` VARCHAR(30) COLLATE utf8_general_ci NOT NULL,
  `PRICE` DOUBLE NOT NULL,
  `VERSIONING` BIGINT(20) DEFAULT NULL,
  `CATEGORY` INTEGER(11) NOT NULL,
  PRIMARY KEY (`ID`) USING BTREE,
  UNIQUE KEY `NAME` (`NAME`) USING BTREE,
  KEY `FK_PRODUCTS_CATEGORY_ID` (`CATEGORY_ID`) USING BTREE,
  CONSTRAINT `FK_PRODUITS_CATEGORIE_ID` FOREIGN KEY (`CATEGORIE_ID`) REFERENCES `categories` (`ID`) ON DELETE CASCADE
) ENGINE=InnoDB
AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
;

CREATE TABLE `roles` (
...
) ENGINE=InnoDB
AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
;

CREATE TABLE `users` (
 ...
) ENGINE=InnoDB
AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
;

CREATE TABLE `users_roles` (
...
) ENGINE=InnoDB
AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
;

Let's look, for example, at the SQL script that generates the [PRODUCTS] table (lines 15–29):

  • line 16: [ID] is the primary key (line 23) with the [AUTO_INCREMENT] attribute (line 5). This corresponds to the annotations [@Id, @GeneratedValue(strategy = GenerationType.IDENTITY), @Column(name = ConfigJdbc.TAB_JPA_ID)] for the [id] field of the JPA entity;
  • line 17: the definition of the [DESCRIPTION] column corresponds to the annotation [@Column(name = ConfigJdbc.TAB_PRODUITS_DESCRIPTION, length = 100)] for the [description] field of the JPA entity;
  • line 18: the [CATEGORIE_ID] column is a foreign key from the [PRODUITS] table on the [CATEGORIES.ID] column (line 26). Furthermore, this foreign key has the [ON DELETE CASCADE] attribute. This corresponds to the annotations [@ManyToOne(fetch = FetchType.LAZY), @JoinColumn(name = ConfigJdbc.TAB_PRODUITS_CATEGORIE_ID)] for the [Produit.categorie] field and to the annotation [@OneToMany(fetch = FetchType.LAZY, mappedBy = "categorie", cascade = { CascadeType.ALL }), @CascadeOnDelete] of the [Categorie.produits] field;
  • line 19: the definition of the [NAME] column corresponds to the annotation [@Column(name = ConfigJdbc.TAB_PRODUITS_NAME, unique = true, length = 30, nullable = false)] of the [Product.name] field;
  • line 20: the definition of the [PRICE] column corresponds to the annotation [@Column(name = ConfigJdbc.TAB_PRODUITS_PRIX, nullable = false)] of the [Product.price] field;
  • lines 24-25: the script creates three indexes for each of the table’s unique columns;

The generated tables do not have a default value for the VERSIONING field, whereas the Java code expects one to be present. If this default value is missing, certain tests will fail. We add this attribute as follows:

 
 
 

This is done for the five tables that have the [VERSIONING] column. The default value does not matter; it simply needs to exist. Then, it is incremented by 1 each time the row to which it belongs is modified.

Once this is done, verify that the following test configurations pass:

  • [spring-jdbc-generic-04.JUnitTestDao], which tests the JDBC implementation;
  • [spring-jpa-generic-JUnitTestDao-hibernate-eclipselink], which tests the Hibernate or EclipseLink JPA implementations (in this case, it will be EclipseLink)

Both tests must pass.

9.2.6. Generating tables with Hibernate

We create the Hibernate tables using the following Eclipse environment:

 

Table generation is performed by the run configuration named [generic-create-dbproduitscategories-hibernate] without a Java agent;

The SQL script for the database generated by Hibernate is as follows:


SET FOREIGN_KEY_CHECKS=0;

USE `dbproduitscategories`;

CREATE TABLE `categories` (
  `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `NAME` VARCHAR(30) COLLATE utf8_general_ci NOT NULL,
  `VERSIONING` BIGINT(20) DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE,
  UNIQUE KEY `UK_7ajcg7japnxw846ru01damg8s` (`NAME`) USING BTREE
) ENGINE=InnoDB
AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
;

CREATE TABLE `products` (
  `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `DESCRIPTION` VARCHAR(100) COLLATE utf8_general_ci DEFAULT NULL,
  `CATEGORY_ID` BIGINT(20) NOT NULL,
  `NAME` VARCHAR(30) COLLATE utf8_general_ci NOT NULL,
  `PRICE` DOUBLE NOT NULL,
  `VERSIONING` BIGINT(20) DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE,
  UNIQUE KEY `UK_hfvjn9lp7qoo5x79uu0ump3rf` (`NAME`) USING BTREE,
  KEY `FK_p3foj9yrqnmi7856n9s8mbpue` (`CATEGORY_ID`) USING BTREE,
  CONSTRAINT `FK_p3foj9yrqnmi7856n9s8mbpue` FOREIGN KEY (`CATEGORY_ID`) REFERENCES `categories` (`ID`)
) ENGINE=InnoDB
AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
;

CREATE TABLE `roles` (
...
) ENGINE=InnoDB
AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
;

CREATE TABLE `users` (
...
) ENGINE=InnoDB
AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
;

CREATE TABLE `users_roles` (
 ...
) ENGINE=InnoDB
AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
;

The generated tables are the same since Hibernate also used JPA annotations. For Hibernate, I couldn’t find the equivalent of the EclipseLink annotation [@OnCascadeDelete], which generated the SQL attribute [ON DELETE CASCADE] on the foreign key [PRODUCTS.CATEGORY_ID] (line 25). This attribute must therefore be generated manually, as it is required for testing:

 

The same must be done for the two foreign keys in the [USERS_ROLES] table:

Finally, as was done with the EclipseLink implementation, the [VERSIONING] columns in the five tables must have a default value:

 

Once this is done, verify that the following test configurations pass:

  • [spring-jdbc-generic-04.JUnitTestDao], which tests the JDBC implementation;
  • [spring-jpa-generic-JUnitTestDao-hibernate-eclipselink], which tests the Hibernate or Eclipselink JPA implementations (in this case, it will be Hibernate)

Both tests must pass.

9.2.7. Generating tables with OpenJpa

We repeat the previous procedure with an OpenJpa JPA implementation:

 

Note: Press Alt-F5 and regenerate all Maven projects;

We modify the [ConfigJpa] class that configures the [mysql-config-jpa-openjpa] project as follows:


package generic.jpa.config;

import generic.jdbc.config.ConfigJdbc;

import java.util.Map;

import javax.persistence.EntityManagerFactory;

import org.apache.tomcat.jdbc.pool.DataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Import;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.JpaVendorAdapter;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.Database;
import org.springframework.orm.jpa.vendor.OpenJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;

@Configuration
@Import({ ConfigJdbc.class })
public class ConfigJpa {

    // the JPA provider
    @Bean
    public JpaVendorAdapter jpaVendorAdapter() {
        OpenJpaVendorAdapter openJpaVendorAdapter = new OpenJpaVendorAdapter();
        openJpaVendorAdapter.setShowSql(false);
        openJpaVendorAdapter.setDatabase(Database.MYSQL);
        openJpaVendorAdapter.setGenerateDdl(true);
        return openJpaVendorAdapter;
    }
..
    // EntityManagerFactory
    @Bean
    public EntityManagerFactory entityManagerFactory(JpaVendorAdapter jpaVendorAdapter, DataSource dataSource) {
        LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
        factory.setJpaVendorAdapter(jpaVendorAdapter);
        factory.setPackagesToScan(ENTITIES_PACKAGES);
        Map<String, Object> mapJpaProperties = factory.getJpaPropertyMap();
        mapJpaProperties.put("openjpa.jdbc.MappingDefaults",
                "ForeignKeyDeleteAction=cascade,JoinForeignKeyDeleteAction=restrict");
        factory.setDataSource(dataSource);
        factory.afterPropertiesSet();
        return factory.getObject();
    }
}
  • Lines 40-41: We create a property for OpenJPA that specifies how to generate foreign keys when generating tables. Without this property, foreign keys are not generated. The [ForeignKeyDeleteAction=cascade] attribute allows the [ON DELETE CASCADE] clause to be generated on these foreign keys;

Table generation is performed by the runtime configuration named [generic-create-dbproduitscategories-openjpa], which has two Java agents;

Image

  • The first Java agent is the Spring agent already used with EclipseLink;
  • The second Java agent is provided by OpenJpa;

The SQL script for the generated database is then as follows:


SET FOREIGN_KEY_CHECKS=0;

USE `dbproduitscategories`;

CREATE TABLE `categories` (
  `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `NAME` VARCHAR(30) COLLATE utf8_general_ci NOT NULL,
  `VERSIONING` BIGINT(20) DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE,
  UNIQUE KEY `U_CTGORIS_NOM` (`NAME`) USING BTREE
) ENGINE=InnoDB
AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
;

CREATE TABLE `products` (
  `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `DESCRIPTION` VARCHAR(100) COLLATE utf8_general_ci DEFAULT NULL,
  `CATEGORY_ID` BIGINT(20) DEFAULT NULL,
  `NAME` VARCHAR(30) COLLATE utf8_general_ci NOT NULL,
  `PRICE` DOUBLE NOT NULL,
  `VERSIONING` BIGINT(20) DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE,
  UNIQUE KEY `U_PRODUTS_NOM` (`NAME`) USING BTREE,
  KEY `CATEGORY_ID` (`CATEGORY_ID`) USING BTREE,
  CONSTRAINT `products_ibfk_1` FOREIGN KEY (`CATEGORY_ID`) REFERENCES `categories` (`ID`) ON DELETE CASCADE
) ENGINE=InnoDB
AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
;

CREATE TABLE `roles` (
  `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `NAME` VARCHAR(30) COLLATE utf8_general_ci NOT NULL,
  `VERSIONING` BIGINT(20) DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE,
  UNIQUE KEY `U_ROLES_NAME` (`NAME`) USING BTREE
) ENGINE=InnoDB
AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
;

CREATE TABLE `users` (
  `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `LOGIN` VARCHAR(30) COLLATE utf8_general_ci NOT NULL,
  `NAME` VARCHAR(30) COLLATE utf8_general_ci NOT NULL,
  `PASSWORD` VARCHAR(60) COLLATE utf8_general_ci NOT NULL,
  `VERSIONING` BIGINT(20) DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE,
  UNIQUE KEY `U_USERS_LOGIN` (`LOGIN`) USING BTREE
) ENGINE=InnoDB
AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
;

CREATE TABLE `users_roles` (
  `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `VERSIONING` BIGINT(20) DEFAULT NULL,
  `ROLE_ID` BIGINT(20) NOT NULL,
  `USER_ID` BIGINT(20) NOT NULL,
  PRIMARY KEY (`ID`) USING BTREE,
  KEY `ROLE_ID` (`ROLE_ID`) USING BTREE,
  KEY `USER_ID` (`USER_ID`) USING BTREE,
  CONSTRAINT `users_roles_ibfk_2` FOREIGN KEY (`USER_ID`) REFERENCES `users` (`ID`) ON DELETE CASCADE,
  CONSTRAINT `users_roles_ibfk_1` FOREIGN KEY (`ROLE_ID`) REFERENCES `roles` (`ID`) ON DELETE CASCADE
) ENGINE=InnoDB
AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
;

This is the same as with EclipseLink. We will therefore make the same corrections to the tables. Once this is done, verify that the following test cases pass:

  • [spring-jdbc-generic-04.JUnitTestDao], which tests the JDBC implementation;
  • [spring-jpa-generic-JUnitTestDao-openjpa], which tests an OpenJPA JPA implementation;

Both runs must succeed.

9.3. Generating the [dbproduits] database

The [dbproduits] database is used by the [spring-jdbc-01 to 03] projects. It can also be generated from a JPA entity.

  • In [1], the Eclipse projects. We will be using a MySQL/EclipseLink configuration. The project for generating the [dbproduits] database is [generic-create-dbproduits];
  • in [2], the [PRODUITS] table to be generated;

Note: Press Alt-F5 and regenerate all Maven projects;

9.3.1. Maven Configuration

The Maven configuration for the [generic-create-dbproduits] project is as follows:


<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>dvp.spring.database</groupId>
    <artifactId>generic-create-dbproduits</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>generic-create-dbproduits</name>
    <description>creation of the [dbproduits] database using JPA annotations</description>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.2.3.RELEASE</version>
    </parent>

    <dependencies>
        <!-- JPA configuration for the DBMS -->
        <dependency>
            <groupId>dvp.spring.database</groupId>
            <artifactId>generic-config-jpa</artifactId>
            <version>0.0.1-SNAPSHOT</version>
        </dependency>
    </dependencies>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <start-class>spring.data.console.Main</start-class>
        <java.version>1.7</java.version>
    </properties>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-surefire-plugin</artifactId>
                <version>2.18.1</version>
            </plugin>
        </plugins>
    </build>

</project>

There is only one dependency, lines 22–26, in the project that configures the JPA layer. Ultimately, the dependencies are as follows:

  

9.3.2. The Spring configuration

  

The Spring configuration class is as follows:


package console;

import generic.jdbc.config.ConfigJdbc;
import generic.jpa.config.ConfigJpa;

import javax.persistence.EntityManagerFactory;

import org.apache.tomcat.jdbc.pool.DataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Import;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaVendorAdapter;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;

@EnableJpaRepositories(basePackages = { "console" })
@Configuration
@Import({ ConfigJpa.class })
public class AppConfig {

    // data source
    @Bean
    public DataSource dataSource() {
        // TomcatJdbc data source
        DataSource dataSource = new DataSource();
        // JDBC access configuration
        dataSource.setDriverClassName(ConfigJdbc.DRIVER_CLASSNAME);
        dataSource.setUsername(ConfigJdbc.USER_DBPRODUITS);
        dataSource.setPassword(ConfigJdbc.PASSWD_DBPRODUITS);
        dataSource.setUrl(ConfigJdbc.URL_DBPRODUITS);
        // Initial open connections
        dataSource.setInitialSize(5);
        // result
        return dataSource;
    }

    // EntityManagerFactory
    @Bean
    public EntityManagerFactory entityManagerFactory(JpaVendorAdapter jpaVendorAdapter, DataSource dataSource) {
        LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
        factory.setJpaVendorAdapter(jpaVendorAdapter);
        factory.setPersistenceUnitName("generic-jpa-entities-product-db");
        factory.setDataSource(dataSource);
        factory.afterPropertiesSet();
        return factory.getObject();
    }

}
  • line 18: we import the beans from the [ConfigJpa] class (section 7.3);
  • lines 22–35: we redefine the data source [dataSource]. In [ConfigJpa], the data source is the database [dbproduitscategories]. Here, it will be the database [dbproduits];
  • lines 38–46: we redefine the [entityManagerFactory] bean from the [ConfigJpa] class. In that class, the JPA entities were [Product, Category]. Here, it is only [Product], and it does not have the same definition as in the project that configures the JPA layer;
  • line 42: to define this new JPA entity, we reference the JPA entities defined in the [META-INF/persistence.xml] file:
  

The [persistence.xml] file is as follows:


<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">
    <persistence-unit name="generic-jpa-entities-dbproduits" transaction-type="RESOURCE_LOCAL">
        <!-- JPA entities -->
        <class>generic.jpa.entities.dbproduits.Product</class>
        <exclude-unlisted-classes>true</exclude-unlisted-classes>
    </persistence-unit>
</persistence>
  • line 6: the single JPA entity;
  • line 4: the name of the persistence unit [generic-jpa-entities-dbproduits] referenced in the [entityManagerFactory] bean;

9.3.3. The JPA entity [Product]

  

The JPA entity is defined in the [mysql-config-jpa-eclipselink] project as follows:


package generic.jpa.entities.dbproduits;

import generic.jdbc.config.ConfigJdbc;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity(name="Product1")
@Table(name = ConfigJdbc.TAB_PRODUCTS)
public class Product {

    // fields
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = ConfigJdbc.TAB_PRODUITS_ID)
    private int id;
    @Column(name = ConfigJdbc.PRODUCT_NAME, unique = true, length = 30, nullable = false)
    private String name;
    @Column(name = ConfigJdbc.PRODUCTS_CATEGORY, nullable = false)
    private int category;
    @Column(name = ConfigJdbc.TAB_PRODUITS_PRIX, nullable = false)
    private double price;
    @Column(name = ConfigJdbc.TAB_PRODUITS_DESCRIPTION, length = 100, nullable = false)
    private String description;

    // constructors
    public Product() {

    }

    public Product(int id, String name, int category, double price, String description) {
        this.id = id;
        this.name = name;
        this.category = category;
        this.price = price;
        this.description = description;
    }

    // getters and setters
    ...
}

This is a JPA definition that has now become standard. Note the following points:

  • line 12: we have given the entity a name [Product1]. By default, an entity’s name is the class name, in this case [Product]. However, because there is another JPA entity [Product] in the same project, an error was reported before execution even began. We resolved it this way;
  • line 24: the category here is simply a number;
  • there are no inter-entity relationships. We therefore have a very simple situation;

9.3.4. The executable class

  

The [CreateDatabase] class is as follows:


package console;

import org.springframework.context.annotation.AnnotationConfigApplicationContext;

public class CreateDatabase {

    public static void main(String[] args) {
        // Simply instantiate the Spring context to create the tables in the [dbproduits] database
        // You also need at least one Spring Data Repository, otherwise nothing will happen
        System.out.println("Processing...");
        new AnnotationConfigApplicationContext(AppConfig.class).close();
        System.out.println("Processing complete...");
    }

}

This is code we've seen before.

The [PRODUCTS] table is created with the following runtime configuration:

The console logs are as follows:


Processing...
16:31:17.668 [main] INFO  o.s.c.a.AnnotationConfigApplicationContext - Refreshing org.springframework.context.annotation.AnnotationConfigApplicationContext@6fadae5d: startup date [Tue Jun 02 16:31:17 CEST 2015]; root of context hierarchy
16:31:17.758 [main] INFO  o.s.b.f.s.DefaultListableBeanFactory - Overriding bean definition for bean 'entityManagerFactory': replacing [Root bean: class [null]; scope=; abstract=false; lazyInit=false; autowireMode=3; dependencyCheck=0; autowireCandidate=true; primary=false; factoryBeanName=generic.jpa.config.ConfigJpa; factoryMethodName=entityManagerFactory; initMethodName=null; destroyMethodName=(inferred); defined in class generic.jpa.config.ConfigJpa] with [Root bean: class [null]; scope=; abstract=false; lazyInit=false; autowireMode=3; dependencyCheck=0; autowireCandidate=true; primary=false; factoryBeanName=appConfig; factoryMethodName=entityManagerFactory; initMethodName=null; destroyMethodName=(inferred); defined in console.AppConfig]
16:31:17.759 [main] INFO  o.s.b.f.s.DefaultListableBeanFactory - Overriding bean definition for bean 'dataSource': replacing [Root bean: class [null]; scope=; abstract=false; lazyInit=false; autowireMode=3; dependencyCheck=0; autowireCandidate=true; primary=false; factoryBeanName=generic.jpa.config.ConfigJpa; factoryMethodName=dataSource; initMethodName=null; destroyMethodName=(inferred); defined in class generic.jpa.config.ConfigJpa] with [Root bean: class [null]; scope=; abstract=false; lazyInit=false; autowireMode=3; dependencyCheck=0; autowireCandidate=true; primary=false; factoryBeanName=appConfig; factoryMethodName=dataSource; initMethodName=null; destroyMethodName=(inferred); defined in console.AppConfig]
16:31:18.462 [main] INFO  o.s.o.j.LocalContainerEntityManagerFactoryBean - Building JPA container EntityManagerFactory for persistence unit 'generic-jpa-entities-dbproduits'
[EL Info]: server: 2015-06-02 16:31:18.624--ServerSession(817299424)--Detected server platform: org.eclipse.persistence.platform.server.NoServerPlatform.
[EL Info]: server: 2015-06-02 16:31:19.015--ServerSession(817299424)--Detected server platform: org.eclipse.persistence.platform.server.NoServerPlatform.
[EL Info]: 2015-06-02 16:31:19.021--ServerSession(817299424)--EclipseLink, version: Eclipse Persistence Services - 2.6.0.v20150309-bf26070
[EL Info]: connection: 2015-06-02 16:31:20.122--ServerSession(817299424)--/file:/D:/data/istia-1415/spring data/dvp/dvp-spring-database-04/spring-database-generic/spring-jpa/generic-create-dbproduits/target/classes_generic-jpa-entities-dbproduits login successful
16:31:20.654 [main] INFO  o.s.c.a.AnnotationConfigApplicationContext - Closing org.springframework.context.annotation.AnnotationConfigApplicationContext@6fadae5d: startup date [Tue Jun 02 16:31:17 CEST 2015]; root of context hierarchy
[EL Info]: connection: 2015-06-02 16:31:20.656--ServerSession(817299424)--/file:/D:/data/istia-1415/spring data/dvp/dvp-spring-database-04/spring-database-generic/spring-jpa/generic-create-dbproduits/target/classes_generic-jpa-entities-dbproduits logout successful
[EL Warning]: 2015-06-02 16:31:20.657--session_manager_no_partition
Work completed...

Now, let’s return to the [MyManager] client and refresh the display [1-2]:

In [3], we can see that a table has been generated. Now let’s check the database’s DDL (Domain Definition Language):


SET FOREIGN_KEY_CHECKS=0;

USE `dbproducts`;

CREATE TABLE `products` (
  `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `CATEGORY` INTEGER(11) NOT NULL,
  `DESCRIPTION` VARCHAR(100) COLLATE utf8_general_ci NOT NULL,
  `NAME` VARCHAR(30) COLLATE utf8_general_ci NOT NULL,
  `PRICE` DOUBLE NOT NULL,
  PRIMARY KEY (`ID`) USING BTREE,
  UNIQUE KEY `NAME` (`NAME`) USING BTREE
) ENGINE=InnoDB
AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
;

We do indeed get the expected table. To verify this, we will run the following configuration:

It should succeed.

9.3.6. Hibernate Generation

 

Note: Press Alt-F5 and regenerate all Maven projects;

The runtime configuration is as follows:

The SQL script generated by Hibernate is as follows:


USE `dbproduits`;

CREATE TABLE `products` (
  `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `CATEGORY` INTEGER(11) NOT NULL,
  `DESCRIPTION` VARCHAR(100) COLLATE utf8_general_ci NOT NULL,
  `NAME` VARCHAR(30) COLLATE utf8_general_ci NOT NULL,
  `PRICE` DOUBLE NOT NULL,
  PRIMARY KEY (`ID`) USING BTREE,
  UNIQUE KEY `UK_hfvjn9lp7qoo5x79uu0ump3rf` (`NAME`) USING BTREE
) ENGINE=InnoDB
AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
;

9.3.7. OpenJpa Generation

 

Note: Press Alt-F5 and regenerate all Maven projects;

The runtime configuration is as follows:

The SQL script generated by OpenJpa is as follows:


USE `dbproducts`;

CREATE TABLE `products` (
  `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `CATEGORY` INTEGER(11) NOT NULL,
  `DESCRIPTION` VARCHAR(100) COLLATE utf8_general_ci NOT NULL,
  `NAME` VARCHAR(30) COLLATE utf8_general_ci NOT NULL,
  `PRICE` DOUBLE NOT NULL,
  PRIMARY KEY (`ID`) USING BTREE,
  UNIQUE KEY `U_PRODUTS_NOM` (`NAME`) USING BTREE
) ENGINE=InnoDB
AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
;
https://tahe.developpez.com
This tutorial written by Serge Tahé is made available to the public under the terms of the Creative Commons Attribution – Non-Commercial – ShareAlike 3.0 Unported.