spring boot(学习笔记第八课)
- 数据库操作-MyBatis，Spring Data JPA，多数据源
学习内容：
- 数据库操作-MyBatis
- 数据库操作-Spring Data JPA
- 多数据源(JdbcTemplate)
1. 数据库操作-MyBatis
spring boot的操作有JdbcTemplate，MyBatis，Spring Data JPA主要这三个包。
其中，JdbcTemplate的使用方法前面讲述的一样，需要将SQL文写入到java代码中，看起来不太方便，而且不利于java代码和sql代码的分离。MyBatis应运而生，能够从repository里面分离出去sql文，将其定义到相应的xml中。这里主要练习MyBatis。
- 在
repository之中定义java的MyBatis接口，根据需求的需要定义接口方法，但是这里没有实现。@Mapper public interface BookMapper { int addBook(Book book); int deleteBookById(Integer id); int updateBookById(Book book); int getMaxBookId(); Book getBookById(Integer id); List<Book> getAllBooks(); } - 在
BookMapper接口的同级目录定义xml文件，将BookMapper的方法与SQL进行映射。
注意，这里的类名和方法名一定要与<?xml version="1.0" encoding="utf-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.example.demo.repository.BookMapper"> <insert id="addBook" parameterType="com.example.demo.entity.Book"> insert into book(name,author) values(#{name},#{author}) </insert> <delete id="deleteBookById" parameterType="int"> delete from book where id=#{id} </delete> <update id="updateBookById" parameterType="com.example.demo.entity.Book"> update book set name=#{name},author=#{author} where id=#{id} </update> <select id="getBookById" parameterType="int" resultType="com.example.demo.entity.Book"> select * from book where id=#{id} </select> <select id="getMaxBookId" resultType="int"> select max(id) from book </select> <select id="getAllBooks" resultType="com.example.demo.entity.Book"> select * from book </select> </mapper>BookMapper定义一致。 - 修改pom.xml文件
- 加入
MyBatis的依赖。
注意，依赖的包的具体版本可以直接登录<dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>3.0.3</version> </dependency>maven的官网上直接确认
>>>maven central repo - 将resources的加载路径里面加上上面的
MyBatis的xml文件路径。<resources> <resource> <directory>src/main/java</directory> <includes> <include>**/*.xml</include> </includes> </resource> <resource> <directory>src/main/resources</directory> </resource> </resources>
- 加入
- 修改
service层的代码，通过BookMapper来实现数据库操作。@Service public class BookService { @Autowired private BookMapper bookMapper; public int addBook(Book book){ return bookMapper.addBook(book); } public int updateBook(Book book){ return bookMapper.updateBookById(book); } public int deleteBookById(Integer id){ return bookMapper.deleteBookById(id); } public Book getBookById(Integer id){ return bookMapper.getBookById(id); } public List<Book> getAllBooks(){ return bookMapper.getAllBooks(); } public int getMaxBookId(){ return bookMapper.getMaxBookId(); } } - 执行
/bookOps进行数据库表book的操作。完美的将java代码和sql代码进行了分离。
查看数据库会增加一条记录。
2. 数据库操作-Spring Data JPA
JPA是Java Persistence API的缩写。前面使用了postgreSQL数据库，这里使用MySQL数据库。
-
加入对应的
Spring Data JPA依赖和MySQL的依赖。<dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>3.0.3</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.28</version> <scope>runtime</scope> </dependency> -
修改连接信息，连接
MySQL数据库。#database spring.datasource.type=com.alibaba.druid.pool.DruidDataSource spring.datasource.url=jdbc:mysql://127.0.0.1:3306/springboot spring.datasource.username=finlay spring.datasource.password=123456 spring.jpa.show-sql=true spring.jpa.database=mysql spring.jpa.hibernate.ddl-auto=update注意
spring.jpa.hibernate.ddl-auto=update，定义成update的意思是，spring boot应用程序启动的时候，没有表的时候创建处理，如果存在表，数据不清空。 -
创建
@Entity类。@Data @Entity(name = "t_book") public class Book { @Column(name="book_name",nullable = false) private String name; private Float price; private String author; @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Integer id; @Transient private String description; } -
修改
BookDao接口
比起JdbcTemplate，Sring Data JPA提供了方法的关键字，映射到了SQL的查询语句。除了下面的自动映射，Sring Data JPA同样能通过@Query(value = "***")自定义SQL文。KeyWords 方法名称举例 对应的SQL And findByNameAndAge where name=? and age=? Or findByNameOrAge where name=? or age=? Between findByAgeBetween where age between ? and ? public interface BookDao extends JpaRepository<Book, Integer> { List<Book> getBooksByAuthorStartingWith(String author); List<Book> getBooksByPriceGreaterThan(Float price); @Query(value = "select * from t_book where id =(select max(id) from t_book)", nativeQuery = true) Book getMaxIdBook(); @Query(value = "select b from t_book b where b.id>:id and b.author=:author") List<Book> getBooksByIdAndAuthor(@Param("author") String author, @Param("id") Integer id); @Query(value = "select b from t_book b where b.id<?2 and b.name like %?1%") List<Book> getBooksByIdAndName(String name,Integer id); } -
修改
BookService接口。@Service public class BookService { private BookDao bookDao; public Book addBook(Book book){ return bookDao.save(book); } public Page<Book> getBookByPage(Pageable pageable){ return bookDao.findAll(pageable); } public List<Book> getBooksByAuthorStartWith(String author){ return bookDao.getBooksByAuthorStartingWith(author); } public List<Book> getBooksByPriceGreaterThan(Float price){ return bookDao.getBooksByPriceGreaterThan(price); } public Book getMaxIdBook(){ return bookDao.getMaxIdBook(); } public List<Book> getBooksByIdAndAuthor(String author,Integer id){ return bookDao.getBooksByIdAndAuthor(author,id); } public List<Book> getBooksByIdAndName(String name,Integer id){ return bookDao.getBooksByIdAndName(name,id); } }注意，第一个方法和第二个方法使用了
JpaRepository的findAll和save方法。 -
定义
controller，进行数据库操作。@Controller public class BookController { @Autowired private BookService bookService; @GetMapping("/findAll") @ResponseBody public void findAll() { PageRequest pageable = PageRequest.of(2,3); Page<Book> page = bookService.getBookByPage(pageable); System.out.println("total pages: " + page.getTotalPages()); System.out.println("total elements: " + page.getTotalElements()); System.out.println("search result: " + page.getContent()); } @GetMapping("/search") @ResponseBody public List<Book> search(){ return bookService.getBooksByPriceGreaterThan(30F); } @GetMapping(value = "/save") @ResponseBody public String save(){ Book book = new Book(); book.setAuthor("yongchao.zhai"); book.setName("spring cloud"); book.setPrice(42F); bookService.addBook(book); return "save successfully"; } } -
启动
spring boot，看看表t_book的创建。
Hibernate: create table t_book (id integer not null auto_increment, author varchar(255), book_name varchar(255) not null, price float(23), primary key (id)) engine=InnoDB
-
对
t_book追加数据。
-
开始测试
controller，看看数据返回。
3. 多数据源(JdbcTemplate)
很多时候生产环境多是多数据库共存的。接下来练习如果配置JdbcTemplate方式的多数据库配置。
但是自动配置的会配置默认的jdbcTemplate bean，这个bean使用默认的application.properties中的数据库设置。
- 定义两个数据源（
MySQL和postgreSQL）
定义两个datasource之后，使用JdbcTemplate进行数据库操作，那么就先要必须删除spring boot jpa的相关依赖，Dao，Entity，否则，Spring Data JPA找不到默认的DataSource，会出现错误#database spring.datasource.one.type=com.alibaba.druid.pool.DruidDataSource spring.datasource.one.url=jdbc:mysql://127.0.0.1:3306/springboot spring.datasource.one.username=finlay spring.datasource.one.password=123456 spring.datasource.two.type=com.alibaba.druid.pool.DruidDataSource spring.datasource.two.url=jdbc:postgresql://127.0.0.1:5432/springboot spring.datasource.two.username=finlay spring.datasource.two.password=123456 - 为多数据库环境加入依赖。
<dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.2.9</version> </dependency> - 根据配置注入两个
DataSource。@Configuration public class DataSourceConfig { @Bean(value = "dsOne") @ConfigurationProperties(value = "spring.datasource.one") DataSource dsOne() { return DruidDataSourceBuilder.create().build(); } @Bean(value = "dsTwo") @ConfigurationProperties(value = "spring.datasource.two") DataSource dsTwo() { return DruidDataSourceBuilder.create().build(); } } - 根据注入的两个
DataSource配置两个JdbcTemplate配置bean。@Configuration public class JdbcTemplateConfig { @Qualifier(value = "dsOne") @Autowired private DataSource dataSourceOne; @Qualifier(value = "dsTwo") @Autowired private DataSource dataSourceTwo; @Bean JdbcTemplate jdbcTemplateOne(){ return new JdbcTemplate(dataSourceOne); } @Bean JdbcTemplate jdbcTemplateTwo(){ return new JdbcTemplate(dataSourceTwo); } } - 定义
controller,进行数据库的查询和数据确认。@Controller public class BookController { @Qualifier(value = "jdbcTemplateOne") @Autowired private JdbcTemplate jdbcTemplateOne; @Qualifier(value = "jdbcTemplateTwo") @Autowired private JdbcTemplate jdbcTemplateTwo; @GetMapping(value = "/datasource1") @ResponseBody public String dataSource() { List<Book> books1 = jdbcTemplateOne.query("select * from t_book", new BeanPropertyRowMapper<>(Book.class)); List<Book> books2 = jdbcTemplateTwo.query("select * from book", new BeanPropertyRowMapper<>(Book.class)); System.out.println("books1 is " + books1); System.out.println("books2 is " + books2); return "save successfully"; } } - 从
console上打印出来两个数据库的book表的数据。books1 is [Book(name=null, price=55.0, author=yongchao.zhai, id=1), Book(name=null, price=30.0, author=alex, id=2), Book(name=null, price=33.0, author=finlay, id=3), Book(name=null, price=67.0, author=marko, id=4), Book(name=null, price=40.0, author=mark, id=5), Book(name=null, price=23.0, author=allen, id=6)] books2 is [Book(name=springboot2, price=null, author=finlay2, id=10)]