springboot学习笔记|第二篇:Spring Boot+数据库

1.整合JdbcTemplate

• 创建数据库

create table book
(
    id     int auto_increment
        primary key,
    name   varchar(128) null,
    author varchar(64)  null
);

INSERT INTO study.book (id, name, author) VALUES (1, '新神雕', '金大侠');

• 引入依赖

<dependency>
     <groupId>org.springframework.boot</groupId>
     <artifactId>spring-boot-starter-jdbc</artifactId>
   </dependency>
   <dependency>
     <groupId>org.springframework.boot</groupId>
     <artifactId>spring-boot-starter-web</artifactId>
   </dependency>
   <dependency>
     <groupId>mysql</groupId>
     <artifactId>mysql-connector-java</artifactId>
     <version>8.0.16</version>
   </dependency>
   <dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>druid</artifactId>
     <version>1.1.19</version>
   </dependency>

• 配置数据库

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    url: jdbc:mysql://localhost:3306/study
    username: root
    password: root

• 创建实体

@Data
public class Book {

  /**id*/
  private int id;

  /**书名*/
  private String name;

  /**作者*/
  private String author;
}

• 创建数据库访问层

@Repository
public class JdbcBookMapper {

 @Resource
 private JdbcTemplate jdbcTemplate;

 public int addBook(Book book) {
   return jdbcTemplate
       .update("insert  into book (name,author) values (?,?)", book.getName(), book.getAuthor());
 }

 public int updataBook(Book book) {
   return jdbcTemplate
       .update("update book set name=? ,author=? where id=?", book.getName(), book.getAuthor(),
           book.getId());
 }

 public int deleteBookById(Integer id) {
   return jdbcTemplate.update("delete  from book where id=?", id);
 }

 public Book getBookById(Integer id) {
   return jdbcTemplate
       .queryForObject("select * from book where id=?", new BeanPropertyRowMapper<>(Book.class),
           id);
 }

 public List<Book> getAllBooks() {
   return jdbcTemplate.query("select * from book", new BeanPropertyRowMapper<>(Book.class));
 }
}

完成上述配置，在加上Controller，调用Service,最后通过Service层调用dao层，即可完成数据库的增删改查操作,代码较简单，就不作演示。

2.整合MyBatis

• 引入依赖

<dependency>
     <groupId>org.mybatis.spring.boot</groupId>
     <artifactId>mybatis-spring-boot-starter</artifactId>
     <version>2.1.0</version>
   </dependency>

• 配置mybatis

mybatis:
  mapper-locations: classpath:/mapper/*Mapper.xml
  type-aliases-package: com.study.data.dao.entity

配置说明:
(1)第一个配置mapper.xml位置
(2)第二个配置表示实体类存放位置

• 创建数据库访问层

@Mapper
public interface MybatisBookMapper {

  int addBook(@Param("book") Book book);

  int updataBook(@Param("book") Book book);

  int deleteBookById(Integer id);

  Book getBookById(Integer id);

  List<Book> getAllBooks();
}

• 创建对应mapper.xml

<mapper namespace="com.study.data.dao.mapper.MybatisBookMapper">

  <insert id="addBook" parameterType="com.study.data.dao.entity.Book">
insert  into book (name,author) values (#{book.name},#{book.author})

  </insert>
  <update id="updataBook" parameterType="com.study.data.dao.entity.Book">
    update book set name=#{book.name} ,author=#{book.author} where id=#{book.id}

  </update>
  <delete id="deleteBookById">
    delete  from book where id=#{book.id}
  </delete>
  <select id="getBookById" resultType="com.study.data.dao.entity.Book">
    select * from book where id=#{book.id}
  </select>
  <select id="getAllBooks" resultType="com.study.data.dao.entity.Book">
    select * from book
  </select>
</mapper>

完成上述操作，即完成与MyBatis整合，其它代码较简单，就不做演示

3.整合Spring Data JPA

• 引入依赖

<dependency>
     <groupId>org.springframework.boot</groupId>
     <artifactId>spring-boot-starter-data-jpa</artifactId>
   </dependency>

• 配置jpa

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

配置说明

配置	说明
show-sql	展示sql
database	数据库为mysql
hibernate.ddl-auto	创建时时更新操作
properties.hibernate.dialect	数据库方言

• 配置数据库访问层

public interface JpaUserMapper extends JpaRepository<User, Integer> {

  List<User> getUserByUserNameStartingWith(String userName);

  User getUserById(Integer id);

  @Query(value = "select u from t_user u where u.id=(Select max(id) from t_user)")
  User getMaxIdUser();

  @Query("select u from t_user u where u.id>:id and u.userName=:userName")
  List<User> getUserByIdAndUserName(@Param("userName") String userName, @Param("id") Integer id);

}

Jpa根据根据方法名组合为查询条件，具体规则如下表:

规则	举例	对应sql
Is	findByAgeIs	where age=?
Equals	findByIdEquals	where id=?
Between	findByAgeBetween	where age between ? and ?
LessThan	findByAgeLessThan	where age<?
GreaterThan	findByAgeGreaterThan	where age>?
After	findByAgeAfter	where age >?
Before	findByAgeBefore	where age<?
isNull	findByNameIsNull	where name is null
isNotNull,NotNull	findByNameNotNull	where name is not null
Not	findByGenderNot	where gender<>?
In	findByAgeIn	where age in (?)
NotIn	findByAgeNotIn	where not in (?)
NotLike	findByNameNotLike	where name not like ?
Like	findByNameLike	where name like ?
StarterWith	findByNameStartingWith	where name like ‘?%’
EndingWith	findByNameEndingWith	where name like ‘%?’
Containing,Contains	findByNamecontaining	where name like ‘%?%’
OrderBy	findByAgeGreaterThanOrderByIdDesc	where age>? order by id desc
True	findByenabledTrue	where enabled=true
False	findByenabledFalse	where enabled=false
IgnoreCase	findByNameIgnoreCase	where UPPER(name)=UPPER(?)

4.JdbcTemplate整合多数据源

• 分别创建两个数据库,分别存入两条不同数据
• 引入依赖

<dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>druid-spring-boot-starter</artifactId>
     <version>1.1.20</version>
   </dependency>

• 配置数据库连接

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

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

• 书写数据库配置

@Configuration
public class DataSourceConfig {

  @Bean
  @ConfigurationProperties(value = "spring.datasource.one")
  DataSource dbOne(){
    return DruidDataSourceBuilder.create().build();
  }

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

  @Bean
  JdbcTemplate jdbcTemplateOne(@Qualifier("dbOne")DataSource dataSource){
    return new JdbcTemplate(dataSource);
  }

  @Bean
  JdbcTemplate jdbcTemplateTwo(@Qualifier("dbTwo")DataSource dataSource){
    return new JdbcTemplate(dataSource);
  }
}

说明:
主要从dataSource上区别，最终创建出两个不同连接的JdbcTemplate

• 书写测试类

@RestController
public class JdbcBookController {
  @Resource(name="jdbcTemplateOne")
  private JdbcTemplate jdbcTemplateOne;

  @Resource(name="jdbcTemplateTwo")
  private JdbcTemplate jdbcTemplateTwo;

  @GetMapping("/test1")
  public void test1(){
    List<Book> books1 = jdbcTemplateOne
        .query("select * from book", new BeanPropertyRowMapper<>(Book.class));

    List<Book> books2 = jdbcTemplateTwo
        .query("select * from book", new BeanPropertyRowMapper<>(Book.class));

    System.out.println(books1);
    System.out.println(books2);
  }
}

• 测试结果:
  输出结果如下,表示多数据源已经配置成功
  

5.Mybatis整合多数据源

• 引入依赖
• 书写数据库配置

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

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

• 书写mybatis配置

配置1:

@Configuration
@MapperScan(value = "com.study.data.dao.mapper1",sqlSessionFactoryRef = "sqlSessionFactoryBean1")
public class MyBatisConfigOne {

  @Resource(name = "dbOne")
  DataSource dbOne;

  @Bean
  SqlSessionFactory sqlSessionFactoryBean1() throws Exception {
    SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
    factoryBean.setDataSource(dbOne);
    return factoryBean.getObject();
  }

  @Bean
  SqlSessionTemplate sqlSessionTemplate1() throws Exception {
    return new SqlSessionTemplate(sqlSessionFactoryBean1());
  }
}

配置2:

@Configuration
@MapperScan(value = "com.study.data.dao.mapper2",sqlSessionFactoryRef = "sqlSessionFactoryBean2")
public class MyBatisConfigTwo {

  @Resource(name = "dbTwo")
  DataSource dbTwo;

  @Bean
  SqlSessionFactory sqlSessionFactoryBean2() throws Exception {
    SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
    factoryBean.setDataSource(dbTwo);
    return factoryBean.getObject();
  }

  @Bean
  SqlSessionTemplate sqlSessionTemplate2() throws Exception {
    return new SqlSessionTemplate(sqlSessionFactoryBean2());
  }
}

• 测试

@RestController
public class MybatisController {

  @Resource
  private BookMapper2 bookMapper2;

  @Resource
  private BookMapper bookMapper1;

  @GetMapping("/mybatis/test1")
  public void test1(){
    List<Book> books1 = bookMapper1.getBooks();
    List<Book> books2 = bookMapper2.getAllBooks();
    System.out.println(books1);
    System.out.println(books2);
  }
}

6.JPA整合多数据源

• 引入依赖

<dependency>
     <groupId>org.springframework.boot</groupId>
     <artifactId>spring-boot-starter-data-jpa</artifactId>
   </dependency>

• 书写配置文件

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

• 书写jpa配置文件

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = "com.study.data.dao1",
              entityManagerFactoryRef = "",
              transactionManagerRef = "")
public class JpaConfigOne {

  @Resource(name="dbOne")
  DataSource dbOne;

  @Resource
  JpaProperties jpaProperties;

  @Bean
  @Primary
  LocalContainerEntityManagerFactoryBean entityManagerFactoryBeanOne(EntityManagerFactoryBuilder builder){
    return builder.dataSource(dbOne)
                  .properties(jpaProperties.getProperties())
                  .packages("com.study.data.dao.entity")
                  .persistenceUnit("pu1") //表示持久化单元名
                  .build();
  }

  @Bean
  PlatformTransactionManager platformTransactionManagerOne(EntityManagerFactoryBuilder builder){
    LocalContainerEntityManagerFactoryBean factoryBeanOne = entityManagerFactoryBeanOne(
        builder);
    return new JpaTransactionManager(factoryBeanOne.getObject());
  }
}

配置2与上述类似

• 书写两个dao文件，分别位置 com.study.data.dao1和com.study.data.dao2

public interface JpaUserMapper1 extends JpaRepository<User, Integer> {
}

• 执行测试即可