1.使用传统jdbc对数据进行插入
传统的jdbc插入包括2种方式,使用for循环和使用jdbc的批处理
(一)for循环
package com.imooc.mybatis.jdbc;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
/**
* 事物的提交
* @param connection
*/
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();
}
}
}
/**
* 事物的开始,JDBC的批量添加必须要手动提交
*/
public static void begin(Connection connection){
if(null!=connection){
try {
connection.setAutoCommit(false);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 获取数据库的连接
*/
public static Connection getConnection() throws IOException, ClassNotFoundException, SQLException {
InputStream inputStream=JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties prop=new Properties();
prop.load(inputStream);
String driverClass=prop.getProperty("jdbc.driver");
String jdbcUrl=prop.getProperty("jdbc.url");
String user=prop.getProperty("jdbc.username");
String password=prop.getProperty("jdbc.password");
/**
* 加载驱动
*/
Class clazz=Class.forName(driverClass);
/**
* 获取数据库连接
*/
Connection connection= DriverManager.getConnection(jdbcUrl,user,password);
return connection;
}
/**
* 关闭资源
*/
public static void closeResources(Connection connection, Statement statement, ResultSet resultSet){
if(null!=resultSet){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(null!=statement){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(null!=connection){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
package com.imooc.mybatis.jdbc;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class BatchTestOne {
public static void main(String[] args) throws Exception {
Connection connection=null;
PreparedStatement preparedStatement=null;
connection=JdbcUtils.getConnection();
/**
* 设置jdbc为自动提交
*/
JdbcUtils.begin(connection);
String sql="insert into t_user(username,address) values(?,?)";
Long startTime=System.currentTimeMillis();
preparedStatement=connection.prepareStatement(sql);
for(int i=0;i<1000;i++){
preparedStatement.setString(1,"罗宾"+i);
preparedStatement.setString(2,"海贼");
preparedStatement.executeUpdate();
}
JdbcUtils.commit(connection);
Long endTime=System.currentTimeMillis();
System.out.println("total:"+(endTime-startTime));
JdbcUtils.closeResources(connection,preparedStatement,null);
}
}
(二)使用Jdbc批处理
package com.imooc.mybatis.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
public class BatchTestTwo {
public static void main(String[] args) throws Exception{
Connection connection=null;
PreparedStatement preparedStatement=null;
connection=JdbcUtils.getConnection();
JdbcUtils.begin(connection);
String sql="insert into t_user(username,address) values(?,?)";
preparedStatement=connection.prepareStatement(sql);
Long startTime=System.currentTimeMillis();
for(int i=0;i<10000;i++){
preparedStatement.setString(1,"炮姐"+i);
preparedStatement.setString(2,"某科学的超电磁炮");
//将数据添加到批处理当中
preparedStatement.addBatch();
if(((i+1)%1000)==0){
//执行批处理
preparedStatement.executeBatch();
//清除批处理缓存
preparedStatement.clearBatch();
}
}
JdbcUtils.commit(connection);
Long endTime=System.currentTimeMillis();
System.out.println("total:"+(endTime-startTime));
JdbcUtils.closeResources(connection,preparedStatement,null);
}
}
2.Mybatis批处理
(一)mybatis通过foreach标签实现批量插入
package com.imooc.mybatis.dao;
import com.imooc.mybatis.entity.Person;
import java.util.List;
public interface PersonMapper {
public void deletePerson(Integer id);
public int addPerson(List<Person> lists);
}
**********************************************************
<insert id="addPerson" >
insert into person(username,email,gender) values
<foreach collection="collection" item="person" separator="," >
(#{person.username},#{person.email},#{person.gender})
</foreach>
</insert>
**********************************************************
public void addPerson(){
SqlSession sqlSession=getSqlSessionFactory().openSession();
PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
List<Person> list=new ArrayList<Person>();
for(int i=0;i<5;i++){
list.add(new Person("桂言叶"+i,"yanye@163.com","女"));
}
int count=personMapper.addPerson(list);
System.out.println(count);
sqlSession.commit();
sqlSession.close();
}
(二)mybatis通过mysql数据库连接属性allowMultiQueries=true批量插入
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true
jdbc.username=root
jdbc.password=bzq18107155240
*******************************************************************************
<insert id="addPerson" >
<foreach collection="list" item="person" separator=";">
insert into person(username,email,gender) values
(#{person.username},#{person.email},#{person.gender})
</foreach>
</insert>
如果不在jdbc的url后面加？allowMultiQueries=true，采用这种批量更新会报错的。SQL syntax
(三)使用ExecutorType.BATCH,让sqlSqssion有批处理的能力
package com.imooc.mybatis.dao;
import com.imooc.mybatis.entity.Person;
public interface PersonMapper {
public int batchPerson(Person person);
}
*************************************************************
<insert id="batchPerson">
insert into person(username,email,gender) values (#{username},#{email},#{gender})
</insert>
***********************************************************
public void BatchPerson(){
/**
* 当添加了这个参数的时候，可以让SqlSession有批处理的能力
*/
SqlSession sqlSession=getSqlSessionFactory().openSession(ExecutorType.BATCH);
PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
for (int i = 0; i < 10000; i++) {
personMapper.batchPerson(new Person("庞斑"+i,"pban@163.com","男"));
}
sqlSession.commit();
sqlSession.close();
}
网友评论