下面列出了java.sql.PreparedStatement#setArray ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
@Test
public void testArrayRefToLiteral() throws Exception {
Connection conn;
long ts = nextTimestamp();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
conn = DriverManager.getConnection(getUrl(), props);
try {
PreparedStatement stmt = conn.prepareStatement("select ?[2] from system.\"catalog\" limit 1");
Array array = conn.createArrayOf("CHAR", new String[] {"a","b","c"});
stmt.setArray(1, array);
ResultSet rs = stmt.executeQuery();
assertTrue(rs.next());
assertEquals("b", rs.getString(1));
assertFalse(rs.next());
} catch (SQLException e) {
} finally {
if (conn != null) {
conn.close();
}
}
}
public static void setObject(PreparedStatement stmt, int index, Object obj)
throws SQLException {
if (obj instanceof NullWrapper) {
NullWrapper nullable = (NullWrapper)obj;
if (nullable.value == null) {
stmt.setNull(index, nullable.type);
} else {
setObject(stmt, index, nullable.value);
}
} else if (obj instanceof String) {
stmt.setString(index, (String)obj);
} else if (obj instanceof Integer) {
stmt.setInt(index, (Integer)obj);
} else if (obj instanceof Timestamp) {
stmt.setTimestamp(index, (Timestamp)obj);
} else if (obj instanceof Long) {
stmt.setLong(index, (Long)obj);
} else if (obj instanceof Array) {
stmt.setArray(index, (Array)obj);
} else if (obj instanceof Float) {
stmt.setFloat(index, (Float)obj);
} else if (obj instanceof Double) {
stmt.setDouble(index, (Double)obj);
} else {
throw new IllegalArgumentException(
"type of object is incompatible for object=" + obj.toString());
}
}
@Override
public void set(PreparedStatement target, Array value, int columnIndex, Context context) throws SQLException {
if (value == null) {
target.setNull(columnIndex, Types.ARRAY);
} else {
target.setArray(columnIndex, value);
}
}
/**
* Create an association of an operand tree node and the nth member of a given base type in the
* database.
*
* @param connection The connection to the database.
* @param treeNodeId The tree node that is associated with a type.
* @param baseTypeId the id of the base type that should be associated with the tree node.
* @param position The zero-based index position of the operand within its instruction.
* @param offset An offset to a member contained in the corresponding base type (in bits).
* @param address The address of the instruction that is annotated with this type substitution.
* @throws CouldntSaveDataException Thrown if the type substitution couldn't be written to the
* database.
*/
public static void createTypeSubstitution(final Connection connection,
final int treeNodeId,
final int baseTypeId,
final List<Integer> memberPath,
final int position,
final int offset,
final IAddress address,
final INaviModule module) throws CouldntSaveDataException {
try {
final String query = String.format(
"INSERT INTO %s (module_id, address, \"position\", \"offset\", expression_id, path, "
+ "base_type_id) VALUES (?, ?, ?, ?, ?, ?, ?)", CTableNames.EXPRESSION_TYPES_TABLE);
final PreparedStatement statement = connection.prepareStatement(query);
try {
statement.setInt(1, module.getConfiguration().getId());
statement.setLong(2, address.toLong());
statement.setInt(3, position);
statement.setInt(4, offset);
statement.setInt(5, treeNodeId);
statement.setArray(6, connection.createArrayOf("int4", memberPath.toArray()));
statement.setInt(7, baseTypeId);
statement.executeUpdate();
} finally {
statement.close();
}
} catch (final SQLException exception) {
throw new CouldntSaveDataException(exception);
}
}
@Override
public void bindArgument(@NotNull PreparedStatement ps, int index, @Nullable Object value) throws SQLException {
if (value instanceof SqlArray) {
SqlArray array = (SqlArray) value;
OracleConnection connection = ps.getConnection().unwrap(OracleConnection.class);
ps.setArray(index, connection.createARRAY(array.getType(), array.getValues().toArray()));
} else {
super.bindArgument(ps, index, value);
}
}
@Test
public void testScanWithArrayInWhereClause() throws Exception {
String tenantId = getOrganizationId();
String tableName = createTableWithArray(getUrl(),
getDefaultSplits(tenantId), null);
initTablesWithArrays(tableName, tenantId, null, false, getUrl());
String query = "SELECT a_double_array, /* comment ok? */ b_string, a_float FROM " + tableName + " WHERE ?=organization_id and ?=a_byte_array";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(getUrl(), props);
TestUtil.analyzeTable(conn, tableName);
try {
PreparedStatement statement = conn.prepareStatement(query);
statement.setString(1, tenantId);
// Need to support primitive
Byte[] byteArr = new Byte[2];
byteArr[0] = 25;
byteArr[1] = 36;
Array array = conn.createArrayOf("TINYINT", byteArr);
statement.setArray(2, array);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
// Need to support primitive
Double[] doubleArr = new Double[4];
doubleArr[0] = 25.343;
doubleArr[1] = 36.763;
doubleArr[2] = 37.56;
doubleArr[3] = 386.63;
array = conn.createArrayOf("DOUBLE", doubleArr);
Array resultArray = rs.getArray(1);
assertEquals(resultArray, array);
assertEquals("[25.343, 36.763, 37.56, 386.63]", rs.getString(1));
assertEquals(rs.getString("B_string"), B_VALUE);
assertTrue(Floats.compare(rs.getFloat(3), 0.01f) == 0);
assertFalse(rs.next());
} finally {
conn.close();
}
}
@Override
public void setNonNullParameter(PreparedStatement ps, int i,
Integer[] parameter, JdbcType jdbcType) throws SQLException {
Connection c = ps.getConnection();
Array inArray = c.createArrayOf("smallint", parameter);
ps.setArray(i, inArray);
}
@Test
public void testVarLengthArrComparisonInWhereClauseWithDiffSizeArrays() throws Exception {
Connection conn;
PreparedStatement stmt;
ResultSet rs;
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
conn = DriverManager.getConnection(getUrl(), props);
String table = generateUniqueName();
conn.createStatement()
.execute(
"CREATE TABLE " + table + " ( k VARCHAR PRIMARY KEY, a_string_array VARCHAR(100) ARRAY[4], b_string_array VARCHAR(100) ARRAY[4])");
conn.close();
conn = DriverManager.getConnection(getUrl(), props);
stmt = conn.prepareStatement("UPSERT INTO " + table + " VALUES(?,?,?)");
stmt.setString(1, "a");
String[] s = new String[] { "abc", "def", "ghi", "jkll" };
Array array = conn.createArrayOf("VARCHAR", s);
stmt.setArray(2, array);
s = new String[] { "abc", "def", "ghi", "jklm" };
array = conn.createArrayOf("VARCHAR", s);
stmt.setArray(3, array);
stmt.execute();
conn.commit();
conn.close();
conn = DriverManager.getConnection(getUrl(), props);
rs = conn.createStatement().executeQuery(
"SELECT k, a_string_array[2] FROM " + table + " where a_string_array<b_string_array");
assertTrue(rs.next());
assertEquals("a", rs.getString(1));
assertEquals("def", rs.getString(2));
conn.close();
}
@Test
public void testArrayRefToLiteralCharArrayDiffLengths() throws Exception {
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
PreparedStatement stmt = conn.prepareStatement(TEST_QUERY);
// Test with each element of the char array having different lengths
Array array = conn.createArrayOf("CHAR", new String[] {"a","bb","ccc"});
stmt.setArray(1, array);
ResultSet rs = stmt.executeQuery();
assertTrue(rs.next());
assertEquals("bb", rs.getString(1));
assertFalse(rs.next());
}
}
protected static void initSimpleArrayTable(String tenantId, Date date, Long ts, boolean useNull) throws Exception {
Properties props = new Properties();
if (ts != null) {
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, ts.toString());
}
Connection conn = DriverManager.getConnection(getUrl(), props);
try {
// Insert all rows at ts
PreparedStatement stmt = conn.prepareStatement(
"upsert into " +SIMPLE_TABLE_WITH_ARRAY+
"(" +
" ORGANIZATION_ID, " +
" ENTITY_ID, " +
" x_double, " +
" a_double_array)" +
"VALUES (?, ?, ?, ?)");
stmt.setString(1, tenantId);
stmt.setString(2, ROW1);
stmt.setDouble(3, 1.2d);
// Need to support primitive
Double[] doubleArr = new Double[2];
doubleArr[0] = 64.87;
doubleArr[1] = 89.96;
//doubleArr[2] = 9.9;
Array array = conn.createArrayOf("DOUBLE", doubleArr);
stmt.setArray(4, array);
stmt.execute();
conn.commit();
} finally {
conn.close();
}
}
@Override
public void setNonNullParameter(PreparedStatement ps, int i,
String[] parameter, JdbcType jdbcType) throws SQLException {
Connection c = ps.getConnection();
Array inArray = c.createArrayOf("text", parameter);
ps.setArray(i, inArray);
}
@Test
public void testArrayWithDescOrder() throws Exception {
Connection conn;
PreparedStatement stmt;
ResultSet rs;
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
conn = DriverManager.getConnection(getUrl(), props);
String table = generateUniqueName();
conn.createStatement().execute(
"CREATE TABLE " + table + " ( k VARCHAR, a_string_array VARCHAR(100) ARRAY[4], b_string_array VARCHAR(100) ARRAY[4] \n"
+ " CONSTRAINT pk PRIMARY KEY (k, b_string_array DESC)) \n");
conn.close();
conn = DriverManager.getConnection(getUrl(), props);
stmt = conn.prepareStatement("UPSERT INTO " + table + " VALUES(?,?,?)");
stmt.setString(1, "a");
String[] s = new String[] { "abc", "def", "ghi", "jkll", null, null, "xxx" };
Array array = conn.createArrayOf("VARCHAR", s);
stmt.setArray(2, array);
s = new String[] { "abc", "def", "ghi", "jkll", null, null, null, "xxx" };
array = conn.createArrayOf("VARCHAR", s);
stmt.setArray(3, array);
stmt.execute();
conn.commit();
conn.close();
conn = DriverManager.getConnection(getUrl(), props);
rs = conn.createStatement().executeQuery("SELECT b_string_array FROM " + table);
assertTrue(rs.next());
PhoenixArray strArr = (PhoenixArray)rs.getArray(1);
assertEquals(array, strArr);
assertEquals("['abc', 'def', 'ghi', 'jkll', null, null, null, 'xxx']", rs.getString(1));
conn.close();
}
private void upsertValues(Connection conn, String tableName) throws SQLException {
PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES(?,?,?)");
stmt.setString(1, "a");
String[] s = new String[] { "abc", "def", "ghi", "jkll", null, null, "xxx" };
Array array = conn.createArrayOf("VARCHAR", s);
stmt.setArray(2, array);
s = new String[] { "abc", "def", "ghi", "jkll", null, null, null, "xxx" };
array = conn.createArrayOf("VARCHAR", s);
stmt.setArray(3, array);
stmt.execute();
conn.commit();
}
@Test
public void testArraySelectSingleArrayElemWithCast() throws Exception {
Connection conn;
PreparedStatement stmt;
ResultSet rs;
long ts = nextTimestamp();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
conn = DriverManager.getConnection(getUrl(), props);
conn.createStatement().execute("CREATE TABLE t ( k VARCHAR PRIMARY KEY, a bigint ARRAY[])");
conn.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
conn = DriverManager.getConnection(getUrl(), props);
stmt = conn.prepareStatement("UPSERT INTO t VALUES(?,?)");
stmt.setString(1, "a");
Long[] s = new Long[] {1l, 2l};
Array array = conn.createArrayOf("BIGINT", s);
stmt.setArray(2, array);
stmt.execute();
conn.commit();
conn.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
conn = DriverManager.getConnection(getUrl(), props);
rs = conn.createStatement().executeQuery("SELECT k, CAST(a[2] AS DOUBLE) FROM t");
assertTrue(rs.next());
assertEquals("a",rs.getString(1));
Double d = new Double(2.0);
assertEquals(d, (Double)rs.getDouble(2));
conn.close();
}
@Test
public void testArrayWithCast() throws Exception {
Connection conn;
PreparedStatement stmt;
ResultSet rs;
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
conn = DriverManager.getConnection(getUrl(), props);
String table = generateUniqueName();
conn.createStatement().execute("CREATE TABLE " + table + " ( k VARCHAR PRIMARY KEY, a bigint ARRAY[])");
conn.close();
conn = DriverManager.getConnection(getUrl(), props);
stmt = conn.prepareStatement("UPSERT INTO " + table + " VALUES(?,?)");
stmt.setString(1, "a");
Long[] s = new Long[] { 1l, 2l };
Array array = conn.createArrayOf("BIGINT", s);
stmt.setArray(2, array);
stmt.execute();
conn.commit();
conn.close();
conn = DriverManager.getConnection(getUrl(), props);
rs = conn.createStatement().executeQuery("SELECT CAST(a AS DOUBLE []) FROM " + table);
assertTrue(rs.next());
Double[] d = new Double[] { 1.0, 2.0 };
array = conn.createArrayOf("DOUBLE", d);
PhoenixArray arr = (PhoenixArray)rs.getArray(1);
assertEquals(array, arr);
assertEquals("[1.0, 2.0]", rs.getString(1));
conn.close();
}
@Override
public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
ps.setArray(i, (Array) parameter);
}
@Override
public void setNonNullParameter(PreparedStatement ps, int i, T parameter, JdbcType jdbcType) throws SQLException {
Array param = ps.getConnection().createArrayOf(getDbTypeName(ps.getConnection()), (Object[])parameter);
// ps.setObject(i, param, java.sql.Types.ARRAY);
ps.setArray(i, param);
}
private Connection upsertValues(Properties props, String tableName) throws SQLException, IOException,
InterruptedException {
Connection conn;
PreparedStatement stmt;
conn = getConnection();
stmt = upsertStmt(conn, tableName);
stmt.setString(1, "a");
String[] s = new String[] { "abc", "def", "ghi", "jkll", null, null, "xxx" };
Array array = conn.createArrayOf("VARCHAR", s);
stmt.setArray(2, array);
s = new String[] { "abc", "def", "ghi", "jkll", null, null, null, "xxx" };
array = conn.createArrayOf("VARCHAR", s);
stmt.setArray(3, array);
stmt.execute();
conn.commit();
stmt = upsertStmt(conn, tableName);
stmt.setString(1, "b");
s = new String[] { "xyz", "def", "ghi", "jkll", null, null, "xxx" };
array = conn.createArrayOf("VARCHAR", s);
stmt.setArray(2, array);
s = new String[] { "zya", "def", "ghi", "jkll", null, null, null, "xxx" };
array = conn.createArrayOf("VARCHAR", s);
stmt.setArray(3, array);
stmt.execute();
conn.commit();
stmt = upsertStmt(conn, tableName);
stmt.setString(1, "c");
s = new String[] { "xyz", "def", "ghi", "jkll", null, null, "xxx" };
array = conn.createArrayOf("VARCHAR", s);
stmt.setArray(2, array);
s = new String[] { "zya", "def", "ghi", "jkll", null, null, null, "xxx" };
array = conn.createArrayOf("VARCHAR", s);
stmt.setArray(3, array);
stmt.execute();
conn.commit();
stmt = upsertStmt(conn, tableName);
stmt.setString(1, "d");
s = new String[] { "xyz", "def", "ghi", "jkll", null, null, "xxx" };
array = conn.createArrayOf("VARCHAR", s);
stmt.setArray(2, array);
s = new String[] { "zya", "def", "ghi", "jkll", null, null, null, "xxx" };
array = conn.createArrayOf("VARCHAR", s);
stmt.setArray(3, array);
stmt.execute();
conn.commit();
stmt = upsertStmt(conn, tableName);
stmt.setString(1, "b");
s = new String[] { "xyz", "def", "ghi", "jkll", null, null, "xxx" };
array = conn.createArrayOf("VARCHAR", s);
stmt.setArray(2, array);
s = new String[] { "zya", "def", "ghi", "jkll", null, null, null, "xxx" };
array = conn.createArrayOf("VARCHAR", s);
stmt.setArray(3, array);
stmt.execute();
conn.commit();
stmt = upsertStmt(conn, tableName);
stmt.setString(1, "e");
s = new String[] { "xyz", "def", "ghi", "jkll", null, null, "xxx" };
array = conn.createArrayOf("VARCHAR", s);
stmt.setArray(2, array);
s = new String[] { "zya", "def", "ghi", "jkll", null, null, null, "xxx" };
array = conn.createArrayOf("VARCHAR", s);
stmt.setArray(3, array);
stmt.execute();
conn.commit();
return conn;
}
@Test(expected = SQLFeatureNotSupportedException.class)
public void assertSetArray() throws SQLException {
for (PreparedStatement each : statements) {
each.setArray(1, null);
}
}
@Test
public void testScanWithNonFixedWidthArrayInWhereClause() throws Exception {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(),
getDefaultSplits(tenantId), null, ts - 2);
initTablesWithArrays(tenantId, null, ts, false, getUrl());
String query = "SELECT a_double_array, /* comment ok? */ b_string, a_float FROM table_with_array WHERE ?=organization_id and ?=a_string_array";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB,
Long.toString(ts + 2)); // Execute at timestamp 2
Connection conn = DriverManager.getConnection(getUrl(), props);
try {
PreparedStatement statement = conn.prepareStatement(query);
statement.setString(1, tenantId);
// Need to support primitive
String[] strArr = new String[4];
strArr[0] = "ABC";
strArr[1] = "CEDF";
strArr[2] = "XYZWER";
strArr[3] = "AB";
Array array = conn.createArrayOf("VARCHAR", strArr);
statement.setArray(2, array);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
// Need to support primitive
Double[] doubleArr = new Double[4];
doubleArr[0] = 25.343;
doubleArr[1] = 36.763;
doubleArr[2] = 37.56;
doubleArr[3] = 386.63;
array = conn.createArrayOf("DOUBLE", doubleArr);
Array resultArray = rs.getArray(1);
assertEquals(resultArray, array);
assertEquals(rs.getString("B_string"), B_VALUE);
assertTrue(Floats.compare(rs.getFloat(3), 0.01f) == 0);
assertFalse(rs.next());
} finally {
conn.close();
}
}