i used a native query to call a function in my postgresql database, code is like this:
Code:
String query = "select details,rec_xml[1] from f_get_reccomendations(\'/ma:recommendations/ma:recommendation/name/text()\') ORDER BY id DESC LIMIT 1;";
Query q = em.createNativeQuery(query);
return q.getResultList();
the function defined as:
Code:
CREATE OR REPLACE FUNCTION f_get_reccomendations (
IN xpath varchar(128),
OUT id int,
OUT recs_num int,
OUT details varchar(25),
OUT d_stamp timestamp,
OUT rec_xml xml[]
) RETURNS SETOF RECORD AS
$$
select id, recs_num, details, d_stamp, xpath($1, recs,
array [
array['xs','http://www.w3.org/2001/XMLSchema'],
array['ma','http://schemas.medio.com/analytics/1.0']
]
) from recommendations;
$$
LANGUAGE SQL;
when ran from the psql, it worked, but when i run in the code, it threw me error: No Dialect mapping for JDBC type: 2009. my guess is the rec_xml is defined as xml[] type that hibernate could not understand, so i added the following code to extend the Dialect:
Code:
public class RelePostgreSQLDialect extends PostgreSQLDialect {
public RelePostgreSQLDialect() {
super();
registerColumnType(Types.VARCHAR, "xml");
}
}
and specified the dialect in persistem.xml:
Code:
<persistence-unit name="dashboard-postgres-dev" transaction-type="RESOURCE_LOCAL">
..
<property name="hibernate.dialect" value="com.dyihi.eval.rdb.RelePostgreSQLDialect" />
...
</persistence-unit>
but still i got the same error, please help!