EmployeeMapperDynamicSql.java
package com.gong.mybatis.mapper; import java.util.List; import java.util.Map; import org.apache.ibatis.annotations.MapKey; import org.apache.ibatis.annotations.Param; import com.gong.mybatis.bean.Employee; public interface EmployeeMapperDynamicSql { public List<Employee> getEmpByConditionIf(Employee employee); public void addEmp(@Param("emps") List<Employee> employee); }
EmployeeMapperDynamicSql.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.gong.mybatis.mapper.EmployeeMapperDynamicSql"> <!-- 查询,要查那个就带上那个条件 --> <select id="getEmpByConditionIf" resultType="com.gong.mybatis.bean.Employee"> select * from tbl_employee <where> <choose> <when test="id!=null"> id=#{id} </when> <when test="lastName!=null"> last_name like #{lastName} </when> <when test="email!=null"> email=#{email} </when> <otherwise> </otherwise> </choose> </where> </select> <insert id="addEmp"> insert into tbl_employee(last_name,gender,email,d_id) values <foreach collection="emps" item="emp" separator=","> (#{emp.lastName},#{emp.gender},#{emp.email},#{emp.dept.id}) </foreach> </insert> </mapper>
第一种方式如上所示:insert into tbl_employee(last_name,gender,email,d_id) values (?,?,?,?) , (?,?,?,?)
进行测试:
package com.gong.mybatis.test; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.Arrays; import java.util.HashMap; import java.util.List; import java.util.Map; 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.Test; import com.gong.mybatis.bean.Department; import com.gong.mybatis.bean.Employee; import com.gong.mybatis.mapper.EmployeeMapperDynamicSql; public class TestMybatis3 { public SqlSessionFactory getSqlSessionFactory() throws IOException { String resource = "mybatis-config.xml"; InputStream is = Resources.getResourceAsStream(resource); return new SqlSessionFactoryBuilder().build(is); } @Test public void test() throws IOException { SqlSessionFactory sqlSessionFactory = getSqlSessionFactory(); SqlSession openSession = sqlSessionFactory.openSession(); try { EmployeeMapperDynamicSql mapper = openSession.getMapper(EmployeeMapperDynamicSql.class); List<Employee> emps = new ArrayList<>(); Department d1 = new Department(1); Employee e1 = new Employee(null,"manwang","1","manwang@qq.com",d1); Department d2 = new Department(2); Employee e2 = new Employee(null,"timo","0","timo@qq.com",d2); emps.add(e1); emps.add(e2); mapper.addEmp(emps); List<Employee> es = mapper.getEmpByConditionIf(new Employee()); for(Employee e:es) { System.out.println(e); } openSession.commit(); } finally { openSession.close(); } } }
输出结果:
DEBUG 01-21 21:16:38,183 ==> Preparing: insert into tbl_employee(last_name,gender,email,d_id) values (?,?,?,?) , (?,?,?,?) (BaseJdbcLogger.java:145) DEBUG 01-21 21:16:38,261 ==> Parameters: manwang(String), 1(String), manwang@qq.com(String), 1(Integer), timo(String), 0(String), timo@qq.com(String), 2(Integer) (BaseJdbcLogger.java:145) DEBUG 01-21 21:16:38,374 <== Updates: 2 (BaseJdbcLogger.java:145) DEBUG 01-21 21:16:38,385 ==> Preparing: select * from tbl_employee (BaseJdbcLogger.java:145) DEBUG 01-21 21:16:38,386 ==> Parameters: (BaseJdbcLogger.java:145) DEBUG 01-21 21:16:38,430 <== Total: 6 (BaseJdbcLogger.java:145) Employee [id=1, lastName=dema, gender=1, email=dema@qq.com, dept=null] Employee [id=2, lastName=jack, gender=1, email=675544321@qq.com, dept=null] Employee [id=3, lastName=小红, gender=0, email=xiaohong@qq.com, dept=null] Employee [id=4, lastName=小明, gender=0, email=xiaoming@qq.com, dept=null] Employee [id=5, lastName=manwang, gender=1, email=manwang@qq.com, dept=null] Employee [id=6, lastName=timo, gender=0, email=timo@qq.com, dept=null]
第二种方式需要在配置连接数据源时使用:
jdbc.url=jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true
然后在xml文件中:
<insert id="addEmp"> <foreach collection="emps" item="emp" separator=";"> insert into tbl_employee(last_name,gender,email,d_id) values (#{emp.lastName},#{emp.gender},#{emp.email},#{emp.dept.id}) </foreach> </insert>
这种方式的sql形如:insert into tbl_employee(last_name,gender,email,d_id) values (?,?,?,?) ; insert into tbl_employee(last_name,gender,email,d_id) values (?,?,?,?)
结果:
DEBUG 01-21 21:34:32,747 ==> Preparing: insert into tbl_employee(last_name,gender,email,d_id) values (?,?,?,?) ; insert into tbl_employee(last_name,gender,email,d_id) values (?,?,?,?) (BaseJdbcLogger.java:145) DEBUG 01-21 21:34:32,823 ==> Parameters: manwang(String), 1(String), manwang@qq.com(String), 1(Integer), timo(String), 0(String), timo@qq.com(String), 2(Integer) (BaseJdbcLogger.java:145) DEBUG 01-21 21:34:32,889 <== Updates: 1 (BaseJdbcLogger.java:145) DEBUG 01-21 21:34:32,900 ==> Preparing: select * from tbl_employee (BaseJdbcLogger.java:145) DEBUG 01-21 21:34:32,902 ==> Parameters: (BaseJdbcLogger.java:145) DEBUG 01-21 21:34:32,966 <== Total: 8 (BaseJdbcLogger.java:145) Employee [id=1, lastName=dema, gender=1, email=dema@qq.com, dept=null] Employee [id=2, lastName=jack, gender=1, email=675544321@qq.com, dept=null] Employee [id=3, lastName=小红, gender=0, email=xiaohong@qq.com, dept=null] Employee [id=4, lastName=小明, gender=0, email=xiaoming@qq.com, dept=null] Employee [id=5, lastName=manwang, gender=1, email=manwang@qq.com, dept=null] Employee [id=6, lastName=timo, gender=0, email=timo@qq.com, dept=null] Employee [id=7, lastName=manwang, gender=1, email=manwang@qq.com, dept=null] Employee [id=8, lastName=timo, gender=0, email=timo@qq.com, dept=null]
mybatis动态sql之使用foreach进行批量插入的两种方式
原文:https://www.cnblogs.com/xiximayou/p/12226003.html