Example usage for java.sql ResultSet getBytes

List of usage examples for java.sql ResultSet getBytes

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

Introduction

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

Prototype

byte[] getBytes(String columnLabel) throws SQLException;

Source Link

Document

Retrieves the value of the designated column in the current row of this ResultSet object as a byte array in the Java programming language.

Usage

From source file:biblivre3.administration.ReportsDAO.java

public AssetHoldingDto getAssetHoldingFullReportData() {
    AssetHoldingDto dto = new AssetHoldingDto();
    Connection con = null;/*ww w  . j  a  v  a  2s  .  c o m*/
    try {
        con = getDataSource().getConnection();
        String sql = " SELECT H.holding_serial, H.asset_holding, R.record FROM cataloging_holdings H INNER JOIN cataloging_biblio R "
                + " ON R.record_serial = H.record_serial WHERE H.database = 0 " + " ORDER BY H.asset_holding ";

        final PreparedStatement pst = con.prepareStatement(sql);
        pst.setFetchSize(100);

        final ResultSet rs = pst.executeQuery();
        List<String[]> dataList = new ArrayList<String[]>();
        while (rs.next()) {
            Record record = MarcUtils.iso2709ToRecord(rs.getBytes("record"));
            String assetHolding = rs.getString("asset_holding");
            String serial = rs.getString("holding_serial");
            String[] data = new String[7];
            data[0] = serial;
            data[1] = assetHolding;
            data[2] = Indexer.listOneTitle(record);
            data[3] = Indexer.listPrimaryAuthor(record);
            data[4] = Indexer.listFormattedLocation(record);
            data[5] = Indexer.listEdition(record);
            data[6] = Indexer.listYearOfPublication(record);
            dataList.add(data);
        }
        dto.setData(dataList);
    } catch (Exception e) {
        log.error(e.getMessage(), e);
        throw new ExceptionUser("ERROR_REPORT_DAO_EXCEPTION");
    } finally {
        closeConnection(con);
    }
    return dto;
}

From source file:uk.ac.ebi.orchem.search.SimilaritySearch.java

/**
 * Similarity score calculation between one database compound and a user's query compound.
 * @param userQuery  query structure in SMILES or MOL
 * @param queryType  MOL or SMILES// w ww .jav  a2  s. c o m
 * @param compoundId ID of database compound to calculate similarity with
 * @return tanimoto similarity score
 * @throws Exception
 */
public static float singleCompoundSimilarity(Clob userQuery, String queryType, String compoundId)
        throws Exception {

    OracleConnection conn = null;
    PreparedStatement pstmtFp = null;
    ResultSet resFp = null;
    float tanimotoCoeff = 0;

    try {
        //User query
        int clobLen = new Long(userQuery.length()).intValue();
        String query = (userQuery.getSubString(1, clobLen));
        IAtomContainer molecule = null;
        if (queryType.equals(Utils.QUERY_TYPE_MOL)) {
            molecule = MoleculeCreator.getMoleculeFromMolfile(query);
        } else if (queryType.equals(Utils.QUERY_TYPE_SMILES)) {
            SmilesParser sp = new SmilesParser(DefaultChemObjectBuilder.getInstance());
            molecule = sp.parseSmiles(query);
        } else
            throw new RuntimeException("Query type not recognized");
        BitSet queryFp = FingerPrinterAgent.FP.getExtendedFingerPrinter().getFingerprint(molecule);
        float queryBitCount = queryFp.cardinality();
        byte[] queryBytes = Utils.toByteArray(queryFp, extFpSize);

        //Database comound
        conn = (OracleConnection) new OracleDriver().defaultConnection();
        String compoundQuery = "select bit_count, fp from orchem_fingprint_simsearch s where id=?";
        pstmtFp = conn.prepareStatement(compoundQuery);
        pstmtFp.setFetchSize(1);
        pstmtFp.setString(1, compoundId);
        resFp = pstmtFp.executeQuery();

        if (resFp.next()) {
            byte[] dbByteArray = resFp.getBytes("fp");
            float compoundBitCount = resFp.getFloat("bit_count");
            tanimotoCoeff = calcTanimoto(queryBytes, queryBytes.length, dbByteArray, queryBitCount,
                    compoundBitCount);
        } else
            throw new RuntimeException("Compound " + compoundId + " not found in similarity table");

    } catch (Exception e) {
        e.printStackTrace();
        throw e;
    } finally {
        if (resFp != null)
            resFp.close();
        if (pstmtFp != null)
            pstmtFp.close();
        if (conn != null)
            conn.close();
    }
    return tanimotoCoeff;
}

From source file:biblivre3.administration.ReportsDAO.java

public AssetHoldingByDateDto getAssetHoldingByDateReportData(String initialDate, String finalDate) {
    AssetHoldingByDateDto dto = new AssetHoldingByDateDto();
    Connection con = null;/*  w ww  . jav  a 2s.  c  om*/
    try {
        con = getDataSource().getConnection();
        String sql = " SELECT H.asset_holding, to_char(H.created, 'DD/MM/YYYY'), R.record, H.record "
                + " FROM cataloging_holdings H INNER JOIN cataloging_biblio R "
                + " ON R.record_serial = H.record_serial WHERE H.database = 0 "
                + " AND H.created >= to_date(?, 'DD-MM-YYYY') " + " AND H.created <= to_date(?, 'DD-MM-YYYY') "
                + " ORDER BY H.created, H.asset_holding ";

        final PreparedStatement pst = con.prepareStatement(sql);
        pst.setString(1, initialDate);
        pst.setString(2, finalDate);
        pst.setFetchSize(100);

        final ResultSet rs = pst.executeQuery();
        List<String[]> dataList = new ArrayList<String[]>();
        while (rs.next()) {
            Record record = MarcUtils.iso2709ToRecord(rs.getBytes(3));
            Record holding = MarcUtils.iso2709ToRecord(rs.getBytes(4));
            String assetHolding = rs.getString("asset_holding");
            String creationDate = rs.getString(2);
            String[] data = new String[6];
            data[0] = creationDate;
            data[1] = assetHolding;
            data[2] = Indexer.listOneTitle(record);
            data[3] = Indexer.listPrimaryAuthor(record);
            data[4] = Indexer.listYearOfPublication(record);
            data[5] = Indexer.listSourceAcquisitionDate(holding);
            dataList.add(data);
        }
        dto.setData(dataList);
    } catch (Exception e) {
        log.error(e.getMessage(), e);
        throw new ExceptionUser("ERROR_REPORT_DAO_EXCEPTION");
    } finally {
        closeConnection(con);
    }
    return dto;
}

From source file:biblivre3.administration.ReportsDAO.java

public SummaryReportDto getSummaryReportData(Database database) {
    SummaryReportDto dto = new SummaryReportDto();
    Connection con = null;//from  w ww.j av  a2s . c  om
    try {
        con = getDataSource().getConnection();
        String sql = "SELECT record, record_serial FROM cataloging_biblio WHERE database = ?;";
        String countSql = "SELECT count(holding_serial) FROM cataloging_holdings WHERE record_serial = ?;";

        final PreparedStatement pst = con.prepareStatement(sql);
        pst.setFetchSize(100);

        final PreparedStatement count = con.prepareStatement(countSql);

        pst.setInt(1, database.ordinal());

        final ResultSet rs = pst.executeQuery();
        List<String[]> dataList = new ArrayList<String[]>();
        while (rs.next()) {
            Record record = MarcUtils.iso2709ToRecord(rs.getBytes("record"));
            String[] data = new String[8];
            data[0] = Indexer.listOneTitle(record);
            data[1] = Indexer.listAuthors(record);
            data[2] = Indexer.listIsbn(record);
            data[3] = Indexer.listEditor(record);// editora(50)
            data[4] = Indexer.listYearOfPublication(record);// ano(20)
            data[5] = Indexer.listEdition(record);
            data[6] = Indexer.listLocation(record)[0];

            count.setInt(1, rs.getInt("record_serial"));
            ResultSet countRs = count.executeQuery();
            countRs.next();
            data[7] = countRs.getString(1);
            dataList.add(data);
        }
        dto.setData(dataList);
    } catch (Exception e) {
        log.error(e.getMessage(), e);
        throw new ExceptionUser("ERROR_REPORT_DAO_EXCEPTION");
    } finally {
        closeConnection(con);
    }
    return dto;
}

From source file:org.wso2.carbon.certificate.mgt.core.dao.impl.AbstractCertificateDAOImpl.java

@Override
public List<CertificateResponse> getAllCertificates() throws CertificateManagementDAOException {
    PreparedStatement stmt = null;
    ResultSet resultSet = null;
    CertificateResponse certificateResponse;
    List<CertificateResponse> certificates = new ArrayList<>();
    int tenantId = PrivilegedCarbonContext.getThreadLocalCarbonContext().getTenantId();
    try {/*from w w  w  .j av  a2  s. com*/
        Connection conn = this.getConnection();
        String sql = "SELECT CERTIFICATE, SERIAL_NUMBER, TENANT_ID, USERNAME"
                + " FROM DM_DEVICE_CERTIFICATE WHERE TENANT_ID = ? ORDER BY ID DESC";
        stmt = conn.prepareStatement(sql);
        stmt.setInt(1, tenantId);
        resultSet = stmt.executeQuery();

        while (resultSet.next()) {
            certificateResponse = new CertificateResponse();
            byte[] certificateBytes = resultSet.getBytes("CERTIFICATE");
            certificateResponse.setSerialNumber(resultSet.getString("SERIAL_NUMBER"));
            certificateResponse.setTenantId(resultSet.getInt("TENANT_ID"));
            certificateResponse.setUsername(resultSet.getString("USERNAME"));
            CertificateGenerator.extractCertificateDetails(certificateBytes, certificateResponse);
            certificates.add(certificateResponse);
        }
    } catch (SQLException e) {
        String errorMsg = "SQL error occurred while retrieving the certificates.";
        log.error(errorMsg, e);
        throw new CertificateManagementDAOException(errorMsg, e);
    } finally {
        CertificateManagementDAOUtil.cleanupResources(stmt, resultSet);
    }
    return certificates;
}

From source file:biblivre3.administration.ReportsDAO.java

public TreeMap<String, Set<Integer>> searchAuthors(String authorName) {
    TreeMap<String, Set<Integer>> results = new TreeMap<String, Set<Integer>>();

    String[] terms = authorName.split(" ");
    Connection con = null;/*  w  ww  .  j a  v a2  s. c  o  m*/
    try {
        con = getDataSource().getConnection();
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT DISTINCT B.record_serial, B.record FROM cataloging_biblio B ");
        sql.append("INNER JOIN idx_author I ON I.record_serial = B.record_serial WHERE B.database = 0 ");

        for (int i = 0; i < terms.length; i++) {
            sql.append(
                    "AND B.record_serial in (SELECT record_serial FROM idx_author WHERE index_word >= ? and index_word < ?) ");
        }

        PreparedStatement st = con.prepareStatement(sql.toString());
        int index = 1;
        for (int i = 0; i < terms.length; i++) {
            st.setString(index++, terms[i]);
            st.setString(index++, TextUtils.incrementLastChar(terms[i]));
        }

        ResultSet rs = st.executeQuery();
        if (rs != null) {
            while (rs.next()) {
                Integer id = rs.getInt("record_serial");
                String iso2709 = new String(rs.getBytes("record"), "UTF-8");
                Record record = MarcUtils.iso2709ToRecord(iso2709);
                String name = Indexer.listPrimaryAuthor(record);
                if (results.containsKey(name)) {
                    Set<Integer> ids = results.get(name);
                    ids.add(id);
                } else {
                    Set<Integer> ids = new HashSet<Integer>();
                    ids.add(id);
                    results.put(name, ids);
                }
            }
        }
    } catch (Exception e) {
        log.error(e.getMessage(), e);
        throw new ExceptionUser("ERROR_REPORT_DAO_EXCEPTION");
    } finally {
        closeConnection(con);
    }
    return results;
}

From source file:org.snaker.engine.access.jdbc.JdbcHelper.java

/**
 * ?ResultSet?index?requiredType?//from   w  w  w  .  j av  a2s.co m
 * @param rs
 * @param index
 * @param requiredType
 * @return
 * @throws SQLException
 */
public static Object getResultSetValue(ResultSet rs, int index, Class<?> requiredType) throws SQLException {
    if (requiredType == null) {
        return getResultSetValue(rs, index);
    }

    Object value = null;
    boolean wasNullCheck = false;

    if (String.class.equals(requiredType)) {
        value = rs.getString(index);
    } else if (boolean.class.equals(requiredType) || Boolean.class.equals(requiredType)) {
        value = rs.getBoolean(index);
        wasNullCheck = true;
    } else if (byte.class.equals(requiredType) || Byte.class.equals(requiredType)) {
        value = rs.getByte(index);
        wasNullCheck = true;
    } else if (short.class.equals(requiredType) || Short.class.equals(requiredType)) {
        value = rs.getShort(index);
        wasNullCheck = true;
    } else if (int.class.equals(requiredType) || Integer.class.equals(requiredType)) {
        value = rs.getInt(index);
        wasNullCheck = true;
    } else if (long.class.equals(requiredType) || Long.class.equals(requiredType)) {
        value = rs.getLong(index);
        wasNullCheck = true;
    } else if (float.class.equals(requiredType) || Float.class.equals(requiredType)) {
        value = rs.getFloat(index);
        wasNullCheck = true;
    } else if (double.class.equals(requiredType) || Double.class.equals(requiredType)
            || Number.class.equals(requiredType)) {
        value = rs.getDouble(index);
        wasNullCheck = true;
    } else if (byte[].class.equals(requiredType)) {
        value = rs.getBytes(index);
    } else if (java.sql.Date.class.equals(requiredType)) {
        value = rs.getDate(index);
    } else if (java.sql.Time.class.equals(requiredType)) {
        value = rs.getTime(index);
    } else if (java.sql.Timestamp.class.equals(requiredType) || java.util.Date.class.equals(requiredType)) {
        value = rs.getTimestamp(index);
    } else if (BigDecimal.class.equals(requiredType)) {
        value = rs.getBigDecimal(index);
    } else if (Blob.class.equals(requiredType)) {
        value = rs.getBlob(index);
    } else if (Clob.class.equals(requiredType)) {
        value = rs.getClob(index);
    } else {
        value = getResultSetValue(rs, index);
    }

    if (wasNullCheck && value != null && rs.wasNull()) {
        value = null;
    }
    return value;
}

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

/**
 * Test that the values were correclty inserted
 * /*from  ww  w.j av  a 2  s . c  o  m*/
 * @param connection
 */
@SuppressWarnings("deprecation")
public void selectPrepStatementTest(Connection connection, int customerId, int orderId) throws Exception {
    int customer_id;
    int item_id;
    String description;
    BigDecimal cost_price;
    BigDecimal cost_price_scale;
    Date date_placed;
    Timestamp date_shipped;
    byte[] jpeg_image = null;
    boolean is_delivered;
    int quantity;

    String sql = "select * from orderlog where  customer_id = ? and item_id = ? ";

    PreparedStatement prepStatement = connection.prepareStatement(sql);

    int i = 1;
    prepStatement.setInt(i++, customerId);
    prepStatement.setInt(i++, orderId);

    ResultSet rs = prepStatement.executeQuery();

    MessageDisplayer.display("");

    SqlUtil sqlUtil = new SqlUtil(connection);

    while (rs.next()) {

        customer_id = rs.getInt("customer_id");
        item_id = rs.getInt("item_id");
        description = rs.getString("description");
        cost_price = rs.getBigDecimal("cost_price");
        cost_price_scale = rs.getBigDecimal("cost_price", 5);
        date_placed = rs.getDate("date_placed");
        date_shipped = rs.getTimestamp("date_shipped");
        jpeg_image = rs.getBytes("jpeg_image");

        if (sqlUtil.isIngres()) {
            is_delivered = (rs.getInt("is_delivered") == 1) ? true : false;
        } else {
            is_delivered = rs.getBoolean("is_delivered");
        }

        quantity = rs.getInt("quantity");

        MessageDisplayer.display("customer_id     : " + customer_id);
        MessageDisplayer.display("item_id         : " + item_id);
        MessageDisplayer.display("description     : " + description);
        MessageDisplayer.display("cost_price      : " + cost_price);
        MessageDisplayer.display("cost_price_scale: " + cost_price_scale);
        MessageDisplayer.display("date_placed     : " + date_placed);
        MessageDisplayer.display("date_shipped    : " + date_shipped);
        MessageDisplayer.display("jpeg_image      : " + jpeg_image);
        MessageDisplayer.display("is_delivered    : " + is_delivered);
        MessageDisplayer.display("quantity        : " + quantity);

        // Assert done on first 18 chars (ex: 2011-11-02 16:26:14), because
        // MySql Truncs
        // the remaining milliseconds
        Assert.assertEquals(dateShippedUpdated.toString().substring(0, 19),
                date_shipped.toString().substring(0, 19));

        if (new SqlUtil(connection).isSQLAnywhere()) {
            // Because SQLK Anywhere stores 5000.0000 instead of 5000 in db
            Assert.assertEquals(new BigDecimal(customer_id * increaseFactor).toString(),
                    cost_price.toString().substring(0, 4));
        } else {
            Assert.assertEquals(new BigDecimal(customer_id * increaseFactor).toString(), cost_price.toString());
        }

        Assert.assertEquals(true, is_delivered);

        Assert.assertEquals(customer_id * increaseFactor * 2, quantity);

        i = 1;
        customer_id = rs.getInt(i++);
        item_id = rs.getInt(i++);
        description = rs.getString(i++);
        int iForCostPrice = i;
        cost_price = rs.getBigDecimal(i++);
        cost_price_scale = rs.getBigDecimal(iForCostPrice, 5);
        date_placed = rs.getDate(i++);
        date_shipped = rs.getTimestamp(i++);

        // NO! do not read twice the same file ==> has been delete at first
        // read
        // jpeg_image = rs.getBytes(i++);
        i++;

        is_delivered = rs.getBoolean(i++);
        quantity = rs.getInt(i++);

        MessageDisplayer.display("");
        MessageDisplayer.display("customer_id     : " + customer_id);
        MessageDisplayer.display("item_id         : " + item_id);
        MessageDisplayer.display("description     : " + description);
        MessageDisplayer.display("cost_price      : " + cost_price);
        MessageDisplayer.display("cost_price_scale: " + cost_price_scale);
        MessageDisplayer.display("date_placed     : " + date_placed);
        MessageDisplayer.display("date_shipped    : " + date_shipped);
        MessageDisplayer.display("jpeg_image      : " + jpeg_image);
        MessageDisplayer.display("is_delivered    : " + is_delivered);
        MessageDisplayer.display("quantity        : " + quantity);

        // Assert done on first 18 chars (ex: 2011-11-02 16:26:14), because
        // MySql Truncs
        // the remaining milliseconds
        Assert.assertEquals(date_shipped.toString().substring(0, 18),
                dateShippedUpdated.toString().substring(0, 18));

        if (new SqlUtil(connection).isSQLAnywhere()) {
            // Because SQLK Anywhere stores 5000.0000 instead of 5000 in db
            Assert.assertEquals(new BigDecimal(customer_id * increaseFactor).toString(),
                    cost_price.toString().substring(0, 4));
        } else {
            Assert.assertEquals(new BigDecimal(customer_id * increaseFactor).toString(), cost_price.toString());
        }

        Assert.assertEquals(true, is_delivered);
        Assert.assertEquals(new Integer(customer_id * increaseFactor * 2), new Integer(quantity));
    }

    prepStatement.close();
    rs.close();

    MessageDisplayer.display("Select done!");

}

From source file:cz.lbenda.dataman.db.RowDesc.java

/** Load initial column value from rs */
public void loadInitialColumnValue(ColumnDesc columnDesc, ResultSet rs) throws SQLException {
    Object val;
    if (columnDesc.getDataType() == ColumnType.BIT) {
        val = rs.getBoolean(columnDesc.getPosition());
        if (Boolean.TRUE.equals(val)) {
            val = (byte) 1;
        } else if (Boolean.FALSE.equals(val)) {
            val = (byte) 0;
        }/*from   ww w . ja  va 2s.c  om*/
    } else if (columnDesc.getDataType() == ColumnType.BIT_ARRAY) {
        val = rs.getBytes(columnDesc.getPosition());
    } else {
        val = rs.getObject(columnDesc.getPosition());
    }
    setInitialColumnValue(columnDesc, val);
}

From source file:it.anyplace.sync.repository.repo.SqlRepository.java

@Override
public byte[] popTempData(String key) {
    checkNotNull(emptyToNull(key));/*from  w  w  w. ja  va 2 s  . com*/
    try (Connection connection = getConnection()) {
        byte[] data;
        try (PreparedStatement statement = connection
                .prepareStatement("SELECT record_data FROM temporary_data WHERE record_key = ?")) {
            statement.setString(1, key);
            ResultSet resultSet = statement.executeQuery();
            checkArgument(resultSet.first());
            data = resultSet.getBytes(1);
        }
        try (PreparedStatement statement = connection
                .prepareStatement("DELETE FROM temporary_data WHERE record_key = ?")) {
            statement.setString(1, key);
            int count = statement.executeUpdate();
            checkArgument(count == 1);
        }
        checkNotNull(data);
        return data;
    } catch (SQLException ex) {
        throw new RuntimeException(ex);
    }
}