MyBatis学习总结 (2)

「爱情、让人受尽委屈。」 2022-12-25 04:52 211阅读 0赞

# MyBatis (2) #

MyBatis的使用第一部分，传送地址：[MyBatis的使用(1) 初学者必看 基于xml配置的MyBatis MyBatis的生命周期 ResultMap的使用 日志工厂][MyBatis_1_ _ _xml_MyBatis MyBatis_ ResultMap_]

### 文章目录 ###

*  MyBatis (2)
 *   *  一、使用注解开发
     *  二、使用注解完成删除操作
     *  三、联合查询
     *   *  1. 级联属性的方式
         *  2. 使用association标签的方式
         *  3. 使用collection标签的方式
     *  四、分步查询
     *  五、动态sql
     *  六、动态sql的其他标签
     *   *  1. foreach标签
         *  2. choose标签
         *  3. set标签

## 一、使用注解开发 ##

1.  注解使用在Mapper接口的方法上，无需再使用UserMapper.xml文件

public interface UserMapper { 
        
        @Select("select * from user limit #{startIndex}, #{pageSize}")
        List<User> getUserListById(Map<String, Integer> map);
    
    }

1.  需要在核心配置文件mybatis-config.xml中绑定接口

1.  测试

@Test
    public void test01() { 
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    
        //向map参数中赋值
        HashMap<String, Integer> map = new HashMap<String, Integer>();
        map.put("startIndex", 0);
        map.put("pageSize", 2);
    
        List<User> userListById = userMapper.getUserListById(map);
        sqlSession.close();
    }

1.  日志输出

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM0MzE5MA_size_16_color_FFFFFF_t_70]

## 二、使用注解完成删除操作 ##

1.  Mapper接口中添加注解

public interface UserMapper { 
    
        @Delete("delete from user where id = #{uid}")
        int deleteUser(@Param("uid") int id);
    
        /** * 1. 方法上的注解还有@Insert()、@Update()两种，分别完成对应的操作 * 2. 方法中的参数使用@Param注解表示给参数名起别名 * * 注意： (1)参数有多个基本类型和String类型时需要使用@Param注解 * (2)参数如果只有一个基本类型时，可以不使用@Param注解，但尽量使用 * (3)类类型不需要使用@Param注解 * */
    
    }

1.  需要在核心配置文件mybatis-config.xml中绑定接口

1.  测试

注意：

public static SqlSession getSqlSession() { 
        //可以在MyBatisUtils工具类中设置sqlSession为自动提交
        return sqlSessionFactory.openSession(true);
    }

@Test
    public void test01() { 
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        int res = userMapper.deleteUser(3);
        //已经在工具类中设置为自动提交，故此处无需提交事务
        sqlSession.close();
    }

1.  日志输出  
    ![在这里插入图片描述][20201130161756477.png]
2.  其余使用注解的操作与此类似，不再赘述

## 三、联合查询 ##

有两张表t\_key和t\_lock，对应关系如下所示：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM0MzE5MA_size_16_color_FFFFFF_t_70 1]

### 1. 级联属性的方式 ###

(1) Key类和Lock类

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM0MzE5MA_size_16_color_FFFFFF_t_70 2]

(2) KeyMapper接口中声明

public Key getKeyById(Integer id);

(3) KeyMapper.xml中编写

<select id="getKeyById" resultMap="mykey">
     		select k.id, k.`keyname`, k.`lockid`, l.`id` lid, l.`lockName` 
    		from t_key k
    		left join t_lock l on k.`lockid`=l.`id`
    		where k.`id`=#{id}
     	</select>
    
      	<resultMap id="mykey" type="com.qizegao.bean.Key" >
     		<id property="id" column="id"/>
     		<result property="keyName" column="keyname"/>
     		<result property="lock.id" column="lid"/>
     		<result property="lock.lockName" column="lockName"/>
     	</resultMap>

### 2. 使用association标签的方式 ###

KeyMapper.xml中编写 (select标签的内容与上述一致)

### 3. 使用collection标签的方式 ###

(1) Key类和Lock类

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM0MzE5MA_size_16_color_FFFFFF_t_70 3]

(2) LockMapper接口中声明

public Lock getLockById(Integer id);

(3) LockMapper.xml中声明

<select id="getLockById" resultMap="mylock">
     		select l.*,k.id kid,k.`keyname`,k.`lockid` 
     		from t_lock l 
    		left join t_key k on l.`id`=k.`lockid`
    		where l.id=#{id}
     	</select>
    
     	<resultMap id="mylock" type="com.atguigu.bean.Lock">
     		<id property="id" column="id"/>
     		<result property="lockName" column="lockName"/>
     		
     		<collection property="keys" ofType="com.atguigu.bean.Key">
     			
     			<id property="id" column="kid"/>
     			<result property="keyName" column="keyname"/>
     		</collection>
     	</resultMap>

## 四、分步查询 ##

1.  Key类和Lock类

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM0MzE5MA_size_16_color_FFFFFF_t_70 4]

1.  KeyMapper接口和LockMapper接口中编写

public Key getKeyByIdSimple(Integer id); //KeyMapper中的方法
    
    public Lock getLockByIdSimple(Integer id); //LockMapper中的方法

1.  KeyMapper.xml和LockMapper.xml中编写

<select id="getLockByIdSimple" resultType="com.atguigu.qizegao.Lock">
     		select * from t_lock where id=#{id}
     	</select>
    
     	
     	<select id="getKeyByIdSimple" resultMap="mykey02">
     		select * from t_key where id=#{id}
     	</select>
     	<resultMap id="mykey02" type="com.qizegao.bean.Key">
     		<id property="id" column="id"/>
     		<result property="keyName" column="keyname"/>
     		<association property="lock" 
     			select="com.qizegao.dao.LockDao.getLockByIdSimple"
     			column="lockid" ></association>
     	</resultMap>

## 五、动态sql ##

1.  创建一张数据库表t\_teacher

![在这里插入图片描述][2020113016225621.png]

1.  创建Teacher类

public class Teacher { 
    
    	private Integer id;
    	private String name;
    	private String course;
    	private String address;
    	private Date birth;
    	//以及其余JavaBean结构
    	
    }

1.  TeacherDao中编写

public List<Teacher> getTeacherByCondition(Teacher teacher);

1.  TeacherDao.xml中编写

<resultMap id="teacherMap" type="com.qizegao.bean.Teacher">
    		<id property="id" column="id" />
    		<result property="address" column="address" />
    		<result property="birth" column="birth_date" />
    		<result property="course" column="class_name" />
    		<result property="name" column="teacherName" />
    	</resultMap>
    	
    	<select id="getTeacherByCondition" resultMap="teacherMap">
    		
    			select * from t_teacher
    		
    		
    
    		<trim prefix="where" prefixOverrides="and">
    		
    			
    		
    			<if test="id!=null">
    				id > #{id} and
    			</if>
    
    			
    			<if test="name!=null && !name.equals("")">
    				and teacherName like #{name} 
    			</if>
    			
    			<if test="birth!=null">
    			    
    				and birth_date < #{birth} 
    			</if>
    		</trim>
    	</select>

1.  测试类中编写

手动创建一个参数中需要的teacher对象，根据需求调用对象的setter方法，sql语句会根据是 否传入对应的属性值而动态的变化

1.  注意

去掉trim标签，使用 if标签 可以自动的去除if标签中的前缀and

## 六、动态sql的其他标签 ##

### 1. foreach标签 ###

(1) TeacherDao中编写

public List<Teacher> getTeacherByIdIn(@Param("ids")List<Integer> ids);

(2) TeacherDao.xml中编写

<select id="getTeacherByIdIn" resultMap="teacherMap">
    		
    		SELECT * FROM t_teacher WHERE id IN
    		
    		
    		
    		<if test="ids.size >0">
    			<foreach collection="ids" item="id_item" separator="," open="(" close=")">
    				#{id_item} 
    			</foreach>
    		</if>
    	</select>

### 2. choose标签 ###

(1) TeacherDao中编写

public List<Teacher> getTeacherByConditionChoose(Teacher teacher);

(2) TeacherDao.xml中编写

<select id="getTeacherByConditionChoose" resultMap="teacherMap">
    		select * from t_teacher
    		
    		
    		
    		<where> 
    
    			
    			
    			<choose>
    				<when test="id!=null">
    					id=#{id}
    				</when>
    				<when test="name!=null and !name.equals("")">
    					teacherName=#{name}
    				</when>
    				<when test="birth!=null">
    					birth_date = #{birth}
    				</when>
    				<otherwise>
    					1=1 
    				</otherwise>
    			</choose>
    		</where>
    	</select>

### 3. set标签 ###

(1) TeacherDao中编写

public int updateTeacher(Teacher teacher);

(2) TeacherDao.xml中编写

<update id="updateTeacher">
    		UPDATE t_teacher
    		
    		
    		
    		<set>
    			<if test="name!=null and !name.equals("")">
    				teacherName=#{name},
    			</if>
    			<if test="address!=null and !address.equals("")">
    				address=#{address},
    			</if>
    			<if test="birth!=null">
    				birth_date=#{birth}
    			</if>
    		</set>
    		<where> 
    			id=#{id}
    		</where>
    	</update>

[MyBatis_1_ _ _xml_MyBatis MyBatis_ ResultMap_]: https://blog.csdn.net/weixin_49343190/article/details/110390362
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM0MzE5MA_size_16_color_FFFFFF_t_70]: /images/20221120/8dc88eac725648c2ac3734f62951a9e4.png
[20201130161756477.png]: /images/20221120/5ed0dceec94b406e9f24783aca34aa50.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM0MzE5MA_size_16_color_FFFFFF_t_70 1]: /images/20221120/ac916e24aba74f0598f84687f2981175.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM0MzE5MA_size_16_color_FFFFFF_t_70 2]: /images/20221120/5c82b2f403ea4d05905350fbd7922be2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM0MzE5MA_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20201130162113616.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM0MzE5MA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM0MzE5MA_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/2020113016221040.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTM0MzE5MA==,size_16,color_FFFFFF,t_70
[2020113016225621.png]: https://img-blog.csdnimg.cn/2020113016225621.png