Java Code Examples for java.sql.Connection#createArrayOf()

The following examples show how to use java.sql.Connection#createArrayOf() . 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 check out the related API usage on the sidebar.
Example 1
Source Project: phoenix   File: ArrayAppendFunctionIT.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void testArrayAppendFunctionWithNestedFunctions2() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());
    String tableName = initTables(conn);

    ResultSet rs;
    rs = conn.createStatement().executeQuery("SELECT ARRAY_APPEND(integers,ARRAY_ELEM(ARRAY[2,4],1)) FROM " + tableName + " WHERE region_name = 'SF Bay Area'");
    assertTrue(rs.next());

    Integer[] integers = new Integer[]{2345, 46345, 23234, 456, 2};

    Array array = conn.createArrayOf("INTEGER", integers);

    assertEquals(array, rs.getArray(1));
    assertFalse(rs.next());
}
 
Example 2
Source Project: phoenix   File: ArrayConcatFunctionIT.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void testArrayConcatFunctionWithNulls4() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());
    String tableName = initTables(conn);

    ResultSet rs;
    PreparedStatement st = conn.prepareStatement("SELECT ARRAY_CAT(?,?) FROM " + tableName + " WHERE region_name = 'SF Bay Area'");
    Array array1 = conn.createArrayOf("VARCHAR", new Object[]{null, "a", null, "b", "c", null, null});
    st.setArray(1, array1);
    Array array2 = conn.createArrayOf("VARCHAR", new Object[]{null, null, "a", null, "b", null, "c", null});
    st.setArray(2, array2);
    rs = st.executeQuery();
    assertTrue(rs.next());

    Array expected = conn.createArrayOf("VARCHAR", new Object[]{null, "a", null, "b", "c", null, null, null, null, "a", null, "b", null, "c", null});

    assertEquals(expected, rs.getArray(1));
    assertFalse(rs.next());
}
 
Example 3
Source Project: phoenix   File: ArrayConcatFunctionIT.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void testArrayConcatFunctionWithNestedFunctions2() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());
    String tableName = initTables(conn);

    ResultSet rs;
    rs = conn.createStatement().executeQuery("SELECT ARRAY_CAT(integers,ARRAY[ARRAY_ELEM(ARRAY[2,4],1),ARRAY_ELEM(ARRAY[2,4],2)]) FROM " + tableName + " WHERE region_name = 'SF Bay Area'");
    assertTrue(rs.next());

    Integer[] integers = new Integer[]{2345, 46345, 23234, 456, 2, 4};

    Array array = conn.createArrayOf("INTEGER", integers);

    assertEquals(array, rs.getArray(1));
    assertFalse(rs.next());
}
 
Example 4
Source Project: phoenix   File: ArrayConcatFunctionIT.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void testArrayConcatFunctionWithNulls2() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());
    String tableName = initTables(conn);

    ResultSet rs;
    PreparedStatement st = conn.prepareStatement("SELECT ARRAY_CAT(?,?) FROM " + tableName + " WHERE region_name = 'SF Bay Area'");
    Array array1 = conn.createArrayOf("VARCHAR", new Object[]{"a", "b", "c"});
    st.setArray(1, array1);
    Array array2 = conn.createArrayOf("VARCHAR", new Object[]{null, "a", "b", "c"});
    st.setArray(2, array2);
    rs = st.executeQuery();
    assertTrue(rs.next());

    Array expected = conn.createArrayOf("VARCHAR", new Object[]{"a", "b", "c", null, "a", "b", "c"});

    assertEquals(expected, rs.getArray(1));
    assertFalse(rs.next());
}
 
Example 5
Source Project: phoenix   File: ArrayFillFunctionIT.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void testArrayFillFunctionWithNestedFunctions2() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());
    

    ResultSet rs;
    rs = conn.createStatement().executeQuery(
        "SELECT ARRAY_FILL('hello', ARRAY_LENGTH(ARRAY[34, 45])) FROM " + tableName
            + " WHERE region_name = 'SF Bay Area'");
    assertTrue(rs.next());

    Object[] objects = new Object[]{"hello", "hello"};

    Array array = conn.createArrayOf("VARCHAR", objects);

    assertEquals(array, rs.getArray(1));
    assertFalse(rs.next());
}
 
Example 6
Source Project: phoenix   File: ArrayAppendFunctionIT.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void testArrayAppendFunctionDoublesWithNull() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());
    String tableName = initTables(conn);

    ResultSet rs;
    rs = conn.createStatement().executeQuery("SELECT ARRAY_APPEND(doubles,NULL) FROM " + tableName + " WHERE region_name = 'SF Bay Area'");
    assertTrue(rs.next());

    Double[] doubles = new Double[]{23.45, 46.345, 23.234, 45.6, 5.78};

    Array array = conn.createArrayOf("DOUBLE", doubles);

    assertEquals(array, rs.getArray(1));
    assertFalse(rs.next());
}
 
Example 7
Source Project: phoenix   File: Array2IT.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void testArrayIndexUsedInGroupByClause() throws Exception {
    String tenantId = getOrganizationId();
    String table = createTableWithArray(getUrl(),
            getDefaultSplits(tenantId), null);
    initTablesWithArrays(table, tenantId, null, false, getUrl());
    String query = "SELECT a_double_array[2] FROM " + table + "  GROUP BY a_double_array[2]";
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        Double[] doubleArr = new Double[1];
        doubleArr[0] = 40.0;
        conn.createArrayOf("DOUBLE", doubleArr);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        doubleArr = new Double[1];
        doubleArr[0] = 36.763;
        Double result =  rs.getDouble(1);
        assertEquals(doubleArr[0], result);
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 
Example 8
Source Project: phoenix   File: ArrayAppendFunctionIT.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void testArrayAppendFunctionWithUpsert2() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());

    String tableName = generateUniqueName();
    String ddl = "CREATE TABLE " + tableName + " (region_name VARCHAR PRIMARY KEY,integers INTEGER[])";
    conn.createStatement().execute(ddl);

    String dml = "UPSERT INTO " + tableName + "(region_name,integers) VALUES('SF Bay Area',ARRAY_APPEND(ARRAY[4,5],6))";
    conn.createStatement().execute(dml);
    conn.commit();

    ResultSet rs;
    rs = conn.createStatement().executeQuery("SELECT integers FROM " + tableName + " WHERE region_name = 'SF Bay Area'");
    assertTrue(rs.next());

    Integer[] integers = new Integer[]{4, 5, 6};

    Array array = conn.createArrayOf("INTEGER", integers);

    assertEquals(array, rs.getArray(1));
    assertFalse(rs.next());
}
 
Example 9
Source Project: phoenix   File: ArrayFillFunctionIT.java    License: Apache License 2.0 6 votes vote down vote up
@Test(expected = IllegalArgumentException.class)
public void testArrayFillFunctionInvalidLength2() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());
    

    ResultSet rs;
    rs = conn.createStatement().executeQuery(
        "SELECT ARRAY_FILL(\"timestamp\",length1) FROM " + tableName
            + " WHERE region_name = 'SF Bay Area'");
    assertTrue(rs.next());

    Object[] objects = new Object[]{new Timestamp(1432102334184l), new Timestamp(1432102334184l), new Timestamp(1432102334184l)};

    Array array = conn.createArrayOf("TIMESTAMP", objects);
    assertEquals(array, rs.getArray(1));
    assertFalse(rs.next());
}
 
Example 10
Source Project: phoenix   File: ArrayAppendFunctionIT.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void testArrayAppendFunctionWithUpsert3() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());

    String tableName = generateUniqueName();
    String ddl = "CREATE TABLE " + tableName + " (region_name VARCHAR PRIMARY KEY,doubles DOUBLE[])";
    conn.createStatement().execute(ddl);

    String dml = "UPSERT INTO " + tableName + "(region_name,doubles) VALUES('SF Bay Area',ARRAY_APPEND(ARRAY[5.67,7.87],9.0))";
    conn.createStatement().execute(dml);
    conn.commit();

    ResultSet rs;
    rs = conn.createStatement().executeQuery("SELECT doubles FROM " + tableName + " WHERE region_name = 'SF Bay Area'");
    assertTrue(rs.next());

    Double[] doubles = new Double[]{5.67, 7.87, new Double(9)};

    Array array = conn.createArrayOf("DOUBLE", doubles);

    assertEquals(array, rs.getArray(1));
    assertFalse(rs.next());
}
 
Example 11
Source Project: phoenix   File: ArrayFillFunctionIT.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void testArrayFillFunctionWithNestedFunctions3() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());
    

    ResultSet rs;
    rs = conn.createStatement().executeQuery(
        "SELECT ARRAY_FILL(3.4, ARRAY_LENGTH(ARRAY[34, 45])) FROM " + tableName
            + " WHERE region_name = 'SF Bay Area'");
    assertTrue(rs.next());

    Object[] objects = new Object[]{BigDecimal.valueOf(3.4), BigDecimal.valueOf(3.4)};

    Array array = conn.createArrayOf("DECIMAL", objects);

    assertEquals(array, rs.getArray(1));
    assertFalse(rs.next());
}
 
Example 12
Source Project: phoenix   File: ArrayFillFunctionIT.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void testArrayFillFunctionWithNestedFunctions1() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());
    

    ResultSet rs;
    rs = conn.createStatement().executeQuery(
        "SELECT ARRAY_FILL(ARRAY_ELEM(ARRAY[23,45],1),3) FROM " + tableName
            + " WHERE region_name = 'SF Bay Area'");
    assertTrue(rs.next());

    Integer[] integers = new Integer[]{23, 23, 23};

    Array array = conn.createArrayOf("INTEGER", integers);

    assertEquals(array, rs.getArray(1));
    assertFalse(rs.next());
}
 
Example 13
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 14
Source Project: phoenix   File: StatsCollectorIT.java    License: Apache License 2.0 5 votes vote down vote up
@Test
public void testUpdateStatsForTheTable() throws Throwable {
    Connection conn;
    PreparedStatement stmt;
    ResultSet rs;
    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, 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");
    String[] s;
    Array array;
    conn = upsertValues(props, "t");
    // CAll the update statistics query here. If already major compaction has run this will not get executed.
    stmt = conn.prepareStatement("UPDATE STATISTICS T");
    stmt.execute();
    stmt = upsertStmt(conn, "t");
    stmt.setString(1, "z");
    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.close();
    conn = DriverManager.getConnection(getUrl(), props);
    // This analyze would not work
    stmt = conn.prepareStatement("UPDATE STATISTICS T");
    stmt.execute();
    rs = conn.createStatement().executeQuery("SELECT k FROM T");
    assertTrue(rs.next());
    conn.close();
}
 
Example 15
Source Project: phoenix   File: Array2IT.java    License: Apache License 2.0 5 votes vote down vote up
@Test
public void testUpsertValuesWithNull() throws Exception {

    String tenantId = getOrganizationId();
    String table = createTableWithArray(getUrl(), getDefaultSplits(tenantId), null);
    String query = "upsert into  " + table + " (ORGANIZATION_ID,ENTITY_ID,a_double_array) values('" + tenantId
            + "','00A123122312312',null)";
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
                                                                             // at
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        int executeUpdate = statement.executeUpdate();
        assertEquals(1, executeUpdate);
        conn.commit();
        statement.close();
        conn.close();
        // create another connection
        props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        conn = DriverManager.getConnection(getUrl(), props);
        query = "SELECT ARRAY_ELEM(a_double_array,2) FROM  " + table;
        statement = conn.prepareStatement(query);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        // Need to support primitive
        Double[] doubleArr = new Double[1];
        doubleArr[0] = 0.0d;
        conn.createArrayOf("DOUBLE", doubleArr);
        Double result = rs.getDouble(1);
        assertEquals(doubleArr[0], result);
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 
Example 16
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 17
Source Project: phoenix   File: Array2IT.java    License: Apache License 2.0 5 votes vote down vote up
@Test
public void testArrayConstructorWithMultipleRows2() throws Exception {

    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    String table = generateUniqueName();
    String ddl = "CREATE TABLE  " + table + "  (region_name VARCHAR PRIMARY KEY, a INTEGER, b INTEGER)";
    conn.createStatement().execute(ddl);
    conn.commit();
    conn.close();
    conn = DriverManager.getConnection(getUrl(), props);
    PreparedStatement stmt = conn.prepareStatement("UPSERT INTO  " + table + " (region_name, a, b) VALUES('a', 6,3)");
    stmt.execute();
    stmt = conn.prepareStatement("UPSERT INTO  " + table + " (region_name, a, b) VALUES('b', 2,4)");
    stmt.execute();
    stmt = conn.prepareStatement("UPSERT INTO  " + table + " (region_name, a, b) VALUES('c', 6,3)");
    stmt.execute();
    conn.commit();
    conn.close();
    conn = DriverManager.getConnection(getUrl(), props);
    ResultSet rs;
    rs = conn.createStatement().executeQuery("SELECT ARRAY[a,b] from  " + table + " ");
    assertTrue(rs.next());
    Array arr = conn.createArrayOf("INTEGER", new Object[]{6, 3});
    assertEquals(arr, rs.getArray(1));
    rs.next();
    arr = conn.createArrayOf("INTEGER", new Object[]{2, 4});
    assertEquals(arr, rs.getArray(1));
    rs.next();
    arr = conn.createArrayOf("INTEGER", new Object[]{6, 3});
    assertEquals(arr, rs.getArray(1));
    rs.next();
}
 
Example 18
Source Project: phoenix   File: ArrayIT.java    License: Apache License 2.0 5 votes vote down vote up
@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);
    conn.close();
}
 
Example 19
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 20
Source Project: phoenix   File: Array1IT.java    License: Apache License 2.0 4 votes vote down vote up
@Test
public void testArraySelectGetString() throws Exception {
    Connection conn;
    PreparedStatement stmt;

    String tenantId = getOrganizationId();

    // create the table
    String tableName = createTableWithAllArrayTypes(getUrl(), getDefaultSplits(tenantId), null);

    // populate the table with data
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    conn = DriverManager.getConnection(getUrl(), props);
    stmt =
            conn.prepareStatement("UPSERT INTO "
                    + tableName
                    + "(ORGANIZATION_ID, ENTITY_ID, BOOLEAN_ARRAY, BYTE_ARRAY, DOUBLE_ARRAY, FLOAT_ARRAY, INT_ARRAY, LONG_ARRAY, SHORT_ARRAY, STRING_ARRAY)\n"
                    + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
    stmt.setString(1, tenantId);
    stmt.setString(2, ROW1);
    // boolean array
    Array boolArray = conn.createArrayOf("BOOLEAN", new Boolean[] { true, false });
    int boolIndex = 3;
    stmt.setArray(boolIndex, boolArray);
    // byte array
    Array byteArray = conn.createArrayOf("TINYINT", new Byte[] { 11, 22 });
    int byteIndex = 4;
    stmt.setArray(byteIndex, byteArray);
    // double array
    Array doubleArray = conn.createArrayOf("DOUBLE", new Double[] { 67.78, 78.89 });
    int doubleIndex = 5;
    stmt.setArray(doubleIndex, doubleArray);
    // float array
    Array floatArray = conn.createArrayOf("FLOAT", new Float[] { 12.23f, 45.56f });
    int floatIndex = 6;
    stmt.setArray(floatIndex, floatArray);
    // int array
    Array intArray = conn.createArrayOf("INTEGER", new Integer[] { 5555, 6666 });
    int intIndex = 7;
    stmt.setArray(intIndex, intArray);
    // long array
    Array longArray = conn.createArrayOf("BIGINT", new Long[] { 7777777L, 8888888L });
    int longIndex = 8;
    stmt.setArray(longIndex, longArray);
    // short array
    Array shortArray = conn.createArrayOf("SMALLINT", new Short[] { 333, 444 });
    int shortIndex = 9;
    stmt.setArray(shortIndex, shortArray);
    // create character array
    Array stringArray = conn.createArrayOf("VARCHAR", new String[] { "a", "b" });
    int stringIndex = 10;
    stmt.setArray(stringIndex, stringArray);
    stmt.execute();
    conn.commit();
    conn.close();

    conn = DriverManager.getConnection(getUrl(), props);
    stmt =
            conn.prepareStatement("SELECT organization_id, entity_id, boolean_array, byte_array, double_array, float_array, int_array, long_array, short_array, string_array FROM "
                    + tableName);
    TestUtil.analyzeTable(conn, tableName);

    ResultSet rs = stmt.executeQuery();
    assertTrue(rs.next());

    assertEquals(tenantId, rs.getString(1));
    assertEquals(ROW1, rs.getString(2));
    
    assertArrayGetString(rs, boolIndex, boolArray, "true, false");
    assertArrayGetString(rs, byteIndex, byteArray, "11, 22");
    assertArrayGetString(rs, doubleIndex, doubleArray, "67.78, 78.89");
    assertArrayGetString(rs, floatIndex, floatArray, "12.23, 45.56");
    assertArrayGetString(rs, intIndex, intArray, "5555, 6666");
    assertArrayGetString(rs, longIndex, longArray, "7777777, 8888888");
    assertArrayGetString(rs, shortIndex, shortArray, "333, 444");
    assertArrayGetString(rs, stringIndex, stringArray, "'a', 'b'");
    conn.close();
}