在mybatis框架下写代码的步骤:
在一对多查询和多对一查询之前,先准备数据库
以老师和学生为例
老师的数据库
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');
老师和学生的接口
老师接口:
package com.kuang.mapper;
import com.kuang.pojo.Teacher;
import java.util.List;
/**
* #Author:槐序二四
* #Time:2020/2/21 14:04
* #Motto:人间荒唐市侩 不如山中作怪🌙.
*/
public interface TeacherMapper {
//获取这个老师下的所有的学生
public Teacher getTeacher(int id);
}
学生接口:
package com.kuang.mapper;
import com.kuang.pojo.Student;
import java.util.List;
/**
* #Author:槐序二四
* #Time:2020/2/21 14:04
* #Motto:人间荒唐市侩 不如山中作怪🌙.
*/
public interface StudentMapper {
//获取所有的学生及学生对应老师的信息
List<Student> getStudents();
}
一、多对一查寻
从刚创建的数据库我们可以知道,很多个学生只有一个老师
从很多个学生这个角度出发,去查询他们的老师,就是多对一查询
老师类:
package com.kuang.pojo;
import lombok.Data;
import java.util.List;
/**
* #Author:槐序二四
* #Time:2020/2/21 14:05
* #Motto:人间荒唐市侩 不如山中作怪🌙.
*/
@Data
public class Teacher {
private int id;
private String name;
}
学生类:
package com.kuang.pojo;
import lombok.Data;
/**
* #Author:槐序二四
* #Time:2020/2/21 14:05
* #Motto:人间荒唐市侩 不如山中作怪🌙.
*/
@Data
public class Student {
private int id;
private String name;
private Teacher teacher;
}
学生的xml文件(我们以学生的角度出发查询,不用写老师的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.kuang.mapper.StudentMapper">
<resultMap id="StudentTeacher" type="Student">
<id property="id" column="sid"/>
<result property="name" column="sname"/>
<association property="teacher" javaType="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
</association>
</resultMap>
<select id="getStudents" resultMap="StudentTeacher">
select s.id sid,s.name sname,t.id tid,t.name tname
from student s,teacher t
where s.tid = t.id
</select>
</mapper>
这是sql的查询语句
我们做了一个映射
测试类:
package com.kuang.mapper;
import com.kuang.pojo.Student;
import com.kuang.utils.MyBatisUtils;
import org.junit.Test;
import java.util.List;
/**
* #Author:槐序二四
* #Time:2020/2/21 14:25
* #Motto:人间荒唐市侩 不如山中作怪🌙.
*/
public class StudentMapperTest {
@Test
public void testGetStudent(){
StudentMapper mapper = MyBatisUtils.getSession().getMapper(StudentMapper.class);
List<Student> students = mapper.getStudents();
for (Student student : students) {
System.out.println(student);
}
}
}
二、一对多查询
通过数据库,我们可以知道,一个老师拥有很多个学生,将这个老师下的所有学生查出来,这就是一对多查询
老师类:
package com.kuang.pojo;
import lombok.Data;
import java.util.List;
/**
* #Author:槐序二四
* #Time:2020/2/21 14:05
* #Motto:人间荒唐市侩 不如山中作怪🌙.
*/
@Data
public class Teacher {
private int id;
private String name;
private List<Student> students;
}
学生类:
package com.kuang.pojo;
import lombok.Data;
/**
* #Author:槐序二四
* #Time:2020/2/21 14:05
* #Motto:人间荒唐市侩 不如山中作怪🌙.
*/
@Data
public class Student {
private int id;
private String name;
private int tid;
}
老师的xml文件(我们以老师的角度出发查询,不用写学生的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">
<!-- 原来我们都是编写具体的执行sql -->
<mapper namespace="com.kuang.mapper.TeacherMapper">
<resultMap id="TeacherStudent" type="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
<collection property="students" ofType="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
<select id="getTeacher" resultMap="TeacherStudent">
select s.id id,s.name sname,t.id tid,t.name tname
from student s,teacher t
where s.tid = t.id and t.id=#{id}
</select>
</mapper>
这是我们的sql语句
我们做了一个映射
测试类:
package com.kuang.mapper;
import com.kuang.pojo.Teacher;
import com.kuang.utils.MyBatisUtils;
import org.junit.Test;
/**
* #Author:槐序二四
* #Time:2020/2/21 18:17
* #Motto:人间荒唐市侩 不如山中作怪🌙.
*/
public class TeacherMapperTest {
@Test
public void testGetTeacher(){
TeacherMapper mapper = MyBatisUtils.getSession().getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher(1);//获取1号老师有多少个学生
System.out.println(teacher);
}
}
三、动态SQL
首先创建需要的数据库
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
按照文章开头的步骤
接口:
package com.kuang.mapper;
import com.kuang.pojo.Blog;
import java.util.List;
import java.util.Map;
/**
* #Author:槐序二四
* #Time:2020/2/21 18:34
* #Motto:人间荒唐市侩 不如山中作怪🌙.
*/
public interface BlogMapper {
int addBlog(Blog blog);
//通过作者名和博客名来查询博客
//如果作者名为null 那么则根据博客名字来查询
List<Blog> getBlogByIf(Map map);
int updateBlog(Map map);
//查询博客,只要有一个条件满足即可
List<Blog> queryBlogByChoose(Map map);
//查询多个博客
List<Blog> queryBlogByForeach(Map map);
}
实体类:
package com.kuang.pojo;
import lombok.Data;
import java.util.Date;
/**
* #Author:槐序二四
* #Time:2020/2/21 18:32
* #Motto:人间荒唐市侩 不如山中作怪🌙.
*/
@Data
public class Blog {
private String id;
private String title;
private String author;
private Date createDate; //create_time 不对应
private int views;
}
if标签:
如果我们需要拼接where条件,又不希望客户端传来的错误信息,需要更加智能的where
如果有后面的where语句,就自动添加
如果后面开头的语句是and或者or它会自动去掉
<?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.kuang.mapper.BlogMapper">
<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>
测试类:
package com.kuang.mapper;
import com.kuang.pojo.Blog;
import com.kuang.utils.IDUtils;
import com.kuang.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.*;
/**
* #Author:槐序二四
* #Time:2020/2/21 18:48
* #Motto:人间荒唐市侩 不如山中作怪🌙.
*/
public class BlogMapperTest {
@Test
public void testGetBlogByIf(){
SqlSession session = MyBatisUtils.getSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
Map<String,String> map = new HashMap<String, String>();
/*map.put("title","Mybatis So easy");*/
map.put("author","AA");
List<Blog> blogByIf = mapper.getBlogByIf(map);
for (Blog blog : blogByIf) {
System.out.println(blog);
}
}
set标签
如果满足set标签,会自动拼接set标签
也就是说:如果后面有“,” 它会自己去掉
<?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">
<!-- 原来我们都是编写具体的执行sql -->
<mapper namespace="com.kuang.mapper.BlogMapper">
<update id="updateBlog" parameterType="Blog">
update mybatis.blog
<set>
<if test="title!=null">
title = #{title},
</if>
<if test="author!=null">
author = #{author}
</if>
</set>
where id = #{id}
</update>
</mapper>
测试类:
package com.kuang.mapper;
import com.kuang.pojo.Blog;
import com.kuang.utils.IDUtils;
import com.kuang.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.*;
/**
* #Author:槐序二四
* #Time:2020/2/21 18:48
* #Motto:人间荒唐市侩 不如山中作怪🌙.
*/
public class BlogMapperTest {
@Test
public void testSet(){
SqlSession session = MyBatisUtils.getSession(true);
BlogMapper mapper = session.getMapper(BlogMapper.class);
HashMap<String, String> map = new HashMap<String, String>();
map.put("id","ffa9e0f48d30440bb4caf43f9fca66d8");
map.put("title","老大");
mapper.updateBlog(map);
}
}
choose标签
choose标签好比Java中的switch
<?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">
<!-- 原来我们都是编写具体的执行sql -->
<mapper namespace="com.kuang.mapper.BlogMapper">
<!--
choose标签好比Java中的switch
-->
<select id="queryBlogByChoose" parameterType="map" resultType="Blog">
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>
</mapper>
测试类:
package com.kuang.mapper;
import com.kuang.pojo.Blog;
import com.kuang.utils.IDUtils;
import com.kuang.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.*;
/**
* #Author:槐序二四
* #Time:2020/2/21 18:48
* #Motto:人间荒唐市侩 不如山中作怪🌙.
*/
public class BlogMapperTest {
@Test
public void testChoose(){
SqlSession session = MyBatisUtils.getSession(true);
BlogMapper mapper = session.getMapper(BlogMapper.class);
Map<String, String> map = new HashMap<String, String>();
map.put("title","Mybatis So easy");
map.put("author","AA");
mapper.queryBlogByChoose(map);
}
}
foreach标签
collection:输入的参数
id:遍历出来的每一项
通过id遍历出来的参数 可以在foreach中使用
<?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">
<!-- 原来我们都是编写具体的执行sql -->
<mapper namespace="com.kuang.mapper.BlogMapper">
<select id="queryBlogByForeach" parameterType="map" resultType="Blog">
select * from mybatis.blog
<where>
<foreach collection="ids" item="id" open="and(" close=")" separator="or">
id = #{id}
</foreach>
</where>
</select>
</mapper>
测试类:
package com.kuang.mapper;
import com.kuang.pojo.Blog;
import com.kuang.utils.IDUtils;
import com.kuang.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.*;
/**
* #Author:槐序二四
* #Time:2020/2/21 18:48
* #Motto:人间荒唐市侩 不如山中作怪🌙.
*/
public class BlogMapperTest {
@Test
public void testForeach(){
SqlSession session = MyBatisUtils.getSession(true);
BlogMapper mapper = session.getMapper(BlogMapper.class);
Map map = new HashMap();
ArrayList<String> ids = new ArrayList<String>();
ids.add("ffa9e0f48d30440bb4caf43f9fca66d8");
ids.add("b643a415355741e69ec37a1c0e259124");
ids.add("7f2ae9e7fbc24d5c86be8c141324bfb1");
map.put("ids",ids);
mapper.queryBlogByForeach(map);
}
}