mybatis mysql autoreconnect=true_2019-05-19_Mybatis关于mysql批量插入学习

最新推荐文章于 2022-11-17 13:22:50 发布

瞎闹腾i

最新推荐文章于 2022-11-17 13:22:50 发布

阅读量242

点赞数

文章标签： mybatis mysql autoreconnect=true

本文链接：https://blog.csdn.net/weixin_32260467/article/details/113417322

版权

Mybatis关于mysql批量插入学习

1.概述

对mybatis常用的操作命令做一下总结,数据库基于mysql。后面章节会涉及数据的批量插入、批量更新和更新后返回主键ID等内容。

1.数据库信息

1.1.表结构定义

CREATE TABLE users (

id int(11) NOT NULL AUTO_INCREMENT,

NAME varchar(20) DEFAULT NULL,

age int(11) DEFAULT NULL,

remark varchar(200) DEFAULT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB AUTO_INCREMENT=682 DEFAULT CHARSET=utf8;

1.2.数据库配置db.properties

mysql.driver=com.mysql.jdbc.Driver

需要开启多行执行功能

mysql.url=jdbc:mysql://localhost:3306/mybatis?userUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true

mysql.username=root

mysql.password=123456

1.3.实体类

package com.tech.ability.mybatis.Entity;

/**

Created by kikop on 2019/1/17.

public class MyUser {

//实体类的属性和表的字段名称一一对应

private int id;

private String name;

private int age;

public String getRemark() {

return remark;

}

public void setRemark(String remark) {

this.remark = remark;

}

private String remark;

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public int getAge() {

return age;

}

public void setAge(int age) {

this.age = age;

}

@Override

public String toString() {

return "MyUser{" +

"age=" + age +

", id=" + id +

", name='" + name + ''' +

", remark='" + remark + ''' +

'}';

}

2.mybatis mapper接口文件

package com.tech.ability.mybatis.Mapper;

import com.alibaba.fastjson.JSONObject;

import org.apache.ibatis.annotations.Insert;

import org.apache.ibatis.annotations.SelectKey;

import org.springframework.data.repository.query.Param;

/**

Created by kikop on 2019/5/19.

public interface MyUserMapper {

}

3.mybatis sqlmap映射文件

insert into users(name,age,remark)

( select '

math?formula=%7BcurrentRow.name%7D'%2C%23%7BcurrentRow.age%7D%2C' {currentRow.remark}')

insert into users(name,age,remark)

values('${name}',#{age},'${remark}')

4.mybatis工具类

package com.tech.ability.mybatis;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.ExecutorType;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;

import java.io.InputStream;

/**

Created by kikop on 2019/5/19.

public class mybatisUtils {

//mybatis核心配置文件

private static final String mybatis_configPath = "mybatisconfig.xml";

/**

* 读取SqlSession

* @return

public static SqlSession getSqlSession(ExecutorType execType, boolean isAutoCommit) {

SqlSession sqlSession = null;

try {

//1.读取mybatis的配置文件

//String resource = "mybatisconfig.xml";

//2.构建sqlSession的工厂

//使用类加载器加载 mybatis 的 classspath中配置文件

InputStream inputStream = null;

// InputStream inputStream = mybatisTest.class.getClassLoader().getResourceAsStream(resource);

inputStream = Resources.getResourceAsStream(mybatis_configPath);

SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

//2.构建sqlSession的工厂

//使用MyBatis提供的Resources类加载mybatis的配置文件(它也加载关联的映射文件)

//Reader reader = Resources.getResourceAsReader(resource);

//SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);

//3.创建能执行映射文件中sql的sqlSession

// sqlSession = sessionFactory.openSession(isAutoCommit);

sqlSession = sessionFactory.openSession(execType, isAutoCommit);

} catch (IOException e) {

e.printStackTrace();

}

return sqlSession;

}

/**

* 关闭session(回收连接池必须)

* @param session

public static void closeSqlSession(SqlSession session) {

session.close();

}

/**

* 关闭inputStream

* @param inputStream

public static void closeInputSream(InputStream inputStream) {

try {

inputStream.close();

} catch (IOException e) {

e.printStackTrace();

}

5.测试代码

5.1.测试1

package com.tech.ability.mybatis;

import com.alibaba.fastjson.JSONObject;

import com.tech.ability.mybatis.Entity.MyUser;

import org.apache.ibatis.session.ExecutorType;

import org.apache.ibatis.session.SqlSession;

import org.apache.logging.log4j.LogManager;

import org.apache.logging.log4j.Logger;

import java.util.ArrayList;

import java.util.List;

/**

Created by kikop on 2019/1/17.

public class mybatisTest {

private static final Logger logger = LogManager.getLogger(mybatisTest.class);

/**

* 批量插入实现1

public static void batchInsertUserInfoListByUnionAllTest() {

//1.构建sqlSession

SqlSession session = mybatisUtils.getSqlSession(ExecutorType.SIMPLE, false);

//2.映射sql的标识字符串

StringBuffer statement = new StringBuffer();

statement.append("com.tech.ability.mybatis.Mapper.MyUserMapper"); //mapper标签的namespace属性的值

statement.append(".batchInsertUserInfoListByUnionAll"); //select标签的id属性值

//3.定义查询条件

List idList = new ArrayList<>();

for (int i = 0; i < 100; i++) {

JSONObject jsonObject = new JSONObject();

jsonObject.put("name", String.format("name_%d", i));

jsonObject.put("age", i);

jsonObject.put("remark", String.format("remark_%d", i));

idList.add(jsonObject);

}

JSONObject requestJsonObject = new JSONObject();

requestJsonObject.put("idList", idList);

//4.执行查询返回一个唯一user对象的sql

int updateResult = session.insert(statement.toString(), requestJsonObject);

System.out.println("[insertUserXml]影响的行数updateResult:" + updateResult + "返回值respID:" + requestJsonObject.get("respID"));

if (updateResult > 0) {

session.commit();

} else {

session.rollback();

}

//5.资源释放(连接池回收)

mybatisUtils.closeSqlSession(session);

}

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/E:/workfolder/JavaWebSoft/maven/LocalMavenRep/org/apache/logging/log4j/log4j-slf4j-impl/2.1/log4j-slf4j-impl-2.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/E:/workfolder/JavaWebSoft/maven/LocalMavenRep/org/slf4j/slf4j-log4j12/1.6.1/slf4j-log4j12-1.6.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]

2019-05-19 17:06:11.931 DEBUG [main][LogFactory.java:124] - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.

2019-05-19 17:06:11.945 DEBUG [main][PooledDataSource.java:304] - PooledDataSource forcefully closed/removed all connections.

2019-05-19 17:06:11.946 DEBUG [main][PooledDataSource.java:304] - PooledDataSource forcefully closed/removed all connections.

2019-05-19 17:06:11.947 DEBUG [main][PooledDataSource.java:304] - PooledDataSource forcefully closed/removed all connections.

2019-05-19 17:06:12.132 DEBUG [main][JdbcTransaction.java:129] - Opening JDBC Connection

2019-05-19 17:06:12.591 DEBUG [main][PooledDataSource.java:378] - Created connection 898694235.

2019-05-19 17:06:12.591 DEBUG [main][JdbcTransaction.java:95] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@3590fc5b]

2019-05-19 17:06:12.595 DEBUG [main][BaseJdbcLogger.java:132] - ooo Using Connection [com.mysql.jdbc.JDBC4Connection@3590fc5b]

2019-05-19 17:06:12.600 DEBUG [main][BaseJdbcLogger.java:132] - ==> Preparing: insert into users(name,age,remark) ( select 'name_0',?,'remark_0') union all ( select 'name_1',?,'remark_1') union all ( select 'name_2',?,'remark_2') union all ( select 'name_3',?,'remark_3') union all ( select 'name_4',?,'remark_4') union all ( select 'name_5',?,'remark_5') union all ( select 'name_6',?,'remark_6') union all ( select 'name_7',?,'remark_7') union all ( select 'name_8',?,'remark_8') union all ( select 'name_9',?,'remark_9') union all ( select 'name_10',?,'remark_10') union all ( select 'name_11',?,'remark_11') union all ( select 'name_12',?,'remark_12') union all ( select 'name_13',?,'remark_13') union all ( select 'name_14',?,'remark_14') union all ( select 'name_15',?,'remark_15') union all ( select 'name_16',?,'remark_16') union all ( select 'name_17',?,'remark_17') union all ( select 'name_18',?,'remark_18') union all ( select 'name_19',?,'remark_19') union all ( select 'name_20',?,'remark_20') union all ( select 'name_21',?,'remark_21') union all ( select 'name_22',?,'remark_22') union all ( select 'name_23',?,'remark_23') union all ( select 'name_24',?,'remark_24') union all ( select 'name_25',?,'remark_25') union all ( select 'name_26',?,'remark_26') union all ( select 'name_27',?,'remark_27') union all ( select 'name_28',?,'remark_28') union all ( select 'name_29',?,'remark_29') union all ( select 'name_30',?,'remark_30') union all ( select 'name_31',?,'remark_31') union all ( select 'name_32',?,'remark_32') union all ( select 'name_33',?,'remark_33') union all ( select 'name_34',?,'remark_34') union all ( select 'name_35',?,'remark_35') union all ( select 'name_36',?,'remark_36') union all ( select 'name_37',?,'remark_37') union all ( select 'name_38',?,'remark_38') union all ( select 'name_39',?,'remark_39') union all ( select 'name_40',?,'remark_40') union all ( select 'name_41',?,'remark_41') union all ( select 'name_42',?,'remark_42') union all ( select 'name_43',?,'remark_43') union all ( select 'name_44',?,'remark_44') union all ( select 'name_45',?,'remark_45') union all ( select 'name_46',?,'remark_46') union all ( select 'name_47',?,'remark_47') union all ( select 'name_48',?,'remark_48') union all ( select 'name_49',?,'remark_49') union all ( select 'name_50',?,'remark_50') union all ( select 'name_51',?,'remark_51') union all ( select 'name_52',?,'remark_52') union all ( select 'name_53',?,'remark_53') union all ( select 'name_54',?,'remark_54') union all ( select 'name_55',?,'remark_55') union all ( select 'name_56',?,'remark_56') union all ( select 'name_57',?,'remark_57') union all ( select 'name_58',?,'remark_58') union all ( select 'name_59',?,'remark_59') union all ( select 'name_60',?,'remark_60') union all ( select 'name_61',?,'remark_61') union all ( select 'name_62',?,'remark_62') union all ( select 'name_63',?,'remark_63') union all ( select 'name_64',?,'remark_64') union all ( select 'name_65',?,'remark_65') union all ( select 'name_66',?,'remark_66') union all ( select 'name_67',?,'remark_67') union all ( select 'name_68',?,'remark_68') union all ( select 'name_69',?,'remark_69') union all ( select 'name_70',?,'remark_70') union all ( select 'name_71',?,'remark_71') union all ( select 'name_72',?,'remark_72') union all ( select 'name_73',?,'remark_73') union all ( select 'name_74',?,'remark_74') union all ( select 'name_75',?,'remark_75') union all ( select 'name_76',?,'remark_76') union all ( select 'name_77',?,'remark_77') union all ( select 'name_78',?,'remark_78') union all ( select 'name_79',?,'remark_79') union all ( select 'name_80',?,'remark_80') union all ( select 'name_81',?,'remark_81') union all ( select 'name_82',?,'remark_82') union all ( select 'name_83',?,'remark_83') union all ( select 'name_84',?,'remark_84') union all ( select 'name_85',?,'remark_85') union all ( select 'name_86',?,'remark_86') union all ( select 'name_87',?,'remark_87') union all ( select 'name_88',?,'remark_88') union all ( select 'name_89',?,'remark_89') union all ( select 'name_90',?,'remark_90') union all ( select 'name_91',?,'remark_91') union all ( select 'name_92',?,'remark_92') union all ( select 'name_93',?,'remark_93') union all ( select 'name_94',?,'remark_94') union all ( select 'name_95',?,'remark_95') union all ( select 'name_96',?,'remark_96') union all ( select 'name_97',?,'remark_97') union all ( select 'name_98',?,'remark_98') union all ( select 'name_99',?,'remark_99')

2019-05-19 17:06:12.661 DEBUG [main][BaseJdbcLogger.java:132] - ==> Parameters: 0(Integer), 1(Integer), 2(Integer), 3(Integer), 4(Integer), 5(Integer), 6(Integer), 7(Integer), 8(Integer), 9(Integer), 10(Integer), 11(Integer), 12(Integer), 13(Integer), 14(Integer), 15(Integer), 16(Integer), 17(Integer), 18(Integer), 19(Integer), 20(Integer), 21(Integer), 22(Integer), 23(Integer), 24(Integer), 25(Integer), 26(Integer), 27(Integer), 28(Integer), 29(Integer), 30(Integer), 31(Integer), 32(Integer), 33(Integer), 34(Integer), 35(Integer), 36(Integer), 37(Integer), 38(Integer), 39(Integer), 40(Integer), 41(Integer), 42(Integer), 43(Integer), 44(Integer), 45(Integer), 46(Integer), 47(Integer), 48(Integer), 49(Integer), 50(Integer), 51(Integer), 52(Integer), 53(Integer), 54(Integer), 55(Integer), 56(Integer), 57(Integer), 58(Integer), 59(Integer), 60(Integer), 61(Integer), 62(Integer), 63(Integer), 64(Integer), 65(Integer), 66(Integer), 67(Integer), 68(Integer), 69(Integer), 70(Integer), 71(Integer), 72(Integer), 73(Integer), 74(Integer), 75(Integer), 76(Integer), 77(Integer), 78(Integer), 79(Integer), 80(Integer), 81(Integer), 82(Integer), 83(Integer), 84(Integer), 85(Integer), 86(Integer), 87(Integer), 88(Integer), 89(Integer), 90(Integer), 91(Integer), 92(Integer), 93(Integer), 94(Integer), 95(Integer), 96(Integer), 97(Integer), 98(Integer), 99(Integer)

2019-05-19 17:06:12.665 DEBUG [main][BaseJdbcLogger.java:132] - <== Updates: 100

2019-05-19 17:06:12.666 DEBUG [main][JdbcTransaction.java:66] - Committing JDBC Connection [com.mysql.jdbc.JDBC4Connection@3590fc5b]

2019-05-19 17:06:12.668 DEBUG [main][JdbcTransaction.java:117] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@3590fc5b]

2019-05-19 17:06:12.669 DEBUG [main][JdbcTransaction.java:85] - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@3590fc5b]

2019-05-19 17:06:12.670 DEBUG [main][PooledDataSource.java:332] - Returned connection 898694235 to pool.

5.2.测试2

/**

* 批量插入实现2

* Batch返回值:固定

public static void batchInsertUserInfoListByExecutorBachTest2() {

//1.构建sqlSession

SqlSession session = mybatisUtils.getSqlSession(ExecutorType.BATCH, false);

//2.映射sql的标识字符串

StringBuffer statement = new StringBuffer();

statement.append("com.tech.ability.mybatis.Mapper.MyUserMapper"); //mapper标签的namespace属性的值

statement.append(".batchInsertUserInfoListByExecutorBach"); //select标签的id属性值

try {

//3.定义查询条件

for (int i = 0; i < 100; i++) {

JSONObject jsonObject = new JSONObject();

jsonObject.put("name", String.format("name_%d", i));

jsonObject.put("age", i);

jsonObject.put("remark", String.format("remark_%d", i));

// handler.parameterize(stmt);

// handler.batch(stmt);

// return -2147482646;

//4.执行查询返回一个唯一user对象的sql

int updateResult = session.insert(statement.toString(), jsonObject);

System.out.println("[insertUserXml]影响的行数updateResult:" + updateResult + "返回值respID:" + jsonObject.get("respID"));

}

session.commit();

} catch (Exception ex) {

ex.printStackTrace();

session.rollback();

} finally {

//5.资源释放(连接池回收)

mybatisUtils.closeSqlSession(session);

}

6.调用

public static void main(String[] args) throws Exception {

//getUserInfoByIDTest();

//getUserInfoByInTest();

//getUserInfoByInnerForeachTest();

//batchUpdateUserInfoListManualTest();

//batchUpdateUserInfoListAutoTest();

//batchInsertUserInfoListByUnionAllTest();

//batchInsertUserInfoListByExecutorBachTest2();

//insertUserXmlTest();

batchInsertUserInfoListByUnionAllTest();

}

2019-05-19 22:31:24.436 DEBUG [main][LogFactory.java:124] - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.

2019-05-19 22:31:24.449 DEBUG [main][PooledDataSource.java:304] - PooledDataSource forcefully closed/removed all connections.

2019-05-19 22:31:24.450 DEBUG [main][PooledDataSource.java:304] - PooledDataSource forcefully closed/removed all connections.

2019-05-19 22:31:24.451 DEBUG [main][PooledDataSource.java:304] - PooledDataSource forcefully closed/removed all connections.

2019-05-19 22:31:24.576 DEBUG [main][JdbcTransaction.java:129] - Opening JDBC Connection

2019-05-19 22:31:24.915 DEBUG [main][PooledDataSource.java:378] - Created connection 1786294176.

2019-05-19 22:31:24.918 DEBUG [main][JdbcTransaction.java:95] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@6a78afa0]

2019-05-19 22:31:24.921 DEBUG [main][BaseJdbcLogger.java:132] - ooo Using Connection [com.mysql.jdbc.JDBC4Connection@6a78afa0]

2019-05-19 22:31:24.923 DEBUG [main][BaseJdbcLogger.java:132] - ==> Preparing: insert into users(name,age,remark) values('name_0',?,'remark_0')

2019-05-19 22:31:24.985 DEBUG [main][BaseJdbcLogger.java:132] - ==> Parameters: 0(Integer)