MyBatis的基础操作
1. MyBatis XML配置文件
1.1 简单介绍
使⽤Mybatis的注解方式,主要是来完成⼀些简单的增删改查功能.
如果需要实现复杂的SQL功能,建议使⽤XML来配置映射语句,也就是将SQL语句写在XML配置⽂件中。
MyBatis XML的⽅式需要以下两步:
1.配置数据库连接字符串和MyBatis
2.写持久层代码
1.2 配置连接字符串和MyBatis
此步骤需要进⾏两项设置,数据库连接字符串设置和 MyBatis 的 XML ⽂件配置。
application.yml
⽂件, 配置内容如下:
# 配置 mybatis xml 的⽂件路径,在 resources/mapper 创建所有表的 xml ⽂件
mybatis:
mapper-locations: classpath:mapper/**Mapper.xml
application.properties
⽂件, 配置内容如下:
# 配置 mybatis xml 的⽂件路径,在 resources/mapper 创建所有表的 xml ⽂件
mybatis.mapper-locations=classpath:mapper/**Mapper.xml
1.3 XMl文件实现–分层
持久层代码分两部分:
(1)⽅法定义 Interface
(2)⽅法实现: XXX.xml
1.4 XMl文件实现–举例
举例:
(0)详细的准备工作请看MyBatis入门
在java/org.example.mybatis.model下创建一个实体类:
@Data
public class UserInfo {
private Integer id;
private String username;
private String password;
private Integer age;
private Integer gender;
private String phone;
private Integer deleteFlag;
private Date createTime;
private Date updateTime;
}
(1)Mapper接口:
package org.example.mybatis.mapper;
import org.apache.ibatis.annotations.Mapper;
import org.example.mybatis.model.UserInfo;
import java.util.List;
@Mapper
public interface UserInfoXMLMapper {
//定义接口
List<UserInfo> queryAllUser();
}
(2)在resources下创建mapper目录
(3)在resources/mapper
创建UserInfoXMLMapper.xml
文件
如果创建的新文件中有内容,把文件中的内容全部删去,添加以下内容(数据持久成的实现,MyBatis 的固定 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="org.example.mybatis.mapper.UserInfoXMLMapper">
</mapper>
注意:<mapper namespace="org.example.mybatis.mapper.UserInfoXMLMapper"></mapper>
中的namespace
= 创建接口类的路径+接口名称
图解:
(4)查询所有⽤⼾的具体实现 :
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.example.mybatis.mapper.UserInfoXMLMapper">
<select id="queryAllUser" resultType="org.example.mybatis.model.UserInfo">
select id,username, password, age, gender, phone,delete_flag,
create_time,update_time from user_info
</select>
</mapper>
以下是对以上标签的说明:
(1)<Mapper>标签:需要指定 nemespace 属性,表示命名空间,值为 mapper 接⼝的全限定名,等于 括全包名.类名
(2)<select>查询标签:是⽤来执⾏数据库的查询操作的,其中的 id
和 Mapper接口中的方法名是一样的,表示对接口的具体实现方法。resultType
是返回的数据类型,也就是开头我们定义的实体类。
测试代码:
@Slf4j
@SpringBootTest //启动Sring 容器
class UserInfoXMLMapperTest {
@Autowired
private UserInfoXMLMapper userInfoXMLMapper;
@Test
void queryAllUser() {
userInfoXMLMapper.queryAllUser();
}
}
运行结果:
2.增删改查操作
2.1 增(insert)
2.1.1 不使用@Param
UserInfoXMLMapper接口:
@Mapper
public interface UserInfoXMLMapper {
//定义接口
Integer insert(UserInfo userinfo);
}
UserInfoXMLMapper.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="org.example.mybatis.mapper.UserInfoXMLMapper">
//插入
<insert id = "insert" >
insert into user_info (username, password, age, gender, phone)
values(#{username}, #{password},#{age}, #{gender},#{phone})
</insert>
</mapper>
接口中方法接收的参数会自动与 #{username}, #{password},#{age}, #{gender},#{phone} 匹配。
测试代码:
@Slf4j
@SpringBootTest //启动Sring 容器
class UserInfoXMLMapperTest {
@Autowired
private UserInfoXMLMapper userInfoXMLMapper;
@Test
void insert() {
UserInfo userInfo= new UserInfo();
userInfo.setUsername("6666");
userInfo.setPassword("6666");
userInfo.setAge(19);
userInfo.setGender(1);
userInfo.setPhone("6666666");
userInfoXMLMapper.insert(userInfo);
}
}
运行结果:
MySQL:
2.1.2 用@Param
UserInfoXMLMapper接口:
@Mapper
public interface UserInfoXMLMapper {
//定义接口
Integer insert(@Param("userInfo") UserInfo uInfo);
}
UserInfoXMLMapper.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="org.example.mybatis.mapper.UserInfoXMLMapper">
//插入
<insert id = "insert" >
insert into user_info (username, password, age, gender, phone)
values(#{username}, #{password},#{age}, #{gender},#{phone})
</insert>
</mapper>
测试代码:
@Slf4j
@SpringBootTest //启动Sring 容器
class UserInfoXMLMapperTest {
@Autowired
private UserInfoXMLMapper userInfoXMLMapper;
@Test
void insert() {
UserInfo userInfo= new UserInfo();
userInfo.setUsername("6666");
userInfo.setPassword("6666");
userInfo.setAge(19);
userInfo.setGender(1);
userInfo.setPhone("6666666");
userInfoXMLMapper.insert(userInfo);
}
}
运行结果:
很明显,发生报错,报错的内容是:没有找到usernam参数,但是有可用的参数userInfo
说明:当使用@Param把传的参数改为userInfo
后,#{username}, #{password},#{age}, #{gender},#{phone} 就不能自动从对象中匹配值,需要使用明确的指出数据来自哪。
修改后的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="org.example.mybatis.mapper.UserInfoXMLMapper">
<insert id = "insert" >
insert into user_info (username, password, age, gender, phone)
values(#{userInfo.username}, #{userInfo.password},
#{userInfo.age}, #{userInfo.gender},#{userInfo.phone})
</insert>
</mapper>
测试代码:
@Slf4j
@SpringBootTest //启动Sring 容器
class UserInfoXMLMapperTest {
@Autowired
private UserInfoXMLMapper userInfoXMLMapper;
@Test
void insert() {
UserInfo userInfo= new UserInfo();
userInfo.setUsername("6666");
userInfo.setPassword("6666");
userInfo.setAge(19);
userInfo.setGender(1);
userInfo.setPhone("6666666");
userInfoXMLMapper.insert(userInfo);
}
}
运行结果:
MySQL:
2.1.3 返回自增键
UserInfoXMLMapper接口:
@Mapper
public interface UserInfoXMLMapper {
//定义接口
Integer insert(UserInfo userInfo);
}
UserInfoXMLMapper.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="org.example.mybatis.mapper.UserInfoXMLMapper">
//插入
<insert id = "insert" useGeneratedKeys="true" keyProperty="id">
insert into user_info (username, password, age, gender, phone)
values(#{username}, #{password},#{age}, #{gender},#{phone})
</insert>
</mapper>
测试代码:
@Slf4j
@SpringBootTest //启动Sring 容器
class UserInfoXMLMapperTest {
@Autowired
private UserInfoXMLMapper userInfoXMLMapper;
@Test
void insert() {
UserInfo userInfo= new UserInfo();
userInfo.setUsername("6666");
userInfo.setPassword("6666");
userInfo.setAge(19);
userInfo.setGender(1);
userInfo.setPhone("6666666");
Integer count = userInfoXMLMapper.insert(userInfo);
log.info("影响的行数:" +count+", 返回的主键:" +userInfo.getId());
}
}
运行结果:
MySQL:
2.2 删(delete)
2.2.1 传参 Integer
UserInfoXMLMapper接口:
@Mapper
public interface UserInfoXMLMapper {
//定义接口
Integer delete(Integer id);
}
UserInfoXMLMapper.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="org.example.mybatis.mapper.UserInfoXMLMapper">
//插入
<delete id = "delete" >
delete from user_info where id=#{id}
</delete>
</mapper>
测试代码:
@Slf4j
@SpringBootTest //启动Sring 容器
class UserInfoXMLMapperTest {
@Autowired
private UserInfoXMLMapper userInfoXMLMapper;
@Test
void delete() {
userInfoXMLMapper.delete(11);
}
}
运行结果:
MySQL:
2.2.2 传参 对象
UserInfoXMLMapper接口:
@Mapper
public interface UserInfoXMLMapper {
//定义接口
Integer delete(UserInfo userInfo);
}
UserInfoXMLMapper.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="org.example.mybatis.mapper.UserInfoXMLMapper">
//插入
<delete id = "delete" >
delete from user_info where id=#{id}
</delete>
</mapper>
测试代码:
@Slf4j
@SpringBootTest //启动Sring 容器
class UserInfoXMLMapperTest {
@Autowired
private UserInfoXMLMapper userInfoXMLMapper;
@Test
void delete() {
UserInfo userInfo= new UserInfo();
userInfo.setId(10);
userInfoXMLMapper.delete(userInfo);
}
}
运行结果:
MySQL:
2.3 改(update)
UserInfoXMLMapper接口:
@Mapper
public interface UserInfoXMLMapper {
Integer update(UserInfo uerInfo);
}
UserInfoXMLMapper.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="org.example.mybatis.mapper.UserInfoXMLMapper">
<update id ="update">
update user_info set username=#{username} where id =#{id}
</update>
</mapper>
测试代码:
@Slf4j
@SpringBootTest //启动Sring 容器
class UserInfoXMLMapperTest {
@Autowired
private UserInfoXMLMapper userInfoXMLMapper;
@Test
void update() {
UserInfo userInfo= new UserInfo();
userInfo.setUsername("9999999");
userInfo.setId(9);
userInfoXMLMapper.update(userInfo);
}
}
运行结果:
MySQL:
2.4 查(select)
UserInfoXMLMapper接口:
@Mapper
public interface UserInfoXMLMapper {
List<UserInfo> queryAllUser();
}
UserInfoXMLMapper.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="org.example.mybatis.mapper.UserInfoXMLMapper">
<select id ="queryAllUser" resultType="org.example.mybatis.model.UserInfo">
select id ,username, password, age, gender, phone,
delete_flag,create_time,update_time from user_info
</select>
</mapper>
运行结果:
上述是MySQL查询的结果,下面是映射到Java类的结果:
为什么 delete_flag,create_time,update_time 的值为空呢?
MyBatis 会根据方法的返回结果进⾏赋值.
方法用对象 UserInfo接收返回结果, MySQL 查询出来数据为⼀条, 就会自动赋值给对象.
方法用List接收返回结果, MySQL 查询出来数据为多条时, 也会⾃动赋值给List。
但是⽅法使⽤UserInfo接收,MySQL 查询返回的数据为多条时, MyBatis执⾏就会报错。
原因分析:
当对象中有多个属性时,自动映射查询结果时,MyBatis 会获取结果中返回的列名并在 Java 类中查找相同名字的属性。 这意味着如果发现了 id 列和 id 属性,MyBatis 会将列 id的值赋给 id 属性,如果发现了delete_flag列和 deleteFlag属性,MyBatis 不会将delete_flag列的值赋给 deleteFlag属性。
解决方法在下面的章节
3 列名和属性名匹配
3.1 起别名 as
在SQL语句中,给列名起别名,保持别名和实体类属性名⼀样(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="org.example.mybatis.mapper.UserInfoXMLMapper">
<select id ="queryAllUser" resultType="org.example.mybatis.model.UserInfo">
select id ,username, password, age, gender, phone,
delete_flag as deleteFlag,create_time as createTime,
update_time as updateTime from user_info
</select>
</mapper>
测试代码:
@Slf4j
@SpringBootTest //启动Sring 容器
class UserInfoXMLMapperTest {
@Test
void queryAllUser() {
List<UserInfo> u = userInfoXMLMapper.queryAllUser();
for(Object it : u){
log.info(it.toString());
}
}
运行结果:
3.2 结果映射<resultMap>
需要使用<resultMap>标签进行映射(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="org.example.mybatis.mapper.UserInfoXMLMapper">
<resultMap id="baseMap" type="org.example.mybatis.model.UserInfo">
<id column="id" property="id"></id>
<result column="delete_flag" property="deleteFlag"></result>
<result column="create_time" property="createTime"></result>
<result column="update_time" property="updateTime"></result>
</resultMap>
<select id ="queryAllUser" resultType="org.example.mybatis.model.UserInfo" resultMap="baseMap">
select id ,username, password, age, gender, phone,
delete_flag, create_time ,
update_time from user_info
</select>
</mapper>
一个resultMap
可以被重复使用,resultMap
的id可以被很多的标签使用。
3.3 配置文件中开启驼峰命令
在配置文件Application.yml
文件中添加如下的配置:
mybatis:
configuration:
map-underscore-to-camel-case: true #配置驼峰⾃动转换
在配置文件Application.properties
文件中添加如下的配置:
mybatis.configuration.map-underscore-to-camel-case: true #配置驼峰⾃动转换
Mapper接口:
@Mapper
public interface UserInfoXMLMapper {
List<UserInfo> queryAllUser();
}
XML文件对SQL语句的实现:
<?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="org.example.mybatis.mapper.UserInfoXMLMapper">
<resultMap id="baseMap" type="org.example.mybatis.model.UserInfo">
<id column="id" property="id"></id>
<result column="delete_flag" property="deleteFlag"></result>
<result column="create_time" property="createTime"></result>
<result column="update_time" property="updateTime"></result>
</resultMap>
<select id ="queryAllUser" resultType="org.example.mybatis.model.UserInfo" resultMap="baseMap">
select id ,username, password, age, gender, phone,
delete_flag, create_time ,
update_time from user_info
</select>
</mapper>
测试代码:
@Slf4j
@SpringBootTest //启动Sring 容器
class UserInfoXMLMapperTest {
@Test
void queryAllUser() {
List<UserInfo> u = userInfoXMLMapper.queryAllUser();
for(Object it : u){
log.info(it.toString());
}
}
}
运行结果:
数据库的字段名 => 类的属性名
驼峰命名规则: abc_xyz => abcXyz
表中字段名:abc_xyz
类中属性名:abcXyz