Example usage for java.sql ResultSet getArray

List of usage examples for java.sql ResultSet getArray

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. ResultSet
  6. getArray

Introduction

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

Prototype

Array getArray(String columnLabel) throws SQLException;

Source Link

Document

Retrieves the value of the designated column in the current row of this ResultSet object as an Array object in the Java programming language.

Usage

From source file:com.chiorichan.database.DatabaseEngine.java

public static Map<String, Object> convertRow(ResultSet rs) throws SQLException {
    Map<String, Object> result = Maps.newLinkedHashMap();
    ResultSetMetaData rsmd = rs.getMetaData();

    int numColumns = rsmd.getColumnCount();

    for (int i = 1; i < numColumns + 1; i++) {
        String columnName = rsmd.getColumnName(i);

        // Loader.getLogger().info( "Column: " + columnName + " <-> " + rsmd.getColumnTypeName( i ) );

        if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) {
            result.put(columnName, rs.getArray(columnName));
        } else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) {
            result.put(columnName, rs.getInt(columnName));
        } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) {
            result.put(columnName, rs.getInt(columnName));
        } else if (rsmd.getColumnType(i) == java.sql.Types.BIT) // Sometimes tinyints are read as bits
        {//from   w ww .j a  va 2 s  . co  m
            result.put(columnName, rs.getInt(columnName));
        } else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) {
            result.put(columnName, rs.getBoolean(columnName));
        } else if (rsmd.getColumnTypeName(i).contains("BLOB")
                || rsmd.getColumnType(i) == java.sql.Types.BINARY) {
            // BLOG = Max Length 65,535. Recommended that you use a LONGBLOG.
            byte[] bytes = rs.getBytes(columnName);
            result.put(columnName, bytes);
            /*
             * try
             * {
             * result.put( columnName, new String( bytes, "ISO-8859-1" ) );
             * }
             * catch ( UnsupportedEncodingException e )
             * {
             * e.printStackTrace();
             * }
             */
        } else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE) {
            result.put(columnName, rs.getDouble(columnName));
        } else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) {
            result.put(columnName, rs.getFloat(columnName));
        } else if (rsmd.getColumnTypeName(i).equals("INT")) {
            result.put(columnName, rs.getInt(columnName));
        } else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR) {
            result.put(columnName, rs.getNString(columnName));
        } else if (rsmd.getColumnTypeName(i).equals("VARCHAR")) {
            result.put(columnName, rs.getString(columnName));
        } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) {
            result.put(columnName, rs.getInt(columnName));
        } else if (rsmd.getColumnType(i) == java.sql.Types.DATE) {
            result.put(columnName, rs.getDate(columnName));
        } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) {
            result.put(columnName, rs.getTimestamp(columnName));
        } else {
            result.put(columnName, rs.getObject(columnName));
        }
    }

    return result;
}

From source file:org.gofleet.openLS.ddbb.dao.postgis.PostGisHBGeoCodingDAO.java

@Transactional(readOnly = true)
public List<AbstractResponseParametersType> geocoding(final GeocodeRequestType param) {
    HibernateCallback<List<AbstractResponseParametersType>> action = new HibernateCallback<List<AbstractResponseParametersType>>() {
        public List<AbstractResponseParametersType> doInHibernate(Session session)
                throws HibernateException, SQLException {

            List<AddressType> addressList = param.getAddress();
            List<AbstractResponseParametersType> res_ = new LinkedList<AbstractResponseParametersType>();

            for (AddressType addressType : addressList) {

                // TODO change deprecation?
                @SuppressWarnings("deprecation")
                CallableStatement consulta = session.connection()
                        .prepareCall("{call gls_geocoding(?, ?, ?, ?, ?)}");

                String street = GeoUtil.extractStreet(addressType);
                String munsub = GeoUtil.extractMunSub(addressType);
                String mun = GeoUtil.extractMun(addressType);
                String subcountry = GeoUtil.extractSubCountry(addressType);
                String country = GeoUtil.extractCountry(addressType);

                consulta.setString(1, street);
                consulta.setString(2, munsub);
                consulta.setString(3, mun);
                consulta.setString(4, subcountry);
                consulta.setString(5, country);

                LOG.debug(consulta);//from ww w .  j a va  2s. c  o m

                ResultSet o = consulta.executeQuery();
                GeocodeResponseType grt = new GeocodeResponseType();
                while (o.next()) {
                    GeocodeResponseListType geocode = new GeocodeResponseListType();
                    try {
                        PGgeometry g = (PGgeometry) o.getObject("geometry");
                        Jdbc4Array address = (Jdbc4Array) o.getArray("address");

                        GeocodedAddressType addresstype = new GeocodedAddressType();
                        addresstype.setPoint(PostGisUtils.getReferencedPoint(g));
                        addresstype.setAddress(PostGisUtils.getAddress(address));

                        geocode.getGeocodedAddress().add(addresstype);

                        geocode.setNumberOfGeocodedAddresses(BigInteger.valueOf(1l));

                        grt.getGeocodeResponseList().add(geocode);
                    } catch (Throwable t) {
                        LOG.error("Error extracting data from database.", t);
                    }
                    res_.add(grt);
                }
            }
            return res_;
        }

    };

    return hibernateTemplate.executeWithNativeSession(action);
}

From source file:org.gofleet.openLS.ddbb.dao.GeoCodingDAO.java

@Transactional(readOnly = true)
public List<List<AbstractResponseParametersType>> geocoding(final GeocodeRequestType param) {
    HibernateCallback<List<List<AbstractResponseParametersType>>> action = new HibernateCallback<List<List<AbstractResponseParametersType>>>() {
        public List<List<AbstractResponseParametersType>> doInHibernate(Session session)
                throws HibernateException, SQLException {

            List<List<AbstractResponseParametersType>> res = new LinkedList<List<AbstractResponseParametersType>>();
            List<AddressType> addressList = param.getAddress();

            for (AddressType addressType : addressList) {
                List<AbstractResponseParametersType> res_ = new LinkedList<AbstractResponseParametersType>();

                // TODO change deprecation?
                @SuppressWarnings("deprecation")
                CallableStatement consulta = session.connection()
                        .prepareCall("{call gls_geocoding(?, ?, ?, ?, ?)}");

                String street = GeoUtil.extractStreet(addressType);
                String munsub = GeoUtil.extractMunSub(addressType);
                String mun = GeoUtil.extractMun(addressType);
                String subcountry = GeoUtil.extractSubCountry(addressType);
                String country = GeoUtil.extractCountry(addressType);

                consulta.setString(1, street);
                consulta.setString(2, munsub);
                consulta.setString(3, mun);
                consulta.setString(4, subcountry);
                consulta.setString(5, country);

                LOG.debug(consulta);/*w w w.j  av a 2s .  c o m*/

                ResultSet o = consulta.executeQuery();
                GeocodeResponseType grt = new GeocodeResponseType();
                while (o.next()) {
                    GeocodeResponseListType geocode = new GeocodeResponseListType();
                    try {
                        PGgeometry g = (PGgeometry) o.getObject("geometry");
                        Jdbc4Array address = (Jdbc4Array) o.getArray("address");

                        GeocodedAddressType addresstype = new GeocodedAddressType();
                        addresstype.setPoint(GeoUtil.getReferencedPoint(g));
                        addresstype.setAddress(GeoUtil.getAddress(address));

                        geocode.getGeocodedAddress().add(addresstype);

                        geocode.setNumberOfGeocodedAddresses(BigInteger.valueOf(1l));

                        grt.getGeocodeResponseList().add(geocode);
                    } catch (Throwable t) {
                        LOG.error("Error extracting data from database.", t);
                    }
                    res_.add(grt);
                }
                res.add(res_);
            }
            return res;
        }

    };

    return hibernateTemplate.executeWithNativeSession(action);
}

From source file:org.kawanfw.sql.servlet.sql.ResultSetWriter.java

/**
 * Format the column as an java.sqlArray
 * /*from   w  ww.  j a v a2  s .  co m*/
 * @param resultSet
 * @param columnIndex
 * @return
 * @throws SQLException
 * @throws IOException
 */
private String formatArrayColumn(ResultSet resultSet, int columnIndex) throws SQLException, IOException {
    Array array = resultSet.getArray(columnIndex);
    ArrayHttp arrayHttp = new ArrayHttp(array);
    ArrayTransporter arrayTransporter = new ArrayTransporter();
    String base64 = arrayTransporter.toBase64(arrayHttp);
    return base64;
}

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  w  w  .ja  v  a2s  .  c om*/

        // 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.phoenix.end2end.CSVCommonsLoaderIT.java

@Test
public void testCSVCommonsUpsert_WithArray() throws Exception {
    CSVParser parser = null;//from   w w  w.jav a  2  s .  c om
    PhoenixConnection conn = null;
    try {

        // Create table
        String statements = "CREATE TABLE IF NOT EXISTS ARRAY_TABLE "
                + "(ID BIGINT NOT NULL PRIMARY KEY, VALARRAY INTEGER ARRAY);";
        conn = DriverManager.getConnection(getUrl()).unwrap(PhoenixConnection.class);
        PhoenixRuntime.executeStatements(conn, new StringReader(statements), null);

        // Upsert CSV file
        CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn, "ARRAY_TABLE", ImmutableList.<String>of(), true,
                ',', '"', null, "!");
        csvUtil.upsert(new StringReader("ID,VALARRAY\n" + "1,2!3!4\n"));

        // Compare Phoenix ResultSet with CSV file content
        PreparedStatement statement = conn.prepareStatement("SELECT ID, VALARRAY FROM ARRAY_TABLE");
        ResultSet phoenixResultSet = statement.executeQuery();
        assertTrue(phoenixResultSet.next());
        assertEquals(1L, phoenixResultSet.getLong(1));
        assertEquals(PArrayDataType.instantiatePhoenixArray(PInteger.INSTANCE, new Integer[] { 2, 3, 4 }),
                phoenixResultSet.getArray(2));
        assertFalse(phoenixResultSet.next());
    } finally {
        if (parser != null)
            parser.close();
        if (conn != null)
            conn.close();
    }
}

From source file:org.trafodion.rest.util.JdbcT2Util.java

private synchronized JSONArray convertResultSetToJSON(java.sql.ResultSet rs) throws Exception {
    if (LOG.isDebugEnabled())
        LOG.debug("Begin convertResultSetToJSON");

    JSONArray json = new JSONArray();

    try {// www  .ja  v  a2s  .co  m

        java.sql.ResultSetMetaData rsmd = rs.getMetaData();

        while (rs.next()) {
            int numColumns = rsmd.getColumnCount();
            JSONObject obj = new JSONObject();

            for (int i = 1; i < numColumns + 1; i++) {

                String column_name = rsmd.getColumnName(i);

                if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) {
                    obj.put(column_name, rs.getArray(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) {
                    obj.put(column_name, rs.getLong(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) {
                    obj.put(column_name, rs.getBoolean(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.BLOB) {
                    obj.put(column_name, rs.getBlob(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE) {
                    obj.put(column_name, rs.getDouble(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) {
                    obj.put(column_name, rs.getFloat(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.INTEGER) {
                    obj.put(column_name, rs.getInt(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR) {
                    obj.put(column_name, rs.getNString(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.CHAR
                        || rsmd.getColumnType(i) == java.sql.Types.VARCHAR) {
                    //prevent obj.put from removing null key value from JSONObject
                    String s = rs.getString(column_name);
                    if (s == null)
                        obj.put(column_name, new String(""));
                    else
                        obj.put(column_name, rs.getString(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) {
                    obj.put(column_name, rs.getInt(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) {
                    obj.put(column_name, rs.getInt(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.DATE) {
                    obj.put(column_name, rs.getDate(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) {
                    obj.put(column_name, rs.getTimestamp(column_name));
                } else {
                    obj.put(column_name, rs.getObject(column_name));
                }

            } //end foreach 
            json.put(obj);

        } //end while 

    } catch (SQLException e) {
        e.printStackTrace();
        if (LOG.isDebugEnabled())
            LOG.error(e.getMessage());
        throw e;
    } catch (Exception e) {
        e.printStackTrace();
        if (LOG.isDebugEnabled())
            LOG.error(e.getMessage());
        throw e;
    }

    if (LOG.isDebugEnabled())
        LOG.debug("End convertResultSetToJSON");

    return json;
}

From source file:org.sakaiproject.webservices.SakaiReport.java

protected String toJsonString(ResultSet rs) throws SQLException, JSONException {
    ResultSetMetaData rsmd = rs.getMetaData();
    JSONArray array = new JSONArray();
    int numColumns = rsmd.getColumnCount();

    while (rs.next()) {

        JSONObject obj = new JSONObject();
        for (int i = 1; i < numColumns + 1; i++) {

            String column_label = rsmd.getColumnLabel(i);

            LOG.debug("Column Name=" + column_label + ",type=" + rsmd.getColumnType(i));

            switch (rsmd.getColumnType(i)) {
            case Types.ARRAY:
                obj.put(column_label, rs.getArray(i));
                break;
            case Types.BIGINT:
                obj.put(column_label, rs.getInt(i));
                break;
            case Types.BOOLEAN:
                obj.put(column_label, rs.getBoolean(i));
                break;
            case Types.BLOB:
                obj.put(column_label, rs.getBlob(i));
                break;
            case Types.DOUBLE:
                obj.put(column_label, rs.getDouble(i));
                break;
            case Types.FLOAT:
                obj.put(column_label, rs.getFloat(i));
                break;
            case Types.INTEGER:
                obj.put(column_label, rs.getInt(i));
                break;
            case Types.NVARCHAR:
                obj.put(column_label, rs.getNString(i));
                break;
            case Types.VARCHAR:
                obj.put(column_label, rs.getString(i));
                break;
            case Types.TINYINT:
                obj.put(column_label, rs.getInt(i));
                break;
            case Types.SMALLINT:
                obj.put(column_label, rs.getInt(i));
                break;
            case Types.DATE:
                obj.put(column_label, rs.getDate(i));
                break;
            case Types.TIMESTAMP:
                obj.put(column_label, rs.getTimestamp(i));
                break;
            default:
                obj.put(column_label, rs.getObject(i));
                break;
            }//from ww w  .j  a va  2 s.com

        }
        array.put(obj);

    }
    return array.toString();
}

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   w  w w .j  av a2s .  c  o  m
    throw new SQLException("Unhandled JDBC type: " + column.getJdbcType());
}

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

/**
 * {@inheritDoc}/*ww  w  . j  a v  a 2 s. c  o m*/
 */
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();
}