Spring Boot访问关系型数据库三剑客JDBC、JPA、Mybatis

背景

从业十多年Spring JDBC、JPA、Mybatis三种持久层框架都使用过，各有千秋，当下的项目使用Mybatis最多。之前的项目还都是使用xml配置文件的框架，最近两年才用Spring Boot多起来，因为老程序员不干屈居人后的进取心，且不想搞那繁琐而无边际的业务需求，所以利用业余时间扑下心来好好看看官文。随着年龄的增长，精力、记性却不断递减，怕时间长了不知其初心，所以记录之。
在官方文档向导中存在关于JDBC和JPA的资料，我想从实际项目的角度出发去学习并记录，所以我以mysql数据库作为存储，从配置数据源、JDBC访问数据、JPA访问数据、Mybatis访问数据几方面去学习。

配置数据源

引入mysql

新建一个Spring Boot工程，引入mysql驱动。

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>

yml文件配置项

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test
spring.datasource.username=root
spring.datasource.password=mysql3306@118
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

Spring Data JDBC

新建一个web工程，添加jdbc、web等相关依赖包。

添加依赖

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

jar包依赖如下：

新建Entity

public class JdbcUser {

    private Integer id;
    private String name;
    private String email;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    @Override
    public String toString() {
        return "JpaUser{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", email='" + email + '\'' +
                '}';
    }
}

控制器演示代码

@RestController
public class JdbcController {

    protected final static Logger logger = LoggerFactory.getLogger(JdbcController.class);

    @Autowired
    public JdbcTemplate jdbcTemplate;

    @GetMapping("/jdbc/users")
    public List<JdbcUser> users() {
        return jdbcTemplate.query("select id,name,email from jdbc_user", new RowMapper<JdbcUser>() {
            @Override
            public JdbcUser mapRow(ResultSet rs, int rowNum) throws SQLException {
                JdbcUser jdbcUser = new JdbcUser();
                jdbcUser.setId(rs.getInt(1));
                jdbcUser.setName(rs.getString(2));
                jdbcUser.setEmail(rs.getString(3));

                return jdbcUser;
            }
        });
    }
}

浏览器访问结果

http://127.0.0.1:8080/jdbc/users

[{"id":4,"name":"李田","email":"litian@126.com"},{"id":5,"name":"王华","email":"wanghua@126.com"}]

Spring Data JPA

添加依赖

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

新建Entity

@Entity
public class JpaUser {

    @Id
    private Integer id;
    private String name;
    private String email;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    @Override
    public String toString() {
        return "JpaUser{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", email='" + email + '\'' +
                '}';
    }
}

新建Repository接口

public interface UserRepository extends JpaRepository<JpaUser, Integer>  {
}

控制器演示代码

@RestController
public class TestController {

    protected final static Logger logger = LoggerFactory.getLogger(TestController.class);

    @Autowired
    private UserRepository userRepository;

    @GetMapping("/jpa/users")
    public List<JpaUser> users() {
        return userRepository.findAll();
    }

}

浏览器访问结果

[{"id":4,"name":"赵五","email":"zhaowu@126.com"}]

Mybatis

添加依赖

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

新建Entity

public class Teacher {

    private Integer id;
    private String name;
    private Integer age;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "Teacher{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}

新建Mapper接口

@Repository
public interface TeacherMapper {

    public int insertTeacher(Teacher teacher);

    public List<Teacher> selectList();
}

控制器演示代码

@RestController
public class MybatisController {

    protected final static Logger logger = LoggerFactory.getLogger(MybatisController.class);

    @Autowired
    public TeacherMapper teacherMapper;


    @GetMapping("mybatis/teachers")
    public List<Teacher> teachers() {
        Teacher teacher = new Teacher();
        teacher.setName("李继");
        teacher.setAge(35);
        teacherMapper.insertTeacher(teacher);

        return teacherMapper.selectList();
    }
}

yml配置项

除了数据源的配置外，添加mybatis的配置，如下：

mybatis.mapper-locations=classpath:config/*Mapper.xml
mybatis.type-aliases-package=com.example.entity

Application启动

@SpringBootApplication
@MapperScan(basePackages = "com.example.dao", annotationClass = Repository.class)
public class MysqlMybatisDemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(MysqlMybatisDemoApplication.class, args);
    }

}

浏览器访问结果

http://127.0.0.1:8080/mybatis/teachers

[{"id":1,"name":"李继","age":35},{"id":2,"name":"李继","age":35},{"id":3,"name":"李继","age":35}]

总结

通过学习，三种框架基本情况如下：