MyBatis_4

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.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter;
import com.wangxing.mybatis.Mapper.PersonMapper;
import com.wangxing.mybatis.bean.Person;
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对象
     */
    public static SqlSession getSqlSession() throws Exception {
        SqlSession sqlSession = null;
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("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(880);
            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","zhangsan");
            parameter.put("age",66);
            List<Person>personlist=personMapper.selectPerson(parameter);
            sqlSession.commit();
            for(Person person:personlist) {
                System.out.println(person.getPerid() + "\t" + person.getPername() + "\t" + person.getPerage() + "\t" + person.getPeraddress());
            }
        }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.deletePersonByAge(880);
           personMapper.deletePersonByName("张三");
           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>

测试代码

/**
 * 测试sql语句中通过${}方式接收参数值
 */
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元素，实现批量添加

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 {
        void deletePersonForeach(List<Integer> perids);
}

SQL映射文件

<delete id="deletePersonForeach" parameterType="java.util.List">
    delete from t_person where per_id in
    <foreach collection="list" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</delete>

测试代码

/**
 * 测试foreach元素，实现批量删除
 */
public static  void deletePersonForeach(){
    SqlSession  sqlSession=null;
    try{
        sqlSession=getSqlSession();
        PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
        List<Integer> perids=new ArrayList<Integer>();
        perids.add(8);
        perids.add(9);
        perids.add(10);
        perids.add(11);
        personMapper.deletePersonForeach(perids);
        sqlSession.commit();
    }catch (Exception e){
        e.printStackTrace();
        sqlSession.rollback();
    }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();
    }
}