SpringBoot(七)——数据访问

十一、数据访问

1、整合JDBC

首先使用idea的向导化创建一个工程，选择我们需要的功能模块

在resources文件下创建一个application.yml配置文件链接上MySQL数据库

spring:
  datasource:
    username: root
    password: xxxxxx
    url: jdbc:mysql://192.168.3.158:3306/jdbc
    driver-class-name: com.mysql.jdbc.Driver

2、DataSourceInitializer

DataSourceInitializer是ApplicationListener的一个继承类
作用

• runSchemaScripts();运行建表语句
• runDataScripts();运行插入数据的sql语句

使用方法

默认规则：schema.sql，schema-all.sql；
可以使用   
	schema:
      - classpath:department.sql
      指定位置

3、小案例

首先在resources文件下面创建一个.sql文件

CREATE TABLE `department` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `departmentName` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

将.sql文件路径写在.yml文件中

    schema:
     - classpath: department.sql

测试代码：

@Controller
public class HelloController {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @ResponseBody
    @GetMapping("/query")
    public Map<String, Object> map(){
        List<Map<String, Object>> maps = jdbcTemplate.queryForList("select * from department");
        return maps.get(0);
    }
}

向/query发送一个get请求，然后去到localhost:8080/页面就可以看到一个json格式的数据显示出来。

4、整合Druid数据源

<1>、配置属性

在pom文件中引入Druid的依赖

  		<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.8</version>
        </dependency>

在yml文件中将数据源地址改成Druid

type: com.alibaba.druid.pool.DruidDataSource

还可以进行一些其他的配置

#   数据源其他配置
    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
#   配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
    filters: stat,wall,log4j
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

我们需要手动配置上下面这些配置，不然不会生效。

@Configuration
public class DruidConfig {

    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druid(){
        return new DruidDataSource();
    }
}

<2>、配置Druid的监控

 	//配置Druid的监控
    //1、配置管理后台的Servlet
    @Bean
    public ServletRegistrationBean statViewServlet(){
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");

        Map<String, String> initParams = new HashMap<>();
        initParams.put("loginUsername", "Tom");
        initParams.put("loginPassword", "xxxxxx");
        initParams.put("allow", "");    //默认是允许所有访问
        initParams.put("deny", "192.168.3.158");

        bean.setInitParameters(initParams);

        return bean;
    }

    //2、配置web监控的filter
    @Bean
    public FilterRegistrationBean webStatFilter(){
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());

        Map<String, String> initParams = new HashMap<>();
        initParams.put("exclusions", "*.js,*.css, /druid/*");

        bean.setInitParameters(initParams);
        bean.setUrlPatterns(Arrays.asList("/*"));

        return bean;
    }

重新运行打开localhost:8080/druid会自动跳转到login界面，进行登录之后就可以看到监控后台的界面。

5、整合MyBatis

<1>、搭建实验环境

新建工程，添加MyBatis依赖，再添加Druid依赖

        <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.8</version>
        </dependency>

重新创建yml文件，连上数据库，配置Druid，书写Druid的config类进行配置，可以参考上面
sql文件的路径

    schema:
     - classpath: sql/department.sql
     - classpath: sql/Employee.sql

<2>、封装表数据

Department：

public class Department {

    private Integer id;
    private String departmentName;

    public Integer getId() {
        return id;
    }

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

    public String getDepartmentName() {
        return departmentName;
    }

    public void setDepartmentName(String departmentName) {
        this.departmentName = departmentName;
    }

    @Override
    public String toString() {
        return "Department{" +
                "id=" + id +
                ", departmentName='" + departmentName + '\'' +
                '}';
    }
}

Employee：

public class Employee {

    private Integer id;
    private String lastName;
    private String email;
    private Integer gender;
    private Integer dId;

    public Integer getId() {
        return id;
    }

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

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Integer getGender() {
        return gender;
    }

    public void setGender(Integer gender) {
        this.gender = gender;
    }

    public Integer getdId() {
        return dId;
    }

    public void setdId(Integer dId) {
        this.dId = dId;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", lastName='" + lastName + '\'' +
                ", email='" + email + '\'' +
                ", gender=" + gender +
                ", dId=" + dId +
                '}';
    }
}

<3>、基于注解来用MyBatis实现CRUD操作

(1)、创建表的接口

public interface DepartmentMapper {

}

//@Mapper //操作数据库的mapper
public interface DepartmentMapper {

    @Select("select * from department where id=#{id}")
    public Department getDeptById(Integer id);

    @Delete("delete from department where id=#{id}")
    public int deleteDeptById(Integer id);

    @Options(useGeneratedKeys = true, keyProperty = "id")
    @Insert("insert into department(departmentName) values(#{departmentName})")
    public int insertDept(Department department);

    @Update("update department set departmentName=#{departmentName} where id=#{id}")
    public int updateDept(Department department);

}

(2)、向页面发送请求，执行请求对应数据

@RestController
public class DeptController {

    @Autowired
    DepartmentMapper departmentMapper;

    @GetMapping("/dept/{id}")
    public Department getDept(@PathVariable("id") Integer id){
        return departmentMapper.getDeptById(id);
    }

    @GetMapping("/dept")
    public Department insertDept(Department department){
        departmentMapper.insertDept(department);
        return department;
    }
}

然后运行来到对应页面测试即可（记得创建表）
localhost:8080/dept/1
localhost:8080/dept?departmentName=tom
如果我们想要获取到自增主键，只要在对应的语句前设置一个Option注解即可

    @Options(useGeneratedKeys = true, keyProperty = "id")
    @Insert("insert into department(departmentName) values(#{departmentName})")
    public int insertDept(Department department);

(3)、自定义MyBatis的Config

@org.springframework.context.annotation.Configuration
public class MyBatisConfig {

    @Bean
    public ConfigurationCustomizer configurationCustomizer(){

        return new ConfigurationCustomizer() {
            @Override
            public void customize(Configuration configuration) {
                configuration.setMapUnderscoreToCamelCase(true);
            }
        };
    }
}

在主类中使用@MapperScan即可扫面所有的Mapper接口

@MapperScan(value = "com.example.springboot.mapper")    //批量扫描所有的Mapper接口

<4>、基于配置文件来用MyBatis实现CRUD操作

(1)、创建接口

public interface EmployeeMapper {

    Employee getEmpById(Integer id);

    void insertEmp(Employee employee);
}

(2)、编写配置文件

<?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>

</configuration>

(3)、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.springboot.mapper.EmployeeMapper">
   <!-- public Employee getEmpById(Integer id);

    public void insertEmp(Employee employee);-->
    <select id="getEmpById" resultType="com.example.springboot.bean.Employee">
        SELECT * FROM employee WHERE id=#{id}
    </select>

    <insert id="insertEmp">
        INSERT INTO employee(lastName,email,gender,d_id) VALUES (#{lastName},#{email},#{gender},#{dId})
    </insert>
</mapper>

(4)、MyBatis检索配置文件

	@Autowired
    EmployeeMapper employeeMapper;
    
    @GetMapping(value = "/emp/{id}")
    public Employee getEmp(@PathVariable("id") Integer id){
        return employeeMapper.getEmpById(id);
    }

(5)、在配置文件中配置自己想要实现的功能

    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

6、整合SpringData JPA

<1>、SpringData JPA简介

概念
SpringData项目目的是为了简化构建基于Spring框架应用的数据访问技术，包括非关系型数据库，MapReduce框架，云服务等等；另外也包含对关系型数据库的访问支持
特点
SpringData提供统一的API来对数据访问层进行操作；这主要是SpringDataCommons项目来实现的。SpringDataCommons让我们在使用sql，nosql的时候都有基于spring的统一标准，标准包括crud等相关操作。
同一接口
Repository<T,ID extends Serializable>:统一接口
RevisionRepository<T,ID extends Serializable,N extends Number & Comparable>:基于乐观锁机制

乐观锁机制：
认为数据一般情况下不会造成冲突，所以在数据进行提交更新时，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让用户返回错误的信息，让用户决定如何去做。

CrudRepository<T,ID extends Serializable>:基本Crud操作
PagingAndSortingRepository<T,ID extends Serializable>：基本CRUD及分页
提供数据访问模板xxxTemplate
就像JDBCTemplate…
JPA与SpringData

<2>、SpringData JPA

(1)JPARepository基本功能
(2)定义符合规范方法命名
(3)@Query自定义查询，定制查询Sql
(4)Specifications查询(SpringDataJPA支持JPA2.0 的Criteria查询)

<3>、创建适合的环境

创建application.yml进行数据库连接

spring:
  datasource:
    url: jdbc:mysql://192.168.3.158/jpa
    username: root
    password: xxxxxx
    driver-class-name: com.mysql.jdbc.Driver

<4>、测试实例

(1)、创建一个带有几个私有属性的类，自动生成get、set方法

使用JPA注解配置映射关系，标记主键属性，列属性，和其他属性

//使用JPA注解配置映射关系
@Entity     //告诉JPA这是一个实体类
@Table(name = "tbl_user")   //@Table指定和哪个数据表对应；省略默认表名是user
public class User {

    @Id     //主键
    @GeneratedValue(strategy = GenerationType.IDENTITY) //自增主键
    private Integer id;

    @Column(name = "last_name", length = 50)    //和数据表对应的一个列
    private String lastName;

    @Column //省略默认列名就是属性名
    private String email;

    public Integer getId() {
        return id;
    }

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

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }
}

(2)、编写一个Dao接口来操作实体类对应的数据表（Repository）

//继承JpaRepository完成对数据库的操作
public interface UserRepository extends JpaRepository<User, Integer> {

}

(3)、基本的配置JpaProperties

 jpa:
    hibernate:
#    更新或者创建数据表结构
      ddl-auto: update
#    控制台显示SQL
     show-sql: true

运行程序，看到表已经创建出来。

(4)、CRUD操作

package com.example.springboot.controller;

import com.example.springboot.entity.User;
import com.example.springboot.repository.UserRepository;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class UserController {

    @Autowired
    UserRepository userRepository;

    @GetMapping("user/{id}")
    public User getUser(@PathVariable("id") Integer id){
        User user = userRepository.findOne(id);
        return user;
    }

    @GetMapping("/user")
    public User insertUser(User user){
        User save = userRepository.save(user);
        return save;
    }
}