03_整合jdbc和mybatis

张创琦 2022.03.11

1 整合jdbc

  1. 导入 tb_user.sql 文件
  2. 引入依赖和 mysql 数据库驱动
  1. <dependency>
  2.     <groupId>org.springframework.boot</groupId>
  3.     <artifactId>spring-boot-starter-jdbc</artifactId>
  4. </dependency>
  5. <dependency>
  6.     <groupId>org.springframework.boot</groupId>
  7.     <artifactId>spring-boot-starter-test</artifactId>
  8. </dependency>
  9. <dependency>
  10.     <groupId>mysql</groupId>
  11.     <artifactId>mysql-connector-java</artifactId>
  12.     <version>5.1.46</version>
  13. </dependency>

  1. 配置连接池

    1. Dependencies -> jdbc -> HikariCP (速度非常快的连接池)
  2. application.properties
  1. # 连接四大参数
  2. spring.datasource.url=jdbc:mysql://localhost:3306/springboot 
  3. spring.datasource.username=root spring.datasource.password=123
  4. # 可省略，SpringBoot自动推断
  5. spring.datasource.driverClassName=com.mysql.jdbc.Driver
  7. spring.datasource.hikari.idle-timeout=60000 
  8. spring.datasource.hikari.maximum-pool-size=30 
  9. spring.datasource.hikari.minimum-idle=10
  1. com.lxs.domain. User.java
  1. public class User implements Serializable { 
  2.     private Long id;
  3.     // 用户名
  4.     //自动转换下换线到驼峰命名user_name -> userName
  5.     private String userName;
  7.     // 密 码
  8.     private String password;
  10.     // 姓 名
  11.     private String name;
  13.     // 年 龄
  14.     private Integer age;
  16.     // 性别，1男性，2女性
  17.     private Integer sex;
  19.     // 出生日期
  20.     private Date birthday;
  22.     // 创建时间
  23.     private Date created;
  25.     // 更新时间
  26.     private Date updated;
  28.     // 备 注
  29.     private String note;
  31.     // toString 和 getter setter 方法
  32. }
  1. com.lxs.dao. JdbcDao
  1. @Repository
  2. public class JdbcDao {
  4.     @Autowired
  5.     private JdbcTemplate jdbcTemplate;
  7.     public List<User> findAll() {
  8.     return jdbcTemplate.query("select * from tb_user", new BeanPropertyRowMapper<> (User.class));
  9.     }
  11. }
  1. test.java.com.lxs.dao.JdbcDaoTest
  1. @RunWith(SpringRunner.class) @SpringBootTest
  2. public class JdbcDaoTest {
  4.     @Autowired
  5.     private JdbcDao jdbcDao;
  7.     @Test
  8.     public void findAll() {
  9.         List<User> list = jdbcDao.findAll(); 
  10.         for (User user : list) {
  11.             System.out.println(user);
  12.         }
  13.     }
  14. }

测试结果：

  1. User{id=1, userName='zhangsan', password='1', name='张三', age=18, sex=1, birthday=2019-02-27 00:00:00.0, created=2019-02-27 00:00:00.0, updated=2019-02-27 00:00:00.0, note='在学习Java...'}
  2. User{id=2, userName='lisi', password='1', name='李四', age=18, sex=1, birthday=2019-02-27 00:00:00.0, created=2019-02-27 00:00:00.0, updated=2019-02-27 00:00:00.0, note='在学习Java...'}
  3. User{id=3, userName='wangwu', password='1', name='王五', age=18, sex=1, birthday=2019-02-27 00:00:00.0, created=2019-02-27 00:00:00.0, updated=2019-02-27 00:00:00.0, note='在学习Java...'}
  4. User{id=4, userName='fanbingbing', password='1', name='范冰冰', age=18, sex=2, birthday=2019-02-27 00:00:00.0, created=2019-02-27 00:00:00.0, updated=2019-02-27 00:00:00.0, note='在学习Java...'}
  5. User{id=5, userName='guodegang', password='1', name='郭德纲', age=18, sex=1, birthday=2019-02-27 00:00:00.0, created=2019-02-27 00:00:00.0, updated=2019-02-27 00:00:00.0, note='在学习Java...'}

2 整合mybatis

  1. 配置 pom.xml 文件
  1. <!--mybatis -->
  2. <dependency>
  3.     <groupId>org.mybatis.spring.boot</groupId>
  4.     <artifactId>mybatis-spring-boot-starter</artifactId>
  5.     <version>1.3.2</version>
  6. </dependency
  1. application.properties
  1. # mybatis 别名扫描
  2. mybatis.type-aliases-package=com.lxs.domain  
  3. # mapper.xml文件位置,如果没有映射文件，请注释掉
  4. mybatis.mapper-locations=classpath:mappers/*.xml

  1. 直接使用刚才 jdbc 用到的实体类

  2. 配置接口文件

  1. package com.lxs.dao;
  3. import com.lxs.domain.User;
  5. import java.util.List;
  7. //ctrl shift t  快速创建测试类
  8. // 6.1方法
  9. //@Mapper
  10. public interface UserDao {
  12.     public List<User> findAll();
  14. }
  1. 映射文件
  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  4. <mapper namespace="com.lxs.dao.UserDao">
  6.     <select id="findAll" resultType="user">
  7.         select * from tb_user
  8.     </select>
  10. </mapper>

  1. Mapper的加载接口代理对象方式

    1. 在 Dao 接口使用 @Mapper 注解（不推荐）
    2. 在 Application.java 中设置 MapperScan, 注解扫描的包（推荐）
  1. @MapperScan("com.lxs.dao")
  2. public class Application {
  3.     ...
  4. }

测试

  1. 引入测试构建
  1. <dependency>
  2.     <groupId>org.springframework.boot</groupId>
  3.     <artifactId>spring-boot-starter-test</artifactId>
  4. </dependency>
  1. 测试代码
  1. package com.lxs.dao;
  3. import org.junit.Test;
  4. import org.junit.runner.RunWith;
  5. import org.springframework.beans.factory.annotation.Autowired;
  6. import org.springframework.boot.test.context.SpringBootTest;
  7. import org.springframework.test.context.junit4.SpringRunner;
  9. import static org.junit.Assert.*;
  11. @RunWith(SpringRunner.class)
  12. @SpringBootTest
  13. public class UserDaoTest {
  15.     @Autowired
  16.     private UserDao userDao; // 虽然报错, 但是可以运行成功...
  18.     @Test
  19.     public void findAll() {
  20.         userDao.findAll().forEach(user -> {
  21.             System.out.println(user);
  22.         });
  23.     }
  24. }

运行结果

  1. 2022-03-11 19:59:06.056 DEBUG 12600 --- [           main] com.lxs.dao.UserDao.findAll              : ==>  Preparing: select * from tb_user 
  2. 2022-03-11 19:59:06.101 DEBUG 12600 --- [           main] com.lxs.dao.UserDao.findAll              : ==> Parameters: 
  3. 2022-03-11 19:59:06.139 DEBUG 12600 --- [           main] com.lxs.dao.UserDao.findAll              : <==      Total: 5
  4. User{id=1, userName='null', password='1', name='张三', age=18, sex=1, birthday=Wed Feb 27 00:00:00 CST 2019, created=Wed Feb 27 00:00:00 CST 2019, updated=Wed Feb 27 00:00:00 CST 2019, note='在学习Java...'}
  5. User{id=2, userName='null', password='1', name='李四', age=18, sex=1, birthday=Wed Feb 27 00:00:00 CST 2019, created=Wed Feb 27 00:00:00 CST 2019, updated=Wed Feb 27 00:00:00 CST 2019, note='在学习Java...'}
  6. User{id=3, userName='null', password='1', name='王五', age=18, sex=1, birthday=Wed Feb 27 00:00:00 CST 2019, created=Wed Feb 27 00:00:00 CST 2019, updated=Wed Feb 27 00:00:00 CST 2019, note='在学习Java...'}
  7. User{id=4, userName='null', password='1', name='范冰冰', age=18, sex=2, birthday=Wed Feb 27 00:00:00 CST 2019, created=Wed Feb 27 00:00:00 CST 2019, updated=Wed Feb 27 00:00:00 CST 2019, note='在学习Java...'}
  8. User{id=5, userName='null', password='1', name='郭德纲', age=18, sex=1, birthday=Wed Feb 27 00:00:00 CST 2019, created=Wed Feb 27 00:00:00 CST 2019, updated=Wed Feb 27 00:00:00 CST 2019, note='在学习Java...'}