mysql execute 新增,MyBatis 批量新增数据

3 Idiots

传统JDBC批量插入方法：

1.Java代码中使用For循环直接插入SQL数据如：execute()或executeUpdate()方法。

2.借助于Statement、Prestatement对象的批处理方法addBatch

public class jdbcUtil {

// 处理数据库事务，批量操作需要手动提交事务

public static void commit(Connection connection){

if (null!=connection){

try {

connection.commit();

}catch (SQLException e){

e.printStackTrace();

}

}

}

//事务的回滚

public static void rollback(Connection connection){

if (null!=connection){

try {

connection.rollback();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

// 事务的开始

public static void begin(Connection connection) {

if (null != connection) {

try {

connection.setAutoCommit(false);

} catch (SQLException e) {

e.printStackTrace();

}

}

}

/**

* 获取连接方法

* @Param

* @Return

*/

public static Connection getConnection() throws Exception {

Properties properties = new Properties();

InputStream is = jdbcUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");

//jdbc文件内容以 流 的方式加载到properties文件中

properties.load(is);

String driver=properties.getProperty("driver");

String username=properties.getProperty("username");

String password=properties.getProperty("password");

String url=properties.getProperty("url");

System.out.println(driver+":"+password);

Class.forName(driver);

return DriverManager.getConnection(url,username,password);

}

/**

* 通用的关闭资源的方法

* @Param connection

* @Param statement

* @Param resultSet

*/

public static void closeResources(Connection connection, Statement statement, ResultSet resultSet){

if (null!=connection){

try {

connection.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

if (null!=statement){

try {

statement.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

if (null!=resultSet){

try {

resultSet.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

}

使用for循环插入

public class BatchTestOne {

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

Connection connection=null;

PreparedStatement preparedStatement=null;

connection = jdbcUtil.getConnection();

jdbcUtil.begin(connection); //autocommit false

String sql = "insert into t_user(username,password) values(?,?)";

preparedStatement=connection.prepareStatement(sql);

long beginTime = System.currentTimeMillis();

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

preparedStatement.setString(1,"user"+(i+1));

preparedStatement.setString(2,"pwd"+(i+1));

preparedStatement.executeUpdate();

}

jdbcUtil.commit(connection);

long endTime = System.currentTimeMillis();

System.out.println("total time:"+(endTime-beginTime));//4150

}

}

使用addBatch批处理方式插入

public class BatchTestTwo {

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

Connection connection=null;

PreparedStatement preparedStatement=null;

connection = jdbcUtil.getConnection();

jdbcUtil.begin(connection);

String sql = "insert into t_user(username,password) values(?,?)";

preparedStatement=connection.prepareStatement(sql);

long beginTime = System.currentTimeMillis();

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

preparedStatement.setString(1,"batch"+(i+1));

preparedStatement.setString(2,"num"+(i+1));

preparedStatement.addBatch();

if ((i+1)%1000==0){

preparedStatement.executeBatch();

preparedStatement.clearBatch();

}

}

jdbcUtil.commit(connection);

long endTime = System.currentTimeMillis();

System.out.println("total time:"+(endTime-beginTime));//1817

}

}

可以看出：使用批处理的方式要比普通的for循环要快很多。mybatis封装了jdbc，所以mybatis的批处理方式类似于addBatch

MyBatis进行批量插入的方法

MySQL添加多条数据的方式

insert into person(username,email,gender) VALUES("zhangsan","zhangsan@163.com","F"),("lisi","lisi@163.com","F")

或者

insert into person(username,email,gender) VALUES("tom","zhangsan@163.com","F");

insert into person(username,email,gender) VALUES("jerry","lisi@163.com","F")

1.借助foreach标签使用 insert into table values

public class Person {

private Integer id;

private String username;

private String email;

private String gender;

public Person(String username, String email, String gender) {

this.id = id;

this.username = username;

this.email = email;

this.gender = gender;

}

}

public interface PersonMapper {

void addPersons(@Param("persons") List persons);

}

insert into person(username,email,gender) VALUES

(#{person.username},#{person.email},#{person.gender})

/p>

PUBLIC "-//mybatis.org//DTD Config 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-config.dtd">

测试

public class MyBatisTest {

public static SqlSessionFactory sqlSessionFactory = null;

public static SqlSessionFactory getSqlSessionFactory() {

if (sqlSessionFactory == null) {

String resource = "mybatis-config.xml";

try {

Reader reader = Resources.getResourceAsReader(resource);

sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);

} catch (IOException e) {

e.printStackTrace();

}

}

return sqlSessionFactory;

}

public void processMybatisBatch()

{

SqlSession sqlSession = this.getSqlSessionFactory().openSession();

PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class);

List persons=new ArrayList();

for (int i = 0; i <1000 ; i++)

{

Person person=new Person("jerry"+i,"email@"+i,"f");

persons.add(person);

}

personMapper.addPersons(persons);

sqlSession.commit();

}

public static void main(String[] args) {

new MyBatisTest().processMybatisBatch();

}

}

2.借助MySQL数据库连接属性 allowMultiQueries=true

driver=com.mysql.jdbc.Driver

url=jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true

username=root

password=123456

然后，只需要修改上面PersonMapper.xml中的insert语句

insert into person(username,email,gender) VALUES

(#{person.username},#{person.email},#{person.gender})

3.基于SqlSession的ExecutorType进行批量添加

public interface PersonMapper {

void addPerson(Person person);

}

insert into person(username,email,gender) VALUES (#{username},#{email},#{gender})

测试

public void testBatchForExecutor() {

SqlSession sqlSession = this.getSqlSessionFactory().openSession(ExecutorType.BATCH);

PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class);

for (int i = 0; i <10 ; i++)

{

personMapper.addPerson(new Person("Tom","email@"+i,"F"));

}

sqlSession.commit();

sqlSession.close();

}

总结

传统JDBC批量插入方法：

1.利用for循环进行插入的方式存在严重效率问题，需要频繁获取Session，获取连接。

2.使用批处理，代码和SQL的耦合度高，代码量较大。

MyBatis进行批量插入的方法：

1.MySQL下批量保存的两种方式，建议使用第一种

2.借助于Executor的Batch批量添加，可与Spring框架整合，数据量大的时候，我们一般采用这种方式。