List of usage examples for java.sql PreparedStatement setBytes
void setBytes(int parameterIndex, byte x[]) throws SQLException;
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); } } }