文章目录
1.多对一查询(关联关系)
1.1sql数据
CREATE DATABASE `mybatis`;
USE `mybatis`;
CREATE TABLE `user`(
`id` INT(20) NOT NULL PRIMARY KEY,
`name` VARCHAR(30) DEFAULT NULL,
`pwd` VARCHAR(30) DEFAULT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO `user`(`id`,`name`,`pwd`) VALUES
(1,'王麻子','123456'),
(2,'张三','123456'),
(3,'李四','123890');
CREATE TABLE `teacher` (
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO teacher(`id`, `name`) VALUES (1, '张老师');
CREATE TABLE `student` (
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
`tid` INT(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fktid` (`tid`),
CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', '小明', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', '小红', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', '小张', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', '小李', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', '小王', '1');
SELECT s.id sid,s.name sname,t.name tname
FROM student s,teacher t
WHERE s.tid=t.id
1.2新建student、teacher类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
private int id;
private String name;
private Teacher teacher;
/** 属性名 列名
* id id
* name name
* teacher.id tid
*/
}
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
private int id;
private String name;
}
1.3接口、mapper映射文件
接口
public interface StudentMapper {
//查询所有学生信息
List<Student> getStudents();
}
mapper映射文件(两种方式)
<?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.li.mapper.StudentMapper">
<!-- 按照结果嵌套处理-->
<resultMap id="StudentTeacher" type="Student">
<id property="id" column="sid"/>
<result property="name" column="sname"/>
<association property="teacher" javaType="Teacher">
<result property="name" column="tname"/>
<result property="id" column="tid"/>
</association>
</resultMap>
<select id="getStudents" resultMap="StudentTeacher">
select s.id sid,s.name sname,t.name tname,t.id tid
from student s,teacher t
where s.tid=t.id
</select>
<!-- 按照查询嵌套处理-->
<select id="getStudents2" resultMap="Student2Teacher">
select * from student;
</select>
<resultMap id="Student2Teacher" type="Student">
<result property="id" column="id"/>
<result property="name" column="name"/>
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="Teacher">
select * FROM teacher where id = #{id}
</select>
</mapper>
1.4 测试类
public class StudentMapperTest {
@Test
public void testGetStudents() {
StudentMapper mapper = MybatisUtils.getSqlSession().getMapper(StudentMapper.class);
List<Student> students = mapper.getStudents();
for (Student student : students) {
System.out.println(student);
}
}
}
查询结果
SQLyog中多对一查询结果显而易见
2.一对多查询(包含关系)
一对多查询student、teacher实体类有变化
2.1teacher类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
private int id;
private String name;
private int tid;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
private int id;
private String name;
private List<Student> students;
//一对多 老师有很多学生包含关系
}
2.2接口、mapper映射文件
接口
public interface TeacherMapper {
Teacher getTeacher(int id);
}
mapper映射文件
<?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.li.mapper.TeacherMapper">
<!--按照结果嵌套处理-->
<resultMap id="TeacherStudent" type="Teacher">
<id property="id" column="tid"/>
<result property="name" column="tname"/>
<collection property="students" ofType="Student">
<id property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
<select id="getTeacher" resultMap="TeacherStudent">
select s.id sid,s.name sname,t.id tid,t.name tname
from student s,teacher t
where s.tid=t.id and t.id=#{id}
</select>
<!--按照查询嵌套处理-->
<select id="getTeacher2" resultMap="TeacherStudent2">
select * from mybatis.teacher where id = #{id}
</select>
<resultMap id="TeacherStudent2" type="Teacher">
<result property="id" column="id"/>
<result property="name" column="name"/>
<collection property="students" javaType="ArrayList" ofType="Student" select="getStudent" column="id"/>
</resultMap>
<select id="getStudent" resultType="Student">
select * from mybatis.student where tid = #{tid}
</select>
</mapper>
2.3测试类
public class TeacherMapperTest {
@Test
public void testGetTeacher(){
TeacherMapper mapper = MybatisUtil.getSession().getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher(2);
System.out.println(teacher);
}
}
查询结果
3.动态SQL
根据不同参数,执行不同sql
准备数据
3.1sql数据
CREATE TABLE `blog` (
`id` VARCHAR(50) NOT NULL COMMENT '博客id',
`title` VARCHAR(100) NOT NULL COMMENT '博客标题',
`author` VARCHAR(30) NOT NULL COMMENT '博客作者',
`create_time` DATETIME NOT NULL COMMENT '创建时间',
`views` INT(30) NOT NULL COMMENT '浏览量'
) ENGINE=INNODB DEFAULT CHARSET=utf8
3.2新建Blog类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Blog {
private String id;
private String title;
private String author;
//createDate create_time
/***
* 是否开启驼峰命名自动映射,即从经典数据库列名 A_COLUMN 映射到经典 Java 属性名 aColumn。
*/
private Date createDate;
private int views;
}
驼峰命名(mybatis-config.xml)
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
3.3接口、mapper映射文件
接口
public interface BlogMapper {
//新增博客
int addBlog(Blog blog);
//根据博客名、作者名查询
//如果博客名为null、根据作者名查询
List<Blog> getBlogByIf(Map<String,String> map);
}
mapper映射文件
<?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.li.mapper.BlogMapper">
<insert id="addBlog" parameterType="Blog">
insert into mybatis.blog(id, title, author, create_time, views)
values (#{id}, #{title}, #{author}, #{createDate}, #{views})
</insert>
<!-- 如果没有传入参数,打印结果里面有where 为了使idea智能判断如果没有参数则不显示where 如果有参数则显示结果 用where标签 -->
<select id="getBlogByIf" resultType="Blog" parameterType="map">
select * from mybatis.blog
<where>
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</where>
</select>
</mapper>
3.4测试类
public class BlogMapperTest {
//插入一些基本数据,便于查询
@Test
public void testAddBlog(){
BlogMapper mapper = MybatisUtil.getSession(true).getMapper(BlogMapper.class);
Blog blog = new Blog();
blog.setId(IDUtil.getId());
blog.setTitle("HTML入门");
blog.setAuthor("老司机说");
blog.setCreateDate(new Date());
blog.setViews(666);
mapper.addBlog(blog);
Blog blog2 = new Blog();
blog2.setId(IDUtil.getId());
blog2.setTitle("Python入门");
blog2.setAuthor("老司机说");
blog2.setCreateDate(new Date());
blog2.setViews(666);
mapper.addBlog(blog2);
Blog blog3 = new Blog();
blog3.setId(IDUtil.getId());
blog3.setTitle("Linux入门");
blog3.setAuthor("老司机说");
blog3.setCreateDate(new Date());
blog3.setViews(666);
mapper.addBlog(blog3);
Blog blog4 = new Blog();
blog4.setId(IDUtil.getId());
blog4.setTitle("C语言入门");
blog4.setAuthor("老司机说");
blog4.setCreateDate(new Date());
blog4.setViews(666);
mapper.addBlog(blog4);
}
@Test
public void testGetBlogByIf(){
BlogMapper mapper = MybatisUtil.getSession().getMapper(BlogMapper.class);
Map<String, String> map = new HashMap<String, String>();
map.put("author","老司机说");
mapper.getBlogByIf(map);
}
}
结果
if
接口
public interface BlogMapper {
//根据博客名、作者名查询
//如果博客名为null、根据作者名查询 不知道用户传入的参数类型用map
List<Blog> getBlogByIf(Map map);
}
编写mapper
<select id="getBlogByIf" resultType="Blog" parameterType="map">
select * from mybatis.blog where
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</select>
测试
public class BlogMapperTest {
@Test
public void testGetBlogByIf(){
BlogMapper mapper = MybatisUtil.getSession().getMapper(BlogMapper.class);
Map<String, String> map = new HashMap<String, String>();
map.put("author","老司机说");
mapper.getBlogByIf(map);
}
结果可以发现sql语句是
select * from mybatis.blog WHERE title = ?
如果传入的是两个参数呢?
@Test
public void testGetBlogByIf(){
BlogMapper mapper = MybatisUtil.getSession().getMapper(BlogMapper.class);
Map<String, String> map = new HashMap<String, String>();
map.put("title","CSS入门");
map.put("author","小爱说");
mapper.getBlogByIf(map);
}
结果可以发现两个参数sql语句的变化
select * from mybatis.blog WHERE title = ? and author = ?
这也印证不同参数执行不同sql语句
如果用户不传入参数呢?
如何解决用户不传入参数问题,sql语句还是正确的
使用
<select id="getBlogByIf" resultType="Blog" parameterType="map">
select * from mybatis.blog
<where>
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</where>
</select>
where set
接口
public interface BlogMapper {
//更新用户信息
int updateBlog(Map map);
}
编写mapper
<update id="updateBlog" parameterType="map">
update mybatis.blog
<set>
<if test="title != null">
title = #{title},
</if>
<if test="author">
author = #{author}
</if>
</set>
where id=#{id}
</update>
set 元素等价的自定义 trim 元素
<update id="updateBlog" parameterType="map">
update mybatis.blog
<trim prefix="SET" suffixOverrides=",">
<if test="title != null">
title = #{title},
</if>
<if test="author">
author = #{author}
</if>
where id=#{id}
</trim>
</update>
测试
@Test
public void testUpdateBlog(){
BlogMapper mapper = MybatisUtil.getSession(true).getMapper(BlogMapper.class);
Map<String, String> map = new HashMap<String, String>();
map.put("id","9729a6e7129c4f428214039c2d162b56");
map.put("title","pS入门");
mapper.updateBlog(map);
}
结果更新成功
choose、when、otherwise
接口
public interface BlogMapper {
//查询博客满足一个条件即可
List<Blog> queryBlogByChoosn(Map map);
}
实现mapper
<select id="queryBlogByChoosn" resultType="Blog" parameterType="map">
select * from mybatis.blog
<where>
<choose>
<when test="title != null">
title = #{title}
</when>
<when test="author != null">
and author = #{author}
</when>
<otherwise>
and views = #{views}
</otherwise>
</choose>
</where>
</select>
测试
@Test
public void testQuqeryBlogByChoosn(){
BlogMapper mapper = MybatisUtil.getSession(true).getMapper(BlogMapper.class);
HashMap<String, String> map = new HashMap<String, String>();
map.put("author","老司机说");
map.put("title","Python入门");
mapper.queryBlogByChoosn(map);
}
map.put(“title”,“Python入门”);运行结果
map.put(“author”,“老司机说”);运行结果
foreach
接口
public interface BlogMapper {
List<Blog> queryBlogByForeach(Map map);
}
实现mapper
<!--
collection 集和的对象 输入的参数map
item 遍历出来的每一项
-->
<select id="queryBlogByForeach" resultType="Blog" parameterType="map">
select * from mybatis.blog
<where>
<foreach collection="ids" item="id" open="and (" close=")" separator="or">
id = #{id}
</foreach>
</where>
</select>
测试
@Test
public void testQuqeryBlogByForeach() {
BlogMapper mapper = MybatisUtil.getSession(true).getMapper(BlogMapper.class);
HashMap<String, ArrayList> map = new HashMap();
ArrayList<String> ids = new ArrayList<String>();
ids.add("fc65d14e2630430c8a63470935dcea42");
ids.add("9d099406272a4dafa522cdc98898cb29");
ids.add("9e37d5fc299b4cee8400f2d5c3b4d3ec");
map.put("ids",ids);
mapper.queryBlogByForeach(map);
}
结果