Springboot开心学习（第九天）

最新推荐文章于 2020-12-10 11:50:24 发布

咖喱东东

最新推荐文章于 2020-12-10 11:50:24 发布

阅读量1.3k

点赞数

分类专栏： Springboot 文章标签： spring boot jpa 数据库 mysql

本文链接：https://blog.csdn.net/qq_39410381/article/details/107681098

版权

Springboot 专栏收录该内容

21 篇文章 0 订阅

订阅专栏

文章目录

1. SpringBoot整合Spring Data JPA
2. SpringBoot整合Spring Data JPA多数据源

1. SpringBoot整合Spring Data JPA

新建SpringBoot项目，依赖如下：
在这里插入图片描述
修改pom.xml

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.10</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
    <version>5.1.27</version>
</dependency>

整体结构：
在这里插入图片描述
修改application.properties

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.password=123456
spring.datasource.username=root
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/sky

spring.jpa.show-sql=true
spring.jpa.database=mysql
spring.jpa.database-platform=mysql
spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL57Dialect

Book

package org.sky.jpa.bean;

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

/**
 * @author sky
 * @date 2020/7/30 14:21
 */
@Entity(name = "t_book")
public class Book {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String author;
    private String name;

    public Book() {
    }

    public Book(String author, String name) {
        this.author = author;
        this.name = name;
    }

    @Override
    public String toString() {
        return "Book{" +
                "id=" + id +
                ", author='" + author + '\'' +
                ", name='" + name + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

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

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

BookDao

package org.sky.jpa.dao;

import org.sky.jpa.bean.Book;
import org.springframework.data.jpa.repository.JpaRepository;

/**
 * @author sky
 * @date 2020/7/30 14:30
 */
public interface BookDao extends JpaRepository<Book, Integer> {

}

启动项目，数据库就会生成对应的表:
在这里插入图片描述

1.1 新增数据

JpaApplicationTests

package org.sky.jpa;

import org.junit.jupiter.api.Test;
import org.sky.jpa.bean.Book;
import org.sky.jpa.dao.BookDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class JpaApplicationTests {

    @Autowired
    BookDao bookDao;

    @Test
    void add() {
        Book book1 = new Book("sky", "China");
        Book book2 = new Book("罗贯中", "三国演义");
        Book book3 = new Book("曹雪芹", "红楼梦");
        Book book4 = new Book("施耐庵", "水浒传");
        Book book5 = new Book("吴承恩", "西游记");
        Book book6 = new Book("鲁迅", "狂人日记");
        bookDao.save(book1);
        bookDao.save(book2);
        bookDao.save(book3);
        bookDao.save(book4);
        bookDao.save(book5);
        bookDao.save(book6);
    }

}

结果：
在这里插入图片描述

1.2 修改

    @Test
    void update(){
        Book book = new Book("sky", "无著作");
        book.setId(1);
        bookDao.saveAndFlush(book);
    }

结果：
在这里插入图片描述

1.3 删除操作

    @Test
    void delete(){
        bookDao.deleteById(1);
    }

结果：
在这里插入图片描述

1.4 查询操作

1.4.1 简单查询

    @Test
    void query1(){
        Optional<Book> byId = bookDao.findById(2);
        System.out.println(byId);
        List<Book> all = bookDao.findAll();
        System.out.println(all);
    }

结果：
在这里插入图片描述

1.4.2 排序查询

    @Test
    void query2(){

        List<Book> bookList = bookDao.findAll(Sort.by(Sort.Direction.DESC, "id"));
        System.out.println(bookList);
    }

结果：
在这里插入图片描述

1.4.3 分页查询

    @Test
    void query3(){
        PageRequest pageable = PageRequest.of(0, 2);
        Page<Book> page = bookDao.findAll(pageable);
        System.out.println("总记录数："+page.getTotalElements());
        System.out.println("当前页记录数："+page.getNumberOfElements());
        System.out.println("每页记录数："+page.getSize());
        System.out.println("总页数："+page.getTotalPages());
        System.out.println("查询结果："+page.getContent());
        System.out.println("当前页（从0开始）："+page.getNumber());
        System.out.println("是否为首页："+page.isFirst());
        System.out.println("是否为尾页："+page.isLast());
    }

结果：
在这里插入图片描述

1.4.4 Spring Data JPA关键字定义查询

BookDao

public interface BookDao extends JpaRepository<Book, Integer> {
    List<Book> findBookByIdGreaterThan(Integer id);
}

注意：根据提示来写

    @Test
    void query4(){
        System.out.println(bookDao.findBookByIdGreaterThan(3));
    }

结果：
在这里插入图片描述

1.4.5 Spring Data JPA自定义查询SQL

BookDao

public interface BookDao extends JpaRepository<Book, Integer> {

    List<Book> findBookByIdGreaterThan(Integer id);

    @Query(value = "select * from t_book where id=(select max(id) from t_book)", nativeQuery = true)
    Book findMaxIdBook();
}

    @Test
    void query5(){
        System.out.println(bookDao.findMaxIdBook());
    }

结果：
在这里插入图片描述

1.4.5 Spring Data JPA自定义数据修改

BookDao

public interface BookDao extends JpaRepository<Book, Integer> {

    List<Book> findBookByIdGreaterThan(Integer id);

    @Query(value = "select * from t_book where id=(select max(id) from t_book)", nativeQuery = true)
    Book findMaxIdBook();

    @Query(value = "insert into t_book (author, name) values(?2, ?1)", nativeQuery = true)
    @Modifying
    @Transactional
    Integer addBook(String name, String author);

    @Query(value = "insert into t_book (author, name) values(:author, :name)", nativeQuery = true)
    @Modifying
    @Transactional
    Integer addBook2(String name, String author);
}

    @Test
    void test(){
        Integer back1 = bookDao.addBook("呐喊", "鲁迅");
        System.out.println(back1);
        Integer back2 = bookDao.addBook2("朝花夕拾", "鲁迅");
        System.out.println(back2);
    }

结果：
在这里插入图片描述

2. SpringBoot整合Spring Data JPA多数据源

创建新的项目，依赖跟上一节一致、pom文件修改一致、Book一致

整体结构：
在这里插入图片描述
application.properties

spring.datasource.one.password=123456
spring.datasource.one.url=jdbc:mysql://127.0.0.1:3306/sky
spring.datasource.one.username=root
spring.datasource.one.type=com.alibaba.druid.pool.DruidDataSource

spring.datasource.two.password=123456
spring.datasource.two.url=jdbc:mysql://127.0.0.1:3306/sky2
spring.datasource.two.username=root
spring.datasource.two.type=com.alibaba.druid.pool.DruidDataSource

spring.jpa.properties.hibernate.ddl-auto=update
spring.jpa.properties.database-platform=mysql
spring.jpa.properties.show-sql=true
spring.jpa.properties.database=mysql
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL57Dialect

DataSourceConfig

package org.sky.jps2.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;

/**
 * @author sky
 * @date 2020/7/30 16:28
 */
@Configuration
public class DataSourceConfig {
    @Bean
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.one")
    DataSource dsOne(){
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.two")
    DataSource dsTwo(){
        return DruidDataSourceBuilder.create().build();
    }
}

JpaConfig1

package org.sky.jps2.config;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
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 javax.sql.DataSource;

/**
 * @author sky
 * @date 2020/7/30 16:30
 */
@Configuration
@EnableJpaRepositories(basePackages = "org.sky.jps2.dao1", entityManagerFactoryRef =
        "localContainerEntityManagerFactoryBean1", transactionManagerRef = "platformTransactionManager1")
public class JpaConfig1 {
    @Autowired
    @Qualifier("dsOne")
    DataSource dsOne;

    @Autowired
    JpaProperties jpaProperties;

    @Bean
    @Primary
    LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBean1(EntityManagerFactoryBuilder builder) {
        return builder.dataSource(dsOne)
                .properties(jpaProperties.getProperties())
                .persistenceUnit("pu1")
                .packages("org.sky.jps2.bean")
                .build();

    }

    @Bean
    PlatformTransactionManager platformTransactionManager1(EntityManagerFactoryBuilder builder){
        return new JpaTransactionManager(localContainerEntityManagerFactoryBean1(builder).getObject());
    }
}

JpaConfig2

package org.sky.jps2.config;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
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 javax.sql.DataSource;

/**
 * @author sky
 * @date 2020/7/30 16:30
 */
@Configuration
@EnableJpaRepositories(basePackages = "org.sky.jps2.dao2", entityManagerFactoryRef =
        "localContainerEntityManagerFactoryBean2", transactionManagerRef = "platformTransactionManager2")
public class JpaConfig2 {
    @Autowired
    @Qualifier("dsTwo")
    DataSource dsTwo;

    @Autowired
    JpaProperties jpaProperties;

    @Bean
    LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBean2(EntityManagerFactoryBuilder builder) {
        return builder.dataSource(dsTwo)
                .properties(jpaProperties.getProperties())
                .persistenceUnit("pu2")
                .packages("org.sky.jps2.bean")
                .build();

    }

    @Bean
    PlatformTransactionManager platformTransactionManager2(EntityManagerFactoryBuilder builder){
        return new JpaTransactionManager(localContainerEntityManagerFactoryBean2(builder).getObject());
    }
}

BookDao1

package org.sky.jps2.dao1;

import org.sky.jps2.bean.Book;
import org.springframework.data.jpa.repository.JpaRepository;

/**
 * @author sky
 * @date 2020/7/30 16:38
 */
public interface BookDao1 extends JpaRepository<Book, Integer> {
}

BookDao2

package org.sky.jps2.dao2;

import org.sky.jps2.bean.Book;
import org.springframework.data.jpa.repository.JpaRepository;

/**
 * @author sky
 * @date 2020/7/30 16:38
 */
public interface BookDao2 extends JpaRepository<Book, Integer> {
}

Jps2ApplicationTests

package org.sky.jps2;

import org.junit.jupiter.api.Test;
import org.sky.jps2.bean.Book;
import org.sky.jps2.dao1.BookDao1;
import org.sky.jps2.dao2.BookDao2;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest
class Jps2ApplicationTests {

    @Autowired
    BookDao1 bookDao1;
    @Autowired
    BookDao2 bookDao2;
    @Test
    void contextLoads() {
        List<Book> all = bookDao1.findAll();
        System.out.println(all);
        List<Book> all1 = bookDao2.findAll();
        System.out.println(all1);
    }

}