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

The following examples show how to use java.sql.PreparedStatement#setArray() . These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may want to check out the right sidebar which shows the related API usage.
Example 1
Source Project: phoenix   File: ArrayIT.java    License: Apache License 2.0 6 votes vote down vote up
@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();
        }
    }

}
 
Example 2
Source Project: schedge   File: Utils.java    License: MIT License 5 votes vote down vote up
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());
  }
}
 
Example 3
@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);
    }
}
 
Example 4
Source Project: binnavi   File: PostgreSQLTypeFunctions.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * 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);
  }
}
 
Example 5
Source Project: dalesbred   File: OracleDialect.java    License: MIT License 5 votes vote down vote up
@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);
    }
}
 
Example 6
Source Project: phoenix   File: Array1IT.java    License: Apache License 2.0 5 votes vote down vote up
@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();
    }
}
 
Example 7
Source Project: mmpt   File: SmallIntArrayTypeHandler.java    License: MIT License 5 votes vote down vote up
@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);
}
 
Example 8
Source Project: phoenix   File: Array2IT.java    License: Apache License 2.0 5 votes vote down vote up
@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();
}
 
Example 9
Source Project: phoenix   File: Array2IT.java    License: Apache License 2.0 5 votes vote down vote up
@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());
    }
}
 
Example 10
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();
     }
}
 
Example 11
Source Project: mmpt   File: TextArrayTypeHandler.java    License: MIT License 5 votes vote down vote up
@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);
}
 
Example 12
Source Project: phoenix   File: Array1IT.java    License: Apache License 2.0 5 votes vote down vote up
@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();
}
 
Example 13
Source Project: phoenix   File: NoOpStatsCollectorIT.java    License: Apache License 2.0 5 votes vote down vote up
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();
}
 
Example 14
Source Project: phoenix   File: ArrayIT.java    License: Apache License 2.0 5 votes vote down vote up
@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();
}
 
Example 15
Source Project: phoenix   File: Array1IT.java    License: Apache License 2.0 5 votes vote down vote up
@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();
}
 
Example 16
@Override
public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
	ps.setArray(i, (Array) parameter);
}
 
Example 17
Source Project: mybatis-types   File: ArrayTypeHandler.java    License: MIT License 4 votes vote down vote up
@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);
}
 
Example 18
Source Project: phoenix   File: BaseStatsCollectorIT.java    License: Apache License 2.0 4 votes vote down vote up
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;
}
 
Example 19
@Test(expected = SQLFeatureNotSupportedException.class)
public void assertSetArray() throws SQLException {
    for (PreparedStatement each : statements) {
        each.setArray(1, null);
    }
}
 
Example 20
Source Project: phoenix   File: ArrayIT.java    License: Apache License 2.0 4 votes vote down vote up
@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();
	}
}