环境说明
- JDK 17
- MySQL 8.0.32
- Mybatis 3.5.10
环境准备
导入相关依赖:
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.32</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.10</version>
</dependency>
增删改查
查询涉及到的最重要的标签就是 resultMap 结果映射,resultMap 子标签及作用:
-
constructor——用于在实例化类时,注入结果到构造方法中
idArg
—— ID 参数;标记出作为 ID 的结果可以帮助提高整体性能arg
—— 将被注入到构造方法的一个普通结果
-
id —— 一个 ID 结果;标记出作为 ID 的结果可以帮助提高整体性能
-
result —— 注入到字段或 JavaBean 属性的普通结果
-
association—— 一个复杂类型的关联;许多结果将包装成这种类型
- 嵌套结果映射 – 关联可以是
resultMap
元素,或是对其它结果映射的引用
- 嵌套结果映射 – 关联可以是
-
collection—— 一个复杂类型的集合
- 嵌套结果映射 – 集合可以是
resultMap
元素,或是对其它结果映射的引用
- 嵌套结果映射 – 集合可以是
-
discriminator—— 使用结果值来决定使用哪个resultMap
-
case—— 基于某些值的结果映射
- 嵌套结果映射 –
case
也是一个结果映射,因此具有相同的结构和元素;或者引用其它的结果映射
- 嵌套结果映射 –
-
简单查询
@Data
@AllArgsConstructor
public class Student{
int sid;
String name;
String sex;
}
<mapper namespace="StudentMapper">
<resultMap id="studentMap" type="com.test.entity.Student">
<!--<id property="sid" column="id"/>-->
<!--<result property="name" column="name"/>-->
<!--<result property="sex" column="sex"/>-->
<constructor>
<idArg javaType="_int" column="id"/>
<arg javaType="String" column="name"/>
<arg javaType="String" column="sex"/>
<!--注意 javaType "_int"对应于int类型,"int"对应于Integer类型-->
</constructor>
</resultMap>
<select id="selectStudent" resultMap="studentMap">
select * from student
</select>
</mapper>
新增数据
<insert id="insertStudent" parameterType="com.test.entity.Student">
insert into student(name, sex) values(#{name}, #{sex})
</insert>
Student student = new Student();
student.setName("Blue");
student.setSex("男");
sqlSession.insert("insertStudent", student);
修改数据
<update id="updateStudent" parameterType="com.test.entity.Student">
update student set name = 'Atlantic' where id = #{sid}
</update>
Student student = new Student();
student.setSid(4);
sqlSession.update("updateStudent", student);
sqlSession.update("updateStudent", 6);
删除数据
<update id="deleteStudent" parameterType="com.test.entity.Student">
delete from student where id = #{sid}
</update>
Student student = new Student();
student.setSid(5);
sqlSession.update("deleteStudent", student);
sqlSession.update("deleteStudent", 4);
复杂查询
collection 标签一对多查询(每查询到一个人就找到其对应的多个角色)
@Data
public class User {
private Integer id;
private String name;
private List<Role> roles;
}
@Data
public class Role {
private Integer id;
private Integer userId;
private String name;
private String type;
}
<mapper namespace="UserMapper">
<!-- 定义resultMap -->
<resultMap id="UserResultMap" type="com.test.entity.User">
<result column="id" property="id"/>
<result column="name" property="name"/>
<collection property="roles" ofType="com.test.entity.Role">
<result column="role_id" property="id"/>
<result column="role_uid" property="userId"/>
<result column="role_name" property="name"/>
<result column="role_type" property="type"/>
</collection>
</resultMap>
<!--查询语句-->
<select id="selectUserById" resultMap="UserResultMap">
select u.id,u.name, r.id AS role_id ,r.user_id AS role_uid, r.name AS role_name ,r.type AS role_type
FROM user AS u
INNER JOIN role AS r
ON u.id = r.user_id
where u.id = #{id}
</select>
</mapper>
create table `user` (
`id` int(11) not null auto_increment,
`name` varchar(255) default null comment '名称',
primary key (`id`)
) engine=innodb auto_increment=0 default charset=utf8mb4;
create table `role` (
`id` int(11) not null auto_increment,
`user_id` int(11) not null comment '用户id',
`name` varchar(255) default null comment '角色名称',
`type` varchar(255) default null comment '角色类型',
primary key (`id`)
) engine=innodb auto_increment=0 default charset=utf8mb4;
参考:Mybatis | Mybatis标签collection一对多的使用 - 掘金 (juejin.cn)
association 标签查询一对多情况中的一对一(每查询到一个角色都找到其对应的人)
@Data
public class Role {
private Integer id;
private Integer userId;
private String name;
private String type;
private User user;
}
@Data
public class User {
private Integer id;
private String name;
}
<mapper namespace="UserMapper">
<resultMap id="test" type="com.test.entity.Role">
<id column="id" property="id"/>
<result column="user_id" property="userId"/>
<result column="name" property="name"/>
<result column="type" property="type"/>
<association property="user" javaType="com.test.entity.User">
<id column="uid" property="id"/>
<result column="uname" property="name"/>
</association>
</resultMap>
<select id="selectRole" resultMap="test">
select *, user.id as uid, user.name as uname
from role
left join user
on role.user_id = user.id
-- where user.id = #{id} 查询指定 id 的user的多个角色
</select>
</mapper>
使用注解开发
简单查询
public interface StudentMapper {
@Result(column = "id", property = "sid")
@Select("select * from student where id = #{sid}")
Student selectStudentById(int sid);
@ConstructorArgs({
@Arg(column = "id", javaType = int.class),
@Arg(column = "sex", javaType = String.class)
})
@Select("select * from student where id=#{sid} and sex=#{sex}")
Student selectStudentByIdAndSex(@Param("sid") int sid, @Param("sex") String sex);
@Result(column = "id", property = "sid")
@Select("select * from student")
List<Student> selectStudentList();
try(SqlSession sqlSession = SqlSessionUtil.openSession(false)){
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
System.out.println(mapper.selectStudentById(3));
// 构造方法映射查询
System.out.println(mapper.selectStudentByIdAndSex(1, "女"));
List<Student> list = mapper.selectStudentList();
新增数据
@Insert("insert into student(name,sex) values(#{name}, #{sex})")
void addStudent(Student student);
Student student = new Student();
student.setName("Ajax");
student.setSex("男");
mapper.addStudent(student);
修改数据
@Update("update student set name=#{name}, sex=#{sex} where id=#{sid}")
void updateStudentById(@Param("name") String name,
@Param("sex") String sex,
@Param("sid") int sid);
mapper.updateStudentById("Asky", "男", 6);
删除数据
@Delete("delete from student where id=#{sid}")
void deleteStudentById(@Param("sid") int sid);
mapper.deleteStudentById(10);
复杂查询
collection 标签一对多查询(每查询到一个人就找到其对应的多个角色)
public interface UserMapper {
@Results({
@Result(column = "id", property = "id"),
@Result(column = "name", property = "name"),
@Result(column = "id", property = "roles", many =
@Many(select = "getRoleByUid"))
})
@Select("select * from user where id=#{id}")
User selectUserById(int id);
@Result(column = "user_id", property = "userId")
@Select("select * from role where user_id = #{uid}")
Role getRoleByUid(int uid);
// UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
// User user = userMapper.selectUserById(2);
// System.out.println(user);
association 标签查询一对多情况中的一对一(每查询到一个角色都找到其对应的人)
public interface RoleMapper {
@Results({
@Result(column = "id", property = "id"),
@Result(column = "user_id", property = "userId"),
@Result(column = "name", property = "name"),
@Result(column = "type", property = "type"),
@Result(column = "user_id", property = "user", one =
@One(select = "getUserById"))
})
@Select("select * from role")
List<Role> getRoleList();
@Select("select * from user where id = #{uid}")
User getUserById(int uid);
// RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);
// List<Role> roleList = roleMapper.getRoleList();
// roleList.forEach(System.out::println);