如何从 jdbc 代码传递数组对象

发布于 2024-09-09 11:18:48 字数 640 浏览 7 评论 0原文

我在 informix 11.5 数据库中有一个存储过程 get_data(estargs set(char(1000) not null)) 。我必须使用这个存储过程才能从数据库中获取值。

我尝试使用这种方式，但失败了：

conn = dataSource.getConnection();
            String [] arrayObj={"and code = 'Value1'","and lmt= 10000.000"};
            CallableStatement test=conn.prepareCall("{call get_data(?)}");
            test.setObject(1, arrayObj);
            test.execute();
            ResultSet testrs = test.getResultSet();
            while (testrs.next()) {
                int data = testrs.getInt(1);
                System.out.println(data);

            }

这不起作用。你认为我做错了什么？

原文

I have a stored procedure get_data(estargs set(char(1000) not null)) in the informix 11.5 database. I have to use this stored procedure in order to get a value from the database.

I tried using this way but it fails:

conn = dataSource.getConnection();
            String [] arrayObj={"and code = 'Value1'","and lmt= 10000.000"};
            CallableStatement test=conn.prepareCall("{call get_data(?)}");
            test.setObject(1, arrayObj);
            test.execute();
            ResultSet testrs = test.getResultSet();
            while (testrs.next()) {
                int data = testrs.getInt(1);
                System.out.println(data);

            }

This is not working. What do you think I am doing wrong?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

乙白 2024-09-16 11:18:48

那是不可能的。替换

conn.prepareCall("{call get_data(?)}");

为

conn.prepareCall("{call get_data(?, ?)}");

并替换

test.setObject(1, arrayObj);

为

test.setObject(1, arrayObj[0]);
test.setObject(2, arrayObj[1]);

相关问题：

如何在 IN 子句中设置多个值？

更新：使其更加“动态”，您希望借助以下两个实用方法自己生成和填充占位符：

public static String preparePlaceHolders(int length) {
    StringBuilder builder = new StringBuilder(length * 2 - 1);
    for (int i = 0; i < length; i++) {
        if (i > 0) builder.append(',');
        builder.append('?');
    }
    return builder.toString();
}

public static void setValues(PreparedStatement preparedStatement, Object... values) throws SQLException {
    for (int i = 0; i < values.length; i++) {
        preparedStatement.setObject(i + 1, values[i]);
    }
}

可以如下使用：

private static final String SQL_CALL_GET_DATA = "{call get_data(%s)}";

// ...
String sql = String.format(SQL_CALL_GET_DATA, preparePlaceholders(arrayObj.length));
statement = connection.prepareCall(sql);
setValues(statement, arrayObj);
// ...

That's not possible. Replace

conn.prepareCall("{call get_data(?)}");

conn.prepareCall("{call get_data(?, ?)}");

and replace

test.setObject(1, arrayObj);

test.setObject(1, arrayObj[0]);
test.setObject(2, arrayObj[1]);

Related question:

How to set multiple values in IN clause?

Update: make it all more "dynamically", you'd like to generate and populate the placeholders yourself with help of the following two utility methods:

public static String preparePlaceHolders(int length) {
    StringBuilder builder = new StringBuilder(length * 2 - 1);
    for (int i = 0; i < length; i++) {
        if (i > 0) builder.append(',');
        builder.append('?');
    }
    return builder.toString();
}

public static void setValues(PreparedStatement preparedStatement, Object... values) throws SQLException {
    for (int i = 0; i < values.length; i++) {
        preparedStatement.setObject(i + 1, values[i]);
    }
}

which can be used as follows:

private static final String SQL_CALL_GET_DATA = "{call get_data(%s)}";

// ...
String sql = String.format(SQL_CALL_GET_DATA, preparePlaceholders(arrayObj.length));
statement = connection.prepareCall(sql);
setValues(statement, arrayObj);
// ...

回复收藏 0 原文