jpa 连接多个mysql 数据库_jpa与多个数据库

yml 配置

server:

port: 8081 #设置端口号

spring:

datasource:

primary: #mysql 数据库

jdbc-url: jdbc:mysql://localhost:3306/logapi?&characterEncoding=utf-8&serverTimezone=UTC

username: username

password: password

driver-class-name: com.mysql.cj.jdbc.Driver

secondary: #oracle 数据库

jdbc-url: jdbc:oracle:thin:@localhost:test

username: username

password: password

driver-class-name: oracle.jdbc.driver.OracleDriver

thymeleaf:

cache: false #关闭缓存，避免修改页面需要重启

prefix: classpath:/templates/ #设置路径

suffix: .html

mode: HTML

encoding: UTF-8 #设置字符编码

servlet:

content-type: text/html;

resources:

static-locations: classpath:/META-INF/resources/,classpath:/resources/,classpath:/static/,classpath:/public/ #配置静态资源路径

devtools:

restart:

exclude: static/**,public/**

freemarker:

check-template-location: false

jpa:

database-platform: org.hibernate.dialect.MySQL5Dialect

show-sql: true #显示sql语句

hibernate:

ddl-auto: update

上面就是配置 yml 文件，连接多个数据库 primary 和 secondary 很重要，等会会用到

DataSourceConfig 配置：

package com.ky.config;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;

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;

@Configuration

public class DataSourceConfig {

@Bean(name = "primaryDataSource")

@Qualifier("primaryDataSource")

@ConfigurationProperties(prefix = "spring.datasource.primary") // application.properteis中对应属性的前缀

public DataSource dataSourcelogapi() {

return DataSourceBuilder.create().build();

}

@Bean(name = "secondaryDataSource")

@Primary

@Qualifier("secondaryDataSource")

@ConfigurationProperties(prefix = "spring.datasource.secondary") // application.properteis中对应属性的前缀

public DataSource primaryDataSource() {

return DataSourceBuilder.create().build();

}

}

然后配置 logApi 的连接

package com.ky.config;

import javax.persistence.EntityManager;

import javax.sql.DataSource;

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;

@Configuration

@EnableTransactionManagement

@EnableJpaRepositories(

entityManagerFactoryRef = "entityManagerFactoryprimary",

transactionManagerRef = "transactionManagerprimary",

basePackages = { "com.ky.mapper.logApi" })

public class MybatisDbLogApiConfig {

@Autowired

@Qualifier("primaryDataSource")

private DataSource primaryDataSource;

@Bean(name = "entityManagerprimary")

@Primary

public EntityManager entityManager(EntityManagerFactoryBuilder builder) {

return entityManagerFactoryprimary(builder).getObject().createEntityManager();

}

@Primary

@Bean(name = "entityManagerFactoryprimary")

public LocalContainerEntityManagerFactoryBean entityManagerFactoryprimary (EntityManagerFactoryBuilder builder) {

return builder

.dataSource(primaryDataSource)

.packages("com.ky.entity.logApi") //设置实体类所在位置

.persistenceUnit("primaryPersistenceUnit")

.build();

}

@Primary

@Bean(name = "transactionManagerprimary")

public PlatformTransactionManager transactionManagerSecond(EntityManagerFactoryBuilder builder) {

return new JpaTransactionManager(entityManagerFactoryprimary(builder).getObject());

}

}

然后配置 test 的连接：

package com.ky.config;

import javax.persistence.EntityManager;

import javax.sql.DataSource;

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;

@Configuration

@EnableTransactionManagement

@EnableJpaRepositories(

entityManagerFactoryRef = "entityManagerFactorysecondary",

transactionManagerRef = "transactionManagersecondary",

basePackages = { "com.ky.mapper.crm" })

public class MybatisDbCrmConfig {

@Autowired

@Qualifier("secondaryDataSource")

private DataSource secondaryDataSource;

@Bean(name = "entityManagersecondary")

public EntityManager entityManager(EntityManagerFactoryBuilder builder) {

return entityManagerFactorysecondary(builder).getObject().createEntityManager();

}

@Bean(name = "entityManagerFactorysecondary")

public LocalContainerEntityManagerFactoryBean entityManagerFactorysecondary (EntityManagerFactoryBuilder builder) {

return builder

.dataSource(secondaryDataSource)

.packages("com.ky.entity.crm") //设置实体类所在位置

.persistenceUnit("secondaryPersistenceUnit")

.build();

}

@Bean(name = "transactionManagersecondary")

public PlatformTransactionManager transactionManagerSecond(EntityManagerFactoryBuilder builder) {

return new JpaTransactionManager(entityManagerFactorysecondary(builder).getObject());

}

}

这里只用改一下实体的位置就行了

之后我就写一个实体类的配置，其他都一样

logPool 实体类配置：

package com.ky.entity.logApi;

import java.io.Serializable;

import java.util.Date;

import javax.persistence.Column;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.Id;

import javax.persistence.Table;

import com.fasterxml.jackson.annotation.JsonFormat;

import lombok.Data;

@Data

@Entity

@Table(name = "log_pool")

public class LogPool implements Serializable {

/**

*

*/

private static final long serialVersionUID = 8210837130319782243L;

@Id

@GeneratedValue

private Integer id;

private String ip;

@Column(name = "api_name")

private String apiName;

@Column(name = "step_state")

private String stepState;

private String step1;

private String step2;

private String step3;

private String step4;

private Date date1;

private Date date2;

private Date date3;

private Date date4;

private String status;

private String message;

private String state;

private String memo;

}

其余的就和 jpa 的操作一样了

这里注意的是：如果不想写 sql 语句，又想使用高级查询，你得这么写

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

CriteriaQuery query = criteriaBuilder.createQuery(LogPool.class);

Root logPoolRoot = query.from(LogPool.class);

List predicatesList = new ArrayList<>();

if(logPool.getIp() != null) {

predicatesList.add(

criteriaBuilder.and(

criteriaBuilder.like(

logPoolRoot.get("ip"),

"%" + logPool.getIp() + "%")));

}

if(logPool.getApiName() != null) {

predicatesList.add(

criteriaBuilder.and(

criteriaBuilder.like(

logPoolRoot.get("apiName"),

"%" + logPool.getApiName() + "%")));

}

query.where(predicatesList.toArray(new Predicate[predicatesList.size()]));

TypedQuery typedQuery = entityManager.createQuery(query);

List resultList = typedQuery.getResultList();

这样，就可以写出动态模糊或者如果为空就不查询了