Java 在 Hibernate 中使用 Native SQL 批量插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25419488/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Batch insert using Native SQL in Hibernate
提问by Oomph Fortuity
I want to insert records in database using Hibernate Native SQL.The code is like below
我想使用 Hibernate Native SQL 在数据库中插入记录。代码如下
Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
String sqlInsert = "insert into sampletbl (name) values (?) ";
for(String name : list){
session.createSQLQuery( sqlInsert )
.setParameter(1,name)
.executeUpdate();
}
tx.commit();
session.close();
Above code is working fine.I think it is not the best way. Please give me another possible ways to do this if any. Thank you
上面的代码工作正常。我认为这不是最好的方法。如果有的话，请给我另一种可能的方法来做到这一点。谢谢
采纳答案by Oomph Fortuity
Hibernate have a Batch functionality.But in above case I am using Native SQL,as per my observation hibernate batch is not much effective in case of Native SQL.Yes,surely it avoids the out of memory error but does not improves much performance.
Hence I retreated to implemented JDBC Batch in Hibernate.Hibernate provides method doWork()to get Connection from Hibernate Session.
Hibernate 有 Batch 功能。但在上述情况下，我使用的是 Native SQL，根据我的观察，Hibernate 批处理在 Native SQL 的情况下不是很有效。是的，当然它避免了内存不足错误，但并没有提高太多性能。因此我退回到在 Hibernate 中实现 JDBC Batch。Hibernate 提供了doWork()从 Hibernate Session 获取连接的方法。
Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
//get Connction from Session
session.doWork(new Work() {
@Override
public void execute(Connection conn) throws SQLException {
PreparedStatement pstmt = null;
try{
String sqlInsert = "insert into sampletbl (name) values (?) ";
pstmt = conn.prepareStatement(sqlInsert );
int i=0;
for(String name : list){
pstmt .setString(1, name);
pstmt .addBatch();
//20 : JDBC batch size
if ( i % 20 == 0 ) {
pstmt .executeBatch();
}
i++;
}
pstmt .executeBatch();
}
finally{
pstmt .close();
}
}
});
tx.commit();
session.close();
回答by bitkot
If you don't need to worry about SQL injection. i.e you are not getting data from user side then you can do this.
如果你不需要担心 SQL 注入。即您没有从用户端获取数据，那么您可以这样做。
StringBuilder sqlInsert = new StringBuilder("insert into sampletbl (name) values ");
for(String name : list){
sqlInsert.append("("+name++"),");
}
sqlInsert.setLength(sqlInsert.length() - 1);
session.createSQLQuery( sqlInsert.toString()).executeUpdate();
It will create a query like this.
它将创建一个这样的查询。
insert into sampletbl (name) values ("name1"), ("name2")....
This way your query will run only once and not for each and every item in the list.
这样您的查询将只运行一次，而不是针对列表中的每个项目。
回答by George Ninan
Here is the same example for Java 8, Hibernate-JPA 2.1:
以下是 Java 8 Hibernate-JPA 2.1 的相同示例：
@Repository
public class SampleNativeQueryRepository {
private final Logger log = LoggerFactory.getLogger(SampleNativeQueryRepository.class);
@PersistenceContext
private EntityManager em;
public void bulkInsertName(List<String> list){
Session hibernateSession = em.unwrap(Session.class);
String sql = "insert into sampletbl (name) values (:name) ";
hibernateSession.doWork(connection -> {
try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
int i = 1;
for(String name : list) {
preparedStatement.setString(1, name);
preparedStatement.addBatch();
//Batch size: 20
if (i % 20 == 0) {
preparedStatement.executeBatch();
}
i++;
}
preparedStatement.executeBatch();
} catch (SQLException e) {
log.error("An exception occurred in SampleNativeQueryRepository.bulkInsertName: {}", e);
}
});
}
}
