openJPA的用户说明文档中说明,在用createNativeQuery时,如果不是SELEC,则统一按存储过程调用处理,但Oracle的存储过程返回结果集的情况与MSSQL和Sybase等数据库均不相同,它是返回一个引用游标,而不是直接返回结果集,即对CallableStatement不能直接调用executeQuery来取得结果集,而必须先用excute,然后用statement.getObject(int index)来取得结果集,因此,必须对源程序进行修改(0.9.7-1.0.0均要改)。按openJPA的设计思想,我在DBDictionary.java中(openjpa-jdbc组件下)增加两个方法
public class DBDictionary
implements Configurable, ConnectionDecorator, JoinSyntaxes,
LoggingConnectionDecorator.SQLWarningHandler {
/** *//**
* register stored procedure resultset parameters
* @param stmnt
* @throws SQLException
*/
public void regStoredProcedureResultParam(CallableStatement stmnt) throws SQLException {
}
/** *//**
* get stored procedure resultset
* @param stmt
* @return
* @throws SQLException
*/
public ResultSet getStoredProcedureOutResult(CallableStatement stmnt) throws SQLException {
return null;
}
}
然后修改OracleDictionary.java(openjpa-jdbc组件下),具体实现这两个方法
public class OracleDictionary
extends DBDictionary {
/** *//**
* register stored procedure resultset parameters
* @param stmnt
* @throws SQLException
*/
public void regStoredProcedureResultParam(CallableStatement stmnt) throws SQLException {
stmnt.registerOutParameter(1, -10);
}
/** *//**
* get stored procedure resultset
* @param stmt
* @return
* @throws SQLException
*/
public ResultSet getStoredProcedureOutResult(CallableStatement stmnt) throws SQLException {
stmnt.execute();
return (ResultSet) stmnt.getObject(1);
}
}
对于其它的数据库,请打开对应的Dictionary.java,进行针对性修改(我只用Oracle)
然后再修改SQLStoreQuery.java
public class SQLStoreQuery extends AbstractStoreQuery {
private static class SQLExecutor extends AbstractExecutor {
public ResultObjectProvider executeQuery(StoreQuery q, Object[] params, Range range) {
JDBCStore store = ((SQLStoreQuery) q).getStore();
DBDictionary dict = store.getDBDictionary();
String sql = q.getContext().getQueryString();
List paramList;
if (params.length > 0) {
paramList = new ArrayList(Arrays.asList(params));
try {
sql = substituteParams(sql, paramList);
} catch (IOException ioe) {
throw new UserException(ioe);
}
} else
paramList = Collections.EMPTY_LIST;
SQLBuffer buf = new SQLBuffer(dict).append(sql);
Connection conn = store.getConnection();
JDBCFetchConfiguration fetch = (JDBCFetchConfiguration) q.getContext().getFetchConfiguration();
boolean wrcall = false;
ResultObjectProvider rop;
PreparedStatement stmnt = null;
try {
// use the right method depending on sel vs. proc, lrs setting
if (_select && !range.lrs)
stmnt = buf.prepareStatement(conn);
else if (_select)
stmnt = buf.prepareStatement(conn, fetch, -1, -1);
else {
// stored procedure
if (!range.lrs)
stmnt = buf.prepareCall(conn);
else
stmnt = buf.prepareCall(conn, fetch, -1, -1);
// if the stored procedure has resultset,set the out resultset param
if (q.getContext().getResultType() != null) {
dict.regStoredProcedureResultParam((CallableStatement) stmnt);
wrcall = true;
}
}
int index = 0;
for (Iterator i = paramList.iterator(); i.hasNext();)
dict.setUnknown(stmnt, ++index, i.next(), null);
ResultSetResult res = new ResultSetResult(conn, stmnt, wrcall ? dict
.getStoredProcedureOutResult((CallableStatement) stmnt) : stmnt.executeQuery(), store);
if (_resultMapping != null)
rop = new MappedQueryResultObjectProvider(_resultMapping, store, fetch, res);
else if (q.getContext().getCandidateType() != null)
rop = new GenericResultObjectProvider((ClassMapping) _meta, store, fetch, res);
else
rop = new SQLProjectionResultObjectProvider(store, fetch, res, q.getContext().getResultType());
} catch (SQLException se) {
if (stmnt != null)
try {
stmnt.close();
} catch (SQLException se2) {
}
try {
conn.close();
} catch (SQLException se2) {
}
throw SQLExceptions.getStore(se, dict);
}
if (range.start != 0 || range.end != Long.MAX_VALUE)
rop = new RangeResultObjectProvider(rop, range.start, range.end);
return rop;
}
用maven重新编译和打包,即可。
用法:
EntityManager em = ...
Query q = em.createNativeQuery("{?=call a()}", A.class);
List<?> list q.getResultList();