public static void main(String[] args) {
// by default each and every query is auto-committed
Connection con = MyConnection.getConnection();
Statement ps = null;
ResultSet rs = null;
String insertQuery1 = "insert into test.emp values(124,'Neeta',56756.90)";
String insertQuery2 = "insert into test.emp values(125,'Meeta',23534.90)";
String insertQuery3 = "insert into test.emp values(123,'Reeta',37547.90)";
try {
// enablr transactions
con.setAutoCommit(false); // here tx boundary begins
ps = con.createStatement();
ps.addBatch(insertQuery1);
ps.addBatch(insertQuery2);
ps.addBatch(insertQuery3);
int rec[] = ps.executeBatch();
System.out.println("batch Executed!!");
con.commit(); // tx done or completed
} catch (Exception e) {
e.printStackTrace();
} // end of catch
finally {
try {
if (ps != null) ps.close();
if (con != null) con.close();
} catch (Exception e2) {
e2.printStackTrace();
} // end of catch
} // end of finally
} // end of main
private void updateCardToDecks(final List<Deck> decks)
throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
final Map<Integer, CardApearancesInDecks> cardDBIdToNewApearances =
createCardToNewApearances(decks);
try (final Statement statement = connection.createStatement()) {
for (final Map.Entry<Integer, CardApearancesInDecks> entry :
cardDBIdToNewApearances.entrySet()) {
final Integer cardDBId = entry.getKey();
final CardApearancesInDecks newAppearances = entry.getValue();
final String updateMainboard =
String.format(
"UPDATE cdi_card_decks_index SET cdi_mainboard_appearances = CONCAT(IF(cdi_mainboard_appearances IS NULL, '', cdi_mainboard_appearances), \"%s\") WHERE cdi_card_id=%d",
newAppearances.getMainboardAppearancesString(), cardDBId);
statement.addBatch(updateMainboard);
if (newAppearances.sideboardApearences != null
&& newAppearances.sideboardApearences.size() > 0) {
final String updateSideboard =
String.format(
"UPDATE cdi_card_decks_index SET cdi_sideboard_appearances = CONCAT(IF(cdi_sideboard_appearances IS NULL, '', cdi_sideboard_appearances), \"%s\") WHERE cdi_card_id=%d",
newAppearances.getSideboardAppearancesString(), cardDBId);
statement.addBatch(updateSideboard);
}
}
final int[] updatedRows = statement.executeBatch();
}
}
@Test
public void testAddBatchSql() throws SQLException {
TGroupConnection conn = null;
Statement stat = null;
try {
conn = tgds.getConnection();
stat = conn.createStatement();
stat.addBatch("update t set name = 'newName' ");
stat.addBatch("update t set type = 2 ");
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 testExecuteBatch04() throws SQLException {
trace("testExecuteBatch04");
int i = 0;
int[] retValue = {0, 0, 0};
int updCountLength = 0;
String sUpdCoffee = COFFEE_UPDATE1;
String sInsCoffee = COFFEE_INSERT1;
String sDelCoffee = COFFEE_DELETE1;
stat.addBatch(sUpdCoffee);
stat.addBatch(sDelCoffee);
stat.addBatch(sInsCoffee);
int[] updateCount = stat.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");
}
String query1 = "SELECT COUNT(*) FROM TEST WHERE TYPE_ID=1";
ResultSet rs = stat.executeQuery(query1);
rs.next();
retValue[i++] = rs.getInt(1);
// 1 as Delete Statement will delete only one row
retValue[i++] = 1;
// 1 as Insert Statement will insert only one row
retValue[i++] = 1;
for (int j = 0; j < updateCount.length; j++) {
trace("Update Count : " + updateCount[j]);
if (updateCount[j] != retValue[j]) {
fail("j=" + j + " right:" + retValue[j]);
}
}
}
private void testAddBatch02() throws SQLException {
trace("testAddBatch02");
int i = 0;
int[] retValue = {0, 0, 0};
int updCountLength = 0;
String sUpdCoffee = COFFEE_UPDATE1;
String sDelCoffee = COFFEE_DELETE1;
String sInsCoffee = COFFEE_INSERT1;
stat.addBatch(sUpdCoffee);
stat.addBatch(sDelCoffee);
stat.addBatch(sInsCoffee);
int[] updateCount = stat.executeBatch();
updCountLength = updateCount.length;
trace("updateCount Length:" + updCountLength);
assertEquals(3, updCountLength);
String query1 = "SELECT COUNT(*) FROM TEST WHERE TYPE_ID=1";
ResultSet rs = stat.executeQuery(query1);
rs.next();
retValue[i++] = rs.getInt(1);
// 1 as delete Statement will delete only one row
retValue[i++] = 1;
// 1 as insert Statement will insert only one row
retValue[i++] = 1;
trace("ReturnValue count : " + retValue.length);
for (int j = 0; j < updateCount.length; j++) {
trace("Update Count:" + updateCount[j]);
trace("Returned Value : " + retValue[j]);
assertEquals("j:" + j, retValue[j], updateCount[j]);
}
}
public void batchUpdate() throws SQLException {
Statement stmt = null;
try {
this.con.setAutoCommit(false);
stmt = this.con.createStatement();
stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Amaretto', 49, 9.99, 0, 0)");
stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Hazelnut', 49, 9.99, 0, 0)");
stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Amaretto_decaf', 49, 10.99, 0, 0)");
stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Hazelnut_decaf', 49, 10.99, 0, 0)");
int[] updateCounts = stmt.executeBatch();
this.con.commit();
} catch (BatchUpdateException b) {
JDBCTutorialUtilities.printBatchUpdateException(b);
} catch (SQLException ex) {
JDBCTutorialUtilities.printSQLException(ex);
} finally {
if (stmt != null) {
stmt.close();
}
this.con.setAutoCommit(true);
}
}
@Override
protected void addDatabaseCreateStatementsToBatch(Statement statement) throws SQLException {
LOGGER.info("setup raft log");
statement.addBatch(
"CREATE TABLE IF NOT EXISTS entries(log_index BIGINT PRIMARY KEY, term BIGINT NOT NULL, type TINYINT NOT NULL, data BLOB DEFAULT NULL)");
statement.addBatch("CREATE INDEX IF NOT EXISTS entries_index ON entries(log_index DESC)");
}
/**
* The first statement in this batch does not return an update count. SUCCESS_NO_INFO is expected
* instead.
*/
public void testNoCount() throws Exception {
Statement stmt = con.createStatement();
stmt.addBatch("create table #testbatch (id int, data varchar(255))");
stmt.addBatch("insert into #testbatch VALUES(1, 'Test line')");
int x[] = stmt.executeBatch();
assertEquals(2, x.length);
assertEquals(SUCCESS_NO_INFO, x[0]);
assertEquals(1, x[1]);
}
private void testClearBatch02() throws SQLException {
trace("testClearBatch02");
String sUpdCoffee = COFFEE_UPDATE1;
String sInsCoffee = COFFEE_INSERT1;
String sDelCoffee = COFFEE_DELETE1;
stat.addBatch(sUpdCoffee);
stat.addBatch(sDelCoffee);
stat.addBatch(sInsCoffee);
stat.clearBatch();
assertEquals(0, stat.executeBatch().length);
}
@Override
public void traceMarker() throws Exception {
Statement statement = connection.createStatement();
try {
statement.addBatch("insert into employee (name) values ('huckle')");
statement.addBatch("insert into employee (name) values ('sally')");
statement.executeBatch();
// intentionally not calling statement.clearBatch()
statement.addBatch("insert into employee (name) values ('lowly')");
statement.addBatch("insert into employee (name) values ('pig will')");
statement.executeBatch();
} finally {
statement.close();
}
}
/** Test batched statements. */
public void testBatch() throws Exception {
Statement stmt = con.createStatement();
stmt.execute("create table #testbatch (id int, data varchar(255))");
for (int i = 0; i < 5; i++) {
if (i == 2) {
// This statement will generate an error
stmt.addBatch("INSERT INTO #testbatch VALUES ('xx', 'This is line " + i + "')");
} else {
stmt.addBatch("INSERT INTO #testbatch VALUES (" + i + ", 'This is line " + i + "')");
}
}
int x[];
try {
x = stmt.executeBatch();
} catch (BatchUpdateException e) {
x = e.getUpdateCounts();
}
if (con.getMetaData().getDatabaseProductName().toLowerCase().startsWith("microsoft")
&& ((JtdsDatabaseMetaData) con.getMetaData()).getDatabaseMajorVersion() > 6) {
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 or SQL Server 6.5 - 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++) {
stmt.addBatch("INSERT INTO #testbatch VALUES (" + i + ", 'This is line " + i + "')");
}
x = stmt.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 uploadTempTable(
Statement tempStmt, String tempTableName, List<String> patientNumList) throws SQLException {
// smuniraju: Extended to include POSTGRES
String serverType = dataSourceLookup.getServerType();
String createTempInputListTable = "";
if (serverType.equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)) {
createTempInputListTable = "create table " + tempTableName + " ( char_param1 varchar(100) )";
} else if (serverType.equalsIgnoreCase(DAOFactoryHelper.POSTGRES)) {
createTempInputListTable =
"create temporary table " + tempTableName + " ( char_param1 varchar(100) )";
}
tempStmt.executeUpdate(createTempInputListTable);
log.debug("created temp table" + tempTableName);
// load to temp table
// TempInputListInsert inputListInserter = new
// TempInputListInsert(dataSource,TEMP_PDO_INPUTLIST_TABLE);
// inputListInserter.setBatchSize(100);
int i = 0;
for (String singleValue : patientNumList) {
tempStmt.addBatch("insert into " + tempTableName + " values ('" + singleValue + "' )");
log.debug("adding batch" + singleValue);
i++;
if (i % 100 == 0) {
log.debug("batch insert");
tempStmt.executeBatch();
}
}
log.debug("batch insert1");
tempStmt.executeBatch();
}
private static CurVO executeBatchNoBindSQL(String sql, List voList, Connection connect)
throws Exception {
CurVO resultVO;
if (voList == null || voList.size() == 0) {
resultVO = new CurVO();
resultVO.setBatchBesult(new int[0]);
}
Statement stm = null;
try {
stm = connect.createStatement();
for (int i = 0; i < voList.size(); i++) {
// P.print(DBAssistantTools.GetJointSQL(sql, voList.get(i),
// null, false));
/** ---------------------打印------------------------- */
P.printSQL(sql, voList.get(i));
/** ---------------------打印------------------------- */
// 添加SQL字符串
stm.addBatch(SqlTool.GetJointSQL(sql, voList.get(i), null, false));
}
// 该数组可查看每次SQL处理的整形结果
resultVO = new CurVO();
resultVO.setBatchBesult(stm.executeBatch());
resultVO.setResult(stm.getUpdateCount());
return resultVO;
} catch (Exception e) {
throw e;
} finally {
DBTool.close(null, stm, null);
}
}
public boolean createAllTable() {
Statement pstmt = null;
boolean statusFinal = false;
try {
List<String> queries = getAllTabelQuaries();
conn = DBConnection.dbConnection();
pstmt = conn.createStatement();
for (String query : queries) {
pstmt.addBatch(query);
}
int[] status = pstmt.executeBatch();
for (int s : status) {
if (s >= 0) {
statusFinal = true;
}
System.out.println(s);
}
return statusFinal;
} catch (Exception e) {
e.printStackTrace();
return false;
} finally {
try {
if (pstmt != null) pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* Issue multiple SQL updates on a single JDBC Statement using batching. Will fall back to
* separate updates on a single Statement if the JDBC driver does not support batch updates.
*
* @param sqls
* @return
* @throws SQLException
*/
public int[] batchUpdate(final String[] sqls) {
validateSqlParameter(sqls);
Connection conn = null;
Statement stmt = null;
try {
conn = getConnection();
conn.setAutoCommit(false);
applyQueryTimeOut(stmt);
stmt = conn.createStatement();
for (String sql : sqls) {
if (showSql) {
logger.info(getFinalSql(sql));
}
stmt.addBatch(sql);
}
int[] result = stmt.executeBatch();
conn.commit();
return result;
} catch (SQLException e) {
try {
JdbcUtils.rollback(conn);
} catch (Exception unused) {
}
throw new RuntimeException(e);
} finally {
JdbcUtils.closeQuietly(stmt);
JdbcUtils.closeQuietly(conn);
}
}
public boolean execSQL(String[] sqls) {
Statement statement = null;
Connection connection = null;
try {
connection = DBManager.getConnection();
connection.setAutoCommit(false);
statement = connection.createStatement();
for (String s : sqls) {
statement.addBatch(s);
}
statement.executeBatch();
connection.commit();
return true;
} catch (Exception e) {
// TODO: handle exception
try {
connection.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
return false;
} finally {
try {
connection.setAutoCommit(true);
statement.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
DBManager.freeConnection(connection);
}
}
private int[] batch(Config config, Connection conn, List<String> sqlList, int batchSize)
throws SQLException {
if (sqlList == null || sqlList.size() == 0)
throw new IllegalArgumentException("The sqlList length must more than 0.");
if (batchSize < 1) throw new IllegalArgumentException("The batchSize must more than 0.");
int counter = 0;
int pointer = 0;
int size = sqlList.size();
int[] result = new int[size];
Statement st = conn.createStatement();
for (int i = 0; i < size; i++) {
st.addBatch(sqlList.get(i));
if (++counter >= batchSize) {
counter = 0;
int[] r = st.executeBatch();
conn.commit();
for (int k = 0; k < r.length; k++) result[pointer++] = r[k];
}
}
int[] r = st.executeBatch();
conn.commit();
for (int k = 0; k < r.length; k++) result[pointer++] = r[k];
DbKit.closeQuietly(st);
return result;
}
public void insertGrandprizePoint() throws IOException, SQLException {
recordCounter = 0;
System.out.println("Starting inserting Grand Prize point records into points' table..");
LogLoader.setInfo(
PointUpdaterDaemon.class.getSimpleName(),
"[INSERT GRANDPRIZE] Started: " + getCurrentTimeStamp());
// INSERT MONTHLY - Supposed to be executed on INIT w/CREATE TABLE function
// Binding a single input stream CSV reader & its buffer
CSVReader grandprizeReader =
new CSVReader(
new FileReader(workingDir + grandprizeFile), recSeparator, escChar, headerLine);
String[] grandprize;
String insertGrandprizeQuery;
if (con == null) {
db_object.openConnection();
con = db_object.getConnection();
}
try {
stm = con.createStatement();
while ((grandprize = grandprizeReader.readNext()) != null) {
// System.out.println(monthly[0] + monthly[1] + monthly[2]);
insertGrandprizeQuery =
"INSERT INTO tbl_points_grandprize (point_cardno, point_grandprize) VALUES ('"
+ grandprize[1].trim()
+ "', "
+ grandprize[2].trim()
+ ");";
stm.addBatch(insertGrandprizeQuery);
// stm = con.createStatement();
// affectedRow = stm.executeUpdate(updateGrandprizeQuery);
recordCounter++;
}
stm.executeBatch();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
stm.close();
if (con != null) {
try {
db_object.closeConnection();
} catch (SQLException e) {
e.printStackTrace();
} finally {
con = null;
}
}
}
statGrandprize = true;
System.out.println("Grand Prize Records Inserted:" + recordCounter);
System.out.println("Grand Prize Records Finished Time: " + getCurrentTimeStamp());
LogLoader.setInfo(
PointUpdaterDaemon.class.getSimpleName(),
"[INSERT GRANDPRIZE] Finished: " + getCurrentTimeStamp());
}
@Override
public void insertBatchUser() throws SQLException {
// TODO Auto-generated method stub
Statement st = con.createStatement();
String sql = "insert into User (username,password) values('abc','def')";
st.addBatch(sql);
String sql1 = "insert into User (username,password) values('abc11','def1')";
st.addBatch(sql1);
String sql2 = "insert into User (username,password) values('abc12','def2')";
st.addBatch(sql2);
int[] data = st.executeBatch();
for (int count : data) {
System.out.println(count);
}
}
/**
* This test should generate an error as the second statement in the batch returns a result set.
*/
public void testResultSetError() throws Exception {
Statement stmt = con.createStatement();
stmt.addBatch("create table #testbatch (id int, data varchar(255))");
stmt.addBatch("insert into #testbatch VALUES(1, 'Test line')");
stmt.addBatch("SELECT 'This is an error'");
int x[];
try {
x = stmt.executeBatch();
fail("Expecting BatchUpdateException");
} catch (BatchUpdateException e) {
x = e.getUpdateCounts();
}
assertEquals(3, x.length);
assertEquals(SUCCESS_NO_INFO, x[0]);
assertEquals(1, x[1]);
assertEquals(EXECUTE_FAILED, x[2]);
}
/** Test for bug [1180169] JDBC escapes not allowed with Sybase addBatch. */
public void testBatchEsc() throws Exception {
Statement stmt = con.createStatement();
stmt.execute("CREATE TABLE #TESTBATCH (ts datetime)");
stmt.addBatch("INSERT INTO #TESTBATCH VALUES ({ts '1999-01-01 23:50:00'})");
int counts[] = stmt.executeBatch();
assertEquals(1, counts[0]);
stmt.close();
}
public void deleteRowsByColumns(List<Map<String, Object>> otherEntities, Statement statement)
throws SQLException {
if (otherEntities.size() == 0) {
return;
}
List<String> columns = new ArrayList<String>(otherEntities.get(0).keySet());
String deleteStatement = constructDeleteStatement(columns, otherEntities);
statement.addBatch(deleteStatement);
}
public void updateMonthlyPoint() throws IOException, SQLException {
recordCounter = 0;
System.out.println("Starting updating monthly point records into points' table..");
LogLoader.setInfo(
PointUpdaterDaemon.class.getSimpleName(),
"[UPDATE MONTHLY] Started: " + getCurrentTimeStamp());
// UPDATE MONTHLY
// Binding a single input stream CSV reader & its buffer
CSVReader monthlyReader =
new CSVReader(new FileReader(workingDir + monthlyFile), recSeparator, escChar, headerLine);
String[] monthly;
String updateMonthlyQuery;
if (con == null) {
db_object.openConnection();
con = db_object.getConnection();
}
try {
stm = con.createStatement();
while ((monthly = monthlyReader.readNext()) != null) {
// System.out.println(monthly[0] + monthly[1] + monthly[2]);
updateMonthlyQuery =
"UPDATE tbl_points SET point_monthly = "
+ monthly[2].trim()
+ " WHERE point_cardno = '"
+ monthly[1].trim()
+ "';";
stm.addBatch(updateMonthlyQuery);
recordCounter++;
}
stm.executeBatch();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
stm.close();
if (con != null) {
try {
db_object.closeConnection();
} catch (SQLException e) {
e.printStackTrace();
} finally {
con = null;
}
}
}
statMonthly = true;
System.out.println("Updated Monthly Records:" + recordCounter);
System.out.println("Monthly Records Update Finished Time: " + getCurrentTimeStamp());
LogLoader.setInfo(
PointUpdaterDaemon.class.getSimpleName(),
"[UPDATE MONTHLY] Finished: " + getCurrentTimeStamp());
}
public void updateGrandprizePoint() throws IOException, SQLException {
recordCounter = 0;
System.out.println("Starting updating grand prize point records into points' table..");
LogLoader.setInfo(
PointUpdaterDaemon.class.getSimpleName(),
"[UPDATE GRANDPRIZE] Started: " + getCurrentTimeStamp());
// UPDATE GRANDPRIZE
CSVReader grandprizeReader =
new CSVReader(
new FileReader(workingDir + grandprizeFile), recSeparator, escChar, headerLine);
String[] grandprize;
String updateGrandprizeQuery;
if (con == null) {
db_object.openConnection();
con = db_object.getConnection();
}
try {
stm = con.createStatement();
while ((grandprize = grandprizeReader.readNext()) != null) {
// System.out.println(grandprize[0] + grandprize[1] + grandprize[2]);
updateGrandprizeQuery =
"UPDATE tbl_points SET point_grandprize = "
+ grandprize[2].trim()
+ " WHERE point_cardno = '"
+ grandprize[1].trim()
+ "';";
stm.addBatch(updateGrandprizeQuery);
recordCounter++;
}
stm.executeBatch();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
stm.close();
if (con != null) {
try {
db_object.closeConnection();
} catch (SQLException e) {
e.printStackTrace();
} finally {
con = null;
}
}
}
statGrandprize = true;
System.out.println("Updated Grand Prize Records: " + recordCounter);
System.out.println("Grand Prize Records Finished Time: " + getCurrentTimeStamp());
LogLoader.setInfo(
PointUpdaterDaemon.class.getSimpleName(),
"[UPDATE GRANDPRIZE] Finished: " + getCurrentTimeStamp());
}
@Before
public void setUp() throws SQLException {
// Create a table in H2 and put some data in it for querying.
connection = DriverManager.getConnection(h2ConnectionString, username, password);
try (Statement statement = connection.createStatement()) {
// Setup table
statement.addBatch("CREATE SCHEMA IF NOT EXISTS TEST;");
statement.addBatch(
"CREATE TABLE IF NOT EXISTS TEST.TEST_TABLE "
+ "(P_ID INT NOT NULL PRIMARY KEY, FIRST_NAME "
+ "VARCHAR(255), LAST_NAME VARCHAR(255));");
statement.addBatch("INSERT INTO TEST.TEST_TABLE VALUES (1, 'Adam', 'Kunicki')");
statement.addBatch("INSERT INTO TEST.TEST_TABLE VALUES (2, 'Jon', 'Natkins')");
statement.addBatch("INSERT INTO TEST.TEST_TABLE VALUES (3, 'Jon', 'Daulton')");
statement.addBatch("INSERT INTO TEST.TEST_TABLE VALUES (4, 'Girish', 'Pancha')");
statement.executeBatch();
}
}
/* goodG2B1() - use goodsource and badsink by changing first IO.STATIC_FINAL_FIVE==5 to IO.STATIC_FINAL_FIVE!=5 */
private void goodG2B1() throws Throwable {
String data;
if (IO.STATIC_FINAL_FIVE != 5) {
/* 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;
} else {
/* FIX: Use a hardcoded string */
data = "foo";
}
if (IO.STATIC_FINAL_FIVE == 5) {
if (data != null) {
String names[] = data.split("-");
int successCount = 0;
Connection dbConnection = null;
Statement sqlStatement = null;
try {
dbConnection = IO.getDBConnection();
sqlStatement = dbConnection.createStatement();
for (int i = 0; i < names.length; i++) {
/* POTENTIAL FLAW: data concatenated into SQL statement used in executeBatch(), which could result in SQL Injection */
sqlStatement.addBatch(
"update users set hitcount=hitcount+1 where name='" + names[i] + "'");
}
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 Statament", exceptSql);
}
try {
if (dbConnection != null) {
dbConnection.close();
}
} catch (SQLException exceptSql) {
IO.logger.log(Level.WARNING, "Error closing Connection", exceptSql);
}
}
}
}
}
/* goodG2B() - use goodsource and badsink */
private void goodG2B() throws Throwable {
String dataCopy;
{
String data;
/* FIX: Use a hardcoded string */
data = "foo";
dataCopy = data;
}
{
String data = dataCopy;
if (data != null) {
String names[] = data.split("-");
int successCount = 0;
Connection dbConnection = null;
Statement sqlStatement = null;
try {
dbConnection = IO.getDBConnection();
sqlStatement = dbConnection.createStatement();
for (int i = 0; i < names.length; i++) {
/* POTENTIAL FLAW: data concatenated into SQL statement used in executeBatch(), which could result in SQL Injection */
sqlStatement.addBatch(
"update users set hitcount=hitcount+1 where name='" + names[i] + "'");
}
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 Statament", exceptSql);
}
try {
if (dbConnection != null) {
dbConnection.close();
}
} catch (SQLException exceptSql) {
IO.logger.log(Level.WARNING, "Error closing Connection", exceptSql);
}
}
}
}
}
// 插入注册类型
public int insertZclx(ArrayList al) {
int result1 = 1;
Connection conn = null;
if (al != null && !al.isEmpty()) {
try {
conn = DBConnection.getConnection();
if (conn != null) {
conn.setAutoCommit(false);
Statement stsm = conn.createStatement();
Iterator it = al.iterator();
while (it.hasNext()) {
HashMap hm = (HashMap) it.next();
String sql =
"insert into SKQ_ZCLX(ZCLXBM,ZCLXMC,ZCLXJC,STATUS) values('"
+ (String) hm.get("zclxbm")
+ "','"
+ (String) hm.get("zclxmc")
+ "','"
+ (String) hm.get("zclxjc")
+ "',1)";
stsm.addBatch(sql);
}
int[] result = stsm.executeBatch();
conn.commit();
for (int i = 0; i < result.length; i++) {
if (result[i] == Statement.EXECUTE_FAILED || result[i] == Statement.SUCCESS_NO_INFO) {
conn.rollback();
stsm.close();
result1 = -1;
}
break;
}
}
} catch (SQLException e) {
// TODO Auto-generated catch block
result1 = -1;
// e.getMessage();
e.printStackTrace();
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
return result1;
}
public static void main(String[] args) throws Exception {
Class.forName(DBDRIVER);
Connection conn = DriverManager.getConnection(DBURL, USER, PASSWORD);
Statement stmt = conn.createStatement();
conn.setAutoCommit(false); // 取消自动提交
try {
stmt.addBatch("INSERT INTO member(mid,name) VALUES (myseq.nextval, '测试A')");
stmt.addBatch("INSERT INTO member(mid,name) VALUES (myseq.nextval, '测试B')");
stmt.addBatch("INSERT INTO member(mid,name) VALUES (myseq.nextval, '测试C')");
stmt.addBatch("INSERT INTO member(mid,name) VALUES (myseq.nextval, '测试D')");
stmt.addBatch("INSERT INTO member(mid,name) VALUES (myseq.nextval, '测试E')");
int result[] = stmt.executeBatch(); // 执行处理
System.out.println(Arrays.toString(result));
conn.commit(); // 如果没有错误，进行提交
} catch (Exception e) {
e.printStackTrace();
conn.rollback(); // 如果出现异常则进行回滚
}
conn.close();
}
public void executeBatch() {
int statusCode[];
init();
statusCode = new int[0];
try {
int count = 0;
for (int i = 0; i < sb.size(); i++) {
count++;
stmt.addBatch((new StringBuilder()).append(sb.get(i)).append(" ").toString());
if (count > 30) {
count = 0;
statusCode = stmt.executeBatch();
stmt.clearBatch();
}
}
System.out.println((new StringBuilder("sql:")).append(sb).toString());
if (count > 0) statusCode = stmt.executeBatch();
message = (new StringBuilder(String.valueOf(statusCode.length))).toString();
dbConn.commit();
sb.clear();
success = true;
stmt.close();
dbConn.close();
} catch (Exception ex) {
success = false;
message = ex.getMessage();
try {
dbConn.rollback();
stmt.close();
dbConn.close();
} catch (Exception e) {
dbConn = null;
}
println(statusCode);
}
try {
dbConn.close();
} catch (Exception e) {
dbConn = null;
}
try {
dbConn.close();
} catch (Exception e) {
dbConn = null;
}
try {
dbConn.close();
} catch (Exception e) {
dbConn = null;
}
}