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
IsfindByAgeIswhere age=?
EqualsfindByIdEqualswhere id=?
BetweenfindByAgeBetweenwhere age between ? and ?
LessThanfindByAgeLessThanwhere age<?
GreaterThanfindByAgeGreaterThanwhere age>?
AfterfindByAgeAfterwhere age >?
BeforefindByAgeBeforewhere age<?
isNullfindByNameIsNullwhere name is null
isNotNull,NotNullfindByNameNotNullwhere name is not null
NotfindByGenderNotwhere gender<>?
InfindByAgeInwhere age in (?)
NotInfindByAgeNotInwhere not in (?)
NotLikefindByNameNotLikewhere name not like ?
LikefindByNameLikewhere name like ?
StarterWithfindByNameStartingWithwhere name like ‘?%’
EndingWithfindByNameEndingWithwhere name like ‘%?’
Containing,ContainsfindByNamecontainingwhere name like ‘%?%’
OrderByfindByAgeGreaterThanOrderByIdDescwhere age>? order by id desc
TruefindByenabledTruewhere enabled=true
FalsefindByenabledFalsewhere enabled=false
IgnoreCasefindByNameIgnoreCasewhere 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.dao1com.study.data.dao2
public interface JpaUserMapper1 extends JpaRepository<User, Integer> {
}
  • 执行测试即可
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值