Example usage for java.sql PreparedStatement setBytes

List of usage examples for java.sql PreparedStatement setBytes

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. setBytes

Introduction

In this page you can find the example usage for java.sql PreparedStatement setBytes.

Prototype

void setBytes(int parameterIndex, byte x[]) throws SQLException;

Source Link

Document

Sets the designated parameter to the given Java array of bytes.

Usage

From source file:org.apache.jcs.auxiliary.disk.jdbc.JDBCDiskCache.java

public void doUpdate(ICacheElement ce) {
    incrementUpdateCount();//from   w w w  . j av  a2s. c om

    if (log.isDebugEnabled()) {
        log.debug("updating, ce = " + ce);
    }

    Connection con;
    try {
        con = poolAccess.getConnection();
    } catch (SQLException e) {
        log.error("Problem getting conenction.", e);
        return;
    }

    try {
        // TEST
        Statement sStatement = null;
        try {
            sStatement = con.createStatement();
            alive = true;
        } catch (SQLException e) {
            log.error("Problem creating statement.", e);
            alive = false;
        } finally {
            try {
                sStatement.close();
            } catch (SQLException e) {
                log.error("Problem closing statement.", e);
            }
        }

        if (!alive) {
            if (log.isInfoEnabled()) {
                log.info("Disk is not alive, aborting put.");
            }
            return;
        }

        if (log.isDebugEnabled()) {
            log.debug("Putting [" + ce.getKey() + "] on disk.");
        }

        byte[] element;

        try {
            element = serialize(ce);
        } catch (IOException e) {
            log.error("Could not serialize element", e);
            return;
        }

        boolean exists = false;

        // First do a query to determine if the element already exists
        if (this.getJdbcDiskCacheAttributes().isTestBeforeInsert()) {
            exists = doesElementExist(ce);
        }

        // If it doesn't exist, insert it, otherwise update
        if (!exists) {
            try {
                String sqlI = "insert into " + getJdbcDiskCacheAttributes().getTableName()
                        + " (CACHE_KEY, REGION, ELEMENT, MAX_LIFE_SECONDS, IS_ETERNAL, CREATE_TIME, CREATE_TIME_SECONDS, SYSTEM_EXPIRE_TIME_SECONDS) "
                        + " values (?, ?, ?, ?, ?, ?, ?, ?)";

                PreparedStatement psInsert = con.prepareStatement(sqlI);
                psInsert.setString(1, (String) ce.getKey());
                psInsert.setString(2, this.getCacheName());
                psInsert.setBytes(3, element);
                psInsert.setLong(4, ce.getElementAttributes().getMaxLifeSeconds());
                if (ce.getElementAttributes().getIsEternal()) {
                    psInsert.setString(5, "T");
                } else {
                    psInsert.setString(5, "F");
                }
                Date createTime = new Date(ce.getElementAttributes().getCreateTime());
                psInsert.setDate(6, createTime);

                long now = System.currentTimeMillis() / 1000;
                psInsert.setLong(7, now);

                long expireTime = now + ce.getElementAttributes().getMaxLifeSeconds();
                psInsert.setLong(8, expireTime);

                psInsert.execute();
                psInsert.close();
            } catch (SQLException e) {
                if (e.toString().indexOf("Violation of unique index") != -1
                        || e.getMessage().indexOf("Violation of unique index") != -1
                        || e.getMessage().indexOf("Duplicate entry") != -1) {
                    exists = true;
                } else {
                    log.error("Could not insert element", e);
                }

                // see if it exists, if we didn't already
                if (!exists && !this.getJdbcDiskCacheAttributes().isTestBeforeInsert()) {
                    exists = doesElementExist(ce);
                }
            }
        }

        // update if it exists.
        if (exists) {
            String sqlU = null;
            try {
                sqlU = "update " + getJdbcDiskCacheAttributes().getTableName()
                        + " set ELEMENT  = ?, CREATE_TIME = ?, CREATE_TIME_SECONDS = ?, "
                        + " SYSTEM_EXPIRE_TIME_SECONDS = ? " + " where CACHE_KEY = ? and REGION = ?";
                PreparedStatement psUpdate = con.prepareStatement(sqlU);
                psUpdate.setBytes(1, element);

                Date createTime = new Date(ce.getElementAttributes().getCreateTime());
                psUpdate.setDate(2, createTime);

                long now = System.currentTimeMillis() / 1000;
                psUpdate.setLong(3, now);

                long expireTime = now + ce.getElementAttributes().getMaxLifeSeconds();
                psUpdate.setLong(4, expireTime);

                psUpdate.setString(5, (String) ce.getKey());
                psUpdate.setString(6, this.getCacheName());
                psUpdate.execute();
                psUpdate.close();

                if (log.isDebugEnabled()) {
                    log.debug("ran update " + sqlU);
                }
            } catch (SQLException e2) {
                log.error("e2 sql [" + sqlU + "] Exception: ", e2);
            }
        }
    } finally {
        try {
            con.close();
        } catch (SQLException e) {
            log.error("Problem closing connection.", e);
        }
    }

    if (log.isInfoEnabled()) {
        if (updateCount % LOG_INTERVAL == 0) {
            // TODO make a log stats method
            log.info("Update Count [" + updateCount + "]");
        }
    }
}

From source file:org.cerberus.crud.dao.impl.TestCaseCountryPropertiesDAO.java

@Override
public List<String> findCountryByPropertyNameAndTestCase(String test, String testcase, String property) {
    List<String> result = new ArrayList<String>();

    final String query = "SELECT country FROM testcasecountryproperties WHERE test = ? AND testcase = ? AND hex(`property`) like hex(?)";

    Connection connection = this.databaseSpring.connect();
    try {/*from ww w.j a v  a 2 s  . c o  m*/
        PreparedStatement preStat = connection.prepareStatement(query);
        try {
            preStat.setString(1, test);
            preStat.setString(2, testcase);
            preStat.setBytes(3, property.getBytes("UTF-8"));

            ResultSet resultSet = preStat.executeQuery();
            try {
                while (resultSet.next()) {
                    String country = resultSet.getString("country");
                    if (country != null && !"".equals(country)) {
                        result.add(country);
                    }
                }
            } catch (SQLException exception) {
                LOG.error("Unable to execute query : " + exception.toString());
            } finally {
                resultSet.close();
            }
        } catch (SQLException exception) {
            LOG.error("Unable to execute query : " + exception.toString());
        } catch (UnsupportedEncodingException ex) {
            LOG.error(ex.toString());
        } finally {
            preStat.close();
        }
    } catch (SQLException exception) {
        LOG.error("Unable to execute query : " + exception.toString());
    } finally {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            LOG.warn(e.toString());
        }
    }

    if (result.size() == 0) {
        return null;
    }

    return result;
}

From source file:org.apache.gora.sql.store.SqlStore.java

protected void setBytes(PreparedStatement statement, Column column, int index, byte[] value)
        throws SQLException {

    switch (column.getJdbcType()) {
    case BLOB://  w ww  .  java  2s  .c o m
        statement.setBlob(index, new ByteArrayInputStream(value), value.length);
        break;
    case BINARY:
    case VARBINARY:
        statement.setBytes(index, value);
        break;
    case LONGVARBINARY:
        statement.setBinaryStream(index, new ByteArrayInputStream(value));
        break;
    }
}

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

/**
 * Do a 100 row insert inside a loop/*from   w  w  w . ja v  a 2s.  c om*/
 * 
 * @param connection
 *            the AceQL Connection
 * 
 * @throws Exception
 *             it any Exception occurs
 */
public void insertLoopPrepStatement(Connection connection, int numberToInsert) throws Exception {
    // We can now use our Remote JDBC Connection as a regular Connection!
    connection.setAutoCommit(false);

    // We will do all our remote insert in a SQL Transaction
    try {
        String sql = "insert into orderlog values ( ?, ?, ?, ?, ?, ?, ?, ?, ? )";

        // Create a new Prepared Statement
        PreparedStatement prepStatement = null;

        MessageDisplayer.display("");
        MessageDisplayer.display("Inserting " + numberToInsert + " orderlog...");

        SqlUtil sqlUtil = new SqlUtil(connection);

        for (int customerId = 1; customerId < numberToInsert + 1; customerId++) {
            int i = 1;
            long theTime = new java.util.Date().getTime();

            prepStatement = connection.prepareStatement(sql);

            prepStatement.setInt(i++, customerId);
            prepStatement.setInt(i++, customerId);
            prepStatement.setString(i++, "Item Description No " + customerId);
            prepStatement.setBigDecimal(i++, new BigDecimal(customerId));
            prepStatement.setDate(i++, new java.sql.Date(theTime));
            prepStatement.setTimestamp(i++, new Timestamp(theTime));

            prepStatement.setBytes(i++, null); // No Blob in this example.

            if (sqlUtil.isIngres() || sqlUtil.isPostgreSQL()) {
                prepStatement.setInt(i++, 0);
            } else {
                prepStatement.setBoolean(i++, false);
            }

            prepStatement.setInt(i++, customerId);

            prepStatement.executeUpdate();
            prepStatement.close();
        }

        // We do either everything in a single transaction or nothing
        connection.commit(); // Commit is propagated on Server
        MessageDisplayer.display("Remote Commit Done on AceQL Server!");
    } catch (Exception e) {
        connection.rollback();
        throw e;
    } finally {
        connection.setAutoCommit(true);
    }

}

From source file:org.panbox.core.keymgmt.JDBCHelperNonRevokeable.java

protected void storeKeys(ShareKeyDB shareKeys, ObfuscationKeyDB obKeys, Connection con) throws SQLException {
    // Store ShareKeys
    PreparedStatement insert = con.prepareStatement(INSERT_SHAREKEYS);
    Iterator<Integer> entries = shareKeys.getKeyIterator();
    while (entries.hasNext()) {
        int version = entries.next();
        ShareKeyDBEntry entry = shareKeys.getEntry(version);

        insert.setInt(1, version);// ww w . j  ava2s.  c o m

        Iterator<PublicKey> keys = entry.getKeyIterator();
        while (keys.hasNext()) {
            PublicKey pKey = (PublicKey) keys.next();
            byte[] encKey = entry.getEncryptedKey(pKey);
            // Store id, time, pkey enckey;
            insert.setBytes(2, pKey.getEncoded());
            insert.setBytes(3, encKey);
            int count = insert.executeUpdate();
            logger.debug("Inserted " + count + " sharekey");
        }
    }
    insert.close();

    // Store ObKeys
    insert = con.prepareStatement(INSERT_OBFUSCATIONKEYS);
    Iterator<PublicKey> keys = obKeys.getKeys();
    while (keys.hasNext()) {
        PublicKey pKey = (PublicKey) keys.next();
        byte[] encKey = obKeys.get(pKey);
        insert.setBytes(1, pKey.getEncoded());
        insert.setBytes(2, encKey);
        int count = insert.executeUpdate();
        logger.debug("Inserted " + count + " obkey");
        insert.clearParameters();
    }
    insert.close();
}

From source file:org.cerberus.crud.dao.impl.TestCaseCountryPropertiesDAO.java

@Override
public TestCaseCountryProperties findTestCaseCountryPropertiesByKey(String test, String testcase,
        String country, String property) throws CerberusException {
    TestCaseCountryProperties result = null;
    boolean throwException = false;
    final String query = "SELECT * FROM testcasecountryproperties WHERE test = ? AND testcase = ? AND country = ? AND hex(`property`) = hex(?)";

    Connection connection = this.databaseSpring.connect();
    try {// w  ww  . j a v  a2  s . co m
        PreparedStatement preStat = connection.prepareStatement(query);
        try {
            preStat.setString(1, test);
            preStat.setString(2, testcase);
            preStat.setString(3, country);
            preStat.setBytes(4, property.getBytes("UTF-8"));

            ResultSet resultSet = preStat.executeQuery();
            try {
                if (resultSet.first()) {
                    String description = resultSet.getString("description");
                    String type = resultSet.getString("type");
                    String database = resultSet.getString("database");
                    String value1 = resultSet.getString("value1");
                    String value2 = resultSet.getString("value2");
                    int length = resultSet.getInt("length");
                    int rowLimit = resultSet.getInt("rowLimit");
                    String nature = resultSet.getString("nature");
                    int retryNb = resultSet.getInt("RetryNb");
                    int retryPeriod = resultSet.getInt("RetryPeriod");
                    result = factoryTestCaseCountryProperties.create(test, testcase, country, property,
                            description, type, database, value1, value2, length, rowLimit, nature, retryNb,
                            retryPeriod);
                } else {
                    throwException = true;
                }
            } catch (SQLException exception) {
                LOG.error("Unable to execute query : " + exception.toString());
            } finally {
                resultSet.close();
            }
        } catch (SQLException exception) {
            LOG.error("Unable to execute query : " + exception.toString());
        } catch (UnsupportedEncodingException ex) {
            LOG.error(ex.toString());
        } finally {
            preStat.close();
        }
    } catch (SQLException exception) {
        LOG.error("Unable to execute query : " + exception.toString());
    } finally {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            LOG.warn(e.toString());
        }
    }
    if (throwException) {
        throw new CerberusException(new MessageGeneral(MessageGeneralEnum.NO_DATA_FOUND));
    }
    return result;
}

From source file:org.cerberus.crud.dao.impl.TestCaseCountryPropertiesDAO.java

@Override
public void deleteTestCaseCountryProperties(TestCaseCountryProperties tccp) throws CerberusException {
    boolean throwExcep = false;
    final String query = "DELETE FROM testcasecountryproperties WHERE test = ? and testcase = ? and country = ? and hex(`property`) like hex(?)";

    Connection connection = this.databaseSpring.connect();
    try {//from www.  ja  v a  2s.c  om
        PreparedStatement preStat = connection.prepareStatement(query);
        try {
            preStat.setString(1, tccp.getTest());
            preStat.setString(2, tccp.getTestCase());
            preStat.setString(3, tccp.getCountry());
            preStat.setBytes(4, tccp.getProperty().getBytes("UTF-8"));

            throwExcep = preStat.executeUpdate() == 0;
        } catch (SQLException exception) {
            LOG.error("Unable to execute query : " + exception.toString());
        } catch (UnsupportedEncodingException ex) {
            LOG.error(ex.toString());
        } finally {
            preStat.close();
        }
    } catch (SQLException exception) {
        LOG.error("Unable to execute query : " + exception.toString());
    } finally {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            LOG.warn(e.toString());
        }
    }
    if (throwExcep) {
        throw new CerberusException(new MessageGeneral(MessageGeneralEnum.CANNOT_UPDATE_TABLE));
    }
}

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

@Override
public void addCertificate(List<Certificate> certificates) throws CertificateManagementDAOException {
    Connection conn;/*  ww w .j a v a 2 s  . co m*/
    PreparedStatement stmt = null;
    try {
        conn = this.getConnection();
        stmt = conn.prepareStatement(
                "INSERT INTO DM_DEVICE_CERTIFICATE (SERIAL_NUMBER, CERTIFICATE, TENANT_ID, USERNAME)"
                        + " VALUES (?,?,?,?)");
        PrivilegedCarbonContext threadLocalCarbonContext = PrivilegedCarbonContext
                .getThreadLocalCarbonContext();
        String username = threadLocalCarbonContext.getUsername();
        for (Certificate certificate : certificates) {
            // the serial number of the certificate used for its creation is set as its alias.
            String serialNumber = certificate.getSerial();
            if (serialNumber == null || serialNumber.isEmpty()) {
                serialNumber = String.valueOf(certificate.getCertificate().getSerialNumber());
            }
            byte[] bytes = Serializer.serialize(certificate.getCertificate());

            stmt.setString(1, serialNumber);
            stmt.setBytes(2, bytes);
            stmt.setInt(3, certificate.getTenantId());
            stmt.setString(4, username);
            stmt.addBatch();
        }
        stmt.executeBatch();
    } catch (SQLException | IOException e) {
        throw new CertificateManagementDAOException("Error occurred while saving certificates. ", e);
    } finally {
        CertificateManagementDAOUtil.cleanupResources(stmt, null);
    }
}

From source file:org.cerberus.crud.dao.impl.TestCaseCountryPropertiesDAO.java

@Override
public List<String> findCountryByProperty(TestCaseCountryProperties testCaseCountryProperties) {
    List<String> list = null;
    final StringBuilder query = new StringBuilder();
    query.append("SELECT country FROM testcasecountryproperties WHERE test = ? AND testcase = ?");
    query.append(/* w  ww.ja va 2  s  .  c o m*/
            " AND HEX(`property`) = hex(?) AND `type` =? AND `database` =? AND hex(`value1`) like hex( ? ) AND hex(`value2`) like hex( ? ) AND `length` = ? ");
    query.append(" AND `rowlimit` = ? AND `nature` = ?");

    // Debug message on SQL.
    if (LOG.isDebugEnabled()) {
        LOG.debug("SQL : " + query.toString());
    }

    Connection connection = this.databaseSpring.connect();
    try {
        PreparedStatement preStat = connection.prepareStatement(query.toString());
        try {
            preStat.setString(1, testCaseCountryProperties.getTest());
            preStat.setString(2, testCaseCountryProperties.getTestCase());
            preStat.setBytes(3, testCaseCountryProperties.getProperty().getBytes("UTF-8"));
            preStat.setString(4, testCaseCountryProperties.getType());
            preStat.setString(5, testCaseCountryProperties.getDatabase());
            preStat.setBytes(6, testCaseCountryProperties.getValue1().getBytes("UTF-8"));
            preStat.setBytes(7, testCaseCountryProperties.getValue2().getBytes("UTF-8"));
            preStat.setString(8, String.valueOf(testCaseCountryProperties.getLength()));
            preStat.setString(9, String.valueOf(testCaseCountryProperties.getRowLimit()));
            preStat.setString(10, testCaseCountryProperties.getNature());

            ResultSet resultSet = preStat.executeQuery();
            try {
                list = new ArrayList<String>();
                String valueToAdd;

                while (resultSet.next()) {
                    valueToAdd = resultSet.getString("Country") == null ? "" : resultSet.getString("Country");
                    list.add(valueToAdd);
                }
            } catch (SQLException exception) {
                LOG.error("Unable to execute query : " + exception.toString());
            } finally {
                resultSet.close();
            }
        } catch (SQLException exception) {
            LOG.error("Unable to execute query : " + exception.toString());
        } catch (UnsupportedEncodingException ex) {
            LOG.error(ex.toString());
        } finally {
            preStat.close();
        }
    } catch (SQLException exception) {
        LOG.error("Unable to execute query : " + exception.toString());
    } finally {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            LOG.warn(e.toString());
        }
    }
    return list;
}

From source file:org.apache.phoenix.end2end.UserDefinedFunctionsIT.java

@Test
public void testUDFsWithSameChildrenInAQuery() throws Exception {
    Connection conn = driver.connect(url, EMPTY_PROPS);
    Statement stmt = conn.createStatement();
    conn.createStatement().execute("create table t11(k varbinary primary key, k1 integer, lastname varchar)");
    String query = "UPSERT INTO t11" + "(k, k1, lastname) " + "VALUES(?,?,?)";
    PreparedStatement pStmt = conn.prepareStatement(query);
    pStmt.setBytes(1, new byte[] { 0, 0, 0, 0, 0, 0, 0, 1 });
    pStmt.setInt(2, 1);//from w  ww .j  av  a2 s .c om
    pStmt.setString(3, "jock");
    pStmt.execute();
    conn.commit();
    stmt.execute("create function udf1(VARBINARY) returns UNSIGNED_LONG as 'org.apache.phoenix.end2end."
            + GETX_CLASSNAME + "' using jar " + "'" + util.getConfiguration().get(DYNAMIC_JARS_DIR_KEY)
            + "/myjar5.jar" + "'");
    stmt.execute("create function udf2(VARBINARY) returns INTEGER as 'org.apache.phoenix.end2end."
            + GETY_CLASSNAME + "' using jar " + "'" + util.getConfiguration().get(DYNAMIC_JARS_DIR_KEY)
            + "/myjar6.jar" + "'");
    ResultSet rs = stmt.executeQuery("select udf1(k), udf2(k) from t11");
    assertTrue(rs.next());
    assertEquals(72057594037927936l, rs.getLong(1));
    assertEquals(0, rs.getInt(2));
    rs = stmt.executeQuery("select udf2(k), udf1(k) from t11");
    assertTrue(rs.next());
    assertEquals(0, rs.getInt(1));
    assertEquals(72057594037927936l, rs.getLong(2));
    rs = stmt.executeQuery("select udf1(k), udf1(k) from t11");
    assertTrue(rs.next());
    assertEquals(72057594037927936l, rs.getLong(1));
    assertEquals(72057594037927936l, rs.getLong(2));
}