spring-boot笔记--数据访问

一、Spring Boot与数据访问

1、简介

Spring Boot在数据访问层,无论是SQL还是NOSQL,Spring Boot默认使用Spring Data进行整合,添加了大量的自动配置,引入了各种xxxTemplate,xxxRepository来简化我们对数据访问层的操作,我们只需要进行简单的设置即可

2、整合基本的JDBC和数据源

我们使用基本的JDBC和MySQL驱动,先在pom.xml引入依赖

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

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

然后在application.properties中写入

spring:
  datasource:
    username: root
    password: xxxxxx
    url: jdbc:mysql://localhost:3306/jdbc?serverTimezone=UTC
    # 如果不加?serverTimezone=UTC会报错
    driver-class-name: com.mysql.cj.jdbc.Driver
    # driver-class-name可以不用加

我们在ApplicationTests中测试

@Autowired
DataSource dataSource;

@Test
void contextLoads() throws SQLException {
    System.out.println(dataSource.getClass());
    Connection connection = dataSource.getConnection();
    System.out.println(connection);
    connection.close();
}

我们可以查看数据源相关配置的源文件,都在DataSourceProperties里面

@ConfigurationProperties(
    prefix = "spring.datasource"
)
public class DataSourceProperties implements BeanClassLoaderAware, InitializingBean {

自动配置原理

自动配置都在org.springframework.boot.autoconfigure.jdbc里面

  1. 参考DataSourceConfiguration,根据相关配置创建数据源,默认使用Tomcat连接池,可以指定spring.datasource.type的属性来配置自定义的数据源

  2. Spring Boot默认可以支持BasicDataSource、HikariDataSource、org.apache.tomcat.jdbc.pool.DataSource、

  3. 自定义数据源

    @Configuration(
        proxyBeanMethods = false
    )
    @ConditionalOnMissingBean({DataSource.class})
    @ConditionalOnProperty(
        name = {"spring.datasource.type"}
    )
    static class Generic {
        Generic() {
        }
    
        @Bean
        DataSource dataSource(DataSourceProperties properties) {
            //initializeDataSourceBuilder返回DataSourceBuilder创建数据源,利用反射响应type的数据源,并且绑定相关属性
            return properties.initializeDataSourceBuilder().build();
        }
    }
    
  4. 现在看DataSourceAutoConfiguration类,它引入了一个DataSourceInitializationConfiguration类,而DataSourceInitializationConfiguration类又引入了一个DataSourceInitializerInvoker类

    DataSourceInitializerInvoker类作用,可以自动运行建表语句和插入数据的sql语句

    schema-*.sql  data-*.sql
    默认写为:schema.sql或者schema-all.sql都可以
    也可以自己设置路径
    
    spring:
      datasource:
        username: root
        password: 123456
        url: jdbc:mysql://localhost:3306/jdbc?serverTimezone=UTC
        //Spring Boot2.0以上都得配置initialization-mode: always
        initialization-mode: always
        //设置自定义sql文件路径
        schema:
          - classpath:sql_schema.sql
    
    这里还有我自己遇到的一个坑
    sql文件里的创建表语句必须在末尾加上';',例如下面如果最后一个')'后不加';'是可以在Navicat里运行的,但是在Spring Boot的sql文件里必须加上';',否则会报错
    CREATE TABLE IF NOT EXISTS `jdbc_student_schema`(
    		`id` INTEGER AUTO_INCREMENT,
    		`name` VARCHAR(10) NOT NULL,
    		`gender` VARCHAR(1) not null,
    		`card` VARCHAR(16) not null,
    		`date` date,
    		PRIMARY KEY(`id`)
    );
    
  5. 自动配置了JdbcTemplate来操作数据库

    @Controller
    public class HelloController {
    
        @Autowired
        JdbcTemplate jdbcTemplate;
    
        @ResponseBody
        @GetMapping("/hello")
        public Map<String, Object> hello(){
    
            List<Map<String, Object>> maps = jdbcTemplate.queryForList("select * from jdbc_student");
    
            System.out.println(maps);
    
            return maps.get(0);
    
        }
    }
    

    访问localhost:8080/hello,数据返回了

3、整合Druid数据源

配置属性

Druid Spring Boot Starter 配置属性的名称完全遵照 Druid,你可以通过 Spring Boot 配置文件来配置Druid数据库连接池和监控,如果没有配置则使用默认值。

  • JDBC 配置
spring.datasource.druid.url= # 或spring.datasource.url= 
spring.datasource.druid.username= # 或spring.datasource.username=
spring.datasource.druid.password= # 或spring.datasource.password=
spring.datasource.druid.driver-class-name= #或 spring.datasource.driver-class-name=
  • 连接池配置
spring.datasource.druid.initial-size=
spring.datasource.druid.max-active=
spring.datasource.druid.min-idle=
spring.datasource.druid.max-wait=
spring.datasource.druid.pool-prepared-statements=
spring.datasource.druid.max-pool-prepared-statement-per-connection-size= 
spring.datasource.druid.max-open-prepared-statements= #和上面的等价
spring.datasource.druid.validation-query=
spring.datasource.druid.validation-query-timeout=
spring.datasource.druid.test-on-borrow=
spring.datasource.druid.test-on-return=
spring.datasource.druid.test-while-idle=
spring.datasource.druid.time-between-eviction-runs-millis=
spring.datasource.druid.min-evictable-idle-time-millis=
spring.datasource.druid.max-evictable-idle-time-millis=
spring.datasource.druid.filters= #配置多个英文逗号分隔
....//more
  • 监控配置
# WebStatFilter配置,说明请参考Druid Wiki,配置_配置WebStatFilter
spring.datasource.druid.web-stat-filter.enabled= #是否启用StatFilter默认值false
spring.datasource.druid.web-stat-filter.url-pattern=
spring.datasource.druid.web-stat-filter.exclusions=
spring.datasource.druid.web-stat-filter.session-stat-enable=
spring.datasource.druid.web-stat-filter.session-stat-max-count=
spring.datasource.druid.web-stat-filter.principal-session-name=
spring.datasource.druid.web-stat-filter.principal-cookie-name=
spring.datasource.druid.web-stat-filter.profile-enable=

# StatViewServlet配置,说明请参考Druid Wiki,配置_StatViewServlet配置
spring.datasource.druid.stat-view-servlet.enabled= #是否启用StatViewServlet(监控页面)默认值为false(考虑到安全问题默认并未启动,如需启用建议设置密码或白名单以保障安全)
spring.datasource.druid.stat-view-servlet.url-pattern=
spring.datasource.druid.stat-view-servlet.reset-enable=
spring.datasource.druid.stat-view-servlet.login-username=
spring.datasource.druid.stat-view-servlet.login-password=
spring.datasource.druid.stat-view-servlet.allow=
spring.datasource.druid.stat-view-servlet.deny=

# Spring监控配置,说明请参考Druid Github Wiki,配置_Druid和Spring关联监控配置
spring.datasource.druid.aop-patterns= # Spring监控AOP切入点,如x.y.z.service.*,配置多个英文逗号分隔

4、整合MyBatis

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

配置application.yml

spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://localhost:3306/jdbc?serverTimezone=UTC
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      initial-size: 5
      min-idle: 5
      max-active: 20
      max-wait: 60000
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 300000
      validation-query: SELECT 1 FROM DUAL
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      pool-prepared-statements: true
      filters: stat, wall, slf4j
      max-pool-prepared-statement-per-connection-size: 20
      use-global-data-source-stat: true
      connect-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
      stat-view-servlet:
        enabled: true
        url-pattern: /druid/*
        login-username: admin
        login-password: 123456

最后创建与数据表对应的JavaBean

4.1、注解版MyBatis

创建一个mapper类

//指定这是一个操作数据库的mapper
@Mapper
public interface StudentMapper {

    @Select("select * from jdbc_student where id=#{id}")
    public Student getStudentById(Integer id);

    @Delete("delete from jdbc_student where id=#{id}")
    public int deleteById(Integer id);
	
    @Options(useGeneratedKeys = true, keyProperty = "id")
    @Insert("insert into jdbc_student(name, gender, card, date) values(#{name}, #{gender}, #{card}, #{date})")
    public int insertStudent(Student student);

    @Update("update jdbc_student set name=#{name}, gender=#{gender}, card=#{card}, date=#{date} where id=#{id}")
    public int updateStudent(Student student);

}

在controller里测试它

@RestController
public class StudentController {

    @Autowired
    StudentMapper studentMapper;

    @GetMapping("/stu/{id}")
    public Student getStudent(@PathVariable("id") Integer id){

        return studentMapper.getStudentById(id);

    }

    @GetMapping("/stu")
    public Student insertStudent(Student student){
        studentMapper.insertStudent(student);
        return student;
    }


}

自定义MyBatis的配置规则,只需要在容器中添加一个ConfigurationCustomizer

@Configuration
public class MyBatisConfig {

    @Bean
    public ConfigurationCustomizer configurationCustomizer(){
        return new ConfigurationCustomizer() {
            @Override
            public void customize(org.apache.ibatis.session.Configuration configuration) {
                configuration.setMapUnderscoreToCamelCase(true);
            }
        };
    }

}

批量扫描mapper,这样就不必在每个mapper前面加@Mapper注解

@MapperScan(value = "com.xxx.xxx.mapper")

4.2、配置版MyBatis

创建另一个mapper类和对应 的Javabean

public interface ShareMapper {

    public Share getShareById(Integer id);

    public void insertShare(Share share);

    public void deleteShare(Integer id);

    public void updateShare(Share share);

}

创建全局配置文件mybatis-config.xml和mapper对应的xml文件ShareMapper.xml

<!--ShareMapper.xml-->
    
<?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.spring.mybatis.mapper.ShareMapper">
    <select id="getShareById" resultType="com.spring.mybatis.bean.Share" >
        select * from jdbc_share where id=#{id}
    </select>
    
    <insert id="insertShare" parameterType="com.spring.mybatis.bean.Share" useGeneratedKeys="true" keyProperty="id">
        insert into jdbc_share(tscode, name) value (#{tscode}, #{name})
    </insert>

    <delete id="deleteShare" parameterType="com.spring.mybatis.bean.Share">
        delete jdbc_share where id=#{id}
    </delete>

    <update id="updateShare" parameterType="com.spring.mybatis.bean.Share">
        update jdbc_student set tscode=#{tscode}, name=#{name} where id=#{id}
    </update>

</mapper>
<!--mybatis-config.xml-->

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!--驼峰命名法-->
    </settings>
</configuration>

然后在spplication.yml中配置

mybatis:
  config-location: classpath:mybatis/mybatis-config.xml
  # 全局配置文件
  mapper-locations: classpath:mybatis/mapper/*.xml
  # 所有在路径下的.xml文件

5、整合JPA

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

JPA也是基于ORM思想的,即Object、Relational、Mapping

  1. 编写一个实体类(bean)和数据表进行映射,并且配置好映射关系

    @Entity()
    @Table(name = "jdbc_share")
    public class Share {
    
        //这是一个主键
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Integer id;
    
        //这是和数据表对应的一个列
        @Column
        private String tscode;
        @Column
        private String name;
        
        //Getter、Setter方法
    
  2. 编写一个Dao接口来操作实体类对应的数据表

    //继承JpaRepository完成对数据库的操作
    public interface ShareRepository extends JpaRepository<Share, Integer> {
    
    }
    
    
  3. yml基本配置

    spring:
      datasource:
        url: jdbc:mysql://localhost:3306/jdbc
        username: root
        password: 123456
        driver-class-name: com.mysql.cj.jdbc.Driver
    
      jpa:
        hibernate:
          # 更新或创建表结构
          ddl-auto: update
        # 控制台显示sql
        show-sql: true
    

在controller里进行查找插入

@RestController
public class ShareController {

    @Autowired
    ShareRepository shareRepository;

    @GetMapping("/share/{id}")
    public Share getShare(@PathVariable("id") Integer id){
        Share share = shareRepository.findById(id).get();
        return share;
    }

    @GetMapping("/share")
    public Share insertShare(Share share){
        Share save = shareRepository.save(share);
        return save;
    }
}

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值