[Java知识库]SpringBoot整合JDBC、Druid、Mybatis

SpringBoot整合JDBC、Druid、Mybatis

整合jdbc

新建SpringBoot项目时导入相关依赖
新建application.yml配置文件，进行如下配置

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

在test中编写如下代码进行测试

@SpringBootTest
class Springboot04DataApplicationTests {

    @Autowired
    DataSource dataSource;
    @Test
    void contextLoads() throws SQLException {
        // 查看一下默认的数据源
        // SpringBoot默认Hikari数据库连接池
        System.out.println(dataSource.getClass());

        // 获得数据库连接
        Connection connection = dataSource.getConnection();
        System.out.println(connection);

        // xxx Template: SpringBoot 已经配置好模板Bean，拿来即用CRUD
        //关闭
        connection.close();
    }
}

编写JDBCController进行CRUD 查询测试

@RestController
public class JDBCController {

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

整合Druid

在application.yml配置文件中指定使用Druid数据库连接池(一定要注意缩进，严格)

spring:
  datasource:
    username: root
    password: root
    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

    #Spring Boot 默认是不注入这些属性值的，需要自己绑定
    #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

新建DruidConfig.java （Druid配置类）后台监听

@Configuration
public class DruidConfig {

    // 绑定
    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druidDataSource(){
        return new DruidDataSource();
    }

    // 后台监控功能: web.xml
    // 因为SpringBoot 内置了servlet容器，所以没有web.xml 替代方法：ServletRegistrationBean
    @Bean
    public ServletRegistrationBean statViewServlet(){
        ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(),"/druid/*");

        // 后台需要有人登陆，账号密码配置
        HashMap<String, String> initParameters = new HashMap<>();

        // 增加配置
        initParameters.put("loginUsername","admin");// 登录的key是固定的不能乱写
        initParameters.put("loginPassword","123456");

        // 允许谁可以访问 为空所有人都可以访问
        initParameters.put("allow","");

        // 禁止谁能访问
        // initParameters.put("","192.168.");

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

    //filter
    @Bean
    public FilterRegistrationBean webStatFilter(){
        FilterRegistrationBean bean = new FilterRegistrationBean<>();
        bean.setFilter(new WebStatFilter());
        // 可以过滤那些请求呢
        Map<String, String> initParameters = new HashMap<>();
        // 这些东西不进行统计
        initParameters.put("exclusions","*.js,*.css,/druid/*");
        bean.setInitParameters(initParameters);
        return bean;
    }
}

启动项目访问http://localhost:8080/druid将跳转到druid后台监听登录页面如下

输入上面设置的用户名密码，进入druid后台监听

访问我们整合jdbc定义的查询接口http://localhost:8080/userList

回到druid后台监控点击SQL监听可以看到我们刚刚sql语句的执行情况

整合Mybatis

新建spring项目勾选上相关依赖
导包

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

查看是否导入以下依赖包（总）

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

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

     <dependency>
         <groupId>mysql</groupId>
         <artifactId>mysql-connector-java</artifactId>
         <scope>runtime</scope>
     </dependency>
     <dependency>
         <groupId>org.projectlombok</groupId>
         <artifactId>lombok</artifactId>
         <optional>true</optional>
     </dependency>
     <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-test</artifactId>
         <scope>test</scope>
     </dependency>
 </dependencies>

编写application.properties配置文件连接数据库参数，也可以用yml配置，书写更简单，上次用了yml这次用properties练习下

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

编写User实体类

@Data
@AllArgsConstructor
@NoArgsConstructor

public class User {
    private int id;
    private String username;
    private String pwd;
}

编写dao层接口userMapper

// 1、这个注解表示该类是一个Mybatis的mapper类
@Mapper
@Repository // dao层注解
public interface UserMapper {

    List<User> queryUserList();

    User queryUserById(@Param("id") int id);

    int addUser(User user);

    int updateUser(User user);

    int deleteUser(@Param("id") int id);
}

mybatis配置在application.properties配置文件中添加mybatis配置

# 整合mybatis
# 配置别名
mybatis.type-aliases-package=com.example.pojo
# mapper.xml文件路径
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml

编写UserMapper.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">
<!--namespace=绑定一个对应的Dao/Mapper接口-->
<mapper namespace="com.example.mapper.UserMapper">

    <!--id对应UserDao中的方法名-->
    <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,username,pwd)
        values (#{id},#{username},#{pwd})
    </insert>

    <update id="updateUser" parameterType="User">
        update user
        set username = #{username},pwd = #{pwd}
        where id = #{id}
    </update>

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

</mapper>

因就是简单的mybatis整合，这里 不再编写service，直接使用Controller层调用dao层，正常开发应该先用service层调dao层，再用controller层调service层

@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);
        }
        return userList;
    }
}