环境准备(非核心方法)
创建表
创建表的sql(下表是基于Oracle创建的)
CREATE TABLE "SYSTEM"."STUDENT" ("ID" NUMBER(10, 0),"NAME" VARCHAR2(20 BYTE),"ADDRES" CLOB,PRIMARY KEY ( "ID" )USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOLDEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT )TABLESPACE "SYSTEM"ENABLE
)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGSTORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOLDEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT )
TABLESPACE "SYSTEM"LOB ( "ADDRES" ) STORE AS BASICFILE (TABLESPACE "SYSTEM"ENABLE STORAGE IN ROWCHUNK 8192RETENTIONNOCACHE LOGGINGSTORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOLDEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT ));
表结构-具体使用什么数据库都行以自己的项目为准
创建集成mybatis的springboot 项目(数据库是基于Oracle的)
pom
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.5.0</version><relativePath/> <!-- lookup parent from repository --></parent><groupId>com.example</groupId><artifactId>demo</artifactId><version>0.0.1-SNAPSHOT</version><name>mybatis-demo</name><description>测试mybaits</description><properties><java.version>1.8</java.version></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><!-- MyBatis --><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>2.2.0</version></dependency><!-- Oracle JDBC --><dependency><groupId>com.oracle.database.jdbc</groupId><artifactId>ojdbc8</artifactId><version>12.2.0.1</version></dependency></dependencies></project>
application
spring.datasource.url=jdbc:oracle:thin:@localhost:1521:xe
spring.datasource.username=system
spring.datasource.password=oracle
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
制造假数据
代码
student表操作类
@Service
public class studentDaoTest {@Autowiredprivate SqlSessionTemplate sqlSessionTemplate;public void inster(){Connection connection = null;SqlSessionFactory sqlSessionFactory = sqlSessionTemplate.getSqlSessionFactory();SqlSession sqlSession = sqlSessionFactory.openSession();connection=sqlSession.getConnection();StringBuilder sql = new StringBuilder();sql.append("INSERT INTO student (id, name, addres) VALUES (?, ?, ?)");try (PreparedStatement statement = connection.prepareStatement(sql.toString())) {for (int i = 1; i <= 100; i++) {statement.setInt(1, i);statement.setString(2, "Name " + i);statement.setString(3, "Addres " + i);statement.addBatch();}statement.executeBatch();}catch (Exception e){System.out.println(e.getMessage());}}}
测试类
@SpringBootTest
class MybatisDemoApplicationTests {@Autowiredprivate studentDaoTest studentDaoTest;@Testvoid contextLoads() {studentDaoTest.inster();}}
结果
批量更新sql拼接(核心方法)
更新方法
public String testSQlAppendBatch(List<Map<String,Object>> prms){Connection connection = null;//具体的拼接参数(按顺序添加到此集合里)List<Object> params = new ArrayList<>();SqlSessionFactory sqlSessionFactory = sqlSessionTemplate.getSqlSessionFactory();SqlSession sqlSession = sqlSessionFactory.openSession();connection = sqlSession.getConnection();//获取拼接好的sqlString sql = sqlAppend(prms, params);try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {//设置预处理参数sqlAppendtemp(preparedStatement,params);//执行更新int i = preparedStatement.executeUpdate();System.out.println("jdbc更新成功"+i+"条数据");} catch (Exception e) {System.out.println(e.getMessage());} finally {if (connection != null) {try {connection.close();} catch (SQLException throwables) {throwables.printStackTrace();}}}return "成功!";}
拼接sql
// 拼接sqlprivate String sqlAppend(List<Map<String,Object>> temp,List<Object> par){//par 集合里保存-- 参数映射位置和内容(因为arraylist 是有序的),下面使用sqlAppendtemp 结合par 设置参数。String reslut="";StringBuilder sql = new StringBuilder();sql.append("UPDATE student SET ");sql.append("name = CASE ");for (Map<String, Object> stringObjectMap : temp) {if (null!=stringObjectMap.get("name")){sql.append("WHEN id = ? THEN ?");//添加参数par.add(stringObjectMap.get("id"));par.add(stringObjectMap.get("name"));}}sql.append("END, ");//去掉多余的 case endreslut = sql.toString().replace("name = CASE END, ", " ");sql.append("addres = CASE ");for (Map<String, Object> stringObjectMap : temp) {if (null!=stringObjectMap.get("addres")){sql.append("WHEN id = ? THEN ?");//添加参数par.add(stringObjectMap.get("id"));par.add(stringObjectMap.get("addres"));}}sql.append("END, ");//去掉多余的 case endreslut = sql.toString().replace("addres = CASE END, ", " ");sql.append("id = CASE ");for (Map<String, Object> stringObjectMap : temp) {if (null!=stringObjectMap.get("idd")){sql.append("WHEN id = ? THEN ?");//添加参数par.add(stringObjectMap.get("id"));par.add(stringObjectMap.get("idd"));}}sql.append(" END ");//去掉多余的 case endreslut = sql.toString().replace("id = CASE END", " ");//判断是否需要截取最后一个逗号(去除空格)reslut= reslut.trim();String substring = reslut.substring(reslut.length() - 1, reslut.length());if (substring.equals(",")){reslut=reslut.substring(0,reslut.length()-1);}//拼接where 条件String strWhere=" where ";for (Map<String, Object> stringObjectMap : temp) {if (null!=stringObjectMap.get("id")){strWhere+="id = ? OR ";par.add(stringObjectMap.get("id"));}}strWhere=strWhere.trim();String substring1 = strWhere.substring(strWhere.length() - 2, strWhere.length());if (substring1.equals("OR")){strWhere=strWhere.substring(0,strWhere.length()-2);}reslut+=" "+strWhere;return reslut;}
设置参数
//设置预处理参数private void sqlAppendtemp(PreparedStatement preparedStatement,List<Object> parms){try {int index=1;for (Object parm : parms) {preparedStatement.setObject(index++,parm);}} catch (SQLException throwables) {throwables.printStackTrace();}}
测试类
package com.example.demo;import com.example.demo.test.studentDaoTest;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;@SpringBootTest
class MybatisDemoApplicationTests {@Autowiredprivate studentDaoTest studentDaoTest;@Testvoid contextLoads() {List<Map<String,Object>> tem=new ArrayList<>();int temp=1001;for (int i = 1001; i < 1009; i++) {Map<String,Object> h=new HashMap<>();h.put("id",i);h.put("idd",temp);h.put("name",i+"kk");h.put("addres",i+"dd");tem.add(h);temp++;}String s = studentDaoTest.testSQlAppendBatch(tem);System.out.println(s);}}