spring|spring-boot和JPA多数据源整合 spring|boot

今天弄一下spring-boot和jpa的多数据源整合
Jpa（Java Persistence API）Java持久化API，它是一套ORM规范
只是一套规范
Spring Boot中使用的Jpa实际上是Spring Data Jpa，Spring Data是Spring家族的一个子项目，用于简化SQL和NoSQL的访问，在Spring Data中，只要你的方法名称符合规范，它就知道你想干嘛，不需要自己再去写SQL。
接下来我们简单来弄下，直接
在idea里创建spring-boot工程，勾选web里的web和sql里的JPA和mysql依赖

文章图片

然后在依赖里，写一下mysql的版本，一般五点几就行了，再添加Druid依赖
依赖如下

4.0.0org.springframework.boot spring-boot-starter-parent 2.1.5.RELEASE com.liy jpa 0.0.1-SNAPSHOT jpa Demo project for Spring Boot1.8 org.springframework.boot spring-boot-starter-data-jpa org.springframework.boot spring-boot-starter-web mysql mysql-connector-java runtime 5.1.27 com.alibaba druid-spring-boot-starter 1.1.10 org.springframework.boot spring-boot-starter-test test org.springframework.boot spring-boot-maven-plugin

然后写配置信息
application.properties

spring.datasource.one.username=root spring.datasource.one.password=root spring.datasource.one.url=jdbc:mysql:///again spring.datasource.one.driver-class-name=com.mysql.jdbc.Driver spring.datasource.one.type=com.alibaba.druid.pool.DruidDataSourcespring.datasource.two.username=root spring.datasource.two.password=root spring.datasource.two.url=jdbc:mysql:///demo spring.datasource.two.driver-class-name=com.mysql.jdbc.Driver spring.datasource.two.type=com.alibaba.druid.pool.DruidDataSource#数据库平台 spring.jpa.properties.database-platform=mysql #如果再次运行，检查表是否要更新 spring.jpa.properties.hibernate.ddl-auto=update #sql信息打印 spring.jpa.properties.show-sql=true #定义数据库的方言 spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL57Dialectspring.jpa.properties.database=mysql

写个bean类。写好bean类就可以运行下工程，它会自动在数据库里生成相对应的表

package com.liy.bean; import javax.persistence.*; //生成数据库表的关键注解，后面的name是表名 @Entity(name = "book") public class Book { //必须写个id @Id //也必须设置为主键，以及自增长 @GeneratedValue(strategy = GenerationType.IDENTITY) private int id; //给他起别名，设置约束什么的也是@Column注解里去设置 @Column(name = "bookname") private String username; private String auther; public Book() { super(); }public Book(String username, String auther) { this.username = username; this.auther = auther; }@Override protected Object clone() throws CloneNotSupportedException { return super.clone(); }@Override protected void finalize() throws Throwable { super.finalize(); }@Override public String toString() { return "Book{" + "id=" + id + ", username='" + username + '\'' + ", auther='" + auther + '\'' + '}'; }public int getId() { return id; }public void setId(int id) { this.id = id; }public String getUsername() { return username; }public void setUsername(String username) { this.username = username; }public String getAuther() { return auther; }public void setAuther(String auther) { this.auther = auther; } }

【spring|spring-boot和JPA多数据源整合】然后写个区分两个数据的DataSource配置类
DataSourceConfig

@Configuration public class DataSourceConfig {@Bean @ConfigurationProperties(prefix = "spring.datasource.one") @Primary DataSource dsOne(){ return DruidDataSourceBuilder.create().build(); }@Bean @ConfigurationProperties(prefix = "spring.datasource.two") DataSource dsTwo(){ return DruidDataSourceBuilder.create().build(); } } 然后写两个jpa的配置类，分别对应两个数据源JpaConfig@Configuration @EnableJpaRepositories(basePackages = "com.liy.dao1", entityManagerFactoryRef = "localContainerEntityManagerFactoryBeanOne", transactionManagerRef = "platformTransactionManagerOne") public class JpaConfig {@Autowired @Qualifier(value = "https://www.it610.com/article/dsOne") DataSource dsOne; @Autowired JpaProperties jr; @Bean @Primary LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBeanOne(EntityManagerFactoryBuilder builder){ return builder.dataSource(dsOne) .properties(jr.getProperties()) .packages("com.liy.bean") .persistenceUnit("pu1") .build(); }@Bean @Primary PlatformTransactionManager platformTransactionManagerOne(EntityManagerFactoryBuilder builder){ LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBean = localContainerEntityManagerFactoryBeanOne(builder); return new JpaTransactionManager(localContainerEntityManagerFactoryBean.getObject()); } }

JpaConfig1 ,其中代码基本一样，要在一个配置类中的两个方法都加上@Primary注解，我都加在上面那个了

@Configuration @EnableJpaRepositories(basePackages = "com.liy.dao2", entityManagerFactoryRef = "localContainerEntityManagerFactoryBeanTwo", transactionManagerRef = "platformTransactionManagerTwo") public class JpaConfig1 {@Autowired @Qualifier(value = "https://www.it610.com/article/dsTwo") DataSource dsTwo; @Autowired JpaProperties jr; @Bean LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBeanTwo(EntityManagerFactoryBuilder builder){ return builder.dataSource(dsTwo) .properties(jr.getProperties()) .packages("com.liy.bean") .persistenceUnit("pu2") .build(); }@Bean PlatformTransactionManager platformTransactionManagerTwo(EntityManagerFactoryBuilder builder){ LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBean = localContainerEntityManagerFactoryBeanTwo(builder); return new JpaTransactionManager(localContainerEntityManagerFactoryBean.getObject()); } }

然后根据上面JpaConfig的扫描负责的包来写两个dao包

文章图片

BookDao1

package com.liy.dao1; import com.liy.bean.Book; import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.data.jpa.repository.Modifying; import org.springframework.data.jpa.repository.Query; import org.springframework.data.repository.query.Param; import org.springframework.transaction.annotation.Transactional; import java.util.List; public interface BookDao1 extends JpaRepository {//方法名可以靠拼接，相当于自定义sql public List findBookByIdGreaterThanAndAndUsernameContains(Integer id,String usernames); @Query(value = "https://www.it610.com/article/select count(*) from book",nativeQuery = true) public long TotalCount(); //如果sql语句不是查询，那么必须加@Modifying注解 //另外在对数据库进行DML(update,delete,insert)操作时，必须加上事物，也就是@Transactional //@Transactional记得不要导错包 //import org.springframework.transaction.annotation.Transactional; @Query(value = "https://www.it610.com/article/update book set auther=:autherwhere id=:id",nativeQuery = true) @Modifying @Transactional public int update(@Param("id") int id,@Param("auther") String auther); @Query(value = "https://www.it610.com/article/update book set bookname=:booknamewhere id=:id",nativeQuery = true) @Modifying public int update1(@Param("id") int id ,@Param("bookname") String bookname); }

BookDao2

package com.liy.dao2; import com.liy.bean.Book; import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.data.jpa.repository.Modifying; import org.springframework.data.jpa.repository.Query; import org.springframework.data.repository.query.Param; import org.springframework.transaction.annotation.Transactional; public interface BookDao2 extends JpaRepository {@Query(value = "https://www.it610.com/article/update book set auther=:autherwhere id=:id",nativeQuery = true) @Modifying @Transactional public int update1(@Param("id") int id,@Param("auther") String auther); }

然后在spring-boot自带的测试类里进行测试

package com.liy; import com.liy.bean.Book; import com.liy.dao1.BookDao1; import com.liy.dao2.BookDao2; import com.liy.service.BookService; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.data.domain.Page; import org.springframework.data.domain.PageRequest; import org.springframework.data.domain.Sort; import org.springframework.test.context.junit4.SpringRunner; import java.util.List; @RunWith(SpringRunner.class) @SpringBootTest public class JpaApplicationTests {@Autowired BookDao1 db1; @Autowired BookDao2 db2; @Test public void updateMany(){ db1.update(3,"liy1"); db2.update1(3,"liy1"); }@Test public void TestFind() {List a = db1.findBookByIdGreaterThanAndAndUsernameContains(1, "a"); System.out.println(a); long l = db1.TotalCount(); System.out.println(l); db1.update(3,"aaaa"); }@Test public void contextLoads() { Book book = new Book("老夫子", "afaf"); db1.save(book); }@Test public void update(){ Book book = new Book("老夫子", "老夫"); book.setId(1); db1.saveAndFlush(book); }@Test public void find(){List all = db1.findAll(Sort.by(new Sort.Order(Sort.Direction.DESC, "id"))); System.out.println(all); }@Test public void findPage(){ PageRequest data = https://www.it610.com/article/PageRequest.of(0, 2); System.out.println(data); Page page = db1.findAll(data); System.out.println(page.getNumberOfElements()); //当前记录数 System.out.println(page.getNumber()); // System.out.println(page.getSize()); //每页应该查到的记录数 System.out.println(page.isLast()); //是否是最后一页 System.out.println(page.isFirst()); //是否是第一页 System.out.println(page.getTotalPages()); //总页数 System.out.println(page.getTotalElements()); //总记录数 System.out.println(page.getContent()); //当前页的数据} }

数据库里