Example usage for java.sql Array getArray

List of usage examples for java.sql Array getArray

Introduction

In this page you can find the example usage for java.sql Array getArray.

Prototype

Object getArray() throws SQLException;

Source Link

Document

Retrieves the contents of the SQL ARRAY value designated by this Array object in the form of an array in the Java programming language.

Usage

From source file:InsertStores.java

public static void main(String args[]) {

    String url = "jdbc:mySubprotocol:myDataSource";
    Connection con;/*from w ww .jav  a 2s . co  m*/
    Statement stmt;
    try {
        Class.forName("myDriver.ClassName");

    } catch (java.lang.ClassNotFoundException e) {
        System.err.print("ClassNotFoundException: ");
        System.err.println(e.getMessage());
    }

    try {

        con = DriverManager.getConnection(url, "myLogin", "myPassword");

        stmt = con.createStatement();
        con.setAutoCommit(false);

        String insertStore1 = "INSERT INTO STORES VALUES (" + "100001, "
                + "ADDRESS(888, 'Main_Street', 'Rancho_Alegre', " + "'CA', '94049'), "
                + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', "
                + "'Colombian_Decaf', 'French_Roast_Decaf'), "
                + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000001))";

        stmt.addBatch(insertStore1);

        String insertStore2 = "INSERT INTO STORES VALUES (" + "100002, "
                + "ADDRESS(1560, 'Alder', 'Ochos_Pinos', " + "'CA', '94049'), "
                + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', "
                + "'Colombian_Decaf', 'French_Roast_Decaf', " + "'Kona', 'Kona_Decaf'), "
                + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000001))";

        stmt.addBatch(insertStore2);

        String insertStore3 = "INSERT INTO STORES VALUES (" + "100003, "
                + "ADDRESS(4344, 'First_Street', 'Verona', " + "'CA', '94545'), "
                + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', "
                + "'Colombian_Decaf', 'French_Roast_Decaf', " + "'Kona', 'Kona_Decaf'), "
                + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000002))";

        stmt.addBatch(insertStore3);

        String insertStore4 = "INSERT INTO STORES VALUES (" + "100004, "
                + "ADDRESS(321, 'Sandy_Way', 'La_Playa', " + "'CA', '94544'), "
                + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', "
                + "'Colombian_Decaf', 'French_Roast_Decaf', " + "'Kona', 'Kona_Decaf'), "
                + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000002))";

        stmt.addBatch(insertStore4);

        String insertStore5 = "INSERT INTO STORES VALUES (" + "100005, "
                + "ADDRESS(1000, 'Clover_Road', 'Happyville', " + "'CA', '90566'), "
                + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', "
                + "'Colombian_Decaf', 'French_Roast_Decaf'), "
                + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000003))";

        stmt.addBatch(insertStore5);

        int[] updateCounts = stmt.executeBatch();

        ResultSet rs = stmt.executeQuery("SELECT * FROM STORES");

        System.out.println("Table STORES after insertion:");
        System.out.println("STORE_NO  LOCATION          COF_TYPE     MGR");
        while (rs.next()) {
            int storeNo = rs.getInt("STORE_NO");
            Struct location = (Struct) rs.getObject("LOCATION");
            Object[] locAttrs = location.getAttributes();
            Array coffeeTypes = rs.getArray("COF_TYPE");
            String[] cofTypes = (String[]) coffeeTypes.getArray();

            Ref managerRef = rs.getRef("MGR");
            PreparedStatement pstmt = con.prepareStatement("SELECT MANAGER FROM MANAGERS WHERE OID = ?");
            pstmt.setRef(1, managerRef);
            ResultSet rs2 = pstmt.executeQuery();
            rs2.next();
            Struct manager = (Struct) rs2.getObject("MANAGER");
            Object[] manAttrs = manager.getAttributes();

            System.out.print(storeNo + "   ");
            System.out.print(locAttrs[0] + " " + locAttrs[1] + " " + locAttrs[2] + ", " + locAttrs[3] + "  "
                    + locAttrs[4] + " ");
            for (int i = 0; i < cofTypes.length; i++)
                System.out.print(cofTypes[i] + " ");
            System.out.println(manAttrs[1] + ", " + manAttrs[2]);

            rs2.close();
            pstmt.close();
        }

        rs.close();
        stmt.close();
        con.close();

    } catch (BatchUpdateException b) {
        System.err.println("-----BatchUpdateException-----");
        System.err.println("SQLState:  " + b.getSQLState());
        System.err.println("Message:  " + b.getMessage());
        System.err.println("Vendor:  " + b.getErrorCode());
        System.err.print("Update counts:  ");
        int[] updateCounts = b.getUpdateCounts();
        for (int i = 0; i < updateCounts.length; i++) {
            System.err.print(updateCounts[i] + "   ");
        }
        System.err.println("");

    } catch (SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
        System.err.println("SQLState:  " + ex.getSQLState());
        System.err.println("Message:  " + ex.getMessage());
        System.err.println("Vendor:  " + ex.getErrorCode());
    }
}

From source file:io.lightlink.types.ArrayConverter.java

@Override
public Object readFromCallableStatement(CallableStatement cs, int pos, RunnerContext runnerContext,
        String colName) throws SQLException, IOException {
    Array array = cs.getArray(pos);
    return array == null ? null : array.getArray();
}

From source file:io.lightlink.types.ArrayConverter.java

@Override
public Object readFromResultSet(ResultSet resultSet, int pos, RunnerContext runnerContext, String colName)
        throws SQLException, IOException {
    Array array = resultSet.getArray(pos);
    return array == null ? null : array.getArray();
}

From source file:annis.sqlgen.AnnotatedSpanExtractor.java

private List<Annotation> extractAnnotations(Array array) throws SQLException {
    List<Annotation> result = new ArrayList<>();

    if (array != null) {
        String[] arrayLines = (String[]) array.getArray();

        for (String line : arrayLines) {
            if (line != null) {
                String namespace = null;
                String name = null;
                String value = null;

                String[] split = line.split(":", 3);

                Preconditions.checkState(split.length == 3,
                        "The annotation string for the matrix entry must contain a namespace, name and value");

                if (split.length == 3) {
                    namespace = split[0];
                    name = split[1];/*from   ww  w  . j av a 2 s.c  o  m*/
                    value = split[2];
                }

                if ("".equals(namespace)) {
                    namespace = null;
                }

                result.add(new annis.model.Annotation(namespace, name, value));
            } // if line not null
        }
    }

    return result;
}

From source file:annis.sqlgen.AnnotatedSpanExtractor.java

@Override
public AnnotatedSpan mapRow(ResultSet resultSet, int rowNum) throws SQLException {
    long id = resultSet.getLong("id");
    String coveredText = resultSet.getString("span");

    Array arrayAnnotation = resultSet.getArray("annotations");
    ResultSetMetaData rsMeta = resultSet.getMetaData();
    Array arrayMeta = null;/*  w w  w.j  a  v  a2s . co m*/
    for (int i = 1; i <= rsMeta.getColumnCount(); i++) {
        if ("metadata".equals(rsMeta.getColumnName(i))) {
            arrayMeta = resultSet.getArray(i);
            break;
        }
    }

    List<Annotation> annotations = extractAnnotations(arrayAnnotation);
    List<Annotation> metaData = arrayMeta == null ? new LinkedList<Annotation>()
            : extractAnnotations(arrayMeta);

    // create key
    Array sqlKey = resultSet.getArray("key");
    Validate.isTrue(!resultSet.wasNull(), "Match group identifier must not be null");
    Validate.isTrue(sqlKey.getBaseType() == Types.BIGINT,
            "Key in database must be from the type \"bigint\" but was \"" + sqlKey.getBaseTypeName() + "\"");

    List<Long> key = Arrays.asList((Long[]) sqlKey.getArray());

    return new AnnotatedSpan(id, coveredText, annotations, metaData, key);
}

From source file:ru.org.linux.user.ProfileDao.java

@Nonnull
public Profile readProfile(@NotNull User user) {
    List<Profile> profiles = jdbcTemplate.query("SELECT settings, main FROM user_settings WHERE id=?",
            new RowMapper<Profile>() {
                @Override/* ww  w. ja  va 2 s  . c  o  m*/
                public Profile mapRow(ResultSet resultSet, int i) throws SQLException {
                    Array boxes = resultSet.getArray("main");

                    if (boxes != null) {
                        return new Profile(
                                new ProfileHashtable(DefaultProfile.getDefaultProfile(),
                                        (Map<String, String>) resultSet.getObject("settings")),
                                Arrays.asList((String[]) boxes.getArray()));
                    } else {
                        return new Profile(new ProfileHashtable(DefaultProfile.getDefaultProfile(),
                                (Map<String, String>) resultSet.getObject("settings")), null);
                    }
                }
            }, user.getId());

    if (profiles.isEmpty()) {
        return new Profile(
                new ProfileHashtable(DefaultProfile.getDefaultProfile(), new HashMap<String, String>()), null);
    } else {
        return profiles.get(0);
    }
}

From source file:annis.sqlgen.MatrixSqlGenerator.java

private List<Annotation> extractAnnotations(Array array) throws SQLException {
    List<Annotation> result = new ArrayList<Annotation>();

    if (array != null) {
        String[] arrayLines = (String[]) array.getArray();

        for (String line : arrayLines) {
            if (line != null) {
                String namespace = null;
                String name = null;
                String value = null;

                String[] split = line.split(":");
                if (split.length > 2) {
                    namespace = split[0];
                    name = split[1];//from   w w  w . ja  v a 2 s  . com
                    value = split[2];
                } else if (split.length > 1) {
                    name = split[0];
                    value = split[1];
                } else {
                    name = split[0];
                }

                if (value != null) {
                    try {
                        value = new String(Base64.decodeBase64(value), "UTF-8");
                    } catch (UnsupportedEncodingException ex) {
                        log.error(null, ex);
                    }
                }

                result.add(new annis.model.Annotation(namespace, name, value));
            } // if line not null
        }
    }

    return result;
}

From source file:annis.sqlgen.MatrixSqlGenerator.java

@Override
public List<AnnotatedMatch> extractData(ResultSet resultSet) throws SQLException, DataAccessException {
    List<AnnotatedMatch> matches = new ArrayList<AnnotatedMatch>();

    Map<List<Long>, AnnotatedSpan[]> matchesByGroup = new HashMap<List<Long>, AnnotatedSpan[]>();

    while (resultSet.next()) {
        long id = resultSet.getLong("id");
        String coveredText = resultSet.getString("span");

        Array arrayAnnotation = resultSet.getArray("annotations");
        Array arrayMeta = resultSet.getArray("metadata");

        List<Annotation> annotations = extractAnnotations(arrayAnnotation);
        List<Annotation> metaData = extractAnnotations(arrayMeta);

        // create key
        Array sqlKey = resultSet.getArray("key");
        Validate.isTrue(!resultSet.wasNull(), "Match group identifier must not be null");
        Validate.isTrue(sqlKey.getBaseType() == Types.BIGINT,
                "Key in database must be from the type \"bigint\" but was \"" + sqlKey.getBaseTypeName()
                        + "\"");

        Long[] keyArray = (Long[]) sqlKey.getArray();
        int matchWidth = keyArray.length;
        List<Long> key = Arrays.asList(keyArray);

        if (!matchesByGroup.containsKey(key)) {
            matchesByGroup.put(key, new AnnotatedSpan[matchWidth]);
        }//from   w  ww. j  ava2  s  .co  m

        // set annotation spans for *all* positions of the id
        // (node could have matched several times)
        for (int posInMatch = 0; posInMatch < key.size(); posInMatch++) {
            if (key.get(posInMatch) == id) {
                matchesByGroup.get(key)[posInMatch] = new AnnotatedSpan(id, coveredText, annotations, metaData);
            }
        }
    }

    for (AnnotatedSpan[] match : matchesByGroup.values()) {
        matches.add(new AnnotatedMatch(Arrays.asList(match)));
    }

    return matches;

}

From source file:org.apache.calcite.runtime.SqlFunctions.java

/** Converts a JDBC array to a list. */
public static List arrayToList(final java.sql.Array a) {
    if (a == null) {
        return null;
    }// w  ww  . j a v  a 2 s.c  om
    try {
        return Primitive.asList(a.getArray());
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

From source file:org.eclipse.ecr.core.storage.sql.jdbc.dialect.DialectPostgreSQL.java

@Override
@SuppressWarnings("boxing")
public Serializable getFromResultSet(ResultSet rs, int index, Column column) throws SQLException {
    switch (column.getJdbcType()) {
    case Types.VARCHAR:
    case Types.CLOB:
        return getFromResultSetString(rs, index, column);
    case Types.BIT:
        return rs.getBoolean(index);
    case Types.SMALLINT:
    case Types.INTEGER:
    case Types.BIGINT:
        return rs.getLong(index);
    case Types.DOUBLE:
        return rs.getDouble(index);
    case Types.TIMESTAMP:
        return getFromResultSetTimestamp(rs, index, column);
    case Types.ARRAY:
        Array array = rs.getArray(index);
        return array == null ? null : (Serializable) array.getArray();
    }/*from   ww  w.  j  a  va  2 s  .co  m*/
    throw new SQLException("Unhandled JDBC type: " + column.getJdbcType());
}