第三天课程结束，多对多，通用mapper让我体会到代码不断的被简化，感谢前辈的贡献。
多对多符合现实场景，万物之间都是有关联的，都是多个对多个的关系。
6.关联查询：多对多
6.1 学生和老师数据模型
6.1.1 表间关系
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rsaQAZGk-1632788039797)(assets/image-20210721145256539.png)]
#老师表
CREATE TABLE teacher(
tid INT PRIMARY KEY,
NAME VARCHAR(50)
);
#学生表
CREATE TABLE student(
sid INT PRIMARY KEY,
NAME VARCHAR(50)
);
#中间表
CREATE TABLE teacher_student(
teacher_id INT ,
student_id INT,
CONSTRAINT ts_t_fk FOREIGN KEY (teacher_id) REFERENCES teacher(tid),
CONSTRAINT ts_s_fk FOREIGN KEY (student_id) REFERENCES student(sid)
);
INSERT INTO teacher VALUES (1,'肖老师');
INSERT INTO teacher VALUES (2,'马老师');
INSERT INTO student VALUES (1,'张三');
INSERT INTO student VALUES (2,'李四');
INSERT INTO student VALUES (3,'王五');
INSERT INTO teacher_student VALUES (1,1);
INSERT INTO teacher_student VALUES (1,2);
INSERT INTO teacher_student VALUES (1,3);
INSERT INTO teacher_student VALUES (2,1);
INSERT INTO teacher_student VALUES (2,2);
6.1.2 JavaBean及其关系
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sWRet8On-1632788039802)(assets/image-20210721145855505.png)]
-
JavaBean：Student
package com.czxy.ssm.domain; import java.util.ArrayList; import java.util.List; /** * #学生表 * CREATE TABLE student( * sid INT PRIMARY KEY, * NAME VARCHAR(50) * ); * @author 桐叔 * @email liangtong@itcast.cn */ public class Student { private Integer sid; private String name; private List<Teacher> teacherList = new ArrayList<>(); public Integer getSid() { return sid; } public void setSid(Integer sid) { this.sid = sid; } public String getName() { return name; } public void setName(String name) { this.name = name; } public List<Teacher> getTeacherList() { return teacherList; } public void setTeacherList(List<Teacher> teacherList) { this.teacherList = teacherList; } @Override public String toString() { return "Student{" + "sid=" + sid + ", name='" + name + '\'' + ", teacherList=" + teacherList + '}'; } } -
JavaBean：Teacher
package com.czxy.ssm.domain; import java.util.ArrayList; import java.util.List; /** * #老师表 * CREATE TABLE teacher( * tid INT PRIMARY KEY, * NAME VARCHAR(50) * ); * @author 桐叔 * @email liangtong@itcast.cn */ public class Teacher { private Integer tid; private String name; private List<Student> studentList = new ArrayList<>(); public Integer getTid() { return tid; } public void setTid(Integer tid) { this.tid = tid; } public String getName() { return name; } public void setName(String name) { this.name = name; } public List<Student> getStudentList() { return studentList; } public void setStudentList(List<Student> studentList) { this.studentList = studentList; } @Override public String toString() { return "Teacher{" + "tid=" + tid + ", name='" + name + '\'' + ", studentList=" + studentList + '}'; } }
6.2 多对多：老师–>学生
- 需要根据老师tid查询中间表中，对应的所有学生id
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Q5jllkRi-1632788039812)(assets/image-20210721155108930.png)]
6.2.1 student 映射
package com.czxy.ssm.mapper;
import com.czxy.ssm.domain.Student;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
/**
* @author 桐叔
* @email liangtong@itcast.cn
*/
public interface StudentMapper {
/**
* 通过tid查询对应的学生
* @param tid
* @return
* @throws Exception
*/
@Select("select * from student s where s.sid in (select student_id from teacher_student where teacher_id = #{tid} )")
public Student findStudentByTeacherId(@Param("tid") Integer tid) throws Exception;
}
6.2.2 teacher 映射
package com.czxy.ssm.mapper;
import com.czxy.ssm.domain.Teacher;
import org.apache.ibatis.annotations.*;
/**
* @author 桐叔
* @email liangtong@itcast.cn
*/
public interface TeacherMapper {
@Select("select * from teacher t where t.tid = #{tid}")
@Results({
@Result(property="tid" , column="tid"),
@Result(property="name" , column="name"),
@Result(property="studentList" , many=@Many(select="com.czxy.ssm.mapper.StudentMapper.findStudentByTeacherId") , column="tid"),
})
public Teacher selectById(@Param("tid") Integer tid);
}
6.2.3 测试
package com.czxy.ssm.test;
import com.czxy.ssm.domain.Order;
import com.czxy.ssm.domain.Teacher;
import com.czxy.ssm.mapper.OrderMapper;
import com.czxy.ssm.mapper.TeacherMapper;
import com.czxy.ssm.utils.MyBatisUtils;
/**
* @author 桐叔
* @email liangtong@itcast.cn
*/
public class Test09_SelectTeacher {
public static void main(String[] args) {
TeacherMapper teacherMapper = MyBatisUtils.getMapper(TeacherMapper.class);
Teacher teacher = teacherMapper.selectById(1);
// 打印
System.out.println(teacher);
MyBatisUtils.commitAndclose();
}
}
6.2.4 巩固练习
-
查询所有的老师，并查询老师对应的学生。
package com.czxy.ssm.mapper; import com.czxy.ssm.domain.Teacher; import org.apache.ibatis.annotations.*; import java.util.List; /** * @author 桐叔 * @email liangtong@itcast.cn */ public interface TeacherMapper { /** * 通过id查询详情 * @param tid * @return */ @Select("select * from teacher t where t.tid = #{tid}") @Results(id = "teacherResult" , value = { @Result(property="tid" , column="tid"), @Result(property="name" , column="name"), @Result(property="studentList" , many=@Many(select="com.czxy.ssm.mapper.StudentMapper.findStudentByTeacherId") , column="tid"), }) public Teacher selectById(@Param("tid") Integer tid); /** * 查询所有 * @return */ @Select("select * from teacher") @ResultMap("teacherResult") public List<Teacher> selectList(); } -
测试类
package com.czxy.ssm.test; import com.czxy.ssm.domain.Teacher; import com.czxy.ssm.mapper.TeacherMapper; import com.czxy.ssm.utils.MyBatisUtils; import java.util.List; /** * @author 桐叔 * @email liangtong@itcast.cn */ public class Test10_SelectAllTeacher { public static void main(String[] args) { TeacherMapper teacherMapper = MyBatisUtils.getMapper(TeacherMapper.class); List<Teacher> teacherList = teacherMapper.selectList(); // 打印 teacherList.forEach(teacher -> { System.out.println(teacher); }); MyBatisUtils.commitAndclose(); } }
7.分页查询
MyBatis没有提供分页支持，需要自己编写limit语句。
开发中我们采用PageHelper插件。
7.1 搭建环境
7.1.1 导入jar包
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GgqMakI9-1632788039814)(assets/image-20210721161056976.png)]
7.1.2 添加插件
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IvmWya7i-1632788039815)(assets/image-20210721161328679.png)]
<!-- 插件 -->
<plugins>
<plugin interceptor="com.github.pagehelper.PageHelper">
<!-- 方言 -->
<property name="dialect" value="mysql"/>
<!-- 设置为true时，使用RowBounds分页会进行count查询 -->
<property name="rowBoundsWithCount" value="true"/>
</plugin>
</plugins>
7.2 语法
1) 设置分页数据
PageHelper.startPage(int pageNum, int pageSize)
参数1：pageNum 第几页
参数2：pageSize 页面显示个数
2) 封装分页结果 PageInfo
new PageInfo(查询结果) //创建分页对象
pageInfo.getTotal(), //自动查询总条数
pageInfo.getPages(), //总分页数
7.3 使用
package com.czxy.ssm.test;
import com.czxy.ssm.domain.Teacher;
import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.TeacherMapper;
import com.czxy.ssm.mapper.UserMapper;
import com.czxy.ssm.utils.MyBatisUtils;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import java.util.List;
/**
* @author 桐叔
* @email liangtong@itcast.cn
*/
public class Test11_Page {
public static void main(String[] args) {
UserMapper userMapper = MyBatisUtils.getMapper(UserMapper.class);
// 设置分页 **
PageHelper.startPage(1,2);
// 查询
List<User> users = userMapper.selectAll();
// 获得封装对象 **
PageInfo<User> pageInfo = new PageInfo<>(users);
// 打印分页信息
long total = pageInfo.getTotal();
List<User> list = pageInfo.getList();
System.out.println("总条数：" + total);
System.out.println("分页数据：");
list.forEach(user -> {
System.out.println(user);
});
MyBatisUtils.commitAndclose();
}
}
8. 通用Mapper
8.1 概述
-
通用Mapper对MyBatis进行简化的第三方工具包。
-
通用Mapper提供了一个名为
Mapper<T>的接口，用于自动完成单表的增删改查操作。
public interface UserMapper extends Mapper<User> {
}
- 如果通用Mapper中的方法不足以满足你的需求，直接添加自定义方法即可。
8.2 搭建环境
8.2.1 导入jar
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cthmBtcG-1632788039816)(assets/image-20210721173357517.png)]
8.2.2 修改工具类
- 添加内容从官方文档中拷贝
package com.czxy.ssm.utils;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.MySqlMapper;
import tk.mybatis.mapper.entity.Config;
import tk.mybatis.mapper.mapperhelper.MapperHelper;
import java.io.InputStream;
/**
* @author 桐叔
* @email liangtong@itcast.cn
*/
public class MyBatisUtils {
// 会话工厂
private static SqlSessionFactory factory;
static{
try {
// 1.1 加载核心配置文件
InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
// 1.2 获得工厂
factory = new SqlSessionFactoryBuilder().build(is);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
private static ThreadLocal<SqlSession> local = new ThreadLocal<>();
/**
* 获得新会话
* @return
*/
private static SqlSession openSession(){
SqlSession sqlSession = local.get();
if(sqlSession == null){
sqlSession = factory.openSession();
//创建一个MapperHelper
MapperHelper mapperHelper = new MapperHelper();
//特殊配置
Config config = new Config();
// 设置UUID生成策略
// 配置UUID生成策略需要使用OGNL表达式
// 默认值32位长度:@java.util.UUID@randomUUID().toString().replace("-", "")
//config.setUUID("");
// 主键自增回写方法,默认值MYSQL,详细说明请看文档
// config.setIDENTITY("HSQLDB");
// 支持方法上的注解
// 3.3.1版本增加
config.setEnableMethodAnnotation(true);
config.setNotEmpty(true);
// 序列的获取规则,使用{num}格式化参数，默认值为{0}.nextval，针对Oracle
// 可选参数一共3个，对应0,1,2,分别为SequenceName，ColumnName, PropertyName
//config.setSeqFormat("NEXT VALUE FOR {0}");
// 设置全局的catalog,默认为空，如果设置了值，操作表时的sql会是catalog.tablename
//config.setCatalog("");
// 设置全局的schema,默认为空，如果设置了值，操作表时的sql会是schema.tablename
// 如果同时设置了catalog,优先使用catalog.tablename
//config.setSchema("");
// 主键自增回写方法执行顺序,默认AFTER,可选值为(BEFORE|AFTER)
//config.setOrder("AFTER");
//设置配置
mapperHelper.setConfig(config);
// 注册通用tk.mybatis.mapper.common.Mapper接口 - 可以自动注册继承的接口
mapperHelper.registerMapper(Mapper.class);
mapperHelper.registerMapper(MySqlMapper.class);
// mapperHelper.registerMapper(SqlServerMapper.class);
// mapperHelper.registerMapper(IdsMapper.class);
//配置完成后，执行下面的操作
mapperHelper.processConfiguration(sqlSession.getConfiguration());
local.set(sqlSession);
}
return sqlSession;
}
/**
* 获得mapper
* @param clazz
* @return
*/
public static <T> T getMapper(Class<T> clazz){
return openSession().getMapper(clazz);
}
/**
* 释放资源
*/
public static void close() {
SqlSession sqlSession = openSession();
if(sqlSession != null){
sqlSession.close();
}
}
/**
* 提交并释放资源
*/
public static void commitAndclose() {
SqlSession sqlSession = openSession();
if(sqlSession != null){
sqlSession.commit();
close();
}
}
/**
* 回滚并释放资源
*/
public static void rollbackAndclose() {
SqlSession sqlSession = openSession();
if(sqlSession != null){
sqlSession.rollback();
close();
}
}
}
8.3 编写Mapper
- 编写接口，继承tk.mybatis.mapper.common.Mapper接口即可
- 注意：Mapper接口以tk开头
package com.czxy.ssm.mapper;
import com.czxy.ssm.domain.User;
import tk.mybatis.mapper.common.Mapper;
/**
* @author 桐叔
* @email liangtong@itcast.cn
*/
public interface UserMapper2 extends Mapper<User> {
}
8.4 通用API
-
查询方法
方法名 描述 T selectOne(T t) 根据实体中的属性进行查询，只能有一个返回值，有多个结果是抛出异常，查询条件使用等号 List select(T t) 根据实体中的属性值进行查询，查询条件使用等号 List selectAll() 查询全部结果 int selectCount(T t) 根据实体中的属性查询总数，查询条件，使用等号 T selectByPrimaryKey(Object key) 根据主键字段进行查询 boolean existsWhithPrimaryKey(Object key) 根据主键字段查询记录是否存在 List selectByExample(Object example) 根据Example条件进行查询 T selectOneByExample(Object example) 根据Example条件进行查询，只能有一个返回值 int selectCountByExample(Object example) 根据Example条件进行查询记录数 -
插入方法
方法名 描述 int insert(T t) 保存一个实体，null的属性也会保存，不会使用数据库默认值 int intsertSelective(T t) 保存一个实体，null的属性不会保存，使用数据库默认值 -
更新方法
方法名 描述 int updateByPrimaryKey(T t) 根据主键更新实体全部字段，null值会被更新 int updateByPrimaryKeySelective(T t) 根据主键更新实体中不为null值的字段 -
删除方法
方法名 描述 int delete(T t) 根据实体属性作为条件进行删除，查询条件使用等号 int deletePrimaryKey(Object key) 根据主键字段进行删除 int deleteByExample(Object example) 根据Example条件删除数据
8.4.1 通过主键查询
-
1）确定主键，否则所有字段都是主键
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NNPDBIdk-1632788039817)(assets/image-20210721173445273.png)]
-
2）测试
package com.czxy.ssm.test; import com.czxy.ssm.domain.User; import com.czxy.ssm.mapper.UserMapper; import com.czxy.ssm.mapper.UserMapper2; import com.czxy.ssm.utils.MyBatisUtils; import org.junit.Test; import java.io.IOException; import java.util.List; /** * @author 桐叔 * @email liangtong@itcast.cn */ public class Test13_Mapper { @Test public void testSelectByPrimaryKey() { UserMapper2 userMapper2 = MyBatisUtils.getMapper(UserMapper2.class); User user = userMapper2.selectByPrimaryKey("1"); System.out.println(user); MyBatisUtils.commitAndclose(); } }
8.4.2 查询所有
@Test
public void testSelectAll() {
UserMapper2 userMapper2 = MyBatisUtils.getMapper(UserMapper2.class);
List<User> list = userMapper2.selectAll();
// 打印
list.forEach(System.out::println);
MyBatisUtils.commitAndclose();
}
8.4.3 添加
@Test
public void testInsert() {
UserMapper2 userMapper2 = MyBatisUtils.getMapper(UserMapper2.class);
User user = new User();
user.setUid("2");
user.setUsername("jack");
user.setPassword("1234");
user.setName("杰克");
user.setEmail("itcast_lt@163.com");
user.setBirthday(new Date());
user.setSex("男");
user.setSex("0");
int result = userMapper2.insert(user);
// 打印
System.out.println(result);
MyBatisUtils.commitAndclose();
}
8.4.4 修改
@Test
public void testUpdate() {
UserMapper2 userMapper2 = MyBatisUtils.getMapper(UserMapper2.class);
User user = new User();
user.setUid("2");
user.setUsername("jack");
user.setPassword("1234");
user.setName("杰克");
user.setEmail("itcast_lt@163.com");
user.setBirthday(new Date());
user.setSex("男");
user.setSex("0");
int result = userMapper2.updateByPrimaryKey(user);
// 打印
System.out.println(result);
MyBatisUtils.commitAndclose();
}
8.4.5 删除
@Test
public void testDelete() {
UserMapper2 userMapper2 = MyBatisUtils.getMapper(UserMapper2.class);
int result = userMapper2.deleteByPrimaryKey("2");
// 打印
System.out.println(result);
MyBatisUtils.commitAndclose();
}
8.4.6 多条件查询
- 语法：
// 获得多条件对象
Example example = new Example(对象.class);
Example.Criteria criteria = example.createCriteria();
// 常见条件方法
andLike() //模糊查询
andEqualTo() //等值查询
andLessThanOrEqualTo() //<=查询
andGreaterThanOrEqualTo() //>=查询
andBetween() //区间查询
@Test
public void testCondition() {
UserMapper2 userMapper2 = MyBatisUtils.getMapper(UserMapper2.class);
Example example = new Example(User.class);
Example.Criteria criteria = example.createCriteria();
criteria.andLike("name", "%王%");
criteria.andEqualTo("sex", "男");
List<User> list = userMapper2.selectByExample(example);
for (User user : list) {
System.out.println(user);
}
MyBatisUtils.commitAndclose();
}
总结：学完之后还是会有疑问，通用mapper可以用多表查询吗，可以再去网上查一波。注解版目前是学完了，还有xml版，其实原理一样，只不过适用场景不同罢了。加油，离工作又进了一天。
1131
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
