MyBatis_4

于 2021-04-01 22:02:46 发布
阅读量112 收藏
点赞数
分类专栏： myBatis 文章标签： mybatis
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/m0_49935332/article/details/115385250
版权

1.#{}和${}的用法 

测试：

   Mybatis-config.xml 设置sql语句在控制台输出

<settings>
    <setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>

测试#{}与${}的区别

package com.wangxing.mybatis.mapper;

import java.util.List;
import java.util.Map;

import com.wangxing.mybatis.bean.Person;

public interface PersonMapper {
	List<Person> selectPersonByName1(String pername);
	List<Person> selectPersonByName2(Map<String ,Object> pername);
}

SQL映射文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.wangxing.mybatis.mapper.PersonMapper">
	<resultMap type="com.wangxing.mybatis.bean.Person" id="personMap">
		<id column="per_id" property="perid"/>
		<result column="per_name" property="pername"/>
		<result column="per_age" property="perage"/>
		<result column="per_address" property="peraddress"/>
	</resultMap>
	<select id="selectPersonByName1" parameterType="java.lang.String" resultMap="personMap">
		select * from t_person where per_name like ${pername}
	</select>
	<select id="selectPersonByName2" parameterType="java.lang.String" resultMap="personMap">
		select * from t_person where per_name like #{pername}
	</select>
</mapper>

测试代码

package com.wangxing.mybatis.test;

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

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 com.wangxing.mybatis.bean.Person;
import com.wangxing.mybatis.mapper.PersonMapper;

public class TestMain {
	/**
	 * 测试#{}与${}的区别
	 */
	public static void testSelectPersonByName(){
		SqlSession sqlSession = null;
		try {
			SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
			sqlSession = sqlSessionFactory.openSession();
			PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class);
			HashMap<String, Object> pername = new HashMap<String, Object>();
			pername.put("pername", "'%zhangsan%'");
			//EL${}表达式是通过键值对键的名称来找到对应的值它是拼接字符串的形式[statement]
			//#{}会自动给参数加上''[prepratstatement]
			//当使用${}接收数据时调用对应的方法必须传入键值对${}才能找到对应的数据
			//当使用#{}接收数据时调用对应的方法如果利用sqlSession对象调用方法必须传入键值对mapper对象根据具体方法参数传递
			//如果通过mapper对象调用具体的sql方法调用的是接口中的具体方法数据通过接口方法的参数传递
			//如果通过SqlSession对象调用sql方法则调用的是SqlSession对象自身原本的sql方法参数通过键值对传递
			//List<Person> personlist= sqlSession.selectList("selectPersonByName1", pername);
			//List<Person> personlist = personMapper.selectPersonByName1("'%zhangsan%'");错误形式
			//List<Person> personlist = personMapper.selectPersonByName2(pername);
			List<Person> personlist= sqlSession.selectList("selectPersonByName2", pername);
			if(personlist.size()>0){
				for(Person person:personlist){
					System.out.println(person.getPerid()+"\t"+person.getPername()+"\t"+person.getPerage()+"\t"+person.getPeraddress());
				}
			}else{
				System.out.println("没有数据");
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			sqlSession.close();
		}
	}
        public static void main(String[] args) {
		testSelectPersonByName();
	}
}

总结：

  在mybatis的mapper文件[sql映射文件]中，参数传递有2种方式。一种是#{}，另一种是${}，两者有较大的区别：

   #{} 实现的是sql语句的预处理，之后执行的sql中用？号代替。使用时不需要关注参数的数据类型。mybatis会自动实现数据类型转换，并且可以防止sql注入。

   ${}实现sql语句的拼接操作，不做数据类型转换，需要自行判断数据类型，不能防止sql注入。

   #{}占位符，用于参数传递。${}用于sql拼接

2.为什么要使用动态SQL？

   如果你有使用 JDBC 或其他类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句有多么痛苦。拼接的时候要确保不能忘了必要的空格，还要注意省掉列名列表最后的逗号。

   利用动态 SQL 这一特性可以彻底摆脱这种痛苦。

例如:

String insertsql="insert into t_user values(null,'"+userbean.getUsername()+
			"',"+userbean.getUserage()+","+userbean.isUsersex()+
			",'"+userbean.getUseraddress()+"','"+userbean.getUserday()+"');";

3.有哪些常用的动态SQL，它们如何使用？

  • if
  • choose (when, otherwise)
  • trim (where, set)
  • foreach

测试foreach元素，实现批量添加/批量删除

   Sql语句中的批量添加

   INSERT INTO `t_person` VALUES (6,'java',20,'西安'),(7,'javaee',21,'西安');

   Sql语句中的批量删除

   delete from t_person where per_id in(6,7);

   foreach元素

<foreach collection="list【表示需要被遍历的数据结合】" 
item="从collection对应的集合中得到的每一个数据对象【java对象】" 
separator="数据对象【java对象】的分隔符">
</foreach>

foreach元素，实现批量添加

   数据访问接口 

package com.wangxing.mybatis.mapper;


import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.wangxing.mybatis.bean.Person;

public interface PersonMapper {
	void insertPersonForeach(List<Person> personlist);
}

SQL映射文件

<insert id="insertPersonForeach" parameterType="List">
	insert into t_person values
	<foreach collection="list" item="person" separator=",">
		(null,#{person.pername},#{person.perage},#{person.peraddress})
	</foreach>
</insert>

测试代码

package com.wangxing.mybatis.test;

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

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 com.wangxing.mybatis.bean.Person;
import com.wangxing.mybatis.mapper.PersonMapper;

public class TestMain {
	/**
	 * 测试Foreach标记[批量insert]
	 */
	public static void testForeachInsert(){
		SqlSession sqlSession = null;
		try {
			SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
			sqlSession = ssf.openSession();
			PersonMapper pm = sqlSession.getMapper(PersonMapper.class);
			List<Person> personlist = new ArrayList<Person>();
			for(int i=0;i<10;i++){
				Person person = new Person();
				person.setPername("zhangsan_"+i);
				person.setPerage(20+i);
				person.setPeraddress("北京"+i);
				personlist.add(person);
			}
			//pm.insertPersonForeach(personlist);
			sqlSession.insert("insertPersonForeach", personlist);
			sqlSession.commit();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			sqlSession.close();
		}
	}
        public static void main(String[] args) {
		testForeachInsert();
	}
}

foreach元素，实现批量删除

数据访问接口

package com.wangxing.mybatis.mapper;


import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.wangxing.mybatis.bean.Person;

public interface PersonMapper {
	void deletePersonForeach(int parameter[]);
}

SQL映射文件

<delete id="deletePersonForeach" parameterType="list" >
	delete from t_person where per_id in
        <!-- collection:循环的集合/数组 -->
	<!-- item：来自集合/数组中的每一个元素对象 -->
	<!-- separator：分割符 -->
	<!-- open：开始符号 -->
	<!-- close：结束符号 -->
	<foreach collection="array" item="id" open="(" separator="," close=")">
		#{id}
	</foreach>		
</delete>

测试代码

package com.wangxing.mybatis.test;

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

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 com.wangxing.mybatis.bean.Person;
import com.wangxing.mybatis.mapper.PersonMapper;

public class TestMain {
	/**
	 * 测试Foreach标记[批量delete]
	 */
	public static void testForeachDeleter(){
		SqlSession sqlSession = null;
		try {
			SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
			sqlSession = ssf.openSession();
			PersonMapper pm = sqlSession.getMapper(PersonMapper.class);
			int parameter[] = {24,22};
			pm.deletePersonForeach(parameter);
			//sqlSession.delete("deletePersonForeach", parameter);
			sqlSession.commit();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			sqlSession.close();
		}
	}
        public static void main(String[] args) {
		testForeachDeleter();
	}
}

测试if元素

  数据访问接口

package com.wangxing.mybatis.mapper;
import com.wangxing.mybatis.bean.Person;
import java.util.List;
import java.util.Map;
public interface PersonMapper {
    List<Person> selectPersonIf(HashMap<String,Object> map);
}

SQL映射文件

<select id="selectPersonIf" parameterType="map" resultMap="personMap">
	select * from t_person where 1=1
	<if test="pername != null and pername.length()>0">
		and per_name like #{pername}
	</if>
	<if test="perage != null and perage>0">
		and per_age = #{perage}
	</if>
	<if test="peraddress!=null and peraddress.length()>0">
		and per_address = #{peraddress}
	</if>
</select>

测试代码

package com.wangxing.mybatis.test;

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

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 com.wangxing.mybatis.bean.Person;
import com.wangxing.mybatis.mapper.PersonMapper;

public class TestMain {
	/**
	 * 测试if标记[拼接查询]
	 */
	public static void testIfSelect(){
		SqlSession sqlSession = null;
		try {
			SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
			sqlSession = ssf.openSession();
			PersonMapper pm = sqlSession.getMapper(PersonMapper.class);
			HashMap<String,Object> map = new HashMap<String,Object>();
			map.put("pername", "%zhangsan%");
			map.put("perage", 23);
			map.put("peraddress", null);
			List<Person> personlist = sqlSession.selectList("selectPersonIf", map);
			//List<Person> personlist = pm.selectPersonIf(map);
			if(personlist!=null){
				for(Person person:personlist){
					System.out.println(person.getPerid()+"\t"+person.getPername()+"\t"+person.getPerage()+"\t"+person.getPeraddress());
				}
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			sqlSession.close();
		}
	}
        public static void main(String[] args) {
		testIfSelect();
	}
}

测试choose (when, otherwise)【每次只匹配一个条件】

   需求：查询用户信息，如果输入了用户名，根据用户名进行模糊查找，返回

                如果输入了年龄，根据年龄进行匹配查找，返回

                如果输入了地址，根据地址进行模糊查找，返回

   如果查询条件都为空，那么就查询所有。

   有点类似于Switch语句

数据访问接口

package com.wangxing.mybatis.mapper;

import java.util.List;
import java.util.Map;

import com.wangxing.mybatis.bean.Person;

public interface PersonMapper {
	List<Person> selectPersonchoose(Map<String, Object> map);
}

SQL映射文件

<select id="selectPersonchoose" parameterType="map" resultMap="personMap">
	select * from t_person where 1=1
	<choose>
		<when test="name!=null and name.length()>0">
			and per_name = #{name}
		</when>
		<when test="age!=null and age>0">
			and per_age = #{age}
		</when>
		<when test="address!=null and address.length()>0">
			and per_address = #{address}
		</when>
		<otherwise></otherwise>
	</choose>
</select>

测试代码

package com.wangxing.mybatis.test;

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

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 com.wangxing.mybatis.bean.Person;
import com.wangxing.mybatis.mapper.PersonMapper;

public class TestMain {
	/**
	 * 测试choose元素，实现拼接查询
	 */
	public static void testChooseSlect(){
		SqlSession sqlSession = null;
		try {
			SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
			sqlSession = ssf.openSession();
			PersonMapper pm = sqlSession.getMapper(PersonMapper.class);
			HashMap<String, Object> map = new HashMap<String,Object>();
			map.put("name", "zhangsan_0");
			map.put("age", 23);
			map.put("address", "北京");
			List<Person> personlist = pm.selectPersonchoose(map);
			if(personlist!=null){
				for(Person person:personlist){
					System.out.println(person.getPerid()+"\t"+person.getPername()+"\t"+person.getPerage()+"\t"+person.getPeraddress());
				}
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			sqlSession.close();
		}
	}
        public static void main(String[] args) {
		testChooseSlect();
	}
}

测试where元素

package com.wangxing.mybatis.mapper;
import com.wangxing.mybatis.bean.Person;
import java.util.List;
import java.util.Map;
public interface PersonMapper {
       List<Person> selectPersonByWhere(Map<String,Object> pername);
}
<resultMap id="personMap" type="com.wangxing.mybatis.bean.Person">
    <id column="per_id" property="perid"></id>
    <result column="per_name" property="pername"></result>
    <result column="per_age" property="perage"></result>
    <result column="per_address" property="peraddress"></result>
</resultMap>
<select id="selectPersonByWhere" parameterType="hashMap" resultMap="personMap">
    select * from t_person
    <where>
        <if test="name !=null and name !=''">
            and  per_name like #{name}
        </if>
        <if test="age !=0 and age !=null">
            and  per_age=#{age}
        </if>
        <if test="address !=null and address !=''">
            and  per_address like #{address}
        </if>
    </where>
</select>
/**
 * 测试where元素
 */
public static  void selectPersonByWhere(){
    SqlSession  sqlSession=null;
    try{
        sqlSession=getSqlSession();
        PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
        HashMap<String,Object> pername=new HashMap<String,Object>();
        pername.put("name",null);
        pername.put("age",23);
        pername.put("address","xian");
        List<Person> personList=personMapper.selectPersonByWhere(pername);
        sqlSession.commit();
        for(Person person:personList){
            System.out.println(person.getPerid()+"\t"+person.getPername());
        }
    }catch (Exception e){
        e.printStackTrace();
        sqlSession.rollback();
    }finally {
        sqlSession.close();
    }
}

测试set元素

package com.wangxing.mybatis.mapper;

import com.wangxing.mybatis.bean.Person;

import java.util.List;
import java.util.Map;

public interface PersonMapper {
    void updatePerson(Map<String,Object> pername);
}
<update id="updatePerson" parameterType="hashMap">
    update  t_person
    <set>
        <if test="name !=null and name !=''">
            per_name = #{name},
        </if>
        <if test="age !=null and age !=0">
            per_age = #{age},
        </if>
        <if test="address !=null and address !=''">
            per_address = #{address},
        </if>
    </set>
    <where>per_id = #{id}</where>
</update>
/**
 * 测试set元素
 */
public static  void updatePerson(){
    SqlSession  sqlSession=null;
    try{
        sqlSession=getSqlSession();
        PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
        HashMap<String,Object> pername=new HashMap<String,Object>();
        pername.put("id",12);
        pername.put("name",null);
        pername.put("age",33);
        pername.put("address","西安");
        personMapper.updatePerson(pername);
        sqlSession.commit();
    }catch (Exception e){
        e.printStackTrace();
        sqlSession.rollback();
    }finally {
        sqlSession.close();
    }
}
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值