1 关联查询:多对多
1.1 根据表编写JavaBean
-
学生
public class Student { private Integer sid; private String name; // 多对多:不同的学生,可以上【不同老师】的课 private List<Teacher> teacherList = new ArrayList<>();
-
老师
public class Teacher { private Integer tid; private String name; //多对多: 不同的老师,给【不同的学生】讲课 private List<Student> studentList = new ArrayList<>();
1.2 关联查询
1.2.1 功能分析
- 查询所有的老师,同时查询老师教授的所有学生
- 步骤:
- 编写TeacherMapper,查询所有老师(不含学生)
- 编写teacher测试类,查询所有老师(不含学生)
- 编写StudentMapper,根据tid查询关联的所有学生(中间表–>学生表)
- 编写student测试类
- 修改TeacherMapper,给Teacher的studentList添加映射关系
1.2.2 单表:查询所有老师
-
完成步骤1和步骤2
-
功能接口
package com.czxy.ssm.mapper; import com.czxy.ssm.domain.Teacher; import org.apache.ibatis.annotations.Select; import java.util.List; /** * @author LiReign */ public interface TeacherMapper { /** * 查询所有老师 * @return */ @Select("select * from teacher") public List<Teacher> selectAll(); }
-
测试类
package com.czxy.ssm; import com.czxy.ssm.domain.Teacher; import com.czxy.ssm.mapper.TeacherMapper; import com.czxy.ssm.utils.MyBatisUtils; import java.util.List; /** * @author LiReign */ public class Test04_SelectAllTeacher { public static void main(String[] args) { //1 获得mapper TeacherMapper teacherMapper = MyBatisUtils.getMapper(TeacherMapper.class); //2 查询所有 List<Teacher> list = teacherMapper.selectAll(); for (Teacher teacher : list) { System.out.println(teacher); } //3 释放 MyBatisUtils.commitAndclose(); } }
1.2.3 单表:指定老师的所有学生
-
编写Mapper
package com.czxy.ssm.mapper; import com.czxy.ssm.domain.Student; import org.apache.ibatis.annotations.Param; import org.apache.ibatis.annotations.Select; import java.util.List; /** * @author LiReign */ public interface StudentMapper { /** * 通过tid查询所有的学生 * @param tid * @return */ @Select("SELECT s.* FROM teacher_student ts,student s WHERE ts.student_id = s.sid AND ts.teacher_id = #{tid}") public List<Student> selectAllByTid(@Param("tid") Integer tid); }
-
测试类
package com.czxy.ssm; import com.czxy.ssm.domain.Student; import com.czxy.ssm.mapper.StudentMapper; import com.czxy.ssm.utils.MyBatisUtils; import java.util.List; /** * @author LiReign */ public class Test05_SelectAllStudent { public static void main(String[] args) { //1 获得mapper StudentMapper studentMapper = MyBatisUtils.getMapper(StudentMapper.class); //2 查询所有 List<Student> list = studentMapper.selectAllByTid(2); for (Student student : list) { System.out.println(student); } //3 释放 MyBatisUtils.commitAndclose(); } }
1.2.4 关联
/**
* 查询所有老师
* @return
*/
@Select("select * from teacher")
@Results({
@Result(property = "tid",column = "tid"),
@Result(property = "studentList", column = "tid", many = @Many(select="com.czxy.ssm.mapper.StudentMapper.selectAllByTid"))
})
public List<Teacher> selectAll();
2 分页
2.1 分析执行机制
2.2 编写步骤
- 前提:导入jar包、配置分页插件(是否执行查询总条数)
- 完成之前成功
- 查询之前设置分页参数:PageHelper.startPage()
- 使用PageInfo封装查询结果
2.3 分页环境
-
导入jar包
-
配置分页插件
<!-- 插件 --> <plugins> <plugin interceptor="com.github.pagehelper.PageHelper"> <!-- 方言 --> <property name="dialect" value="mysql"/> <!-- 设置为true时,使用RowBounds分页会进行count查询 --> <property name="rowBoundsWithCount" value="true"/> </plugin> </plugins>
2.4 分页操作
package com.czxy.ssm;
import com.czxy.ssm.domain.User;
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 LiReign
*/
public class Test06_Page {
public static void main(String[] args) {
//1 获得mapper
UserMapper userMapper = MyBatisUtils.getMapper(UserMapper.class);
//3 设置分页参数
PageHelper.startPage(1, 4);
//3 分页数据
List<User> list = userMapper.selectAll();
//4 分页封装
PageInfo pageInfo = new PageInfo(list);
System.out.println("总条数:" + pageInfo.getTotal());
System.out.println("每页个数:" + pageInfo.getPageSize());
System.out.println("当前页:" + pageInfo.getPageNum());
System.out.println("分页数:" + pageInfo.getPages());
System.out.println("是否上一页:" + pageInfo.isHasPreviousPage());
System.out.println("是否下一页:" + pageInfo.isHasNextPage());
//3 释放
MyBatisUtils.commitAndclose();
}
}
3 通用Mapper
3.1 目标:
- 简化单表的增删改查操作
3.2 分析
3.3 入门:查询所有
-
导入jar包
-
完善工具类
//修改openSession方法,需要导入的包都是tk下的 import tk.mybatis.mapper.common.Mapper; import tk.mybatis.mapper.common.MySqlMapper; import tk.mybatis.mapper.entity.Config; import tk.mybatis.mapper.mapperhelper.MapperHelper; /** * 获得新会话 * @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; }
-
编写UserMapper2,继承通用mapper
package com.czxy.ssm.mapper; import com.czxy.ssm.domain.User; import tk.mybatis.mapper.common.Mapper; //注意:tk包 /** * @author LiReign */ public interface UserMapper2 extends Mapper<User> { }
-
测试查询所有
package com.czxy.ssm; 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 com.github.pagehelper.PageHelper; import com.github.pagehelper.PageInfo; import java.util.List; /** * @author LiReign */ public class Test07_Mapper_selectAll { public static void main(String[] args) { //1 获得mapper UserMapper2 userMapper2 = MyBatisUtils.getMapper(UserMapper2.class); //2 查询素有 List<User> list = userMapper2.selectAll(); list.forEach(System.out::println); //3 释放 MyBatisUtils.commitAndclose(); } }
3.4 常见功能
3.4.1 通过id查询
-
使用前提:必须设置主键
-
核心API
User user = userMapper2.selectByPrimaryKey("u004");
3.4.2 添加
//userMapper2.insert(); //添加所有数据
//userMapper2.insertSelective(); //添加所有非空的数据
//userMapper2.insert(user);
userMapper2.insertSelective(user);
3.4.3 修改
//userMapper2.updateByPrimaryKey(); //通过主键更新所有数据
//userMapper2.updateByPrimaryKeySelective(); //通过主键更新所有非空的数据
// userMapper2.updateByPrimaryKey(user);
userMapper2.updateByPrimaryKeySelective(user);
3.4.4 删除
// 通过主键删除数据
int result = userMapper2.deleteByPrimaryKey("u005");
3.4.5 条件查询
// 2.1 拼凑条件
Example example = new Example(User.class);
Example.Criteria criteria = example.createCriteria();
//criteria.andLike("name","%张%");
//criteria.andEqualTo("password", "1234");
// 2.2 条件查询
List<User> list = userMapper2.selectByExample(example);
3.5 JPA常见注解
@Entity(name="表名")
public class 类 {
@Id
private String id;
@Column(name = "列名")
private String username;
}