springboot 整合mybatis 编写第一个查询demo
2020-05-05 本文已影响0人
思议岁月
目前主流的数据库持久层有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
注意:
-
spring.datasource.url中serverTimezone 时区必须填写; -
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显示
image.png
成功!遇到的问题:
解决org.apache.ibatis.binding.BindingException: Invalid bound statement (not found)问题 参考https://blog.csdn.net/sundacheng1989/article/details/81630370大多数配置参数没有修改导致的错误.
