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();
}
}
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();
}
}
protected void deleteConflictingIGPermissions(String igResourceName, String dlResourceName)
throws Exception {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = DataAccess.getConnection();
DatabaseMetaData databaseMetaData = con.getMetaData();
boolean supportsBatchUpdates = databaseMetaData.supportsBatchUpdates();
ps =
con.prepareStatement(
"select companyId, scope, primKey, roleId from "
+ "ResourcePermission where name = ?");
ps.setString(1, igResourceName);
rs = ps.executeQuery();
ps =
con.prepareStatement(
"delete from ResourcePermission where name = ? and "
+ "companyId = ? and scope = ? and primKey = ? and "
+ "roleId = ?");
int count = 0;
while (rs.next()) {
ps.setString(1, dlResourceName);
ps.setLong(2, rs.getLong("companyId"));
ps.setLong(3, rs.getLong("scope"));
ps.setLong(4, rs.getLong("primKey"));
ps.setLong(5, rs.getLong("roleId"));
if (supportsBatchUpdates) {
ps.addBatch();
if (count == PropsValues.HIBERNATE_JDBC_BATCH_SIZE) {
ps.executeBatch();
count = 0;
} else {
count++;
}
} else {
ps.executeUpdate();
}
}
if (supportsBatchUpdates && (count > 0)) {
ps.executeBatch();
}
} finally {
DataAccess.cleanUp(con, ps, rs);
}
}
@Test
public void batch() throws SQLException {
ResultSet rs;
stat.executeUpdate("create table test (c1, c2, c3, c4);");
PreparedStatement prep = conn.prepareStatement("insert into test values (?,?,?,?);");
for (int i = 0; i < 10; i++) {
prep.setInt(1, Integer.MIN_VALUE + i);
prep.setFloat(2, Float.MIN_VALUE + i);
prep.setString(3, "Hello " + i);
prep.setDouble(4, Double.MAX_VALUE + i);
prep.addBatch();
}
assertArrayEq(prep.executeBatch(), new int[] {1, 1, 1, 1, 1, 1, 1, 1, 1, 1});
assertEquals(0, prep.executeBatch().length);
prep.close();
rs = stat.executeQuery("select * from test;");
for (int i = 0; i < 10; i++) {
assertTrue(rs.next());
assertEquals(Integer.MIN_VALUE + i, rs.getInt(1));
assertEquals(Float.MIN_VALUE + i, rs.getFloat(2));
assertEquals("Hello " + i, rs.getString(3));
assertEquals(Double.MAX_VALUE + i, rs.getDouble(4));
}
rs.close();
stat.executeUpdate("drop table test;");
}
@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);
}
}
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;
}
public int[] batch(Connection conn, String sql, List<Object[]> paramList) throws SQLException {
if (paramList == null) {
throw new IllegalArgumentException("NO SQL Paramters");
}
int[] rows = null;
PreparedStatement stmt = null;
try {
stmt = this.prepare(conn, sql);
int count = 0;
for (Object[] objs : paramList) {
this.fillSQLParamter(stmt, objs);
stmt.addBatch();
count++;
if (count == 100) {
stmt.executeBatch();
count = 0;
}
}
if (count != 0) {
rows = stmt.executeBatch();
}
} finally {
SQLUtil.close(stmt);
}
return rows;
}
@Override
public void init() {
super.init();
doInJDBC(
connection -> {
try (PreparedStatement postStatement = connection.prepareStatement(INSERT_POST); ) {
int postCount = getPostCount();
int index;
for (int i = 0; i < postCount; i++) {
if (i > 0 && i % 100 == 0) {
postStatement.executeBatch();
}
index = 0;
postStatement.setString(++index, String.format("Post no. %1$d", i));
postStatement.setInt(++index, 0);
postStatement.setLong(++index, i);
postStatement.addBatch();
}
postStatement.executeBatch();
} catch (SQLException e) {
fail(e.getMessage());
}
});
}
public void save(Iterable<Pessoa> data) throws SQLException, ParseException {
try (PreparedStatement pstmt = this.conn.prepareStatement(QUERY_INSERT)) {
try {
conn.setAutoCommit(false);
int batchSize = 0;
for (Pessoa pessoa : data) {
pstmt.setString(1, pessoa.getNome());
pstmt.setString(2, pessoa.getCargo());
if (pessoa.getDataNascimento() == null) {
pstmt.setNull(3, Types.DATE);
} else {
pstmt.setDate(3, new Date(pessoa.getDataNascimento().getTime()));
}
if (pessoa.getCpf() == null) {
pstmt.setNull(4, Types.BIGINT);
} else {
pstmt.setLong(4, pessoa.getCpf());
}
pstmt.addBatch();
batchSize++;
if (batchSize == this.chunkSize) {
pstmt.executeBatch();
batchSize = 0;
}
}
/*
* necessário, pois o driver do hsqldb lança exceção caso
* seja chamado executeBatch sem nenhum addBatch antes
*/
if (batchSize > 0) {
pstmt.executeBatch();
}
conn.commit();
} catch (SQLException e) {
conn.rollback();
throw e;
}
}
}
@Override
public void executeInserts(
PreparedStatement ps,
List<Row> rows,
List<Column> columns,
boolean supportsBatchUpdates,
String sql,
JDBCConnection connection)
throws SQLException {
List<Serializable> debugValues =
connection.logger.isLogEnabled() ? new ArrayList<Serializable>() : null;
String loggedSql = supportsBatchUpdates && rows.size() > 1 ? sql + " -- BATCHED" : sql;
int batch = 0;
for (Row row : rows) {
batch++;
Serializable id = row.id;
Serializable[] array = row.values;
for (int i = 0; i < array.length; i++) {
int n = 0;
for (Column column : columns) {
n++;
String key = column.getKey();
Serializable v;
if (key.equals(Model.MAIN_KEY)) {
v = id;
} else if (key.equals(Model.COLL_TABLE_POS_KEY)) {
v = Long.valueOf((long) i);
} else if (key.equals(Model.COLL_TABLE_VALUE_KEY)) {
v = array[i];
} else {
throw new RuntimeException(key);
}
column.setToPreparedStatement(ps, n, v);
if (debugValues != null) {
debugValues.add(v);
}
}
if (debugValues != null) {
connection.logger.logSQL(loggedSql, debugValues);
debugValues.clear();
}
if (supportsBatchUpdates) {
ps.addBatch();
if (batch % JDBCRowMapper.UPDATE_BATCH_SIZE == 0) {
ps.executeBatch();
connection.countExecute();
}
} else {
ps.execute();
connection.countExecute();
}
}
}
if (supportsBatchUpdates) {
ps.executeBatch();
connection.countExecute();
}
}
public <S> void execute(InputLoader<S> inputLoader, InputConverter<S> converter) {
long count = 0;
Connection readConnection = null;
Statement stmt = null;
ResultSet rs = null;
Connection writeConnection = null;
PreparedStatement writeStatement = null;
try {
writeConnection = db.getDataSource().getConnection();
writeConnection.setAutoCommit(false);
writeStatement = writeConnection.prepareStatement(converter.updateSql());
readConnection = db.getDataSource().getConnection();
readConnection.setAutoCommit(false);
stmt =
readConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(GROUP_SIZE);
if (db.getDialect().getId().equals(MySql.ID)) {
stmt.setFetchSize(Integer.MIN_VALUE);
} else {
stmt.setFetchSize(GROUP_SIZE);
}
rs = stmt.executeQuery(convertSelectSql(inputLoader.selectSql(), db));
int cursor = 0;
while (rs.next()) {
if (converter.convert(inputLoader.load(rs), writeStatement)) {
writeStatement.addBatch();
cursor++;
count++;
}
if (cursor == GROUP_SIZE) {
writeStatement.executeBatch();
writeConnection.commit();
cursor = 0;
}
}
if (cursor > 0) {
writeStatement.executeBatch();
writeConnection.commit();
}
} catch (SQLException e) {
SqlUtil.log(LOGGER, e);
throw processError(e);
} catch (Exception e) {
throw processError(e);
} finally {
DbUtils.closeQuietly(writeStatement);
DbUtils.closeQuietly(writeConnection);
DbUtils.closeQuietly(readConnection, stmt, rs);
LOGGER.info("{} rows have been updated", count);
}
}
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);
}
}
public void insert(Connection conn, String file, String table) throws IOException, SQLException {
PreparedStatement lockps = conn.prepareStatement("LOCK TABLES " + table + " WRITE");
lockps.execute();
lockps.close();
String sql = "INSERT INTO " + table + " (tax_id, gene_id, accession) VALUES (?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
FileInputStream fis = new FileInputStream(file);
GZIPInputStream gis = new GZIPInputStream(fis);
InputStreamReader ir = new InputStreamReader(gis);
BufferedReader br = new BufferedReader(ir);
int batchcount = 0;
if (br.ready()) {
br.readLine();
while (br.ready()) {
String line = br.readLine();
Scanner sc = new Scanner(line);
sc.useDelimiter("\t");
int taxid = sc.nextInt();
int geneid = sc.nextInt();
sc.next();
String accession = sc.next();
if (accession.contains("_")) {
batchcount++;
accession = accession.substring(0, accession.indexOf("."));
ps.setInt(1, taxid);
ps.setInt(2, geneid);
ps.setString(3, accession);
sc.close();
ps.addBatch();
if (batchcount % 1000 == 0) {
ps.executeBatch();
}
}
}
ps.executeBatch();
}
br.close();
ir.close();
gis.close();
fis.close();
ps.close();
}
/** 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 long executeBatch(PreparedStatement stmt) throws SQLException {
if (configuration.getUseLiterals()) {
return stmt.executeUpdate();
} else if (configuration.getTemplates().isBatchCountViaGetUpdateCount()) {
stmt.executeBatch();
return stmt.getUpdateCount();
} else {
long rv = 0;
for (int i : stmt.executeBatch()) {
rv += i;
}
return rv;
}
}
public void execute(InputStream inputStream) throws Exception {
this.inputStream = inputStream;
try {
deleteOldRecords();
noIds = getNOIds();
classCodesLegal = getClassCodeLegal();
String query =
"INSERT INTO chm62edt_habitat_class_code (ID_HABITAT, ID_CLASS_CODE, TITLE, RELATION_TYPE, CODE) VALUES (?,?,?,?,?)";
this.preparedStatement = con.prepareStatement(query);
String queryUpdateSitesTabInfo =
"UPDATE chm62edt_tab_page_habitats SET LEGAL_INSTRUMENTS='Y' WHERE ID_NATURE_OBJECT = ?";
this.preparedStatementTabInfo = con.prepareStatement(queryUpdateSitesTabInfo);
// con.setAutoCommit(false);
parseDocument();
if (!(counter % 10000 == 0)) {
preparedStatement.executeBatch();
preparedStatement.clearParameters();
preparedStatementTabInfo.executeBatch();
preparedStatementTabInfo.clearParameters();
}
// con.commit();
} catch (Exception e) {
// con.rollback();
// con.commit();
throw new IllegalArgumentException(e.getMessage(), e);
} finally {
if (preparedStatement != null) {
preparedStatement.close();
}
if (preparedStatementTabInfo != null) {
preparedStatementTabInfo.close();
}
if (con != null) {
con.close();
}
}
}
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
}
}
}
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 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();
}
}
private List<String> performUpdateRelational(
QName ftName, List<ParsedPropertyReplacement> replacementProps, IdFilter filter)
throws FeatureStoreException, FilterEvaluationException {
FeatureTypeMapping ftMapping = schema.getFtMapping(ftName);
FIDMapping fidMapping = ftMapping.getFidMapping();
int updated = 0;
PreparedStatement stmt = null;
try {
String sql =
createRelationalUpdateStatement(
ftMapping, fidMapping, replacementProps, filter.getSelectedIds());
if (sql != null) {
LOG.debug("Update: " + sql);
stmt = conn.prepareStatement(sql.toString());
setRelationalUpdateValues(replacementProps, ftMapping, stmt, filter, fidMapping);
int[] updates = stmt.executeBatch();
for (int noUpdated : updates) {
updated += noUpdated;
}
}
} catch (SQLException e) {
JDBCUtils.log(e, LOG);
throw new FeatureStoreException(JDBCUtils.getMessage(e), e);
} finally {
JDBCUtils.close(stmt);
}
LOG.debug("Updated {} features.", updated);
return new ArrayList<String>(filter.getMatchingIds());
}
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();
}
}
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 float getPrice(String sqlPrice, String code) {
Float price = 0.0f;
Connection conn = null;
PreparedStatement ps = null;
try {
conn = getSession().connection();
String sql =
"select "
+ sqlPrice
+ " sqlPrice from biolib.peptide_amino_acid where code = '"
+ code
+ "'";
ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
price = rs.getFloat("sqlPrice");
}
// int count = rs.getInt(1);
ps.executeBatch();
rs.close();
conn.commit();
return price;
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return 0;
}
@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);
}
}
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.");
}
}
}
@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());
}
}
@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) {
}
}
}
}
}
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);
}
/**
* @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;
}
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();
}