Spring_MyBatis 使用动态 SQL 语句
一、文件结构
二、代码
1 package mybatis.entity; 2 3 public class Entity { 4 private int id; 5 private String name; 6 private String sex; 7 @Override 8 public String toString() { 9 return "Entity [id=" + id + ", name=" + name + ", sex=" + sex + "]"; 10 } 11 public Entity(int id, String name, String sex) { 12 super(); 13 this.id = id; 14 this.name = name; 15 this.sex = sex; 16 } 17 public Entity() { 18 super(); 19 } 20 public int getId() { 21 return id; 22 } 23 public void setId(int id) { 24 this.id = id; 25 } 26 public String getName() { 27 return name; 28 } 29 public void setName(String name) { 30 this.name = name; 31 } 32 public String getSex() { 33 return sex; 34 } 35 public void setSex(String sex) { 36 this.sex = sex; 37 } 38 39 }
1 package mybatis.mapper; 2 3 import java.util.List; 4 5 import mybatis.entity.Entity; 6 7 /** 8 * 定义一个映射器 9 * 1.映射文件(mybatis/entity/EntityMapper.xml)中的namespace的值修改成映射器的完全限定名 10 * 2.映射器中方法的名称与映射文件中的节点的id值一致，参数和返回类型也一致 11 */ 12 public interface PerMapper { 13 public List<Entity> selectWithCondition(Entity en); 14 public List<Entity> selectWithChoose(Entity en); 15 public List<Entity> selectWithWhere(Entity en); 16 public List<Entity> selectWithTrim(Entity en); 17 public int updataById(Entity en); 18 public List<Entity> SelectForeachIn(List<Integer> list); 19 public int insertForeach(List<Entity> list); 20 public int updateForeachById(List<Entity> list); 21 public int forEachUpdate(List<Entity> list); 22 }
1 package mybatis.test; 2 3 import java.util.ArrayList; 4 import java.util.List; 5 import org.apache.ibatis.session.SqlSession; 6 import org.apache.ibatis.session.SqlSessionFactory; 7 import org.junit.Before; 8 import org.junit.Test; 9 import mybatis.entity.Entity; 10 import mybatis.mapper.PerMapper; 11 import mybatis.util.SqlSessionFactoryUtil; 12 13 public class TestMybatis { 14 public SqlSessionFactory sqlSessionFactory = null; 15 16 @Before 17 public void init() { 18 sqlSessionFactory = SqlSessionFactoryUtil.getSqlSessionFactoryInstance(); 19 } 20 21 @Test 22 public void selectByIf() { 23 SqlSession session = sqlSessionFactory.openSession(); 24 PerMapper mapper = session.getMapper(PerMapper.class); 25 Entity en = new Entity(); 26 en.setId(103); 27 List<Entity> list = mapper.selectWithCondition(en); 28 System.out.println(list); 29 session.close(); 30 } 31 @Test 32 public void selectByChoose() { 33 SqlSession session = sqlSessionFactory.openSession(); 34 PerMapper mapper = session.getMapper(PerMapper.class); 35 Entity en = new Entity(); 36 //en.setId(103); 37 //en.setName("孙悟空"); 38 en.setSex("男"); 39 List<Entity> list = mapper.selectWithChoose(en); 40 for (Entity e : list) { 41 System.out.println(e); 42 } 43 session.close(); 44 } 45 @Test 46 public void selectByWhere() { 47 SqlSession session = sqlSessionFactory.openSession(); 48 PerMapper mapper = session.getMapper(PerMapper.class); 49 Entity en = new Entity(); 50 //en.setId(103); 51 //en.setName("孙悟空"); 52 en.setSex("女"); 53 List<Entity> list = mapper.selectWithWhere(en); 54 for (Entity e : list) { 55 System.out.println(e); 56 } 57 session.close(); 58 } 59 @Test 60 public void selectByTrim() { 61 SqlSession session = sqlSessionFactory.openSession(); 62 PerMapper mapper = session.getMapper(PerMapper.class); 63 Entity en = new Entity(); 64 //en.setId(103); 65 en.setName("孙悟空"); 66 en.setSex("女"); 67 List<Entity> list = mapper.selectWithTrim(en); 68 for (Entity e : list) { 69 System.out.println(e); 70 } 71 session.close(); 72 } 73 @Test 74 public void updateBySet() { 75 SqlSession session = sqlSessionFactory.openSession(); 76 PerMapper mapper = session.getMapper(PerMapper.class); 77 Entity en = new Entity(); 78 en.setId(106); 79 en.setName("布尔玛"); 80 //en.setSex("m"); 81 System.out.println(mapper.updataById(en)); 82 session.commit(); 83 session.close(); 84 } 85 @Test 86 public void SelectForeachIn() { 87 SqlSession session = sqlSessionFactory.openSession(); 88 PerMapper mapper = session.getMapper(PerMapper.class); 89 ArrayList<Integer> list = new ArrayList<Integer>(); 90 list.add(101); 91 list.add(102); 92 list.add(103); 93 List<Entity> list2=mapper.SelectForeachIn(list); 94 for (Entity e : list2) { 95 System.out.println(e); 96 } 97 session.close(); 98 } 99 @Test 100 public void insertForeach() { 101 SqlSession session = sqlSessionFactory.openSession(); 102 PerMapper mapper = session.getMapper(PerMapper.class); 103 ArrayList<Entity> list = new ArrayList<Entity>(); 104 Entity en = new Entity(); 105 list.add(new Entity(202,"龟仙人","男")); 106 list.add(new Entity(203,"天津饭","男")); 107 list.add(new Entity(204,"梦琪","女")); 108 System.out.println(mapper.insertForeach(list)); 109 session.commit(); 110 session.close(); 111 } 112 @Test 113 public void updateForeachById() { 114 SqlSession session = sqlSessionFactory.openSession(); 115 PerMapper mapper = session.getMapper(PerMapper.class); 116 ArrayList<Entity> list = new ArrayList<Entity>(); 117 Entity en = new Entity(); 118 en.setId(201); 119 en.setName("龟仙人"); 120 en.setSex("男"); 121 Entity en2 = new Entity(); 122 en.setId(202); 123 en.setName("克林"); 124 en.setSex("男"); 125 list.add(en); 126 list.add(en2); 127 /*list.add(new Entity(201,"龟仙人","男")); 128 list.add(new Entity(202,"克林","男")); 129 list.add(new Entity(204,"兰琪","女"));*/ 130 System.out.println(mapper.forEachUpdate(list)); 131 session.commit(); 132 session.close(); 133 } 134 }
1 package mybatis.util; 2 3 import java.io.IOException; 4 import java.io.Reader; 5 6 import org.apache.ibatis.io.Resources; 7 import org.apache.ibatis.session.SqlSessionFactory; 8 import org.apache.ibatis.session.SqlSessionFactoryBuilder; 9 10 /** 通过单例模式管理SqlSessionFactory对象 */ 11 public class SqlSessionFactoryUtil { 12 private SqlSessionFactoryUtil() { 13 } 14 15 /* 恶汉式 */ 16 private static SqlSessionFactory sqlSessionFactory; 17 static { 18 Reader reader; 19 try { 20 reader = Resources.getResourceAsReader("mybatisConfig.xml"); 21 SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); 22 sqlSessionFactory = builder.build(reader); 23 } catch (IOException e) { 24 e.printStackTrace(); 25 } 26 } 27 public static SqlSessionFactory getSqlSessionFactoryInstance() { 28 return sqlSessionFactory; 29 } 30 /* 31 * 懒汉式： 32 * private static SqlSessionFactory sqlSessionFactory = null; 33 * //synchronized 防并发 34 * public synchronized static SqlSessionFactory getSqlSessionFactoryInstance() { 35 * if (sqlSessionFactory != null) { 36 * return sqlSessionFactory; 37 * } else { 38 Reader reader; 39 try { 40 reader = Resources.getResourceAsReader("mybatisConfig.xml"); 41 SqlSessionFactoryBuilder 42 builder = new SqlSessionFactoryBuilder(); 43 sqlSessionFactory = builder.build(reader); 44 } catch (IOException e) { 45 e.printStackTrace(); } 46 return sqlSessionFactory; 47 * } 48 * } 49 */ 50 }
三、配置
1 <?xml version="1.0" encoding="UTF-8"?> 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 3 "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 4 <mapper namespace="mybatis.mapper.PerMapper"> 5 <resultMap id="ResultMap" type="Entity"> 6 <!-- 映射主键 --> 7 <id property="id" column="qid"></id> 8 <!-- 非主键字段 --> 9 <result property="name" column="qname"></result> 10 <result property="sex" column="qsex"></result> 11 </resultMap> 12 13 <!-- if 查询 符合条件就添加约束 --> 14 <select id="selectWithCondition" resultType="Entity"> 15 select * from person2 where 1=1 16 <if test="id>0">and id=#{id}</if> 17 <if test="name!=null">and name=#{name}</if> 18 <if test="sex!=null">and sex=#{sex}</if> 19 </select> 20 <!-- choose 查询 按从上到下满足的第一个条件添加其约束 --> 21 <select id="selectWithChoose" resultMap="ResultMap" 22 parameterType="Entity"> 23 select id qid,name qname,sex qsex from person2 where 24 <choose> 25 <when test="id>0">id=#{id}</when> 26 <when test="name!=null">name=#{name}</when> 27 <otherwise>sex=#{sex}</otherwise> 28 </choose> 29 </select> 30 <!-- where 查询 自动跟随where --> 31 <select id="selectWithWhere" resultMap="ResultMap" 32 parameterType="Entity"> 33 select id qid,name qname,sex qsex from person2 34 <where> 35 <choose> 36 <when test="id>0">id=#{id}</when> 37 <when test="name!=null">name=#{name}</when> 38 <otherwise>sex=#{sex}</otherwise> 39 </choose> 40 </where> 41 </select> 42 <!-- trim 查询 设置 prefix 并吸收第一个 prefixOverrides --> 43 <select id="selectWithTrim" resultMap="ResultMap" 44 parameterType="Entity"> 45 select * from person2 46 <trim prefix="where" prefixOverrides="and|or"> 47 <if test="id>0">or id=#{id}</if> 48 <if test="name!=null">or name=#{name}</if> 49 <if test="sex!=null">or sex=#{sex}</if> 50 </trim> 51 </select> 52 <!-- set 更新 符合条件就自动添加约束 注意用','分割 --> 53 <update id="updataById" parameterType="Entity"> 54 update person2 55 <set> 56 <if test="name!=null">name=#{name},</if> 57 <if test="sex!=null">sex=#{sex}</if> 58 </set> 59 where id=#{id} 60 </update> 61 <!-- foreach collection:容器类型 item：临时变量 index：下标 open：参数开始标识 separator：参数分割符 62 close：参数结束标识 --> 63 <select id="SelectForeachIn" resultType="Entity" 64 parameterType="java.util.List"> 65 select * from person2 where id in 66 <foreach collection="list" item="i" index="index" open="(" 67 separator="," close=")"> 68 #{i} 69 </foreach> 70 </select> 71 <!-- 动态sql batch(批处理) insert --> 72 <insert id="insertForeach" parameterType="java.util.List"> 73 insert into person2 values 74 <foreach collection="list" item="i" index="index" 75 separator=","> 76 (#{i.id},#{i.name},#{i.sex}) 77 </foreach> 78 </insert> 79 <!-- 动态sql batch(批处理) update --> 80 <update id="updateForeachById" parameterType="java.util.List"> 81 <foreach collection="list" item="i" index="index" 82 separator=";"> 83 update person2 84 <set> 85 <if test="i.name!=null">name=#{i.name},</if> 86 <if test="i.sex!=null">sex=#{i.sex}</if> 87 </set> 88 where id=#{i.id} 89 </foreach> 90 </update> 91 <update id="forEachUpdate" parameterType="java.util.List"> 92 <foreach item="item" index="index" collection="list" open="" close="" separator=";"> 93 update person2 94 <set> 95 <if test="item.name!=null"> 96 name=#{item.name},</if> 97 <if test="item.sex!=null"> 98 sex=#{item.sex} 99 </if> 100 </set> 101 where id=#{item.id} 102 </foreach> 103 </update> 104 </mapper>
1 <?xml version="1.0" encoding="UTF-8"?> 2 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" 3 "http://mybatis.org/dtd/mybatis-3-config.dtd"> 4 <configuration> 5 <!-- 配置属性 --> 6 <properties resource="config.properties" /> 7 <!-- 设置实体类别名 _ *** --> 8 <typeAliases> 9 <typeAlias alias="Entity" type="mybatis.entity.Entity" /> 10 </typeAliases> 11 <!-- 配置数据库连接环境(支持多个数据库连接) --> 12 <environments default="development"> 13 <!-- 数据库连接 --> 14 <environment id="development"> 15 <!-- 配置事务管理器，JDBC：按照jdbc方式处理事务 --> 16 <transactionManager type="JDBC" /> 17 <!-- 配置数据库连接信息，POOLED：表示以数据库连接池的形式连接数据库 --> 18 <dataSource type="POOLED"> 19 <property name="driver" value="${driver}" /> 20 <property name="url" value="jdbc:mysql://localhost:3306/mybatis?serverTimezone=Asia/Shanghai&allowMultiQueries=true" /> 21 <property name="username" value="${username}" /> 22 <property name="password" value="${password}" /> 23 <!-- 在任意时间可以存在的活动（也就是正在使用）连接数量，默认值：10 --> 24 <property name="poolMaximumActiveConnections" value="300" /> 25 <!-- 任意时间可能存在的空闲连接数 默认是5,最好设置为0,否则可能会崩溃掉 --> 26 <property name="poolMaximumIdleConnections" value="0" /> 27 <!-- 在被强制返回之前，池中连接被检出（checked out）时间，默认值：20000 毫秒（即 20 秒） --> 28 <property name="poolMaximumCheckoutTime" value="20000" /> 29 <!-- 这是一个底层设置，如果获取连接花费的相当长的时间，它会给连接池打印状态日志并重新尝试获取一个连接（避免在误配置的情况下一直安静的失败），默认值：20000 30 毫秒（即 20 秒）。 --> 31 <property name="poolTimeToWait" value="20000" /> 32 <!-- 是否启用侦测查询。若开启，也必须使用一个可执行的 SQL 语句设置 poolPingQuery 属性（最好是一个非常快的 SQL），默认值：false。 --> 33 <property name="poolPingEnabled" value="true" /> 34 <!-- 配置 poolPingQuery 的使用频度。这可以被设置成匹配具体的数据库连接超时时间，来避免不必要的侦测，默认值：0（即所有连接每一时刻都被侦测 35 — 当然仅当 poolPingEnabled 为 true 时适用） --> 36 <property name="poolPingConnectionsNotUsedFor" 37 value="3600000" /> 38 <!-- 发送到数据库的侦测查询，用来检验连接是否处在正常工作秩序中并准备接受请求。默认是“NO PING QUERY SET”，这会导致多数数据库驱动失败时带有一个恰当的错误消息。 --> 39 <property name="poolPingQuery" value="select 1" /> 40 </dataSource> 41 </environment> 42 </environments> 43 <!-- 加载映射文件 --> 44 <mappers> 45 <mapper resource="mybatis/entity/EntityMapper.xml" /> 46 </mappers> 47 </configuration>
1 <project xmlns="http://maven.apache.org/POM/4.0.0" 2 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 3 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> 4 <modelVersion>4.0.0</modelVersion> 5 <groupId>mybatis</groupId> 6 <artifactId>MyBatis_3</artifactId> 7 <version>0.0.1-SNAPSHOT</version> 8 <dependencies> 9 <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> 10 <dependency> 11 <groupId>mysql</groupId> 12 <artifactId>mysql-connector-java</artifactId> 13 <version>8.0.11</version> 14 </dependency> 15 <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis --> 16 <dependency> 17 <groupId>org.mybatis</groupId> 18 <artifactId>mybatis</artifactId> 19 <version>3.0.4</version> 20 </dependency> 21 <!-- https://mvnrepository.com/artifact/junit/junit --> 22 <dependency> 23 <groupId>junit</groupId> 24 <artifactId>junit</artifactId> 25 <version>4.12</version> 26 <scope>test</scope> 27 </dependency> 28 <!-- https://mvnrepository.com/artifact/log4j/log4j --> 29 <dependency> 30 <groupId>log4j</groupId> 31 <artifactId>log4j</artifactId> 32 <version>1.2.12</version> 33 </dependency> 34 35 </dependencies> 36 </project>
1 #key=value 2 driver=com.mysql.cj.jdbc.Driver 3 url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=Asia/Shanghai&allowMultiQueries=true 4 username=root 5 password=123456
Maven依赖：log4j。
