朝小闇的博客

海上月是天上月,眼前人是心上人

SpringBoot(四)——数据库连接(整合Mybatis)

本节讲解数据库连接以及mybatis使用

1.数据库连接及原生用法

  1. 新建项目,配置依赖:

    • Web->Spring Web;
    • SQL->JDBC API;
    • SQL->Spring Data JDBC;
    • SQL->MySQL Driver;
  2. IDEA连接数据库(可选,可以使用其它软件直接打开mysql显示):

    如图,我已连接自己的mysql中springboot_mybatis数据库,其中已配置简单表user:

    image-20210309160519767
  3. 新建application.yml文件(可以使用默认的application.properties),并对该项目连接mysql数据库:

    1
    2
    3
    4
    5
    6
    7
    spring:
    datasource:
    username: root
    password: password
    # serverTimezone=UTC是时区,
    url: jdbc:mysql://localhost:3306/springboot_mybatis?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
    driver-class-name: com.mysql.cj.jdbc.Driver
  4. 新建controller包,并在其下新建JDBCController.java文件,代码如下(运行后已可正常访问并简易增删查改数据库):

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    package com.kun.demodata.controller;

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

    import java.util.List;
    import java.util.Map;

    @RestController
    public class JDBCController {
    // Template是Spring默认配置好的模板bean,可以拿来即用
    @Autowired
    JdbcTemplate jdbcTemplate;
    // 查询数据库所有信息,没有实体类时使用Map获取数据库中的数据
    @GetMapping("/userList")
    public List<Map<String,Object>> userList(){
    String sql = "select * from user";
    List<Map<String,Object>> mapList = jdbcTemplate.queryForList(sql);
    return mapList;
    }
    // 增加用户信息
    @GetMapping("/addUser")
    public String addUser(){
    String sql = "insert into springboot_mybatis.user(id,name,pwd) values(4,'小芳','ffffff')";
    jdbcTemplate.update(sql);
    return "addUser-ok";
    }
    // 修改用户信息
    @GetMapping("/updateUser/{id}")
    public String updateUser(@PathVariable("id") int id){
    // 占位符语法格式
    String sql = "update springboot_mybatis.user set name=?,pwd=? where id="+id;
    // 封装
    Object[] objects = new Object[2];
    objects[0] = "小黄";
    objects[1] = "password";
    // 直接传参
    jdbcTemplate.update(sql,objects);
    return "updateUser-ok";
    }
    // 删除用户信息
    @GetMapping("/deleteUser/{id}")
    public String deleteUser(@PathVariable("id") int id){
    String sql = "delete from springboot_mybatis.user where id=?";
    // 直接传参
    jdbcTemplate.update(sql,id);
    return "deleteUser-ok";
    }
    }

2.Druid数据源

Druid数据源出自阿里,它最强大的功能在于其日志监控

  1. 查看默认数据源,在test文件中查看Spring默认数据源,hikari是速度最快的数据源:

    1
    2
    3
    4
    5
    6
    7
    8
    @Autowired
    DataSource dataSource;

    @Test
    void contextLoads() {
    // 查看默认数据源 com.zaxxer.hikari.HikariDataSource
    System.out.println(dataSource.getClass());
    }
  2. pom.xml增加druid依赖以及log4j依赖,并加载:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    <dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.4</version>
    </dependency>
    <!-- 日志监控依赖,添加之后才能使用druid日志监控功能 -->
    <dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.12</version>
    </dependency>
  3. application.yml文件修改默认配置:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    spring:
    datasource:
    type: com.alibaba.druid.pool.DruidDataSource

    # Spring默认不注入这些属性配置,需要自己绑定,一般根据公司需要个性绑定,也是druid专有属性
    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000
    timeBetweenEvictionRunMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    # 配置filter,stat:监控统计;log4j:日志记录;wall:防御sql注入
    filters: stat,wall,log4j
    maxPoolPreparedStatmentPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
  4. 新建Config包,并在其下新建DruidConfig.java文件,代码如下:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    package com.kun.demodata.config;

    import com.alibaba.druid.pool.DruidDataSource;
    import com.alibaba.druid.support.http.StatViewServlet;
    import com.alibaba.druid.support.http.WebStatFilter;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.boot.web.servlet.FilterRegistrationBean;
    import org.springframework.boot.web.servlet.ServletRegistrationBean;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;

    import javax.servlet.Filter;
    import javax.sql.DataSource;
    import java.util.HashMap;

    @Configuration
    public class DruidConfig {
    // 配置绑定路径,即此处创建的属性可以直接被spring.datasource以下使用
    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druidDataSource(){
    return new DruidDataSource();
    }
    // 后台监控功能,因为SpringBoot内置了Servlet容器,所以没有web.xml配置
    @Bean
    public ServletRegistrationBean statViewServlet(){
    // 配置请求访问路径
    ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(),"/druid/*");
    // 后台登录初始配置,从源码查看参数性质再重写源码参数
    HashMap<String, String> initParameters = new HashMap<>();
    // 配置后台登录账号密码,且此处key值loginUsername、loginPassword唯一绑定,不可更改
    initParameters.put("loginUsername","admin");
    initParameters.put("loginPassword","123456");
    // 允许访问,此处写入localhost或具体账户则仅可访问
    initParameters.put("allow","");
    // 禁止访问,initParameters.put("name","192.168.123.123");
    bean.setInitParameters(initParameters);
    return bean;
    }
    // filter过滤功能
    @Bean
    public FilterRegistrationBean webStatFilter(){
    FilterRegistrationBean<Filter> bean = new FilterRegistrationBean<>();
    bean.setFilter(new WebStatFilter());
    // 设置过滤请求参数
    HashMap<String, String> initParameters = new HashMap<>();
    initParameters.put("exclusions","*.js,*.css,/druid/*");
    bean.setInitParameters(initParameters);
    return bean;
    }
    }

注:如果运行报错为log4j Warning,需要在resources目录下新建log4j.properties配置文件并注入代码:

1
2
3
4
log4j.rootLogger=DEBUG, stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

3.整合Mybatis

这里依然使用上一个项目文件,连接数据库与上一致,无需重复。

  1. pom.xml文件中引入依赖:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    <!-- lombok可自动注入有参无参构造等 -->
    <dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    </dependency>
    <!-- mybatis -->
    <dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.1</version>
    </dependency>
  2. 新建实体层pojo,下建User.java实体类:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    package com.kun.demodata.pojo;

    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;

    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public class User {
    private int id;
    private String name;
    private String pwd;
    }
  3. 新建mapper目录,该目录实质就是dao目录,即数据访问层,下建UserMapper.java文件:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    package com.kun.demodata.mapper;

    import com.kun.demodata.pojo.User;
    import org.apache.ibatis.annotations.Mapper;
    import org.springframework.stereotype.Repository;

    import java.util.List;

    // @Mapper表示这是一个mybatis接口类,@Repository即注入SpringBoot
    @Mapper
    @Repository
    public interface UserMapper {
    List<User> queryUserList();

    User queryUserById();

    int addUser(User user);

    int updateUser(User user);

    int deleteUser(int id);
    }
  4. 在resources目录下新建mybatis.mapper目录,下建对应UserMapper.xml文件,用于实现sql数据访问:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    <?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.kun.demodata.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,pwd) values (#{id},#{name},#{pwd})
    </insert>
    <update id="updateUser" parameterType="User">
    update user set name = #{name},pwd = #{pwd} where id = #{id}
    </update>
    <delete id="deleteUser" parameterType="User">
    delete from user where id = #{id}
    </delete>
    </mapper>
  5. 除此之外,还需添加application.yml配置,用于整合连接mybatis:

    1
    2
    3
    4
    # 整合mybatis,分别为实体类位置和mapper实现层位置
    mybatis:
    type-aliases-package: com.kun.demodata.pojo
    mapper-locations: classpath:mybatis/mapper/*.xml
  6. 最后新建UserController.java文件,实现最终的控制服务调用,此处不再重复增删改等方法,只以查询为例,并且上一篇博客中未连接的数据库会由此类mybatis整合进去:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    package com.kun.demodata.controller;

    import com.kun.demodata.mapper.UserMapper;
    import com.kun.demodata.pojo.User;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RestController;

    import java.util.List;

    @RestController
    public class UserController {
    @Autowired
    private UserMapper userMapper;

    @GetMapping("/queryUserList")
    public List<User> queryUserList(){
    List<User> userList = userMapper.queryUserList();
    return userList;
    }
    }
-------- 本文结束 感谢阅读 --------