SpringBoot integrates JPA and configures multiple databases

Posted by jbulaswad on Fri, 06 Mar 2020 01:59:50 +0100

Write before

SpringBoot is easy to create projects, and data access throws away a lot of tedious configuration. In the previous series of blogs, I've taught you how to use SpringBoot for data access. It talks about integrating JDBC, MyBatis, and JPA.In my own actual development, I am more used to using JPA for data access if there are no restrictions, so here I am writing a blog post to teach you how to use SpringBoot to integrate JPA and configure multiple databases. If it is helpful, remember to pay attention and praise.

Get ready

Before we start, we need to create a project. The project was created using Idea's Spring Initializr. When you select the SpringBoot scenario, check the Web, Spring Data JPA, MySQL Driver three, and then the project is created successfully.

Main Profile

If we only have access to one database, we only need to configure the database simply and provide the appropriate account and password. However, it is not a problem to have multiple databases, which is equivalent to one more configuration. All the configuration is as follows

application.properties

#Database Unified Configuration
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.hbm2ddl.auto=update
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
spring.jpa.properties.hibernate.format_sql=true

#Master database
spring.datasource.primary.jdbc-url=jdbc:mysql://localhost/ubiquity?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.datasource.primary.username=root
spring.datasource.primary.password=123456
spring.datasource.primary.driver-class-name=com.mysql.jdbc.Driver

#Subdatabase
spring.datasource.secondary.jdbc-url=jdbc:mysql://localhost/ubiquity_vote?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.datasource.secondary.username=root
spring.datasource.secondary.password=123456
spring.datasource.secondary.driver-class-name=com.mysql.jdbc.Driver

Configuration Class

We all know that the configurations we write in the configuration file require us to override the default configuration by injecting the configuration classes in order to take effect, so we can think that since you need to use multiple databases, there will naturally be multiple database-related configuration classes (in this blog post example, I use two databases, so there are two data pairs)Set Class)

We know that we first write the components of two configuration databases in a configuration-only class, and then separate their component names as follows.

DataSourceConfig.java

package com.dbc.ubiquity.Config;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateProperties;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;
import java.util.Map;

@Configuration
public class DataSourceConfig {
    @Autowired
    private JpaProperties jpaProperties;
    @Autowired
    private HibernateProperties hibernateProperties;

    @Bean(name = "primaryDataSource")
    @Primary
    @ConfigurationProperties("spring.datasource.primary")
    public DataSource firstDataSource(){
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "secondaryDataSource")
    @ConfigurationProperties("spring.datasource.secondary")
    public DataSource secondDataSource(){
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "vendorProperties")
    public Map<String, Object> getVendorProperties() {
        return hibernateProperties.determineHibernateProperties(
                jpaProperties.getProperties(), new HibernateSettings());
    }
}

We then wrote a configuration class for both databases, which has very similar code, and I'm sure you can understand something after you hit it once.

PrimaryConfig.java

package com.dbc.ubiquity.Config;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.Map;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "entityManagerFactoryPrimary",
        transactionManagerRef = "transactionManagerPrimary",
        basePackages = {"com.dbc.ubiquity.Repository.Primary"}//Location of Dao Layer
)
public class PrimaryConfig {
    @Autowired
    @Qualifier("primaryDataSource")
    private DataSource primaryDataSource;

    @Autowired
    @Qualifier("vendorProperties")
    private Map<String, Object> vendorProperties;

    @Bean(name = "entityManagerFactoryPrimary")
    @Primary
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary(EntityManagerFactoryBuilder builder){
        return builder
                .dataSource(primaryDataSource)
                .properties(vendorProperties)
                .packages("com.dbc.ubiquity.Model.Primary")//Location of entity classes
                .persistenceUnit("primaryPersistenceUnit")
                .build();
    }

    @Bean(name = "entityManagerPrimary")
    @Primary
    public EntityManager entityManager(EntityManagerFactoryBuilder builder){
        return entityManagerFactoryPrimary(builder).getObject().createEntityManager();
    }

    @Bean(name = "transactionManagerPrimary")
    @Primary
    PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder){
        return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject());
    }
}

SecondaryConfig.java

package com.dbc.ubiquity.Config;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.Map;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "entityManagerFactorySecondary",
        transactionManagerRef = "transactionManagerSecondary",
        basePackages = {"com.dbc.ubiquity.Repository.Secondary"}
)
public class SecondaryConfig {
    @Autowired
    @Qualifier("secondaryDataSource")
    private DataSource secondaryDataSource;

    @Autowired
    @Qualifier("vendorProperties")
    private Map<String, Object> vendorProperties;

    @Bean(name = "entityManagerFactorySecondary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactorySecondary(EntityManagerFactoryBuilder builder){
        return builder
                .dataSource(secondaryDataSource)
                .properties(vendorProperties)
                .packages("com.dbc.ubiquity.Model.Secondary")
                .persistenceUnit("secondaryPersistenceUnit")
                .build();
    }

    @Bean(name = "entityManagerSecondary")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder){
        return entityManagerFactorySecondary(builder).getObject().createEntityManager();
    }

    @Bean(name = "transactionManagerSecondary")
    PlatformTransactionManager transactionManagerSecondary(EntityManagerFactoryBuilder builder){
        return new JpaTransactionManager(entityManagerFactorySecondary(builder).getObject());
    }
}

Entity Class

First I'll put the catalog structure of the entity class here to make it easier to write later. Then I'll talk about the creation of the entity class. Idea can be used here to help us reverse the generation of the entity class, that is, to automatically generate the entity class according to the database tables that have been created in the database. However, the entity class generated in this way does not conform to the serialization format that JPA is now written in.Of course, you can directly change the template generated by the entity class, which is correct and can be changed. I will not repeat how to change it here, Baidu can know it), so I will create it manually. The advantage of manual creation is to have a deeper understanding of the knowledge points, of course, it takes more time to tap.

User.java

package com.dbc.ubiquity.Entity.Primary;

import javax.persistence.*;
import java.io.Serializable;

@Entity
@Table(name = "USER", schema = "ubiquity", catalog = "")
public class User implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private long id;
    @Column(nullable = false, unique = true)
    private String userName;
    @Column(nullable = false)
    private String passWord;
    @Column(nullable = false, unique = true)
    private String email;
    @Column(nullable = true, unique = true)
    private String nickName;
    @Column(nullable = false)
    private String regTime;

    public User() {
    }

    public User(String userName, String passWord, String email, String nickName, String regTime) {
        this.userName = userName;
        this.passWord = passWord;
        this.email = email;
        this.nickName = nickName;
        this.regTime = regTime;
    }

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getPassWord() {
        return passWord;
    }

    public void setPassWord(String passWord) {
        this.passWord = passWord;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getNickName() {
        return nickName;
    }

    public void setNickName(String nickName) {
        this.nickName = nickName;
    }

    public String getRegTime() {
        return regTime;
    }

    public void setRegTime(String regTime) {
        this.regTime = regTime;
    }
}

Userq.java

package com.dbc.ubiquity.Entity.Secondary;

import javax.persistence.*;
import java.io.Serializable;

@Entity
@Table(name = "USERQ", schema = "ubiquity_vote", catalog = "")
public class Userq implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private long id;
    @Column(nullable = false, unique = true)
    private String userName;
    @Column(nullable = false)
    private String passWord;
    @Column(nullable = false, unique = true)
    private String email;
    @Column(nullable = true, unique = true)
    private String nickName;
    @Column(nullable = false)
    private String regTime;

    public Userq() {
    }

    public Userq(String userName, String passWord, String email, String nickName, String regTime) {
        this.userName = userName;
        this.passWord = passWord;
        this.email = email;
        this.nickName = nickName;
        this.regTime = regTime;
    }

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getPassWord() {
        return passWord;
    }

    public void setPassWord(String passWord) {
        this.passWord = passWord;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getNickName() {
        return nickName;
    }

    public void setNickName(String nickName) {
        this.nickName = nickName;
    }

    public String getRegTime() {
        return regTime;
    }

    public void setRegTime(String regTime) {
        this.regTime = regTime;
    }
}

When I write here, I'll tell you what some of the configurations in the main configuration file are, as follows

It has other properties as follows:

  • ddl-auto:create - Every time you run the program, no tables will create new tables, and any data in the tables will be emptied
  • ddl-auto:create-drop--empties the table every time the program ends
  • ddl-auto:update - Every time you run the program, no table will create a new table, no data will be emptied, only updated
  • ddl-auto:validate - Run the program to verify that the data has the same field type as the database and that differences will cause errors

Dao Layer

package com.dbc.ubiquity.Repository.Primary;

import com.dbc.ubiquity.Entity.Primary.User;
import org.springframework.data.jpa.repository.JpaRepository;

public interface UserPrimaryPository extends JpaRepository<User, Long> {
    User findById(long id);
    User findByUserName(String userName);
    User findByUserNameOrEmail(String username, String email);
}


package com.dbc.ubiquity.Repository.Secondary;

import com.dbc.ubiquity.Entity.Secondary.Userq;
import org.springframework.data.jpa.repository.JpaRepository;

public interface UserSecondaryPository extends JpaRepository<Userq, Long> {
    Userq findById(long id);
    Userq findByUserName(String userName);
    Userq findByUserNameOrEmail(String username, String email);
}

test

This concludes our multi-database configuration process, and finally comes the testing phase. Let's verify that our configuration is useful.The contents of the test class are as follows:

package com.dbc.ubiquity;

import com.dbc.ubiquity.Entity.Primary.User;
import com.dbc.ubiquity.Entity.Secondary.Userq;
import com.dbc.ubiquity.Repository.Primary.UserPrimaryPository;
import com.dbc.ubiquity.Repository.Secondary.UserSecondaryPository;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import javax.annotation.Resource;
import java.text.DateFormat;
import java.util.Date;

@SpringBootTest
class UbiquityApplicationTests {
	@Resource
	private UserPrimaryPository userPrimaryPository;
	@Resource
	private UserSecondaryPository userSecondaryPository;

	@Test
	public void testSave() throws Exception{
		Date date = new Date();
		DateFormat dateFormat = DateFormat.getDateTimeInstance(DateFormat.LONG, DateFormat.LONG);
		String formattedDate = dateFormat.format(date);
		userPrimaryPository.save(new User("aa", "aa123456","aa@126.com", "aa",  formattedDate));
		userPrimaryPository.save(new User("bb", "bb123456","bb@126.com", "bb",  formattedDate));
		userSecondaryPository.save(new Userq("cc", "cc123456","cc@126.com", "cc",  formattedDate));
	}

	@Test
	public void testDelete() throws Exception {
		userPrimaryPository.deleteAll();
		userSecondaryPository.deleteAll();
	}

	@Test
	public void testBaseQuery() {
		Date date = new Date();
		DateFormat dateFormat = DateFormat.getDateTimeInstance(DateFormat.LONG, DateFormat.LONG);
		String formattedDate = dateFormat.format(date);
		User user=new User("ff", "ff123456","ff@126.com", "ff",  formattedDate);
		Userq userq=new Userq("ff", "ff123456","ff@126.com", "ff",  formattedDate);
		userPrimaryPository.findAll();
		userSecondaryPository.findById(3l);
		userSecondaryPository.save(userq);
		user.setId(2l);
		userPrimaryPository.delete(user);
		userPrimaryPository.count();
		userSecondaryPository.findById(3l);
	}

	@Test
	void contextLoads() {
	}

}


109 original articles were published. 556 won. 1.3 million visits+
Private letter follow

Topics: Spring Java Database JDBC

Hot Topics

©2024 Programmer Think