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

发布时间:2023-05-16 08:30

个人博客:http://blog.kunpw.cn/

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

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

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

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

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

    SpringBoot(四)——数据库连接(整合Mybatis)_第1张图片
  3. 新建application.yml文件(可以使用默认的application.properties),并对该项目连接mysql数据库:

    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文件,代码如下(运行后已可正常访问并简易增删查改数据库):

    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是速度最快的数据源:

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

    <dependency>
        <groupId>com.alibabagroupId>
        <artifactId>druidartifactId>
        <version>1.2.4version>
    dependency>
    
    <dependency>
        <groupId>log4jgroupId>
        <artifactId>log4jartifactId>
        <version>1.2.12version>
    dependency>
    
  3. application.yml文件修改默认配置:

    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文件,代码如下:

    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配置文件并注入代码:

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文件中引入依赖:

    
    <dependency>
        <groupId>org.projectlombokgroupId>
        <artifactId>lombokartifactId>
    dependency>
    
    <dependency>
        <groupId>org.mybatis.spring.bootgroupId>
        <artifactId>mybatis-spring-boot-starterartifactId>
        <version>2.1.1version>
    dependency>
    
  2. 新建实体层pojo,下建User.java实体类:

    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文件:

    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数据访问:

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

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

    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;
        }
    }
    

ItVuer - 免责声明 - 关于我们 - 联系我们

本网站信息来源于互联网,如有侵权请联系:561261067@qq.com

桂ICP备16001015号