9月27日MyBatis笔记
发布时间：
本文字数：927 字 阅读完需：约 6 分钟
多表联查
示例1：查询人员及人员所在的部门
AppTest.java
@Test
public void test16() throws IOException{
Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
SqlSession sqlSession = factory.openSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
User u = userDao.selectUserAndDeptByPrimaryKey01("admin");
System.out.println(u);
sqlSession.close();
reader.close();
}
@Test
public void test17() throws IOException {
Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
SqlSession sqlSession = factory.openSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
User u = userDao.selectUserAndDeptByPrimaryKey02("admin");
System.out.println(u);
sqlSession.close();
reader.close();
}
DeptDao.java
Dept selectByPrimaryKey(Integer deptId);
DeptDao.xml
<select id="selectByPrimaryKey" resultMap="BaseResultMap">
select <include refid="columnName"></include> from t_dept where dept_id = #{deptId}
</select>
UserDao.java
User selectUserAndDeptByPrimaryKey01(String userId);
User selectUserAndDeptByPrimaryKey02(String userId);
List<User> selectAllByDeptId(String DeptId);
UserDao.xml
<resultMap id="Rm01" type="com.zr.vo.User">
<id column="user_id" property="userId"></id>
<result column="user_name" property="userName"></result>
<result column="password" property="password"></result>
<result column="sex" property="sex"></result>
<result column="tel" property="tel"></result>
<result column="status" property="status"></result>
<result column="img" property="img"></result>
<result column="dept_id" property="deptId"></result>
<result column="create_time" property="createTime"></result>
<result column="create_user" property="createUser"></result>
<result column="modify_time" property="modifyTime"></result>
<result column="modify_user" property="modifyUser"></result>
<association property="dept" javaType="com.zr.vo.Dept" select="com.zr.dao.DeptDao.selectByPrimaryKey" column="dept_id"></association>
</resultMap>
<resultMap id="Rm02" type="com.zr.vo.User">
<id column="user_id" property="userId"></id>
<result column="user_name" property="userName"></result>
<result column="password" property="password"></result>
<result column="sex" property="sex"></result>
<result column="tel" property="tel"></result>
<result column="status" property="status"></result>
<result column="img" property="img"></result>
<result column="dept_id" property="deptId"></result>
<result column="create_time" property="createTime"></result>
<result column="create_user" property="createUser"></result>
<result column="modify_time" property="modifyTime"></result>
<result column="modify_user" property="modifyUser"></result>
<association property="dept" >
<id column="dept_dept_id" property="deptId"></id>
<result column="dept_dept_name" property="deptName"></result>
<result column="dept_location" property="location"></result>
<result column="dept_create_time" property="createTime"></result>
<result column="dept_create_user" property="createUser"></result>
<result column="dept_modify_time" property="modifyTime"></result>
<result column="dept_modify_user" property="modifyUser"></result>
</association>
</resultMap>
<select id="selectUserAndDeptByPrimaryKey01" resultMap="Rm01">
select * from t_user where user_id = #{userId}
</select>
<select id="selectUserAndDeptByPrimaryKey02" resultMap="Rm02">
select u.*,
d.dept_id as dept_dept_id,
d.dept_name as dept_dept_name,
d.location as dept_location,
d.create_time as dept_create_time,
d.create_user as dept_create_user,
d.modify_time as dept_modify_time,
d.modify_user as dept_modify_user
from t_user u left join t_dept d on u.dept_id=d.dept_id where u.user_id=#{userId}
</select>
<select id="selectAllByDeptId" resultMap="BaseResultMap">
select * from t_user where dept_id=#{deptId}
</select>
示例2：查询部门及该部门的所有人员
AppTest.java
/**
* 查询部门及该部门的所有人员
*
* @throws IOException
*/
@Test
public void test18() throws IOException {
Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
SqlSession sqlSession = factory.openSession();
DeptDao deptDao = sqlSession.getMapper(DeptDao.class);
Dept dept = deptDao.selectByPrimaryKey01(1);
System.out.println(dept);
sqlSession.close();
reader.close();
}
/**
* 查询部门及该部门的所有人员
*
* @throws IOException
*/
@Test
public void test19() throws IOException {
Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
SqlSession sqlSession = factory.openSession();
DeptDao deptDao = sqlSession.getMapper(DeptDao.class);
Dept dept = deptDao.selectByPrimaryKey02(1);
System.out.println(dept);
sqlSession.close();
reader.close();
}
DeptDao.xml
<resultMap id="Rm01" type="com.zr.vo.Dept">
<id column="dept_id" property="deptId"></id>
<result column="dept_name" property="deptName"></result>
<result column="location" property="location"></result>
<result column="create_time" property="createTime"></result>
<result column="create_user" property="createUser"></result>
<result column="modify_time" property="modifyTime"></result>
<result column="modify_user" property="modifyUser"></result>
<collection property="userList" select="com.zr.dao.UserDao.selectAllByDeptId" column="dept_id"></collection>
</resultMap>
<resultMap id="Rm02" type="com.zr.vo.Dept">
<id column="dept_id" property="deptId"></id>
<result column="dept_name" property="deptName"></result>
<result column="dept_location" property="location"></result>
<result column="dept_create_time" property="createTime"></result>
<result column="dept_create_user" property="createUser"></result>
<result column="dept_modify_time" property="modifyTime"></result>
<result column="dept_modify_user" property="modifyUser"></result>
<!-- ofType collection的对象类型-->
<collection property="userList" ofType="com.zr.vo.User">
<id column="user_id" property="userId"></id>
<result column="user_name" property="userName"></result>
<result column="password" property="password"></result>
<result column="sex" property="sex"></result>
<result column="tel" property="tel"></result>
<result column="status" property="status"></result>
<result column="img" property="img"></result>
<result column="dept_id" property="deptId"></result>
<result column="create_time" property="createTime"></result>
<result column="create_user" property="createUser"></result>
<result column="modify_time" property="modifyTime"></result>
<result column="modify_user" property="modifyUser"></result>
</collection>
</resultMap>
<select id="selectByPrimaryKey01" resultMap="Rm01">
select <include refid="columnName"></include> from t_dept where dept_id = #{deptId}
</select>
<select id="selectByPrimaryKey02" resultMap="Rm02">
select u.* ,d.dept_id as dept_dept_id,
d.dept_name as dept_dept_name,
d.location as dept_location,
d.create_time as dept_create_time,
d.create_user as dept_create_user,
d.modify_time as dept_modify_time,
d.modify_user as dept_modify_user from t_dept d left join t_user u
on d.dept_id = u.dept_id where d.dept_id = #{deptId)}
</select>
DeptDao.java
Dept selectByPrimaryKey01(Integer deptId);
Dept selectByPrimaryKey02(Integer deptId);
延迟加载
多表联查时配置延迟加载，可以在不需要查询某些表的时候不进行查询
配置方法: mybatis-config.xml
<configuration>
<settings>
<setting name="logImpl" value="org.apache.ibatis.logging.stdout.StdOutImpl"/>
<!-- 配置延迟加载 -->
<setting name="lazyLoadingEnabled" value="true"/>
</settings>
动态sql
适用于模糊查询，动态添加查询条件，使用<where>标签，其中用<if>标签做判断，用法如下
UserDao.xml
<select id="selectAll" resultMap="BaseResultMap">
select * from t_user
<where>
<if test="userId != null and userId != ''">and user_id like '%${userId}%'</if>
<if test="userName != null">and user_name like '%${userName}%'</if>
</where>
</select>
AppTest.java
/**
* 查询多条数据
*/
@Test
public void test02() throws IOException {
Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
SqlSession sqlSession = factory.openSession(false);
User user = new User();
user.setUserId("ad");
UserDao userDao = sqlSession.getMapper(UserDao.class);
List<User> userList = userDao.selectAll(user);
System.out.println(userList);
sqlSession.close();
reader.close();
}
选择标签
<where><choose><when><otherwise>相当于 if...else if... else...
<select id="selectAllByOp" resultType="com.zr.vo.User">
select * from t_user
<where>
<choose>
<when test="op=='All'">user_name = #{userName}</when>
<when test="op=='Left'">user_name like '${userName}%'</when>
<when test="op=='Right'">user_name like '%${userName}'</when>
<otherwise>user_name like '%${userName}%'</otherwise>
</choose>
</where>
</select>
test='判断语句' 作为判断条件，满足则加入标签内的语句
trim标签
mybatis的trim标签一般用于去除sql语句中多余的and关键字，逗号，或者给sql语句前拼接 “where“、“set“以及“values(“ 等前缀，或者添加“)“等后缀，可用于选择性插入、更新、删除或者条件查询等操作。
示例
<insert id="insert">
<!-- insert into t_user values (#{userId}, #{userName}, #{password}, #{sex}, #{tel}, #{status}, #{img}, #{deptId} #{createTime}, #{createUser}, #{modifyTime}, #{modifyUser})-->
insert into t_user
<trim prefix="(" suffix=")" suffixOverrides=","> <!--忽略后缀逗号 -->
<if test="userId != null">user_id,</if>
<if test="userName != null">user_name,</if>
<if test="password!= null">password,</if>
<if test="sex != null">sex,</if>
<if test="tel != null">tel,</if>
<if test="status!= null">status,</if>
<if test="img != null">img,</if>
<if test="deptId != null">dept_id,</if>
<if test="createTime!= null">create_time,</if>
<if test="createUser!= null">create_user,</if>
<if test="modifyTime!= null">modify_time,</if>
<if test="modifyUser!= null">modify_user,</if>
</trim>
values
<trim prefix="(" suffix=")" suffixOverrides=","> <!--忽略后缀逗号 -->
<if test="userId != null">#{userId},</if>
<if test="userName != null">#{userName},</if>
<if test="password!= null">#{password},</if>
<if test="sex != null">#{sex},</if>
<if test="tel != null">#{tel},</if>
<if test="status!= null">#{status},</if>
<if test="img != null">#{img},</if>
<if test="deptId != null">#{deptId},</if>
<if test="createTime!= null">#{createTime},</if>
<if test="createUser!= null">#{createUser},</if>
<if test="modifyTime!= null">#{modifyTime},</if>
<if test="modifyUser!= null">#{modifyUser},</if>
</trim>
</insert>
Powerd by YlBlog(玉龙博客)