List of usage examples for java.sql PreparedStatement isClosed
boolean isClosed() throws SQLException;
Statement object has been closed. From source file:com.streamsets.pipeline.stage.origin.jdbc.cdc.oracle.OracleCDCSource.java
private void generateRecords(Offset startingOffset, PreparedStatement selectChanges) { // When this is called the first time, Logminer was started either from SCN or from a start date, so we just keep // track of the start date etc. LOG.info("Attempting to generate records"); boolean error; StringBuilder query = new StringBuilder(); BigDecimal lastCommitSCN = new BigDecimal(startingOffset.scn); int sequenceNumber = startingOffset.sequence; LocalDateTime startTime = adjustStartTime(startingOffset.timestamp); String lastTxnId = startingOffset.txnId; LocalDateTime endTime = getEndTimeForStartTime(startTime); ResultSet resultSet = null;/*from w ww . j av a 2 s. c o m*/ while (!getContext().isStopped()) { error = false; generationStarted = true; try { recordQueue.put(new RecordOffset(dummyRecord, new Offset(version, startTime, lastCommitSCN.toPlainString(), sequenceNumber, lastTxnId))); selectChanges = getSelectChangesStatement(); if (!useLocalBuffering) { selectChanges.setBigDecimal(1, lastCommitSCN); selectChanges.setInt(2, sequenceNumber); selectChanges.setBigDecimal(3, lastCommitSCN); if (shouldTrackDDL) { selectChanges.setBigDecimal(4, lastCommitSCN); } } selectChanges.setFetchSize(configBean.jdbcFetchSize); resultSet = selectChanges.executeQuery(); while (resultSet.next() && !getContext().isStopped()) { String queryFragment = resultSet.getString(5); BigDecimal scnDecimal = resultSet.getBigDecimal(1); String scn = scnDecimal.toPlainString(); String xidUsn = String.valueOf(resultSet.getLong(10)); String xidSlt = String.valueOf(resultSet.getString(11)); String xidSqn = String.valueOf(resultSet.getString(12)); String xid = xidUsn + "." + xidSlt + "." + xidSqn; // Query Fragment is not null -> we need to process // Query Fragment is null AND the query string buffered from previous rows due to CSF == 0 is null, // nothing to do, go to next row // Query Fragment is null, but there is previously buffered data in the query, go ahead and process. if (queryFragment != null) { query.append(queryFragment); } else if (queryFragment == null && query.length() == 0) { LOG.debug(READ_NULL_QUERY_FROM_ORACLE, scn, xid); continue; } // CSF is 1 if the query is incomplete, so read the next row before parsing // CSF being 0 means query is complete, generate the record if (resultSet.getInt(9) == 0) { if (query.length() == 0) { LOG.debug(READ_NULL_QUERY_FROM_ORACLE, scn, xid); continue; } String queryString = query.toString(); query.setLength(0); String username = resultSet.getString(2); short op = resultSet.getShort(3); String timestamp = resultSet.getString(4); LocalDateTime tsDate = Timestamp.valueOf(timestamp).toLocalDateTime(); delay.getValue().put("delay", getDelay(tsDate)); String table = resultSet.getString(6); BigDecimal commitSCN = resultSet.getBigDecimal(7); int seq = resultSet.getInt(8); String rsId = resultSet.getString(13); Object ssn = resultSet.getObject(14); String schema = String.valueOf(resultSet.getString(15)); int rollback = resultSet.getInt(16); String rowId = resultSet.getString(17); SchemaAndTable schemaAndTable = new SchemaAndTable(schema, table); TransactionIdKey key = new TransactionIdKey(xid); bufferedRecordsLock.lock(); try { if (useLocalBuffering && bufferedRecords.containsKey(key) && bufferedRecords.get(key) .contains(new RecordSequence(null, null, 0, 0, rsId, ssn, null))) { continue; } } finally { bufferedRecordsLock.unlock(); } Offset offset = null; if (LOG.isDebugEnabled()) { LOG.debug( "Commit SCN = {}, SCN = {}, Operation = {}, Txn Id = {}, Timestamp = {}, Row Id = {}, Redo SQL = {}", commitSCN, scn, op, xid, tsDate, rowId, queryString); } if (op != DDL_CODE && op != COMMIT_CODE && op != ROLLBACK_CODE) { if (!useLocalBuffering) { offset = new Offset(version, tsDate, commitSCN.toPlainString(), seq, xid); } Map<String, String> attributes = new HashMap<>(); attributes.put(SCN, scn); attributes.put(USER, username); attributes.put(TIMESTAMP_HEADER, timestamp); attributes.put(TABLE, table); attributes.put(SEQ, String.valueOf(seq)); attributes.put(XID, xid); attributes.put(RS_ID, rsId); attributes.put(SSN, ssn.toString()); attributes.put(SCHEMA, schema); attributes.put(ROLLBACK, String.valueOf(rollback)); attributes.put(ROWID_KEY, rowId); if (!useLocalBuffering || getContext().isPreview()) { if (commitSCN.compareTo(lastCommitSCN) < 0 || (commitSCN.compareTo(lastCommitSCN) == 0 && seq < sequenceNumber)) { continue; } lastCommitSCN = commitSCN; sequenceNumber = seq; if (configBean.keepOriginalQuery) { attributes.put(QUERY_KEY, queryString); } try { Record record = generateRecord(queryString, attributes, op); if (record != null && record.getEscapedFieldPaths().size() > 0) { recordQueue.put(new RecordOffset(record, offset)); } } catch (UnparseableSQLException ex) { LOG.error("Parsing failed", ex); unparseable.offer(queryString); } } else { bufferedRecordsLock.lock(); try { HashQueue<RecordSequence> records = bufferedRecords.computeIfAbsent(key, x -> { x.setTxnStartTime(tsDate); return createTransactionBuffer(key.txnId); }); int nextSeq = records.isEmpty() ? 1 : records.tail().seq + 1; RecordSequence node = new RecordSequence(attributes, queryString, nextSeq, op, rsId, ssn, tsDate); records.add(node); } finally { bufferedRecordsLock.unlock(); } } } else if (!getContext().isPreview() && useLocalBuffering && (op == COMMIT_CODE || op == ROLLBACK_CODE)) { // so this commit was previously processed or it is a rollback, so don't care. if (op == ROLLBACK_CODE || scnDecimal.compareTo(lastCommitSCN) < 0) { bufferedRecordsLock.lock(); try { bufferedRecords.remove(key); } finally { bufferedRecordsLock.unlock(); } } else { bufferedRecordsLock.lock(); try { HashQueue<RecordSequence> records = bufferedRecords.getOrDefault(key, EMPTY_LINKED_HASHSET); if (lastCommitSCN.equals(scnDecimal) && xid.equals(lastTxnId)) { removeProcessedRecords(records, sequenceNumber); } int bufferedRecordsToBeRemoved = records.size(); LOG.debug(FOUND_RECORDS_IN_TRANSACTION, bufferedRecordsToBeRemoved, xid); lastCommitSCN = scnDecimal; lastTxnId = xid; sequenceNumber = addRecordsToQueue(tsDate, scn, xid); } finally { bufferedRecordsLock.unlock(); } } } else { offset = new Offset(version, tsDate, scn, 0, xid); boolean sendSchema = false; // Commit/rollback in Preview will also end up here, so don't really do any of the following in preview // Don't bother with DDL events here. if (!getContext().isPreview()) { // Event is sent on every DDL, but schema is not always sent. // Schema sending logic: // CREATE/ALTER: Schema is sent if the schema after the ALTER is newer than the cached schema // (which we would have sent as an event earlier, at the last alter) // DROP/TRUNCATE: Schema is not sent, since they don't change schema. DDL_EVENT type = getDdlType(queryString); if (type == DDL_EVENT.ALTER || type == DDL_EVENT.CREATE) { sendSchema = refreshSchema(scnDecimal, new SchemaAndTable(schema, table)); } recordQueue.put(new RecordOffset(createEventRecord(type, queryString, schemaAndTable, offset.toString(), sendSchema, timestamp), offset)); } } query.setLength(0); } } } catch (SQLException ex) { error = true; // force a restart from the same timestamp. if (ex.getErrorCode() == MISSING_LOG_FILE) { LOG.warn("SQL Exception while retrieving records", ex); addToStageExceptionsQueue(new StageException(JDBC_86, ex)); } else if (ex.getErrorCode() != RESULTSET_CLOSED_AS_LOGMINER_SESSION_CLOSED) { LOG.warn("SQL Exception while retrieving records", ex); } else if (ex.getErrorCode() == QUERY_TIMEOUT) { LOG.warn("LogMiner select query timed out"); } else if (ex.getErrorCode() == LOGMINER_START_MUST_BE_CALLED) { LOG.warn("Last LogMiner session did not start successfully. Will retry", ex); } else { LOG.error("Error while reading data", ex); addToStageExceptionsQueue(new StageException(JDBC_52, ex)); } } catch (StageException e) { LOG.error("Error while reading data", e); error = true; addToStageExceptionsQueue(e); } catch (InterruptedException ex) { LOG.error("Interrupted while waiting to add data"); Thread.currentThread().interrupt(); } catch (Exception ex) { LOG.error("Error while reading data", ex); error = true; addToStageExceptionsQueue(new StageException(JDBC_52, ex)); } finally { // If an incomplete batch is seen, it means we are going to move the window forward // Ending this session and starting a new one helps reduce PGA memory usage. try { if (resultSet != null && !resultSet.isClosed()) { resultSet.close(); } if (selectChanges != null && !selectChanges.isClosed()) { selectChanges.close(); } } catch (SQLException ex) { LOG.warn("Error while attempting to close SQL statements", ex); } try { endLogMnr.execute(); } catch (SQLException ex) { LOG.warn("Error while trying to close logminer session", ex); } try { if (error) { resetConnectionsQuietly(); } else { discardOldUncommitted(startTime); startTime = adjustStartTime(endTime); endTime = getEndTimeForStartTime(startTime); } startLogMinerUsingGivenDates(startTime.format(dateTimeColumnHandler.dateFormatter), endTime.format(dateTimeColumnHandler.dateFormatter)); } catch (SQLException ex) { LOG.error("Error while attempting to start LogMiner", ex); addToStageExceptionsQueue(new StageException(JDBC_52, ex)); } catch (StageException ex) { LOG.error("Error while attempting to start logminer for redo log dictionary", ex); addToStageExceptionsQueue(ex); } } } }
From source file:com.cmart.DB.CassandraDBQuery.java
public void populateDB(Connection conn) { boolean createSpace = true; boolean truncateData = false; boolean doUsers = false; boolean doAddresses = false; boolean doItems = false; boolean doOldItems = false; boolean doImages = false; boolean doPurchases = false; boolean doPayments = false; boolean doBids = false; boolean doOldBids = false; boolean doCategories = false; boolean doComments = false; boolean doQuestions = false; boolean doAccounts = false; boolean doStates = false; boolean makeAll = false; /*try {/*from w ww . ja v a 2 s .c o m*/ Class.forName(DRIVER); conn = DriverManager.getConnection(URL); } catch (Exception e1) { // TODO Auto-generated catch block e1.printStackTrace(); }*/ PreparedStatement statement = null; ResultSet rs = null; String CQL = null; if (createSpace) { try { URL = URL.substring(0, URL.indexOf("CMARTv1") - 1); if (conn == null) conn = this.getConnection(); PreparedStatement statementpre = conn.prepareStatement("DROP KEYSPACE CMARTv1;"); statementpre.executeUpdate(); Thread.sleep(2000); // may have to wait to agree statementpre.close(); } catch (Exception e) { e.printStackTrace(); } try { PreparedStatement statementpre = conn.prepareStatement( "create keyspace CMARTv1 WITH gc_grace_seconds=30 AND strategy_options:replication_factor=3 AND strategy_class = 'SimpleStrategy' AND durable_writes=false;"); //statementpre = conn.prepareStatement("create keyspace CMARTv11 WITH gc_grace_seconds=30 AND strategy_options:DC1 = '2' AND replication_factor = '2' AND strategy_class = 'NetworkTopologyStrategy';"); statementpre.executeUpdate(); Thread.sleep(2000); statementpre.close(); } catch (Exception e) { e.printStackTrace(); } finally { this.forceCloseConnection(conn); URL = URL + "/CMARTv1"; System.out.println(URL); conn = null; } } if (conn == null) conn = this.getConnection(); if (truncateData) { for (int i = 0; i < 5; i++) { try { conn.close(); } catch (SQLException e2) { // TODO Auto-generated catch block e2.printStackTrace(); } try { Class.forName(DRIVER); conn = DriverManager.getConnection(URL); } catch (Exception e1) { // TODO Auto-generated catch block e1.printStackTrace(); } try { statement = conn.prepareStatement("TRUNCATE items"); statement.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } try { statement = conn.prepareStatement("TRUNCATE olditems"); statement.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } try { statement = conn.prepareStatement("TRUNCATE purchased"); statement.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } try { statement = conn.prepareStatement("TRUNCATE users"); statement.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } try { statement = conn.prepareStatement("TRUNCATE addresses"); statement.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } try { statement = conn.prepareStatement("TRUNCATE bids"); statement.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } try { statement = conn.prepareStatement("TRUNCATE olditems"); statement.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } try { statement = conn.prepareStatement("TRUNCATE maxbids"); statement.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } try { statement = conn.prepareStatement("TRUNCATE maxoldbids"); statement.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } try { statement = conn.prepareStatement("TRUNCATE comments"); statement.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } try { statement = conn.prepareStatement("TRUNCATE images"); statement.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } try { statement = conn.prepareStatement("TRUNCATE questions"); statement.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } try { statement = conn.prepareStatement("TRUNCATE payments"); statement.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } try { statement = conn.prepareStatement("TRUNCATE priceitems"); statement.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } try { statement = conn.prepareStatement("TRUNCATE revpriceitems"); statement.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } try { statement = conn.prepareStatement("TRUNCATE revtimeitems"); statement.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } System.out.println("success"); } } /* * Create the users col set */ if (doUsers || makeAll) { System.out.println("Dropping users"); try { CQL = "DROP COLUMNFAMILY users;"; statement = conn.prepareStatement(CQL); statement.executeUpdate(); System.out.println("Dropping users successful"); } catch (Exception e) { System.err.println("CassandraQuery: INIT no columnfamily users."); e.printStackTrace(); } System.out.println("Adding users"); try { CQL = "CREATE COLUMNFAMILY users (KEY bigint PRIMARY KEY, userid bigint, username text, password text, username_password text, authtoken text, firstname text,lastname text, email text, rating bigint) ;"; statement = conn.prepareStatement(CQL); statement.executeUpdate(); // Used to confirm username and password CQL = "CREATE INDEX users_username_password_idx ON users (username_password); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); // Used to check authtoken CQL = "CREATE INDEX users_userid_idx ON users (userid); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); // Used to check if username exists CQL = "CREATE INDEX users_username_idx ON users (username); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); // Used to check if email exists CQL = "CREATE INDEX users_email_idx ON users (email); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); // ?? CQL = "CREATE INDEX users_authtoken_idx ON users (authtoken); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); System.out.println("Adding users successful"); } catch (Exception e) { e.printStackTrace(); } } /* * Creating the addresses col set */ if (doAddresses || makeAll) { System.out.println("Dropping addresses"); try { CQL = "DROP COLUMNFAMILY addresses;"; statement = conn.prepareStatement(CQL); statement.executeUpdate(); System.out.println("Dropping addresses successful"); } catch (Exception e) { System.err.println("CassandraQuery: INIT no columnfamily addresses."); e.printStackTrace(); } System.out.println("Adding addresses"); try { CQL = "CREATE COLUMNFAMILY addresses (KEY bigint PRIMARY KEY, userid bigint, street text, town text, zip text, state text, isdefault boolean, isDefaultKey bigint);"; statement = conn.prepareStatement(CQL); statement.execute(); // Used to get the default address CQL = "CREATE INDEX address_default_idx ON addresses (isDefaultKey); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); // Used to get all of a user's addresses CQL = "CREATE INDEX address_userid_idx ON addresses (userid); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); System.out.println("Adding addresses successful"); } catch (Exception e) { e.printStackTrace(); } } if (doItems || makeAll) { System.out.println("Dropping items"); try { CQL = "DROP COLUMNFAMILY items;"; statement = conn.prepareStatement(CQL); statement.executeUpdate(); CQL = "DROP COLUMNFAMILY revtimeitems;"; statement = conn.prepareStatement(CQL); statement.executeUpdate(); CQL = "DROP COLUMNFAMILY priceitems;"; statement = conn.prepareStatement(CQL); statement.executeUpdate(); CQL = "DROP COLUMNFAMILY revpriceitems;"; statement = conn.prepareStatement(CQL); statement.executeUpdate(); System.out.println("Dropping items successful"); } catch (Exception e) { System.err.println("CassandraQuery: INIT no columnfamily items."); e.printStackTrace(); } System.out.println("Adding items"); try { CQL = "CREATE COLUMNFAMILY items (KEY bigint PRIMARY KEY, itemid bigint, name text, description text, thumbnail text, sellerid bigint, categoryid bigint, currentwinner bigint, quantity bigint, noofbids bigint, startdate bigint, enddate bigint, curbid text, maxbid text, startprice text,reserveprice text,buynowprice text, pikey bigint, rpikey bigint, ts bigint) WITH gc_grace_seconds=15 ; "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); CQL = "CREATE COLUMNFAMILY revtimeitems (KEY bigint PRIMARY KEY, enddate bigint, catzero bigint, itemid bigint, categoryid bigint) WITH gc_grace_seconds=15 ; "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); CQL = "CREATE COLUMNFAMILY priceitems (KEY bigint PRIMARY KEY, pikey bigint, catzero bigint, itemid bigint, categoryid bigint) WITH gc_grace_seconds=15 ; "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); CQL = "CREATE COLUMNFAMILY revpriceitems (KEY bigint PRIMARY KEY, pikey bigint, catzero bigint, itemid bigint, categoryid bigint) WITH gc_grace_seconds=15 ; "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); // Used to get the items the user is selling CQL = "CREATE INDEX item_sellerid_idx ON items (sellerid); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); // Used to browse items CQL = "CREATE INDEX item_itemid_idx ON items (itemid); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); // Used to list items by categoryID CQL = "CREATE INDEX item_categoryid_idx ON items (categoryid); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); CQL = "CREATE INDEX revtimeitem_categoryid_idx ON revtimeitems (categoryid); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); CQL = "CREATE INDEX revtimeitem_categoryz_idx ON revtimeitems (catzero); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); CQL = "CREATE INDEX priceitem_categoryid_idx ON priceitems (categoryid); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); CQL = "CREATE INDEX revpriceitem_categoryid_idx ON revpriceitems (categoryid); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); CQL = "CREATE INDEX priceitem_categoryz_idx ON priceitems (catzero); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); CQL = "CREATE INDEX revpriceitem_categoryz_idx ON revpriceitems (catzero); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); System.out.println("Adding items successful"); } catch (Exception e) { e.printStackTrace(); } } if (doOldItems || makeAll) { System.out.println("Dropping old items"); try { CQL = "DROP COLUMNFAMILY olditems;"; statement = conn.prepareStatement(CQL); statement.executeUpdate(); System.out.println("Dropping old items successful"); } catch (Exception e) { System.err.println("CassandraQuery: INIT no columnfamily olditems."); e.printStackTrace(); } System.out.println("Adding old items"); try { CQL = "CREATE COLUMNFAMILY olditems (KEY bigint PRIMARY KEY, itemid bigint, name text, description text, thumbnail text, sellerid bigint, categoryid bigint, currentwinner bigint, quantity bigint, noofbids bigint, startdate bigint, enddate bigint, curbid text, maxbid text, startprice text,reserveprice text,buynowprice text, ts bigint); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); // Used to get the items the user has sold CQL = "CREATE INDEX olditem_sellerid_idx ON olditems (sellerid); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); // Used to browse items CQL = "CREATE INDEX olditem_itemid_idx ON olditems (itemid); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); System.out.println("Adding olditems successful"); } catch (Exception e) { e.printStackTrace(); } } if (doImages || makeAll) { System.out.println("Dropping images"); try { CQL = "DROP COLUMNFAMILY images;"; statement = conn.prepareStatement(CQL); statement.executeUpdate(); System.out.println("Dropping images successful"); } catch (Exception e) { System.err.println("CassandraQuery: INIT no columnfamily images."); e.printStackTrace(); } System.out.println("Adding images"); try { CQL = "CREATE COLUMNFAMILY images (KEY bigint PRIMARY KEY, URL text, description text, itemid bigint, position bigint) ; "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); // Used to get the items the user has sold CQL = "CREATE INDEX images_itemid_idx ON images (itemid); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); System.out.println("Adding images successful"); } catch (Exception e) { e.printStackTrace(); } } if (doPurchases || makeAll) { System.out.println("Dropping purchases"); try { CQL = "DROP COLUMNFAMILY purchased;"; statement = conn.prepareStatement(CQL); statement.executeUpdate(); System.out.println("Dropping images successful"); } catch (Exception e) { System.err.println("CassandraQuery: INIT no columnfamily purchases."); e.printStackTrace(); } System.out.println("Adding purchases"); try { CQL = "CREATE COLUMNFAMILY purchased (KEY bigint PRIMARY KEY, userid bigint, itemid bigint, purcashedquantity int, price text, purchasedate bigint, paid boolean, paiddate bigint, name text, description text, thumbnail text, sellerid bigint, categoryid bigint, currentwinner bigint, quantity bigint, noofbids bigint, startdate bigint, enddate bigint, curbid text, maxbid text, ts bigint); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); // Used to get the items a user has purchased CQL = "CREATE INDEX purchases_userid_idx ON purchased (userid); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); System.out.println("Adding purchases successful"); } catch (Exception e) { e.printStackTrace(); } } if (doPayments || makeAll) { System.out.println("Dropping payments"); try { CQL = "DROP COLUMNFAMILY payments;"; statement = conn.prepareStatement(CQL); statement.executeUpdate(); System.out.println("Dropping payments successful"); } catch (Exception e) { System.err.println("CassandraQuery: INIT no columnfamily payments."); e.printStackTrace(); } System.out.println("Adding payments"); try { CQL = "CREATE COLUMNFAMILY payments (KEY bigint PRIMARY KEY, userid bigint, itemid bigint, quantity int, price text, paiddate bigint, street text, town text, zip text, state text, nameoncard text, creditcardno text, cvv text, expirationdate bigint); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); // Used to get the items a user has purchased CQL = "CREATE INDEX payments_userid_idx ON payments (userid); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); System.out.println("Adding payments successful"); } catch (Exception e) { e.printStackTrace(); } } if (doBids || makeAll) { System.out.println("Dropping bids"); try { CQL = "DROP COLUMNFAMILY bids;"; statement = conn.prepareStatement(CQL); statement.executeUpdate(); System.out.println("Dropping bids successful"); } catch (Exception e) { System.err.println("CassandraQuery: INIT no columnfamily bids."); e.printStackTrace(); } try { CQL = "DROP COLUMNFAMILY maxbids;"; statement = conn.prepareStatement(CQL); statement.executeUpdate(); System.out.println("Dropping maxbids successful"); } catch (Exception e) { System.err.println("CassandraQuery: INIT no columnfamily maxbids."); e.printStackTrace(); } System.out.println("Adding bids"); try { CQL = "CREATE COLUMNFAMILY bids (KEY bigint PRIMARY KEY, userid bigint, itemid bigint, quantity int, bid text, maxbid text, biddate bigint) WITH gc_grace_seconds=15 ; "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); CQL = "CREATE COLUMNFAMILY maxbids (KEY text PRIMARY KEY, bidkey bigint, userid bigint, itemid bigint, quantity int, bid text, maxbid text, biddate bigint, ts bigint) WITH comparator = UTF8Type AND gc_grace_seconds=15 ; "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); // Used to get the items a user has bid on CQL = "CREATE INDEX maxbids_userid_idx ON maxbids (userid); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); // Used to get all max bids on an item when purchasing CQL = "CREATE INDEX maxbids_itemid_idx ON maxbids (itemid); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); // Used to get the items a user has bid on??? <-old CQL = "CREATE INDEX bids_userid_idx ON bids (userid); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); // Used to get all bids when purchasing item CQL = "CREATE INDEX bids_itemid_idx ON bids (itemid); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); System.out.println("Adding bids successful"); } catch (Exception e) { e.printStackTrace(); } } if (doOldBids || makeAll) { System.out.println("Dropping old bids"); try { CQL = "DROP COLUMNFAMILY oldbids;"; statement = conn.prepareStatement(CQL); statement.executeUpdate(); System.out.println("Dropping oldbids successful"); } catch (Exception e) { System.err.println("CassandraQuery: INIT no columnfamily oldbids."); e.printStackTrace(); } try { CQL = "DROP COLUMNFAMILY maxoldbids;"; statement = conn.prepareStatement(CQL); statement.executeUpdate(); System.out.println("Dropping maxoldbids successful"); } catch (Exception e) { System.err.println("CassandraQuery: INIT no columnfamily maxbids."); e.printStackTrace(); } System.out.println("Adding oldbids"); try { CQL = "CREATE COLUMNFAMILY oldbids (KEY bigint PRIMARY KEY, userid bigint, itemid bigint, quantity int, bid text, maxbid text, biddate bigint); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); CQL = "CREATE COLUMNFAMILY maxoldbids (KEY text PRIMARY KEY, bidkey bigint, userid bigint, itemid bigint, quantity int, bid text, maxbid text, biddate bigint, ts bigint) WITH comparator = UTF8Type; "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); // Used to get the items a user has bid on CQL = "CREATE INDEX maxoldbids_userid_idx ON maxoldbids (userid); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); // Used to get all max bids on an item CQL = "CREATE INDEX maxoldbids_itemid_idx ON maxoldbids (itemid); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); // Used to get the items a user has purchased CQL = "CREATE INDEX oldbids_userid_idx ON oldbids (userid); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); System.out.println("Adding oldbids successful"); } catch (Exception e) { e.printStackTrace(); } } if (doCategories || makeAll) { System.out.println("Dropping categories"); try { CQL = "DROP COLUMNFAMILY categories;"; statement = conn.prepareStatement(CQL); statement.executeUpdate(); System.out.println("Dropping categories successful"); } catch (Exception e) { System.err.println("CassandraQuery: INIT no columnfamily categories."); e.printStackTrace(); } System.out.println("Adding categories"); try { CQL = "CREATE COLUMNFAMILY categories (KEY bigint PRIMARY KEY,parent bigint, name text, ts bigint); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); // Used to get the items a user has purchased CQL = "CREATE INDEX categories_parent_idx ON categories (parent); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); System.out.println("Adding categories successful"); } catch (Exception e) { e.printStackTrace(); } } if (doComments || makeAll) { System.out.println("Dropping comments"); try { CQL = "DROP COLUMNFAMILY comments;"; statement = conn.prepareStatement(CQL); statement.executeUpdate(); System.out.println("Dropping comments successful"); } catch (Exception e) { System.err.println("CassandraQuery: INIT no columnfamily comments."); e.printStackTrace(); } System.out.println("Adding comments"); try { CQL = "CREATE COLUMNFAMILY comments (KEY bigint PRIMARY KEY,from_user_id bigint, to_user_id bigint, itemid bigint, rating bigint, date bigint, comment text); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); // Used to get the items a user has purchased CQL = "CREATE INDEX comments_itemid_idx ON comments (itemid); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); System.out.println("Adding comments successful"); } catch (Exception e) { e.printStackTrace(); } } if (doQuestions || makeAll) { System.out.println("Dropping questions"); try { CQL = "DROP COLUMNFAMILY questions;"; statement = conn.prepareStatement(CQL); statement.executeUpdate(); System.out.println("Dropping questions successful"); } catch (Exception e) { System.err.println("CassandraQuery: INIT no columnfamily questions."); e.printStackTrace(); } System.out.println("Adding questions"); try { CQL = "CREATE COLUMNFAMILY questions (KEY bigint PRIMARY KEY,from_user_id bigint, to_user_id bigint, itemid bigint, is_question boolean, date bigint, content text, responseTo bigint); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); // Used to get the items a user has purchased CQL = "CREATE INDEX questions_itemid_idx ON questions (itemid); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); System.out.println("Adding questions successful"); } catch (Exception e) { e.printStackTrace(); } } if (doAccounts || makeAll) { System.out.println("Dropping accounts"); try { CQL = "DROP COLUMNFAMILY accounts;"; statement = conn.prepareStatement(CQL); statement.executeUpdate(); System.out.println("Dropping accounts successful"); } catch (Exception e) { System.err.println("CassandraQuery: INIT no columnfamily accounts."); e.printStackTrace(); } System.out.println("Adding accounts"); try { CQL = "CREATE COLUMNFAMILY accounts (KEY bigint PRIMARY KEY,name text, nameoncard text, creditcardno text, cvv text, expirationdate bigint); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); System.out.println("Adding accounts successful"); } catch (Exception e) { e.printStackTrace(); } } if (doStates || makeAll) { System.out.println("Dropping states"); try { CQL = "DROP COLUMNFAMILY states;"; statement = conn.prepareStatement(CQL); statement.executeUpdate(); System.out.println("Dropping states successful"); } catch (Exception e) { System.err.println("CassandraQuery: INIT no columnfamily states."); e.printStackTrace(); } System.out.println("Adding states"); try { CQL = "CREATE COLUMNFAMILY states (KEY bigint PRIMARY KEY,shortname text, longname text); "; statement = conn.prepareStatement(CQL); statement.executeUpdate(); System.out.println("Adding states successful"); } catch (Exception e) { e.printStackTrace(); } } try { if (statement != null && !statement.isClosed()) statement.close(); conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
From source file:org.apache.hadoop.raid.DBUtils.java
public static void close(ResultSet generatedKeys, PreparedStatement[] pstmts, Connection conn) { if (generatedKeys != null) { try {/*from w ww . jav a2 s . co m*/ generatedKeys.close(); } catch (Exception e) { LOG.warn("Error to close ResultSet", e); } finally { try { if (!generatedKeys.isClosed()) { LOG.warn("ResultSet is not closed"); DBUtils.numDBOpenObjects++; } } catch (Exception ignore) { DBUtils.numDBOpenObjects++; } } } if (pstmts != null && pstmts.length > 0) { for (PreparedStatement pstmt : pstmts) { if (pstmt == null) { continue; } try { pstmt.close(); } catch (Exception e) { LOG.warn("Error to close PreparedStatement", e); } finally { try { if (!pstmt.isClosed()) { LOG.warn("PreparedStatement is not closed"); DBUtils.numDBOpenObjects++; } } catch (Exception ignore) { DBUtils.numDBOpenObjects++; } } } } if (conn != null) { try { conn.close(); } catch (Exception e) { LOG.warn("Error to close Connection", e); } finally { try { if (!conn.isClosed()) { LOG.warn("Connection is not closed"); DBUtils.numDBOpenObjects++; } } catch (Exception ignore) { DBUtils.numDBOpenObjects++; } } } }
From source file:org.apache.marmotta.kiwi.persistence.KiWiConnection.java
/** * Return the prepared statement with the given identifier; first looks in the statement cache and if it does * not exist there create a new statement. * * @param key the id of the statement in statements.properties * @return//from w w w .j av a2 s. c o m * @throws SQLException */ public PreparedStatement getPreparedStatement(String key) throws SQLException { requireJDBCConnection(); PreparedStatement statement = statementCache.get(key); if (statement == null || statement.isClosed()) { statement = connection.prepareStatement(dialect.getStatement(key), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); statementCache.put(key, statement); } statement.clearParameters(); if (persistence.getDialect().isCursorSupported()) { statement.setFetchSize(persistence.getConfiguration().getCursorSize()); } return statement; }
From source file:org.apache.marmotta.kiwi.persistence.KiWiConnection.java
/** * Return the prepared statement with the given identifier; first looks in the statement cache and if it does * not exist there create a new statement. This method is used for building statements with variable argument * numbers (e.g. in an IN).//from w w w. ja v a 2 s. c om * * @param key the id of the statement in statements.properties * @return * @throws SQLException */ public PreparedStatement getPreparedStatement(String key, int numberOfArguments) throws SQLException { requireJDBCConnection(); PreparedStatement statement = statementCache.get(key + numberOfArguments); if (statement == null || statement.isClosed()) { StringBuilder s = new StringBuilder(); for (int i = 0; i < numberOfArguments; i++) { if (i != 0) { s.append(','); } s.append('?'); } statement = connection.prepareStatement( String.format(dialect.getStatement(key), s.toString(), numberOfArguments), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); statementCache.put(key + numberOfArguments, statement); } statement.clearParameters(); if (persistence.getDialect().isCursorSupported()) { statement.setFetchSize(persistence.getConfiguration().getCursorSize()); } return statement; }
From source file:org.dspace.storage.rdbms.DatabaseUtils.java
/** * Clean the existing database, permanently removing all data and tables * <P>/* w w w. j a va2s . c om*/ * FlywayDB (http://flywaydb.org/) is used to clean the database * * @param flyway * Initialized Flyway object * @param dataSource * Initialized DataSource * @throws SQLException if database error * If database cannot be cleaned. */ private static synchronized void cleanDatabase(Flyway flyway, DataSource dataSource) throws SQLException { try { // First, run Flyway's clean command on database. // For MOST database types, this takes care of everything flyway.clean(); try (Connection connection = dataSource.getConnection()) { // Get info about which database type we are using String dbType = getDbType(connection); // If this is Oracle, the only way to entirely clean the database // is to also purge the "Recyclebin". See: // http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9018.htm if (dbType.equals(DBMS_ORACLE)) { PreparedStatement statement = null; try { statement = connection.prepareStatement("PURGE RECYCLEBIN"); statement.executeQuery(); } finally { if (statement != null && !statement.isClosed()) statement.close(); } } } } catch (FlywayException fe) { // If any FlywayException (Runtime) is thrown, change it to a SQLException throw new SQLException("Flyway clean error occurred", fe); } }
From source file:org.dspace.storage.rdbms.DatabaseUtils.java
public static boolean sequenceExists(Connection connection, String sequenceName) { boolean exists = false; PreparedStatement statement = null; ResultSet results = null;/* w w w . j a v a 2s .c om*/ // Whether or not to filter query based on schema (this is DB Type specific) boolean schemaFilter = false; try { // Get the name of the Schema that the DSpace Database is using // (That way we can search the right schema) String schema = getSchemaName(connection); // Canonicalize everything to the proper case based on DB type schema = canonicalize(connection, schema); sequenceName = canonicalize(connection, sequenceName); // Different database types store sequence information in different tables String dbtype = getDbType(connection); String sequenceSQL = null; switch (dbtype) { case DBMS_POSTGRES: // Default schema in PostgreSQL is "public" if (schema == null) { schema = "public"; } // PostgreSQL specific query for a sequence in a particular schema sequenceSQL = "SELECT COUNT(1) FROM pg_class, pg_namespace " + "WHERE pg_class.relnamespace=pg_namespace.oid " + "AND pg_class.relkind='S' " + "AND pg_class.relname=? " + "AND pg_namespace.nspname=?"; // We need to filter by schema in PostgreSQL schemaFilter = true; break; case DBMS_ORACLE: // Oracle specific query for a sequence owned by our current DSpace user // NOTE: No need to filter by schema for Oracle, as Schema = User sequenceSQL = "SELECT COUNT(1) FROM user_sequences WHERE sequence_name=?"; break; case DBMS_H2: // In H2, sequences are listed in the "information_schema.sequences" table // SEE: http://www.h2database.com/html/grammar.html#information_schema sequenceSQL = "SELECT COUNT(1) " + "FROM INFORMATION_SCHEMA.SEQUENCES " + "WHERE SEQUENCE_NAME = ?"; break; default: throw new SQLException("DBMS " + dbtype + " is unsupported."); } // If we have a SQL query to run for the sequence, then run it if (sequenceSQL != null) { // Run the query, passing it our parameters statement = connection.prepareStatement(sequenceSQL); statement.setString(1, sequenceName); if (schemaFilter) { statement.setString(2, schema); } results = statement.executeQuery(); // If results are non-zero, then this sequence exists! if (results != null && results.next() && results.getInt(1) > 0) { exists = true; } } } catch (SQLException e) { log.error("Error attempting to determine if sequence " + sequenceName + " exists", e); } finally { try { // Ensure statement gets closed if (statement != null && !statement.isClosed()) statement.close(); // Ensure ResultSet gets closed if (results != null && !results.isClosed()) results.close(); } catch (SQLException e) { // ignore it } } return exists; }
From source file:org.georchestra.urbanisme.RenseignUrbaBackend.java
/** * Get renseignement d'urbanisme for the given parcelle. * * @param parcelle Parcelle ID/*from w w w.j ava2s.c o m*/ * @return RenseignUrba instance containing the libelles * @throws SQLException */ public RenseignUrba getParcelle(String parcelle) throws SQLException { Connection connection = null; PreparedStatement queryLibellesByParcelle = null; List<String> libellesVal; libellesVal = new ArrayList<String>(); try { connection = this.basicDataSource.getConnection(); String query = "SELECT " + " libelle " + "FROM " + "( SELECT " + " ru.libelle AS libelle," + " theme.ventilation_ddc AS ventilation_ddc," + " ru.numero AS numero " + " FROM " + this.table + " AS ru " + "LEFT OUTER JOIN " + this.tableTheme + " AS theme " + "ON " + " ru.nom_theme = theme.nom " + "WHERE " + " id_parc = ?) AS libelles " + "LEFT JOIN (VALUES " + this.ordreTheme + ") AS ordre(code, priorite) " + "ON libelles.ventilation_ddc = ordre.code " + "ORDER BY ordre.priorite ASC, numero ASC ;"; queryLibellesByParcelle = connection.prepareStatement(query); queryLibellesByParcelle.setString(1, parcelle); ResultSet rs = queryLibellesByParcelle.executeQuery(); while (rs.next()) { String libelle = rs.getString("libelle"); libellesVal.add(libelle); } RenseignUrba renseign = new RenseignUrba(parcelle, libellesVal); return renseign; } finally { if ((queryLibellesByParcelle != null) && (!queryLibellesByParcelle.isClosed())) { queryLibellesByParcelle.close(); } if ((connection != null) && (!connection.isClosed())) { connection.close(); } } }
From source file:org.openbravo.service.system.SystemService.java
/** * This process deletes a client from the database. During its execution, the Scheduler is * stopped, and all sessions active for other users are cancelled * /*from w w w. j av a2 s . com*/ * @param client * The client to be deleted */ public void deleteClient(Client client) { try { long t1 = System.currentTimeMillis(); Platform platform = getPlatform(); Connection con = OBDal.getInstance().getConnection(); killConnectionsAndSafeMode(con); try { if (OBScheduler.getInstance() != null && OBScheduler.getInstance().getScheduler() != null && OBScheduler.getInstance().getScheduler().isStarted()) OBScheduler.getInstance().getScheduler().standby(); } catch (Exception e) { throw new RuntimeException("Could not shutdown scheduler", e); } OBDal.getInstance().getConnection().commit(); disableConstraints(platform); OBContext.setAdminMode(false); OBDal.getInstance().flush(); OBDal.getInstance().getConnection().commit(); String clientId = (String) DalUtil.getId(client); List<String> sqlCommands = new ArrayList<String>(); List<Entity> entities = ModelProvider.getInstance().getModel(); for (Entity entity : entities) { if ((entity.isClientEnabled() || entity.getName().equals("ADClient")) && !entity.isView() && !entity.isDataSourceBased() && !entity.isHQLBased() && !entity.isVirtualEntity()) { final String sql = "delete from " + entity.getTableName() + " where ad_client_id=?"; sqlCommands.add(sql); } } for (String command : sqlCommands) { PreparedStatement ps = null; try { ps = con.prepareStatement(command); ps.setString(1, clientId); ps.executeUpdate(); } finally { if (ps != null && !ps.isClosed()) { ps.close(); } } } PreparedStatement stpref = null; try { stpref = con.prepareStatement("DELETE FROM ad_preference p where visibleat_client_id=?"); stpref.setString(1, clientId); stpref.executeUpdate(); } finally { if (stpref != null && !stpref.isClosed()) { stpref.close(); } } PreparedStatement stpers = null; try { stpers = con .prepareStatement("DELETE FROM obuiapp_uipersonalization p where visibleat_client_id=?"); stpers.setString(1, clientId); stpers.executeUpdate(); } finally { if (stpers != null && !stpers.isClosed()) { stpers.close(); } } con.commit(); OBDal.getInstance().commitAndClose(); enableConstraints(platform); Connection con2 = platform.borrowConnection(); try { resetSafeMode(con2); } finally { platform.returnConnection(con2); } log4j.info("Deletion of client " + clientId + " took " + (System.currentTimeMillis() - t1) + " miliseconds"); } catch (Exception e) { log4j.error("exception when deleting the client: ", e); } finally { OBContext.restorePreviousMode(); // We restart the scheduler try { if (OBScheduler.getInstance() != null && OBScheduler.getInstance().getScheduler() != null) { OBScheduler.getInstance().getScheduler().start(); } } catch (SchedulerException e) { log4j.error("There was an error while restarting the scheduler", e); } } }
From source file:org.openbravo.service.system.SystemService.java
private void resetSafeMode(Connection con) { try {//from w ww. j a v a2 s . c om PreparedStatement ps2 = null; try { ps2 = con.prepareStatement("UPDATE AD_SYSTEM_INFO SET SYSTEM_STATUS='RB70'"); ps2.executeUpdate(); } finally { if (ps2 != null && !ps2.isClosed()) { ps2.close(); } } } catch (Exception e) { throw new RuntimeException("Couldn't reset the safe mode", e); } }