方式一:使用Statement实现
Connection conn = DBCUtils.getConnection();
Statement st = conn.createStatement();
for(int i = 1;i <= 20000;i++){
String sql = "insert into goods(name)values('name_" + i + "')";
st.execute(sql);
}
方式二:使用PreparedStatement
/**
* 批量插入的方式二:使用PreparedStatement
*/
@Test
public void testInsert1(){
Connection conn = null;
PreparedStatement ps = null;
try {
conn = DBUtil.getConnection("test");
String sql = "insert into goods(name) values (?)";
ps = conn.prepareStatement(sql);
long start = System.currentTimeMillis();
for (int i = 1; i <= 500; i++) {
ps.setString(1,"name" + i);
ps.execute();
}
long end = System.currentTimeMillis();
System.out.println("一共花费的时间为:" + (end - start));//17901
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtil.close(conn,ps,null);
}
}
方式三:攒SQL:addBatch()
1.addBatch()、executeBatch()、clearBatch()
2.mysql服务器默认是关闭批处理的,我们需要通过一个参数,让mysql开启批处理的支持。?rewriteBatchedStatements=true 写在配置文件的url后面
3.使用更新的mysql 驱动:mysql-connector-java-5.1.37-bin.jar
/**
*批量插入的方式三:
* 1.addBatch()、executeBatch()、clearBatch()
* 2.mysql服务器默认是关闭批处理的,我们需要通过一个参数,让mysql开启批处理的支持。
* ?rewriteBatchedStatements=true 写在配置文件的url后面
* 3.使用更新的mysql 驱动:mysql-connector-java-5.1.37-bin.jar
*/
@Test
public void testInsert2(){
Connection conn = null;
PreparedStatement ps = null;
try {
conn = DBUtil.getConnection("");
String sql = "insert into goods(name) values (?)";
ps = conn.prepareStatement(sql);
long start = System.currentTimeMillis();
for (int i = 1; i <= 500; i++) {
ps.setString(1,"name" + i);
//1."攒"sql
ps.addBatch();
if(i % 50 == 0){
//2.执行batch
ps.executeBatch();
//3.清空batch
ps.clearBatch();
}
}
long end = System.currentTimeMillis();
System.out.println("一共花费的时间为:" + (end - start));//378
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtil.close(conn,ps,null);
}
方式四:设置连接不允许自动提交数据(最佳)
/**
* 批量插入的方式四:设置连接不允许自动提交数据
*/
@Test
public void testInsert3(){
Connection conn = null;
PreparedStatement ps = null;
try {
conn = DBUtil.getConnection("");
String sql = "insert into goods(name) values (?)";
ps = conn.prepareStatement(sql);
//设置不允许自动提交数据
conn.setAutoCommit(false);
long start = System.currentTimeMillis();
for (int i = 1; i <= 500; i++) {
ps.setString(1,"name" + i);
//1."攒"sql
ps.addBatch();
if(i % 50 == 0){
//2.执行batch
ps.executeBatch();
//3.清空batch
ps.clearBatch();
}
}
//提交数据
conn.commit();
long end = System.currentTimeMillis();
System.out.println("一共花费的时间为:" + (end - start));//138
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtil.close(conn,ps,null);
}
}
DBUtil:
package loey.DBUtil;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* JDBC工具类,简化JDBC编程
*
*/
public class DBUtil {
/**
* 工具类中的构造方法是私有的
* 因为工具类中的方法都是静态的,直接通过类名去调即可。
*/
private DBUtil() {
}
private static Properties getProperties(){
//ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
//String driver = bundle.getString("driver");
//String url = bundle.getString("url");
//String user = bundle.getString("user");
//String password = bundle.getString("password");
//String sql = bundle.getString("sql");
// 1.读取配置文件中的4个基本信息
Properties pros = new Properties();
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
try {
pros.load(is);
} catch (IOException e) {
e.printStackTrace();
}
return pros;
}
/**
* 静态代码块,类加载的时候执行
* 把注册驱动程序的代码放在静态代码块中,避免多次获取连接对象时重复调用
*/
static{
Properties pros = getProperties();
String driver = pros.getProperty("driver");
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* @return 获取连接
* @throws SQLException
*/
public static Connection getConnection(String database) throws Exception {
Properties pros = getProperties();
String url = pros.getProperty("url");
String user = pros.getProperty("user");
String password = pros.getProperty("password");
Connection conn = DriverManager.getConnection(url + database, user, password);
return conn;
}
public static void close(Connection conn, Statement ps, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
配置文件:
jdbc.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
user=root
password=1127