2021-04-02

MyBatis

1.Sql映射文件中的parameterType属性传入参数

【insert/update/delete/select元素的输入参数】

parameterType属性传入参数

1. String+基本类型  【String---java.lang.String,int/boolean,java.lang.Integer】
2. POJO类型【对象型】  com.wangxing.bean.Student
3. 集合类型【list,hashMap】

当数据访问接口的方法只有一个参数的时候【String+基本类型/POJO类型】，当有多个参数的时候通常都是使用【集合类型】

例如：

package com.wangxing.mybatis.mapper;
import com.wangxing.mybatis.bean.Person;
import java.util.List;
import java.util.Map;
public interface PersonMapper {
    void insertPerson(Person person);
    void deletePerson(int perid);
    void deletePersonByName(String pername);
    void deletePersonByAge(int perage);
    List<Person> selectPerson(Map<String,Object> parameter);
}

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">
    <!--测试parameterType属性为POJO类型-->
  <insert id="insertPerson" parameterType="com.wangxing.mybatis.bean.Person">
      insert into t_person values(null,#{pername},#{perage},#{peraddress});
  </insert>
    <!--测试parameterType属性为基本类型-->
  <delete id="deletePerson"  parameterType="int">
      delete from  t_person where per_id = #{perid};
  </delete>
    <!--测试parameterType属性为String类型-->
  <delete id="deletePersonByName" parameterType="java.lang.String">
       delete from  t_person where per_name = #{pername};
  </delete>
    <!--测试parameterType属性为基本类型的封装类类型-->
  <delete id="deletePersonByAge" parameterType="java.lang.Integer">
       delete from  t_person where per_age = #{perage};
  </delete>
    <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>
    <!--测试parameterType属性为集合类型-->
    <select id="selectPerson" parameterType="hashMap" resultMap="personMap">
        select * from t_person where per_id=#{id} or per_name=#{name} or per_age=#{age};
    </select>
</mapper>

测试类

package com.wangxing.mybatis.test;
import com.wangxing.mybatis.bean.Person;
import com.wangxing.mybatis.mapper.PersonMapper;
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 java.util.HashMap;
import java.util.List;
public class TestMain {
    /**
     * 得到SqlSession
     * @return
     */
    public static SqlSession  getSqlSession()throws  Exception{
        SqlSession sqlSession=null;
        SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis-config.xml"));
        return sqlSessionFactory.openSession();
    }
    /**
     * 测试parameterType属性为POJO类型
     */
    public static  void testInsertPerson(){
        SqlSession  sqlSession=null;
        try{
            sqlSession=getSqlSession();
            PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
            Person person=new Person();
            person.setPername("java");
            person.setPerage(20);
            person.setPeraddress("西安");
            personMapper.insertPerson(person);
            sqlSession.commit();
        }catch (Exception e){
            e.printStackTrace();
            sqlSession.rollback();
        }finally {
            sqlSession.close();
        }
    }
    /**
     * 测试parameterType属性为集合类型
     */
    public static  void testSelectPerson(){
        SqlSession  sqlSession=null;
        try{
            sqlSession=getSqlSession();
            PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
            HashMap<String,Object> parameter=new HashMap<String,Object>();
            parameter.put("id",3);
            parameter.put("name","wangwu");
            parameter.put("age",20);
            List<Person> personList=personMapper.selectPerson(parameter);
            sqlSession.commit();
            for(Person person:personList){
                System.out.println(person.getPerid()+"\t"+person.getPername());
            }
        }catch (Exception e){
            e.printStackTrace();
            sqlSession.rollback();
        }finally {
            sqlSession.close();
        }
    }
    /**
     * 测试parameterType属性为String+基本类型
     */
    public static  void testDeletePerson(){
        SqlSession  sqlSession=null;
        try{
            sqlSession=getSqlSession();
            PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
            //personMapper.deletePerson(5);
            //personMapper.deletePersonByName("wangwu");
            personMapper.deletePersonByAge(23);
            sqlSession.commit();
        }catch (Exception e){
            e.printStackTrace();
            sqlSession.rollback();
        }finally {
            sqlSession.close();
        }
    }
    public static void main(String[] args) {
        //testInsertPerson();
        //testSelectPerson();
        testDeletePerson();
    }
}

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

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

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

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

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

测试：

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

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

测试#{}的使用

package com.wangxing.mybatis.mapper;
import com.wangxing.mybatis.bean.Person;
import java.util.List;
import java.util.Map;
public interface PersonMapper {
     List<Person> selectPersonByName1(String 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 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="selectPersonByName1" parameterType="java.lang.String" resultMap="personMap">
        select * from t_person where per_name like  #{pername}
    </select>
 </mapper>

测试代码

public static  void selectPersonByName1(){
    SqlSession  sqlSession=null;
    try{
        sqlSession=getSqlSession();
        PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
        List<Person> personList=personMapper.selectPersonByName1("%j%");
        sqlSession.commit();
        for(Person person:personList){
            System.out.println(person.getPerid()+"\t"+person.getPername());
        }
    }catch (Exception e){
        e.printStackTrace();
        sqlSession.rollback();
    }finally {
        sqlSession.close();
    }
}

运行结果:

由此可知#{}就是sql语句执行的预处理，通过使用占位符向里传递参数的形式执行sql语句，自动实现数据类型转换，并且可以防止sql注入。

测试${}的使用

package com.wangxing.mybatis.mapper;
import com.wangxing.mybatis.bean.Person;
import java.util.List;
import java.util.Map;
public interface PersonMapper {
      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 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="selectPersonByName2" parameterType="hashMap" resultMap="personMap">
        select * from t_person where per_name like  ${name};
    </select>
</mapper>

测试代码

public static  void selectPersonByName2(){
    SqlSession  sqlSession=null;
    try{
        sqlSession=getSqlSession();
        PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
        HashMap<String,Object> pername=new HashMap<String,Object>();
        pername.put("name","'%j%'");
        List<Person> personList=personMapper.selectPersonByName2(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();
    }
}

运行结果

由此可知${}就是sql语句的拼接执行，不自动实现数据类型转换，并且不会防止sql注入。

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

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

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

例如:

String insertsql="insert into t_user values(null,'"+userbean.getUsername()+

"',"+userbean.getUserage()+","+userbean.isUsersex()+

",'"+userbean.getUseraddress()+"','"+userbean.getUserday()+"');";

5.有哪些常用的动态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 com.wangxing.mybatis.bean.Person;
import java.util.List;
import java.util.Map;
public interface PersonMapper {
    void insertPersonForeach(List<Person> personList);
}

SQL映射文件

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

测试代码

/**
 * 测试foreach元素，实现批量添加
 */
public static  void insertPersonForeach(){
    SqlSession  sqlSession=null;
    try{
        sqlSession=getSqlSession();
        PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
        personMapper.insertPersonForeach(getPersonList());
        sqlSession.commit();
    }catch (Exception e){
        e.printStackTrace();
        sqlSession.rollback();
    }finally {
        sqlSession.close();
    }
}

测试结果

 

foreach元素，实现批量删除

数据访问接口

package com.wangxing.mybatis.mapper;
import com.wangxing.mybatis.bean.Person;
import java.util.List;
import java.util.Map;
public interface PersonMapper {
         /**
     * 测试通过foreach元素，实现批量删除
     * @param personList
     */
    void deleteList(int ids[]);
}

SQL映射文件

    /**
	 * 测试批量删除
	 */
	public static void testDeleteList(){
		SqlSession  sqlSession=null;
		try{
			//通过SqlSessionFactoryBuilder类创建出SqlSessionFactory对象
		    SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
		    sqlSession=sqlSessionFactory.openSession();
		    int parameter[]={2,4,6};
		    sqlSession.delete("com.wangxing.mybatistest.mapper.PersonMapper.deleteList", parameter);
		    //提交sqlsession
            sqlSession.commit();
	    }catch (Exception e) {
	    	e.printStackTrace();
        }finally{
            sqlSession.close();
        }
    }

运行结果

测试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> selectPersonByIf(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="selectPersonByIf" parameterType="hashMap" resultMap="personMap">
    select * from t_person where 1=1
    <if test="name !=null and name !=''">
        and  per_name like #{name}
    </if>
</select>

/**
 * 测试if元素，判断是否有用户名，如果有就根据用户名模糊查询，如果没有就查询所有
 */
public static  void selectPersonByIf(){
    SqlSession  sqlSession=null;
    try{
        sqlSession=getSqlSession();
        PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
        HashMap<String,Object> pername=new HashMap<String,Object>();
        pername.put("name",null);
        List<Person> personList=personMapper.selectPersonByIf(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();
    }
}

如果有就根据用户名模糊查询

如果没有就查询所有

测试choose (when, otherwise)

【每次只匹配一个条件】

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

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

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

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

  有点类似于Switch语句

package com.wangxing.mybatis.mapper;
import com.wangxing.mybatis.bean.Person;
import java.util.List;
import java.util.Map;
public interface PersonMapper {
     List<Person> selectPersonByChoose(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="selectPersonByChoose" parameterType="hashMap" resultMap="personMap">
    select * from t_person where 1=1
    <choose>
        <when test="name !=null and name !=''">and  per_name like #{name}</when>
        <when test="age !=0 and age !=null">and  per_age=#{age}</when>
        <when test="address !=null and address !=''">and  per_address like #{address}</when>
        <otherwise></otherwise>
    </choose>
</select>

/**
 * 测试choose (when, otherwise)元素
 */
public static  void selectPersonByChoose(){
    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",24);
        pername.put("address","xian");
        List<Person> personList=personMapper.selectPersonByChoose(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();
    }
}

运行结果

 

测试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();
    }
}