将数组值从JPA/Hibernate传递给PostgreSQL。

huangapple go评论73阅读模式
英文:

Pass array values to PostgreSQL from JPA/Hibernate

问题

我尝试从JPA/Hibernate环境将整数数组值传递给plpgsql存储过程。但我总是得到一个异常:函数fn_test_array(bytea)不存在。

我编写了一个简短的演示应用程序,演示了这个问题。
(Wildfly AS,JPA/Hibernate,PostgreSQL 12)

是的,我知道,int[].class是个垃圾,但解决方案是什么呢?:)

存储过程:

CREATE OR REPLACE FUNCTION public.fn_test_array(in_arr integer[], OUT res_int bigint)
RETURNS bigint
LANGUAGE plpgsql
AS $function$
BEGIN

res_int := 201;
 
END;
$function$
;

从Java调用:

StoredProcedureQuery query2 = em.createStoredProcedureQuery("fn_test_array")
.registerStoredProcedureParameter("in_arr", int[].class, ParameterMode.IN)
.registerStoredProcedureParameter("res_int", Long.class, ParameterMode.OUT)
.setParameter("in_arr", new int[]{1, 2});

query2.execute();
Long res2 = (Long) query2.getOutputParameterValue("res_int");
System.out.println("res2: " + res2);

异常:

11:26:11,933 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-19) SQL Error: 0, SQLState: 42883
11:26:11,933 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-19) ERROR: function fn_test_array(bytea) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 15

非常感谢您提前的帮助。

英文:

I try pass integer array value a plpgsql stored procedure from JPA/Hibernate enviroment. But I always get an execpetion: function fn_test_array(bytea) does not exist.

I wrote a short demo application, which demonstrates the problem.
(Wildfly AS, JPA/Hibernate, PostgreSQL 12)

Yes, I know, the int[].class is a rubbish, but then what is the solution? 将数组值从JPA/Hibernate传递给PostgreSQL。

Stored procedure:

CREATE OR REPLACE FUNCTION public.fn_test_array(in_arr integer[], OUT res_int bigint)
RETURNS bigint
LANGUAGE plpgsql
AS $function$
BEGIN

res_int := 201;
 
END;
$function$
;

Call from java:

StoredProcedureQuery query2 = em.createStoredProcedureQuery("fn_test_array")
.registerStoredProcedureParameter("in_arr", int[].class, ParameterMode.IN)
.registerStoredProcedureParameter("res_int", Long.class, ParameterMode.OUT)
.setParameter("in_arr", new int[]{1, 2});

query2.execute();
Long res2 = (Long) query2.getOutputParameterValue("res_int");
System.out.println("res2: " + res2);

Exception:

11:26:11,933 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-19) SQL Error: 0, SQLState: 42883
11:26:11,933 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-19) ERROR: function fn_test_array(bytea) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 15

Thank you in advance for your help.

答案1

得分: 0

很遗憾,我没有找到复杂的解决方案。然而,我使用JDBC连接来解决了它。它的工作原理如下所示。

final Session session = em.unwrap(Session.class);  //Hibernate session
session.doWork(new Work() {
    @Override
    public void execute(Connection connection) throws SQLException {
        try {
            String query = "{CALL fn_test_array(?, ?)}";
            CallableStatement stmt = connection.prepareCall(query);
            
            Integer[] numbers = {1, 2, 3, 5};
            final Array in_arr = connection.createArrayOf("integer", numbers);            
            stmt.setArray(1, in_arr);            
            stmt.registerOutParameter(2, Types.BIGINT);
            stmt.execute();

            Long resLong = stmt.getLong(2);
            System.out.println("resLong: " + resLong);
            stmt.close();            
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
});
英文:

Unfortunately I not found sophistical solution. However, I soled it with JDBC connection. It works like this.

final Session session = em.unwrap(Session.class);  //Hibernate session
session.doWork(new Work() {
	@Override
	public void execute(Connection connection) throws SQLException {
		try {
			String query = "{CALL fn_test_array(?, ?)}";
			CallableStatement stmt = connection.prepareCall(query);
			
			Integer[] numbers = {1, 2, 3, 5};
			final Array in_arr = connection.createArrayOf("integer", numbers);            
			stmt.setArray(1, in_arr);            
			stmt.registerOutParameter(2, Types.BIGINT);
			stmt.execute();

			Long resLong = stmt.getLong(2);
			System.out.println("resLong: " + resLong);
			stmt.close();            
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
	}
});

huangapple
  • 本文由 发表于 2020年9月9日 17:05:25
  • 转载请务必保留本文链接:https://go.coder-hub.com/63808312.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定