解决oracle Array字符集问题的调试手记.

最新推荐文章于 2021-04-12 20:21:17 发布

最新推荐文章于 2021-04-12 20:21:17 发布 · 77 阅读

文章标签：

#Oracle #SQL #Java #JDBC

本文展示了一个使用Java进行Oracle数据库批量查询的例子。通过CallableStatement和ARRAY类型传递参数列表，并利用OracleCachedRowSet缓存结果集，提高了处理大量数据时的效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import oracle.jdbc.OracleTypes;
import oracle.jdbc.rowset.OracleCachedRowSet;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.CharacterSet;

public class Temp {

/**
* @param args
*/
public static void main(String[] args) throws Exception{

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@xxxxxxxx:1521:billbj1","xxxx","xxxx");
CallableStatement callableStatement = conn.prepareCall("{? = call xxxxxxxxxxxxx(?,?,?,?)}");
callableStatement.registerOutParameter(1, OracleTypes.CURSOR);

CharacterSet dbCharset = CharacterSet.make(CharacterSet.AL32UTF8_CHARSET);//可以指定调试的字符集.

List<oracle.sql.CHAR> cid = new ArrayList<oracle.sql.CHAR>();
oracle.sql.CHAR value = new oracle.sql.CHAR("6487", dbCharset);
cid.add(value);

List<oracle.sql.CHAR> nid = new ArrayList<oracle.sql.CHAR>();
value = new oracle.sql.CHAR("0105591", dbCharset);
nid.add(value);

ARRAY channelIdArray = createArray("CHANNELIDARRAY", cid, conn);
callableStatement.setArray(2, channelIdArray);
callableStatement.setString(3, "200810010000");
callableStatement.setString(4, "200810010059");
ARRAY nodeIdArray = createArray("NODEIDARRAY", nid, conn);
callableStatement.setArray(5, nodeIdArray);
callableStatement.execute();

ResultSet rs1 = (ResultSet) callableStatement.getObject(1);
OracleCachedRowSet rs = new OracleCachedRowSet();
rs.populate(rs1);
rs1.close();
callableStatement.close();
conn.close();
int x = 0;
while(rs.next() && ++x < 10){
java.sql.Timestamp d = rs.getTimestamp(1);

System.out.println(d.toString());
}
}
private static ARRAY createArray(final String sqlTypeName,
final List<oracle.sql.CHAR> channelIds, Connection connection)
throws SQLException {
ArrayDescriptor channelIdArraydesc = ArrayDescriptor.createDescriptor(
sqlTypeName, connection);
ARRAY channelIdArray = new ARRAY(channelIdArraydesc, connection,channelIds.toArray(new oracle.sql.CHAR[0]));
return channelIdArray;
}
}