private static void storeNodeCentrality( Connection connection, TableLocation nodesName, KeyedGraph graph) throws SQLException { final PreparedStatement nodeSt = connection.prepareStatement("INSERT INTO " + nodesName + " VALUES(?,?,?)"); try { int count = 0; for (VCent v : (Set<VCent>) graph.vertexSet()) { nodeSt.setInt(1, v.getID()); nodeSt.setDouble(2, v.getBetweenness()); nodeSt.setDouble(3, v.getCloseness()); nodeSt.addBatch(); count++; if (count >= BATCH_SIZE) { nodeSt.executeBatch(); nodeSt.clearBatch(); count = 0; } } if (count > 0) { nodeSt.executeBatch(); nodeSt.clearBatch(); } connection.commit(); } finally { nodeSt.close(); } }
private static void storeEdgeCentrality( Connection connection, TableLocation edgesName, KeyedGraph graph) throws SQLException { final PreparedStatement edgeSt = connection.prepareStatement("INSERT INTO " + edgesName + " VALUES(?,?)"); try { int count = 0; for (EdgeCent e : (Set<EdgeCent>) graph.edgeSet()) { edgeSt.setInt(1, e.getID()); edgeSt.setDouble(2, e.getBetweenness()); edgeSt.addBatch(); count++; if (count >= BATCH_SIZE) { edgeSt.executeBatch(); edgeSt.clearBatch(); count = 0; } } if (count > 0) { edgeSt.executeBatch(); edgeSt.clearBatch(); } connection.commit(); } finally { edgeSt.close(); } }
/** * Convenience method to create a new PreparedStatement for a query. * * @param conn The Connection to use for the statement * @param stmtText Statement text * @param resultSetType Type of result set * @param resultSetConcurrency Concurrency for the result set * @return The PreparedStatement * @throws SQLException thrown if an error occurs creating the statement */ public PreparedStatement getStatementForQuery( ManagedConnection conn, String stmtText, String resultSetType, String resultSetConcurrency) throws SQLException { Connection c = (Connection) conn.getConnection(); if (supportsBatching) { // Check for a waiting batched statement that is ready for processing ConnectionStatementState state = getConnectionStatementState(conn); if (state != null && state.processable) { // Process the batch statement before returning our new query statement processConnectionStatement(conn); } } // Create a new PreparedStatement for this query PreparedStatement ps = null; if (resultSetType != null || resultSetConcurrency != null) { int rsTypeValue = ResultSet.TYPE_FORWARD_ONLY; if (resultSetType != null) { if (resultSetType.equals("scroll-sensitive")) { rsTypeValue = ResultSet.TYPE_SCROLL_SENSITIVE; } else if (resultSetType.equals("scroll-insensitive")) { rsTypeValue = ResultSet.TYPE_SCROLL_INSENSITIVE; } } int rsConcurrencyValue = ResultSet.CONCUR_READ_ONLY; if (resultSetConcurrency != null && resultSetConcurrency.equals("updateable")) { rsConcurrencyValue = ResultSet.CONCUR_UPDATABLE; } ps = c.prepareStatement(stmtText, rsTypeValue, rsConcurrencyValue); ps .clearBatch(); // In case using statement caching and given one with batched statements // left hanging (C3P0) } else { ps = c.prepareStatement(stmtText); ps .clearBatch(); // In case using statement caching and given one with batched statements // left hanging (C3P0) } if (queryTimeout > 0) { // Apply any query timeout ps.setQueryTimeout(queryTimeout / 1000); // queryTimeout is in milliseconds } if (NucleusLogger.DATASTORE.isDebugEnabled()) { NucleusLogger.DATASTORE.debug(LOCALISER.msg("052110", StringUtils.toJVMIDString(ps))); } if (!jdbcStatements) { // Wrap with our parameter logger ps = new ParamLoggingPreparedStatement(ps, stmtText); ((ParamLoggingPreparedStatement) ps).setParamsInAngleBrackets(paramValuesInBrackets); } return ps; }
protected void createTiles() throws InterruptedException, MapCreationException { @SuppressWarnings("unused") // W #unused int maxMapProgress = 2 * (mMap.getXMax() - mMap.getXMin() + 1) * (mMap.getYMax() - mMap.getYMin() + 1); // bundleProgress.initMapCreation(maxMapProgress); TileImageParameters param = mMap.getParameters(); // if (param != null) // mapDlTileProvider = new ConvertedRawTileProvider(mapDlTileProvider, param.getFormat()); try { conn.setAutoCommit(false); int batchTileCount = 0; int tilesWritten = 0; Runtime r = Runtime.getRuntime(); long heapMaxSize = r.maxMemory(); prepStmt = conn.prepareStatement(getTileInsertSQL()); for (int x = mMap.getXMin(); x <= mMap.getXMax(); x++) { for (int y = mMap.getYMin(); y <= mMap.getYMax(); y++) { // checkUserAbort(); // bundleProgress.incMapCreationProgress(); try { // byte[] sourceTileData = mapDlTileProvider.getTileData(x, y); byte[] sourceTileData = null; if (sourceTileData != null) { writeTile(x, y, mMap.getZoom(), sourceTileData); tilesWritten++; long heapAvailable = heapMaxSize - r.totalMemory() + r.freeMemory(); batchTileCount++; if ((heapAvailable < HEAP_MIN) || (batchTileCount >= MAX_BATCH_SIZE)) { log.trace("Executing batch containing " + batchTileCount + " tiles"); prepStmt.executeBatch(); prepStmt.clearBatch(); System.gc(); conn.commit(); // bundleProgress.incMapCreationProgress(batchTileCount); batchTileCount = 0; } } } catch (IOException e) { throw new MapCreationException(mMap, e); } } } prepStmt.executeBatch(); prepStmt.clearBatch(); System.gc(); if (tilesWritten > 0) updateTileMetaInfo(); log.trace("Final commit containing " + batchTileCount + " tiles"); conn.commit(); // bundleProgress.setMapCreationProgress(maxMapProgress); } catch (SQLException e) { throw new MapCreationException(mMap, e); } }
@Test public void testPreparedStatementWithBatch() throws Exception { Connection connection = proxyDataSource.getConnection(); PreparedStatement statement = connection.prepareStatement("update emp set name = ? where id = ?"); statement.setString(1, "BAZ"); statement.setInt(2, 3); statement.addBatch(); statement.clearBatch(); statement.setString(1, "FOO"); statement.setInt(2, 1); statement.addBatch(); statement.setString(1, "BAR"); statement.setInt(2, 2); statement.addBatch(); statement.executeBatch(); final InMemoryLog log = getInMemoryLog(); verifyMessage(CommonsLogLevel.DEBUG, log, "update emp set name = ? where id = ?"); verifyMessage(CommonsLogLevel.DEBUG, log, "[(FOO,1),(BAR,2)]"); }
public static void insertBatch() { String sql = "INSERT into order_line (ol_id, ol_o_id, ol_i_id, ol_qty, ol_discount, ol_comments) VALUES (?, ?, ?, ?, ?, ?)"; Connection conn = null; if (flag) { conn = IMDGHelper.getConnection(); } else { conn = DBHelper.getConnection(); } try { PreparedStatement pst = conn.prepareStatement(sql); Random random = new Random(System.currentTimeMillis()); int count = 1000; int range = 10000; long start = System.currentTimeMillis(); for (int i = 0; i < count; i++) { pst.setInt(1, random.nextInt(range) + 2000); pst.setInt(2, random.nextInt(range) + 2000); pst.setInt(3, random.nextInt(range) + 2000); pst.setInt(4, random.nextInt(range) + 2000); pst.setDouble(5, (double) getRandomInt(0, 30) / 100); pst.setString(6, getRandomAString(20, 100)); pst.addBatch(); } pst.executeBatch(); pst.clearBatch(); long end = System.currentTimeMillis(); System.out.println("INSERT time : " + (end - start)); pst.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } }
public void clearBatch() throws SQLException { Profiler profiler = _profilerPoint.start(); try { _preparedStatement.clearBatch(); } finally { profiler.finish(); } }
/** Flushes the batch */ public void flush() throws SQLException { try { preparedStatement.executeBatch(); preparedStatement.clearBatch(); } catch (SQLException e) { String details = e.getNextException() == null ? "" : e.getNextException().getMessage(); throw new SQLException(e.getMessage() + "\n\n" + details); } }
@Override public void run() { Connection mConn = DBService.sharedInstance().getConnection(); try { while (true) { for (int i = 0; i < 80; i++) { DBDData data = mDataQueue.poll(); if (data == null) { if (mDataQueue.size() == 0) { data = mDataQueue.take(); } else { break; } } if (data != null) { mCacheData.add(data); } } if (mCacheData.size() == 0) { continue; } if (isInterrupt) { break; } PreparedStatement ps = null; try { ps = mConn.prepareStatement(INSERT_SQL); System.out.println("write_size=" + mCacheData.size()); for (DBDData data : mCacheData) { data.insertIntoDB(ps); } int[] result = ps.executeBatch(); System.out.println(result); } catch (Exception e) { e.printStackTrace(); } finally { try { if (ps != null) { ps.clearParameters(); ps.clearBatch(); ps.close(); } } catch (SQLException e) { e.printStackTrace(); } } mCacheData.clear(); } } catch (InterruptedException e) { e.printStackTrace(); } finally { CloseUtil.close(mConn); } }
public static Integer batchInsertData( List<Object[]> data, Connection countCon, PreparedStatement ps, Set<String> yearMonthCache) throws SQLException { if (CollectionUtils.isEmpty(data)) { return 0; } Integer curMaxId = 0; for (int i = 0; i < data.size(); i++) { // 插入数据 // 数据库表的设定也不允许任何列为空 if (data.get(i).length < 4 || data.get(i)[1] == null || data.get(i)[2] == null || data.get(i)[3] == null || data.get(i)[0] == null) { logger.error("搜索关键字备份 - 数据插入:存在空值!"); continue; } Integer curId = ((BigInteger) data.get(i)[0]).intValue(); Date curDate = (Date) data.get(i)[3]; String curYearMonth = new DateTime(curDate).toString("yyyyMM"); if (!yearMonthCache.contains(curYearMonth)) { yearMonthCache.add(curYearMonth); // 如果当前id大于等于5月最大id,结束 for (DateTime temp = DateTime.parse("201401", DateTimeFormat.forPattern("yyyyMM")); temp.toString("yyyyMM").compareTo(curYearMonth) < 0; temp = temp.plusMonths(1)) { // 如果出现比现在的月份还小的分区不存在的情况,也创建 String tempCurString = temp.toString("yyyyMM"); if (!yearMonthCache.contains(tempCurString)) { yearMonthCache.add(tempCurString); createMonthPartitionIfNotExist(tempCurString, countCon); } } createMonthPartitionIfNotExist(curYearMonth, countCon); } ps.setObject(1, curId); ps.setObject(2, data.get(i)[1]); ps.setObject(3, data.get(i)[2]); ps.setObject(4, curDate); ps.addBatch(); if (curId > curMaxId) { curMaxId = curId; } if ((i + 1) == data.size()) { long b = new Date().getTime(); ps.executeBatch(); ps.clearBatch(); logger.info("插入【" + data.size() + "】条数据,耗时【" + (new Date().getTime() - b) + "】毫秒"); } } return curMaxId; }
private void testClearBatch01() throws SQLException { trace("testClearBatch01"); String sPrepStmt = COFFEE_UPDATE; trace("Prepared Statement String:" + sPrepStmt); prep = conn.prepareStatement(sPrepStmt); prep.setInt(1, 2); prep.addBatch(); prep.setInt(1, 3); prep.addBatch(); prep.setInt(1, 4); prep.addBatch(); prep.clearBatch(); assertEquals(0, prep.executeBatch().length); }
/** * 说明:更新给定的部门对象 * * @param con 数据库连接对象 * @param source 部门对象 * @throws LawyerException */ public void updateDepartment(Connection con, Department source) throws LawyerException { String sql = "UPDATE " + DEPTABLE + " SET NAME=?,STATUS=?,SDATE=?,EDATE=?,REMARKS=?,PRINCIPAl=? WHERE ID='" + source.getId() + "' AND SYSID='" + source.getSysID() + "'"; PreparedStatement psment = null; try { psment = con.prepareStatement(sql); // 清除参数 psment.clearBatch(); psment.setString(1, source.getName()); psment.setString(2, source.getStatus()); psment.setDate( 3, null == source.getSdate() ? new java.sql.Date(new Date().getTime()) : new java.sql.Date(source.getSdate().getTime())); psment.setDate( 4, null == source.getEdate() ? new java.sql.Date(new Date().getTime()) : new java.sql.Date(source.getEdate().getTime())); psment.setString(5, source.getRemarks()); psment.setString(6, source.getPrincipal()); psment.addBatch(); // 执行数据库插入处理 psment.execute(); } catch (SQLException e) { throw new LawyerException("Update Department is error! " + e.getMessage()); } finally { try { psment.close(); } catch (SQLException e) { } psment = null; } }
/** * Method to execute a PreparedStatement query, and return the ResultSet. Prints logging * information about timings. * * @param conn The connection (required since the one on PreparedStatement is not always the same * so we can't use it) * @param stmt The statement text * @param ps The Prepared Statement * @return The ResultSet from the query * @throws SQLException Thrown if an error occurs */ public ResultSet executeStatementQuery( ExecutionContext ec, ManagedConnection conn, String stmt, PreparedStatement ps) throws SQLException { if (supportsBatching) { ConnectionStatementState state = getConnectionStatementState(conn); if (state != null) { if (state.processable) { // Current batch statement is processable now so lets just process it before processing // our query processConnectionStatement(conn); } else { // Current wait statement is not processable now so leave it in wait state if (NucleusLogger.DATASTORE_RETRIEVE.isDebugEnabled()) { NucleusLogger.DATASTORE_RETRIEVE.debug(LOCALISER.msg("052106", state.stmtText, stmt)); } } } } // Execute this query long startTime = System.currentTimeMillis(); if (NucleusLogger.DATASTORE_NATIVE.isDebugEnabled()) { if (ps instanceof ParamLoggingPreparedStatement) { NucleusLogger.DATASTORE_NATIVE.debug( ((ParamLoggingPreparedStatement) ps).getStatementWithParamsReplaced()); } else { NucleusLogger.DATASTORE_NATIVE.debug(stmt); } } ResultSet rs = ps.executeQuery(); if (ec != null && ec.getStatistics() != null) { // Add to statistics ec.getStatistics().incrementNumReads(); } ps.clearBatch(); if (NucleusLogger.DATASTORE_RETRIEVE.isDebugEnabled()) { NucleusLogger.DATASTORE_RETRIEVE.debug( LOCALISER.msg("045000", (System.currentTimeMillis() - startTime))); } return rs; }
/** * 说明:存储给定的部门对象 * * @param con 数据库连接对象 * @param source 部门对象 * @throws LawyerException */ public void saveDepartment(Connection con, Department source) throws LawyerException { String sql = "INSERT INTO " + DEPTABLE + "(ID,NAME,STATUS,SDATE,EDATE,REMARKS,PRINCIPAl,SYSID) VALUES(?,?,?,?,?,?,?,?)"; PreparedStatement psment = null; try { psment = con.prepareStatement(sql); // 清除参数 psment.clearBatch(); psment.setString(1, source.getId()); psment.setString(2, source.getName()); psment.setString(3, source.getStatus()); psment.setDate( 4, null == source.getSdate() ? new java.sql.Date(new Date().getTime()) : new java.sql.Date(source.getSdate().getTime())); psment.setDate( 5, null == source.getEdate() ? new java.sql.Date(new Date().getTime()) : new java.sql.Date(source.getEdate().getTime())); psment.setString(6, source.getRemarks()); psment.setString(7, source.getPrincipal()); psment.setString(8, source.getSysID()); psment.addBatch(); // 执行数据库插入处理 psment.execute(); } catch (SQLException e) { throw new LawyerException("Save Department is error! " + e.getMessage()); } finally { try { psment.close(); } catch (SQLException e) { } psment = null; } }
@Override public void traceMarker() throws Exception { PreparedStatement preparedStatement = connection.prepareStatement("insert into employee (name) values (?)"); try { preparedStatement.setString(1, "huckle"); preparedStatement.addBatch(); preparedStatement.setString(1, "sally"); preparedStatement.addBatch(); preparedStatement.executeBatch(); preparedStatement.clearBatch(); preparedStatement.setString(1, "lowly"); preparedStatement.addBatch(); preparedStatement.setString(1, "pig will"); preparedStatement.addBatch(); preparedStatement.executeBatch(); } finally { preparedStatement.close(); } }
/** * Method to execute a PreparedStatement (using PreparedStatement.execute()). Prints logging * information about timings. * * @param ec Execution Context * @param conn The connection (required since the one on PreparedStatement is not always the same * so we can't use it) * @param stmt The statement text * @param ps The Prepared Statement * @return The numer of rows affected (as per PreparedStatement.execute) * @throws SQLException Thrown if an error occurs */ public boolean executeStatement( ExecutionContext ec, ManagedConnection conn, String stmt, PreparedStatement ps) throws SQLException { if (supportsBatching) { // Check for a waiting batched statement that is ready for processing ConnectionStatementState state = getConnectionStatementState(conn); if (state != null && state.processable) { // Process the batch statement before returning our new query statement processConnectionStatement(conn); } } // Process the normal execute statement long startTime = System.currentTimeMillis(); if (NucleusLogger.DATASTORE_NATIVE.isDebugEnabled()) { if (ps instanceof ParamLoggingPreparedStatement) { NucleusLogger.DATASTORE_NATIVE.debug( ((ParamLoggingPreparedStatement) ps).getStatementWithParamsReplaced()); } else { NucleusLogger.DATASTORE_NATIVE.debug(stmt); } } boolean flag = ps.execute(); if (ec != null && ec.getStatistics() != null) { // Add to statistics ec.getStatistics().incrementNumWrites(); } ps.clearBatch(); if (NucleusLogger.DATASTORE_PERSIST.isDebugEnabled()) { NucleusLogger.DATASTORE_PERSIST.debug( LOCALISER.msg( "045002", "" + (System.currentTimeMillis() - startTime), StringUtils.toJVMIDString(ps))); } return flag; }
public static final void persistToDatabase(List<ChartPvUvData> datas) throws SQLException, ClassNotFoundException { if (datas == null || datas.isEmpty()) { return; } Connection conn = null; PreparedStatement pstmt = null; try { conn = getDatabaseConnection(); String sql = "insert into page_view(module, code, country, `from`, `schedule`,pv, uv, gmt_create) values(?,?,?,?,?,?,?,now()) on duplicate key update pv =? ,uv =?"; pstmt = conn.prepareStatement(sql); int count = 0; for (ChartPvUvData data : datas) { ChartPvUvData.KeyEntry keyEntry = data.getKeyEntry(); pstmt.setString(1, data.getModule()); pstmt.setString(2, keyEntry.linkNode); pstmt.setString(3, keyEntry.country); pstmt.setString(4, keyEntry.from); pstmt.setString(5, data.getDate()); pstmt.setInt(6, data.getPv()); pstmt.setInt(7, data.getUv()); pstmt.setInt(8, data.getPv()); pstmt.setInt(9, data.getUv()); pstmt.addBatch(); count++; if (count >= BATCH_SIZE) { pstmt.executeBatch(); pstmt.clearBatch(); count = 0; } } if (count > 0) { pstmt.executeBatch(); } } finally { closeDatabaseComponent(null, pstmt, conn); } }
@Override public void save(List<Food> foods) { String sql = "insert INTO food VALUES(null, ?, ?, ?, ?);"; try (Connection conn = dataSource.getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) { conn.setAutoCommit(false); for (Food actualFood : foods) { ps.setInt(1, actualFood.getCalories()); ps.setBoolean(2, actualFood.isVegan()); ps.setString(3, actualFood.getName()); ps.setInt(4, actualFood.getPrice()); ps.addBatch(); } ps.executeBatch(); ps.clearBatch(); conn.commit(); } catch (SQLException e) { e.printStackTrace(); } }
static int loadItem(int itemKount) { int k = 0; int t = 0; int randPct = 0; int len = 0; int startORIGINAL = 0; try { now = new java.util.Date(); t = itemKount; System.out.println("\nStart Item Load for " + t + " Items @ " + now + " ..."); if (outputFiles == true) { out = new PrintWriter(new FileOutputStream(fileLocation + "item.csv")); System.out.println("\nWriting Item file to: " + fileLocation + "item.csv"); } Item item = new Item(); for (int i = 1; i <= itemKount; i++) { item.i_id = i; item.i_name = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(14, 24, gen)); item.i_price = (float) (jTPCCUtil.randomNumber(100, 10000, gen) / 100.0); // i_data randPct = jTPCCUtil.randomNumber(1, 100, gen); len = jTPCCUtil.randomNumber(26, 50, gen); if (randPct > 10) { // 90% of time i_data isa random string of length [26 .. 50] item.i_data = jTPCCUtil.randomStr(len); } else { // 10% of time i_data has "ORIGINAL" crammed somewhere in middle startORIGINAL = jTPCCUtil.randomNumber(2, (len - 8), gen); item.i_data = jTPCCUtil.randomStr(startORIGINAL - 1) + "ORIGINAL" + jTPCCUtil.randomStr(len - startORIGINAL - 9); } item.i_im_id = jTPCCUtil.randomNumber(1, 10000, gen); k++; if (outputFiles == false) { itemPrepStmt.setLong(1, item.i_id); itemPrepStmt.setString(2, item.i_name); itemPrepStmt.setDouble(3, item.i_price); itemPrepStmt.setString(4, item.i_data); itemPrepStmt.setLong(5, item.i_im_id); itemPrepStmt.addBatch(); if ((k % configCommitCount) == 0) { long tmpTime = new java.util.Date().getTime(); String etStr = " Elasped Time(ms): " + ((tmpTime - lastTimeMS) / 1000.000) + " "; System.out.println(etStr.substring(0, 30) + " Writing record " + k + " of " + t); lastTimeMS = tmpTime; itemPrepStmt.executeBatch(); itemPrepStmt.clearBatch(); transCommit(); } } else { String str = ""; str = str + item.i_id + ","; str = str + item.i_name + ","; str = str + item.i_price + ","; str = str + item.i_data + ","; str = str + item.i_im_id; out.println(str); if ((k % configCommitCount) == 0) { long tmpTime = new java.util.Date().getTime(); String etStr = " Elasped Time(ms): " + ((tmpTime - lastTimeMS) / 1000.000) + " "; System.out.println(etStr.substring(0, 30) + " Writing record " + k + " of " + t); lastTimeMS = tmpTime; } } } // end for long tmpTime = new java.util.Date().getTime(); String etStr = " Elasped Time(ms): " + ((tmpTime - lastTimeMS) / 1000.000) + " "; System.out.println(etStr.substring(0, 30) + " Writing record " + k + " of " + t); lastTimeMS = tmpTime; if (outputFiles == false) { itemPrepStmt.executeBatch(); } transCommit(); now = new java.util.Date(); System.out.println("End Item Load @ " + now); } catch (SQLException se) { System.out.println(se.getMessage()); transRollback(); } catch (Exception e) { e.printStackTrace(); transRollback(); } return (k); } // end loadItem()
/** * insert the given file into the database * * @param f * @param insertDCMSeriesStmt * @param insertDCMObjectStmt * @param insertDCMTagsStmt * @param currentDbConnection */ private void insertFile2DB( File f, PreparedStatement insertDCMSeriesStmt, PreparedStatement insertDCMObjectStmt, PreparedStatement insertDCMTagsStmt, Connection currentDbConnection) { logger.info( " \r\n\r\n" + "#######################################################\r\n" + "#\r\n" + "# STARTING IMPORT FOR " + f.getName() + "\r\n" + "#\r\n" + "#######################################################"); used_files++; long start = System.currentTimeMillis(); boolean importFile = true; try { DicomHeader dh = new DicomHeader(f); DicomObject dcmObjectHeader = dh.getDicomObject(); // if a rule to filter exist -> no import if (!filterTags.isEmpty()) { for (Iterator<String> iter = filterTags.keySet().iterator(); iter.hasNext(); ) { String tag = (String) iter.next(); // check comma separated filter e. g. 0008,0060=MR, UR, NM, US if (filterTags.get(tag).contains(",")) { String[] splittedValue = filterTags.get(tag).split(", "); for (int i = 0; i < splittedValue.length; i++) { if (splittedValue[i].equals(dh.getHeaderStringValue(tag))) { importFile = false; logger.info("NOT importing File, matches Filtertags: " + filterTags.get(tag)); break; } } } else if (dh.getHeaderStringValue(tag).equals(filterTags.get(tag))) { importFile = false; logger.info("NOT importing File, matches Filtertags: " + filterTags.get(tag)); break; } } } // filter the reconstruction-images if (importFile && CHECK_RECONSTRUCTION) { importFile = checkRecontruction(currentDbConnection, dh); } if (importFile) { // insert dcmSeries if it not exists if (!seriesExists(currentDbConnection, dh)) { try { int i = 1; // index for database-fields in series for (Iterator<String> iter = dicomTags.keySet().iterator(); iter.hasNext(); i++) { String tag = (String) iter.next(); String VR = DicomHeader.getHeaderFieldType(tag); // if Date if ("DA".equals(VR)) { try { Date d = dh.getHeaderDateValue(tag); insertDCMSeriesStmt.setDate(i, new java.sql.Date(d.getTime())); } catch (Exception e) { insertDCMSeriesStmt.setDate(i, null); } // if Time } else if ("TM".equals(VR)) { try { Date d = dh.getHeaderDateValue(tag); insertDCMSeriesStmt.setTime(i, new java.sql.Time(d.getTime())); } catch (Exception e) { insertDCMSeriesStmt.setTime(i, null); } // else String } else { String s = dh.getHeaderStringValue(tag); s = s.replaceAll("\0", ""); insertDCMSeriesStmt.setString(i, s); } } insertDCMSeriesStmt.execute(); // Exception if Seriesuid exist } catch (SQLException sqle) { if (sqle.getMessage().indexOf("duplicate key") < 0) { logger.error("Error adding series: ", sqle); } else { logger.warn("Series allready in DB"); } currentDbConnection.rollback(); } } // insert the dicom-object and the tags try { int j = 1; // index for database-fields in dicom-objects insertDCMObjectStmt.setString(j++, dh.getDicomObject().getString(Tag.SeriesInstanceUID)); insertDCMObjectStmt.setString(j++, dh.getDicomObject().getString(Tag.SOPInstanceUID)); insertDCMObjectStmt.setTimestamp(j++, new Timestamp(System.currentTimeMillis())); // storing the md5 of pixel-data in database, if CHECKSUM is true if (CHECKSUM) { DicomInputStream dis = new DicomInputStream(f); DicomObject dcmObj = new BasicDicomObject(); dis.readDicomObject(dcmObj, -1); DicomElement de = dcmObj.get(Tag.PixelData); byte[] bytes = de.getBytes(); MessageDigest md = MessageDigest.getInstance("MD5"); md.update(bytes); byte[] md5 = md.digest(); BigInteger bi = new BigInteger(1, md5); insertDCMObjectStmt.setString(j++, bi.toString(16)); } else { insertDCMObjectStmt.setString(j++, null); } insertDCMObjectStmt.setString(j++, SOFTWARE_VERSION); insertDCMObjectStmt.execute(); // currentDbConnection.commit(); int parentID = getDCMObjectID( currentDbConnection, dh.getDicomObject().getString(Tag.SOPInstanceUID)); itemOrder = 1; // recursive insert of dicom-tags insertDCMTagsStmt.clearBatch(); insertDicomTags(parentID, dcmObjectHeader, null, insertDCMTagsStmt); insertDCMTagsStmt.executeBatch(); currentDbConnection.commit(); logger.info("File inserted"); imported_files++; } catch (SQLException sqle) { currentDbConnection.rollback(); if (sqle.getMessage().indexOf("duplicate key") < 0) { logger.error("Error importing image: ", sqle); } else { logger.warn( "File with UID=" + dh.getDicomObject().getString(Tag.SOPInstanceUID) + " allready in db"); } } } if (DELETE_FILES_AFTER_IMPORT) { f.delete(); } else { boolean success = f.renameTo(new File(OK_DIR, f.getName())); if (success) { logger.info("moved file to: " + OK_DIR + File.separator + f.getName()); } else { logger.warn("unable to move file to: " + OK_DIR + File.separator + f.getName()); } } } catch (Exception e) { boolean success = f.renameTo(new File(ERROR_DIR, f.getName())); logger.log( Level.WARN, "Failed to insert file: " + f.getAbsolutePath() + " " + e.getMessage(), e); if (success) { logger.info("moved file to: " + ERROR_DIR + File.separator + f.getName()); } else { logger.warn("unable to move file to: " + ERROR_DIR + File.separator + f.getName()); } try { currentDbConnection.rollback(); } catch (SQLException e1) { logger.log(Level.ERROR, e1.getMessage(), e1); } } logger.info( "END OF IMPORT FOR " + f.getName() + " took " + (System.currentTimeMillis() - start) + "µsec. \r\n" + "Files used: " + used_files + " Files imported: " + imported_files + " \r\n" + "#######################################################"); }
public boolean batchProductQR(int sum) { if (sum <= 0 || sum > 1000) { return false; } String imgedir = null; String os = System.getProperty("os.name"); if (os.indexOf("Window") != -1) { imgedir = WINDOWS_DIR; } else { imgedir = LINUX_DIR; } boolean flag = false; DBService db = null; PreparedStatement st = null; int error = 0; try { DES des = new DES(); des.setKey("wh#@!a59"); db = new DBService(); int n = db.getInt("SELECT MAX(url_id) FROM wht_app_url"); st = db.getConn() .prepareStatement( "insert into wht_app_url(url_id,url,imgedir,imgename,imgedirname) values(?,?,?,?,?)"); db.getConn().setAutoCommit(false); for (int i = 1; i <= sum; i++) { n = n + i; if (QRCodeUtil.encode( URLPATH + n, imgedir + "logo.png", imgedir + "QRcard" + n + ".png", true)) { st.setInt(1, n); st.setString(2, URLPATH + des.decrypt(n + "")); st.setString(3, imgedir); st.setString(4, "QRcard" + n + ".png"); st.setString(5, imgedir + "QRcard" + n + ".png"); st.addBatch(); if ((n + 1) % 50 == 0) { st.executeBatch(); st.clearBatch(); } } else { error++; } } st.executeBatch(); db.getConn().commit(); flag = true; Log.info("生成智能名片二维码失败张数:" + error); } catch (Exception e) { db.rollback(); Log.error("生成智能名片二维码出错:" + e.toString()); } finally { if (null != st) { // st.close(); st = null; } if (null != db) { db.close(); db = null; } } return flag; }
public void clearBatch() throws SQLException { statement.clearBatch(); }
protected Map<UUID, Integer> getPlayerIds(Set<OfflinePlayer> players) throws SQLException { setupStatement_1(mConnection); myAddPlayerStatement.clearBatch(); for (OfflinePlayer player : players) { myAddPlayerStatement.setString(1, player.getPlayer().getUniqueId().toString()); myAddPlayerStatement.setString(2, player.getPlayer().getName()); myAddPlayerStatement.addBatch(); } myAddPlayerStatement.executeBatch(); myAddPlayerStatement.close(); int left = players.size(); Iterator<OfflinePlayer> it = players.iterator(); HashMap<UUID, Integer> ids = new HashMap<UUID, Integer>(); while (left > 0) { PreparedStatement statement; int size = 0; if (left >= 10) { size = 10; statement = mGetPlayerStatement[3]; } else if (left >= 5) { size = 5; statement = mGetPlayerStatement[2]; } else if (left >= 2) { size = 2; statement = mGetPlayerStatement[1]; } else { size = 1; statement = mGetPlayerStatement[0]; } left -= size; ArrayList<OfflinePlayer> temp = new ArrayList<OfflinePlayer>(size); for (int i = 0; i < size; ++i) { OfflinePlayer player = it.next(); temp.add(player); statement.setString(i + 1, player.getUniqueId().toString()); } ResultSet results = statement.executeQuery(); int index = 0; while (results.next()) { OfflinePlayer player = temp.get(index++); if (results.getString(1).equals(player.getUniqueId().toString()) && !results.getString(2).equals(player.getPlayer().getName())) { MobHunting.instance .getLogger() .severe( "Name change detected(1): " + results.getString(2) + " -> " + player.getPlayer().getName() + " UUID=" + player.getUniqueId().toString()); updatePlayerName(player.getPlayer()); } ids.put(UUID.fromString(results.getString(1)), results.getInt(3)); } } return ids; }
static int loadOrder(int whseKount, int distWhseKount, int custDistKount) { int k = 0; int t = 0; PrintWriter outLine = null; PrintWriter outNewOrder = null; try { if (outputFiles == true) { out = new PrintWriter(new FileOutputStream(fileLocation + "order.csv")); System.out.println("\nWriting Order file to: " + fileLocation + "order.csv"); outLine = new PrintWriter(new FileOutputStream(fileLocation + "order-line.csv")); System.out.println("\nWriting Order Line file to: " + fileLocation + "order-line.csv"); outNewOrder = new PrintWriter(new FileOutputStream(fileLocation + "new-order.csv")); System.out.println("\nWriting New Order file to: " + fileLocation + "new-order.csv"); } now = new java.util.Date(); Oorder oorder = new Oorder(); NewOrder new_order = new NewOrder(); OrderLine order_line = new OrderLine(); jdbcIO myJdbcIO = new jdbcIO(); t = (whseKount * distWhseKount * custDistKount); t = (t * 11) + (t / 3); System.out.println( "whse=" + whseKount + ", dist=" + distWhseKount + ", cust=" + custDistKount); System.out.println("\nStart Order-Line-New Load for approx " + t + " rows @ " + now + " ..."); for (int w = 1; w <= whseKount; w++) { for (int d = 1; d <= distWhseKount; d++) { for (int c = 1; c <= custDistKount; c++) { oorder.o_id = c; oorder.o_w_id = w; oorder.o_d_id = d; oorder.o_c_id = jTPCCUtil.randomNumber(1, custDistKount, gen); oorder.o_carrier_id = jTPCCUtil.randomNumber(1, 10, gen); oorder.o_ol_cnt = jTPCCUtil.randomNumber(5, 15, gen); oorder.o_all_local = 1; oorder.o_entry_d = System.currentTimeMillis(); k++; if (outputFiles == false) { myJdbcIO.insertOrder(ordrPrepStmt, oorder); } else { String str = ""; str = str + oorder.o_id + ","; str = str + oorder.o_w_id + ","; str = str + oorder.o_d_id + ","; str = str + oorder.o_c_id + ","; str = str + oorder.o_carrier_id + ","; str = str + oorder.o_ol_cnt + ","; str = str + oorder.o_all_local + ","; Timestamp entry_d = new java.sql.Timestamp(oorder.o_entry_d); str = str + entry_d; out.println(str); } // 900 rows in the NEW-ORDER table corresponding to the last // 900 rows in the ORDER table for that district (i.e., with // NO_O_ID between 2,101 and 3,000) if (c > 2100) { new_order.no_w_id = w; new_order.no_d_id = d; new_order.no_o_id = c; k++; if (outputFiles == false) { myJdbcIO.insertNewOrder(nworPrepStmt, new_order); } else { String str = ""; str = str + new_order.no_w_id + ","; str = str + new_order.no_d_id + ","; str = str + new_order.no_o_id; outNewOrder.println(str); } } // end new order for (int l = 1; l <= oorder.o_ol_cnt; l++) { order_line.ol_w_id = w; order_line.ol_d_id = d; order_line.ol_o_id = c; order_line.ol_number = l; // ol_number order_line.ol_i_id = jTPCCUtil.randomNumber(1, 100000, gen); order_line.ol_delivery_d = oorder.o_entry_d; if (order_line.ol_o_id < 2101) { order_line.ol_amount = 0; } else { // random within [0.01 .. 9,999.99] float f = (float) (jTPCCUtil.randomNumber(1, 999999, gen) / 100.0); order_line.ol_amount = f; // XXX float error line 1. } order_line.ol_supply_w_id = jTPCCUtil.randomNumber(1, numWarehouses, gen); order_line.ol_quantity = 5; order_line.ol_dist_info = jTPCCUtil.randomStr(24); k++; if (outputFiles == false) { myJdbcIO.insertOrderLine(orlnPrepStmt, order_line); } else { String str = ""; str = str + order_line.ol_w_id + ","; str = str + order_line.ol_d_id + ","; str = str + order_line.ol_o_id + ","; str = str + order_line.ol_number + ","; str = str + order_line.ol_i_id + ","; Timestamp delivery_d = new Timestamp(order_line.ol_delivery_d); str = str + delivery_d + ","; str = str + order_line.ol_amount + ","; str = str + order_line.ol_supply_w_id + ","; str = str + order_line.ol_quantity + ","; str = str + order_line.ol_dist_info; outLine.println(str); } if ((k % configCommitCount) == 0) { long tmpTime = new java.util.Date().getTime(); String etStr = " Elasped Time(ms): " + ((tmpTime - lastTimeMS) / 1000.000) + " "; System.out.println(etStr.substring(0, 30) + " Writing record " + k + " of " + t); lastTimeMS = tmpTime; if (outputFiles == false) { ordrPrepStmt.executeBatch(); nworPrepStmt.executeBatch(); orlnPrepStmt.executeBatch(); ordrPrepStmt.clearBatch(); nworPrepStmt.clearBatch(); orlnPrepStmt.clearBatch(); transCommit(); } } } // end for [l] } // end for [c] } // end for [d] } // end for [w] System.out.println(" Writing final records " + k + " of " + t); if (outputFiles == false) { ordrPrepStmt.executeBatch(); nworPrepStmt.executeBatch(); orlnPrepStmt.executeBatch(); } else { outLine.close(); outNewOrder.close(); } transCommit(); now = new java.util.Date(); System.out.println("End Orders Load @ " + now); } catch (Exception e) { e.printStackTrace(); transRollback(); if (outputFiles == true) { outLine.close(); outNewOrder.close(); } } return (k); } // end loadOrder()
/** 执行批量操作 */ public int executeBatchUpdate(Connection con) throws SQLException { int totalUpdatedRows = 0; int currentBatchCount = 0; String preSql = "", curSql = ""; PreparedStatement updateStatement = null; int[] batchRows = null; try { Iterator itor = batchRequestList.iterator(); while (itor.hasNext()) { SqlRequest curReqest = (SqlRequest) itor.next(); curSql = curReqest.getSqlText(); String sqlId = curReqest.getSqlId(); String[] paramNames = curReqest.getParamNames(); Object[] paramValues = curReqest.getParamValues(); int[] paramSqlTypeCodes = curReqest.getParamSqlTypeCodes(); JdaTypePersister[] paramTypePersisters = curReqest.getParamTypePersisters(); ParamValueMode[] paramValueModes = curReqest.getParamValueModes(); JdaSessionImpl session = curReqest.getRequestSession(); /** * 满足批量更新的条件如下: 1:上一个Statement更新进去的数据已经达到Size,则需要执行前面Statement, * 2:当前的SQL与前面执行SQL不同时候,则需要执行前面Statement, 3:到达数据列表的终点,不论1与2,都需要执行当前的Statement */ if (updateStatement == null) updateStatement = SqlRequestHandler.createGeneralStatement(con, curSql); if (currentBatchCount == batchUpdateSize) { // Size已满,需要执行批量更新了 batchRows = updateStatement.executeBatch(); totalUpdatedRows += getUpdateCount(batchRows); updateStatement.clearBatch(); currentBatchCount = 0; } if (curSql.equalsIgnoreCase(preSql)) { ParamFactory.setParamValues( session, sqlId, updateStatement, paramNames, paramValues, paramSqlTypeCodes, paramTypePersisters, paramValueModes); updateStatement.addBatch(); currentBatchCount++; preSql = curSql; } else if (!curSql.equalsIgnoreCase(preSql)) { batchRows = updateStatement.executeBatch(); totalUpdatedRows += getUpdateCount(batchRows); updateStatement.clearBatch(); currentBatchCount = 0; CloseUtil.close(updateStatement); updateStatement = null; updateStatement = SqlRequestHandler.createGeneralStatement(con, curSql); ParamFactory.setParamValues( session, sqlId, updateStatement, paramNames, paramValues, paramSqlTypeCodes, paramTypePersisters, paramValueModes); currentBatchCount++; preSql = curSql; } } if (currentBatchCount > 0) { batchRows = updateStatement.executeBatch(); totalUpdatedRows += getUpdateCount(batchRows); updateStatement.clearBatch(); currentBatchCount = 0; CloseUtil.close(updateStatement); updateStatement = null; } } catch (SQLException e) { if (updateStatement != null) { try { updateStatement.clearBatch(); CloseUtil.close(updateStatement); updateStatement = null; } catch (Exception ee) { } } throw e; } return totalUpdatedRows; }
public void clearBatch() throws SQLException { this.prepare(); ps.clearBatch(); }
/** 插入测试数据 */ public void insertTbService() { DataBaseConnection dbc = DataBaseConnection.getInstance(dbdriver, dburl, username, password); PreparedStatement pstmt = null; try { Long beginTime = System.currentTimeMillis(); BigDecimal lon = new BigDecimal(68216140.000000); BigDecimal lat = new BigDecimal(20810506.000000); BigDecimal add = new BigDecimal(5.000000); Long timer = System.currentTimeMillis(); String no = "京"; String sql = "insert into MEM_TB_SERVICEVIEW(vid,vehicleno,cname,maplon,maplat,speed,corp_id,corp_name,team_id,team_name,utc,alarmcode,cid,PLATE_COLOR_ID,VEHICLETYPE_ID) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; pstmt = dbc.getConnection().prepareStatement(sql); for (int i = 1; i <= 50000; i++) { pstmt.setInt(1, i); pstmt.setString(2, no + i); pstmt.setString(3, "abc"); lon = lon.add(add); lat = lat.add(add); pstmt.setBigDecimal(4, lon); pstmt.setBigDecimal(5, lat); pstmt.setInt(6, 20); pstmt.setInt(7, 15); pstmt.setString(8, "中交兴路"); pstmt.setInt(9, 14); pstmt.setString(10, "默认车队"); timer = timer + 25; pstmt.setLong(11, timer); pstmt.setString(12, "12"); pstmt.setInt(13, 15); pstmt.setInt(14, 1); pstmt.setInt(15, 35); pstmt.addBatch(); // 批量保存 if (i % 1000 == 0) { pstmt.executeBatch(); pstmt.clearBatch(); Long endTime = System.currentTimeMillis(); System.out.println("pstmt+batch:" + (endTime - beginTime) / 1000 + "秒"); } } Long endTimes = System.currentTimeMillis(); System.out.println("pstmt+batch:" + (endTimes - beginTime) / 1000 + "秒"); // pstmt.close(); } catch (Exception e) { e.printStackTrace(); } finally { // 关闭数据库连接 try { dbc.close(pstmt, null, dbc.getConnection()); } catch (Exception e) { e.printStackTrace(); } } }
static int loadStock(int whseKount, int itemKount) { int k = 0; int t = 0; int randPct = 0; int len = 0; int startORIGINAL = 0; try { now = new java.util.Date(); t = (whseKount * itemKount); System.out.println("\nStart Stock Load for " + t + " units @ " + now + " ..."); if (outputFiles == true) { out = new PrintWriter(new FileOutputStream(fileLocation + "stock.csv")); System.out.println("\nWriting Stock file to: " + fileLocation + "stock.csv"); } Stock stock = new Stock(); for (int i = 1; i <= itemKount; i++) { for (int w = 1; w <= whseKount; w++) { stock.s_i_id = i; stock.s_w_id = w; stock.s_quantity = jTPCCUtil.randomNumber(10, 100, gen); stock.s_ytd = 0; stock.s_order_cnt = 0; stock.s_remote_cnt = 0; // s_data randPct = jTPCCUtil.randomNumber(1, 100, gen); len = jTPCCUtil.randomNumber(26, 50, gen); if (randPct > 10) { // 90% of time i_data isa random string of length [26 .. 50] stock.s_data = jTPCCUtil.randomStr(len); } else { // 10% of time i_data has "ORIGINAL" crammed somewhere in middle startORIGINAL = jTPCCUtil.randomNumber(2, (len - 8), gen); stock.s_data = jTPCCUtil.randomStr(startORIGINAL - 1) + "ORIGINAL" + jTPCCUtil.randomStr(len - startORIGINAL - 9); } stock.s_dist_01 = jTPCCUtil.randomStr(24); stock.s_dist_02 = jTPCCUtil.randomStr(24); stock.s_dist_03 = jTPCCUtil.randomStr(24); stock.s_dist_04 = jTPCCUtil.randomStr(24); stock.s_dist_05 = jTPCCUtil.randomStr(24); stock.s_dist_06 = jTPCCUtil.randomStr(24); stock.s_dist_07 = jTPCCUtil.randomStr(24); stock.s_dist_08 = jTPCCUtil.randomStr(24); stock.s_dist_09 = jTPCCUtil.randomStr(24); stock.s_dist_10 = jTPCCUtil.randomStr(24); k++; if (outputFiles == false) { stckPrepStmt.setLong(1, stock.s_i_id); stckPrepStmt.setLong(2, stock.s_w_id); stckPrepStmt.setDouble(3, stock.s_quantity); stckPrepStmt.setDouble(4, stock.s_ytd); stckPrepStmt.setLong(5, stock.s_order_cnt); stckPrepStmt.setLong(6, stock.s_remote_cnt); stckPrepStmt.setString(7, stock.s_data); stckPrepStmt.setString(8, stock.s_dist_01); stckPrepStmt.setString(9, stock.s_dist_02); stckPrepStmt.setString(10, stock.s_dist_03); stckPrepStmt.setString(11, stock.s_dist_04); stckPrepStmt.setString(12, stock.s_dist_05); stckPrepStmt.setString(13, stock.s_dist_06); stckPrepStmt.setString(14, stock.s_dist_07); stckPrepStmt.setString(15, stock.s_dist_08); stckPrepStmt.setString(16, stock.s_dist_09); stckPrepStmt.setString(17, stock.s_dist_10); stckPrepStmt.addBatch(); if ((k % configCommitCount) == 0) { long tmpTime = new java.util.Date().getTime(); String etStr = " Elasped Time(ms): " + ((tmpTime - lastTimeMS) / 1000.000) + " "; System.out.println(etStr.substring(0, 30) + " Writing record " + k + " of " + t); lastTimeMS = tmpTime; stckPrepStmt.executeBatch(); stckPrepStmt.clearBatch(); transCommit(); } } else { String str = ""; str = str + stock.s_i_id + ","; str = str + stock.s_w_id + ","; str = str + stock.s_quantity + ","; str = str + stock.s_ytd + ","; str = str + stock.s_order_cnt + ","; str = str + stock.s_remote_cnt + ","; str = str + stock.s_data + ","; str = str + stock.s_dist_01 + ","; str = str + stock.s_dist_02 + ","; str = str + stock.s_dist_03 + ","; str = str + stock.s_dist_04 + ","; str = str + stock.s_dist_05 + ","; str = str + stock.s_dist_06 + ","; str = str + stock.s_dist_07 + ","; str = str + stock.s_dist_08 + ","; str = str + stock.s_dist_09 + ","; str = str + stock.s_dist_10; out.println(str); if ((k % configCommitCount) == 0) { long tmpTime = new java.util.Date().getTime(); String etStr = " Elasped Time(ms): " + ((tmpTime - lastTimeMS) / 1000.000) + " "; System.out.println(etStr.substring(0, 30) + " Writing record " + k + " of " + t); lastTimeMS = tmpTime; } } } // end for [w] } // end for [i] long tmpTime = new java.util.Date().getTime(); String etStr = " Elasped Time(ms): " + ((tmpTime - lastTimeMS) / 1000.000) + " "; System.out.println(etStr.substring(0, 30) + " Writing final records " + k + " of " + t); lastTimeMS = tmpTime; if (outputFiles == false) { stckPrepStmt.executeBatch(); } transCommit(); now = new java.util.Date(); System.out.println("End Stock Load @ " + now); } catch (SQLException se) { System.out.println(se.getMessage()); transRollback(); } catch (Exception e) { e.printStackTrace(); transRollback(); } return (k); } // end loadStock()
private void insertBatches() throws DaoException, SQLException, InterruptedException { boolean finished = false; Connection cnx = ds.getConnection(); if (isPostGisLoader) { try { ((org.postgresql.PGConnection) cnx) .addDataType("geometry", Class.forName("org.postgis.PGgeometry")); // ((org.postgresql.PGConnection) cnx).addDataType("geometry", // Class.forName("org.postgis.Multipolygon")); } catch (ClassNotFoundException e) { throw new DaoException( "Could not find PostGIS geometry type. Is the PostGIS library in the class path?: " + e.getMessage()); } } PreparedStatement statement = null; try { String[] names = new String[fields.length]; String[] questions = new String[fields.length]; for (int i = 0; i < fields.length; i++) { names[i] = fields[i]; questions[i] = "?"; } String sql = "INSERT INTO " + table + "(" + StringUtils.join(names, ",") + ") " + "VALUES (" + StringUtils.join(questions, ",") + ");"; statement = cnx.prepareStatement(sql); while (!finished && inserterState != InserterState.FAILED) { // accumulate batch int batchSize = 0; while (!finished && batchSize < BATCH_SIZE && inserterState != InserterState.FAILED) { Object row[] = rowBuffer.poll(100, TimeUnit.MILLISECONDS); if (row == null) { // do nothing } else if (row[0] == POSION_PILL) { rowBuffer.put(new Object[] {POSION_PILL}); finished = true; } else { batchSize++; for (int i = 0; i < row.length; i++) { if (row[i] != null && row[i].getClass().equals(java.lang.Character.class)) statement.setObject(i + 1, row[i].toString()); else statement.setObject(i + 1, row[i]); } statement.addBatch(); } } try { statement.executeBatch(); cnx.commit(); } catch (SQLException e) { cnx.rollback(); while (e != null) { LOG.error("insert batch failed, attempting to continue:", e); e = e.getNextException(); } } statement.clearBatch(); } } finally { if (statement != null) { JDBCUtils.safeClose(statement); } AbstractSqlDao.quietlyCloseConn(cnx); } }
static int loadCust(int whseKount, int distWhseKount, int custDistKount) { int k = 0; int t = 0; Customer customer = new Customer(); History history = new History(); PrintWriter outHist = null; try { now = new java.util.Date(); if (outputFiles == true) { out = new PrintWriter(new FileOutputStream(fileLocation + "customer.csv")); System.out.println("\nWriting Customer file to: " + fileLocation + "customer.csv"); outHist = new PrintWriter(new FileOutputStream(fileLocation + "cust-hist.csv")); System.out.println("\nWriting Customer History file to: " + fileLocation + "cust-hist.csv"); } t = (whseKount * distWhseKount * custDistKount * 2); System.out.println("\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++) { 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) (jTPCCUtil.randomNumber(1, 5000, gen) / 10000.0); if (jTPCCUtil.randomNumber(1, 100, gen) <= 10) { customer.c_credit = "BC"; // 10% Bad Credit } else { customer.c_credit = "GC"; // 90% Good Credit } customer.c_last = jTPCCUtil.getLastName(gen); customer.c_first = jTPCCUtil.randomStr(jTPCCUtil.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 = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(10, 20, gen)); customer.c_street_2 = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(10, 20, gen)); customer.c_city = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(10, 20, gen)); customer.c_state = jTPCCUtil.randomStr(3).toUpperCase(); customer.c_zip = "123456789"; customer.c_phone = "(732)744-1700"; customer.c_since = sysdate.getTime(); customer.c_middle = "OE"; customer.c_data = jTPCCUtil.randomStr(jTPCCUtil.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.getTime(); history.h_amount = 10; history.h_data = jTPCCUtil.randomStr(jTPCCUtil.randomNumber(10, 24, gen)); k = k + 2; if (outputFiles == false) { custPrepStmt.setLong(1, customer.c_id); custPrepStmt.setLong(2, customer.c_d_id); custPrepStmt.setLong(3, customer.c_w_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.setDouble(11, customer.c_payment_cnt); custPrepStmt.setDouble(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); Timestamp since = new Timestamp(customer.c_since); custPrepStmt.setTimestamp(19, 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); Timestamp hdate = new Timestamp(history.h_date); histPrepStmt.setTimestamp(6, hdate); 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) + " "; System.out.println(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 + ","; Timestamp hdate = new Timestamp(history.h_date); str = str + hdate + ","; 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) + " "; System.out.println(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) + " "; System.out.println(etStr.substring(0, 30) + " Writing record " + k + " of " + t); lastTimeMS = tmpTime; custPrepStmt.executeBatch(); histPrepStmt.executeBatch(); transCommit(); now = new java.util.Date(); if (outputFiles == true) { outHist.close(); } System.out.println("End Cust-Hist Data Load @ " + now); } catch (SQLException se) { System.out.println(se.getMessage()); transRollback(); if (outputFiles == true) { outHist.close(); } } catch (Exception e) { e.printStackTrace(); transRollback(); if (outputFiles == true) { outHist.close(); } } return (k); } // end loadCust()