Example usage for java.sql PreparedStatement setBytes

List of usage examples for java.sql PreparedStatement 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.wso2.carbon.policy.mgt.core.dao.impl.PolicyDAOImpl.java

@Override
public boolean addPolicyCriteriaProperties(List<PolicyCriterion> policyCriteria)
        throws PolicyManagerDAOException {
    Connection conn;/*from w w w  . ja  v a2  s .  c o  m*/
    PreparedStatement stmt = null;
    try {
        conn = this.getConnection();
        String query = "INSERT INTO DM_POLICY_CRITERIA_PROPERTIES (POLICY_CRITERION_ID, PROP_KEY, PROP_VALUE, "
                + "CONTENT) VALUES (?, ?, ?, ?)";
        stmt = conn.prepareStatement(query);

        for (PolicyCriterion criterion : policyCriteria) {
            Properties prop = criterion.getProperties();
            for (String name : prop.stringPropertyNames()) {

                stmt.setInt(1, criterion.getId());
                stmt.setString(2, name);
                stmt.setString(3, prop.getProperty(name));
                stmt.setBytes(4, PolicyManagerUtil.getBytes(criterion.getObjectMap()));
                stmt.addBatch();
            }
            stmt.executeBatch();
        }
        //   stmt.executeUpdate();

    } catch (SQLException | IOException e) {
        throw new PolicyManagerDAOException(
                "Error occurred while inserting the criterion properties " + "to database", e);
    } finally {
        PolicyManagementDAOUtil.cleanupResources(stmt, null);
    }
    return false;
}

From source file:org.parosproxy.paros.db.TableHistory.java

public RecordHistory getHistoryCache(HistoryReference ref, HttpMessage reqMsg)
        throws SQLException, HttpMalformedHeaderException {

    //  get the cache from provided reference.
    //  naturally, the obtained cache should be AFTER AND NEARBY to the given reference.
    //  - historyId up to historyId+200
    //  - match sessionId
    //  - history type can be MANUEL or hidden (hidden is used by images not explicitly stored in history)
    //  - match URI
    PreparedStatement psReadCache = null;

    if (isExistStatusCode) {
        //          psReadCache = getConnection().prepareStatement("SELECT TOP 1 * FROM HISTORY WHERE URI = ? AND METHOD = ? AND REQBODY = ? AND " + HISTORYID + " >= ? AND " + HISTORYID + " <= ? AND SESSIONID = ? AND (HISTTYPE = " + HistoryReference.TYPE_MANUAL + " OR HISTTYPE = " + HistoryReference.TYPE_HIDDEN + ") AND STATUSCODE != 304");
        psReadCache = getConnection().prepareStatement(
                "SELECT TOP 1 * FROM HISTORY WHERE URI = ? AND METHOD = ? AND REQBODY = ? AND " + HISTORYID
                        + " >= ? AND " + HISTORYID + " <= ? AND SESSIONID = ? AND STATUSCODE != 304");

    } else {/*from   w w  w.j a  va 2 s  .  c  om*/
        //          psReadCache = getConnection().prepareStatement("SELECT * FROM HISTORY WHERE URI = ? AND METHOD = ? AND REQBODY = ? AND " + HISTORYID + " >= ? AND " + HISTORYID + " <= ? AND SESSIONID = ? AND (HISTTYPE = " + HistoryReference.TYPE_MANUAL + " OR HISTTYPE = " + HistoryReference.TYPE_HIDDEN + ")");
        psReadCache = getConnection()
                .prepareStatement("SELECT * FROM HISTORY WHERE URI = ? AND METHOD = ? AND REQBODY = ? AND "
                        + HISTORYID + " >= ? AND " + HISTORYID + " <= ? AND SESSIONID = ?)");

    }
    psReadCache.setString(1, reqMsg.getRequestHeader().getURI().toString());
    psReadCache.setString(2, reqMsg.getRequestHeader().getMethod());

    if (bodiesAsBytes) {
        psReadCache.setBytes(3, reqMsg.getRequestBody().getBytes());
    } else {
        psReadCache.setString(3, new String(reqMsg.getRequestBody().getBytes()));
    }

    psReadCache.setInt(4, ref.getHistoryId());
    psReadCache.setInt(5, ref.getHistoryId() + 200);
    psReadCache.setLong(6, ref.getSessionId());

    ResultSet rs = psReadCache.executeQuery();
    RecordHistory rec = null;

    try {
        do {
            rec = build(rs);
            // for retrieval from cache, the message requests nature must be the same.
            // and the result should NOT be NOT_MODIFIED for rendering by browser
            if (rec != null && rec.getHttpMessage().equals(reqMsg) && rec.getHttpMessage().getResponseHeader()
                    .getStatusCode() != HttpStatusCode.NOT_MODIFIED) {
                return rec;
            }

        } while (rec != null);

    } finally {
        try {
            rs.close();
            psReadCache.close();
        } catch (Exception e) {
            // ZAP: Log exceptions
            log.warn(e.getMessage(), e);
        }
    }

    // if cache not exist, probably due to NOT_MODIFIED,
    // lookup from cache BEFORE the given reference

    if (isExistStatusCode) {
        //            psReadCache = getConnection().prepareStatement("SELECT TOP 1 * FROM HISTORY WHERE URI = ? AND METHOD = ? AND REQBODY = ? AND SESSIONID = ? AND STATUSCODE != 304 AND (HISTTYPE = " + HistoryReference.TYPE_MANUAL + " OR HISTTYPE = " + HistoryReference.TYPE_HIDDEN  + ")");
        psReadCache = getConnection().prepareStatement(
                "SELECT TOP 1 * FROM HISTORY WHERE URI = ? AND METHOD = ? AND REQBODY = ? AND SESSIONID = ? AND STATUSCODE != 304");

    } else {
        //            psReadCache = getConnection().prepareStatement("SELECT * FROM HISTORY WHERE URI = ? AND METHOD = ? AND REQBODY = ? AND SESSIONID = ? AND (HISTTYPE = " + HistoryReference.TYPE_MANUAL + " OR HISTTYPE = " + HistoryReference.TYPE_HIDDEN  + ")");
        psReadCache = getConnection().prepareStatement(
                "SELECT * FROM HISTORY WHERE URI = ? AND METHOD = ? AND REQBODY = ? AND SESSIONID = ?");

    }
    psReadCache.setString(1, reqMsg.getRequestHeader().getURI().toString());
    psReadCache.setString(2, reqMsg.getRequestHeader().getMethod());

    if (bodiesAsBytes) {
        psReadCache.setBytes(3, reqMsg.getRequestBody().getBytes());
    } else {
        psReadCache.setString(3, new String(reqMsg.getRequestBody().getBytes()));
    }

    psReadCache.setLong(4, ref.getSessionId());

    rs = psReadCache.executeQuery();
    rec = null;

    try {
        do {
            rec = build(rs);
            if (rec != null && rec.getHttpMessage().equals(reqMsg) && rec.getHttpMessage().getResponseHeader()
                    .getStatusCode() != HttpStatusCode.NOT_MODIFIED) {
                return rec;
            }

        } while (rec != null);

    } finally {
        try {
            rs.close();
            psReadCache.close();
        } catch (Exception e) {
            // ZAP: Log exceptions
            log.warn(e.getMessage(), e);
        }

    }

    return null;
}

From source file:org.apache.jmeter.protocol.jdbc.AbstractJDBCTestElement.java

private void setArgument(PreparedStatement pstmt, String argument, int targetSqlType, int index)
        throws SQLException {
    switch (targetSqlType) {
    case Types.INTEGER:
        pstmt.setInt(index, Integer.parseInt(argument));
        break;//  ww  w  . ja  v a  2  s.  c o m
    case Types.DECIMAL:
    case Types.NUMERIC:
        pstmt.setBigDecimal(index, new BigDecimal(argument));
        break;
    case Types.DOUBLE:
    case Types.FLOAT:
        pstmt.setDouble(index, Double.parseDouble(argument));
        break;
    case Types.CHAR:
    case Types.LONGVARCHAR:
    case Types.VARCHAR:
        pstmt.setString(index, argument);
        break;
    case Types.BIT:
    case Types.BOOLEAN:
        pstmt.setBoolean(index, Boolean.parseBoolean(argument));
        break;
    case Types.BIGINT:
        pstmt.setLong(index, Long.parseLong(argument));
        break;
    case Types.DATE:
        pstmt.setDate(index, Date.valueOf(argument));
        break;
    case Types.REAL:
        pstmt.setFloat(index, Float.parseFloat(argument));
        break;
    case Types.TINYINT:
        pstmt.setByte(index, Byte.parseByte(argument));
        break;
    case Types.SMALLINT:
        pstmt.setShort(index, Short.parseShort(argument));
        break;
    case Types.TIMESTAMP:
        pstmt.setTimestamp(index, Timestamp.valueOf(argument));
        break;
    case Types.TIME:
        pstmt.setTime(index, Time.valueOf(argument));
        break;
    case Types.BINARY:
    case Types.VARBINARY:
    case Types.LONGVARBINARY:
        pstmt.setBytes(index, argument.getBytes());
        break;
    case Types.NULL:
        pstmt.setNull(index, targetSqlType);
        break;
    default:
        pstmt.setObject(index, argument, targetSqlType);
    }
}

From source file:org.wso2.carbon.device.mgt.core.operation.mgt.dao.impl.GenericOperationDAOImpl.java

@Override
public void addOperationResponse(int enrolmentId, int operationId, Object operationResponse)
        throws OperationManagementDAOException {
    PreparedStatement stmt = null;
    ByteArrayOutputStream bao = null;
    ObjectOutputStream oos = null;
    ResultSet rs = null;//from  w w  w  .ja  v  a  2  s.  c  o m
    try {
        Connection connection = OperationManagementDAOFactory.getConnection();

        stmt = connection.prepareStatement(
                "SELECT ID FROM DM_ENROLMENT_OP_MAPPING WHERE ENROLMENT_ID = ? " + "AND OPERATION_ID = ?");
        stmt.setInt(1, enrolmentId);
        stmt.setInt(2, operationId);

        rs = stmt.executeQuery();
        int enPrimaryId = 0;
        if (rs.next()) {
            enPrimaryId = rs.getInt("ID");
        }
        stmt = connection
                .prepareStatement("INSERT INTO DM_DEVICE_OPERATION_RESPONSE(OPERATION_ID, ENROLMENT_ID, "
                        + "EN_OP_MAP_ID, OPERATION_RESPONSE, RECEIVED_TIMESTAMP) VALUES(?, ?, ?, ?, ?)");
        bao = new ByteArrayOutputStream();
        oos = new ObjectOutputStream(bao);
        oos.writeObject(operationResponse);

        stmt.setInt(1, operationId);
        stmt.setInt(2, enrolmentId);
        stmt.setInt(3, enPrimaryId);
        stmt.setBytes(4, bao.toByteArray());
        stmt.setTimestamp(5, new Timestamp(new Date().getTime()));
        stmt.executeUpdate();
    } catch (SQLException e) {
        throw new OperationManagementDAOException("Error occurred while inserting operation response", e);
    } catch (IOException e) {
        throw new OperationManagementDAOException("Error occurred while serializing policy operation object",
                e);
    } finally {
        if (bao != null) {
            try {
                bao.close();
            } catch (IOException e) {
                log.warn("Error occurred while closing ByteArrayOutputStream", e);
            }
        }
        if (oos != null) {
            try {
                oos.close();
            } catch (IOException e) {
                log.warn("Error occurred while closing ObjectOutputStream", e);
            }
        }
        OperationManagementDAOUtil.cleanupResources(stmt, rs);
    }
}

From source file:org.eclipse.smila.connectivity.framework.crawler.jdbc.test.AbstractDataEnabledJdbcCrawlerTestCase.java

/**
 * {@inheritDoc} Called by the JUnit-Runner before execution of a testMethod of inheriting classes. Sets up a Database
 * fixture by performing the following steps:
 * <ol>// w  w w  .j  a  v a 2  s .  c  o  m
 * <li>Shutdown a (potentially) running Derby engine by calling {@link DriverManager#getConnection(String)} with the
 * Shutdown-URL (see {@link #SHUTDOWN_URL}).</li>
 * <li>Delete all database files (potentially) remaining from prior test cases.</li>
 * <li>Configure Derby engine to log all executed SQL in the log file and to rather append to an existing logfile than
 * to overwrite it.</li>
 * <li>Get a {@link Connection} to the Derby DB and insert 100 rows of data (see source code for details). This
 * includes BLOB (from image file) and CLOB (from text file) fields.</li>
 * <li>Release allocated JDBC-resources (Statement, Connection).</li>
 * <li>Shutdown Derby Engine via Shutdown-URL (so the Crawler can start it up as it would normally)</li>
 * <li>Instantiates a {@link JdbcCrawler}.</li>
 * </ol>
 * 
 * @see junit.framework.TestCase#setUp()
 */
@Override
protected void setUp() throws Exception {

    super.setUp();

    Class.forName(DRIVER_NAME).newInstance();
    // shutdown embedded Derby engine (if running)
    // using SHUTDOWN_URL *always* results in SQLException, so we catch and ignore ...
    try {
        DriverManager.getConnection(SHUTDOWN_URL);
    } catch (final SQLException e) {
        _log.info("Testcase Setup: Shutting down Derby Engine");

    }

    // delete existing db files
    final File dbDirectory = new File(DB_NAME);
    if (FileUtils.deleteQuietly(dbDirectory)) {
        _log.info("Deleted DB files of [" + DB_NAME + "] database");
    } else {
        _log.warn("Could not delete DB files of [" + DB_NAME + "] database");
    }

    Class.forName(DRIVER_NAME).newInstance();
    final Properties p = System.getProperties();

    // we want to see all sql in the db log file
    p.put("derby.language.logStatementText", "true");
    // we don't want the logfile to be recreated each time the engine starts ...
    p.put("derby.infolog.append", "true");
    Connection connection = DriverManager.getConnection(CONNECTION_URL);

    final ArrayList<Statement> statements = new ArrayList<Statement>(); // list of Statements,
    // PreparedStatements
    PreparedStatement psInsert = null;
    Statement createStatement = null;

    createStatement = connection.createStatement();
    statements.add(createStatement);

    // create a person table...
    createStatement
            .execute("CREATE TABLE person(id int, vorname varchar(40), name varchar(40), strasse varchar(40), "
                    + "plz varchar(5), ort varchar(40), festnetz varchar(20), body_mass_index double, vacationdays "
                    + "integer, birthday date, scheduled_for_downsizing smallint, downsized timestamp, photo blob, cv clob)");
    _log.info("Created TABLE [person]");

    // insert 100 records ...
    psInsert = connection
            .prepareStatement("INSERT INTO person VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?)");
    statements.add(psInsert);

    // prepare resource files for blob / clob insertion...
    final File resDir = new File(RES_FOLDER_PATH);
    final File photoFile = new File(resDir, PHOTO_FILE_NAME);
    final File cvFile = new File(resDir, CV_FILE_NAME);

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

        psInsert.setInt(COLUMN_ID, i);
        psInsert.setString(COLUMN_FIRSTNAME, "Mustervorname" + i);
        psInsert.setString(COLUMN_SURNAME, "Mustername" + i);
        psInsert.setString(COLUMN_STREET, "Musterstrasse " + i);
        psInsert.setString(COLUMN_PLZ, String.valueOf(getRandomInteger(DIGITS_IN_PLZ)));
        psInsert.setString(COLUMN_CITY, "Musterstadt" + i);
        psInsert.setString(COLUMN_PHONE,
                "0" + getRandomInteger(DIGITS_IN_AREA_CODE) + "-" + getRandomInteger(DIGITS_IN_EXTENSION));
        psInsert.setDouble(COLUMN_BMI, (Math.random() / Math.random()));
        psInsert.setLong(COLUMN_VACATIONDAYS, getRandomInteger(MAX_VACATIONDAYS));
        psInsert.setDate(COLUMN_BIRTHDAY, new Date(new java.util.Date().getTime()));
        psInsert.setBoolean(COLUMN_SCHEDULED_FOR_DOWNSIZING, ((getRandomInteger(1) % 2) == 0));
        psInsert.setDate(COLUMN_DOWNSIZED, new Date(new java.util.Date().getTime()));

        psInsert.setBytes(COLUMN_PHOTO, FileUtils.readFileToByteArray(photoFile));

        psInsert.setString(COLUMN_CV, FileUtils.readFileToString(cvFile));

        psInsert.execute();

    }

    // release all open resources to avoid unnecessary memory usage

    for (final Statement st : statements) {
        try {
            st.close();
        } catch (final SQLException sqle) {
            _log.error("Could not release Statement", sqle);
        }
    }
    statements.clear();

    // Connection
    try {
        if (connection != null) {
            connection.close();
            connection = null;
        }
    } catch (final SQLException sqle) {
        _log.error("Could not release Connection", sqle);
    }

    // shutdown Derby engine AGAIN, so the Crawler can start it up as it would normally
    try {
        DriverManager.getConnection(SHUTDOWN_URL);
    } catch (final SQLException e) {
        _log.info("Testcase Setup: Shutting down Derby Engine");
    }

    _crawler = new JdbcCrawler();

}

From source file:org.parosproxy.paros.db.paros.ParosTableHistory.java

@Override
public RecordHistory getHistoryCache(HistoryReference ref, HttpMessage reqMsg)
        throws DatabaseException, HttpMalformedHeaderException {
    try {//  w  ww. j a v  a  2s  .c om
        //  get the cache from provided reference.
        //  naturally, the obtained cache should be AFTER AND NEARBY to the given reference.
        //  - historyId up to historyId+200
        //  - match sessionId
        //  - history type can be MANUEL or hidden (hidden is used by images not explicitly stored in history)
        //  - match URI
        PreparedStatement psReadCache = null;

        if (isExistStatusCode) {
            //          psReadCache = getConnection().prepareStatement("SELECT TOP 1 * FROM HISTORY WHERE URI = ? AND METHOD = ? AND REQBODY = ? AND " + HISTORYID + " >= ? AND " + HISTORYID + " <= ? AND SESSIONID = ? AND (HISTTYPE = " + HistoryReference.TYPE_MANUAL + " OR HISTTYPE = " + HistoryReference.TYPE_HIDDEN + ") AND STATUSCODE != 304");
            psReadCache = getConnection().prepareStatement(
                    "SELECT TOP 1 * FROM HISTORY WHERE URI = ? AND METHOD = ? AND REQBODY = ? AND " + HISTORYID
                            + " >= ? AND " + HISTORYID + " <= ? AND SESSIONID = ? AND STATUSCODE != 304");

        } else {
            //          psReadCache = getConnection().prepareStatement("SELECT * FROM HISTORY WHERE URI = ? AND METHOD = ? AND REQBODY = ? AND " + HISTORYID + " >= ? AND " + HISTORYID + " <= ? AND SESSIONID = ? AND (HISTTYPE = " + HistoryReference.TYPE_MANUAL + " OR HISTTYPE = " + HistoryReference.TYPE_HIDDEN + ")");
            psReadCache = getConnection()
                    .prepareStatement("SELECT * FROM HISTORY WHERE URI = ? AND METHOD = ? AND REQBODY = ? AND "
                            + HISTORYID + " >= ? AND " + HISTORYID + " <= ? AND SESSIONID = ?)");

        }
        psReadCache.setString(1, reqMsg.getRequestHeader().getURI().toString());
        psReadCache.setString(2, reqMsg.getRequestHeader().getMethod());

        if (bodiesAsBytes) {
            psReadCache.setBytes(3, reqMsg.getRequestBody().getBytes());
        } else {
            psReadCache.setString(3, new String(reqMsg.getRequestBody().getBytes()));
        }

        psReadCache.setInt(4, ref.getHistoryId());
        psReadCache.setInt(5, ref.getHistoryId() + 200);
        psReadCache.setLong(6, ref.getSessionId());

        ResultSet rs = psReadCache.executeQuery();
        RecordHistory rec = null;

        try {
            do {
                rec = build(rs);
                // for retrieval from cache, the message requests nature must be the same.
                // and the result should NOT be NOT_MODIFIED for rendering by browser
                if (rec != null && rec.getHttpMessage().equals(reqMsg) && rec.getHttpMessage()
                        .getResponseHeader().getStatusCode() != HttpStatusCode.NOT_MODIFIED) {
                    return rec;
                }

            } while (rec != null);

        } finally {
            try {
                rs.close();
                psReadCache.close();
            } catch (Exception e) {
                // ZAP: Log exceptions
                log.warn(e.getMessage(), e);
            }
        }

        // if cache not exist, probably due to NOT_MODIFIED,
        // lookup from cache BEFORE the given reference

        if (isExistStatusCode) {
            //            psReadCache = getConnection().prepareStatement("SELECT TOP 1 * FROM HISTORY WHERE URI = ? AND METHOD = ? AND REQBODY = ? AND SESSIONID = ? AND STATUSCODE != 304 AND (HISTTYPE = " + HistoryReference.TYPE_MANUAL + " OR HISTTYPE = " + HistoryReference.TYPE_HIDDEN  + ")");
            psReadCache = getConnection().prepareStatement(
                    "SELECT TOP 1 * FROM HISTORY WHERE URI = ? AND METHOD = ? AND REQBODY = ? AND SESSIONID = ? AND STATUSCODE != 304");

        } else {
            //            psReadCache = getConnection().prepareStatement("SELECT * FROM HISTORY WHERE URI = ? AND METHOD = ? AND REQBODY = ? AND SESSIONID = ? AND (HISTTYPE = " + HistoryReference.TYPE_MANUAL + " OR HISTTYPE = " + HistoryReference.TYPE_HIDDEN  + ")");
            psReadCache = getConnection().prepareStatement(
                    "SELECT * FROM HISTORY WHERE URI = ? AND METHOD = ? AND REQBODY = ? AND SESSIONID = ?");

        }
        psReadCache.setString(1, reqMsg.getRequestHeader().getURI().toString());
        psReadCache.setString(2, reqMsg.getRequestHeader().getMethod());

        if (bodiesAsBytes) {
            psReadCache.setBytes(3, reqMsg.getRequestBody().getBytes());
        } else {
            psReadCache.setString(3, new String(reqMsg.getRequestBody().getBytes()));
        }

        psReadCache.setLong(4, ref.getSessionId());

        rs = psReadCache.executeQuery();
        rec = null;

        try {
            do {
                rec = build(rs);
                if (rec != null && rec.getHttpMessage().equals(reqMsg) && rec.getHttpMessage()
                        .getResponseHeader().getStatusCode() != HttpStatusCode.NOT_MODIFIED) {
                    return rec;
                }

            } while (rec != null);

        } finally {
            try {
                rs.close();
                psReadCache.close();
            } catch (Exception e) {
                // ZAP: Log exceptions
                log.warn(e.getMessage(), e);
            }

        }

        return null;
    } catch (SQLException e) {
        throw new DatabaseException(e);
    }
}

From source file:dao.PersonalinfoAddQuery.java

public void run(Connection conn, String[] params) throws BaseDaoException {

    /*  public void run(Connection conn, String dob, String title, 
           String ihave, String iwant, String industry, String company, 
           String pwebsite, String cwebsite, String blogsite, String education, 
           String city, String state, String country, 
           String desc, String interests, String zipcode, 
           int gender, String nickname, String designation, 
      String bcity, String bstate, String bcountry, String bzipcode, 
           String hphone, String cphone, String bphone, 
           String yim, String aim, String msn, String icq, String loginid,
           String pLabel, String dLabel) throws BaseDaoException {
    *///from ww w  . j av  a 2  s  .c om

    /****  use UTC_TIMESTAMP after upgrading mysql **/
    // default date

    PreparedStatement query = null;

    String dob = params[0];
    String title = params[1];
    String ihave = params[2];
    String iwant = params[3];
    String industry = params[4];
    String company = params[5];
    String pwebsite = params[6];
    String cwebsite = params[7];
    String blogsite = params[8];
    String education = params[9];
    String city = params[10];
    String state = params[11];
    String country = params[12];
    String desc = params[13];
    String interests = params[14];
    String zipcode = params[15];
    String gender = params[16];
    String nickname = params[17];
    String designation = params[18];
    String bcity = params[19];
    String bstate = params[20];
    String bcountry = params[21];
    String bzipcode = params[22];
    String hphone = params[23];
    String cphone = params[24];
    String bphone = params[25];
    String yim = params[26];
    String aim = params[27];
    String msn = params[28];
    String icq = params[29];
    //String loginid = params[30];
    String pLabel = params[30];
    String dLabel = params[31];
    String street = params[32];
    String bstreet = params[33];
    String zone = params[34];

    if (RegexStrUtil.isNull(dob)) {
        dob = "1970-01-01";
    }

    byte[] mydesc = { ' ' };
    if (!RegexStrUtil.isNull(desc)) {
        mydesc = desc.getBytes();
    }

    String stmt = "insert into usertab values (" + "LAST_INSERT_ID()" + ", CURRENT_TIMESTAMP(), " + 0 + ", '"
            + dob + "', '" + title + "','" + ihave + "', '" + iwant + "', '" + industry + "', '" + company
            + "', '" + pwebsite + "', '" + cwebsite + "', '" + blogsite + "', '" + education + "', '" + street
            + "', '" + city + "', '" + state + "', '" + country + "',?, '" + interests + "', '" + zipcode
            + "', '" + designation + "', '" + gender + "', '" + nickname + "', '" + bstreet + "', '" + bcity
            + "', '" + bstate + "', '" + bcountry + "', '" + bzipcode + "', '" + hphone + "', '" + cphone
            + "', '" + bphone + "', '" + yim + "', '" + aim + "','" + msn + "', '" + icq + "', '" + pLabel
            + "', '" + dLabel + "', '" + zone + "')";
    try {
        query = conn.prepareStatement(stmt);
        query.setBytes(1, mydesc);
        query.executeUpdate();
    } catch (Exception e) {
        logger.warn("Error occured while executing PersonalinfoAddQuery," + stmt, e);
        throw new BaseDaoException("Error occured PersonalinfoAddQuery" + stmt, e);
    }
}

From source file:com.alfaariss.oa.engine.tgt.jdbc.JDBCTGTFactory.java

/**
 * Persist the TGT in the JDBC storage./*from   www. ja  va2  s .co m*/
 * @param tgt The TGT to persist.
 * @param bProcessEvent TRUE if event must be performed
 * @return the event that was or would be performed 
 * @throws PersistenceException If persistance.
 * @see IEntityManager#persist(IEntity)
 */
private TGTListenerEvent performPersist(JDBCTGT tgt, boolean bProcessEvent) throws PersistenceException {
    if (tgt == null)
        throw new IllegalArgumentException("Suplied tgt is empty or invalid");

    TGTListenerEvent listenerEvent = null;
    List<TGTEventError> listTGTEventErrors = null;

    Connection oConnection = null;
    PreparedStatement ps = null;

    String id = tgt.getId();
    try {
        oConnection = _oDataSource.getConnection();
        if (id == null) // New TGT
        {
            byte[] baId = new byte[ITGT.TGT_LENGTH];
            do {
                _random.nextBytes(baId);
                try {
                    id = ModifiedBase64.encode(baId);
                } catch (UnsupportedEncodingException e) {
                    _logger.error("Could not create tgt id for byte[]: " + baId, e);
                    throw new PersistenceException(SystemErrors.ERROR_INTERNAL);
                }
            } while (exists(id)); //Key allready exists 

            //Update expiration time and id
            tgt.setId(id);
            long expiration = System.currentTimeMillis() + _lExpiration;
            tgt.setTgtExpTime(expiration);

            try {
                //Create statement
                ps = oConnection.prepareStatement(_sInsertQuery);
                ps.setString(1, id);
                ps.setTimestamp(2, new Timestamp(expiration));
                ps.setBytes(3, Serialize.encode(tgt.getUser()));
                ps.setBytes(4, Serialize.encode(tgt.getAuthenticationProfile()));
                ps.setBytes(5, Serialize.encode(tgt.getModifiableAuthNProfileIDs()));
                ps.setBytes(6, Serialize.encode(tgt.getModifiableRequestorIDs()));
                ps.setBytes(7, Serialize.encode(tgt.getAttributes()));
                int i = ps.executeUpdate();
                _logger.debug(i + " New TGT(s) added:" + id);

                listenerEvent = TGTListenerEvent.ON_CREATE;
                if (bProcessEvent) {
                    try {
                        processEvent(listenerEvent, tgt);
                    } catch (TGTListenerException e) {
                        listTGTEventErrors = e.getErrors();
                    }
                }
            } catch (SQLException e) {
                _logger.error("Could not execute insert query: " + _sInsertQuery, e);
                throw new PersistenceException(SystemErrors.ERROR_RESOURCE_INSERT);
            }
        } else if (tgt.isExpired()) // Expired
        {
            _logger.debug("TGT Expired: " + id);

            IUser tgtUser = tgt.getUser();
            _eventLogger.info(new UserEventLogItem(null, tgt.getId(), null, UserEvent.TGT_EXPIRED,
                    tgtUser.getID(), tgtUser.getOrganization(), null, null, this, null));

            listenerEvent = TGTListenerEvent.ON_REMOVE;
            if (bProcessEvent) {
                try {
                    processEvent(listenerEvent, tgt);
                } catch (TGTListenerException e) {
                    listTGTEventErrors = e.getErrors();
                }
            }

            try {
                ps = oConnection.prepareStatement(_sRemoveQuery);
                ps.setString(1, id);
                int i = ps.executeUpdate();
                _logger.debug(i + " TGT removed: " + id);
            } catch (SQLException e) {
                _logger.error("Could not execute delete query: " + _sRemoveQuery, e);
                throw new PersistenceException(SystemErrors.ERROR_RESOURCE_REMOVE);
            }

            int iCountR = 0;
            if (_aliasStoreSP != null)
                iCountR = _aliasStoreSP.remove(oConnection, id);
            int iCountF = 0;
            if (_aliasStoreIDP != null)
                iCountF = _aliasStoreIDP.remove(oConnection, id);

            if (_logger.isDebugEnabled() && iCountR + iCountF > 0) {
                StringBuffer sbDebug = new StringBuffer("Removed '");
                sbDebug.append(iCountR);
                sbDebug.append("' (requestor based) aliasses and '");
                sbDebug.append(iCountF);
                sbDebug.append("' (remote enitity based) aliasses");
                _logger.debug(sbDebug.toString());
            }
        } else // Update
        {
            try {
                // Update expiration time
                long expiration = System.currentTimeMillis() + _lExpiration;
                tgt.setTgtExpTime(expiration);
                // Update tgt
                ps = oConnection.prepareStatement(_sUpdateQuery);
                ps.setTimestamp(1, new Timestamp(expiration));
                ps.setBytes(2, Serialize.encode(tgt.getUser()));
                ps.setBytes(3, Serialize.encode(tgt.getAuthenticationProfile()));
                ps.setBytes(4, Serialize.encode(tgt.getModifiableAuthNProfileIDs()));
                ps.setBytes(5, Serialize.encode(tgt.getModifiableRequestorIDs()));
                ps.setBytes(6, Serialize.encode(tgt.getAttributes()));
                ps.setString(7, id);
                int i = ps.executeUpdate();
                _logger.debug(i + " TGT updated:" + id);

                listenerEvent = TGTListenerEvent.ON_UPDATE;
                if (bProcessEvent) {
                    try {
                        processEvent(listenerEvent, tgt);
                    } catch (TGTListenerException e) {
                        listTGTEventErrors = e.getErrors();
                    }
                }
            } catch (SQLException e) {
                _logger.error("Could not execute update query: " + _sUpdateQuery, e);
                throw new PersistenceException(SystemErrors.ERROR_RESOURCE_UPDATE);
            }
        }

        if (listTGTEventErrors != null) {//TGT Event processing failed, error has been logged already
            throw new TGTListenerException(listTGTEventErrors);
        }
    } catch (PersistenceException e) {
        throw e;
    } catch (Exception e) {
        _logger.error("Internal error during persist of tgt with id: " + id, e);
        throw new PersistenceException(SystemErrors.ERROR_RESOURCE_UPDATE);
    } finally {
        try {
            if (ps != null)
                ps.close();
        } catch (SQLException e) {
            _logger.error("Could not close statement", e);
        }
        try {
            if (oConnection != null)
                oConnection.close();
        } catch (SQLException e) {
            _logger.error("Could not close connection", e);
        }
    }

    return listenerEvent;
}

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

/**
 * Sets the value of this blob as the specified bytes. This method works the
 * same as the Blob.setBytes(long pos, byte[], int offset, int length) as
 * specified in the JDBC 3.0 API. Because of this, the first element in the
 * bytes to write to is actually element 1 (as opposed to the standard array
 * treatment where the first element is at position 0).
 * /*from w  ww .j a  va 2  s  . c om*/
 * @param table
 * @param columnName
 * @param id
 * @param pos
 * @param bytes
 * @param offset
 * @param length
 * @return
 */
public int setBlobAsBytes(String table, String columnName, String id, long pos, byte[] bytes, int offset,
        int length) {
    Connection conn = Database.getConnection();
    Statement statement = null;
    ResultSet resultSet = null;
    int written = 0;
    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);

        // null check
        if (resultBytes == null) {
            // because this method is used to place byte in specific positions, it
            // wouldn't
            // make sense to insert the bytes into a null columnName as it defeats
            // the
            // purpose of
            // this method. Just return a write count of 0 and don't do anything
            // else.
            return written;
        } else {
            // modify the blob
            written = length;
            byte[] setBytes = null;

            pos = pos - 1; // subtract one to use positioning like a normal array

            // check to see if the bytes will run longer than the current length of
            // the blob length.
            if ((pos + length) > resultBytes.length) {
                byte[] temp = new byte[(int) (pos + length)];
                // get the old bytes, up until pos
                for (int i = 0; i < pos; i++) {
                    temp[i] = resultBytes[i];
                }

                // set the new bytes
                for (int i = 0; i < length; i++) {
                    temp[(int) pos] = bytes[offset];
                    offset++;
                    pos++;
                    written++;
                }
                setBytes = temp;
            } else {
                // set the new bytes
                for (int i = 0; i < length; i++) {
                    resultBytes[(int) pos] = bytes[offset];
                    offset++;
                    pos++;
                    written++;
                }
                setBytes = resultBytes;
            }

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

From source file:com.alfaariss.oa.engine.session.jdbc.JDBCSessionFactory.java

/**
 * Persist the session in the JDBC storage.
 * //from  w  ww  .j  ava 2  s . c o  m
 * The sessionstate is saved with its name.
 * @param session The session to persist.
 * @throws PersistenceException If persistance fails.
 * @see IEntityManager#persist(IEntity)
 */
public void persist(JDBCSession session) throws PersistenceException {
    if (session == null)
        throw new IllegalArgumentException("Suplied session is empty or invalid");

    Connection oConnection = null;
    PreparedStatement psInsert = null;
    PreparedStatement psDelete = null;
    PreparedStatement psUpdate = null;

    String id = session.getId();
    try {
        oConnection = _oDataSource.getConnection();
        if (id == null) // New Session
        {
            try {
                byte[] baId = new byte[ISession.ID_BYTE_LENGTH];
                do {
                    _random.nextBytes(baId);
                    try {
                        id = ModifiedBase64.encode(baId);
                    } catch (UnsupportedEncodingException e) {
                        _logger.error("Could not create id for byte[]: " + baId, e);
                        throw new PersistenceException(SystemErrors.ERROR_INTERNAL);
                    }
                } while (exists(id)); //Key allready exists  

                // Update expiration time and id
                long expiration = System.currentTimeMillis() + _lExpiration;
                session.setTgtExpTime(expiration);
                session.setId(id);

                //Create statement                  
                psInsert = oConnection.prepareStatement(_sInsertQuery);
                psInsert.setString(1, id);
                psInsert.setString(2, session.getTGTId());
                psInsert.setInt(3, session.getState().ordinal());
                psInsert.setString(4, session.getRequestorId());
                psInsert.setString(5, session.getProfileURL());
                psInsert.setBytes(6, Serialize.encode(session.getUser()));
                psInsert.setTimestamp(7, new Timestamp(expiration));
                psInsert.setBoolean(8, session.isForcedAuthentication());
                psInsert.setBoolean(9, session.isPassive());
                psInsert.setBytes(10, Serialize.encode(session.getAttributes()));
                psInsert.setString(11, session.getForcedUserID());
                psInsert.setBytes(12, Serialize.encode(session.getLocale()));
                psInsert.setBytes(13, Serialize.encode(session.getSelectedAuthNProfile()));
                psInsert.setBytes(14, Serialize.encode(session.getAuthNProfiles()));

                int i = psInsert.executeUpdate();
                _logger.info(i + " new session(s) added: " + id + " for requestor '" + session.getRequestorId()
                        + "'");
            } catch (SQLException e) {
                _logger.error("Could not execute insert query: " + _sInsertQuery, e);
                throw new PersistenceException(SystemErrors.ERROR_RESOURCE_INSERT);
            }

        } else if (session.isExpired()) // Expired
        {
            try {
                _logger.info("Session Expired: " + id);

                _eventLogger.info(new UserEventLogItem(session, null, UserEvent.SESSION_EXPIRED, this, null));

                psDelete = oConnection.prepareStatement(_sRemoveQuery);
                psDelete.setString(1, id);
                int i = psDelete.executeUpdate();
                _logger.debug(i + " session(s) removed: " + id);
            } catch (SQLException e) {
                _logger.error("Could not execute delete query: " + _sRemoveQuery, e);
                throw new PersistenceException(SystemErrors.ERROR_RESOURCE_REMOVE);
            }
        } else // Update
        {
            try {
                // Update expiration time
                long expiration = System.currentTimeMillis() + _lExpiration;
                session.setExpTime(expiration);
                psUpdate = oConnection.prepareStatement(_sUpdateQuery);
                psUpdate.setString(1, session.getTGTId());
                psUpdate.setInt(2, session.getState().ordinal());
                psUpdate.setString(3, session.getRequestorId());
                psUpdate.setString(4, session.getProfileURL());
                psUpdate.setBytes(5, Serialize.encode(session.getUser()));
                psUpdate.setTimestamp(6, new Timestamp(expiration));
                psUpdate.setBoolean(7, session.isForcedAuthentication());
                psUpdate.setBoolean(8, session.isPassive());
                psUpdate.setBytes(9, Serialize.encode(session.getAttributes()));
                psUpdate.setString(10, session.getForcedUserID());
                psUpdate.setBytes(11, Serialize.encode(session.getLocale()));
                psUpdate.setBytes(12, Serialize.encode(session.getSelectedAuthNProfile()));
                psUpdate.setBytes(13, Serialize.encode(session.getAuthNProfiles()));
                psUpdate.setString(14, id);

                int i = psUpdate.executeUpdate();
                _logger.info(
                        i + " session(s) updated: " + id + " for requestor '" + session.getRequestorId() + "'");
            } catch (SQLException e) {
                _logger.error("Could not execute update query: " + _sUpdateQuery, e);
                throw new PersistenceException(SystemErrors.ERROR_RESOURCE_UPDATE);
            }
        }
    } catch (PersistenceException e) {
        throw e;
    } catch (Exception e) {
        _logger.error("Internal error during persist of session with id: " + id, e);
        throw new PersistenceException(SystemErrors.ERROR_RESOURCE_UPDATE);
    } finally {
        try {
            if (psInsert != null)
                psInsert.close();
        } catch (SQLException e) {
            _logger.debug("Could not close insert statement", e);
        }
        try {
            if (psDelete != null)
                psDelete.close();
        } catch (SQLException e) {
            _logger.debug("Could not close delete statement", e);
        }
        try {
            if (psUpdate != null)
                psUpdate.close();
        } catch (SQLException e) {
            _logger.debug("Could not close update statement", e);
        }
        try {
            if (oConnection != null)
                oConnection.close();
        } catch (SQLException e) {
            _logger.debug("Could not close connection", e);
        }
    }
}