List of usage examples for java.sql PreparedStatement addBatch
void addBatch() throws SQLException;
PreparedStatement object's batch of commands. From source file:org.apache.tajo.catalog.store.AbstractDBStore.java
@Override public void createTable(final CatalogProtos.TableDescProto table) throws UndefinedDatabaseException, DuplicateTableException { Connection conn = null;/*from ww w.j ava 2 s . c om*/ PreparedStatement pstmt = null; ResultSet res = null; final String[] splitted = IdentifierUtil.splitTableName(table.getTableName()); if (splitted.length == 1) { throw new TajoInternalError( "createTable() requires a qualified table name, but it is '" + table.getTableName() + "'"); } final String databaseName = splitted[0]; final String tableName = splitted[1]; if (existTable(databaseName, tableName)) { throw new DuplicateTableException(tableName); } final int dbid = getDatabaseId(databaseName); try { conn = getConnection(); conn.setAutoCommit(false); String sql = "INSERT INTO TABLES (DB_ID, " + COL_TABLES_NAME + ", TABLE_TYPE, PATH, DATA_FORMAT, HAS_SELF_DESCRIBE_SCHEMA) VALUES(?, ?, ?, ?, ?, ?) "; if (LOG.isDebugEnabled()) { LOG.debug(sql); } pstmt = conn.prepareStatement(sql); pstmt.setInt(1, dbid); pstmt.setString(2, tableName); if (table.getIsExternal()) { pstmt.setString(3, TableType.EXTERNAL.name()); } else { pstmt.setString(3, TableType.MANAGED.name()); } pstmt.setString(4, table.getPath()); pstmt.setString(5, table.getMeta().getDataFormat()); pstmt.setBoolean(6, table.getSchema() == null); pstmt.executeUpdate(); pstmt.close(); String tidSql = "SELECT TID from " + TB_TABLES + " WHERE " + COL_DATABASES_PK + "=? AND " + COL_TABLES_NAME + "=?"; pstmt = conn.prepareStatement(tidSql); pstmt.setInt(1, dbid); pstmt.setString(2, tableName); res = pstmt.executeQuery(); if (!res.next()) { throw new TajoInternalError("There is no TID matched to '" + table.getTableName() + '"'); } int tableId = res.getInt("TID"); res.close(); pstmt.close(); String colSql = "INSERT INTO " + TB_COLUMNS + // 1 2 3 4 " (TID, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE)" + " VALUES(?, ?, ?, ?) "; if (LOG.isDebugEnabled()) { LOG.debug(colSql); } pstmt = conn.prepareStatement(colSql); for (int i = 0; i < table.getSchema().getFieldsCount(); i++) { ColumnProto col = table.getSchema().getFields(i); org.apache.tajo.type.Type type = TypeProtobufEncoder.decode(col.getType()); pstmt.setInt(1, tableId); pstmt.setString(2, extractSimpleName(col.getName())); pstmt.setInt(3, i); pstmt.setString(4, TypeStringEncoder.encode(type)); pstmt.addBatch(); pstmt.clearParameters(); } pstmt.executeBatch(); pstmt.close(); if (table.getMeta().hasParams()) { String propSQL = "INSERT INTO " + TB_OPTIONS + "(TID, KEY_, VALUE_) VALUES(?, ?, ?)"; if (LOG.isDebugEnabled()) { LOG.debug(propSQL); } pstmt = conn.prepareStatement(propSQL); for (KeyValueProto entry : table.getMeta().getParams().getKeyvalList()) { pstmt.setInt(1, tableId); pstmt.setString(2, entry.getKey()); pstmt.setString(3, entry.getValue()); pstmt.addBatch(); pstmt.clearParameters(); } pstmt.executeBatch(); pstmt.close(); } if (table.hasStats()) { String statSql = "INSERT INTO " + TB_STATISTICS + " (TID, NUM_ROWS, NUM_BYTES) VALUES(?, ?, ?)"; if (LOG.isDebugEnabled()) { LOG.debug(statSql); } pstmt = conn.prepareStatement(statSql); pstmt.setInt(1, tableId); pstmt.setLong(2, table.getStats().getNumRows()); pstmt.setLong(3, table.getStats().getNumBytes()); pstmt.executeUpdate(); pstmt.close(); } if (table.hasPartition()) { String partSql = "INSERT INTO PARTITION_METHODS (TID, PARTITION_TYPE, EXPRESSION, EXPRESSION_SCHEMA) VALUES(?, ?, ?, ?)"; if (LOG.isDebugEnabled()) { LOG.debug(partSql); } pstmt = conn.prepareStatement(partSql); pstmt.setInt(1, tableId); pstmt.setString(2, table.getPartition().getPartitionType().name()); pstmt.setString(3, table.getPartition().getExpression()); pstmt.setBytes(4, table.getPartition().getExpressionSchema().toByteArray()); pstmt.executeUpdate(); } // If there is no error, commit the changes. conn.commit(); } catch (SQLException se) { if (conn != null) { try { conn.rollback(); } catch (SQLException e) { LOG.error(e, e); } } throw new TajoInternalError(se); } finally { CatalogUtil.closeQuietly(pstmt, res); } }
From source file:com.flexive.ejb.beans.PhraseEngineBean.java
/** * {@inheritDoc}//from w w w .java 2 s . c o m */ @Override @TransactionAttribute(TransactionAttributeType.REQUIRED) public void moveTreeNode(int category, long nodeId, long mandatorId, int delta) throws FxNoAccessException, FxNotFoundException { if (delta == 0) return; checkMandatorAccess(mandatorId, FxContext.getUserTicket()); Connection con = null; PreparedStatement ps = null; try { // Obtain a database connection con = Database.getDbConnection(); ps = con.prepareStatement("SELECT PARENTID, PARENTMANDATOR FROM " + TBL_PHRASE_TREE + " WHERE ID=? AND MANDATOR=? AND CAT=?"); ps.setLong(1, nodeId); ps.setLong(2, mandatorId); ps.setInt(3, category); ResultSet rs = ps.executeQuery(); if (rs == null || !rs.next()) throw new FxNotFoundException("ex.phrases.node.notFound.id", nodeId, mandatorId); long parentId = rs.getLong(1); if (rs.wasNull()) parentId = -1L; long parentMandatorId = rs.getLong(2); if (rs.wasNull()) parentMandatorId = -1L; rs.close(); ps.close(); //0..node id, 1..pos List<Long> positions = Lists.newArrayListWithCapacity(10); if (parentId == -1L) { ps = con.prepareStatement("SELECT ID FROM " + TBL_PHRASE_TREE + " WHERE PARENTID IS NULL AND MANDATOR=? AND CAT=? ORDER BY POS"); ps.setLong(1, mandatorId); ps.setInt(2, category); } else { ps = con.prepareStatement("SELECT ID FROM " + TBL_PHRASE_TREE + " WHERE PARENTID=? AND PARENTMANDATOR=? AND MANDATOR=? AND CAT=? ORDER BY POS"); ps.setLong(1, parentId); ps.setLong(2, parentMandatorId); ps.setLong(3, mandatorId); ps.setInt(4, category); } rs = ps.executeQuery(); long currPos = 1; int index = -1; while (rs != null && rs.next()) { if (index == -1 && nodeId == rs.getLong(1)) index = (int) currPos - 1; positions.add(rs.getLong(1)); currPos++; } if (positions.size() < 2) //only one node, can not change position return; int newIndex = index + delta; if (newIndex < 0) newIndex = 0; if (delta > 0) newIndex++; if (newIndex > (positions.size() - 1)) newIndex = positions.size(); positions.add(newIndex, nodeId); if (newIndex > index) positions.remove(index); else positions.remove(index + 1); //write back new positions ps.close(); ps = con.prepareStatement( "UPDATE " + TBL_PHRASE_TREE + " SET POS=? WHERE ID=? AND MANDATOR=? AND CAT=?"); ps.setLong(3, mandatorId); ps.setInt(4, category); for (int i = 1; i <= positions.size(); i++) { ps.setLong(1, i); ps.setLong(2, positions.get(i - 1)); ps.addBatch(); } ps.executeBatch(); } catch (SQLException exc) { EJBUtils.rollback(ctx); throw new FxDbException(LOG, exc, "ex.db.sqlError", exc.getMessage()).asRuntimeException(); } finally { Database.closeObjects(PhraseEngineBean.class, con, ps); } }
From source file:edu.umass.cs.gigapaxos.SQLPaxosLogger.java
private synchronized boolean logBatchDB(PendingLogTask[] packets) { if (isClosed()) return false; if (!isLoggingEnabled() /* && !ENABLE_JOURNALING */) return true; boolean logged = true; PreparedStatement pstmt = null; Connection conn = null;// www.ja v a 2s.c o m String cmd = "insert into " + getMTable() + " values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; long t0 = System.nanoTime(), t0Millis = System.currentTimeMillis(), t1 = t0; int i = 0; try { for (i = 0; i < packets.length; i++) { if (conn == null) { conn = this.getDefaultConn(); conn.setAutoCommit(false); pstmt = conn.prepareStatement(cmd); } PaxosPacket packet = packets[i].lmTask.logMsg; // accept and decision use a faster implementation int[] sb = AbstractPaxosLogger.getSlotBallot(packet); pstmt.setString(1, packet.getPaxosID()); pstmt.setInt(2, packet.getVersion()); pstmt.setInt(3, sb[0]); pstmt.setInt(4, sb[1]); pstmt.setInt(5, sb[2]); pstmt.setInt(6, packet.getType().getInt()); pstmt.setString(7, packets[i].logfile); pstmt.setLong(8, packets[i].logfileOffset); byte[] msgBytes = isJournalingEnabled() ? new byte[0] : deflate(toBytes(packet)); if (getLogMessageBlobOption()) { pstmt.setInt(9, packets[i].length);// msgBytes.length); Blob blob = conn.createBlob(); blob.setBytes(1, msgBytes); pstmt.setBlob(10, blob); } else { String packetString = packet.toString(); pstmt.setInt(9, packetString.length()); pstmt.setString(10, packetString); } pstmt.addBatch(); if ((i + 1) % MAX_DB_BATCH_SIZE == 0 || (i + 1) == packets.length) { int[] executed = pstmt.executeBatch(); conn.commit(); pstmt.clearBatch(); for (int j : executed) logged = logged && (j > 0); if (logged) log.log(Level.FINE, "{0}{1}{2}{3}{4}{5}", new Object[] { this, " successfully logged the " + "last ", (i + 1) % MAX_DB_BATCH_SIZE == 0 ? MAX_DB_BATCH_SIZE : (i + 1) % MAX_DB_BATCH_SIZE, " messages in ", (System.nanoTime() - t1) / 1000, " us" }); t1 = System.nanoTime(); } } } catch (Exception sqle) { /* If any exception happens, we must return false to preserve * safety. We return true only if every message is logged * successfully. */ sqle.printStackTrace(); log.severe(this + " incurred " + sqle + " while logging batch of size:" + packets.length + "; packet_length = " + packets[i].toString().length()); assert (packets[i].toString().length() < MAX_LOG_MESSAGE_SIZE); logged = false; } finally { cleanup(pstmt); cleanup(conn); } if (ENABLE_JOURNALING) DelayProfiler.updateDelayNano("index", t0, packets.length); else DelayProfiler.updateDelay("logBatchDB", t0Millis); // DelayProfiler.updateCount("#logged", packets.length); DelayProfiler.updateMovAvg("#potential_batched", packets.length); return logged; }
From source file:com.flexive.ejb.beans.PhraseEngineBean.java
/** * {@inheritDoc}/*from www . j av a2 s .co m*/ */ @Override @TransactionAttribute(TransactionAttributeType.REQUIRED) public long savePhrase(int category, String phraseKey, FxString value, FxPhraseSearchValueConverter converter, Object tag, long mandator) throws FxNoAccessException { Connection con = null; PreparedStatement ps = null; final UserTicket userTicket = FxContext.getUserTicket(); checkMandatorAccess(mandator, userTicket); checkPhraseKey(phraseKey); try { // Obtain a database connection con = Database.getDbConnection(); long phraseId; ps = con.prepareStatement("SELECT ID FROM " + TBL_PHRASE + " WHERE PKEY=? AND MANDATOR=? AND CAT=?"); ps.setString(1, phraseKey); ps.setLong(2, mandator); ps.setInt(3, category); ResultSet rs = ps.executeQuery(); if (rs != null && rs.next()) { phraseId = rs.getLong(1); rs.close(); ps.close(); ps = con.prepareStatement("DELETE FROM " + TBL_PHRASE_VALUES + " WHERE ID=? AND MANDATOR=?"); ps.setLong(1, phraseId); ps.setLong(2, mandator); ps.executeUpdate(); } else { try { phraseId = fetchNextPhraseId(mandator); } catch (FxApplicationException e) { EJBUtils.rollback(ctx); throw e.asRuntimeException(); } ps.close(); ps = con.prepareStatement( "INSERT INTO " + TBL_PHRASE + "(ID,PKEY,MANDATOR,HID,CAT)VALUES(?,?,?,?,?)"); ps.setLong(1, phraseId); ps.setString(2, phraseKey); ps.setLong(3, mandator); ps.setBoolean(4, false); ps.setInt(5, category); ps.executeUpdate(); } if (!value.isEmpty()) { ps.close(); ps = con.prepareStatement( "INSERT INTO " + TBL_PHRASE_VALUES + "(ID,MANDATOR,LANG,PVAL,SVAL,TAG)VALUES(?,?,?,?,?,?)"); ps.setLong(1, phraseId); ps.setLong(2, mandator); FxString fxTag = tag instanceof FxString ? (FxString) tag : null; for (long lang : value.getTranslatedLanguages()) { ps.setLong(3, lang); final String translation = value.getTranslation(lang); if (StringUtils.isBlank(translation)) continue; ps.setString(4, translation); if (converter != null) ps.setString(5, converter.convert(translation, lang)); else ps.setString(5, translation.trim().toUpperCase()); if (fxTag != null) { if (!fxTag.isMultiLanguage() || fxTag.translationExists(lang)) ps.setString(6, fxTag.getTranslation(lang)); else ps.setNull(6, Types.VARCHAR); } else { if (tag != null && !StringUtils.isBlank(String.valueOf(tag))) ps.setString(6, String.valueOf(tag)); else ps.setNull(6, Types.VARCHAR); } ps.addBatch(); } ps.executeBatch(); } return phraseId; } catch (SQLException exc) { EJBUtils.rollback(ctx); throw new FxDbException(LOG, exc, "ex.db.sqlError", exc.getMessage()).asRuntimeException(); } finally { Database.closeObjects(PhraseEngineBean.class, con, ps); } }
From source file:edu.umass.cs.gigapaxos.SQLPaxosLogger.java
/** * Batched version of putCheckpointState. This is a complicated method with * very different behaviors for updates and inserts. If update is true, it * attempts to batch-update all the checkpoints and for those * updates/inserts that failed, it attempts to individually update/insert * them through//www . j av a2s .com * {@link #putCheckpointState(String, int, Set, int, Ballot, String, int)}. * It is still possible that only a subset of the updates succeed, but that * is okay as checkpoint failure is not fatal except in the case of initial * checkpoint insertion. * * If update is false, it means that this is a batch-insertion of initial * checkpoints, and it is critical that this batch operation is atomic. If * the batch operation only partly succeeds, it should throw an exception so * that the caller can not proceed any further with the batch insertion but * it should also rollback the changes. * * The reason batched creation of initial checkpoints should be atomic is * that otherwise, the checkpoints that did get written essentially are * created paxos instances, but there is no easy way for the caller to know * that they got created and this could lead to nasty surprises later. If * the caller always follows up failed batch creations with sequential * creation, then the rollback is not critical as the sequential creation * will simply "recover" from the checkpoint if any left behind during a * previous failed batch creation. If the caller chooses to keep re-issuing * the batch creation and expects to eventually succeed (assuming that the * instances in the batch didn't actually exist a priori), then rolling back * failed batch creations like below will not help in the event of crashes. * So, the caller should really just issue sequential creation requests if a * batch creation fails or times out for any reason. * * Note: this method just needs to be atomic, i.e., all or none, but not * synchronized. Synchronizing it will invert the invariant that messageLog * is always locked before (because of the getMinLogFile invocation) * SQLPaxosLogger. * * @param tasks * @param update */ @Override public boolean putCheckpointState(CheckpointTask[] tasks, boolean update) { if (isClosed() || DISABLE_CHECKPOINTING) return false; boolean batchSuccess = true; boolean[] committed = new boolean[tasks.length]; long t1 = System.currentTimeMillis(); String insertCmd = "insert into " + getCTable() + " (version,members,slot,ballotnum,coordinator,state,create_time, min_logfile, paxos_id) values (?,?,?,?,?,?,?,?,?)"; String updateCmd = "update " + getCTable() + " set version=?,members=?, slot=?, ballotnum=?, coordinator=?, state=?, create_time=?, min_logfile=? where paxos_id=?"; String cmd = update ? updateCmd : insertCmd; PreparedStatement insertCP = null; Connection conn = null; String minLogfile = null; ArrayList<Integer> batch = new ArrayList<Integer>(); try { for (int i = 0; i < tasks.length; i++) { CheckpointTask task = tasks[i]; assert (task != null); assert (update || task.slot == 0); if ((task.slot == 0) == update) { this.putCheckpointState(task.paxosID, task.version, (task.members), task.slot, task.ballot, task.state, task.gcSlot, task.createTime); committed[i] = true; continue; } if (conn == null) { conn = this.getDefaultConn(); conn.setAutoCommit(false); insertCP = conn.prepareStatement(cmd); } insertCP.setInt(1, task.version); insertCP.setString(2, Util.toJSONString(task.members)); insertCP.setInt(3, task.slot); insertCP.setInt(4, task.ballot.ballotNumber); insertCP.setInt(5, task.ballot.coordinatorID); if (getCheckpointBlobOption()) { Blob blob = conn.createBlob(); blob.setBytes(1, task.state.getBytes(CHARSET)); insertCP.setBlob(6, blob); } else insertCP.setString(6, task.state); insertCP.setLong(7, task.createTime); insertCP.setString(8, minLogfile = this.getSetGCAndGetMinLogfile(task.paxosID, task.version, task.slot - task.gcSlot < 0 ? task.slot : task.gcSlot)); insertCP.setString(9, task.paxosID); insertCP.addBatch(); batch.add(i); incrTotalCheckpoints(); if (shouldLogCheckpoint(1)) log.log(Level.INFO, "{0} checkpointed> ({1}:{2}, {3}{4}, {5}, ({6}, {7}) [{8}]) {9}", new Object[] { this, task.paxosID, task.version, (task.members), task.slot, task.ballot, task.gcSlot, minLogfile, Util.truncate(task.state, TRUNCATED_STATE_SIZE, TRUNCATED_STATE_SIZE), (tasks.length > 1 ? "(batched=" + tasks.length + ")" : "") }); if ((i + 1) % MAX_DB_BATCH_SIZE == 0 || (i + 1) == tasks.length) { int[] executed = insertCP.executeBatch(); conn.commit(); insertCP.clearBatch(); for (int j = 0; j < executed.length; j++) batchSuccess = batchSuccess && (committed[batch.get(j)] = (executed[j] > 0)); batch.clear(); } } if (ENABLE_INSTRUMENTATION && Util.oneIn(10)) DelayProfiler.updateDelay("checkpoint", t1, tasks.length); } catch (SQLException | UnsupportedEncodingException sqle) { log.log(Level.SEVERE, "{0} SQLException while batched checkpointing", new Object[] { this }); sqle.printStackTrace(); } finally { cleanup(insertCP); cleanup(conn); } if (!batchSuccess) { if (update) { for (int i = 0; i < tasks.length; i++) if (!committed[i]) this.putCheckpointState(tasks[i].paxosID, tasks[i].version, tasks[i].members, tasks[i].slot, tasks[i].ballot, tasks[i].state, tasks[i].gcSlot); } else { // rollback for (int i = 0; i < tasks.length; i++) if (committed[i]) this.deleteCheckpoint(tasks[i].paxosID, tasks[i].version, tasks[i].members, tasks[i].slot, tasks[i].ballot, tasks[i].state, tasks[i].gcSlot); throw new PaxosInstanceCreationException( "Rolled back failed batch-creation of " + tasks.length + " paxos instances"); } } for (CheckpointTask task : tasks) this.deleteOutdatedMessages(task.paxosID, task.version, task.ballot, task.slot, task.ballot.ballotNumber, task.ballot.coordinatorID, task.gcSlot); return true; }
From source file:com.oltpbenchmark.benchmarks.tpcc.TPCCLoader.java
protected int loadCust(int whseKount, int distWhseKount, int custDistKount) { int k = 0;/*from w ww. j a va2 s. c om*/ int t = 0; Customer customer = new Customer(); History history = new History(); PrintWriter outHist = null; try { PreparedStatement custPrepStmt = getInsertStatement(TPCCConstants.TABLENAME_CUSTOMER); PreparedStatement histPrepStmt = getInsertStatement(TPCCConstants.TABLENAME_HISTORY); now = new java.util.Date(); if (outputFiles == true) { out = new PrintWriter(new FileOutputStream(fileLocation + "customer.csv")); LOG.debug("\nWriting Customer file to: " + fileLocation + "customer.csv"); outHist = new PrintWriter(new FileOutputStream(fileLocation + "cust-hist.csv")); LOG.debug("\nWriting Customer History file to: " + fileLocation + "cust-hist.csv"); } t = (whseKount * distWhseKount * custDistKount * 2); LOG.debug("\nStart Cust-Hist Load for " + t + " Cust-Hists @ " + now + " ..."); for (int w = 1; w <= whseKount; w++) { for (int d = 1; d <= distWhseKount; d++) { for (int c = 1; c <= custDistKount; c++) { Timestamp sysdate = new java.sql.Timestamp(System.currentTimeMillis()); customer.c_id = c; customer.c_d_id = d; customer.c_w_id = w; // discount is random between [0.0000 ... 0.5000] customer.c_discount = (float) (TPCCUtil.randomNumber(1, 5000, gen) / 10000.0); if (TPCCUtil.randomNumber(1, 100, gen) <= 10) { customer.c_credit = "BC"; // 10% Bad Credit } else { customer.c_credit = "GC"; // 90% Good Credit } if (c <= 1000) { customer.c_last = TPCCUtil.getLastName(c - 1); } else { customer.c_last = TPCCUtil.getNonUniformRandomLastNameForLoad(gen); } customer.c_first = TPCCUtil.randomStr(TPCCUtil.randomNumber(8, 16, gen)); customer.c_credit_lim = 50000; customer.c_balance = -10; customer.c_ytd_payment = 10; customer.c_payment_cnt = 1; customer.c_delivery_cnt = 0; customer.c_street_1 = TPCCUtil.randomStr(TPCCUtil.randomNumber(10, 20, gen)); customer.c_street_2 = TPCCUtil.randomStr(TPCCUtil.randomNumber(10, 20, gen)); customer.c_city = TPCCUtil.randomStr(TPCCUtil.randomNumber(10, 20, gen)); customer.c_state = TPCCUtil.randomStr(3).toUpperCase(); // TPC-C 4.3.2.7: 4 random digits + "11111" customer.c_zip = TPCCUtil.randomNStr(4) + "11111"; customer.c_phone = TPCCUtil.randomNStr(16); customer.c_since = sysdate; customer.c_middle = "OE"; customer.c_data = TPCCUtil.randomStr(TPCCUtil.randomNumber(300, 500, gen)); history.h_c_id = c; history.h_c_d_id = d; history.h_c_w_id = w; history.h_d_id = d; history.h_w_id = w; history.h_date = sysdate; history.h_amount = 10; history.h_data = TPCCUtil.randomStr(TPCCUtil.randomNumber(10, 24, gen)); k = k + 2; if (outputFiles == false) { custPrepStmt.setLong(1, customer.c_w_id); custPrepStmt.setLong(2, customer.c_d_id); custPrepStmt.setLong(3, customer.c_id); custPrepStmt.setDouble(4, customer.c_discount); custPrepStmt.setString(5, customer.c_credit); custPrepStmt.setString(6, customer.c_last); custPrepStmt.setString(7, customer.c_first); custPrepStmt.setDouble(8, customer.c_credit_lim); custPrepStmt.setDouble(9, customer.c_balance); custPrepStmt.setDouble(10, customer.c_ytd_payment); custPrepStmt.setLong(11, customer.c_payment_cnt); custPrepStmt.setLong(12, customer.c_delivery_cnt); custPrepStmt.setString(13, customer.c_street_1); custPrepStmt.setString(14, customer.c_street_2); custPrepStmt.setString(15, customer.c_city); custPrepStmt.setString(16, customer.c_state); custPrepStmt.setString(17, customer.c_zip); custPrepStmt.setString(18, customer.c_phone); custPrepStmt.setTimestamp(19, customer.c_since); custPrepStmt.setString(20, customer.c_middle); custPrepStmt.setString(21, customer.c_data); custPrepStmt.addBatch(); histPrepStmt.setInt(1, history.h_c_id); histPrepStmt.setInt(2, history.h_c_d_id); histPrepStmt.setInt(3, history.h_c_w_id); histPrepStmt.setInt(4, history.h_d_id); histPrepStmt.setInt(5, history.h_w_id); histPrepStmt.setTimestamp(6, history.h_date); histPrepStmt.setDouble(7, history.h_amount); histPrepStmt.setString(8, history.h_data); histPrepStmt.addBatch(); if ((k % configCommitCount) == 0) { long tmpTime = new java.util.Date().getTime(); String etStr = " Elasped Time(ms): " + ((tmpTime - lastTimeMS) / 1000.000) + " "; LOG.debug(etStr.substring(0, 30) + " Writing record " + k + " of " + t); lastTimeMS = tmpTime; custPrepStmt.executeBatch(); histPrepStmt.executeBatch(); custPrepStmt.clearBatch(); custPrepStmt.clearBatch(); transCommit(); } } else { String str = ""; str = str + customer.c_id + ","; str = str + customer.c_d_id + ","; str = str + customer.c_w_id + ","; str = str + customer.c_discount + ","; str = str + customer.c_credit + ","; str = str + customer.c_last + ","; str = str + customer.c_first + ","; str = str + customer.c_credit_lim + ","; str = str + customer.c_balance + ","; str = str + customer.c_ytd_payment + ","; str = str + customer.c_payment_cnt + ","; str = str + customer.c_delivery_cnt + ","; str = str + customer.c_street_1 + ","; str = str + customer.c_street_2 + ","; str = str + customer.c_city + ","; str = str + customer.c_state + ","; str = str + customer.c_zip + ","; str = str + customer.c_phone; out.println(str); str = ""; str = str + history.h_c_id + ","; str = str + history.h_c_d_id + ","; str = str + history.h_c_w_id + ","; str = str + history.h_d_id + ","; str = str + history.h_w_id + ","; str = str + history.h_date + ","; str = str + history.h_amount + ","; str = str + history.h_data; outHist.println(str); if ((k % configCommitCount) == 0) { long tmpTime = new java.util.Date().getTime(); String etStr = " Elasped Time(ms): " + ((tmpTime - lastTimeMS) / 1000.000) + " "; LOG.debug(etStr.substring(0, 30) + " Writing record " + k + " of " + t); lastTimeMS = tmpTime; } } } // end for [c] } // end for [d] } // end for [w] long tmpTime = new java.util.Date().getTime(); String etStr = " Elasped Time(ms): " + ((tmpTime - lastTimeMS) / 1000.000) + " "; LOG.debug(etStr.substring(0, 30) + " Writing record " + k + " of " + t); lastTimeMS = tmpTime; custPrepStmt.executeBatch(); histPrepStmt.executeBatch(); custPrepStmt.clearBatch(); histPrepStmt.clearBatch(); transCommit(); now = new java.util.Date(); if (outputFiles == true) { outHist.close(); } LOG.debug("End Cust-Hist Data Load @ " + now); } catch (SQLException se) { LOG.debug(se.getMessage()); transRollback(); if (outputFiles == true) { outHist.close(); } } catch (Exception e) { e.printStackTrace(); transRollback(); if (outputFiles == true) { outHist.close(); } } return (k); }
From source file:com.viettel.logistic.wms.dao.StockGoodsSerialDAO.java
public ResultDTO updateOrInsertSyncImportGoodsRevoke(StockTransDTO stockTrans, StockTransDetailDTO stockTransDetail, List<StockTransSerialDTO> lstStockTransSerial, Connection connection, String serialStatus, boolean isUpdate) { ResultDTO resultDTO = new ResultDTO(); //THONG TIN SO LUONG NHAP Double amount = 0D;/* www . ja v a2 s .c o m*/ Double amountIssue = 0D; //Cau lenh cap nhat serial StringBuilder sqlStockGoodsSerial = new StringBuilder(); List paramsStockGoodsSerial; List paramsStockTransSerial; PreparedStatement prstmtInsertStockTransSerial; PreparedStatement prstmtInsertStockGoodsSerial; String fromSerial; String toSerial; String serial; String prefixSerial = ""; String suffixFromSerial; String suffixToSerial; int numberNeedToCommit = 0; int numberOfSuccess = 0; int numberOfFail = 0; if (isUpdate) {//Neu la cap nhat sqlStockGoodsSerial.append(" UPDATE stock_goods_serial "); sqlStockGoodsSerial.append( " SET status = ?, change_date = to_date(?,'dd/MM/yyyy hh24:mi:ss'), cell_code = ?, barcode = ?, "); sqlStockGoodsSerial.append(" bincode = ?, goods_state = ?, import_stock_trans_id = ?, order_id = ?, "); sqlStockGoodsSerial.append(" owner_id = ?, owner_type = ? "); sqlStockGoodsSerial.append(" WHERE cust_id = ? AND goods_id = ? AND serial = ? "); sqlStockGoodsSerial.append(" AND status = ? "); sqlStockGoodsSerial.append(" LOG ERRORS REJECT LIMIT UNLIMITED"); } else {//Neu la them moi sqlStockGoodsSerial .append(" INSERT INTO stock_goods_serial (id, cust_id, owner_id, owner_type, goods_id,"); sqlStockGoodsSerial.append(" goods_state, status,sale_type, change_user,"); sqlStockGoodsSerial.append(" price,channel_type_id, barcode, change_date,"); sqlStockGoodsSerial.append(" import_date, sale_date, bincode, add_infor, cell_code,"); sqlStockGoodsSerial.append(" serial,partner_id,import_stock_trans_id,order_id) "); sqlStockGoodsSerial.append(" VALUES (STOCK_GOODS_SERIAL_SEQ.nextval,?,?,?,?,?,?,?,?,TO_NUMBER(?),"); sqlStockGoodsSerial.append(" TO_NUMBER(?),?,to_date(?,'dd/MM/yyyy hh24:mi:ss'),"); sqlStockGoodsSerial.append( " to_date(?,'dd/MM/yyyy hh24:mi:ss'),?,?,?,?,?,TO_NUMBER(?),TO_NUMBER(?),TO_NUMBER(?)) "); sqlStockGoodsSerial.append(" LOG ERRORS REJECT LIMIT UNLIMITED "); } //Cau lenh them moi giao dich StringBuilder sqlStockTransSerial = new StringBuilder(); sqlStockTransSerial.append(" INSERT INTO stock_trans_serial "); sqlStockTransSerial.append(" ( stock_trans_serial_id, stock_trans_id,"); sqlStockTransSerial.append(" stock_trans_detail_id, stock_trans_date, goods_id,"); sqlStockTransSerial.append(" goods_code, goods_name, goods_state, goods_unit_type,"); sqlStockTransSerial.append(" from_serial, to_serial,"); sqlStockTransSerial.append(" amount_order, amount_real, bincode, barcode, "); sqlStockTransSerial.append(" create_datetime,"); sqlStockTransSerial.append(" cell_code ) "); sqlStockTransSerial.append(" VALUES (STOCK_TRANS_SERIAL_SEQ.nextval,?,?,"); sqlStockTransSerial.append(" to_date(?,'dd/MM/yyyy hh24:mi:ss'),?,?,?,?,?,?,?,?,?,?,?,"); sqlStockTransSerial.append(" to_date(?,'dd/MM/yyyy hh24:mi:ss'),?) "); sqlStockTransSerial.append(" LOG ERRORS REJECT LIMIT UNLIMITED "); try { //3. TAO PREPARE STATEMENT prstmtInsertStockTransSerial = connection.prepareStatement(sqlStockTransSerial.toString()); prstmtInsertStockGoodsSerial = connection.prepareStatement(sqlStockGoodsSerial.toString()); //Chi tiet serial for (StockTransSerialDTO stockTransSerial : lstStockTransSerial) { fromSerial = stockTransSerial.getFromSerial(); toSerial = stockTransSerial.getToSerial(); stockTransSerial.setStockTransId(stockTrans.getStockTransId()); stockTransSerial.setStockTransDetailId(stockTransDetail.getStockTransDetailId()); numberNeedToCommit++; //SET PARAMS FOR STOCK_TRANS_SERIAL paramsStockTransSerial = setParamsStockTransSerial(stockTransSerial); //SET PARAMS AND ADD TO BATCH for (int idx = 0; idx < paramsStockTransSerial.size(); idx++) { prstmtInsertStockTransSerial.setString(idx + 1, DataUtil.nvl(paramsStockTransSerial.get(idx), "").toString()); } prstmtInsertStockTransSerial.addBatch(); //Insert chi tiet serial if (!StringUtils.isInteger(fromSerial) || !StringUtils.isInteger(toSerial)) {//Serial la ky tu serial = fromSerial; if (isUpdate) { paramsStockGoodsSerial = setParamForStockGoodsSerialInvoke(stockTrans, stockTransDetail, stockTransSerial, serial, serialStatus); } else { paramsStockGoodsSerial = setParamsStockGoodsSerial(stockTrans, stockTransDetail, stockTransSerial, serial, serialStatus); } //SET PARAMS AND ADD TO BATCH for (int idx = 0; idx < paramsStockGoodsSerial.size(); idx++) { try { prstmtInsertStockGoodsSerial.setString(idx + 1, DataUtil.nvl(paramsStockGoodsSerial.get(idx), "").toString()); } catch (Exception e) { System.out.println(idx); } } prstmtInsertStockGoodsSerial.addBatch(); //bo sung them amount issue } else {//Serial la so //Kiem tra do dai serial kneu >19 thi cat do kieu Long chi co do dai toi da 19 int iLengthSuffixSerial = 0; if (fromSerial.length() > Constants.SERIAL_LIMIT) { prefixSerial = fromSerial.substring(0, fromSerial.length() - Constants.SERIAL_LIMIT); suffixFromSerial = fromSerial.substring(fromSerial.length() - Constants.SERIAL_LIMIT, fromSerial.length()); suffixToSerial = toSerial.substring(toSerial.length() - Constants.SERIAL_LIMIT, toSerial.length()); iLengthSuffixSerial = suffixFromSerial.length(); } else { suffixFromSerial = fromSerial; suffixToSerial = toSerial; iLengthSuffixSerial = fromSerial.length(); } // String tmpSuffixSerial; for (Long lSerial = Long.parseLong(suffixFromSerial); lSerial <= Long .parseLong(suffixToSerial); lSerial++) { tmpSuffixSerial = DataUtil.lPad(lSerial.toString(), "0", iLengthSuffixSerial); serial = prefixSerial + tmpSuffixSerial; if (isUpdate) { paramsStockGoodsSerial = setParamForStockGoodsSerialInvoke(stockTrans, stockTransDetail, stockTransSerial, serial, serialStatus); } else { paramsStockGoodsSerial = setParamsStockGoodsSerial(stockTrans, stockTransDetail, stockTransSerial, serial, serialStatus); } //SET PARAMS AND ADD TO BATCH for (int idx = 0; idx < paramsStockGoodsSerial.size(); idx++) { prstmtInsertStockGoodsSerial.setString(idx + 1, DataUtil.nvl(paramsStockGoodsSerial.get(idx), "").toString()); } prstmtInsertStockGoodsSerial.addBatch(); //Bo sung them thong tin so luong amount issue //amountIssue++; } } //END IF if (numberNeedToCommit >= Constants.COMMIT_NUM) { try { prstmtInsertStockGoodsSerial.executeBatch(); prstmtInsertStockTransSerial.executeBatch(); numberOfSuccess = numberOfSuccess + numberNeedToCommit; } catch (Exception ex) { numberOfFail = numberOfFail + numberNeedToCommit; } numberNeedToCommit = 0; } } //END FOR if (numberNeedToCommit > 0) { try { prstmtInsertStockGoodsSerial.executeBatch(); prstmtInsertStockTransSerial.executeBatch(); numberOfSuccess += numberNeedToCommit; } catch (Exception ex) { // connection.rollback(); numberOfFail += numberNeedToCommit; } } prstmtInsertStockGoodsSerial.close(); prstmtInsertStockTransSerial.close(); } catch (SQLException ex) { ex.printStackTrace(); } //lay so luong hang hoa insert vao ban err$_ List<StockGoodsSerialInforDTO> lstError = getListErrorImportRevoke(stockTrans.getStockTransId()); int amountError = 0; if (lstError != null) { amountError = lstError.size(); } Double strAmount = Double.parseDouble(lstStockTransSerial.size() + ""); numberOfSuccess = Integer.parseInt(String.format("%.0f", strAmount)) - amountError;//tru so luong hang insert loi => so luong hang insert thanh cong numberOfFail = amountError;//so luong hang loi do ta ton tai serial cua khach hang trong kho amountIssue = (double) numberOfSuccess; // resultDTO.setMessage(ParamUtils.SUCCESS); resultDTO.setQuantityFail(numberOfFail); resultDTO.setQuantitySucc(numberOfSuccess); resultDTO.setAmount(amount); resultDTO.setAmountIssue(amountIssue); // tra ve list serial loi resultDTO.setLstStockGoodsSerialInforDTO(lstError); return resultDTO; }
From source file:com.pari.nm.utils.db.ReportDBHelper.java
public static void insertIntoTempPaginationTable(ServiceDescriptor descriptor, ServiceContainerImpl sImpl, String reportId, String sessionId, ArrayList<String> childColNameList) { Connection c = null;//from w w w . j a v a 2s.com PreparedStatement ps = null; if (sImpl != null) { try { StringBuffer sb = new StringBuffer(); String tblName = getTempPaginationTblName(reportId, sessionId); if (tblName == null) { throw new Exception( "No Table Exist with ReportId:\t" + reportId + "and SessionId:\t" + sessionId); } c = DBHelper.getConnection(); if (c == null) { logger.error("Unable to get Connection."); return; } Service[] allServs = sImpl.getAllServices(); if (allServs == null || allServs.length <= 0) { logger.info("Now Rows fetchd for ReportId:" + reportId + "and SessionId:\t" + sessionId); } else { logger.info("Number of Records are:\t" + allServs.length); } // Adding to check sb = new StringBuffer(); sb.append("INSERT INTO " + tblName + "\t Values("); ArrayList<String> colNameList = getColumnInfo(descriptor); if (colNameList != null && !colNameList.isEmpty()) { for (int i = 0; i < colNameList.size() - 1; i++) { sb.append("?,"); } sb.append("?)"); } try { ps = c.prepareStatement(sb.toString()); } catch (SQLException e1) { logger.error("Exception Occured while Executing statement:", e1); } Service[] rowsArr = sImpl.getAllServices(); if (rowsArr == null) { logger.info("Report Contians No Data."); } else { for (Service rowService : rowsArr) { int i = 1; if (colNameList != null && !colNameList.isEmpty()) { for (String colName : colNameList) { try { Object obj = rowService.getAttribute(colName); if (obj instanceof ServiceContainerImpl) { ps.setString(i++, String.valueOf(obj)); if (obj != null) { insertChildTableInfo(c, obj, reportId, sessionId, sImpl, childColNameList, null, colName); } } else if (obj == null) { ps.setString(i++, ""); } else { ps.setString(i++, String.valueOf(obj)); } } catch (SQLException e) { logger.error( "Exception Occured while Inserting Data into Temporary Pagination Table:", e); return; } } } try { ps.addBatch(); } catch (SQLException e) { e.printStackTrace(); } } } try { int[] count = ps.executeBatch(); logger.info("Number of Statements Executed was:\t" + count.length); } catch (SQLException e1) { logger.error("Exception Occured while Executing Batch Insert.", e1); } } catch (Exception e) { logger.error("Exception Occured while Inserting Data into Temporary Pagination Table:", e); } finally { if (ps != null) { try { ps.close(); } catch (SQLException e) { logger.error("Exception Occured while Closing the Prepared Statement Object.", e); } } if (c != null) { try { c.setAutoCommit(true); DBHelper.releaseConnection(c); } catch (SQLException e) { logger.error("Exception Occured while Closing the Connection Object.", e); } } } } }
From source file:org.wso2.carbon.idp.mgt.dao.IdPManagementDAO.java
/** * @param provisioningConnectors/*from w w w .j ava 2s . c om*/ * @param dbConnection * @param idpId * @throws IdentityProviderManagementException * @throws SQLException */ private void addProvisioningConnectorConfigs(ProvisioningConnectorConfig[] provisioningConnectors, Connection dbConnection, int idpId, int tenantId) throws IdentityProviderManagementException, SQLException { PreparedStatement prepStmt = null; ResultSet rs = null; try { // SP_IDP_ID,SP_IDP_PROV_CONNECTOR_TYPE, SP_IDP_PROV_CONFIG_KEY, // SP_IDP_PROV_CONFIG_VALUE, SP_IDP_PROV_CONFIG_IS_SECRET // SP_IDP_PROV_CONFIG_PROPERTY // TENANT_ID, PROVISIONING_CONFIG_ID, PROPERTY_KEY, PROPERTY_VALUE, PROPERTY_TYPE, // IS_SECRET String sqlStmt = IdPManagementConstants.SQLQueries.ADD_IDP_PROVISIONING_PROPERTY_SQL; prepStmt = dbConnection.prepareStatement(sqlStmt); String sqlBaseStmt = IdPManagementConstants.SQLQueries.ADD_IDP_PROVISIONING_CONFIG_SQL; String dbProductName = dbConnection.getMetaData().getDatabaseProductName(); PreparedStatement prepBaseStmt = dbConnection.prepareStatement(sqlBaseStmt, new String[] { DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, "ID") }); if (provisioningConnectors != null) { for (ProvisioningConnectorConfig connector : provisioningConnectors) { Property[] connctorProperties = connector.getProvisioningProperties(); if (connctorProperties != null) { // SP_IDP_PROVISIONING_CONFIG // TENANT_ID, IDP_ID, PROVISIONING_CONNECTOR_TYPE, IS_ENABLED, IS_DEFAULT prepBaseStmt.setInt(1, tenantId); prepBaseStmt.setInt(2, idpId); prepBaseStmt.setString(3, CharacterEncoder.getSafeText(connector.getName())); if (connector.isEnabled()) { prepBaseStmt.setString(4, "1"); } else { prepBaseStmt.setString(4, "0"); } if (connector.isBlocking()) { prepBaseStmt.setString(5, "1"); } else { prepBaseStmt.setString(5, "0"); } prepBaseStmt.executeUpdate(); rs = prepBaseStmt.getGeneratedKeys(); if (rs.next()) { int provisioningConfigID = rs.getInt(1); if (connctorProperties.length > 0) { for (Property config : connctorProperties) { if (config == null) { continue; } // SP_IDP_PROV_CONFIG_PROPERTY //TENANT_ID, PROVISIONING_CONFIG_ID, PROPERTY_KEY, // PROPERTY_VALUE, PROPERTY_BLOB_VALUE, PROPERTY_TYPE, IS_SECRET prepStmt.setInt(1, tenantId); prepStmt.setInt(2, provisioningConfigID); prepStmt.setString(3, CharacterEncoder.getSafeText(config.getName())); // TODO : Sect property type accordingly if (IdentityApplicationConstants.ConfigElements.PROPERTY_TYPE_BLOB .equals(config.getType())) { prepStmt.setString(4, null); setBlobValue(config.getValue(), prepStmt, 5); prepStmt.setString(6, config.getType()); } else { prepStmt.setString(4, CharacterEncoder.getSafeText(config.getValue())); setBlobValue(null, prepStmt, 5); prepStmt.setString(6, IdentityApplicationConstants.ConfigElements.PROPERTY_TYPE_STRING); } if (config.isConfidential()) { prepStmt.setString(7, "1"); } else { prepStmt.setString(7, "0"); } prepStmt.addBatch(); } } } // Adding properties for base config prepStmt.executeBatch(); } } } } catch (IOException e) { throw new IdentityProviderManagementException("An error occurred while processing content stream.", e); } finally { IdentityApplicationManagementUtil.closeResultSet(rs); IdentityApplicationManagementUtil.closeStatement(prepStmt); } }
From source file:org.wso2.carbon.dataservices.core.description.query.SQLQuery.java
private PreparedStatement createProcessedPreparedStatement(int queryType, InternalParamCollection params, Connection conn) throws DataServiceFault { try {//from w w w .j a v a 2 s . co m /* * lets see first if there's already a batch prepared statement * created */ boolean inTheMiddleOfABatch = false; PreparedStatement stmt = this.getBatchPreparedStatement(); int currentParamCount = this.getParamCount(); /* create a new prepared statement */ if (stmt == null) { /* batch mode is not supported for dynamic queries */ Object[] result = this.processDynamicQuery(this.getQuery(), params); String dynamicSQL = (String) result[0]; currentParamCount = (Integer) result[1]; String processedSQL = this.createProcessedQuery(dynamicSQL, params, currentParamCount); if (queryType == SQLQuery.DS_QUERY_TYPE_NORMAL) { if (this.isReturnGeneratedKeys()) { if (this.getKeyColumns() != null) { stmt = conn.prepareStatement(processedSQL, this.getKeyColumns()); } else { stmt = conn.prepareStatement(processedSQL, Statement.RETURN_GENERATED_KEYS); } } else { stmt = conn.prepareStatement(processedSQL); } } else if (queryType == SQLQuery.DS_QUERY_TYPE_STORED_PROC) { stmt = conn.prepareCall(processedSQL); } else { throw new DataServiceFault("Unsupported query type: " + queryType); } } else { inTheMiddleOfABatch = true; } if (!inTheMiddleOfABatch) { /* set query timeout */ if (this.isHasQueryTimeout()) { stmt.setQueryTimeout(this.getQueryTimeout()); } /* adding the try catch to avoid setting this for jdbc drivers that do not implement this method. */ try { /* set fetch direction */ if (this.isHasFetchDirection()) { stmt.setFetchDirection(this.getFetchDirection()); } /* set fetch size - user's setting */ if (this.isHasFetchSize()) { stmt.setFetchSize(this.getFetchSize()); } else { /* * stream data by sections - avoid the full result set * to be loaded to memory, and only stream if there * aren't any OUT parameters, MySQL fails in the * scenario of streaming and OUT parameters, so the * possibility is there for other DBMSs */ if (!this.hasOutParams() && this.getFetchSizeProperty().isChangeFetchSize()) { stmt.setFetchSize(this.getFetchSizeProperty().getFetchSize()); } } } catch (Throwable e) { log.debug("Exception while setting fetch size: " + e.getMessage(), e); } /* set max field size */ if (this.isHasMaxFieldSize()) { stmt.setMaxFieldSize(this.getMaxFieldSize()); } /* set max rows */ if (this.isHasMaxRows()) { stmt.setMaxRows(this.getMaxRows()); } } int currentOrdinal = 0; InternalParam param; ParamValue value; for (int i = 1; i <= currentParamCount; i++) { param = params.getParam(i); value = param.getValue(); /* * handle array values, if value is null, this param has to be * an OUT param */ if (value != null && value.getValueType() == ParamValue.PARAM_VALUE_ARRAY) { for (ParamValue arrayElement : value.getArrayValue()) { this.setParamInPreparedStatement(stmt, param, arrayElement == null ? null : arrayElement.toString(), queryType, currentOrdinal); currentOrdinal++; } } else { /* scalar value */ this.setParamInPreparedStatement(stmt, param, value != null ? value.getScalarValue() : null, queryType, currentOrdinal); currentOrdinal++; } } /* if we are in JDBC batch processing mode, batch it! */ if (this.isJDBCBatchRequest()) { stmt.addBatch(); } return stmt; } catch (SQLException e) { throw new DataServiceFault(e, "Error in 'createProcessedPreparedStatement'"); } }