MyBatis查询数据库
MyBatis 是什么?
MyBatis 是更简单完成程序和数据库交互的⼯具,也就是更简单的操作和读取数据库⼯具。
MyBatis 是⼀款优秀的持久层框架,它⽀持⾃定义 SQL、存储过程以及⾼级映射。MyBatis 去除了⼏ 乎所有的 JDBC 代码以及设置参数和获取结果集的⼯作。MyBatis 可以通过简单的 XML 或注解来配置 和映射原始类型、接⼝和 Java POJO(Plain Old Java Objects,普通⽼式 Java 对象)为数据库中的记录。
- JDBC 的操作流程:
- 创建数据库连接池 DataSource
- 通过 DataSource 获取数据库连接 Connection
- 编写要执⾏带 ? 占位符的 SQL 语句
- 通过 Connection 及 SQL 创建操作命令对象 Statement
- 替换占位符:指定要替换的数据库字段类型,占位符索引及要替换的值 6. 使⽤ Statement 执⾏ SQL 语句
- 查询操作:返回结果集 ResultSet,更新操作:返回更新的数量
- 处理结果集
- 释放资源
对于 JDBC 来说,整个操作⾮常的繁琐,我们不但要拼接每⼀个参 数,⽽且还要按照模板代码的⽅式,⼀步步的操作数据库,并且在每次操作完,还要⼿动关闭连接等, ⽽所有的这些操作步骤都需要在每个⽅法中重复书写。
MyBatis 在整个框架中的定位,框架交互流程图:
MyBatis 也是⼀个 ORM 框架,ORM(Object Relational Mapping),即对象关系映射。在⾯向 >对象编程语⾔中,将关系型数据库中的数据与对象建⽴起映射关系,进⽽⾃动的完成数据与对象 >的互相转换:
- 将输⼊数据(即传⼊对象)+SQL 映射成原⽣ SQL
- 将结果集映射为返回对象,即输出对象
ORM 把数据库映射为对象:
数据库表(table)–> 类(class)
记录(record,⾏数据)–> 对象(object)
字段(field) --> 对象的属性(attribute)
⼀般的 ORM 框架,会将数据库模型的每张表都映射为⼀个 Java 类。也就是说使⽤ MyBatis 可以像操作对象⼀样来操作数据库中的表,可以实现对象和数据库表之间 的转换。
MyBatis的基本使用
创建数据库和表
-- 创建数据库
drop database if exists mycnblog;
create database mycnblog DEFAULT CHARACTER SET utf8mb4;
-- 使⽤数据数据 use mycnblog;
-- 创建表[⽤户表]
drop table if exists userinfo;
create table userinfo(
id int primary key auto_increment,
username varchar(100) not null,
password varchar(32) not null,
photo varchar(500) default '',
createtime datetime default now(),
updatetime datetime default now(),
`state` int default 1
) default charset 'utf8mb4';
-- 创建⽂章表
drop table if exists articleinfo;
create table articleinfo(
id int primary key auto_increment,
title varchar(100) not null,
content text not null,
createtime datetime default now(),
updatetime datetime default now(),
uid int not null,
rcount int not null default 1,
`state` int default 1
)default charset 'utf8mb4';
-- 创建视频表
drop table if exists videoinfo;
create table videoinfo(
vid int primary key,
`title` varchar(250),
`url` varchar(1000),
createtime datetime default now(),
updatetime datetime default now(),
uid int
)default charset 'utf8mb4';
-- 添加⼀个⽤户信息
INSERT INTO `mycnblog`.`userinfo` (`id`, `username`, `password`, `photo`, `createtime`, `updatetime`, `state`) VALUES
(1, 'admin', 'admin', '', '2021-12-06 17:10:48', '2021-12-06 17:10:48', 1)
;
-- ⽂章添加测试数据
insert into articleinfo(title,content,uid)
values('Java','Java正⽂',1);
-- 添加视频
insert into videoinfo(vid,title,url,uid) values(1,'java title','http://ww w.baidu.com',1);
添加MyBatis框架⽀持
- ⽼项⽬添加MyBatis
添加框架⽀持:
<!-- 添加 MyBatis 框架 -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.4</version>
</dependency>
<!-- 添加 MySQL 驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
<scope>runtime</scope>
</dependency>
使⽤EditStarters插件快速添加:
- 新项⽬添加MyBatis
新项⽬创建 Spring Boot 项⽬的时候添加引⽤就可以了。
配置连接字符串和MyBatis
- 配置连接字符串
在application.yml 添加如下内容:
如果使⽤ mysql-connector-java 是 5.x 之前的使⽤的是“com.mysql.jdbc.Driver”,如果是⼤于 5.x 使⽤的是“com.mysql.cj.jdbc.Driver”
# 数据库连接配置
spring:
datasource:
url: jdbc:mysql://localhost:3306/mycnblog?characterEncoding=utf8&useSSL =false
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
- 配置 MyBatis 中的 XML 路径
# 配置 mybatis xml 的⽂件路径,在 resources/mapper 创建所有表的 xml ⽂件 mybatis:
mapper-locations: classpath:mapper/**Mapper.xml
添加业务代码
后端开发的工程思路:
- 添加实体类
import lombok.Data;
import java.util.Date;
@Data
public class User {
private Integer id;
private String username;
private String password;
private String photo;
private Date createTime;
private Date updateTime;
}
- 添加 mapper 接⼝
import org.apache.ibatis.annotations.Mapper; import java.util.List;
@Mapper
public interface UserMapper {
public List<User> getAll();
}
- 添加 UserMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybati s.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.mapper.UserMapper">
</mapper>
UserMapper.xml 查询所有⽤户的具体实现 SQL:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybati s.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.mapper.UserMapper">
<select id="getAll" resultType="com.example.demo.model.User">
select * from userinfo
</select>
</mapper>
<mapper>标签:需要指定 namespace 属性,表示命名空间,值为 mapper 接⼝的全限定
名,包括全包名.类名。
<select>查询标签:是⽤来执⾏数据库的查询操作的:
id:是和 Interface(接⼝)中定义的⽅法名称⼀样的,表示对接⼝的具体实现⽅法。
resultType:是返回的数据类型,也就是开头我们定义的实体类。
- 添加 Service
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;
@Service
public class UserService {
@Resource
private UserMapper userMapper;
public List<User> getAll() {
return userMapper.getAll();
}
}
- 添加 Controller
import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
import java.util.List;
@RestController
@RequestMapping("/u")
public class UserController {
@Resource
private UserService userService;
@RequestMapping("/getall")
public List<User> getAll(){
return userService.getAll();
}
}
- 使⽤ postman 测试
增、删、改操作
增加操作
controller 实现代码:
@RequestMapping(value = "/add",method = RequestMethod.POST) public Integer add(@RequestBody User user){
return userService.getAdd(user);
}
mapper interface:
Integer add(User user);
mapper.xml
<insert id="add">
insert into userinfo(username,password,photo,state)
values(#{username},#{password},#{photo},1)
</insert>
Postman 添加访问:
默认情况下返回的是受影响的⾏数。
{"username":"mysql","password":"mysql","photo":"img.png"}
返回⾃增 id
controller 实现代码:
@RequestMapping(value = "/add2", method = RequestMethod.POST) public Integer add2(@RequestBody User user) {
userService.getAdd2(user);
return user.getId();
}
mapper 接⼝:
@Mapper
public interface UserMapper {
// 添加,返回⾃增id
void add2(User user);
}
mapper.xml 实现如下:
<!-- 返回⾃增id -->
<insert id="add2" useGeneratedKeys="true" keyProperty="id">
insert into userinfo(username,password,photo,state)
values(#{username},#{password},#{photo},1)
</insert>
- useGeneratedKeys:这会令 MyBatis 使⽤ JDBC 的 getGeneratedKeys ⽅法来取出由数据 库内部⽣成的主键(⽐如:像 MySQL 和 SQL Server 这样的关系型数据库管理系统的⾃动 递增字段),默认值:false。
- keyColumn:设置⽣成键值在表中的列名,在某些数据库(像 PostgreSQL)中,当主键列 不是表中的第⼀列的时候,是必须设置的。如果⽣成列不⽌⼀个,可以⽤逗号分隔多个属性 名称。
- keyProperty:指定能够唯⼀识别对象的属性,MyBatis 会使⽤ getGeneratedKeys 的返回 值或 insert 语句的 selectKey ⼦元素设置它的值,默认值:未设置(unset)。如果⽣成列 不⽌⼀个,可以⽤逗号分隔多个属性名称。
postman 返回结果:
修改⽤户操作
controller:
/**
* 修改操作
*
* @param id
* @param name
* @return
*/
@RequestMapping("/update")
public Integer update(Integer id, String name) {
return userService.update(id, name);
}
mapper.xml 实现代码:
<update id="update">
update userinfo set username=#{name} where id=#{id}
</update>
删除⽤户操作
<delete id="delById" parameterType="java.lang.Integer">
delete from userinfo where id=#{id}
</delete>
查询操作详解
参数占位符 #{} 和 ${}
- #{}:预编译处理。
- ${}:字符直接替换。
- 预编译处理是指:MyBatis 在处理#{}时,会将 SQL 中的 #{} 替换为?号,使⽤ PreparedStatement 的 set ⽅法来赋值。直接替换:是MyBatis 在处理 ${} 时,就是把 ${} 替换成变量的值。
- 使⽤ ${sort} 可以实现排序查询,⽽使⽤ #{sort} 就不能实现排序查询了,因为当使⽤ #{sort} 查询时, 如果传递的值为 String 则会加单引号,就会导致 sql 错误。
SQL 注⼊问题
<select id="isLogin" resultType="com.example.demo.model.User">
select * from userinfo where username='${name}' and password='${pwd}' </select>
sql 注⼊代码:“’ or 1='1”
单表查询
⽤于查询的字段,尽量使⽤ #{} 预查询的⽅式。
Controller 实现代码如下:
@RequestMapping("/getuser")
public User getUserById(Integer id) {
return userService.getUserById(id);
}
Mapper.xml 实现代码如下:
<select id="getUserById" resultType="com.example.demo.model.User">
select * from userinfo where id=#{id}
</select>
like 查询
like 使⽤ #{} 报错,相当于: select * from userinfo where username like ‘%‘username’%’。
<select id="findUserByName2" resultType="com.example.demo.model.User">
select * from userinfo where username like '%#{username}%';
</select>
使⽤ mysql 的内置函数 concat() 来处理:
<select id="findUserByName3" resultType="com.example.demo.model.User">
select * from userinfo where username like concat('%',#{usernam e},'%');
</select>
多表查询
返回值类型
如果是增、删、改返回搜影响的⾏数,那么在 mapper.xml 中是可以不设置返回的类型的。
查询不设置返回类型会报错:
controller 代码:
@RequestMapping("/getname")
public String getNameById(Integer id) {
return userService.getNameById(id);
}
mapper.xml 实现代码:
<select id="getNameById">
select username from userinfo where id=#{id}
</select>
访问接⼝执⾏结果显示运⾏了⼀个查询但没有找到结果映射:
查询标签来说⾄少需要两个属性:
id 属性:⽤于标识实现接⼝中的那个⽅法;
结果映射属性:结果映射有两种实现标签: 和 。
resultType
绝⼤数查询场景可以使⽤ resultType 进⾏返回,使⽤⽅便,直接定义到某个实体类即可。
<select id="getNameById" resultType="java.lang.String">
select username from userinfo where id=#{id}
</select>
resultMap
字段名称和程序中的属性名不同的情况,可使⽤ resultMap 配置映射;
⼀对⼀和⼀对多关系可以使⽤ resultMap 映射并查询数据。
- 字段名和属性名不同的情况
程序中的属性:
mapper.xml 代码:
<select id="getUserById" resultType="com.example.demo.model.User">
select * from userinfo where id=#{id}
</select>
使用resultType查询的结果:
使用resultMap查询:
mapper.xml:
<resultMap id="BaseMap" type="com.example.demo.model.User">
<id column="id" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="pwd"></result>
</resultMap>
<select id="getUserById" resultMap="com.example.demo.mapper.UserMapper.Base Map">
select * from userinfo where id=#{id}
</select>
查询结果:
多表查询
在多表查询时,如果使⽤ resultType 标签,在⼀个类中包含了另⼀个对象是查询不出来被包含的对象的
实体类:
@Data
public class ArticleInfo {
private Integer id;
private String title;
private String content;
private LocalDateTime createtime;
private LocalDateTime updatetime;
private Integer rcount;
// 包含了 userinfo 对象
private UserInfo user;
}
程序的执⾏结果:
⼀对⼀的表映射
⼀对⼀映射要使⽤ 标签,具体实现(⼀篇⽂章只对应⼀个作者):
<resultMap id="BaseMap" type="com.example.demo.model.ArticleInfo">
<id property="id" column="id"></id>
<result property="title" column="title"></result>
<result property="content" column="content"></result>
<result property="createtime" column="createtime"></result>
<result property="updatetime" column="updatetime"></result>
<result property="uid" column="uid"></result>
<result property="rcount" column="rcount"></result>
<result property="state" column="state"></result>
<association property="user"
resultMap="com.example.demo.mapper.UserMapper.BaseMap"
columnPrefix="u_">
</association>
</resultMap>
<select id="getAll" resultMap="BaseMap">
select a.*,u.username u_username from articleinfo a
left join userinfo u on a.uid=u.id
</select>