从Java应用程序执行批量插入数据库的不同方法 [英] Different ways of performing bulk insert into database from a java application
问题描述
我正在寻找从Java应用程序向数据库(例如SQL Server 2012)执行批量插入的不同方法.我需要非常有效地将许多实体插入数据库中，而不必对数据库进行与实体一样多的调用.
I am looking for different ways of performing bulk insert into database (e.g. SQL Server 2012) from a Java application. I need to insert lot of entities into database very efficiently without making as many calls to database as there are entities.
我的要求是执行实体的批量插入，其中在数据库中插入实体可能涉及将数据插入一个或多个表中.以下是我可以想到的两种方法:
My requirement is to perform a bulk insert of entities, where an insert of entity in database could involve inserting data into one or more tables. The following are the two ways which I can think of:
-
动态生成一批SQL语句，并通过使用本机JDBC支持对数据库执行该语句.
Dynamically generate a batch of SQL statements and execute it against the database by making use of native JDBC support.
构造所有实体的XML表示，然后通过传递生成的XML来调用存储过程.存储过程负责解析XML并将实体插入数据库.
Construct XML representation of all the entities and then invoke a stored procedure by passing the generated XML. The stored procedure takes care of parsing the XML and inserting the entities to database.
我是Java的新手，对可用框架的知识不足.海事组织，上述两种方法似乎很幼稚，没有利用现有框架. 我要求专家们分享实现批量插入的各种方法及其优点和缺点.我对MyBatis，Spring-MyBatis，Spring-JDBC，JDBC等开放，这有效地解决了该问题.方式.
I am new to Java and not having enough knowledge of available frameworks. IMO, the above two approaches seems to be very naive and not leveraging the available frameworks. I am requesting experts to share different ways of achieving bulk insert along with its pros and cons. I am open to MyBatis, Spring-MyBatis, Spring-JDBC, JDBC, etc which solves the problem in an efficient manner.
谢谢.
推荐答案
我有一个演示，JDBC批处理 文件:demo.txt 内容
I have a demo ,JDBC batch processing file:demo.txt The content
1899942，demo1
1899944，demo2
1899946，demo3
1899948，demo4
1899942 ,demo1
1899944 ,demo2
1899946 ,demo3
1899948 ,demo4
插入数据以读取文件内容
Insert the data reads the file content
我的代码:
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;
}
第一次发言，希望我能帮忙
Speak for the first time, I hope I can help
我是上面的演示，使用PreparedStatement [读取数据调用一次插入PreparedStatement]
I am the demo above use PreparedStatement [Read data calls a PreparedStatement one-off inserted]
JDBC批处理共有3种方法 1.使用PreparedStatement 演示:
JDBC batch There are 3 ways 1.use PreparedStatement demo:
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.use Statement.addBatch方法 演示:
2.use Statement.addBatch methods demo:
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.直接使用声明 演示:
3.Direct use of the Statement demo:
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.84s 方法2:18.421s 方法3:16.359s
Using the above method Insert the 100000 pieces of data Time consuming： method 1:17.844s method 2:18.421s method 3:16.359s
这篇关于从Java应用程序执行批量插入数据库的不同方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
