@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)]");
}
@Override
protected void starting(Description description) {
try (PreparedStatement ps =
spliceClassWatcher.prepareStatement(
String.format("insert into %s (a,b) values (?,?)", baseTable))) {
for (int i = 0; i < 10; i++) {
ps.setInt(1, i);
ps.setInt(2, 2 * i);
ps.addBatch();
}
ps.executeBatch();
} catch (Exception e) {
throw new RuntimeException(e);
}
try (PreparedStatement ps =
spliceClassWatcher.prepareStatement(
String.format("insert into %s (b,c) values (?,?)", rightTable))) {
for (int i = 0; i < 10; i++) {
ps.setInt(1, 2 * i);
ps.setInt(2, i);
ps.addBatch();
}
ps.executeBatch();
} catch (Exception e) {
throw new RuntimeException(e);
}
}
@Test
public void retainKeysInBatch() throws SQLException {
stat.executeUpdate("create table test (c1, c2);");
PreparedStatement prep = conn.prepareStatement("insert into test values (?, ?);");
prep.setInt(1, 10);
prep.setString(2, "ten");
prep.addBatch();
prep.setInt(1, 100);
prep.setString(2, "hundred");
prep.addBatch();
prep.setString(2, "one hundred");
prep.addBatch();
prep.setInt(1, 1000);
prep.setString(2, "thousand");
prep.addBatch();
prep.executeBatch();
prep.close();
ResultSet rs = stat.executeQuery("select * from test;");
assertTrue(rs.next());
assertEquals(10, rs.getInt(1));
assertEquals("ten", rs.getString(2));
assertTrue(rs.next());
assertEquals(100, rs.getInt(1));
assertEquals("hundred", rs.getString(2));
assertTrue(rs.next());
assertEquals(100, rs.getInt(1));
assertEquals("one hundred", rs.getString(2));
assertTrue(rs.next());
assertEquals(1000, rs.getInt(1));
assertEquals("thousand", rs.getString(2));
assertFalse(rs.next());
rs.close();
}
public void doSQL() throws Exception {
Class.forName("org.sqlite.JDBC");
Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");
Statement stat = conn.createStatement();
stat.executeUpdate("drop table if exists people;");
stat.executeUpdate("create table people (name, occupation);");
PreparedStatement prep = conn.prepareStatement("insert into people values (?, ?);");
prep.setString(1, "Gandhi");
prep.setString(2, "politics");
prep.addBatch();
prep.setString(1, "Turing");
prep.setString(2, "computers");
prep.addBatch();
prep.setString(1, "Wittgenstein");
prep.setString(2, "smartypants");
prep.addBatch();
conn.setAutoCommit(false);
prep.executeBatch();
conn.setAutoCommit(true);
ResultSet rs = stat.executeQuery("select * from people;");
while (rs.next()) {
System.out.println("name = " + rs.getString("name"));
System.out.println("job = " + rs.getString("occupation"));
}
rs.close();
conn.close();
}
@Test
public void testAddBatch() throws SQLException {
TGroupConnection conn = null;
PreparedStatement stat = null;
try {
conn = tgds.getConnection();
stat = conn.prepareStatement("update test set type=? where id = ?");
stat.setInt(1, 1);
stat.setString(2, "2askjfoue33");
stat.addBatch();
stat.setInt(1, 2);
stat.setString(2, "retrtorut48");
stat.addBatch();
int[] affectedRow = stat.executeBatch();
System.out.println(Arrays.toString(affectedRow));
MockDataSource.showTrace();
Assert.assertTrue(MockDataSource.hasMethod("db", "db1", "executeBatch"));
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
}
if (stat != null) {
try {
stat.close();
} catch (SQLException e) {
}
}
}
}
}
private void testContinueBatch01() throws SQLException {
trace("testContinueBatch01");
int[] batchUpdates = {0, 0, 0};
int buCountLen = 0;
try {
String sPrepStmt = COFFEE_UPDATE_SET;
trace("Prepared Statement String:" + sPrepStmt);
prep = conn.prepareStatement(sPrepStmt);
// Now add a legal update to the batch
prep.setInt(1, 1);
prep.setString(2, "Continue-1");
prep.setString(3, "COFFEE-1");
prep.addBatch();
// Now add an illegal update to the batch by
// forcing a unique constraint violation
// Try changing the key_id of row 3 to 1.
prep.setInt(1, 1);
prep.setString(2, "Invalid");
prep.setString(3, "COFFEE-3");
prep.addBatch();
// Now add a second legal update to the batch
// which will be processed ONLY if the driver supports
// continued batch processing according to 6.2.2.3
// of the J2EE platform spec.
prep.setInt(1, 2);
prep.setString(2, "Continue-2");
prep.setString(3, "COFFEE-2");
prep.addBatch();
// The executeBatch() method will result in a
// BatchUpdateException
prep.executeBatch();
} catch (BatchUpdateException b) {
trace("expected BatchUpdateException");
batchUpdates = b.getUpdateCounts();
buCountLen = batchUpdates.length;
}
if (buCountLen == 1) {
trace("no continued updates - OK");
return;
} else if (buCountLen == 3) {
trace("Driver supports continued updates.");
// Check to see if the third row from the batch was added
String query = COFFEE_SELECT_CONTINUED;
trace("Query is: " + query);
ResultSet rs = stat.executeQuery(query);
rs.next();
int count = rs.getInt(1);
rs.close();
stat.close();
trace("Count val is: " + count);
// make sure that we have the correct error code for
// the failed update.
if (!(batchUpdates[1] == -3 && count == 1)) {
fail("insert failed");
}
}
}
/** Test batched prepared statements. */
public void testPrepStmtBatch() throws Exception {
Statement stmt = con.createStatement();
stmt.execute("create table #testbatch (id int, data varchar(255))");
PreparedStatement pstmt = con.prepareStatement("INSERT INTO #testbatch VALUES (?, ?)");
for (int i = 0; i < 5; i++) {
if (i == 2) {
pstmt.setString(1, "xxx");
} else {
pstmt.setInt(1, i);
}
pstmt.setString(2, "This is line " + i);
pstmt.addBatch();
}
int x[];
try {
x = pstmt.executeBatch();
} catch (BatchUpdateException e) {
x = e.getUpdateCounts();
}
if (con.getMetaData().getDatabaseProductName().toLowerCase().startsWith("microsoft")) {
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(EXECUTE_FAILED, x[2]);
assertEquals(EXECUTE_FAILED, x[3]);
assertEquals(EXECUTE_FAILED, x[4]);
} else {
// Sybase - Entire batch fails due to data conversion error
// detected in statement 3
assertEquals(5, x.length);
assertEquals(EXECUTE_FAILED, x[0]);
assertEquals(EXECUTE_FAILED, x[1]);
assertEquals(EXECUTE_FAILED, x[2]);
assertEquals(EXECUTE_FAILED, x[3]);
assertEquals(EXECUTE_FAILED, x[4]);
}
// Now without errors
stmt.execute("TRUNCATE TABLE #testbatch");
for (int i = 0; i < 5; i++) {
pstmt.setInt(1, i);
pstmt.setString(2, "This is line " + i);
pstmt.addBatch();
}
x = pstmt.executeBatch();
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(1, x[2]);
assertEquals(1, x[3]);
assertEquals(1, x[4]);
}
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);
}
private void testExecuteBatch01() throws SQLException {
trace("testExecuteBatch01");
int i = 0;
int[] retValue = {0, 0, 0};
int updCountLength = 0;
String sPrepStmt = COFFEE_UPDATE;
trace("Prepared Statement String:" + sPrepStmt);
// get the PreparedStatement object
prep = conn.prepareStatement(sPrepStmt);
prep.setInt(1, 1);
prep.addBatch();
prep.setInt(1, 2);
prep.addBatch();
prep.setInt(1, 3);
prep.addBatch();
int[] updateCount = prep.executeBatch();
updCountLength = updateCount.length;
trace("Successfully Updated");
trace("updateCount Length:" + updCountLength);
if (updCountLength != 3) {
fail("executeBatch");
} else {
trace("executeBatch executes the Batch of SQL statements");
}
// 1 is the number that is set First for Type Id in Prepared Statement
String query1 = "SELECT COUNT(*) FROM TEST WHERE TYPE_ID=1";
// 2 is the number that is set second for Type id in Prepared Statement
String query2 = "SELECT COUNT(*) FROM TEST WHERE TYPE_ID=2";
// 3 is the number that is set Third for Type id in Prepared Statement
String query3 = "SELECT COUNT(*) FROM TEST WHERE TYPE_ID=3";
ResultSet rs = stat.executeQuery(query1);
rs.next();
retValue[i++] = rs.getInt(1);
rs = stat.executeQuery(query2);
rs.next();
retValue[i++] = rs.getInt(1);
rs = stat.executeQuery(query3);
rs.next();
retValue[i++] = rs.getInt(1);
trace("retValue length : " + retValue.length);
for (int j = 0; j < updateCount.length; j++) {
trace("UpdateCount Value:" + updateCount[j]);
trace("RetValue : " + retValue[j]);
if (updateCount[j] != retValue[j]) {
fail("j=" + j + " right:" + retValue[j]);
}
}
}
@Override
public void saveUsageNetworks(List<UsageNetworkVO> usageNetworks) {
Transaction txn = Transaction.currentTxn();
try {
txn.start();
String sql = INSERT_USAGE_NETWORK;
PreparedStatement pstmt = null;
pstmt =
txn.prepareAutoCloseStatement(
sql); // in reality I just want CLOUD_USAGE dataSource connection
for (UsageNetworkVO usageNetwork : usageNetworks) {
pstmt.setLong(1, usageNetwork.getAccountId());
pstmt.setLong(2, usageNetwork.getZoneId());
pstmt.setLong(3, usageNetwork.getHostId());
pstmt.setString(4, usageNetwork.getHostType());
pstmt.setLong(5, usageNetwork.getNetworkId());
pstmt.setLong(6, usageNetwork.getBytesSent());
pstmt.setLong(7, usageNetwork.getBytesReceived());
pstmt.setLong(8, usageNetwork.getAggBytesReceived());
pstmt.setLong(9, usageNetwork.getAggBytesSent());
pstmt.setLong(10, usageNetwork.getEventTimeMillis());
pstmt.addBatch();
}
pstmt.executeBatch();
txn.commit();
} catch (Exception ex) {
txn.rollback();
s_logger.error("error saving usage_network to cloud_usage db", ex);
throw new CloudRuntimeException(ex.getMessage());
}
}
public String addToDB() {
if (wordExists()) {
return String.valueOf(
ChatColor.RED
+ "The word "
+ ChatColor.GOLD
+ word.getName()
+ ChatColor.RED
+ " already exists!");
} else {
try {
Connection con = null;
if (sqltype.equals(SQLType.SQLite)) con = sqlite.getConnection();
if (sqltype.equals(SQLType.MySQL)) con = mysql.getConnection();
PreparedStatement p = con.prepareStatement(Query.INSERT_INTO.value());
p.setString(1, word.getName());
p.setString(2, word.getGroup());
p.addBatch();
con.setAutoCommit(false);
p.executeBatch();
con.setAutoCommit(true);
return String.valueOf(
ChatColor.GREEN
+ "Word "
+ ChatColor.GOLD
+ word.getName()
+ ChatColor.GREEN
+ " has been successfully added!");
} catch (SQLException e) {
plugin.sendErr(
"Error while adding the word '"
+ word.getName()
+ "' to the database. Error message: "
+ e.getMessage()
+ " ERROR CODE: "
+ e.getErrorCode());
e.printStackTrace();
return String.valueOf(
ChatColor.RED
+ "Error adding the word '"
+ ChatColor.GOLD
+ word.getName()
+ ChatColor.RED
+ "' Please check the console for more info.");
} catch (Exception e) {
plugin.sendErr(
"Unknown error while adding the word "
+ word.getName()
+ " to the database. Stacktrace:");
e.printStackTrace();
return String.valueOf(
ChatColor.RED
+ "Error adding the word '"
+ ChatColor.GOLD
+ word.getName()
+ ChatColor.RED
+ "' Please check the console for more info.");
}
}
}
public void processHostTaskData() {
try {
PreparedStatement pstmt = sqlConnection.prepareStatement(Queries.getSqlInsertHostTaskInfo());
AggregationOutput aggregationOutput =
mongoTask.getAggregatedOutput("task", Queries.getHostTaskInfoQuery());
for (DBObject obj : aggregationOutput.results()) {
String timeStamp = (String) obj.get("timestamp");
String action = (String) obj.get("action");
String target = (String) obj.get("target");
double timeTaken = Double.parseDouble(obj.get("timeTaken").toString());
pstmt.setTimestamp(
1,
new java.sql.Timestamp(
MongoAggregationHelper.jsonDateFormat.parse(timeStamp).getTime()));
pstmt.setString(2, target);
pstmt.setString(3, action);
pstmt.setDouble(4, timeTaken);
pstmt.setInt(5, 1);
pstmt.addBatch();
}
pstmt.executeBatch();
} catch (SQLException | ParseException s) {
s.printStackTrace();
}
}
public static void main2(String[] args) {
AdjacentLocListGenerator generator = new AdjacentLocListGenerator();
Connection conn;
try {
conn = DataLoader.getBeijingConn();
AdjacentLocList list = generator.getListFromDB(conn);
conn.setAutoCommit(false);
PreparedStatement stmt =
conn.prepareStatement(
"INSERT INTO AdjacentLocation_Clustered"
+ "(SiteId1, SiteId2, UserCount, TotalCount)"
+ "VALUES (?, ?, ?, ?)");
for (Site loc : list.getSites().values()) {
for (AdjacentLocPair pair : loc.getNextSites()) {
int siteId1 = pair.getSite1().getSiteId();
int siteId2 = pair.getSite2().getSiteId();
if (pair.getUsersCount() == 0) continue;
stmt.setInt(1, siteId1);
stmt.setInt(2, siteId2);
stmt.setInt(3, pair.getUsersCount());
stmt.setInt(4, pair.getTotalCount());
stmt.addBatch();
}
stmt.executeBatch();
conn.commit();
System.out.println(loc.getSiteId() + " succeed.");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Override
public void saveAchievements(Set<AchievementStore> achievements) throws DataStoreException {
try {
HashSet<OfflinePlayer> names = new HashSet<OfflinePlayer>();
for (AchievementStore achievement : achievements) names.add(achievement.player);
Map<UUID, Integer> ids = getPlayerIds(names);
for (AchievementStore achievement : achievements) {
mRecordAchievementStatement.setInt(1, ids.get(achievement.player.getUniqueId()));
mRecordAchievementStatement.setString(2, achievement.id);
mRecordAchievementStatement.setDate(3, new Date(System.currentTimeMillis()));
mRecordAchievementStatement.setInt(4, achievement.progress);
mRecordAchievementStatement.addBatch();
}
mRecordAchievementStatement.executeBatch();
mConnection.commit();
} catch (SQLException e) {
rollback();
throw new DataStoreException(e);
}
}
protected void writeTile(int x, int y, int z, byte[] tileData) throws SQLException, IOException {
prepStmt.setInt(1, x);
prepStmt.setInt(2, y);
prepStmt.setInt(3, 17 - z);
prepStmt.setBytes(4, tileData);
prepStmt.addBatch();
}
/* goodB2G1() - use badsource and goodsink by changing second IO.STATIC_FINAL_FIVE==5 to IO.STATIC_FINAL_FIVE!=5 */
private void goodB2G1() throws Throwable {
String data;
if (IO.STATIC_FINAL_FIVE == 5) {
/* get environment variable ADD */
/* POTENTIAL FLAW: Read data from an environment variable */
data = System.getenv("ADD");
} else {
/* INCIDENTAL: CWE 561 Dead Code, the code below will never run
* but ensure data is inititialized before the Sink to avoid compiler errors */
data = null;
}
if (IO.STATIC_FINAL_FIVE != 5) {
/* INCIDENTAL: CWE 561 Dead Code, the code below will never run */
IO.writeLine("Benign, fixed string");
} else {
if (data != null) {
String names[] = data.split("-");
int successCount = 0;
Connection dbConnection = null;
PreparedStatement sqlStatement = null;
try {
/* FIX: Use prepared statement and executeBatch (properly) */
dbConnection = IO.getDBConnection();
sqlStatement =
dbConnection.prepareStatement("update users set hitcount=hitcount+1 where name=?");
for (int i = 0; i < names.length; i++) {
sqlStatement.setString(1, names[i]);
sqlStatement.addBatch();
}
int resultsArray[] = sqlStatement.executeBatch();
for (int i = 0; i < names.length; i++) {
if (resultsArray[i] > 0) {
successCount++;
}
}
IO.writeLine("Succeeded in " + successCount + " out of " + names.length + " queries.");
} catch (SQLException exceptSql) {
IO.logger.log(Level.WARNING, "Error getting database connection", exceptSql);
} finally {
try {
if (sqlStatement != null) {
sqlStatement.close();
}
} catch (SQLException exceptSql) {
IO.logger.log(Level.WARNING, "Error closing PreparedStatement", exceptSql);
}
try {
if (dbConnection != null) {
dbConnection.close();
}
} catch (SQLException exceptSql) {
IO.logger.log(Level.WARNING, "Error closing Connection", exceptSql);
}
}
}
}
}
private OpResult addEntities(
String query,
Collection h,
BiFunction<Object, PreparedStatement, PreparedStatement> setFunc) {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = getJdbcConnection();
statement = connection.prepareStatement(query);
connection.setAutoCommit(false);
for (Object entity : h) {
setFunc.apply(entity, statement);
statement.addBatch();
}
statement.executeBatch();
connection.commit();
} catch (Exception ex) {
LOG.error("error in querying hdfs_sensitivity_entity table", ex);
} finally {
try {
if (statement != null) statement.close();
if (connection != null) connection.close();
} catch (Exception ex) {
LOG.error("error in closing database resources", ex);
}
}
return new OpResult();
}
@Override
public boolean put(List<TransactionRecord> trs) {
PreparedStatement statement = null;
try {
this.connection.setAutoCommit(false);
statement = this.connection.prepareStatement("insert into t_transaction values (?, ?)");
for (TransactionRecord tr : trs) {
statement.setLong(1, tr.getOffset());
statement.setString(2, tr.getProducerGroup());
statement.addBatch();
}
int[] executeBatch = statement.executeBatch();
this.connection.commit();
this.totalRecordsValue.addAndGet(updatedRows(executeBatch));
return true;
} catch (Exception e) {
log.warn("createDB Exception", e);
return false;
} finally {
if (null != statement) {
try {
statement.close();
} catch (SQLException e) {
}
}
}
}
public void insertProductList(List<Product> products) throws Throwable {
conn.setAutoCommit(false);
PreparedStatement statement =
conn.prepareStatement(
"INSERT INTO `yamaloo`.`product`"
+ " (`ProductName`,`BrandID`, `Description`, `MainPic`, `Price`,`RawSerialNo`)"
+ " VALUES (?,?,?,?,?,?);");
for (Product product : products) {
statement.setString(1, product.getName());
statement.setString(2, product.getDescription());
statement.setString(3, product.getBrandID());
statement.setString(4, product.getpictureURL());
statement.setString(5, product.getPrice());
statement.setString(6, product.getRawSerialNumber());
statement.addBatch();
}
statement.executeBatch();
conn.commit();
statement.close();
conn.setAutoCommit(true);
}
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 static void main(String[] args) {
Connection conn = null;
try {
conn = DataLoader.getBeijingConn();
Statement stmt = conn.createStatement();
PreparedStatement stmt2 =
conn.prepareStatement("INSERT INTO ttttttttt" + "(SiteId, ConnHour)" + " VALUES (?, ?)");
ResultSet rs = stmt.executeQuery("SELECT * FROM FilteredSiteInfo");
while (rs.next()) {
int siteid = rs.getInt("SiteId");
for (int i = 0; i < 24; ++i) {
stmt2.setInt(1, siteid);
stmt2.setInt(2, i);
stmt2.addBatch();
}
}
stmt2.executeBatch();
conn.commit();
} catch (Exception ex) {
}
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void executeBatch(String sql, Object args[][]) throws SQLException, FileNotFoundException {
PreparedStatement prep = connection.prepareStatement(sql);
int i;
for (Object[] a : args) {
i = 1;
for (Object b : a) {
if (b instanceof String) {
prep.setString(i, (String) b);
}
if (b instanceof Integer) {
prep.setInt(i, (Integer) b);
}
if (b instanceof File) {
FileInputStream fis = new FileInputStream((File) b);
prep.setBinaryStream(i, fis);
}
if (b instanceof FileInputStream) {
prep.setBinaryStream(i, (InputStream) b);
}
if (b instanceof BufferedImage) {
byte[] buffer =
((DataBufferByte) ((BufferedImage) b).getRaster().getDataBuffer()).getData();
InputStream ist = new ByteArrayInputStream(buffer);
prep.setBinaryStream(i, ist);
}
i++;
}
prep.addBatch();
}
connection.setAutoCommit(false);
prep.executeBatch();
connection.setAutoCommit(true);
}
public void processHostData() {
try {
PreparedStatement pstmt = sqlConnection.prepareStatement(Queries.getSqlInsertHostInfo());
AggregationOutput aggregationOutput =
mongoTask.getAggregatedOutput("host", Queries.getHostInfoQuery());
for (DBObject obj : aggregationOutput.results()) {
String hostname = (String) obj.get("hostname");
Double cpuUsage = Double.parseDouble(obj.get("cpuusage").toString());
Double cpuMax = Double.parseDouble(obj.get("cpumax").toString());
Double cpuPercent = Double.parseDouble(obj.get("cpupercentage").toString());
Double memUsage = Double.parseDouble(obj.get("memusage").toString());
Double memMax = Double.parseDouble(obj.get("memmax").toString());
Double memPercent = Double.parseDouble(obj.get("mempercentage").toString());
Double upTime = Double.parseDouble(obj.get("uptime").toString());
Double tx = Double.parseDouble(obj.get("tx").toString());
Double rx = Double.parseDouble(obj.get("rx").toString());
pstmt.setTimestamp(1, new java.sql.Timestamp(timeStamp.getTime()));
pstmt.setString(2, hostname);
pstmt.setDouble(3, cpuUsage);
pstmt.setDouble(4, cpuMax);
pstmt.setDouble(5, cpuPercent);
pstmt.setDouble(6, memUsage);
pstmt.setDouble(7, memMax);
pstmt.setDouble(8, memPercent);
pstmt.setDouble(9, upTime);
pstmt.setDouble(10, tx);
pstmt.setDouble(11, rx);
pstmt.addBatch();
}
pstmt.executeBatch();
} catch (SQLException s) {
s.printStackTrace();
}
}
@Override
public void updateAccounts(List<AccountVO> accounts) {
Transaction txn = Transaction.currentTxn();
try {
txn.start();
String sql = UPDATE_ACCOUNT;
PreparedStatement pstmt = null;
pstmt =
txn.prepareAutoCloseStatement(
sql); // in reality I just want CLOUD_USAGE dataSource connection
for (AccountVO acct : accounts) {
pstmt.setString(1, acct.getAccountName());
Date removed = acct.getRemoved();
if (removed == null) {
pstmt.setString(2, null);
} else {
pstmt.setString(
2, DateUtil.getDateDisplayString(TimeZone.getTimeZone("GMT"), acct.getRemoved()));
}
pstmt.setLong(3, acct.getId());
pstmt.addBatch();
}
pstmt.executeBatch();
txn.commit();
} catch (Exception ex) {
txn.rollback();
s_logger.error("error saving account to cloud_usage db", ex);
throw new CloudRuntimeException(ex.getMessage());
}
}
private void saveBuildings(int building_id, String customers, Connection conn) throws Exception {
PreparedStatement psDelete = null;
PreparedStatement psAdd = null;
try {
psDelete =
conn.prepareStatement("delete from maps.building_to_customers where building_id=?");
psDelete.setInt(1, building_id);
psDelete.executeUpdate();
if (customers.length() > 0) {
String cusIds[] = customers.split(",");
psAdd =
conn.prepareStatement(
"insert into maps.building_to_customers (building_id,cusid) values (?,?)");
for (String cusId : cusIds) {
psAdd.setInt(1, building_id);
psAdd.setInt(2, new Integer(cusId));
psAdd.addBatch();
}
psAdd.executeBatch();
}
} finally {
try {
psDelete.close();
} catch (Exception e2) {
// TODO: handle exception
}
try {
psAdd.close();
} catch (Exception e2) {
// TODO: handle exception
}
}
}
@Override
public void updateUserStats(List<UserStatisticsVO> userStats) {
Transaction txn = Transaction.currentTxn();
try {
txn.start();
String sql = UPDATE_USER_STATS;
PreparedStatement pstmt = null;
pstmt =
txn.prepareAutoCloseStatement(
sql); // in reality I just want CLOUD_USAGE dataSource connection
for (UserStatisticsVO userStat : userStats) {
pstmt.setLong(1, userStat.getNetBytesReceived());
pstmt.setLong(2, userStat.getNetBytesSent());
pstmt.setLong(3, userStat.getCurrentBytesReceived());
pstmt.setLong(4, userStat.getCurrentBytesSent());
pstmt.setLong(5, userStat.getAggBytesReceived());
pstmt.setLong(6, userStat.getAggBytesSent());
pstmt.setLong(7, userStat.getId());
pstmt.addBatch();
}
pstmt.executeBatch();
txn.commit();
} catch (Exception ex) {
txn.rollback();
s_logger.error("error saving user stats to cloud_usage db", ex);
throw new CloudRuntimeException(ex.getMessage());
}
}
private int[] batch(Config config, Connection conn, String sql, Object[][] paras, int batchSize)
throws SQLException {
if (paras == null || paras.length == 0)
throw new IllegalArgumentException("The paras array length must more than 0.");
if (batchSize < 1) throw new IllegalArgumentException("The batchSize must more than 0.");
int counter = 0;
int pointer = 0;
int[] result = new int[paras.length];
PreparedStatement pst = conn.prepareStatement(sql);
for (int i = 0; i < paras.length; i++) {
for (int j = 0; j < paras[i].length; j++) {
Object value = paras[i][j];
if (config.dialect.isOracle() && value instanceof java.sql.Date)
pst.setDate(j + 1, (java.sql.Date) value);
else pst.setObject(j + 1, value);
}
pst.addBatch();
if (++counter >= batchSize) {
counter = 0;
int[] r = pst.executeBatch();
conn.commit();
for (int k = 0; k < r.length; k++) result[pointer++] = r[k];
}
}
int[] r = pst.executeBatch();
conn.commit();
for (int k = 0; k < r.length; k++) result[pointer++] = r[k];
DbKit.closeQuietly(pst);
return result;
}
private int performDeleteBlob(IdFilter filter, Lock lock) throws FeatureStoreException {
int deleted = 0;
PreparedStatement stmt = null;
try {
stmt =
conn.prepareStatement(
"DELETE FROM "
+ blobMapping.getTable()
+ " WHERE "
+ blobMapping.getGMLIdColumn()
+ "=?");
for (ResourceId id : filter.getSelectedIds()) {
stmt.setString(1, id.getRid());
stmt.addBatch();
if (fs.getCache() != null) {
fs.getCache().remove(id.getRid());
}
}
int[] deletes = stmt.executeBatch();
for (int noDeleted : deletes) {
deleted += noDeleted;
}
} catch (SQLException e) {
LOG.debug(e.getMessage(), e);
throw new FeatureStoreException(e.getMessage(), e);
} finally {
JDBCUtils.close(stmt);
}
LOG.debug("Deleted " + deleted + " features.");
return deleted;
}
/**
* @param chain_id
* @param processIds the ids of the processes part of this chain
* @param chainID the chainID for the processing chain
* @return the success of creating
* @throws SQLException
*/
public boolean addChain(int run_id, int chain_id, Collection<Integer> processIds)
throws SQLException {
boolean created;
try (AutoCloseableDBConnection c = new AutoCloseableDBConnection(false);
PreparedStatement queryWorker =
c.prepareStatement("SELECT chain_id FROM chain_activities WHERE chain_id=?");
PreparedStatement updateWorker =
c.prepareStatement(
"INSERT INTO chain_activities(run_id,chain_id,process_id) VALUES(?,?,?)",
Statement.RETURN_GENERATED_KEYS); ) {
queryWorker.setInt(1, chain_id);
if (!queryWorker.executeQuery().next()) {
for (int aProcessId : processIds) {
updateWorker.setInt(1, run_id);
updateWorker.setInt(2, chain_id);
updateWorker.setInt(3, aProcessId);
updateWorker.addBatch();
}
updateWorker.executeBatch();
// ResultSet rs = updateWorker.getGeneratedKeys();
created = true;
c.commit();
} else {
created = false;
}
}
return created;
}
public void addBatch() throws SQLException {
try {
wrapped.addBatch();
} catch (SQLException e) {
throw new UcanaccessSQLException(e);
}
}