Java 使用 hibernate 执行本机 sql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24782717/
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
Execute native sql with hibernate
提问by St.Antario
I'm using hibernate 4.2.6and PostgreSQL 9.1I've been trying to execute sql query with hibernate. I've written:
我正在使用hibernate 4.2.6并且PostgreSQL 9.1我一直在尝试使用 hibernate 执行 sql 查询。我写过：
Session session = Hibernate.util.HibernateUtil.getSessionFactory().openSession();
session.beginTransaction();
String sql = String.format("INSERT INTO products (name,cost) VALUES('%s',%s);", product.getName(), product.getCost());
createSQLQuery(sql);//has no effect. Query doesn't execute.
session.getTransaction().commit();
session.close();
This query does not executed in DB. But if I write
此查询不在 DB 中执行。但是如果我写
String sql = String.format("INSERT INTO products (name,cost) VALUES('%s',%s);", product.getName(), product.getCost());
Properties connectionProps = new Properties();
connectionProps.put("user", "postgres");
connectionProps.put("password", "123");
Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/solid",connectionProps);
conn.createStatement().execute(sql);
corresponding row will add to table. Why hibernate doesn't work, but native query wth JDBC works?
相应的行将添加到表中。为什么休眠不起作用，但使用 JDBC 的本机查询有效？
采纳答案by Darshan Lila
This should help you.
这应该对你有帮助。
Session session = Hibernate.util.HibernateUtil.getSessionFactory().openSession();
session.beginTransaction();
String sql = String.format("INSERT INTO products (name,cost) VALUES('%s',%s);",product.getName(), product.getCost());
session.createSQLQuery(sql).executeUpdate();
session.getTransaction().commit();
session.close();
回答by Jp Vinjamoori
Its always better to use PreparedStatement (You dont want to give way to SQL Injections).
使用 PreparedStatement （您不想让位于 SQL 注入）总是更好。
String sql = "INSERT INTO products (name,cost) VALUES (?,?)";
Session sess = Hibernate.util.HibernateUtil.getSessionFactory().openSession();
Connection con = sess.connection();
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, product.getName());
pstmt.setInt(2, product.getCost());
pstmt.executeUpdate();
con.commit();
pstmt.close();
回答by BrotherBear
Another issue that might hit you (like it hit me) is this:
另一个可能会打击您（就像它打击我一样）的问题是：
You want to run a native query, but can't get it to work in your production code? Pay attention if you are using a different database user for the application than the schema owner. In that case you may have to add the schema prefix to the referenced tables in order to make it work.
您想运行本机查询，但无法在您的生产代码中使用它？如果您为应用程序使用与架构所有者不同的数据库用户，请注意。在这种情况下，您可能必须将架构前缀添加到引用的表中才能使其工作。
In my example I am using an entity manager instead of the session:
在我的示例中，我使用的是实体管理器而不是会话：
String sql = "select id from some_table";
Query query = em.createNativeQuery(sql);
List<Long> results = query.getResultList();
if some_table is owned by e.g. dba while the application is running as user, you will need to modify the query to:
如果 some_table 在应用程序以用户身份运行时由 dba 拥有，则需要将查询修改为：
String sql = "select id from dba.some_table";
Having Hibernate set to prefix all tables with
将 Hibernate 设置为所有表的前缀
<prop key="hibernate.default_schema">dba</prop>
does apparently NOT affect native queries.
显然不会影响本机查询。
回答by Jorge Santos Neill
The solution that work for me is the following:
对我有用的解决方案如下：
public List<T> queryNativeExecute(String query) throws CustomException {
List<T> result =null;
Session session =null;
Transaction transaction=null;
try{
session = HibernateUtil.getSessionJavaConfigFactory().openSession();
transaction = session.beginTransaction();
session.createNativeQuery(query).executeUpdate();
transaction.commit();
}catch(Exception exception){
result=null;
if (transaction !=null && transaction.isActive()){
transaction.rollback();
}
throw new CustomException(exception.getMessage(),exception,error.ErrorCode.DATABASE_TABLE,this.getClass().getSimpleName());
}finally{
if (session !=null){
session.close();
}
}
return result;
}
