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()