英文:
CriteriaBuilder call a select function
问题
我有一个查询，我想调用一个函数，而不向函数传递参数。
示例...
CREATE OR REPLACE function demo_fn()
RETURNS TABLE (
id bigint,
somename varchar,
something varchar) AS $$
BEGIN
RETURN QUERY SELECT DISTINCT c.id, c.somename, c.something FROM pluto c
ORDER BY id ASC LIMIT 10;
END;
$$
LANGUAGE plpgsql;
这个查询函数像这样被调用：`select * from demo_fn();`，在数据库中可以正常工作。
另一方面，调用函数的方法是这样的...
CriteriaBuilder cb = em.getCriteriaBuilder();
cb.function("demo_fn", PlutoDTO.class, cb.parameter(null)));
CriteriaQuery cq = cb.createQuery(PlutoDTO.class);
Root root = cq.from(Pluto.class);
TypedQuery q = em.createQuery(cq);
List<PlutoDTO> pluto = q.getResultList();
当我运行函数时
`Caused by: java.lang.IllegalArgumentException: Error occurred validating the Criteria`
有人能指点我正确的方向吗？
真的看不清楚怎么解决这个问题...非常感谢
英文:
I have a query that i want to call a function without parsing a parameter to the function.
Example....
CREATE OR REPLACE function demo_fn()
RETURNS TABLE (
id bigint,
somename varchar,
something varchar) AS $$
BEGIN
RETURN QUERY SELECT DISTINCT c.id, c.somename , c.something FROM pluto c
ORDER BY id ASC LIMIT 10;
END;
$$
LANGUAGE plpgsql;
This query function is called like this select * from demo_fn(); which work perfectly well from the database.
On the other hand, the method calling the function is like this....
CriteriaBuilder cb = em.getCriteriaBuilder();
cb.function("demo_fn", PlutoDTO.class, cb.parameter(null))));
CriteriaQuery cq = cb.createQuery(PlutoDTO.class);
Root root = cq.from(Pluto.class);
TypedQuery q = em.createQuery(cq);
List<PlutoDTO> pluto = q.getResultList();
When i run the function
Caused by: java.lang.IllegalArgumentException: Error occurred validating the Criteria
Can someone please point me to the right direction
Can't really see my way out here.....much appreciated
答案1
得分: 0
看起来通过条件调用函数有点有趣，所以我所做的是，我使用了一个预处理语句而不是...
Session session = entityManager.unwrap(Session.class); // 从Hibernate获取的Session
session.doWork(new Work() {
@Override
public void execute(Connection connection) throws SQLException {
PreparedStatement ps = connection.prepareStatement("select * from demo_fn()");
ResultSet rs = ps.executeQuery();
List<DemoDto> demoDtos = new ArrayList<>();
while (rs.next()) {
DemoDto demoDto = new DemoDto();
demoDto.setDemoValueOne(rs.getString("Column_name_here"));
demoDtos.add(demoDto);
// 从ResultSet中获取值，在这种情况下是rs
}
// 有一个处理这部分的实现
// rs.close();
// ps.close();
}
});
希望将来对某人有所帮助，这确实是一个令人头疼的问题。不过在分页时要小心，它确实非常灵活。
英文:
So it seems calling a function with criteria gets a little interesting, so what i did was, i used a prepared statement rather...
Session session = entityManager.unwrap(Session.class); //Session from Hibernate
session.doWork(new Work() {
@Override
public void execute(Connection connection) throws SQLException {
PreparedStatement ps= connection.prepareStatement("select * from demo_fn()");
ResultSet rs= ps.getResultSet();
List<DemoDto> demoDtos = new ArrayList<>();
while (rs.next()) {
DemoDto demoDto = new DemoDto();
demoDto.setDemoValueOne(rs.getString("Column_name_here"));
demoDtos.add(demoDto);
// Get the values from ResultSet in this case rs
}
//Have an impelementation Taking care of this
//rs.close();
//ps.close();
}
Hope this get helpful to someone in the future, it really was a pain in a spin.
Be careful when paginating though, its really flexible.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论