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:org.openiot.gsn.wrappers.TetraedreFluoWrapper.java

public void run() {
    DataEnumerator data;//from  www. j  a v  a 2 s  .  c o m

    try {
        Thread.sleep(2000);
    } catch (InterruptedException e) {
        logger.error(e.getMessage(), e);
    }

    Connection conn = null;
    ResultSet resultSet = null;

    while (isActive()) {
        try {
            conn = sm.getConnection();
            StringBuilder query = new StringBuilder("select * from ").append(table_name)
                    .append(" where physical_input=").append(physical_input).append(" AND timestamp*1000 > "
                            + latest_timed + "  order by timestamp limit 0," + buffer_size);

            resultSet = sm.executeQueryWithResultSet(query, conn);

            //logger.debug(query);

            while (resultSet.next()) {
                Serializable[] output = new Serializable[this.getOutputFormat().length];

                //long pk = resultSet.getLong(1);
                long timed = resultSet.getLong(3) * 1000;

                //logger.warn("pk => "+ pk);
                //logger.warn("timed => "+ timed);

                for (int i = 0; i < dataFieldsLength; i++) {

                    switch (dataFieldTypes[i]) {
                    case DataTypes.VARCHAR:
                    case DataTypes.CHAR:
                        output[i] = resultSet.getString(i + 1);
                        break;
                    case DataTypes.INTEGER:
                        output[i] = resultSet.getInt(i + 1);
                        break;
                    case DataTypes.TINYINT:
                        output[i] = resultSet.getByte(i + 1);
                        break;
                    case DataTypes.SMALLINT:
                        output[i] = resultSet.getShort(i + 1);
                        break;
                    case DataTypes.DOUBLE:
                        output[i] = resultSet.getDouble(i + 1);
                        break;
                    case DataTypes.BIGINT:
                        output[i] = resultSet.getLong(i + 1);
                        break;
                    case DataTypes.BINARY:
                        output[i] = resultSet.getBytes(i + 1);
                        break;
                    }
                    //logger.warn(i+" (type: "+dataFieldTypes[i]+" ) => "+output[i]);
                }

                StreamElement se = new StreamElement(dataFieldNames, dataFieldTypes, output, timed);
                latest_timed = se.getTimeStamp();

                //logger.warn(" Latest => " + latest_timed);

                this.postStreamElement(se);

                updateCheckPointFile(latest_timed);

                //logger.warn(se);
            }

        } catch (IOException e) {
            logger.error(e.getMessage(), e);
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            sm.close(resultSet);
            sm.close(conn);
        }

        try {
            Thread.sleep(rate);
        } catch (InterruptedException e) {
            logger.error(e.getMessage(), e);
        }
    }
}

From source file:com.runwaysdk.dataaccess.database.general.MySQL.java

/**
 * Truncates a blob by the specified length.
 * //from  www  . j  av a2 s. c om
 * @param table
 * @param columnName
 * @param id
 * @param length
 */
public void truncateBlob(String table, String columnName, String id, long length, Connection conn) {
    Statement statement = null;
    ResultSet resultSet = null;
    try {
        // get the blob
        statement = conn.createStatement();
        String select = "SELECT " + columnName + " FROM " + table + " WHERE " + EntityDAOIF.ID_COLUMN + " = '"
                + id + "'";
        String update = "UPDATE " + table + " SET " + columnName + " = " + "? WHERE " + EntityDAOIF.ID_COLUMN
                + " = '" + id + "'";
        resultSet = statement.executeQuery(select);
        resultSet.next();
        byte[] resultBytes = resultSet.getBytes(columnName);

        // truncate the bytes
        byte[] temp = new byte[(int) length];
        for (int i = 0; i < length; i++) {
            temp[i] = resultBytes[i];
        }

        // save the truncated blob
        PreparedStatement prepared = conn.prepareStatement(update);
        prepared.setBytes(1, temp);
        prepared.executeUpdate();
    } catch (SQLException e) {
        this.throwDatabaseException(e);
    } finally {
        try {
            if (resultSet != null)
                resultSet.close();
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
            this.throwDatabaseException(e);
        }
    }
}

From source file:org.apache.jackrabbit.core.persistence.bundle.BundleDbPersistenceManager.java

/**
 * {@inheritDoc}//  w w  w . jav a  2s  .  c  o  m
 */
public void checkConsistency(String[] uuids, boolean recursive, boolean fix) {
    log.info("{}: checking workspace consistency...", name);

    int count = 0;
    int total = 0;
    Collection modifications = new ArrayList();

    if (uuids == null) {
        // get all node bundles in the database with a single sql statement,
        // which is (probably) faster than loading each bundle and traversing the tree
        ResultSet rs = null;
        DataInputStream din = null;
        try {
            String sql;
            if (getStorageModel() == SM_BINARY_KEYS) {
                sql = "select NODE_ID, BUNDLE_DATA from " + schemaObjectPrefix + "BUNDLE";
            } else {
                sql = "select NODE_ID_HI, NODE_ID_LO, BUNDLE_DATA from " + schemaObjectPrefix + "BUNDLE";
            }
            Statement stmt = connectionManager.executeStmt(sql, new Object[0]);
            rs = stmt.getResultSet();

            // iterate over all nodebundles in the db
            while (rs.next()) {
                NodeId id;
                Blob blob;
                if (getStorageModel() == SM_BINARY_KEYS) {
                    id = new NodeId(new UUID(rs.getBytes(1)));
                    blob = rs.getBlob(2);
                } else {
                    id = new NodeId(new UUID(rs.getLong(1), rs.getLong(2)));
                    blob = rs.getBlob(3);
                }
                din = new DataInputStream(blob.getBinaryStream());
                try {
                    // parse and check bundle
                    // check bundle will log any problems itself
                    if (binding.checkBundle(din)) {
                        // reset stream for readBundle()
                        din = new DataInputStream(blob.getBinaryStream());
                        NodePropBundle bundle = binding.readBundle(din, id);
                        checkBundleConsistency(id, bundle, fix, modifications);
                    } else {
                        log.error("invalid bundle '" + id + "', see previous BundleBinding error log entry");
                    }
                } catch (Exception e) {
                    log.error("Error in bundle " + id + ": " + e);
                }
                count++;
                if (count % 1000 == 0) {
                    log.info(name + ": checked " + count + " bundles...");
                }
            }
        } catch (Exception e) {
            log.error("Error loading bundle", e);
        } finally {
            IOUtils.closeQuietly(din);
            closeResultSet(rs);
            total = count;
        }
    } else {
        // check only given uuids, handle recursive flag

        // 1) convert uuid array to modifiable list
        // 2) for each uuid do
        //     a) load node bundle
        //     b) check bundle, store any bundle-to-be-modified in collection
        //     c) if recursive, add child uuids to list of uuids

        List uuidList = new ArrayList(uuids.length);
        // convert uuid string array to list of UUID objects
        for (int i = 0; i < uuids.length; i++) {
            try {
                uuidList.add(new UUID(uuids[i]));
            } catch (IllegalArgumentException e) {
                log.error("Invalid uuid for consistency check, skipping: '" + uuids[i] + "': " + e);
            }
        }

        // iterate over UUIDs (including ones that are newly added inside the loop!)
        for (int i = 0; i < uuidList.size(); i++) {
            final UUID uuid = (UUID) uuidList.get(i);
            try {
                // load the node from the database
                NodeId id = new NodeId(uuid);
                NodePropBundle bundle = loadBundle(id, true);

                if (bundle == null) {
                    log.error("No bundle found for uuid '" + uuid + "'");
                    continue;
                }

                checkBundleConsistency(id, bundle, fix, modifications);

                if (recursive) {
                    Iterator iter = bundle.getChildNodeEntries().iterator();
                    while (iter.hasNext()) {
                        NodePropBundle.ChildNodeEntry entry = (NodePropBundle.ChildNodeEntry) iter.next();
                        uuidList.add(entry.getId().getUUID());
                    }
                }

                count++;
                if (count % 1000 == 0) {
                    log.info(name + ": checked " + count + "/" + uuidList.size() + " bundles...");
                }
            } catch (ItemStateException e) {
                // problem already logged (loadBundle called with logDetailedErrors=true)
            }
        }

        total = uuidList.size();
    }

    // repair collected broken bundles
    if (consistencyFix && !modifications.isEmpty()) {
        log.info(name + ": Fixing " + modifications.size() + " inconsistent bundle(s)...");
        Iterator iterator = modifications.iterator();
        while (iterator.hasNext()) {
            NodePropBundle bundle = (NodePropBundle) iterator.next();
            try {
                log.info(name + ": Fixing bundle '" + bundle.getId() + "'");
                bundle.markOld(); // use UPDATE instead of INSERT
                storeBundle(bundle);
            } catch (ItemStateException e) {
                log.error(name + ": Error storing fixed bundle: " + e);
            }
        }
    }

    log.info(name + ": checked " + count + "/" + total + " bundles.");
}

From source file:br.gov.jfrj.siga.gc.gsa.GcInformacaoAdaptor.java

/** Gives the bytes of a document referenced with id. */
public void getDocContent(Request req, Response resp) throws IOException {
    DocId id = req.getDocId();/*from   w ww  .  ja v a  2s .  c  om*/
    log.fine("obtendo id = " + id);
    long primaryKey;
    try {
        primaryKey = Long.parseLong(id.getUniqueId());
    } catch (NumberFormatException nfe) {
        resp.respondNotFound();
        return;
    }

    Connection conn = null;
    PreparedStatement stmt = null;
    String query = "select ACRONIMO_ORGAO_USU, ANO, NUMERO, NOME_TIPO_INFORMACAO, TITULO, NOME_ACESSO, HIS_DT_INI, CONTEUDO_TIPO, CONTEUDO, "
            + "(select nome_pessoa from corporativo.dp_pessoa pes where pes.id_pessoa = inf.id_pessoa_titular) SUBSCRITOR, "
            + "(select sigla_lotacao from corporativo.dp_lotacao lot where lot.id_lotacao = inf.id_lotacao_titular) SUBSCRITOR_LOTACAO, "
            + "(select nome_pessoa from corporativo.dp_pessoa pes, corporativo.cp_identidade idn where idn.id_pessoa = pes.id_pessoa and idn.id_identidade = inf.his_idc_ini) CADASTRANTE, "
            + "(select sigla_lotacao from corporativo.dp_lotacao lot, corporativo.dp_pessoa pes, corporativo.cp_identidade idn where lot.id_lotacao = pes.id_lotacao and idn.id_pessoa = pes.id_pessoa and idn.id_identidade = inf.his_idc_ini) CADASTRANTE_LOTACAO "
            + "from sigagc.gc_informacao inf, sigagc.gc_arquivo arq, corporativo.cp_orgao_usuario ou, sigagc.gc_tipo_informacao tp, sigagc.gc_acesso ac  "
            + "where inf.id_arquivo = arq.id_conteudo and inf.id_orgao_usuario = ou.id_orgao_usu and inf.id_tipo_informacao = tp.id_tipo_informacao and inf.id_acesso = ac.id_acesso and numero is not null and ac.id_acesso = 1 "
            + "and inf.id_informacao = ?";
    try {
        conn = getConnection();
        stmt = conn.prepareStatement(query);
        stmt.setLong(1, primaryKey);
        ResultSet rs = stmt.executeQuery();
        if (!rs.next()) {
            resp.respondNotFound();
            return;
        }

        // Add Metadata
        //
        addMetadata(resp, "orgao", rs.getString("ACRONIMO_ORGAO_USU"));
        String codigo = rs.getString("ACRONIMO_ORGAO_USU") + "-GC-" + rs.getInt("ANO") + "/"
                + Long.toString(rs.getLong("NUMERO") + 100000).substring(1);
        addMetadata(resp, "codigo", codigo);
        addMetadata(resp, "origem", "Conhecimento");
        addMetadata(resp, "especie", rs.getString("NOME_TIPO_INFORMACAO"));
        addMetadata(resp, "descricao", rs.getString("TITULO"));
        addMetadata(resp, "acesso", rs.getString("NOME_ACESSO"));
        addMetadata(resp, "data", getDtYYYYMMDD(rs.getDate("HIS_DT_INI")));
        addMetadata(resp, "subscritor_lotacao", rs.getString("SUBSCRITOR_LOTACAO"));
        addMetadata(resp, "subscritor", rs.getString("SUBSCRITOR"));
        addMetadata(resp, "cadastrante_lotacao", rs.getString("CADASTRANTE_LOTACAO"));
        addMetadata(resp, "cadastrante", rs.getString("CADASTRANTE"));

        // Add Acl
        //
        // List<GroupPrincipal> groups = new ArrayList<>();
        // groups.add(new GroupPrincipal(s));
        // Acl acl = new Acl.Builder().setPermitGroups(groups)
        // .setEverythingCaseInsensitive().build();
        // resp.setAcl(acl);

        // Add Atributos essenciais
        //
        resp.setCrawlOnce(false);
        resp.setLastModified(rs.getDate("HIS_DT_INI"));
        try {
            String numero = Long.toString(rs.getLong("NUMERO") + 100000).substring(1);
            resp.setDisplayUrl(
                    new URI(permalink + rs.getString("ACRONIMO_ORGAO_USU") + "GC" + rs.getInt("ANO") + numero));
        } catch (URISyntaxException e) {
            throw new RuntimeException(e);
        }

        // Add Conteudo
        //
        if ("text/html".equals(rs.getString("CONTEUDO_TIPO"))) {
            String html = new String(rs.getBytes("CONTEUDO"), "UTF-8");
            if (html != null) {
                resp.setContentType("text/html");
                resp.getOutputStream().write(html.getBytes());
                return;
            }
        } else {
            resp.respondNotFound();
            return;
        }
    } catch (Exception ex) {
        throw new RuntimeException(ex);
    } finally {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

From source file:gsn.wrappers.JDBCWrapper.java

public void run() {
    DataEnumerator data;// w w w. ja v  a 2  s. c  o  m

    try {
        Thread.sleep(2000);
    } catch (InterruptedException e) {
        logger.error(e.getMessage(), e);
    }

    Connection conn = null;
    ResultSet resultSet = null;

    while (isActive()) {
        try {
            conn = sm.getConnection();
            StringBuilder query = new StringBuilder("select * from ").append(table_name)
                    .append(" where timed > " + latest_timed + " limit 0," + buffer_size);

            resultSet = sm.executeQueryWithResultSet(query, conn);

            //logger.debug(query);

            while (resultSet.next()) {
                Serializable[] output = new Serializable[this.getOutputFormat().length];

                long pk = resultSet.getLong(1);
                long timed = resultSet.getLong(2);

                //logger.warn("pk => "+ pk);
                //logger.warn("timed => "+ timed);

                for (int i = 0; i < dataFieldsLength; i++) {

                    switch (dataFieldTypes[i]) {
                    case DataTypes.VARCHAR:
                    case DataTypes.CHAR:
                        output[i] = resultSet.getString(i + 3);
                        break;
                    case DataTypes.INTEGER:
                        output[i] = resultSet.getInt(i + 3);
                        break;
                    case DataTypes.TINYINT:
                        output[i] = resultSet.getByte(i + 3);
                        break;
                    case DataTypes.SMALLINT:
                        output[i] = resultSet.getShort(i + 3);
                        break;
                    case DataTypes.DOUBLE:
                        output[i] = resultSet.getDouble(i + 3);
                        break;
                    case DataTypes.FLOAT:
                        output[i] = resultSet.getFloat(i + 3);
                        break;
                    case DataTypes.BIGINT:
                        output[i] = resultSet.getLong(i + 3);
                        break;
                    case DataTypes.BINARY:
                        output[i] = resultSet.getBytes(i + 3);
                        break;
                    }
                    //logger.warn(i+" (type: "+dataFieldTypes[i]+" ) => "+output[i]);
                }

                StreamElement se = new StreamElement(dataFieldNames, dataFieldTypes, output, timed);
                latest_timed = se.getTimeStamp();

                //logger.warn(" Latest => " + latest_timed);

                this.postStreamElement(se);

                updateCheckPointFile(latest_timed);

                //logger.warn(se);
            }

        } catch (java.io.IOException e) {
            logger.error(e.getMessage(), e);
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            sm.close(resultSet);
            sm.close(conn);
        }

        try {
            Thread.sleep(rate);
        } catch (InterruptedException e) {
            logger.error(e.getMessage(), e);
        }
    }
}

From source file:gsn.wrappers.TetraedreNMCWrapper.java

public void run() {
    DataEnumerator data;/*from ww w  .jav a  2  s .  c  om*/

    try {
        Thread.sleep(2000);
    } catch (InterruptedException e) {
        logger.error(e.getMessage(), e);
    }

    Connection conn = null;
    ResultSet resultSet = null;

    while (isActive()) {
        try {
            conn = sm.getConnection();
            StringBuilder query = new StringBuilder("select * from ").append(table_name).append(
                    " where timestamp*1000 > " + latest_timed + "  order by timestamp limit 0," + buffer_size);

            resultSet = sm.executeQueryWithResultSet(query, conn);

            //logger.debug(query);

            while (resultSet.next()) {
                Serializable[] output = new Serializable[this.getOutputFormat().length];

                //long pk = resultSet.getLong(1);
                long timed = resultSet.getLong(1) * 1000;

                //logger.warn("pk => "+ pk);
                //logger.warn("timed => "+ timed);

                for (int i = 0; i < dataFieldsLength; i++) {

                    switch (dataFieldTypes[i]) {
                    case DataTypes.VARCHAR:
                    case DataTypes.CHAR:
                        output[i] = resultSet.getString(i + 1);
                        break;
                    case DataTypes.INTEGER:
                        output[i] = resultSet.getInt(i + 1);
                        break;
                    case DataTypes.TINYINT:
                        output[i] = resultSet.getByte(i + 1);
                        break;
                    case DataTypes.SMALLINT:
                        output[i] = resultSet.getShort(i + 1);
                        break;
                    case DataTypes.DOUBLE:
                        output[i] = resultSet.getDouble(i + 1);
                        break;
                    case DataTypes.FLOAT:
                        output[i] = resultSet.getFloat(i + 1);
                        break;
                    case DataTypes.BIGINT:
                        output[i] = resultSet.getLong(i + 1);
                        break;
                    case DataTypes.BINARY:
                        output[i] = resultSet.getBytes(i + 1);
                        break;
                    }
                    //logger.warn(i+" (type: "+dataFieldTypes[i]+" ) => "+output[i]);
                }

                StreamElement se = new StreamElement(dataFieldNames, dataFieldTypes, output, timed);
                latest_timed = se.getTimeStamp();

                //logger.warn(" Latest => " + latest_timed);

                this.postStreamElement(se);

                updateCheckPointFile(latest_timed);

                //logger.warn(se);
            }

        } catch (IOException e) {
            logger.error(e.getMessage(), e);
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            sm.close(resultSet);
            sm.close(conn);
        }

        try {
            Thread.sleep(rate);
        } catch (InterruptedException e) {
            logger.error(e.getMessage(), e);
        }
    }
}

From source file:org.wso2.carbon.policy.mgt.core.dao.impl.PolicyDAOImpl.java

@Override
public Policy getAppliedPolicy(int deviceId, int enrollmentId) throws PolicyManagerDAOException {
    Connection conn;//from  w  ww . ja  v  a 2 s.  co m
    PreparedStatement stmt = null;
    ResultSet resultSet = null;
    int tenantId = PrivilegedCarbonContext.getThreadLocalCarbonContext().getTenantId();
    Policy policy = null;
    try {
        conn = this.getConnection();
        String query = "SELECT * FROM DM_DEVICE_POLICY_APPLIED WHERE DEVICE_ID = ? AND TENANT_ID = ? AND  "
                + "ENROLMENT_ID = ?";
        stmt = conn.prepareStatement(query);
        stmt.setInt(1, deviceId);
        stmt.setInt(2, tenantId);
        stmt.setInt(3, enrollmentId);
        resultSet = stmt.executeQuery();

        while (resultSet.next()) {
            ByteArrayInputStream bais = null;
            ObjectInputStream ois = null;
            byte[] contentBytes;

            try {
                contentBytes = resultSet.getBytes("POLICY_CONTENT");
                bais = new ByteArrayInputStream(contentBytes);
                ois = new ObjectInputStream(bais);
                policy = (Policy) ois.readObject();
            } finally {
                if (bais != null) {
                    try {
                        bais.close();
                    } catch (IOException e) {
                        log.warn("Error occurred while closing ByteArrayOutputStream", e);
                    }
                }
                if (ois != null) {
                    try {
                        ois.close();
                    } catch (IOException e) {
                        log.warn("Error occurred while closing ObjectOutputStream", e);
                    }
                }
            }
        }

    } catch (SQLException e) {
        throw new PolicyManagerDAOException("Error occurred while getting the applied policy", e);
    } catch (IOException e) {
        throw new PolicyManagerDAOException("Unable to read the byte stream for content", e);
    } catch (ClassNotFoundException e) {
        throw new PolicyManagerDAOException("Class not found while converting the object", e);
    } finally {
        PolicyManagementDAOUtil.cleanupResources(stmt, resultSet);
    }
    //
    //        if (policy != null && log.isDebugEnabled()) {
    //            log.debug("Applied policy logging details started ------------------");
    //            log.debug("Applied policy name " + policy.getPolicyName() + "for the device id " + deviceId);
    //            log.debug(policy.getCompliance());
    //            log.debug(policy.getId());
    //            log.debug(policy.getPriorityId());
    //            log.debug("Applied policy logging details finished....");
    //        }
    return policy;
}

From source file:gsn.wrappers.TetraedreFluoWrapper.java

public void run() {
    DataEnumerator data;//from w w  w.jav a2 s  .c  o m

    try {
        Thread.sleep(2000);
    } catch (InterruptedException e) {
        logger.error(e.getMessage(), e);
    }

    Connection conn = null;
    ResultSet resultSet = null;

    while (isActive()) {
        try {
            conn = sm.getConnection();
            StringBuilder query = new StringBuilder("select * from ").append(table_name)
                    .append(" where physical_input=").append(physical_input).append(" AND timestamp*1000 > "
                            + latest_timed + "  order by timestamp limit 0," + buffer_size);

            resultSet = sm.executeQueryWithResultSet(query, conn);

            //logger.debug(query);

            while (resultSet.next()) {
                Serializable[] output = new Serializable[this.getOutputFormat().length];

                //long pk = resultSet.getLong(1);
                long timed = resultSet.getLong(3) * 1000;

                //logger.warn("pk => "+ pk);
                //logger.warn("timed => "+ timed);

                for (int i = 0; i < dataFieldsLength; i++) {

                    switch (dataFieldTypes[i]) {
                    case DataTypes.VARCHAR:
                    case DataTypes.CHAR:
                        output[i] = resultSet.getString(i + 1);
                        break;
                    case DataTypes.INTEGER:
                        output[i] = resultSet.getInt(i + 1);
                        break;
                    case DataTypes.TINYINT:
                        output[i] = resultSet.getByte(i + 1);
                        break;
                    case DataTypes.SMALLINT:
                        output[i] = resultSet.getShort(i + 1);
                        break;
                    case DataTypes.DOUBLE:
                        output[i] = resultSet.getDouble(i + 1);
                        break;
                    case DataTypes.FLOAT:
                        output[i] = resultSet.getFloat(i + 1);
                        break;
                    case DataTypes.BIGINT:
                        output[i] = resultSet.getLong(i + 1);
                        break;
                    case DataTypes.BINARY:
                        output[i] = resultSet.getBytes(i + 1);
                        break;
                    }
                    //logger.warn(i+" (type: "+dataFieldTypes[i]+" ) => "+output[i]);
                }

                StreamElement se = new StreamElement(dataFieldNames, dataFieldTypes, output, timed);
                latest_timed = se.getTimeStamp();

                //logger.warn(" Latest => " + latest_timed);

                this.postStreamElement(se);

                updateCheckPointFile(latest_timed);

                //logger.warn(se);
            }

        } catch (IOException e) {
            logger.error(e.getMessage(), e);
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            sm.close(resultSet);
            sm.close(conn);
        }

        try {
            Thread.sleep(rate);
        } catch (InterruptedException e) {
            logger.error(e.getMessage(), e);
        }
    }
}

From source file:com.alibaba.otter.node.etl.common.db.utils.SqlUtils.java

/**
 * Retrieve a JDBC column value from a ResultSet, using the specified value
 * type./*from w w w  .  j a  va 2  s . c  o  m*/
 * <p>
 * Uses the specifically typed ResultSet accessor methods, falling back to
 * {@link #getResultSetValue(java.sql.ResultSet, int)} for unknown types.
 * <p>
 * Note that the returned value may not be assignable to the specified
 * required type, in case of an unknown type. Calling code needs to deal
 * with this case appropriately, e.g. throwing a corresponding exception.
 * 
 * @param rs is the ResultSet holding the data
 * @param index is the column index
 * @param requiredType the required value type (may be <code>null</code>)
 * @return the value object
 * @throws SQLException if thrown by the JDBC API
 */
private static String getResultSetValue(ResultSet rs, int index, Class<?> requiredType) throws SQLException {
    if (requiredType == null) {
        return getResultSetValue(rs, index);
    }

    Object value = null;
    boolean wasNullCheck = false;

    // Explicitly extract typed value, as far as possible.
    if (String.class.equals(requiredType)) {
        value = rs.getString(index);
    } else if (boolean.class.equals(requiredType) || Boolean.class.equals(requiredType)) {
        value = Boolean.valueOf(rs.getBoolean(index));
        wasNullCheck = true;
    } else if (byte.class.equals(requiredType) || Byte.class.equals(requiredType)) {
        value = new Byte(rs.getByte(index));
        wasNullCheck = true;
    } else if (short.class.equals(requiredType) || Short.class.equals(requiredType)) {
        value = new Short(rs.getShort(index));
        wasNullCheck = true;
    } else if (int.class.equals(requiredType) || Integer.class.equals(requiredType)) {
        value = new Long(rs.getLong(index));
        wasNullCheck = true;
    } else if (long.class.equals(requiredType) || Long.class.equals(requiredType)) {
        value = rs.getBigDecimal(index);
        wasNullCheck = true;
    } else if (float.class.equals(requiredType) || Float.class.equals(requiredType)) {
        value = new Float(rs.getFloat(index));
        wasNullCheck = true;
    } else if (double.class.equals(requiredType) || Double.class.equals(requiredType)
            || Number.class.equals(requiredType)) {
        value = new Double(rs.getDouble(index));
        wasNullCheck = true;
    } else if (java.sql.Time.class.equals(requiredType)) {
        // try {
        // value = rs.getTime(index);
        // } catch (SQLException e) {
        value = rs.getString(index);// ?string0000Time
        // if (value == null && !rs.wasNull()) {
        // value = "00:00:00"; //
        // mysqlzeroDateTimeBehavior=convertToNull0null
        // }
        // }
    } else if (java.sql.Timestamp.class.equals(requiredType) || java.sql.Date.class.equals(requiredType)) {
        // try {
        // value = convertTimestamp(rs.getTimestamp(index));
        // } catch (SQLException e) {
        // ?string0000-00-00 00:00:00Timestamp 
        value = rs.getString(index);
        // if (value == null && !rs.wasNull()) {
        // value = "0000:00:00 00:00:00"; //
        // mysqlzeroDateTimeBehavior=convertToNull0null
        // }
        // }
    } else if (BigDecimal.class.equals(requiredType)) {
        value = rs.getBigDecimal(index);
    } else if (BigInteger.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 if (byte[].class.equals(requiredType)) {
        try {
            byte[] bytes = rs.getBytes(index);
            if (bytes == null) {
                value = null;
            } else {
                value = new String(bytes, "ISO-8859-1");// binaryiso-8859-1
            }
        } catch (UnsupportedEncodingException e) {
            throw new SQLException(e);
        }
    } else {
        // Some unknown type desired -> rely on getObject.
        value = getResultSetValue(rs, index);
    }

    // Perform was-null check if demanded (for results that the
    // JDBC driver returns as primitives).
    if (wasNullCheck && (value != null) && rs.wasNull()) {
        value = null;
    }

    return (value == null) ? null : convertUtilsBean.convert(value);
}

From source file:org.wso2.carbon.identity.workflow.mgt.dao.WorkflowRequestDAO.java

/**
 * Get requests created/updated in given time period
 *
 * @param beginTime    lower limit of date range to filter
 * @param endTime      upper limit of date range to filter
 * @param timeCategory filter by created time or last updated time ?
 * @param tenant       tenant id of currently logged in user
 * @return/*from  w w w . j  av a  2  s .  c o m*/
 * @throws InternalWorkflowException
 */
public org.wso2.carbon.identity.workflow.mgt.bean.WorkflowRequest[] getRequestsFilteredByTime(
        Timestamp beginTime, Timestamp endTime, String timeCategory, int tenant, String status)
        throws InternalWorkflowException {

    Connection connection = IdentityDatabaseUtil.getDBConnection();
    PreparedStatement prepStmt = null;
    String query = "";

    ResultSet resultSet = null;
    try {
        if (timeCategory == UPDATED_AT_FILTER) {
            if (status.equals(ALL_TASKS_FILTER) || status.equals("")) {
                query = SQLConstants.GET_REQUESTS_FILTER_FROM_UPDATED_TIME;
            } else {
                query = SQLConstants.GET_REQUESTS_FILTER_FROM_UPDATED_TIME_AND_STATUS;
            }
        } else {
            if (status.equals(ALL_TASKS_FILTER) || status.equals("")) {
                query = SQLConstants.GET_REQUESTS_FILTER_FROM_CREATED_TIME;
            } else {
                query = SQLConstants.GET_REQUESTS_FILTER_FROM_CREATED_TIME_AND_STATUS;
            }
        }
        prepStmt = connection.prepareStatement(query);
        prepStmt.setTimestamp(1, beginTime);
        prepStmt.setTimestamp(2, endTime);
        prepStmt.setInt(3, tenant);
        if (status.equals(ALL_TASKS_FILTER) || status.equals("")) {

            prepStmt.setInt(4, SQLConstants.maxResultsPerRequest);
        } else {
            prepStmt.setString(4, status);
            prepStmt.setInt(5, SQLConstants.maxResultsPerRequest);
        }
        resultSet = prepStmt.executeQuery();
        ArrayList<org.wso2.carbon.identity.workflow.mgt.bean.WorkflowRequest> requestDTOs = new ArrayList<>();
        while (resultSet.next()) {
            org.wso2.carbon.identity.workflow.mgt.bean.WorkflowRequest requestDTO = new org.wso2.carbon.identity.workflow.mgt.bean.WorkflowRequest();
            requestDTO.setRequestId(resultSet.getString(SQLConstants.REQUEST_UUID_COLUMN));
            requestDTO.setEventType(resultSet.getString(SQLConstants.REQUEST_OPERATION_TYPE_COLUMN));
            requestDTO.setCreatedAt(resultSet.getTimestamp(SQLConstants.REQUEST_CREATED_AT_COLUMN).toString());
            requestDTO.setUpdatedAt(resultSet.getTimestamp(SQLConstants.REQUEST_UPDATED_AT_COLUMN).toString());
            requestDTO.setStatus(resultSet.getString(SQLConstants.REQUEST_STATUS_COLUMN));
            requestDTO.setRequestParams(
                    (deserializeWorkflowRequest(resultSet.getBytes(SQLConstants.REQUEST_COLUMN)))
                            .getRequestParameterAsString());
            requestDTO.setCreatedBy(resultSet.getString(SQLConstants.CREATED_BY_COLUMN));
            requestDTOs.add(requestDTO);
        }
        org.wso2.carbon.identity.workflow.mgt.bean.WorkflowRequest[] requestArray = new org.wso2.carbon.identity.workflow.mgt.bean.WorkflowRequest[requestDTOs
                .size()];
        for (int i = 0; i < requestDTOs.size(); i++) {
            requestArray[i] = requestDTOs.get(i);
        }
        return requestArray;
    } catch (SQLException e) {
        throw new InternalWorkflowException("Error when executing the sql query:" + query, e);
    } catch (ClassNotFoundException | IOException e) {
        throw new InternalWorkflowException("Error when deserializing a workflow request.", e);
    } finally {
        IdentityDatabaseUtil.closeAllConnections(connection, resultSet, prepStmt);
    }
}