springboot 整合mybatis 编写第一个查询demo

目前主流的数据库持久层有springdata jpa与mybatis两种方式,其中mybatis在国内使用更为广泛,一起来边写第一个mybatis demo吧!
项目源码下载地址:码云仓库
首先看下最终代码项目目录结构!

1. 数据库准备阶段

本demo采用的是mysql数据库,首先在mysql建立一个springboot数据库,建立一张学生表,并手动插入一条条数据.

CREATE TABLE `student`(
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '唯一标识id',
  `name` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '姓名',
  `age` int(3) NOT NULL COMMENT '年龄',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
insert into student(id,name,age) VALUES(1,'springboot整合mybatis学习笔记',22);

2. 引入依赖mybatis 和 mysql驱动

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.6.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.springboot</groupId>
    <artifactId>blog</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>blog</name>
    <description>Spring Boot blog</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

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

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

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

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

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

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

3. 编写mybatis 与 mysql配置

spring:
  thymeleaf:
    cache: false #关闭缓存

  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1/springboot?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
    username: root
    password: root

mybatis:
  type-aliases-package: com.springboot.blog.entity
  mapper-locations: classpath:mapping/*Mapper.xml
  configuration:
    map-underscore-to-camel-case: true
    default-fetch-size: 100
    default-statement-timeout: 30

注意:

1. spring.datasource.url中serverTimezone 时区必须填写;

2. mybatis.type-aliases-package要改为自己项目相应包名否则会出错;

   4. 编写student entity实体类

   public class Student  {
    private static final long serialVersionUID = -91969758749726312L;
    /**
     * 唯一标识id
     */
    private Integer id;
    /**
     * 姓名
     */
    private String name;
    /**
     * 年龄
     */
    private Integer age;
   
    public static long getSerialVersionUID() {
        return serialVersionUID;
    }
   
    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;
    }
   }
   

   5. 编写Mapper接口

   ```
   @Mapper
   @Repository
   public interface StudentMapper {

   /**

   • 通过ID查询单条数据
     *
   • @param id 主键

   • @return 实例对象
     */
     Student queryById(Integer id);

     /**

   • 查询指定行数据
     *
   • @param offset 查询起始位置
   • @param limit 查询条数
   • @return 对象列表
     */
     List<Student> queryAllByLimit(@Param(“offset”) int offset, @Param(“limit”) int limit);

/**
 * 通过实体作为筛选条件查询
 *
 * @param student 实例对象
 * @return 对象列表
 */
List<Student> queryAll(Student student);

/**
 * 新增数据
 *
 * @param student 实例对象
 * @return 影响行数
 */
int insert(Student student);

/**
 * 修改数据
 *
 * @param student 实例对象
 * @return 影响行数
 */
int update(Student student);

/**
 * 通过主键删除数据
 *
 * @param id 主键
 * @return 影响行数
 */
int deleteById(Integer id);

}

###6. 编写StudentService接口

public interface StudentService {
/**

 * 通过ID查询单条数据
 *
 * @param id 主键
 * @return 实例对象
 */
Student queryById(Integer id);

/**
 * 查询多条数据
 *
 * @param offset 查询起始位置
 * @param limit 查询条数
 * @return 对象列表
 */
List<Student> queryAllByLimit(int offset, int limit);

/**
 * 新增数据
 *
 * @param student 实例对象
 * @return 实例对象
 */
Student insert(Student student);

/**
 * 修改数据
 *
 * @param student 实例对象
 * @return 实例对象
 */
Student update(Student student);

/**
 * 通过主键删除数据
 *
 * @param id 主键
 * @return 是否成功
 */
boolean deleteById(Integer id);

}

###7 编写StudentServiceImpl实现类

@Service
public class StudentServiceImpl implements StudentService{
@Autowired
private StudentMapper studentMapper;

/**
 * 通过ID查询单条数据
 *
 * @param id 主键
 * @return 实例对象
 */
@Override
public Student queryById(Integer id) {
    return this.studentMapper.queryById(id);
}

/**
 * 查询多条数据
 *
 * @param offset 查询起始位置
 * @param limit 查询条数
 * @return 对象列表
 */
@Override
public List<Student> queryAllByLimit(int offset, int limit) {
    return this.studentMapper.queryAllByLimit(offset, limit);
}

/**
 * 新增数据
 *
 * @param student 实例对象
 * @return 实例对象
 */
@Override
public Student insert(Student student) {
    this.studentMapper.insert(student);
    return student;
}

/**
 * 修改数据
 *
 * @param student 实例对象
 * @return 实例对象
 */
@Override
public Student update(Student student) {
    this.studentMapper.update(student);
    return this.queryById(student.getId());
}

/**
 * 通过主键删除数据
 *
 * @param id 主键
 * @return 是否成功
 */
@Override
public boolean deleteById(Integer id) {
    return this.studentMapper.deleteById(id) > 0;
}

}

###8 编写StudentController

@RestController
@RequestMapping(“student”)
public class StudentController {

@Autowired
private StudentService service;

/**
 * 通过主键查询单条数据
 *
 * @param id 主键
 * @return 单条数据
 */
@GetMapping("selectOne")
public Student selectOne(Integer id) {
    return this.service.queryById(id);
}

}

###9 编写StudentMapper.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">

<mapper namespace="com.springboot.blog.mapper.StudentMapper">
<resultMap type="com.springboot.blog.entity.Student" id="StudentMap">
<result property="id" column="id" jdbcType="INTEGER"/>
<result property="name" column="name" jdbcType="VARCHAR"/>
<result property="age" column="age" jdbcType="INTEGER"/>
</resultMap>

<!--查询单个-->
<select id="queryById" resultMap="StudentMap">
    select
      id, name, age
    from student
    where id = #{id}
</select>

<!--查询指定行数据-->
<select id="queryAllByLimit" resultMap="StudentMap">
    select
      id, name, age
    from student
    limit #{offset}, #{limit}
</select>

<!--通过实体作为筛选条件查询-->
<select id="queryAll" resultMap="StudentMap">
    select
    id, name, age
    from student
    <where>
        <if test="id != null">
            and id = #{id}
        </if>
        <if test="name != null and name != ''">
            and name = #{name}
        </if>
        <if test="age != null">
            and age = #{age}
        </if>
    </where>
</select>

<!--新增所有列-->
<insert id="insert" keyProperty="id" useGeneratedKeys="true">
    insert into student(name, age)
    values (#{name}, #{age})
</insert>

<!--通过主键修改数据-->
<update id="update">
    update student
    <set>
        <if test="name != null and name != ''">
            name = #{name},
        </if>
        <if test="age != null">
            age = #{age},
        </if>
    </set>
    where id = #{id}
</update>

<!--通过主键删除-->
<delete id="deleteById">
    delete from student where id = #{id}
</delete>

</mapper>
```
注意:

<mapper namespace="com.springboot.blog.mapper.StudentMapper"> namespace需要修改

<resultMap type="com.springboot.blog.entity.Student" id="StudentMap"> type需要修改

最后运行项目:访问http://localhost:8080/student/selectOne?id=1显示成功!
遇到的问题:
解决org.apache.ibatis.binding.BindingException: Invalid bound statement (not found)问题 参考https://blog.csdn.net/sundacheng1989/article/details/81630370大多数配置参数没有修改导致的错误.