Java Code Examples for java.sql.PreparedStatement#setArray()

@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();
        }
    }

}
 

@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());
    }
}
 

@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();
}
 

@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();
}
 

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 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();
}
 

@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);
}
 

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();
     }
}
 

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());
  }
}
 

@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();
}
 

@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 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 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);
    }
}
 

/**
 * 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 set(PreparedStatement target, Array value, int columnIndex, Context context) throws SQLException {
    if (value == null) {
        target.setNull(columnIndex, Types.ARRAY);
    } else {
        target.setArray(columnIndex, value);
    }
}
 

@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;
}
 

@Override
public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
	ps.setArray(i, (Array) parameter);
}
 

@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();
	}
}