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

Example 26 with Array

use of java.sql.Array in project calcite-avatica by apache.

the class TypedValue method jdbcToSerial.

/** Converts a value from JDBC format to a type that can be serialized as
   * JSON. */
private static Object jdbcToSerial(ColumnMetaData.Rep rep, Object value, Calendar calendar, SqlType componentType) {
    if (null == value) {
        return null;
    }
    switch(rep) {
        case BYTE_STRING:
            return new ByteString((byte[]) value).toBase64String();
        case JAVA_UTIL_DATE:
        case JAVA_SQL_TIMESTAMP:
        case JAVA_SQL_DATE:
        case JAVA_SQL_TIME:
            long t = ((Date) value).getTime();
            if (calendar != null) {
                t += calendar.getTimeZone().getOffset(t);
            }
            switch(rep) {
                case JAVA_SQL_DATE:
                    return (int) DateTimeUtils.floorDiv(t, DateTimeUtils.MILLIS_PER_DAY);
                case JAVA_SQL_TIME:
                    return (int) DateTimeUtils.floorMod(t, DateTimeUtils.MILLIS_PER_DAY);
                default:
                    return t;
            }
        case ARRAY:
            Array array = (Array) value;
            Objects.requireNonNull(componentType, "Component Type must not be null for ARRAYs");
            try {
                switch(componentType) {
                    case BINARY:
                    case VARBINARY:
                    case LONGVARBINARY:
                        Object[] byteStrings = (Object[]) array.getArray();
                        List<String> convertedStrings = new ArrayList<>(byteStrings.length);
                        for (Object byteString : byteStrings) {
                            convertedStrings.add((String) jdbcToSerial(Rep.BYTE_STRING, byteString, calendar, null));
                        }
                        return convertedStrings;
                    case DATE:
                    case TIME:
                        Object[] dates = (Object[]) array.getArray();
                        List<Integer> serializedDates = new ArrayList<>(dates.length);
                        for (Object obj : dates) {
                            Date date = (Date) obj;
                            if (null == obj) {
                                serializedDates.add(null);
                            } else if (componentType == SqlType.DATE) {
                                serializedDates.add((int) jdbcToSerial(Rep.JAVA_SQL_DATE, date, calendar, null));
                            } else if (componentType == SqlType.TIME) {
                                serializedDates.add((int) jdbcToSerial(Rep.JAVA_SQL_TIME, date, calendar, null));
                            } else {
                                throw new RuntimeException("Unexpected type: " + componentType);
                            }
                        }
                        return serializedDates;
                    case TIMESTAMP:
                        Object[] timestamps = (Object[]) array.getArray();
                        List<Long> serializedTimestamps = new ArrayList<>(timestamps.length);
                        for (Object obj : timestamps) {
                            Timestamp timestamp = (Timestamp) obj;
                            if (null == obj) {
                                serializedTimestamps.add(null);
                            } else {
                                serializedTimestamps.add((long) jdbcToSerial(Rep.JAVA_SQL_TIMESTAMP, timestamp, calendar, null));
                            }
                        }
                        return serializedTimestamps;
                    default:
                        // Either a primitive array or Object[], converted into List<Object>
                        return AvaticaUtils.primitiveList(array.getArray());
                }
            } catch (SQLException e) {
                throw new RuntimeException("Could not obtain base array object", e);
            }
        default:
            return value;
    }
}

Example 27 with Array

use of java.sql.Array in project calcite-avatica by apache.

the class ArrayImplTest method testArrayWithOffsets.

@Test
public void testArrayWithOffsets() throws Exception {
    // Define the struct type we're creating
    ScalarType intType = ColumnMetaData.scalar(Types.INTEGER, "INTEGER", Rep.INTEGER);
    ArrayImpl.Factory factory = new ArrayFactoryImpl(Unsafe.localCalendar().getTimeZone());
    // Create some arrays from the structs
    Array array1 = factory.createArray(intType, Arrays.<Object>asList(1, 2));
    Array array3 = factory.createArray(intType, Arrays.<Object>asList(4, 5, 6));
    Object[] data = (Object[]) array1.getArray(2, 1);
    assertEquals(1, data.length);
    assertEquals(2, data[0]);
    data = (Object[]) array3.getArray(1, 1);
    assertEquals(1, data.length);
    assertEquals(4, data[0]);
    data = (Object[]) array3.getArray(2, 2);
    assertEquals(2, data.length);
    assertEquals(5, data[0]);
    assertEquals(6, data[1]);
    data = (Object[]) array3.getArray(1, 3);
    assertEquals(3, data.length);
    assertEquals(4, data[0]);
    assertEquals(5, data[1]);
    assertEquals(6, data[2]);
}

Example 28 with Array

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

the class StatsCollectorIT method testUpdateStatsWithMultipleTables.

@Test
public void testUpdateStatsWithMultipleTables() throws Throwable {
    String fullTableName2 = SchemaUtil.getTableName(schemaName, "T_" + generateUniqueName());
    Connection conn;
    PreparedStatement stmt;
    ResultSet rs;
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    conn = getConnection();
    conn.createStatement().execute("CREATE TABLE " + fullTableName + " ( 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))" + tableDDLOptions);
    conn.createStatement().execute("CREATE TABLE " + fullTableName2 + " ( 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))" + tableDDLOptions);
    String[] s;
    Array array;
    conn = upsertValues(props, fullTableName);
    conn = upsertValues(props, fullTableName2);
    // CAll the update statistics query here
    stmt = conn.prepareStatement("UPDATE STATISTICS " + fullTableName);
    stmt.execute();
    stmt = conn.prepareStatement("UPDATE STATISTICS " + fullTableName2);
    stmt.execute();
    stmt = upsertStmt(conn, fullTableName);
    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();
    stmt = upsertStmt(conn, fullTableName2);
    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 = getConnection();
    // This analyze would not work
    stmt = conn.prepareStatement("UPDATE STATISTICS " + fullTableName2);
    stmt.execute();
    rs = conn.createStatement().executeQuery("SELECT k FROM " + fullTableName2);
    assertTrue(rs.next());
    conn.close();
}

Example 29 with Array

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

the class StatsCollectorIT method upsertValues.

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 30 with Array

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

the class PhoenixHBaseLoaderIT method testLoadForArrayWithTable.

/**
     * 
     * @throws Exception
     */
@Test
public void testLoadForArrayWithTable() throws Exception {
    //create the table
    final String TABLE = "TABLE15";
    String ddl = "CREATE TABLE  " + TABLE + " ( ID INTEGER PRIMARY KEY, a_double_array double array[])";
    conn.createStatement().execute(ddl);
    Double[] doubleArr = new Double[3];
    doubleArr[0] = 2.2;
    doubleArr[1] = 4.4;
    doubleArr[2] = 6.6;
    Array doubleArray = conn.createArrayOf("DOUBLE", doubleArr);
    Tuple doubleArrTuple = Storage.tuple(2.2d, 4.4d, 6.6d);
    Double[] doubleArr2 = new Double[2];
    doubleArr2[0] = 12.2;
    doubleArr2[1] = 22.2;
    Array doubleArray2 = conn.createArrayOf("DOUBLE", doubleArr2);
    Tuple doubleArrTuple2 = Storage.tuple(12.2d, 22.2d);
    //upsert data.
    final String dml = "UPSERT INTO " + TABLE + " VALUES(?, ?) ";
    PreparedStatement stmt = conn.prepareStatement(dml);
    stmt.setInt(1, 1);
    stmt.setArray(2, doubleArray);
    stmt.execute();
    stmt.setInt(1, 2);
    stmt.setArray(2, doubleArray2);
    stmt.execute();
    conn.commit();
    final Data data = Storage.resetData(pigServer);
    List<Tuple> expectedList = new ArrayList<Tuple>();
    expectedList.add(Storage.tuple(1, doubleArrTuple));
    expectedList.add(Storage.tuple(2, doubleArrTuple2));
    pigServer.setBatchOn();
    pigServer.registerQuery(String.format("A = load 'hbase://table/%s' using " + PhoenixHBaseLoader.class.getName() + "('%s');", TABLE, zkQuorum));
    pigServer.registerQuery("STORE A INTO 'out' using mock.Storage();");
    pigServer.executeBatch();
    List<Tuple> actualList = data.get("out");
    assertEquals(expectedList.size(), actualList.size());
    assertEquals(expectedList, actualList);
}