Example usage for java.sql Array getArray

List of usage examples for java.sql Array getArray

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Array
  6. 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:org.intermine.bio.dataconversion.GoAnnotationDbConverter.java

/**
 * {@inheritDoc}//from   w ww.j  a v  a 2s . c om
 */
public void processGOAnnotations(Connection connection) throws SQLException, ObjectStoreException {

    Item organism = newOrganism("4932");

    ResultSet res = PROCESSOR.getGOAnnotations(connection);

    while (res.next()) {

        String productId = res.getString("sgdid");
        String symbol = res.getString("gene_name");
        String qualifier = res.getString("go_qualifier");
        String goId = res.getString("goid");
        String pub = res.getString("pmid");
        String pubxref = res.getString("sgdrefid");
        String strEvidence = res.getString("evidence_code");
        Array withTextDb = res.getArray("withText");
        String annotType = res.getString("annotation_type");
        String taxonId = parseTaxonId(res.getString("taxid"));
        String dataSourceCode = res.getString("source");
        Array annotExt = res.getArray("annotext");
        Array annotExtPre = res.getArray("annotextension");

        String annotationExtension = "";
        String[] pre = (String[]) annotExtPre.getArray();
        String[] val = (String[]) annotExt.getArray();
        for (int i = 0; i < pre.length; i++) {
            if (pre[i] != null && val[i] != null && i < (pre.length - 1)) {
                annotationExtension += pre[i] + " (" + val[i] + "); ";
            } else if (pre[i] != null && i < pre.length) {
                annotationExtension += pre[i] + " (" + val[i] + ")";
            }
        }

        String[] with = (String[]) withTextDb.getArray();
        String withText = "";
        for (int j = 0; j < with.length; j++) {
            if (with[j] != null && j < (with.length - 1)) {
                withText += with[j] + ",";
            } else if (with[j] != null && j < with.length) {
                withText += with[j];
            }
        }

        if (StringUtils.isNotEmpty(strEvidence)) {
            storeEvidenceCode(strEvidence, annotType, withText);
        } else {
            throw new IllegalArgumentException("Evidence is a required column but not " + "found for goterm "
                    + goId + " and productId " + productId);
        }

        GoTermToGene key = new GoTermToGene(productId, goId, qualifier, withText, annotationExtension, pub,
                pubxref); // create unique key for go annotation

        String dataSource = DATA_SOURCE_NAME;
        String type = "gene";

        String productIdentifier = newProduct(productId, type, organism, dataSource, dataSourceCode, true,
                "primaryIdentifier");

        if (productIdentifier != null) { // null if resolver could not resolve an identifier

            String pubRefId = newPublication(pub, pubxref); // null if no pub found

            Set<Evidence> allEvidenceForAnnotation = goTermGeneToEvidence.get(key); // get evidence codes for this goterm|gene pair

            // new evidence
            String newStrEvidence = "";
            if (StringUtils.isEmpty(withText)) {
                newStrEvidence = strEvidence + ":" + annotType;
            } else {
                newStrEvidence = strEvidence + ":" + annotType + ":" + withText;
            }

            if (allEvidenceForAnnotation == null) { //|| !StringUtils.isEmpty(withText)
                String goTermIdentifier = newGoTerm(goId, dataSource, dataSourceCode);
                Evidence evidence = new Evidence(newStrEvidence, pubRefId, withText, organism, dataSource,
                        dataSourceCode);
                allEvidenceForAnnotation = new LinkedHashSet<Evidence>();
                allEvidenceForAnnotation.add(evidence);
                goTermGeneToEvidence.put(key, allEvidenceForAnnotation);
                Integer storedAnnotationId = createGoAnnotation(productIdentifier, type, goTermIdentifier,
                        organism, qualifier, dataSource, dataSourceCode, annotationExtension);
                evidence.setStoredAnnotationId(storedAnnotationId);
            } else {
                boolean seenEvidenceCode = false;
                Integer storedAnnotationId = null;

                for (Evidence evidence : allEvidenceForAnnotation) {
                    String evidenceCode = evidence.getEvidenceCode();
                    storedAnnotationId = evidence.storedAnnotationId;
                    // already have evidence code, just add pub
                    if (evidenceCode.equals(newStrEvidence)) {
                        evidence.addPublicationRefId(pubRefId);
                        seenEvidenceCode = true;
                    }
                }
                if (!seenEvidenceCode) {
                    Evidence evidence = new Evidence(newStrEvidence, pubRefId, withText, organism, dataSource,
                            dataSourceCode);
                    evidence.storedAnnotationId = storedAnnotationId;
                    allEvidenceForAnnotation.add(evidence);
                }
            }
        }
    }
    storeProductCollections();
    storeEvidence();
}

From source file:org.intermine.bio.dataconversion.SgdConverter.java

private void processPhenotypes(Connection connection) throws SQLException, ObjectStoreException {

    ResultSet res = PROCESSOR.getPhenotypeResults(connection);
    System.out.println("Processing Phenotypes...");
    while (res.next()) {

        String geneFeatureNo = res.getString("dbentity_id");
        String phenotypeAnnotNo = res.getString("annotation_id");
        String groupNo = res.getString("group_id");
        String experimentType = res.getString("experiment");
        String experimentComment = res.getString("experiment_comment");
        String alleleComment = res.getString("allele_comment");
        String reporterComment = res.getString("reporter_comment");
        String mutantType = res.getString("mutant");
        String qualifier_observable = res.getString("phenotype");
        String strainBackground = res.getString("strain_name");
        String reporter = res.getString("reporter");
        String allele = res.getString("allele");
        String assay = res.getString("assay");
        String details = res.getString("details");
        String pmid = res.getString("pmid");
        String refNo = res.getString("refNo");

        Array cond_class = res.getArray("condclass");
        String[] str_cond_class = (String[]) cond_class.getArray();

        Array cond_name = res.getArray("condname");
        String[] str_cond_name = (String[]) cond_name.getArray();

        Array cond_value = res.getArray("condvalue");
        String[] str_cond_value = (String[]) cond_value.getArray();

        Array cond_unit = res.getArray("condunit");
        String[] str_cond_unit = (String[]) cond_unit.getArray();

        String qualifier = "";
        String observable = "";
        if (qualifier_observable.contains(":")) {
            String t[] = qualifier_observable.split(":");
            qualifier = t[1].trim();//from  ww  w  .j a v a2s.  co m
            observable = t[0].trim();
        } else {
            observable = qualifier_observable.trim();
        }

        String strain = "";
        if (StringUtils.isNotEmpty(strainBackground)) {
            String q[] = strainBackground.split("_");
            int len = q.length;
            if (len == 3) {
                strain = q[len - 1];
            } else {
                strain = "Other";
            }
        }
        Item gene = genes.get(geneFeatureNo);

        String chemical = "";
        String condition = "";
        String chemcond = getPhenotypeCondition(str_cond_class, str_cond_name, str_cond_value, str_cond_unit);
        //System.out.println("chemcond is...." + chemcond);
        String cc[] = chemcond.split("_");
        chemical = cc[0];
        condition = cc[1];

        getPhenotype(phenotypeAnnotNo, groupNo, qualifier, observable, experimentType, experimentComment,
                alleleComment, reporterComment, strain, mutantType, reporter, allele, assay, chemical,
                condition, details, pmid, refNo, gene);

    }

}

From source file:org.kawanfw.test.api.client.ArrayTest.java

/**
 * @param connection/*from w w w. j  a v  a2  s . co  m*/
 *            the AceQL Connection
 * 
 * @throws SQLException
 * @throws Exception
 */
public void test(Connection connection) throws SQLException, Exception {

    MessageDisplayer.initClassDisplay(this.getClass().getSimpleName());

    DatabaseMetaData databaseMetaData = connection.getMetaData();
    MessageDisplayer.display(
            "databaseMetaData.getDatabaseProductName()   : " + databaseMetaData.getDatabaseProductName());

    if (connection instanceof RemoteConnection) {
        RemoteConnection connectionHttp = (RemoteConnection) connection;
        if (connectionHttp.isStatelessMode()) {
            MessageDisplayer.display("Arrays are not supported in stateless mode");
            return;
        }
    }

    if (!new SqlUtil(connection).isPostgreSQL() && !new SqlUtil(connection).isHSQLDB()
            && !new SqlUtil(connection).isOracle()) {
        MessageDisplayer.display("Arrays tests are only supported in HSQLDB, Oracle Database & PostgreSQL");
        return;
    }

    Object[] northEastRegion = { "10022", "02110", "07399" };

    String sql = null;
    boolean doInsert = true;

    if (doInsert) {

        // Array aArray = connection.createArrayOf("varchar",
        // northEastRegion);

        Array aArray = null;

        if (new SqlUtil(connection).isOracle()) {
            aArray = connection.createArrayOf("VCARRAY", northEastRegion);
        } else {
            aArray = connection.createArrayOf("varchar", northEastRegion);
        }

        PreparedStatement pstmt = connection.prepareStatement("delete from REGIONS where region_name = ?");
        pstmt.setString(1, "NorthEast");
        pstmt.executeUpdate();
        pstmt.close();

        PreparedStatement pstmt2 = connection
                .prepareStatement("insert into REGIONS (region_name, zips) " + "VALUES (?, ?)");
        pstmt2.setString(1, "NorthEast");
        pstmt2.setArray(2, aArray);
        pstmt2.executeUpdate();
        pstmt2.close();

    }

    sql = "select * from REGIONS";
    PreparedStatement prepStatement = connection.prepareStatement(sql);
    ResultSet rs = prepStatement.executeQuery();

    while (rs.next()) {
        String region_name = rs.getString(1);
        MessageDisplayer.display(region_name + "  ");

        Array z = rs.getArray("ZIPS");

        Object[] zips = (Object[]) z.getArray();

        for (int i = 0; i < zips.length; i++) {
            System.out.print(zips[i] + " ");
        }
        MessageDisplayer.display("");

        if (region_name.equals("NorthEast")) {
            MessageDisplayer.display("Region is NorthEast. Test arrays equality");

            Assert.assertArrayEquals("Region is NorthEast. Test arrays equality", northEastRegion, zips);
        }
    }

    rs.close();

    MessageDisplayer.display("Done!");

}

From source file:org.nuxeo.ecm.core.storage.sql.jdbc.dialect.DialectOracle.java

@Override
public Serializable[] getArrayResult(Array array) throws SQLException {
    Serializable[] ids;/*from   w  w w .ja  va 2 s. c o  m*/
    if (array.getBaseType() == Types.NUMERIC) {
        long[] longs;
        try {
            longs = (long[]) arrayGetLongArrayMethod.invoke(array);
        } catch (IllegalAccessException e) {
            throw new RuntimeException(e);
        } catch (IllegalArgumentException e) {
            throw new RuntimeException(e);
        } catch (InvocationTargetException e) {
            throw new RuntimeException(e);
        }
        ids = new Serializable[longs.length];
        for (int i = 0; i < ids.length; i++) {
            ids[i] = Long.valueOf(longs[i]);
        }
    } else {
        ids = (Serializable[]) array.getArray();
    }
    return ids;
}

From source file:org.nuxeo.ecm.core.storage.sql.jdbc.dialect.DialectPostgreSQL.java

@Override
@SuppressWarnings("boxing")
public Serializable getFromResultSet(ResultSet rs, int index, Column column) throws SQLException {
    int jdbcType = rs.getMetaData().getColumnType(index);
    if (column.getJdbcType() == Types.ARRAY && jdbcType != Types.ARRAY) {
        jdbcType = column.getJdbcBaseType();
    } else {//from  w  w  w .j  a v  a 2  s .  c o  m
        jdbcType = column.getJdbcType();
    }
    switch (jdbcType) {
    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 getCalendarFromTimestamp(rs.getTimestamp(index));
    case Types.ARRAY:
        Array array = rs.getArray(index);
        if (array == null) {
            return null;
        }
        if (array.getBaseType() == Types.TIMESTAMP) {
            return getCalendarFromTimestamp((Timestamp[]) array.getArray());
        } else {
            return (Serializable) array.getArray();
        }
    case Types.OTHER:
        ColumnType type = column.getType();
        if (type.isId()) {
            return getId(rs, index);
        }
        throw new SQLException("Unhandled type: " + column.getType());
    }
    throw new SQLException(
            "Unhandled JDBC type: " + column.getJdbcType() + " for type " + column.getType().toString());
}

From source file:org.smallmind.persistence.orm.hibernate.LongArrayUserType.java

@Override
public Object nullSafeGet(final ResultSet rs, final String[] names, final SessionImplementor sessionImplementor,
        final Object owner) throws HibernateException, SQLException {

    Array array = rs.getArray(names[0]);
    Long[] javaArray = (Long[]) array.getArray();
    return ArrayUtils.toPrimitive(javaArray);
}

From source file:uk.ac.ox.it.ords.api.database.structure.services.impl.hibernate.StructureServiceImpl.java

protected List<HashMap<String, Object>> getIndexesFromPostgres(String databaseName, String databaseServer,
        String table) throws Exception {
    String query = "SELECT " + "i.relname as indexname, " + "idx.indrelid::regclass as tablename, " + "ARRAY( "
            + "SELECT pg_get_indexdef(idx.indexrelid, k + 1, true) "
            + "FROM generate_subscripts(idx.indkey, 1) as k " + "ORDER BY k " + ") as colnames, "
            + "indisunique as isunique, " + "indisprimary as isprimary " + "FROM " + "pg_index as idx "
            + "JOIN pg_class as i " + "ON i.oid = idx.indexrelid "
            + "WHERE CAST(idx.indrelid::regclass as text) = quote_ident(?)";
    List<HashMap<String, Object>> indexes = new ArrayList<HashMap<String, Object>>();
    HashMap<String, Object> index;
    String type;//w w w  .  j a  v a  2s .  c  o  m
    ArrayList<Object> parameters = new ArrayList<Object>();
    parameters.add(table);
    CachedRowSet rs = this.runJDBCQuery(query, parameters, databaseServer, databaseName);
    // List<Object[]> results = this.runSQLQuery(command, null, null, null);
    while (rs.next()) {
        index = new HashMap<String, Object>();
        index.put("name", rs.getString("indexname"));
        ArrayList<String> columns = new ArrayList<String>();
        Array sqlArray = rs.getArray("colnames");
        Object[] cols = (Object[]) sqlArray.getArray();
        // ResultSet columnSet = sqlArray.getResultSet();
        for (Object column : cols) {
            //
            // PG may store the index columns as quoted identifiers, in which case we need
            // to unquote them to return via the API
            //
            columns.add(unquote(column.toString()));
        }
        index.put("columns", columns);
        if (rs.getBoolean("isprimary")) {
            type = "PRIMARY";
        } else if (rs.getBoolean("isunique")) {
            type = "UNIQUE";
        } else {
            type = "INDEX";
        }
        index.put("type", type);

        indexes.add(index);
    }

    return indexes;
}