MyBatis中的动态SQL
注意：这里是在《一对一关系实现员工—＞部门的查询》的基础进行编写代码
《一对一关系实现员工—＞部门的查询》
参考代码：
EmpMapper:
package com.yzh7.mapper;
import com.yzh7.entity.Emp;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
public interface EmpMapper {
//查询所有员工，并且每个员工关联一个部门对象
List<Emp> listAll();
//条件查询1
List<Emp> list1(@Param("empName") String name,
@Param("phone") String phone);
//条件查询2
List<Emp> list2(Map map);
//条件查询3
List<Emp> list3(Map map);
//条件查询4
List<Emp> list4(Map map);
//根据编号查询数据
List<Emp> listByIds(List<Integer> ids);
//分支条件查询
List<Emp> listByChoose(Emp emp);
//针对所有字段进行修改
int update1(Emp emp);
//根据传入的数据是否为空，不为空的才修改
int update2(Emp emp);
//批量插入
int insertBatch(List<Emp> empList);
//插入并接收返回的自增值
int insertAndGetAutoVal(Emp emp);
}
EmpMapper.xml:
<?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.yzh7.mapper.EmpMapper">
<!--定义公共使用的sql代码块-->
<sql id="commonCols">
emp_id,emp_name,sex,phone
</sql>
<select id="list3" resultType="emp">
select
<include refid="commonCols"></include>
from t_emp
</select>
<resultMap id="empMap" type="emp" autoMapping="true">
<id column="emp_id" property="empId"/>
<association property="dept" javaType="dept" autoMapping="true">
</association>
</resultMap>
<sql id="commonQuery">
select e.emp_id ,e.emp_name ,e.sex,e.phone,
d.dept_id,d.dept_name,d.description
from t_emp e
join t_dept d
on e.dept_id = d.dept_id
</sql>
<select id="list4" resultMap="empMap">
<include refid="commonQuery"></include>
<where>
<if test="empName!=null and empName!=''">
and emp_name like concat('%',#{empName},'%')
</if>
</where>
</select>
<!--
useGeneratedKeys:使用插入自增
keyProperty:生成的自增值，存储到传入对象的哪个属性上
-->
<insert id="insertAndGetAutoVal" useGeneratedKeys="true" keyProperty="empId">
insert into t_emp
(emp_name,sex,phone)
values
(#{empName},#{sex},#{phone})
</insert>
<!--分支条件查询-->
<select id="listByChoose" resultType="emp">
select * from t_emp
<where>
<choose>
<when test="empName!=null and empName!=''">
and emp_name = #{empName}
</when>
<when test="sex!=null and sex!=''">
and sex = #{sex}
</when>
<otherwise>
and emp_id=1
</otherwise>
</choose>
</where>
</select>
<!--批量插入-->
<insert id="insertBatch">
insert into t_emp
(emp_name,sex,phone)
VALUES
<foreach collection="list" item="e" separator=",">
(#{e.empName},#{e.sex},#{e.phone})
</foreach>
</insert>
<!--根据编号批量查询-->
<select id="listByIds" resultType="emp">
select * from t_emp
where emp_id in
<!--foreach循环拼接sql语句
collection:指定要循环的目标数据的类型,数组(array) list集合(list)
item:存储遍历的某个数据对象
open:拼接的字符串的前缀
close:拼接的字符串的后缀
separator:拼接内容的分隔符
-->
<foreach collection="list"
item="data"
open="("
close=")"
separator=",">
#{data}
</foreach>
</select>
<update id="update2">
update t_emp
<!--
set:1.自动添加set标签 2.删除最后的逗号
-->
<set>
<if test="empName!=null and empName!=''">
emp_name = #{empName},
</if>
<if test="sex!=null and sex!=''">
sex = #{sex},
</if>
<if test="phone!=null and phone!=''">
phone = #{phone},
</if>
<if test="dept.deptId!=null">
dept_id = #{dept.deptId},
</if>
</set>
where emp_id = #{empId}
</update>
</mapper>
demo6:
package com.yzh7.test;
import com.yzh7.entity.Dept;
import com.yzh7.entity.Emp;
import com.yzh7.mapper.EmpMapper;
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 org.junit.After;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class Demo6 {
//条件查询
@Test
public void test(){
EmpMapper empMapper = session.getMapper(EmpMapper.class);
//构建要修改的员工对象
Emp emp = new Emp();
emp.setEmpId(1);
emp.setEmpName("张三丰");
Dept dept = new Dept();
dept.setDeptId(2);
emp.setDept(dept);
int count = empMapper.update1(emp);
System.out.println("修改的记录数:"+count);
}
@Test
public void test2(){
EmpMapper empMapper = session.getMapper(EmpMapper.class);
//构建要修改的员工对象
Emp emp = new Emp();
emp.setEmpId(2);
emp.setEmpName("李四西");
Dept dept = new Dept();
dept.setDeptId(3);
emp.setDept(dept);
//根据数据本身是否为空，进行修改
int count = empMapper.update2(emp);
System.out.println("修改的记录数:"+count);
}
//会话工厂
private static SqlSessionFactory factory;
//会话
private SqlSession session;
@BeforeClass
public static void befCls() throws IOException {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
factory = new SqlSessionFactoryBuilder().build(is);
}
@Before
public void bf(){
session = factory.openSession();
}
@After
public void af(){
//提交事务
session.commit();
//关闭会话
session.close();
}
}
demo7:
package com.yzh7.test;
import com.yzh7.entity.Dept;
import com.yzh7.entity.Emp;
import com.yzh7.mapper.EmpMapper;
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 org.junit.After;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
public class Demo7 {
//条件查询
@Test
public void test(){
EmpMapper empMapper = session.getMapper(EmpMapper.class);
List<Integer> ids = new ArrayList<>();
ids.add(1);
ids.add(3);
ids.add(4);
List<Emp> empList = empMapper.listByIds(ids);
System.out.println(empList);
}
@Test
public void test2(){
EmpMapper empMapper = session.getMapper(EmpMapper.class);
Emp e1 = new Emp();
e1.setEmpName("aa1");
e1.setSex("男");
e1.setPhone("1212");
Emp e2 = new Emp();
e2.setEmpName("aa2");
e2.setSex("男");
e2.setPhone("1212");
Emp e3 = new Emp();
e3.setEmpName("aa2");
e3.setSex("男");
e3.setPhone("1212");
List<Emp> empList = new ArrayList<>();
empList.add(e1);
empList.add(e2);
empList.add(e3);
int count = empMapper.insertBatch(empList);
System.out.println("记录数:"+count);
}
//条件查询
@Test
public void test3(){
Emp emp = new Emp();
//emp.setEmpName("张三丰");
//emp.setSex("男");
EmpMapper empMapper = session.getMapper(EmpMapper.class);
List<Emp> empList = empMapper.listByChoose(emp);
System.out.println(empList);
}
//条件查询
@Test
public void test4(){
Emp emp = new Emp();
emp.setEmpName("武松");
emp.setSex("男");
emp.setPhone("12312");
EmpMapper empMapper = session.getMapper(EmpMapper.class);
int count = empMapper.insertAndGetAutoVal(emp);
System.out.println("插入的记录数:"+count);
System.out.println("插入之后，返回的自增值:"+emp);
}
//会话工厂
private static SqlSessionFactory factory;
//会话
private SqlSession session;
@BeforeClass
public static void befCls() throws IOException {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
factory = new SqlSessionFactoryBuilder().build(is);
}
@Before
public void bf(){
session = factory.openSession();
}
@After
public void af(){
//提交事务
session.commit();
//关闭会话
session.close();
}
}
demo8:
package com.yzh7.test;
import com.yzh7.entity.Emp;
import com.yzh7.mapper.EmpMapper;
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 org.junit.After;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class Demo8 {
//条件查询
@Test
public void test(){
EmpMapper empMapper = session.getMapper(EmpMapper.class);
Map map = new HashMap();
map.put("empName","李");
List<Emp> empList = empMapper.list4(map);
System.out.println(empList);
}
//会话工厂
private static SqlSessionFactory factory;
//会话
private SqlSession session;
@BeforeClass
public static void befCls() throws IOException {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
factory = new SqlSessionFactoryBuilder().build(is);
}
@Before
public void bf(){
session = factory.openSession();
}
@After
public void af(){
//提交事务
session.commit();
//关闭会话
session.close();
}
}
知识点：
3.1 if:
3.2 where:
3.3 trim, set:
3.4 foreach:
3.5 choose(when,otherwise):
3.6 返回自动增长的主键
3.7 sql标签定义通用sql片段
案例演练：（员工）
（仅展示主要添加代码，其他的mybatis配置等与上篇保持一致）
1.条件查询：if where trim
代码整体布局：
代码如下：
EmpMapper:
package com.yzh7.mapper;
import com.yzh7.entity.Emp;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
public interface EmpMapper {
//查询所有员工，并且每个员工关联一个部门对象
List<Emp> listAll();
//条件查询1(多参查询)
List<Emp> test1(@Param("empName") String empName,
@Param("phone") String phone);
List<Emp> test11(@Param("empName") String empName,
@Param("phone") String phone);
//条件查询2（对象查询）
List<Emp> test2(Map map);
}
EmpMapper.xml:
<?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.yzh7.mapper.EmpMapper">
<resultMap id="empMap" type="emp">
<!-- emp_id,emp_name,sex,phone,dept_id-->
<!--id:表示表中的主键字段 column:查询表的列名 property:表示列名要映射到的实体属性-->
<id column="emp_id" property="empId"/>
<!--result:定义普通列和属性的映射关系-->
<result column="emp_name" property="empName"/>
<result column="sex" property="sex"/>
<result column="phone" property="phone"/>
<result column="dept_id" property="deptId"/>
<!-- dept_id,dept_name,description-->
<!--通过association标签(1对1)，设置员工关联的部门对象-->
<association property="dept" javaType="Dept">
<id column="dept_id" property="deptId"/>
<result column="dept_name" property="deptName"/>
<result column="description" property="description"/>
</association>
</resultMap>
<!--
resultType:只用于单表查询，并且查询结果列名和属性可以直接对应的情况。用于简单查询映射。
resultMap:用于多表查询，或者列名和属性映射关系负责的情况。用于复杂查询映射。
需要通过resultMap节点，配置结果的映射关系
-->
<select id="listAll" resultMap="empMap">
select e.emp_id,e.emp_name,e.sex,e.phone,
d.dept_id,d.dept_name,d.description
from t_emp e
join t_dept d
on e.dept_id=d.dept_id
</select>
<select id="test1" resultType="emp">
select e.emp_id,e.emp_name,e.sex,e.phone
from t_emp e
<where>
<if test="empName!=null and empName!='' ">
and emp_name like concat('%',#{empName},'%')
</if>
<if test="phone!=null and phone!='' ">
and phone like concat('%',#{phone},'%')
</if>
</where>
</select>
<select id="test11" resultType="emp">
select e.emp_id,e.emp_name,e.sex,e.phone
from t_emp e
<trim prefix="where" prefixOverrides="and | or">
<if test="empName!=null and empName!='' ">
and emp_name like concat('%',#{empName},'%')
</if>
<if test="phone!=null and phone!='' ">
and phone like concat('%',#{phone},'%')
</if>
</trim>
</select>
<select id="test2" resultType="emp">
select e.emp_id ,e.emp_name ,e.sex,e.phone
from t_emp e
<where>
<if test="startId!=null">
and emp_id > #{startId}
</if>
<if test="endId!=null">
<![CDATA[
and emp_id < #{endId}
]]>
</if>
<if test="empName!=null and empName!=''">
and emp_name like concat('%',#{empName},'%')
</if>
</where>
</select>
</mapper>
demo5:
package com.yzh7.test;
import com.yzh7.entity.Emp;
import com.yzh7.mapper.EmpMapper;
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 org.junit.After;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class Demo5 {
//条件查询
@Test
public void testTest1(){
EmpMapper empMapper=session.getMapper(EmpMapper.class);
//List<Emp> empList=empMapper.test1(null,null);
List<Emp> empList=empMapper.test1("张","123");
System.out.println(empList);
}
@Test
public void testTest11(){
EmpMapper empMapper=session.getMapper(EmpMapper.class);
//List<Emp> empList=empMapper.test1(null,null);
List<Emp> empList=empMapper.test11("张","123");
System.out.println(empList);
}
@Test
public void test2(){
Map map = new HashMap();
map.put("startId",1);
map.put("endId",3);
map.put("empName","四");
EmpMapper empMapper = session.getMapper(EmpMapper.class);
List<Emp> empList = empMapper.test2(map);
System.out.println(empList);
}
private static SqlSessionFactory factory; //静态
private SqlSession session;
@BeforeClass
public static void befCla() throws IOException {
String resource = "mybatis_config.xml"; //mybatis_config.xml
InputStream inputStream = Resources.getResourceAsStream(resource);
factory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Before
public void bf(){
session=factory.openSession();
}
@After
public void af(){
session.commit();
session.close();
}
}
结果展示：
testTest1():
testTest11():
testTest2():
2.修改：set
代码整体布局：
代码如下：
EmpMapper:
package com.yzh7.mapper;
import com.yzh7.entity.Emp;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
public interface EmpMapper {
//查询所有员工，并且每个员工关联一个部门对象
List<Emp> listAll();
//条件查询1(多参查询)
List<Emp> test1(@Param("empName") String empName,
@Param("phone") String phone);
List<Emp> test11(@Param("empName") String empName,
@Param("phone") String phone);
//条件查询2（对象查询）
List<Emp> test2(Map map);
//针对所有字段进行修改
int update1(Emp emp);
//根据传入的数据是否为空，不为空的才修改
int update2(Emp emp);
}
EmpMapper.xml:
<?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.yzh7.mapper.EmpMapper">
<resultMap id="empMap" type="emp">
<!-- emp_id,emp_name,sex,phone,dept_id-->
<!--id:表示表中的主键字段 column:查询表的列名 property:表示列名要映射到的实体属性-->
<id column="emp_id" property="empId"/>
<!--result:定义普通列和属性的映射关系-->
<result column="emp_name" property="empName"/>
<result column="sex" property="sex"/>
<result column="phone" property="phone"/>
<result column="dept_id" property="deptId"/>
<!-- dept_id,dept_name,description-->
<!--通过association标签(1对1)，设置员工关联的部门对象-->
<association property="dept" javaType="Dept">
<id column="dept_id" property="deptId"/>
<result column="dept_name" property="deptName"/>
<result column="description" property="description"/>
</association>
</resultMap>
<!--
resultType:只用于单表查询，并且查询结果列名和属性可以直接对应的情况。用于简单查询映射。
resultMap:用于多表查询，或者列名和属性映射关系负责的情况。用于复杂查询映射。
需要通过resultMap节点，配置结果的映射关系
-->
<select id="listAll" resultMap="empMap">
select e.emp_id,e.emp_name,e.sex,e.phone,
d.dept_id,d.dept_name,d.description
from t_emp e
join t_dept d
on e.dept_id=d.dept_id
</select>
<select id="test1" resultType="emp">
select e.emp_id,e.emp_name,e.sex,e.phone
from t_emp e
<where>
<if test="empName!=null and empName!='' ">
and emp_name like concat('%',#{empName},'%')
</if>
<if test="phone!=null and phone!='' ">
and phone like concat('%',#{phone},'%')
</if>
</where>
</select>
<select id="test11" resultType="emp">
select e.emp_id,e.emp_name,e.sex,e.phone
from t_emp e
<trim prefix="where" prefixOverrides="and | or">
<if test="empName!=null and empName!='' ">
and emp_name like concat('%',#{empName},'%')
</if>
<if test="phone!=null and phone!='' ">
and phone like concat('%',#{phone},'%')
</if>
</trim>
</select>
<select id="test2" resultType="emp">
select e.emp_id ,e.emp_name ,e.sex,e.phone
from t_emp e
<where>
<if test="startId!=null">
and emp_id > #{startId}
</if>
<if test="endId!=null">
<![CDATA[
and emp_id < #{endId}
]]>
</if>
<if test="empName!=null and empName!=''">
and emp_name like concat('%',#{empName},'%')
</if>
</where>
</select>
<!-- emp_id,emp_name,sex,phone,dept_id-->
<update id="update1">
update t_emp
set emp_name=#{empName},
sex=#{sex},
phone=#{phone},
dept_id=#{dept.deptId}
where emp_id=#{empId}
</update>
<!-- 注意：嵌套对象 dept.dept_id 首先访问Emp实体对象里的dept,然后访问dept对象里的deptId-->
<update id="update2">
update t_emp
<set>
<if test="empName!=null and empName!=''">
emp_name=#{empName},
</if>
<if test="sex!=null and sex!=''">
sex=#{sex},
</if>
<if test="phone!=null and phone!=''">
phone=#{phone},
</if>
<if test="dept.deptId!=null and dept.deptId!=''">
dept_id=#{dept.deptId},
</if>
</set>
where emp_id=#{empId}
</update>
</mapper>
demo6:
package com.yzh7.test;
import com.yzh7.entity.Dept;
import com.yzh7.entity.Emp;
import com.yzh7.mapper.EmpMapper;
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 org.junit.After;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;
public class Demo6 {
@Test
public void testUpdate1(){
EmpMapper empMapper=session.getMapper(EmpMapper.class);
//int update1(Emp emp); //构建要修改的员工对象
Emp emp=new Emp();
emp.setEmpId(1);
emp.setEmpName("张三疯"); //如果表中的列不赋值，将会赋空值
Dept dept=new Dept();
dept.setDeptId(2);
emp.setDept(dept); //注意
int count=empMapper.update1(emp);
System.out.println("修改的记录数："+count);
}
@Test
public void testUpdate2(){
EmpMapper empMapper=session.getMapper(EmpMapper.class);
//int update1(Emp emp); //构建要修改的员工对象
Emp emp=new Emp();
emp.setEmpId(2);
emp.setEmpName("李四皮"); //如果表中的列不赋值，将会赋空值
Dept dept=new Dept();
dept.setDeptId(3);
emp.setDept(dept); //注意
int count=empMapper.update2(emp);
System.out.println("修改的记录数："+count);
}
private static SqlSessionFactory factory; //静态
private SqlSession session;
@BeforeClass
public static void befCla() throws IOException {
String resource = "mybatis_config.xml"; //mybatis_config.xml
InputStream inputStream = Resources.getResourceAsStream(resource);
factory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Before
public void bf(){
session=factory.openSession();
}
@After
public void af(){
session.commit();
session.close();
}
}
结果展示：
testUpdate1():
比较生硬霸道的修改方式：
修改后的数据表：
testUpdate2():
使用set、if标签之后：
修改后的数据表：
3.循环拼接和批量插入：foreach
思路：
代码整体布局：
代码如下：
EmpMapper:
package com.yzh7.mapper;
import com.yzh7.entity.Emp;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
public interface EmpMapper {
//查询所有员工，并且每个员工关联一个部门对象
List<Emp> listAll();
//条件查询1(多参查询)
List<Emp> test1(@Param("empName") String empName,
@Param("phone") String phone);
List<Emp> test11(@Param("empName") String empName,
@Param("phone") String phone);
//条件查询2（对象查询）
List<Emp> test2(Map map);
//针对所有字段进行修改
int update1(Emp emp);
//根据传入的数据是否为空，不为空的才修改
int update2(Emp emp);
//根据编号查询数据
List<Emp> testByIds(List<Integer> ids);
//批量插入
int insertBatch(List<Emp> empList);
}
EmpMapper.xml:
<?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.yzh7.mapper.EmpMapper">
<resultMap id="empMap" type="emp">
<!-- emp_id,emp_name,sex,phone,dept_id-->
<!--id:表示表中的主键字段 column:查询表的列名 property:表示列名要映射到的实体属性-->
<id column="emp_id" property="empId"/>
<!--result:定义普通列和属性的映射关系-->
<result column="emp_name" property="empName"/>
<result column="sex" property="sex"/>
<result column="phone" property="phone"/>
<result column="dept_id" property="deptId"/>
<!-- dept_id,dept_name,description-->
<!--通过association标签(1对1)，设置员工关联的部门对象-->
<association property="dept" javaType="Dept">
<id column="dept_id" property="deptId"/>
<result column="dept_name" property="deptName"/>
<result column="description" property="description"/>
</association>
</resultMap>
<!--
resultType:只用于单表查询，并且查询结果列名和属性可以直接对应的情况。用于简单查询映射。
resultMap:用于多表查询，或者列名和属性映射关系负责的情况。用于复杂查询映射。
需要通过resultMap节点，配置结果的映射关系
-->
<select id="listAll" resultMap="empMap">
select e.emp_id,e.emp_name,e.sex,e.phone,
d.dept_id,d.dept_name,d.description
from t_emp e
join t_dept d
on e.dept_id=d.dept_id
</select>
<select id="test1" resultType="emp">
select e.emp_id,e.emp_name,e.sex,e.phone
from t_emp e
<where>
<if test="empName!=null and empName!='' ">
and emp_name like concat('%',#{empName},'%')
</if>
<if test="phone!=null and phone!='' ">
and phone like concat('%',#{phone},'%')
</if>
</where>
</select>
<select id="test11" resultType="emp">
select e.emp_id,e.emp_name,e.sex,e.phone
from t_emp e
<trim prefix="where" prefixOverrides="and | or">
<if test="empName!=null and empName!='' ">
and emp_name like concat('%',#{empName},'%')
</if>
<if test="phone!=null and phone!='' ">
and phone like concat('%',#{phone},'%')
</if>
</trim>
</select>
<select id="test2" resultType="emp">
select e.emp_id ,e.emp_name ,e.sex,e.phone
from t_emp e
<where>
<if test="startId!=null">
and emp_id > #{startId}
</if>
<if test="endId!=null">
<![CDATA[
and emp_id < #{endId}
]]>
</if>
<if test="empName!=null and empName!=''">
and emp_name like concat('%',#{empName},'%')
</if>
</where>
</select>
<!-- emp_id,emp_name,sex,phone,dept_id-->
<update id="update1">
update t_emp
set emp_name=#{empName},
sex=#{sex},
phone=#{phone},
dept_id=#{dept.deptId}
where emp_id=#{empId}
</update>
<!-- 注意：嵌套对象 dept.dept_id 首先访问Emp实体对象里的dept,然后访问dept对象里的deptId-->
<update id="update2">
update t_emp
<set>
<if test="empName!=null and empName!=''">
emp_name=#{empName},
</if>
<if test="sex!=null and sex!=''">
sex=#{sex},
</if>
<if test="phone!=null and phone!=''">
phone=#{phone},
</if>
<if test="dept.deptId!=null and dept.deptId!=''">
dept_id=#{dept.deptId},
</if>
</set>
where emp_id=#{empId}
</update>
<!-- //根据编号查询数据-->
<!-- List<Emp> testByIds(List<Integer> ids);-->
<!-- //批量插入-->
<!-- int insertBatch(List<Emp> empList);-->
<!--foreach循环拼接sql语句
collection:指定要循环的目标数据的类型,数组(array) list集合(list)
item:存储遍历的某个数据对象
open:拼接的字符串的前缀
close:拼接的字符串的后缀
separator:拼接内容的分隔符
-->
<select id="testByIds" resultType="emp">
select * from t_emp
where emp_id in
<foreach collection="list"
item="data"
open="("
close=")"
separator=",">
#{data}
</foreach>
</select>
<insert id="insertBatch">
insert into t_emp
(emp_name,sex,phone)
values
<foreach collection="list"
item="e"
separator=",">
(#{e.empName},#{e.sex},#{e.phone})
</foreach>
</insert>
</mapper>
Demo7:
package com.yzh7.test;
import com.yzh7.entity.Emp;
import com.yzh7.mapper.EmpMapper;
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 org.junit.After;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
public class Demo7 {
@Test
public void testTestByIds(){
EmpMapper empMapper=session.getMapper(EmpMapper.class);
List<Integer> ids=new ArrayList<>();
ids.add(1);
ids.add(3);
ids.add(4);
List<Emp> empList=empMapper.testByIds(ids);
System.out.println(empList);
}
@Test
public void testInsertBatch(){
EmpMapper empMapper=session.getMapper(EmpMapper.class);
Emp emp1=new Emp();
emp1.setEmpName("aaa");
emp1.setSex("男");
emp1.setPhone("1234561");
Emp emp2=new Emp();
emp2.setEmpName("bbb");
emp2.setSex("女");
emp2.setPhone("1234562");
Emp emp3=new Emp();
emp3.setEmpName("ccc");
emp3.setSex("男");
emp3.setPhone("1234563");
List<Emp> empList=new ArrayList<>();
empList.add(emp1);
empList.add(emp2);
empList.add(emp3);
int count =empMapper.insertBatch(empList);
System.out.println("批量插入的记录数；"+count);
}
private static SqlSessionFactory factory; //静态
private SqlSession session;
@BeforeClass
public static void befCla() throws IOException {
String resource = "mybatis_config.xml"; //mybatis_config.xml
InputStream inputStream = Resources.getResourceAsStream(resource);
factory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Before
public void bf(){
session=factory.openSession();
}
@After
public void af(){
session.commit();
session.close();
}
}
结果展示：
testTestByIds():
testInsertBatch():
批量插入之后，数据表如下：
4.choose 返回自动增长的主键
结果展示：
代码整体布局：
代码如下：
EmpMapper:
package com.yzh7.mapper;
import com.yzh7.entity.Emp;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
public interface EmpMapper {
//查询所有员工，并且每个员工关联一个部门对象
List<Emp> listAll();
//条件查询1(多参查询)
List<Emp> test1(@Param("empName") String empName,
@Param("phone") String phone);
List<Emp> test11(@Param("empName") String empName,
@Param("phone") String phone);
//条件查询2（对象查询）
List<Emp> test2(Map map);
//针对所有字段进行修改
int update1(Emp emp);
//根据传入的数据是否为空，不为空的才修改
int update2(Emp emp);
//根据编号查询数据
List<Emp> testByIds(List<Integer> ids);
//批量插入
int insertBatch(List<Emp> empList);
//分支条件查询
List<Emp> testByChoose(Emp emp);
//插入并接受返回的自增值
int insertAndGetAutoVal(Emp emp);
}
EmpMapper.xml:
<?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.yzh7.mapper.EmpMapper">
<resultMap id="empMap" type="emp">
<!-- emp_id,emp_name,sex,phone,dept_id-->
<!--id:表示表中的主键字段 column:查询表的列名 property:表示列名要映射到的实体属性-->
<id column="emp_id" property="empId"/>
<!--result:定义普通列和属性的映射关系-->
<result column="emp_name" property="empName"/>
<result column="sex" property="sex"/>
<result column="phone" property="phone"/>
<result column="dept_id" property="deptId"/>
<!-- dept_id,dept_name,description-->
<!--通过association标签(1对1)，设置员工关联的部门对象-->
<association property="dept" javaType="Dept">
<id column="dept_id" property="deptId"/>
<result column="dept_name" property="deptName"/>
<result column="description" property="description"/>
</association>
</resultMap>
<!--
resultType:只用于单表查询，并且查询结果列名和属性可以直接对应的情况。用于简单查询映射。
resultMap:用于多表查询，或者列名和属性映射关系负责的情况。用于复杂查询映射。
需要通过resultMap节点，配置结果的映射关系
-->
<select id="listAll" resultMap="empMap">
select e.emp_id,e.emp_name,e.sex,e.phone,
d.dept_id,d.dept_name,d.description
from t_emp e
join t_dept d
on e.dept_id=d.dept_id
</select>
<select id="test1" resultType="emp">
select e.emp_id,e.emp_name,e.sex,e.phone
from t_emp e
<where>
<if test="empName!=null and empName!='' ">
and emp_name like concat('%',#{empName},'%')
</if>
<if test="phone!=null and phone!='' ">
and phone like concat('%',#{phone},'%')
</if>
</where>
</select>
<select id="test11" resultType="emp">
select e.emp_id,e.emp_name,e.sex,e.phone
from t_emp e
<trim prefix="where" prefixOverrides="and | or">
<if test="empName!=null and empName!='' ">
and emp_name like concat('%',#{empName},'%')
</if>
<if test="phone!=null and phone!='' ">
and phone like concat('%',#{phone},'%')
</if>
</trim>
</select>
<select id="test2" resultType="emp">
select e.emp_id ,e.emp_name ,e.sex,e.phone
from t_emp e
<where>
<if test="startId!=null">
and emp_id > #{startId}
</if>
<if test="endId!=null">
<![CDATA[
and emp_id < #{endId}
]]>
</if>
<if test="empName!=null and empName!=''">
and emp_name like concat('%',#{empName},'%')
</if>
</where>
</select>
<!-- emp_id,emp_name,sex,phone,dept_id-->
<update id="update1">
update t_emp
set emp_name=#{empName},
sex=#{sex},
phone=#{phone},
dept_id=#{dept.deptId}
where emp_id=#{empId}
</update>
<!-- 注意：嵌套对象 dept.dept_id 首先访问Emp实体对象里的dept,然后访问dept对象里的deptId-->
<update id="update2">
update t_emp
<set>
<if test="empName!=null and empName!=''">
emp_name=#{empName},
</if>
<if test="sex!=null and sex!=''">
sex=#{sex},
</if>
<if test="phone!=null and phone!=''">
phone=#{phone},
</if>
<if test="dept.deptId!=null and dept.deptId!=''">
dept_id=#{dept.deptId},
</if>
</set>
where emp_id=#{empId}
</update>
<!-- //根据编号查询数据-->
<!-- List<Emp> testByIds(List<Integer> ids);-->
<!-- //批量插入-->
<!-- int insertBatch(List<Emp> empList);-->
<!--foreach循环拼接sql语句
collection:指定要循环的目标数据的类型,数组(array) list集合(list)
item:存储遍历的某个数据对象
open:拼接的字符串的前缀
close:拼接的字符串的后缀
separator:拼接内容的分隔符
-->
<select id="testByIds" resultType="emp">
select * from t_emp
where emp_id in
<foreach collection="list"
item="data"
open="("
close=")"
separator=",">
#{data}
</foreach>
</select>
<insert id="insertBatch">
insert into t_emp
(emp_name,sex,phone)
values
<foreach collection="list"
item="e"
separator=",">
(#{e.empName},#{e.sex},#{e.phone})
</foreach>
</insert>
<select id="testByChoose" resultType="emp">
select * from t_emp
<where>
<choose>
<when test="empName!=null and empName!=''">
and emp_name=#{empName}
</when>
<when test="sex!=null and sex!=''">
and sex=#{sex}
</when>
<otherwise>
and emp_id=1
</otherwise>
</choose>
</where>
</select>
<!--
useGeneratedKeys:使用插入自增
keyProperty:生成的自增值，存储到传入对象的哪个属性上
-->
<insert id="insertAndGetAutoVal" useGeneratedKeys="true" keyProperty="empId">
insert into t_emp
(emp_name,sex,phone)
values
(#{empName},#{sex},#{phone})
</insert>
</mapper>
Demo8:
package com.yzh7.test;
import com.yzh7.entity.Emp;
import com.yzh7.mapper.EmpMapper;
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 org.junit.After;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class Demo8 {
// List<Emp> testByChoose(Emp emp);
@Test
public void testTestByChoose(){
EmpMapper empMapper=session.getMapper(EmpMapper.class);
Emp emp=new Emp();
emp.setEmpName("王五");
emp.setSex("女");
List<Emp> empList=empMapper.testByChoose(emp);
System.out.println(empList);
}
@Test
public void testInsertAndGetAutoVal(){
EmpMapper empMapper=session.getMapper(EmpMapper.class);
Emp emp=new Emp();
emp.setEmpName("王十一");
emp.setSex("男");
emp.setPhone("1234561");
int count=empMapper.insertAndGetAutoVal(emp);
System.out.println("记录数："+count);
System.out.println("插入之后，返回的子增值："+emp);
}
private static SqlSessionFactory factory;
private SqlSession session;
@BeforeClass
public static void befCla() throws IOException {
String resource="mybatis_config.xml";
InputStream inputStream= Resources.getResourceAsStream(resource);
factory=new SqlSessionFactoryBuilder().build(inputStream);
}
@Before
public void bf(){
session=factory.openSession();
}
@After
public void af(){
session.commit();
session.close();
}
}
5.sql标签定义通用sql片段 sql
结果展示：
代码整体布局：
代码如下：
EmpMapper:
package com.yzh7.mapper;
import com.yzh7.entity.Emp;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
public interface EmpMapper {
//查询所有员工，并且每个员工关联一个部门对象
List<Emp> listAll();
//条件查询1(多参查询)
List<Emp> test1(@Param("empName") String empName,
@Param("phone") String phone);
List<Emp> test11(@Param("empName") String empName,
@Param("phone") String phone);
//条件查询2（对象查询）
List<Emp> test2(Map map);
//针对所有字段进行修改
int update1(Emp emp);
//根据传入的数据是否为空，不为空的才修改
int update2(Emp emp);
//根据编号查询数据
List<Emp> testByIds(List<Integer> ids);
//批量插入
int insertBatch(List<Emp> empList);
//分支条件查询
List<Emp> testByChoose(Emp emp);
//插入并接受返回的自增值
int insertAndGetAutoVal(Emp emp);
//条件查询3
Emp test3(Integer empId);
//条件查询4
List<Emp> test4(Map map);
}
EmpMapper.xml:
<?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.yzh7.mapper.EmpMapper">
<resultMap id="empMap" type="emp">
<!-- emp_id,emp_name,sex,phone,dept_id-->
<!--id:表示表中的主键字段 column:查询表的列名 property:表示列名要映射到的实体属性-->
<id column="emp_id" property="empId"/>
<!--result:定义普通列和属性的映射关系-->
<result column="emp_name" property="empName"/>
<result column="sex" property="sex"/>
<result column="phone" property="phone"/>
<result column="dept_id" property="deptId"/>
<!-- dept_id,dept_name,description-->
<!--通过association标签(1对1)，设置员工关联的部门对象-->
<association property="dept" javaType="Dept">
<id column="dept_id" property="deptId"/>
<result column="dept_name" property="deptName"/>
<result column="description" property="description"/>
</association>
</resultMap>
<!--
resultType:只用于单表查询，并且查询结果列名和属性可以直接对应的情况。用于简单查询映射。
resultMap:用于多表查询，或者列名和属性映射关系负责的情况。用于复杂查询映射。
需要通过resultMap节点，配置结果的映射关系
-->
<select id="listAll" resultMap="empMap">
select e.emp_id,e.emp_name,e.sex,e.phone,
d.dept_id,d.dept_name,d.description
from t_emp e
join t_dept d
on e.dept_id=d.dept_id
</select>
<select id="test1" resultType="emp">
select e.emp_id,e.emp_name,e.sex,e.phone
from t_emp e
<where>
<if test="empName!=null and empName!='' ">
and emp_name like concat('%',#{empName},'%')
</if>
<if test="phone!=null and phone!='' ">
and phone like concat('%',#{phone},'%')
</if>
</where>
</select>
<select id="test11" resultType="emp">
select e.emp_id,e.emp_name,e.sex,e.phone
from t_emp e
<trim prefix="where" prefixOverrides="and | or">
<if test="empName!=null and empName!='' ">
and emp_name like concat('%',#{empName},'%')
</if>
<if test="phone!=null and phone!='' ">
and phone like concat('%',#{phone},'%')
</if>
</trim>
</select>
<select id="test2" resultType="emp">
select e.emp_id ,e.emp_name ,e.sex,e.phone
from t_emp e
<where>
<if test="startId!=null">
and emp_id > #{startId}
</if>
<if test="endId!=null">
<![CDATA[
and emp_id < #{endId}
]]>
</if>
<if test="empName!=null and empName!=''">
and emp_name like concat('%',#{empName},'%')
</if>
</where>
</select>
<!-- emp_id,emp_name,sex,phone,dept_id-->
<update id="update1">
update t_emp
set emp_name=#{empName},
sex=#{sex},
phone=#{phone},
dept_id=#{dept.deptId}
where emp_id=#{empId}
</update>
<!-- 注意：嵌套对象 dept.dept_id 首先访问Emp实体对象里的dept,然后访问dept对象里的deptId-->
<update id="update2">
update t_emp
<set>
<if test="empName!=null and empName!=''">
emp_name=#{empName},
</if>
<if test="sex!=null and sex!=''">
sex=#{sex},
</if>
<if test="phone!=null and phone!=''">
phone=#{phone},
</if>
<if test="dept.deptId!=null and dept.deptId!=''">
dept_id=#{dept.deptId},
</if>
</set>
where emp_id=#{empId}
</update>
<!-- //根据编号查询数据-->
<!-- List<Emp> testByIds(List<Integer> ids);-->
<!-- //批量插入-->
<!-- int insertBatch(List<Emp> empList);-->
<!--foreach循环拼接sql语句
collection:指定要循环的目标数据的类型,数组(array) list集合(list)
item:存储遍历的某个数据对象
open:拼接的字符串的前缀
close:拼接的字符串的后缀
separator:拼接内容的分隔符
-->
<select id="testByIds" resultType="emp">
select * from t_emp
where emp_id in
<foreach collection="list"
item="data"
open="("
close=")"
separator=",">
#{data}
</foreach>
</select>
<insert id="insertBatch">
insert into t_emp
(emp_name,sex,phone)
values
<foreach collection="list"
item="e"
separator=",">
(#{e.empName},#{e.sex},#{e.phone})
</foreach>
</insert>
<select id="testByChoose" resultType="emp">
select * from t_emp
<where>
<choose>
<when test="empName!=null and empName!=''">
and emp_name=#{empName}
</when>
<when test="sex!=null and sex!=''">
and sex=#{sex}
</when>
<otherwise>
and emp_id=1
</otherwise>
</choose>
</where>
</select>
<!--
useGeneratedKeys:使用插入自增
keyProperty:生成的自增值，存储到传入对象的哪个属性上
-->
<insert id="insertAndGetAutoVal" useGeneratedKeys="true" keyProperty="empId">
insert into t_emp
(emp_name,sex,phone)
values
(#{empName},#{sex},#{phone})
</insert>
<!--定义公共使用的sql代码块-->
<sql id="commonCols">
emp_id,emp_name,sex,phone
</sql>
<select id="test3" resultType="emp">
select
<include refid="commonCols"></include>
from t_emp
where emp_id=#{empId}
</select>
<sql id="commonQuery">
select e.emp_id ,e.emp_name ,e.sex,e.phone,
d.dept_id,d.dept_name,d.description
from t_emp e
join t_dept d
on e.dept_id = d.dept_id
</sql>
<select id="test4" resultMap="empMap">
<include refid="commonQuery"></include>
<where>
<if test="empName!=null and empName!=''">
and emp_name like concat('%',#{empName},'%')
</if>
</where>
</select>
</mapper>
Demo9:
package com.yzh7.test;
import com.yzh7.entity.Emp;
import com.yzh7.mapper.EmpMapper;
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 org.junit.After;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class Demo9 {
@Test
public void testTest3(){
EmpMapper empMapper=session.getMapper(EmpMapper.class);
System.out.println(empMapper.test3(1));
}
@Test
public void testTest4(){
EmpMapper empMapper=session.getMapper(EmpMapper.class);
Map map=new HashMap();
map.put("empName","李");
List<Emp> empList=empMapper.test4(map);
System.out.println(empList);
}
private static SqlSessionFactory factory;
private SqlSession session;
@BeforeClass
public static void befCla() throws IOException {
String resource="mybatis_config.xml";
InputStream inputStream= Resources.getResourceAsStream(resource);
factory=new SqlSessionFactoryBuilder().build(inputStream);
}
@Before
public void bf(){
session=factory.openSession();
}
@After
public void af(){
session.commit();
session.close();
}
}
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/118051.html
