Mybatis学习02
一、模糊查询
1、mapper接口层
package com.java.mapper;
import com.java.model.EmpEntity;
import java.util.List;
import java.util.Map;
public interface EmpMapper {
/**
* 参数为String类型的模糊查询
* @param name
* @return
*/
List<EmpEntity> selectLikeName(String name);
/**
* 参数为实体类对象的模糊查询
* @param emp
* @return
*/
List<EmpEntity> selectLikeEmpName(EmpEntity emp);
/**
* 参数为map集合的模糊查询
* @param map
* @return
*/
List<EmpEntity> selectLikeEmpNameMap(Map map);
}
2、映射文件:xxxMapper.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">
<!--代理扫描 namespace属性表示当前代理的命名空间,属性值为mapper接口地址,相当于mapper接口实现类 -->
<mapper namespace="com.java.mapper.EmpMapper">
<!--要查询的sql字段-->
<sql id="empSQL">
number,name,job,mgr,hiredate,sal,comm,deptno
</sql>
<!--1、参数为String类型的模糊查询-->
<!--方法1-->
<select id="selectLikeName" parameterType="string" resultType="EmpEntity">
SELECT <include refid="empSQL"/> FROM emp WHERE name LIKE "%"#{name}"%"
</select>
<!--方法2:推荐使用-->
<select id="selectLikeName" parameterType="string" resultType="EmpEntity">
SELECT <include refid="empSQL"/> FROM emp WHERE name LIKE concat("%",#{name},"%")
</select>
<!--方法3,SQL语句:SELECT number,name,job,mgr,hiredate,sal,comm,deptno FROM emp WHERE name LIKE "%子%" -->
<select id="selectLikeName" parameterType="string" resultType="EmpEntity">
SELECT <include refid="empSQL"/> FROM emp WHERE name LIKE "%${value}%"
</select>
<!--2、参数为实体类对象的模糊查询-->
<!--方法1-->
<select id="selectLikeEmpName" parameterType="EmpEntity" resultType="EmpEntity">
SELECT <include refid="empSQL"/> FROM emp WHERE name LIKE concat("%",#{name},"%")
</select>
<!--方法2-->
<select id="selectLikeEmpName" parameterType="EmpEntity" resultType="EmpEntity">
SELECT <include refid="empSQL"/> FROM emp WHERE name LIKE "%"#{name}"%"
</select>
<!--方法3-->
<select id="selectLikeEmpName" parameterType="EmpEntity" resultType="EmpEntity">
SELECT <include refid="empSQL"/> FROM emp WHERE name LIKE '%${name}%'
</select>
<!--3、参数为map集合的模糊查询,#{}中参数为map的key值-->
<select id="selectLikeEmpNameMap" parameterType="map" resultType="EmpEntity">
SELECT <include refid="empSQL"/> FROM emp WHERE name LIKE "%"#{name}"%"
</select>
</mapper>
3、测试类
package com.java;
import com.java.mapper.EmpMapper;
import com.java.model.EmpEntity;
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.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
/**
* @author Liushun
* @date Created in 2018/10/4 21:36
* @description
*/
public class EmpTest {
private SqlSession sqlSession = null;
@Before
public void init() throws IOException {
// 1、加载核心配置文件sqlMapConfig.xml
InputStream is = Resources.getResourceAsStream("sqlMapConfig.xml");
// 2、创建sqlSessionFactory会话工厂
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory factory = builder.build(is);
// 由会话工厂创建sqlSession即会话
sqlSession = factory.openSession();
}
// 参数为String类型的模糊查询
@Test
public void findLikeName(){
sqlSession.getMapper(EmpMapper.class).selectLikeName("子");
}
// 参数为实体类对象的模糊查询
@Test
public void findLikeEmpName(){
EmpEntity emp = new EmpEntity();
emp.setName("子");
sqlSession.getMapper(EmpMapper.class).selectLikeEmpName(emp);
}
// 参数为map集合的模糊查询
@Test
public void findLikeEmpNameMap(){
HashMap<String, Object> map = new HashMap<>();
map.put("name","子");
sqlSession.getMapper(EmpMapper.class).selectLikeEmpNameMap(map);
}
@After
public void destory(){
if(sqlSession != null){
try{
// 提交事务
sqlSession.commit();
}catch (Exception e){
// 有异常,回滚事务
sqlSession.rollback();
}finally{
// 关闭sqlSession
sqlSession.close();
}
}
}
}
4、注意事项:区分 #{}和${}的区别
采用#{}形式赋值,采用是占位符形式?,防止sql注入攻击
采用${}形式赋值,直接赋值,容易造成sql注入攻击
5、查询注意:
查询的select标签中都必须加入结果集属性resultType或者resultMap
二、分页查询
1、Mapper接口层
/**
* 分页查询
* @param map
* @return
*/
List<EmpEntity> selectPage(Map map);
/**
* 模糊分页
* @param map
* @return
*/
List<EmpEntity> selectLikePage(Map map);
2、映射文件:xxxMapper.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">
<!--代理扫描 namespace属性表示当前代理的命名空间,属性值为mapper接口地址,相当于mapper接口实现类 -->
<mapper namespace="com.java.mapper.EmpMapper">
<!--要查询的sql字段-->
<sql id="empSQL">
number,name,job,mgr,hiredate,sal,comm,deptno
</sql>
<!--分页查询:参数是map类型,sql语句中的参数名称必须和map集合中的k值相对应-->
<select id="selectPage" parameterType="map" resultType="EmpEntity">
SELECT <include refid="empSQL"/> FROM emp LIMIT #{startRow},#{pageSize}
</select>
<!--模糊分页-->
<select id="selectLikePage" parameterType="map" resultType="EmpEntity">
SELECT <include refid="empSQL"/> FROM emp WHERE name LIKE concat("%",#{name},"%") LIMIT #{startRow},#{pageSize}
</select>
</mapper>
3、测试类
package com.java;
import com.java.mapper.EmpMapper;
import com.java.model.EmpEntity;
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.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
/**
* @author Liushun
* @date Created in 2018/10/4 21:36
* @description
*/
public class EmpTest {
private SqlSession sqlSession = null;
@Before
public void init() throws IOException {
// 1、加载核心配置文件sqlMapConfig.xml
InputStream is = Resources.getResourceAsStream("sqlMapConfig.xml");
// 2、创建sqlSessionFactory会话工厂
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory factory = builder.build(is);
// 由会话工厂创建sqlSession即会话
sqlSession = factory.openSession();
}
// 分页查询
@Test
public void findPage(){
HashMap<String, Object> map = new HashMap<>();
map.put("startRow",0);
map.put("pageSize",5);
sqlSession.getMapper(EmpMapper.class).selectPage(map);
}
// 模糊分页查询
@Test
public void findLikePage(){
HashMap<String, Object> map = new HashMap<>();
map.put("startRow",0);
map.put("pageSize",5);
map.put("name","子");
sqlSession.getMapper(EmpMapper.class).selectLikePage(map);
}
@After
public void destory(){
if(sqlSession != null){
try{
// 提交事务
sqlSession.commit();
}catch (Exception e){
// 有异常,回滚事务
sqlSession.rollback();
}finally{
// 关闭sqlSession
sqlSession.close();
}
}
}
}
三、mybatis动态sql语句(增、删、改、查)
1、mapper接口层
package com.java.mapper;
import com.java.model.EmpEntity;
import java.util.List;
import java.util.Map;
public interface EmpMapper {
/**
* 动态新增
* @param emp
* @return
*/
Integer insertActiveEmp(EmpEntity emp);
/**
* 动态查询
* @param emp
* @return
*/
List<EmpEntity> selectActiveEmp(EmpEntity emp);
/**
* 动态修改
* @param emp
* @return
*/
Integer updateActiveEmp(EmpEntity emp);
/**
* 批量查询
* @param ids
* @return
*/
List<EmpEntity> selectBatchEmp(List ids);
/**
* 批量删除
* @param ids
* @return
*/
Integer deleteBatchEmp(Integer[] ids);
}
2、映射文件xxxMapper.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">
<!--代理扫描 namespace属性表示当前代理的命名空间,属性值为mapper接口地址,相当于mapper接口实现类 -->
<mapper namespace="com.java.mapper.EmpMapper">
<!--动态新增-->
<!-- trim + if :
if标签表示判断,如果符合条件,则执行条件内容
trim表示去掉多余的指定的字符,prefix表示前缀,suffix表示后缀
suffixOverrides去除字段之后的指定字符
prefixOverrides去除字段之前的指定字符-->
<insert id="insertActiveEmp" parameterType="EmpEntity">
INSERT INTO emp
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="name != null and name != ''">
name,
</if>
<if test="job != null and job != ''">
job,
</if>
<if test="mgr != null and mgr != 0">
mgr,
</if>
<if test="hiredate != null">
hiredate,
</if>
<if test="sal != null and sal != ''">
sal,
</if>
<if test="comm != null and comm != ''">
comm,
</if>
<if test="deptno != null and deptno != 0">
deptno,
</if>
</trim>
VALUES
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="name != null and name != ''">
#{name},
</if>
<if test="job != null and job != ''">
#{job},
</if>
<if test="mgr != null and mgr != 0">
#{mgr},
</if>
<if test="hiredate != null">
#{hiredate},
</if>
<if test="sal != null and sal != ''">
#{sal},
</if>
<if test="comm != null and comm != ''">
#{comm},
</if>
<if test="deptno != null and deptno != 0">
#{deptno},
</if>
</trim>
</insert>
<!--动态查询-->
<!-- where+if :
where标签表示条件连接符
特性:如果一个条件成立,那么where标签会自动把and关键字去掉,
若语句的开头为“AND”或“OR”,where 元素也会将它们去除。
如果所有条件都不成立,那么where会启动自毁程序,把自己也去掉-->
<select id="selectActiveEmp" parameterType="EmpEntity" resultType="EmpEntity">
SELECT <include refid="empSQL"/> FROM emp
<where>
<if test="name != null and name != ''">
AND name LIKE concat("%",#{name},"%")
</if>
<if test="job != null and job != ''">
AND job LIKE concat("%",#{job},"%")
</if>
<if test="mgr != null and mgr != 0">
AND mgr = #{mgr}
</if>
<if test="hiredate != null">
AND hiredate >= #{hiredate}
</if>
<if test="sal != null and sal != 0">
AND sal <= #{sal}
</if>
<if test="comm != null and comm != 0">
AND comm >= #{comm}
</if>
<if test="deptno != null and deptno != 0">
AND deptno = #{deptno}
</if>
</where>
</select>
<!--动态修改-->
<!-- set + if :
set标签专用于修改,特性就是会自动去除掉最后一个条件的之后的逗号
-->
<update id="updateActiveEmp" parameterType="EmpEntity">
update emp
<set>
<if test="name != null and name != ''">
name = #{name},
</if>
<if test="job != null and job != ''">
job = #{job},
</if>
<if test="mgr != null and mgr != 0">
mgr = #{mgr},
</if>
<if test="hiredate != null">
hiredate = #{hiredate},
</if>
<if test="sal != null and sal != 0">
sal = #{sal},
</if>
<if test="comm != null and comm != 0">
comm = #{comm},
</if>
<if test="deptno != null and deptno != 0">
deptno = #{deptno},
</if>
</set>
where number = #{number}
</update>
<!--批量查询-->
<!-- foreach :
foreach标签表示循环标签,collection表示集合属性,属性值有两种,
如果接口的参数是List类型,那么该属性值=list
如果接口的参数是数组类型,那么该属性值=array
open属性表示类似于前缀
close表示类似于后缀
item表示集合的遍历体,属性值随意起
separator表示隔离间隔的关键字属性,
foreach标签之间展示的每次遍历的id值,表达形式#{item属性值}
-->
<select id="selectBatchEmp" parameterType="list" resultType="EmpEntity">
SELECT <include refid="empSQL"/> FROM emp
<where>
number IN
<foreach collection="list" open="(" close=")" separator="," item="ids">
#{ids}
</foreach>
</where>
</select>
<!--批量删除-->
<delete id="deleteBatchEmp" parameterType="integer[]">
DELETE FROM emp
<where>
number in
<foreach collection="array" open="(" separator="," close=")" item="ids">
#{ids}
</foreach>
</where>
</delete>
</mapper>
3、测试类
package com.java;
import com.java.mapper.EmpMapper;
import com.java.model.EmpEntity;
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.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
/**
* @author Liushun
* @date Created in 2018/10/4 21:36
* @description
*/
public class EmpTest {
private SqlSession sqlSession = null;
@Before
public void init() throws IOException {
// 1、加载核心配置文件sqlMapConfig.xml
InputStream is = Resources.getResourceAsStream("sqlMapConfig.xml");
// 2、创建sqlSessionFactory会话工厂
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory factory = builder.build(is);
// 由会话工厂创建sqlSession即会话
sqlSession = factory.openSession();
}
// 动态新增
@Test
public void saveActiveEmp(){
EmpEntity emp = new EmpEntity();
emp.setName("雷震子");
emp.setJob("武将");
sqlSession.getMapper(EmpMapper.class).insertActiveEmp(emp);
}
// 动态查询
@Test
public void findActiveEmp(){
EmpEntity emp = new EmpEntity();
emp.setName("子");
emp.setComm(7000D);
sqlSession.getMapper(EmpMapper.class).selectActiveEmp(emp);
}
// 动态修改
@Test
public void modifyActiveEmp(){
EmpEntity emp = new EmpEntity();
emp.setName("妲己");
emp.setComm(7000D);
emp.setNumber(1033);
sqlSession.getMapper(EmpMapper.class).updateActiveEmp(emp);
}
// 批量查询
@Test
public void findBatchEmp(){
ArrayList<Integer> ids = new ArrayList<>();
ids.add(1001);
ids.add(1002);
ids.add(1003);
sqlSession.getMapper(EmpMapper.class).selectBatchEmp(ids);
}
// 批量删除
@Test
public void removeBatchEmp(){
Integer[] ids =new Integer[5];
ids[0] = 1034;
ids[1] = 1033;
sqlSession.getMapper(EmpMapper.class).deleteBatchEmp(ids);
}
@After
public void destory(){
if(sqlSession != null){
try{
// 提交事务
sqlSession.commit();
}catch (Exception e){
// 有异常,回滚事务
sqlSession.rollback();
}finally{
// 关闭sqlSession
sqlSession.close();
}
}
}
}