MyBatis的各种查询功能
如果查询出的数据只有一条,可以通过
实体类对象接收
List集合接收
Map集合接收,结果{password=123456, sex=男, id=1, age=23, username=admin}
如果查询出的数据有多条,一定不能用实体类对象接收,会抛异常TooManyResultsException,可以通过
实体类类型的LIst集合接收
Map类型的LIst集合接收
在mapper接口的方法上添加@MapKey注解
查询一个实体类对象 1 2 3 4 5 6 7 8 9 10 User getUserById (@Param("id") int id) ; <!--User getUserById (@Param("id") int id) ;--> <select id="getUserById" resultType="User" > select * from t_user where id = #{id} </select>
查询一个List集合 1 2 3 4 5 6 7 8 9 List<User> getUserList () ; <!--List<User> getUserList () ;--> <select id="getUserList" resultType="User" > select * from t_user </select>
查询单个数据 1 2 3 4 5 6 7 8 9 10 11 12 13 int getCount () ; <!--int getCount () ;--> <select id="getCount" resultType="_integer" > select count (id) from t_user </select>
查询一条数据为map集合 1 2 3 4 5 6 7 8 9 10 11 Map<String, Object> getUserToMap (@Param("id") int id) ; <!--Map<String, Object> getUserToMap (@Param("id") int id) ;--> <select id="getUserToMap" resultType="map" > select * from t_user where id = #{id} </select> <!--结果:{password=123456 , sex=男, id=1 , age=23 , username=admin}-->
查询多条数据为map集合 方法一 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 List<Map<String, Object>> getAllUserToMap () ; <!--Map<String, Object> getAllUserToMap () ;--> <select id="getAllUserToMap" resultType="map" > select * from t_user </select> <!-- 结果: [{password=123456 , sex=男, id=1 , age=23 , username=admin}, {password=123456 , sex=男, id=2 , age=23 , username=张三}, {password=123456 , sex=男, id=3 , age=23 , username=张三}] -->
方法二 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 @MapKey("id") Map<String, Object> getAllUserToMap () ; <!--Map<String, Object> getAllUserToMap () ;--> <select id="getAllUserToMap" resultType="map" > select * from t_user </select> <!-- 结果: { 1 ={password=123456 , sex=男, id=1 , age=23 , username=admin}, 2 ={password=123456 , sex=男, id=2 , age=23 , username=张三}, 3 ={password=123456 , sex=男, id=3 , age=23 , username=张三} } -->
特殊SQL的执行 模糊查询 1 2 3 4 5 6 7 8 9 10 11 12 List<User> getUserByLike (@Param("username") String username) ; <!--List<User> getUserByLike (@Param("username") String username) ;--> <select id="getUserByLike" resultType="User" > <!--select * from t_user where username like '%${mohu}%' --> <!--select * from t_user where username like concat ('%' ,#{mohu},'%' ) --> select * from t_user where username like "%" #{mohu}"%" </select>
其中select * from t_user where username like "%"#{mohu}"%"
是最常用的
批量删除
只能使用${},如果使用#{},则解析后的sql语句为delete from t_user where id in ('1,2,3')
,这样是将1,2,3
看做是一个整体,只有id为1,2,3
的数据会被删除。正确的语句应该是delete from t_user where id in (1,2,3)
,或者delete from t_user where id in ('1','2','3')
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 int deleteMore (@Param("ids") String ids) ; <delete id="deleteMore" > delete from t_user where id in (${ids}) </delete>@Test public void deleteMore () { SqlSession sqlSession = SqlSessionUtils.getSqlSession(); SQLMapper mapper = sqlSession.getMapper(SQLMapper.class); int result = mapper.deleteMore("1,2,3,8" ); System.out.println(result); }
动态设置表名
1 2 3 4 5 6 7 8 9 10 List<User> getUserByTable (@Param("tableName") String tableName) ; <!--List<User> getUserByTable (@Param("tableName") String tableName) ;--> <select id="getUserByTable" resultType="User" > select * from ${tableName} </select>
添加功能获取自增的主键
使用场景
t_clazz(clazz_id,clazz_name)
t_student(student_id,student_name,clazz_id)
添加班级信息
获取新添加的班级的id
为班级分配学生,即将某学的班级id修改为新添加的班级的id
在mapper.xml中设置两个属性
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 void insertUser (User user) ; <!--void insertUser (User user) ;--> <insert id="insertUser" useGeneratedKeys="true" keyProperty="id" > insert into t_user values (null ,#{username},#{password},#{age},#{sex},#{email}) </insert>@Test public void insertUser () { SqlSession sqlSession = SqlSessionUtils.getSqlSession(); SQLMapper mapper = sqlSession.getMapper(SQLMapper.class); User user = new User (null , "ton" , "123" , 23 , "男" , "123@321.com" ); mapper.insertUser(user); System.out.println(user); }
自定义映射resultMap resultMap处理字段和属性的映射关系
resultMap:设置自定义映射
属性:
id:表示自定义映射的唯一标识,不能重复
type:查询的数据要映射的实体类的类型
子标签:
id:设置主键的映射关系
result:设置普通字段的映射关系
子标签属性:
property:设置映射关系中实体类中的属性名
column:设置映射关系中表中的字段名
若字段名和实体类中的属性名不一致,则可以通过resultMap设置自定义映射,即使字段名和属性名一致的属性也要映射,也就是全部属性都要列出来
1 2 3 4 5 6 7 8 9 10 11 <resultMap id ="empResultMap" type ="Emp" > <id property ="eid" column ="eid" > </id > <result property ="empName" column ="emp_name" > </result > <result property ="age" column ="age" > </result > <result property ="sex" column ="sex" > </result > <result property ="email" column ="email" > </result > </resultMap > <select id ="getAllEmp" resultMap ="empResultMap" > select * from t_emp</select >
多对一映射处理
查询员工信息以及员工所对应的部门信息
1 2 3 4 5 6 7 8 9 public class Emp { private Integer eid; private String empName; private Integer age; private String sex; private String email; private Dept dept; }
级联方式处理映射关系 1 2 3 4 5 6 7 8 9 10 11 12 13 <resultMap id ="empAndDeptResultMapOne" type ="Emp" > <id property ="eid" column ="eid" > </id > <result property ="empName" column ="emp_name" > </result > <result property ="age" column ="age" > </result > <result property ="sex" column ="sex" > </result > <result property ="email" column ="email" > </result > <result property ="dept.did" column ="did" > </result > <result property ="dept.deptName" column ="dept_name" > </result > </resultMap > <select id ="getEmpAndDept" resultMap ="empAndDeptResultMapOne" > select * from t_emp left join t_dept on t_emp.eid = t_dept.did where t_emp.eid = #{eid}</select >
使用association处理映射关系
association:处理多对一的映射关系
property:需要处理多对的映射关系的属性名
javaType:该属性的类型
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 <resultMap id ="empAndDeptResultMapTwo" type ="Emp" > <id property ="eid" column ="eid" > </id > <result property ="empName" column ="emp_name" > </result > <result property ="age" column ="age" > </result > <result property ="sex" column ="sex" > </result > <result property ="email" column ="email" > </result > <association property ="dept" javaType ="Dept" > <id property ="did" column ="did" > </id > <result property ="deptName" column ="dept_name" > </result > </association > </resultMap > <select id ="getEmpAndDept" resultMap ="empAndDeptResultMapTwo" > select * from t_emp left join t_dept on t_emp.did = t_dept.did where t_emp.eid = #{eid}</select >
分步查询 1. 查询员工信息
select:设置分布查询的sql的唯一标识(namespace.SQLId或mapper接口的全类名.方法名)
column:设置分步查询的条件
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 Emp getEmpAndDeptByStepOne (@Param("eid") Integer eid) ;123456789 <resultMap id="empAndDeptByStepResultMap" type="Emp" > <id property="eid" column="eid" ></id> <result property="empName" column="emp_name" ></result> <result property="age" column="age" ></result> <result property="sex" column="sex" ></result> <result property="email" column="email" ></result> <association property="dept" select="com.atguigu.mybatis.mapper.DeptMapper.getEmpAndDeptByStepTwo" column="did" ></association> </resultMap> <!--Emp getEmpAndDeptByStepOne (@Param("eid") Integer eid) ;--> <select id="getEmpAndDeptByStepOne" resultMap="empAndDeptByStepResultMap" > select * from t_emp where eid = #{eid} </select>
2. 查询部门信息 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 Dept getEmpAndDeptByStepTwo (@Param("did") Integer did) ; <!--此处的resultMap仅是处理字段和属性的映射关系--> <resultMap id="EmpAndDeptByStepTwoResultMap" type="Dept" > <id property="did" column="did" ></id> <result property="deptName" column="dept_name" ></result> </resultMap> <!--Dept getEmpAndDeptByStepTwo (@Param("did") Integer did) ;--> <select id="getEmpAndDeptByStepTwo" resultMap="EmpAndDeptByStepTwoResultMap" > select * from t_dept where did = #{did} </select>
一对多映射处理 1 2 3 4 5 6 public class Dept { private Integer did; private String deptName; private List<Emp> emps; }
collection
collection:用来处理一对多的映射关系
ofType:表示该属性对饮的集合中存储的数据的类型
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 <resultMap id ="DeptAndEmpResultMap" type ="Dept" > <id property ="did" column ="did" > </id > <result property ="deptName" column ="dept_name" > </result > <collection property ="emps" ofType ="Emp" > <id property ="eid" column ="eid" > </id > <result property ="empName" column ="emp_name" > </result > <result property ="age" column ="age" > </result > <result property ="sex" column ="sex" > </result > <result property ="email" column ="email" > </result > </collection > </resultMap > <select id ="getDeptAndEmp" resultMap ="DeptAndEmpResultMap" > select * from t_dept left join t_emp on t_dept.did = t_emp.did where t_dept.did = #{did}</select >
分步查询 1. 查询部门信息 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 Dept getDeptAndEmpByStepOne (@Param("did") Integer did) ; <resultMap id="DeptAndEmpByStepOneResultMap" type="Dept" > <id property="did" column="did" ></id> <result property="deptName" column="dept_name" ></result> <collection property="emps" select="com.atguigu.mybatis.mapper.EmpMapper.getDeptAndEmpByStepTwo" column="did" ></collection> </resultMap> <!--Dept getDeptAndEmpByStepOne (@Param("did") Integer did) ;--> <select id="getDeptAndEmpByStepOne" resultMap="DeptAndEmpByStepOneResultMap" > select * from t_dept where did = #{did} </select>
2. 根据部门id查询部门中的所有员工 1 2 3 4 5 6 7 8 9 10 11 List<Emp> getDeptAndEmpByStepTwo (@Param("did") Integer did) ; <!--List<Emp> getDeptAndEmpByStepTwo (@Param("did") Integer did) ;--> <select id="getDeptAndEmpByStepTwo" resultType="Emp" > select * from t_emp where did = #{did} </select>
延迟加载
分步查询的优点:可以实现延迟加载,但是必须在核心配置文件中设置全局配置信息:
lazyLoadingEnabled:延迟加载的全局开关。当开启时,所有关联对象都会延迟加载
aggressiveLazyLoading:当开启时,任何方法的调用都会加载该对象的所有属性。 否则,每个属性会按需加载
此时就可以实现按需加载,获取的数据是什么,就只会执行相应的sql。此时可通过association和collection中的fetchType属性设置当前的分步查询是否使用延迟加载,fetchType=“lazy(延迟加载)|eager(立即加载)”
1 2 3 4 5 6 7 8 9 10 11 <settings > <setting name ="lazyLoadingEnabled" value ="true" /> </settings > @Test public void getEmpAndDeptByStepOne() { SqlSession sqlSession = SqlSessionUtils.getSqlSession(); EmpMapper mapper = sqlSession.getMapper(EmpMapper.class); Emp emp = mapper.getEmpAndDeptByStepOne(1); System.out.println(emp.getEmpName()); }
开启延迟加载,只运行获取emp的SQL语句
1 2 3 4 5 6 7 8 9 @Test public void getEmpAndDeptByStepOne () { SqlSession sqlSession = SqlSessionUtils.getSqlSession(); EmpMapper mapper = sqlSession.getMapper(EmpMapper.class); Emp emp = mapper.getEmpAndDeptByStepOne(1 ); System.out.println(emp.getEmpName()); System.out.println("----------------" ); System.out.println(emp.getDept()); }
开启后,需要用到查询dept的时候才会调用相应的SQL语句
fetchType:当开启了全局的延迟加载之后,可以通过该属性手动控制延迟加载的效果,fetchType=“lazy(延迟加载)|eager(立即加载)”
1 2 3 4 5 6 7 8 9 10 11 <resultMap id ="empAndDeptByStepResultMap" type ="Emp" > <id property ="eid" column ="eid" > </id > <result property ="empName" column ="emp_name" > </result > <result property ="age" column ="age" > </result > <result property ="sex" column ="sex" > </result > <result property ="email" column ="email" > </result > <association property ="dept" select ="com.atguigu.mybatis.mapper.DeptMapper.getEmpAndDeptByStepTwo" column ="did" fetchType ="lazy" > </association > </resultMap >