JPA Hibernate调用Postgres函数void返回MappingException： [英] JPA Hibernate Call Postgres Function Void Return MappingException:
问题描述
当我尝试使用JPA创建本地查询来调用postgres函数时，出现以下问题： org.hibernate.MappingException：无JDBC类型的方言映射：1111 。
>我在启动单例中创建了一个EJB计时器，每6小时运行一次Postgres函数。该函数返回void并检查已到期的记录，删除它们并更新一些状态。它不需要参数，它将返回void。
-
如果我使用PgAdmin查询工具（select function（）;）调用postgres函数并且返回void， / b>
-
当我在Glassfish 3.1.1上部署应用程序时，我得到一个异常并且无法部署。
这是（缩短的）堆栈跟踪：
警告：A在调用EJB UserQueryBean方法期间发生系统异常public void com.mysoftwareco.entity.utility.UserQueryBean.runRequestCleanup（）
javax.ejb.TransactionRolledbackLocalException：从Bean
抛出的异常... STACK TRACE BLAH BLAH BLAH ...
导致：javax.persistence.PersistenceException：org.hibernate.MappingException：无JDBC类型的Dialect映射：1111
以下是代码：
首先是JPA的内容：
public void runRequestCleanup（）{
String queryString =SELECT a_function_that_hibernate_chokes_on（）;
Query query = em.createNativeQuery（queryString）;
Object result = query.getSingleResult（）;
$ / code> 这是单身人士称之为：
@Startup
@Singleton
public class RequestCleanupTimer {
@Resource
TimerService timerService;
@EJB
UserQueryBean queryBean;
@PostConstruct
@Schedule（hour =* / 6）
void runCleanupTimer（）{
queryBean.runRequestCleanup（）;
code $
和函数：
创建或替换函数a_function_that_hibernate_chokes_on（）
RETURNS void AS
$ BODY $
DECLARE
var_field_id myTable。 FIELD_ID％TYPE;
BEGIN
var_field_id IN
从myTable中选择field_id
where status ='some status'
and disposition ='some disposition'
and valid_through<现在（）
LOOP
BEGIN
- 做东西
END;
END LOOP;
END;
$ BODY $
语言plpgsql VOLATILE
COST 100;
解决方案运行存储过程。
我最终使用了JDBC和准备好的语句。我花了几个小时试图将一个方形的钉子插入一个圆孔后花了15分钟。我调用了同样的jndi数据源，我的持久性单元用来获取连接，创建了一个准备好的语句，并在完成时关闭它。
所以如果你需要运行一个存储过程或Postgres函数）从一个（现在主要是）JPA应用程序，这是什么为我工作：
@Stateless
@LocalBean
public class UserQueryBean实现Serializable {
@Resource（mappedName =jdbc / DatabasePool）
private javax.sql.DataSource ds;
...
public void runRequestCleanup（）{
String queryString =SELECT cleanup_function_that_hibernateJPA_choked_on（）;
连接conn = null;
PreparedStatement statement = null;
尝试{
conn = ds.getConnection（）;
statement = conn.prepareCall（queryString）;
statement.executeQuery（）;
} catch（SQLException ex）{
Logger.getLogger（UserQueryBean.class.getName（））.log（Level.SEVERE，null，ex）;
} finally {
try {
statement.close（）;
conn.close（）;
} catch（SQLException ex）{
Logger.getLogger（UserQueryBean.class.getName（））.log（Level.SEVERE，null，ex）;
}
}
//记录代码位
}
...
}
似乎存在一个可怕的疏忽，忽略了从JPA在服务器上运行函数或存储过程的简单能力;特别是除了空白或受影响的行数外不会返回任何内容。如果这是故意......没有评论。
编辑：增加紧密连接。
I have a problem where I am getting an: org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111 when trying to call a postgres function using JPA create native query.
I created an EJB timer in a startup singleton to run a Postgres function every 6 hours. The function returns void and checks for expired records, deletes them, and updates some statuses. It takes no arguments and it returns void.
The postgres function runs perfectly if I call it using PgAdmin query tool (select function();) and returns void.
When I deploy the app on Glassfish 3.1.1 I get an exception and a failure to deploy.
This is the (shortened) stack trace:
WARNING: A system exception occurred during an invocation on EJB UserQueryBean method public void com.mysoftwareco.entity.utility.UserQueryBean.runRequestCleanup()
javax.ejb.TransactionRolledbackLocalException: Exception thrown from bean
...STACK TRACE BLAH BLAH BLAH ...
Caused by: javax.persistence.PersistenceException: org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111
Here is the code:
First the JPA stuff:
public void runRequestCleanup() {
String queryString = "SELECT a_function_that_hibernate_chokes_on()";
Query query = em.createNativeQuery(queryString);
Object result = query.getSingleResult();
}
This is the singleton calling it:
@Startup
@Singleton
public class RequestCleanupTimer {
@Resource
TimerService timerService;
@EJB
UserQueryBean queryBean;
@PostConstruct
@Schedule(hour = "*/6")
void runCleanupTimer() {
queryBean.runRequestCleanup();
}
}
And the function:
CREATE OR REPLACE FUNCTION a_function_that_hibernate_chokes_on()
RETURNS void AS
$BODY$
DECLARE
var_field_id myTable.field_id%TYPE;
BEGIN
FOR var_field_id IN
select field_id from myTable
where status = 'some status'
and disposition = 'some disposition'
and valid_through < now()
LOOP
BEGIN
-- Do Stuff
END;
END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
解决方案 I had enough messing around with JPA trying to get it to run a stored procedure.
I ended up using JDBC with a prepared statement. I did it in 15 minutes after spending several fruitless hours trying to fit a square peg into a round hole. I called the same jndi datasource my persistence unit uses to get a connection, created a prepared statement and closed it when done.
So if you need to run a stored procedure (or Postgres function) from a (now mostly) JPA app, here is what worked for me:
@Stateless
@LocalBean
public class UserQueryBean implements Serializable {
@Resource(mappedName="jdbc/DatabasePool")
private javax.sql.DataSource ds;
...
public void runRequestCleanup() {
String queryString = "SELECT cleanup_function_that_hibernateJPA_choked_on()";
Connection conn = null;
PreparedStatement statement = null;
try {
conn = ds.getConnection();
statement = conn.prepareCall(queryString);
statement.executeQuery();
} catch (SQLException ex) {
Logger.getLogger(UserQueryBean.class.getName()).log(Level.SEVERE, null, ex);
}finally{
try {
statement.close();
conn.close();
} catch (SQLException ex) {
Logger.getLogger(UserQueryBean.class.getName()).log(Level.SEVERE, null, ex);
}
}
// bit of logging code here
}
...
}
There seems to be a horrible oversight to leave out the simple ability to run a function or stored procedure on the server from JPA; especially one that doesn't return anything except void or the number of rows affected. And if it was deliberate ... no comment.
Edit: added close connection.
这篇关于JPA Hibernate调用Postgres函数void返回MappingException：的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
