MyBatis_4
一、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 updatePerson(Person person);
/*
查询数据
*/
List<Person> selectPerson(Map<String,Object> parameter);
/*
id删除数据
*/
void deletePersonById(int perid);
/*
name删除数据
*/
void deletePersonByNmae(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">
<!--测试parameterType属性为POJO类型-->
<insert id="insertPerson" parameterType="com.wangxing.mybatis.bean.Person">
insert into t_person values(null,#{pername},#{perage},#{peraddress});
</insert>
<!--测试parameterType属性为基本类型-->
<delete id="deletePersonById" parameterType="int">
delete from t_person where per_id=#{perid};
</delete>
<!--测试parameterType属性为String类型-->
<delete id="deletePersonByNmae" parameterType="java.lang.String">
delete from t_person where per_name=#{pername};
</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
*/
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("张三");
person.setPerage(25);
person.setPeraddress("西安");
personMapper.insertPerson(person);
sqlSession.commit();
}catch (Exception e){
e.printStackTrace();
}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",1);
parameter.put("name","张三");
parameter.put("age",25);
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();
}finally {
sqlSession.close();
}
}
/*
测试parameterType属性为基本类型
*/
public static void testdeletePersonById(){
SqlSession sqlSession = null;
try{
sqlSession =getSqlsession();
PersonMapper personMapper =sqlSession.getMapper(PersonMapper.class);
personMapper.deletePersonById(1);
sqlSession.commit();
}catch (Exception e){
e.printStackTrace();
}finally {
sqlSession.close();
}
}
public static void main(String[] args) {
//测试parameterType属性为POJO类型
testinsertPerson();
//测试parameterType属性为集合类型
//testselectPerson();
// 测试parameterType属性为基本类型
//testdeletePersonById();
}
}
二、#{}和 $ {}的用法
在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 testselectPersonByName1(){
SqlSession sqlSession = null;
try{
sqlSession =getSqlsession();
PersonMapper personMapper =sqlSession.getMapper(PersonMapper.class);
List<Person> personList=personMapper.selectPersonByName1("%张%");
for (Person person:personList){
System.out.println(person.getPerid()+"\t"+person.getPername()+"\t"+person.getPerage()+"\t"+person.getPeraddress());
}
sqlSession.commit();
}catch (Exception e){
e.printStackTrace();
}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 testselectPersonByName2(){
SqlSession sqlSession = null;
try{
sqlSession =getSqlsession();
PersonMapper personMapper =sqlSession.getMapper(PersonMapper.class);
HashMap<String,Object> parameter=new HashMap<String,Object>();
parameter.put("name","'%李%'");
List<Person> personList=personMapper.selectPersonByName2(parameter);
for (Person person:personList){
System.out.println(person.getPerid()+"\t"+person.getPername()+"\t"+person.getPerage()+"\t"+person.getPeraddress());
}
sqlSession.commit();
}catch (Exception e){
e.printStackTrace();
}finally {
sqlSession.close();
}
}
运行结果
由此可知${}就是sql语句的拼接执行，不自动实现数据类型转换，并且不会防止sql注入。
三、为什么要使用动态SQL？
如果你有使用 JDBC 或其他类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句有多么痛苦。拼接的时候要确保不能忘了必要的空格，还要注意省掉列名列表最后的逗号。
利用动态 SQL 这一特性可以彻底摆脱这种痛苦。
例如:
String insertsql="insert into t_user values(null,'"+userbean.getUsername()+
"',"+userbean.getUserage()+","+userbean.isUsersex()+
",'"+userbean.getUseraddress()+"','"+userbean.getUserday()+"');";
四、有哪些常用的动态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>
测试代码
/*
得到添加数据集合
*/
public static List<Person> getPersonList(){
List<Person> personList = new ArrayList();
Person person = new Person();
person.setPername("王五");
person.setPerage(23);
person.setPeraddress("宝鸡");
Person person1 = new Person();
person1.setPername("赵六");
person1.setPerage(24);
person1.setPeraddress("咸阳");
Person person2 = new Person();
person2.setPername("王麻子");
person2.setPerage(26);
person2.setPeraddress("安康");
Person person3 = new Person();
person3.setPername("陈七");
person3.setPerage(27);
person3.setPeraddress("榆林");
personList.add(person);
personList.add(person1);
personList.add(person2);
personList.add(person3);
return personList;
}
/**
* 测试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>
测试代码
public static void testdeletePersonForeach(){
SqlSession sqlSession=null;
try{
sqlSession=getSqlsession();
PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
List<Integer> perids = new ArrayList<Integer>();
perids.add(5);
perids.add(6);
perids.add(7);
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);
}
SQL映射文件
<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 testselectPersonIf(){
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("name","%张三%");
List<Person> personList =personMapper.selectPersonByIf(pername);
for (Person person:personList){
System.out.println(person.getPerid()+"\t"+person.getPername()+"\t"+person.getPerage()+"\t"+person.getPeraddress());
}
sqlSession.commit();
}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);
}
SQL映射文件
<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>
测试
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",25);
pername.put("address","西安");
List<Person> personList=personMapper.selectPersonByChoose(pername);
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();
}
}
运行结果
测试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);
}
SQL映射文件
<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>
测试
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",null);
pername.put("address","上海");
List<Person> personList=personMapper.selectPersonByWhere(pername);
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();
}
}
运行结果：
测试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);
}
SQL映射文件
<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>
测试
public static void updatePersonset(){
SqlSession sqlSession=null;
try{
sqlSession=getSqlsession();
PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
HashMap<String,Object> pername=new HashMap<String,Object>();
pername.put("id",2);
pername.put("name",null);
pername.put("age",33);
pername.put("address","宝鸡");
personMapper.updatePersonset(pername);
sqlSession.commit();
}catch (Exception e){
e.printStackTrace();
sqlSession.rollback();
}finally {
sqlSession.close();
}
}
运行结果：
896
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
