java 批量插入数据库_从Java应用程序执行批量插入数据库的不同方法

我有一个演示,JDBC批处理

文件：demo.txt

内容

1899942,demo1

1899944,demo2

1899946,demo3

1899948,demo4

插入数据读取文件内容

我的代码：

public class Test2 {

public static void main(String[] args) {

long start = System.currentTimeMillis();

String sql = "insert into mobile_place(number,place) values(?,?)";

int count=0;

PreparedStatement pstmt = null;

Connection conn = JDBCUtil.getConnection();

try {

pstmt = conn.prepareStatement(sql);

InputStreamReader is = new InputStreamReader(new FileInputStream(new File("D:/CC.txt")),"utf-8");

BufferedReader br = new BufferedReader(is);

conn.setAutoCommit(false);

String s1 = null;

String s2 = null;

while(br.readLine() != null){

count++;

String str = br.readLine().toString().trim();

s1 = str.substring(0, str.indexOf(","));

s2 = str.substring(str.indexOf(",")+1,str.length());

pstmt.setString(1, s1);

pstmt.setString(2, s2);

pstmt.addBatch();

if(count%1000==0){

pstmt.executeBatch();

conn.commit();

conn.close();

conn = JDBCUtil.getConnection();

conn.setAutoCommit(false);

pstmt = conn.prepareStatement(sql);

}

System.out.println("insert "+count+"line");

}

if(count%1000!=0){

pstmt.executeBatch();

conn.commit();

}

long end = System.currentTimeMillis();

System.out.println("Total time spent:"+(end-start));

} catch (Exception e) {

e.printStackTrace();

}finally{

try {

pstmt.close();

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

}

//getConnection()//get jdbc Connection

public static Connection getConnection(){

try {

Class.forName("com.mysql.jdbc.Driver");

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

try {

conn = DriverManager.getConnection(url, userName, password);

} catch (SQLException e) {

e.printStackTrace();

}

return conn;

}

第一次说,我希望我能帮忙

我上面的演示使用PreparedStatement [读取数据调用PreparedStatement一次性插入]

JDBC批处理有3种方法

1.使用PreparedStatement

演示：

try {

Class.forName("com.mysql.jdbc.Driver");

conn = DriverManager.getConnection(o_url, userName, password);

conn.setAutoCommit(false);

String sql = "INSERT adlogs(ip,website,yyyymmdd,hour,object_id) VALUES(?,?,?,?,?)";

PreparedStatement prest = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);

for(int x = 0; x < size; x++){

prest.setString(1, "192.168.1.1");

prest.setString(2, "localhost");

prest.setString(3, "20081009");

prest.setInt(4, 8);

prest.setString(5, "11111111");

prest.addBatch();

}

prest.executeBatch();

conn.commit();

conn.close();

} catch (SQLException ex) {

Logger.getLogger(MyLogger.class.getName()).log(Level.SEVERE, null, ex);

}

2.使用Statement.addBatch方法

演示：

conn.setAutoCommit(false);

Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

for(int x = 0; x < size; x++){

stmt.addBatch("INSERT INTO adlogs(ip,website,yyyymmdd,hour,object_id) VALUES('192.168.1.3', 'localhost','20081009',8,'23123')");

}

stmt.executeBatch();

conn.commit();

3.直接使用声明

演示：

conn.setAutoCommit(false);

Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,

ResultSet.CONCUR_READ_ONLY);

for(int x = 0; x < size; x++){

stmt.execute("INSERT INTO adlogs(ip,website,yyyymmdd,hour,object_id) VALUES('192.168.1.3', 'localhost','20081009',8,'23123')");

}

conn.commit();

使用上面的方法插入100000个数据耗时：方法1：17.844s方法2：18.421s方法3：16.359s