SpringBoot基础教程(五) | Mybatis篇

Mybatis介绍

MyBatis 是支持定制化 SQL、存储过程以及高级映射的优秀的持久层框架。MyBatis 避免了几乎所有的 JDBC 代码和手工设置参数以及抽取结果集。MyBatis 使用简单的 XML 或注解来配置和映射基本体，将接口和 Java 的 POJOs(Plain Old Java Objects,普通的 Java对象)映射成数据库中的记录。

• 优点

  1、简单易学：mybatis本身就很小且简单。没有任何第三方依赖，最简单安装只要两个jar文件+配置几个sql映射文件易于学习，易于使用，通过文档和源代码，可以比较完全的掌握它的设计思路和实现。
  2、灵活：mybatis不会对应用程序或者数据库的现有设计强加任何影响。 sql可以写在xml里，便于统一管理和优化。
  3、提供映射标签，支持对象与数据库的orm字段关系映射。
  4、提供对象关系映射标签，支持对象关系组建维护。
  5、提供xml标签，支持编写动态sql。

• 缺点

  1、编写SQL语句时工作量很大，尤其是字段多、关联表多时，更是如此。
  2、SQL语句依赖于数据库，导致数据库移植性差，不能更换数据库。
  3、框架还是比较简陋，功能尚有缺失，虽然简化了数据绑定代码，但是整个底层数据库查询实际还是要自己写的，工作量也比较大，而且不太容易适应快速数据库修改。

导入项目依赖

       <!--mybatis场景启动器-->
       <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.1</version>
        </dependency>

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

编写配置文件

# 数据库相关配置
spring.datasource.username=root
spring.datasource.password=yzhroot
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=CTT
# mybatis相关配置
# 支持驼峰式与下划线转换
mybatis.configuration.map-underscore-to-camel-case=true

数据库建表语句

CREATE TABLE `department` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `create_date` date DEFAULT NULL,
  `username` varchar(255) DEFAULT NULL,
  `department_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department_foreign` (`department_id`)
) DEFAULT CHARSET=utf8

编写实体类

/**
 * 用户实体类
 */
public class User {

    private Long id;

    private String username;

    private Date createDate;

    private Department department;

    public Long getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Date getCreateDate() {
        return createDate;
    }

    public void setCreateDate(Date createDate) {
        this.createDate = createDate;
    }

    public Department getDepartment() {
        return department;
    }

    public void setDepartment(Department department) {
        this.department = department;
    }
}

/**
 * 部门实体类
 */
public class Department {

    private Long id;

    private String name;

    private List<User> users;

    public Long getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public List<User> getUsers() {
        return users;
    }

    public void setUsers(List<User> users) {
        this.users = users;
    }
}

编写持久层

由于SQL并不复杂，所以这里我使用注解的形式实现持久层功能。

@Mapper
public interface UserDao {

    //根据department_id查询user
    @Select("Select * from user where department_id = #{departmentId}")
    List<User> queryUserByDepartmentId(@Param("departmentId")Long departmentId);

    //根据userId查询user信息
    @Select("Select * from user where id = #{userId}")
    @Results({
            @Result(column = "department_id",property = "department",javaType = Department.class,one = @One(select = "training.springboot.mybatis.dao.DepartmentDao.queryDepartmentById"))
    })
    User queryUserbyUserId(@Param("userId")Long id);
}

@Mapper
public interface DepartmentDao {

    //查询所有部门信息
    @Select("Select * from department")
    @Results({
            @Result(column = "id",property = "users",javaType = List.class,many = @Many(select = "training.springboot.mybatis.dao.UserDao.queryUserByDepartmentId"))
    })
    List<Department> queryAllDepartment();

    //根据部门ID查询部门信息
    @Select("Select * from department where id = #{id}")
    Department queryDepartmentById(@Param("id")Long id);

    //插入一条部门记录
    @Insert("Insert into department(name) values(#{name})")
    Integer addDepartment(Department department);

    //删除一条部门记录
    @Delete("delete from department where id = #{id}")
    Integer deleteDepartmentById(@Param("id")Long id);

}

实现一对多和多对一的关系只需要使用@Results注解即可。

测试

@SpringBootTest
class MybatisApplicationTests {

    @Autowired
    DepartmentDao departmentDao;

    @Autowired
    UserDao userDao;

    @Test
    public void testQueryUser(){
        System.out.println(userDao.queryUserbyUserId((long) 1));
    }

    @Test
    public void testQueryUserByDepartmentId(){
        System.out.println(userDao.queryUserByDepartmentId((long) 1));
    }

    @Test
    public void testQueryDepartmentById(){
        System.out.println(departmentDao.queryDepartmentById((long) 1));
    }

    @Test
    public void testQueryAllDepartments(){
        System.out.println(departmentDao.queryAllDepartment());
    }

    @Test
    public void testInsertDepartment(){
        Department department = new Department();
        department.setName("市场部");
        departmentDao.addDepartment(department);
    }

    @Test
    public void testDeleteDepartment(){
        departmentDao.deleteDepartmentById((long) 2);
    }
}

完整的工程代码链接：https://github.com/youzhihua/springboot-training