SpringBoot 学习（四）数据库整合

4. 整合数据库

4.1 整合 JDBC

(1) 导入依赖

<!--pom.xml-->
<dependencies>

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

    <!--MYSQL 驱动-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>

    <!--单元测试-->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

(2) 配置数据库

# application.yml
spring:
  datasource:
    username: root
    password: 981030
    url: jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
    driver-class-name: com.mysql.cj.jdbc.Driver

(3) 测试数据链接

// DataApplicationTests
@Test
void contextLoads() throws SQLException {
    // 查看默认数据源: com.zaxxer.hikari.HikariDataSource
    System.out.println("dataSource ==> " + dataSource.getClass());

    // 获得数据库链接
    Connection connection = dataSource.getConnection();
    System.out.println("connection ==> " + connection);
    connection.close();
}

(4) RCUD

@RestController
public class JdbcController {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @GetMapping("/userList")
    public List<Map<String, Object>> userList() {
        String sql = "select * from user";
        List<Map<String, Object>> list_maps = jdbcTemplate.queryForList(sql);
        return list_maps;
    }

    @GetMapping("/addUser")
    public String addUser() {
        String sql = "insert into mybatis.user (id, name, pw) values (5, '周五', '555555')";
        int add = jdbcTemplate.update(sql);
        return "add ==> " + add;
    }

    @GetMapping("/updateUser/{id}")
    public String updateUser(@PathVariable("id") int id) {
        String sql = "update mybatis.user set name = ?, pw = ? where id = " + id;
        Object[] objects = new Object[2];
        objects[0] = "周武";
        objects[1] = "50005";
        int update = jdbcTemplate.update(sql, objects);
        return "update ==> " + update;
    }

    @GetMapping("/deleteUser/{id}")
    public String deleteUser(@PathVariable("id") int id) {
        String sql = "delete from mybatis.user where id =? ";
        int delete = jdbcTemplate.update(sql, id);
        return "delete ==> " + delete;
    }
}

4.2 整合 Druid

(1) 导入依赖

<!--pom.xml-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.21</version>
</dependency>
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
</dependency>

(2) 配置数据源

spring:
  datasource:
    username: root
    password: 981030
    url: jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource

    # SpringBoot 默认不注入以下属性值
    # Druid 数据源专有配置
    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
    # stat: 监控统计; log4j: 日志记录; wall: 防御 sql 注入
    # 如果允许时报错 java.lang.ClassNotFoundException: org.apache.Log4j.Priority
    # 则导入 log4j 依赖即可, Maven 地址: https://mvnrepository.com/artifact/log4j/Log4j
    filters: stat, wall, log4j
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

(3) 配置后台监控

@Configuration
public class DruidConfig {

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

    /**
     * 后台监控
     * @return ServletRegistration
     */
    @Bean
    public ServletRegistrationBean statViewServlet() {
        ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");

        HashMap<String, String> initParameters = new HashMap<>();
        // 后台管理员配置
        // key 固定
        initParameters.put("loginUsername", "admin");
        initParameters.put("loginPassword", "123456");

        // 允许访问
        // 允许所有人访问
        initParameters.put("allow", "");

        // 禁止访问
        // initParameters.put("人名", "ip地址");

        // 设置初始化参数
        bean.setInitParameters(initParameters);

        return bean;
    }

    /**
     * 过滤器
     * @return FilterRegistrationBean
     */
    @Bean
    public FilterRegistrationBean webStatFilter() {
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());
        HashMap<String, String> initParameters = new HashMap<>();
        // 过滤请求，不进行统计
        initParameters.put("exclusions", "*.js, *.css, /druid/*");
        bean.setInitParameters(initParameters);
        return bean;
    }
}

4.3 整合 Mybatis

(1) 配置 mapper 路径

# 整合 mybatis
mybatis.type-aliases-package=com.why.pojo
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml

(2) 编写 mapper 接口

// 标识 mybatis 的 mapper
@Mapper
@Repository
public interface UserMapper {

    List<User> queryUserList();

    User queryUserById(int id);

    int addUser(User user);

    int updateUser(User user);

    int deleteUser(int id);
}

(3) 编写 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.why.mapper.UserMapper">

    <select id="queryUserList" resultType="User">
        select * from user
    </select>

    <select id="queryUserById" resultType="User">
        select * from user where id = #{id}
    </select>

    <insert id="addUser" parameterType="User">
        insert into user (id, name, pw) values (#{id}, #{name}, #{pw})
    </insert>

    <insert id="updateUser" parameterType="User">
        update user set name = #{name}, pw = #{pw} where id = #{id}
    </insert>

    <delete id="deleteUser" parameterType="int">
        delete from user where id = #{id}
    </delete>

</mapper>

(4) 编写控制器

@RestController
public class UserController {

    @Autowired
    private UserMapper userMapper;

    @GetMapping("/queryUserList")
    public List<User> queryUserList() {
        List<User> userList = userMapper.queryUserList();
        for (User user : userList) {
            System.out.println("user ==> " + user);
        }
        return userList;
    }

    @GetMapping("/queryUserById/{id}")
    public User queryUserById(@PathVariable("id") int id) {
        User user = userMapper.queryUserById(id);
        return user;
    }

    @GetMapping("/addUser")
    public String queryUserById() {
        int i = userMapper.addUser(new User(6, "吴六", "6000"));
        return "添加 ==> " + i;
    }

    @GetMapping("/updateUser")
    public String updateUser() {
        int i = userMapper.updateUser(new User(6, "吴柳", "6000"));
        return "更新 ==> " + i;
    }

    @GetMapping("/deleteUser/{id}")
    public String deleteUser(@PathVariable("id") int id) {
        int i = userMapper.deleteUser(id);
        return "删除 ==> " + i;
    }
    
}