一、mybatis关联查询
关联查中主要需要理解的知识点是:结果集映射
1、多对一关联查询
接java 知识点 16:https://blog.csdn.net/a__int__/article/details/108152401
1.1、先把环境跑起来
1.1.1、新建表teacher、添加外键
# 使用库
USE mybatis;
# 建表
CREATE TABLE `teacher` (
`id` int(11) NOT NULL,
`name` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 查看表
describe teacher;
# 查看表创建语句
show create table teacher;
# 插入数据
INSERT INTO teacher (id,name) VALUES (1, '李老师');
添加一个外键关联
# 给user表在pwd后面添加一个字段tid
ALTER TABLE user ADD COLUMN tid int DEFAULT NULL AFTER pwd;
# 将tid外键关联到teacher的id上
alter table user add constraint FK_ID foreign key(tid) REFERENCES teacher(id);
# 查看user创建结构
show create table user;
# 将tid的值设为1
UPDATE user SET tid = 1 WHERE id = 1;
查看user创建结构
1.1.2、新建实体类teacher、user
(idea)复制一排:ctrl+d
(linux)快速打开终端:ctrl+alt+t
Teacher.java
package com.haha.pojo;
public class Teacher {
private int id;
private String name;
@Override
public String toString() {
return "Teacher{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
user.java
package com.haha.pojo;
import lombok.Data;
public class User {
private int id;
private String name;
private String pwd;
private Teacher teacher;
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", pwd='" + pwd + '\'' +
", teacher=" + teacher +
'}';
}
public Teacher getTeacher() {
return teacher;
}
public void setTeacher(Teacher teacher) {
this.teacher = teacher;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPed() {
return pwd;
}
public void setPed(String ped) {
this.pwd = ped;
}
}package com.haha.pojo;
import lombok.Data;
public class User {
private int id;
private String name;
private String pwd;
private Teacher teacher;
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", pwd='" + pwd + '\'' +
", teacher=" + teacher +
'}';
}
public Teacher getTeacher() {
return teacher;
}
public void setTeacher(Teacher teacher) {
this.teacher = teacher;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPed() {
return pwd;
}
public void setPed(String ped) {
this.pwd = ped;
}
}
1.1.3、TeacherMapper.java、UserMapper.java
TeacherMapper.java
package com.haha.dao;
import com.haha.pojo.Teacher;
import java.util.List;
public interface TeacherMapper {
List<Teacher> getTeachers();
}
UserMapper.java
package com.haha.dao;
import com.haha.pojo.User;
import java.util.List;
public interface UserMapper {
List<User> getUsers();
}
1.1.4、TeacherMapper.xml、UserMapper.xml
这次我们建在resources文件夹下
新建TeacherMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.haha.dao.TeacherMapper">
<select id="getTeachers" resultType="com.haha.pojo.Teacher">
select * from teacher;
</select>
</mapper>
新建UserMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.haha.dao.UserMapper">
</mapper>
1.1.5、注册
mybatis-config.xml
注册TeacherMapper.xml和UserMapper.xml
1.1.6、测试运行
新建TeacherMapperTest.java
import com.haha.dao.TeacherMapper;
import com.haha.pojo.Teacher;
import com.haha.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class TeacherMapperTest {
@Test
public void test(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
List<Teacher> Teachers = mapper.getTeachers();
for (Teacher Teacher : Teachers) {
System.out.println(Teacher);
}
sqlSession.close();
}
}
运行结果
1.2、多对一关联查询
1.2.1、按照查询嵌套处理
写sql语句,UserMapper.xml:
<select id="getUsers" resultMap="UT">
select * from user;
</select>
<resultMap id="UT" type="com.haha.pojo.User">
<result property="id" column="id" />
<result property="name" column="name" />
<association property="teacher" column="tid" javaType="com.haha.pojo.Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="com.haha.pojo.Teacher">
select * from teacher where id = #{id};
</select>
写一个测试类UserMapperTest.java
import com.haha.dao.UserMapper;
import com.haha.pojo.User;
import com.haha.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class UserMapperTest {
@Test
public void test(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> users = mapper.getUsers();
for (User user : users) {
System.out.println(user);
}
sqlSession.close();
}
}
测试结果
1.2.2、按照结果嵌套处理
在UserMapper.java里面写一个getUsers2()
更改UserMapper.xml:
<select id="getUsers2" resultMap="UT">
select u.id uid , u.name uname,t.name tname
from user u,teacher t
where u.tid = t.id;
</select>
<resultMap id="UT" type="com.haha.pojo.User">
<result property="id" column="uid" />
<result property="name" column="uname" />
<association property="teacher" javaType="com.haha.pojo.Teacher" >
<result property="name" column="tname"/>
</association>
</resultMap>
新建测试方法test2
@Test
public void test2(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> users = mapper.getUsers2();
for (User user : users) {
System.out.println(user);
}
sqlSession.close();
}
运行test2结果
2、一对多关联查询
2.1、按照结果嵌套处理
一个老师对应多个学生
实体类Teacher新增users属性,并为其添加get、set、tostring方法
user
TeacherMapper接口添加方法
TeacherMapper.xml
<select id="getTeacher" resultMap="TU">
select u.id uid , u.name uname,t.name tname,t.id tid
from user u,teacher t
where u.tid = t.id
and t.id = #{tid}
</select>
<resultMap id="TU" type="com.haha.pojo.Teacher">
<result property="id" column="tid" />
<result property="name" column="tname" />
<!--集合用collection-->
<collection property="users" ofType="com.haha.pojo.User" >
<result property="name" column="uname"/>
<result property="id" column="uid"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
测试
@Test
public void test(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher(1);
System.out.println(teacher);
sqlSession.close();
}
运行结果
2.2、按照查询嵌套处理
TeacherMapper.java(新添加方法)
Teacher getTeacher2(@Param("tid") int id);
TeacherMapper.xml
<!--=====================按照查询嵌套处理========================-->
<select id="getTeacher2" resultMap="TU2">
select * from teacher where id = #{tid};
</select>
<resultMap id="TU2" type="com.haha.pojo.Teacher">
<collection property="users" javaType="ArrayList" ofType="com.haha.pojo.User" select="TU2ById" column="id"/>
</resultMap>
<select id="TU2ById" resultType="com.haha.pojo.User">
select * from user where tid = #{tid}
</select>
测试类新添加方法
@Test
public void test2(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher2(1);
System.out.println(teacher);
sqlSession.close();
}
运行结果
二、mybatis动态sql
1、解决字段名和属性名不一致问题
在mybatis-config.xml里面开启驼峰命名转换
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
2、解决mapper.xml里面每次都需要写类包名的问题
在mybatis-config.xml里面开启别名处理器
<typeAliases>
<package name="com.haha.pojo"/>
</typeAliases>
3、动态sql
3.1、先把环境跑起来
先新建一个blog表
create table blog
(
id varchar(50) not null,
title varchar(100) not null,
author varchar(30) not null,
create_time datetime not null,
views int(30) not null,
constraint blog_pk
primary key (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
新建实体类Blog.java (com.haha.pojo包下)
package com.haha.pojo;
import java.util.Date;
public class Blog {
private String id;
private String title;
private String author;
private Date createTime;
private int views;
@Override
public String toString() {
return "Blog{" +
"id='" + id + '\'' +
", title='" + title + '\'' +
", author='" + author + '\'' +
", createTime=" + createTime +
", views=" + views +
'}';
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public int getViews() {
return views;
}
public void setViews(int views) {
this.views = views;
}
}
新建BlogMapper接口
package com.haha.dao;
import com.haha.pojo.Blog;
public interface BlogMapper {
// 插入数据
int addBlog(Blog blog);
}
新建BlogMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.haha.dao.BlogMapper">
<insert id="addBlog" parameterType="blog">
insert into blog (id,title,author,create_time,views)
values (#{id},#{title},#{author},#{createTime},#{views})
</insert>
</mapper>
在mybatis-config.xml里面注册BlogMapper.xml
新建IDUtils
package com.haha.utils;
import java.util.UUID;
public class IDUtils {
public static String getId(){
return UUID.randomUUID().toString().replaceAll("-","");
}
}
项目目录结构
测试运行(向blog表中插入数据)
BlogMapperTest.java
import com.haha.dao.BlogMapper;
import com.haha.pojo.Blog;
import com.haha.utils.IDUtils;
import com.haha.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.Date;
public class BlogMapperTest {
@Test
public void test(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Blog blog = new Blog();
blog.setId(IDUtils.getId());
blog.setTitle("插入第一条数据");
blog.setAuthor("张三");
blog.setCreateTime(new Date());
blog.setViews(100);
mapper.addBlog(blog);
blog.setId(IDUtils.getId());
blog.setTitle("插入第二条数据");
blog.setAuthor("李四");
mapper.addBlog(blog);
blog.setId(IDUtils.getId());
blog.setTitle("插入第三条数据");
blog.setAuthor("张三");
blog.setViews(30);
mapper.addBlog(blog);
sqlSession.close();
}
}
3.2、if
BlogMapper.xml
如果title不为空,就添加查询条件 title=传过来的值
如果author不为空,就添加查询条件 author=传过来的值
<select id="useIf" parameterType="map" resultType="blog">
select * from blog where 1=1
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</select>
测试
@Test
public void test2(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap hashMap = new HashMap();
hashMap.put("title","插入第一条数据");
List<Blog> blogs = mapper.useIf(hashMap);
for(Blog blog : blogs){
System.out.println(blog);
}
sqlSession.close();
}
运行
3.3、choose(when、otherwise)
- choose 元素,它有点像 Java 中的 switch 语句,只选择其中一个通过
官网实例:
<select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG WHERE state = ‘ACTIVE’
<choose>
<when test="title != null">
AND title like #{title}
</when>
<when test="author != null and author.name != null">
AND author_name like #{author.name}
</when>
<otherwise>
AND featured = 1
</otherwise>
</choose>
</select>
3.4、where、set
-
where标签,可以代替sql语句里where
-
set标签,可以代替sql语句里set ,选择其中一个或多个
- trim标签,可以自定义where、set 的前缀后缀(trim标签基本不用)
3.5、foreach
例:
使用foreach之前的sql代码
我们使用foreach代替(id=1 or id=2 or id=3)这段内容
<select id="ididforeach" resultType="user">
SELECT * FROM user WHERE 1=1
<foreach item="id" collection="ids" open="and (" separator="or" close=")">
#{id}
</foreach>
</select>
图解:
3.6、bind(少用)
bind 元素允许你在 OGNL 表达式以外创建一个变量,并将其绑定到当前的上下文。比如:
<select id="selectBlogsLike" resultType="Blog">
<bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
SELECT * FROM BLOG
WHERE title LIKE #{pattern}
</select>
3.7、sql片段
sql标签里面尽量不要使用where标签,容易出错,大多数情况里面只使用if标签等
3.8、在注解中使用动态sql
使用 script 元素
mybatis官网实例: