Examples with Array java.sql.Array used on opensource projects

Example 96 with Array

use of java.sql.Array in project phoenix by apache.

the class ArrayIT method testScanByArrayValue.

@Test
public void testScanByArrayValue() 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_float";
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, // Execute at timestamp 2
    Long.toString(ts + 2));
    Connection conn = DriverManager.getConnection(getUrl(), props);
    analyzeTable(conn, TABLE_WITH_ARRAY);
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        statement.setFloat(2, 0.01f);
        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 array = conn.createArrayOf("DOUBLE", doubleArr);
        PhoenixArray resultArray = (PhoenixArray) 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 97 with Array

use of java.sql.Array in project phoenix by apache.

the class ArrayIT method testArrayWithCast.

@Test
public void testArrayWithCast() 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 CAST(a AS DOUBLE []) FROM t");
    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 98 with Array

use of java.sql.Array in project phoenix by apache.

the class ArrayIT method testVarLengthArrComparisonInWhereClauseWithSameArrays.

@Test
public void testVarLengthArrComparisonInWhereClauseWithSameArrays() 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_same_size ( k VARCHAR PRIMARY KEY, a_string_array VARCHAR(100) ARRAY[4], b_string_array VARCHAR(100) ARRAY[4])");
    conn.close();
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
    conn = DriverManager.getConnection(getUrl(), props);
    stmt = conn.prepareStatement("UPSERT INTO t_same_size VALUES(?,?,?)");
    stmt.setString(1, "a");
    String[] s = new String[] { "abc", "def", "ghi", "jkl" };
    Array array = conn.createArrayOf("VARCHAR", s);
    stmt.setArray(2, array);
    s = new String[] { "abc", "def", "ghi", "jkl" };
    array = conn.createArrayOf("VARCHAR", s);
    stmt.setArray(3, 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, a_string_array[2] FROM t_same_size where a_string_array=b_string_array");
    assertTrue(rs.next());
    assertEquals("a", rs.getString(1));
    assertEquals("def", rs.getString(2));
    conn.close();
}

Example 99 with Array

use of java.sql.Array in project phoenix by apache.

the class ArrayIT method testArrayConstructorWithMultipleRows5.

@Test
public void testArrayConstructorWithMultipleRows5() throws Exception {
    long ts = nextTimestamp();
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
    Connection conn = DriverManager.getConnection(getUrl(), props);
    String ddl = "CREATE TABLE regions5 (region_name VARCHAR PRIMARY KEY, a VARCHAR, b VARCHAR)";
    conn.createStatement().execute(ddl);
    conn.commit();
    conn.close();
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 20));
    conn = DriverManager.getConnection(getUrl(), props);
    PreparedStatement stmt = conn.prepareStatement("UPSERT INTO regions5(region_name, a, b) VALUES('a', 'foo', 'abc')");
    stmt.execute();
    stmt = conn.prepareStatement("UPSERT INTO regions5(region_name, a, b) VALUES('b', 'abc', 'dfg')");
    stmt.execute();
    stmt = conn.prepareStatement("UPSERT INTO regions5(region_name, a, b) VALUES('c', 'foo', 'abc')");
    stmt.execute();
    conn.commit();
    conn.close();
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
    conn = DriverManager.getConnection(getUrl(), props);
    ResultSet rs;
    rs = conn.createStatement().executeQuery("SELECT ARRAY_APPEND(ARRAY[a,b], 'oo') from regions5");
    assertTrue(rs.next());
    Array arr = conn.createArrayOf("VARCHAR", new Object[] { "foo", "abc", "oo" });
    assertEquals(arr, rs.getArray(1));
    rs.next();
    arr = conn.createArrayOf("VARCHAR", new Object[] { "abc", "dfg", "oo" });
    assertEquals(arr, rs.getArray(1));
    rs.next();
    arr = conn.createArrayOf("VARCHAR", new Object[] { "foo", "abc", "oo" });
    assertEquals(arr, rs.getArray(1));
    rs.next();
}

Example 100 with Array

use of java.sql.Array in project phoenix by apache.

the class ArrayIT method initSimpleArrayTable.

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, a_char_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);
        // create character array
        String[] charArr = new String[2];
        charArr[0] = "a";
        charArr[1] = "b";
        array = conn.createArrayOf("CHAR", charArr);
        stmt.setArray(5, array);
        stmt.execute();
        conn.commit();
    } finally {
        conn.close();
    }
}