Java Code Examples for java.sql.Statement#executeBatch()

public static Runnable wanInsertDAP(final String table) throws SQLException {
  CacheSerializableRunnable wanInsert = new CacheSerializableRunnable(
      "DAP Caller") {
    @Override
    public void run2() throws CacheException {
      try {
        Connection conn = TestUtil.jdbcConn;
        Statement st = conn.createStatement();
        boolean orig = conn.getAutoCommit();
        conn.setAutoCommit(false);
        st.addBatch("insert into " + table + " values (1, 'First', 'A', 'VMWARE')");
        st.addBatch("insert into " + table + " values (2, 'Second', 'B', 'VMWARE')");
        st.addBatch("insert into " + table + " values (3, 'Third', 'C', 'VMWARE')");
        st.addBatch("insert into " + table + " values (4, 'Fourth', 'D', 'VMWARE')");
        st.addBatch("insert into " + table + " values (5, 'Fifth', 'E', 'VMWARE')");
        st.addBatch("insert into " + table + " values (6, 'Sixth', 'F', 'VMWARE')");
        st.executeBatch();
        conn.commit();
        conn.setAutoCommit(orig);
      } catch (SQLException sqle) {
        throw GemFireXDRuntimeException.newRuntimeException(null, sqle);
      }
    }
  };
  return wanInsert;
}
 

static void createTable(String tableName) throws Exception {
  Connection conn = getSimpleDSConnection();
  Statement stm = conn.createStatement();

  String sql = "CREATE TABLE " + tableName
      + " (id varchar(5) NOT NULL, name varchar(50), PRIMARY KEY(id))";

  Log.getLogWriter().info("create table statement = " + sql);
  stm = conn.createStatement();
  stm.executeUpdate(sql);
  String key = null;
  for (int i = 1; i <= 4; i++) {
    key = "key" + i;
    sql = "INSERT INTO " + tableName + " VALUES ('" + key + "','value0')";
    Log.getLogWriter().info("insert row = " + sql);
    stm.addBatch(sql);
  }
  stm.executeBatch();
  conn.commit();
  if (stm != null) stm.close();
  if (conn != null) conn.close();
  Log.getLogWriter().info("Created table " + tableName + " in database");
  displayData(tableName);
}
 

public static Runnable batchDelete() {
  CacheSerializableRunnable senderConf = new CacheSerializableRunnable(
      "Sender Configurator") {
    @Override
    public void run2() throws CacheException {
      try {
        Connection conn = TestUtil.jdbcConn;
        Statement st = conn.createStatement();
        st.addBatch("Delete from EMP.PARTITIONED_TABLE where ID = 1");
        st.addBatch("Delete from EMP.PARTITIONED_TABLE where ID = 2");
        st.executeBatch();
      } catch (SQLException sqle) {
        throw GemFireXDRuntimeException.newRuntimeException(null, sqle);
      }
    }
  };
  return senderConf;
}
 

public void test1000StatementsBatch() throws SQLException {
    int updateCount[];

    Statement stmt = createStatement();

    println("Positive Statement: 1000 statements batch");
    for (int i=0; i<1000; i++){
        stmt.addBatch("insert into t1 values(1)");
    }
    updateCount = stmt.executeBatch();
    
    int[] expectedUpdateCount = new int[1000];
    Arrays.fill(expectedUpdateCount, 1);
    assertBatchUpdateCounts(expectedUpdateCount, updateCount);
    
    assertTableRowCount("T1", 1000);

    stmt.close();

    commit();
}
 

public static Runnable batchInsertForBULK() {
  CacheSerializableRunnable senderConf = new CacheSerializableRunnable(
      "Sender Configurator") {
    @Override
    public void run2() throws CacheException {
      try {
        Connection conn = TestUtil.jdbcConn;
        Statement st = conn.createStatement();
        st.addBatch("insert into EMP.PARTITIONED_TABLE values (1, 'First', 'A714', 'Pivotal')");
        st.addBatch("insert into EMP.PARTITIONED_TABLE values (2, 'Second', 'J 605', 'Zimbra')");
        st.executeBatch();
      } catch (SQLException sqle) {
        throw GemFireXDRuntimeException.newRuntimeException(null, sqle);
      }
    }
  };
  return senderConf;
}
 

public void test1000StatementsBatch() throws SQLException {
    int updateCount[];

    Statement stmt = createStatement();

    println("Positive Statement: 1000 statements batch");
    for (int i=0; i<1000; i++){
        stmt.addBatch("insert into t1 values(1)");
    }
    updateCount = stmt.executeBatch();

    int[] expectedUpdateCount = new int[1000];
    Arrays.fill(expectedUpdateCount, 1);
    assertBatchUpdateCounts(expectedUpdateCount, updateCount);

    assertTableRowCount("T1", 1000);

    stmt.close();

    commit();
}
 

/** 
 * helper method to evaluate negative tests where we expect a 
 * batchExecuteException to be returned.
 * @exception SQLException     Thrown if the expected error occurs
 *                             We expect a BatchUpdateException, and
 *                             verify it is so.
 *
 * @param expectedError The sqlstate to look for.
 * @param stmt The Statement that contains the Batch to
 *                             be executed.
 * @param expectedUpdateCount The expectedUpdateCount array.
 */
protected void assertBatchExecuteError( 
    String expectedError,
    Statement stmt,
    int[] expectedUpdateCount) 
throws SQLException 
{
    int[] updateCount;    
    try {
        updateCount = stmt.executeBatch();
        fail("Expected batchExecute to fail");
    } catch (BatchUpdateException bue) {
        assertSQLState(expectedError, bue);
        updateCount = bue.getUpdateCounts();
        assertBatchUpdateCounts(expectedUpdateCount, updateCount);
    } 
}
 

public static Runnable doBatchDelete() {
  CacheSerializableRunnable senderConf = new CacheSerializableRunnable(
      "Sender Configurator") {
    @Override
    public void run2() throws CacheException {
      try {
        Connection conn = TestUtil.jdbcConn;
        conn.setAutoCommit(false);
        Statement st = conn.createStatement();
        st.addBatch("Delete from TESTTABLE where ID = 1");
        st.addBatch("Delete from TESTTABLE where ID = 2");
        st.executeBatch();
        conn.commit();
      } catch (SQLException sqle) {
        throw GemFireXDRuntimeException.newRuntimeException(null, sqle);
      }
    }
  };
  return senderConf;
}
 

private int[] executeBatchOnConnection(Connection conn, List<String> batchedSqls) throws SQLException {
    Statement stmt = createStatementInternal(conn, batchedSqls.get(0), true);
    for (String sql : batchedSqls) {
        stmt.addBatch(sql);
    }
    return stmt.executeBatch();
}
 

private long insert(Connection con, List<String> list) throws SQLException {
	Statement stms = con.createStatement();
	for (String sql : list) {
		stms.addBatch(sql);
	}
	stms.executeBatch();
	if (this.autocommit == false) {
		con.commit();
	}
	// stms.clearBatch();
	stms.close();
	return list.size();
}
 

@Test
public void testUpgradeSchemaObjects() throws Exception {
  XMLCatalogSchemaManager manager;
  Statement stmt;
  
  manager = new XMLCatalogSchemaManager("schemas/derbytest/upgradetest");
  assertThat(manager.isLoaded(), is(true));
  assertThat(manager.getCatalogStore().getPatches(), hasSize(1));
  assertThat(manager.getCatalogStore().getPatches().get(0).getObjects(), hasSize(2));
  
  stmt = conn.createStatement();
  stmt.addBatch("create schema " + manager.getCatalogStore().getSchema().getSchemaName());
  stmt.addBatch("set current schema " + manager.getCatalogStore().getSchema().getSchemaName());
  stmt.addBatch("CREATE TABLE TESTTABLE1 (COL1 INT, COL2 VARCHAR(10))");
  stmt.addBatch("CREATE INDEX TESTINDEX1 ON TESTTABLE1 (COL1)");
  stmt.addBatch("CREATE TABLE TESTTABLE2 (COL1 INT, COL2 VARCHAR(10))");
  stmt.executeBatch();
  manager.upgradeBaseSchema(conn, 1);
  
  DatabaseMetaData metadata = conn.getMetaData();
  ResultSet columns = metadata.getColumns(null, 
      manager.getCatalogStore().getSchema().getSchemaName().toUpperCase(), 
      "TESTTABLE2", "COL2");
  
  assertThat(columns.next(), is(true));
  assertThat(columns.getInt("DATA_TYPE"), is(Types.VARCHAR));
  assertThat(columns.getInt("COLUMN_SIZE"), is(20));
  
  columns = metadata.getColumns(null, 
      manager.getCatalogStore().getSchema().getSchemaName().toUpperCase(), 
      "TESTTABLE2", "COL3");
  
  assertThat(columns.next(), is(true));
  assertThat(columns.getInt("DATA_TYPE"), is(Types.VARCHAR));
  assertThat(columns.getInt("COLUMN_SIZE"), is(25));
}
 

/** Test.
 * @throws SQLException e */
@Test
public void testStatementProxy() throws SQLException {
	DriverManager.registerDriver(driver);
	// nécessite la dépendance vers la base de données H2
	Connection connection = DriverManager.getConnection(H2_DATABASE_URL);
	try {
		connection = jdbcWrapper.createConnectionProxy(connection);
		final Statement statement = connection.createStatement();
		try {
			assertFalse(EQUALS, statement.equals(statement));
			statement.hashCode();

			statement.executeQuery("select 1").close();
			statement.execute("select 2");
			statement.execute("CREATE TABLE IF NOT EXISTS test (name VARCHAR(50) NOT NULL)");
			statement.addBatch("insert into test (name) values ('test')");
			statement.executeBatch();
			statement.addBatch("/* BATCH */ insert into test (name) values ('test')");
			statement.executeBatch();
			statement.addBatch("insert into test (name) values ('test')");
			statement.executeLargeBatch();
			jdbcWrapper.getSqlCounter().setDisplayed(false);
			statement.execute("select 4");
			jdbcWrapper.getSqlCounter().setDisplayed(true);
			statement.execute("explain select 3");
			try {
				statement.execute("invalid sql");
			} catch (final SQLException e) {
				assertNotNull("ok", e);
			}
		} finally {
			statement.close();
		}
	} finally {
		connection.close();
	}
}
 

private void assertStatementOK(String dsName, Connection conn, Statement s)
        throws SQLException {

    // checks currently only implemented for embedded 
    if (usingEmbedded())
    {
        SecurityCheck.assertSourceSecurity(s, "java.sql.Statement");
    }

    Connection c1 = s.getConnection();
    if (c1 != conn)
    {
        // with DerbyNetClient and any kind of DataSource, this goes wrong
        if (!usingDerbyNetClient() && (dsName.indexOf("DataSource") >= 0))
            fail ("incorrect connection object returned for Statement.getConnection()");
    }

    s.addBatch("insert into intTable values 1");
    s.addBatch("insert into intTable values 2,3");
    int[] states = s.executeBatch();
    if (states[0] != 1)
        fail ("invalid update count for first batch statement");
    if (states[1] != 2)
        fail ("invalid update count for second batch statement");

    ResultSet rs = s.executeQuery("VALUES 1");
    if (rs.getStatement() != s)
        fail ("incorrect Statement object returned for ResultSet.getStatement for " + dsName);
    rs.close();
    s.close();
}
 

protected int[] insertBatchToDerbyTable(Connection conn, int[] cid, String[] cust_name,
    Date[] since, String[] addr, int size) throws SQLException {
  Statement stmt = conn.createStatement();
  int tid = getMyTid();
  int counts[] = null;
  
  for (int i=0 ; i<size ; i++) { 
    stmt.addBatch("insert into trade.customers values (" 
      + cid[i] + ", '" + cust_name[i] + "','" + since[i]
      + "','" + addr[i] + "'," + tid + ")");
    Log.getLogWriter().info("Derby - batch inserting into trade.customers CID:" + cid[i] + ",CUSTNAME:" + cust_name[i] +
          ",SINCE:" + since[i] + ",ADDR:" + addr[i] + ",TID:" + tid);
  }
  
  counts = stmt.executeBatch(); 
  
  
  for (int i = 0; i < counts.length; i++) {
    if (counts[i] != -3)
      Log.getLogWriter().info(
          "Derby - inserted into table trade.customers CID:" + cid[i]
              + ",CUSTNAME:" + cust_name[i] + ",SINCE:" + since[i]
              + ",ADDR:" + addr[i] + ",TID:" + tid);
    verifyRowCount.put(tid + "_insert" + i, 0);
    verifyRowCount.put(tid + "_insert" + i, new Integer(counts[i]));
    // Log.getLogWriter().info("Derby inserts " +
    // verifyRowCount.get(tid+"_insert" +i) + " rows");
  }
  return counts;
}
 

public void insertOneBatch(List<String> sqls, ThreadLocal<Long> totalTime, ThreadLocal<Long> errorCount) {
	Statement statement;
	int[] result;
	long errorNum = 0;
	try {
		statement = connection.createStatement();
		for(String sql : sqls){
			statement.addBatch(sql);
		}
		
		long startTime = System.currentTimeMillis();
		result = statement.executeBatch();
		statement.clearBatch();
		statement.close();
		long endTime = System.currentTimeMillis();
		long costTime = endTime - startTime;
		for (int i = 0; i < result.length; i++) {
			if (result[i] == -1) {
				errorNum++;
			}
		}
		if (errorNum > 0) {
			LOGGER.info("Batch insert failed, the failed number is {}! ", errorNum);

		} else {
			totalTime.set(totalTime.get() + costTime);
		}
		errorCount.set(errorCount.get() + errorNum);

	} catch (SQLException e) {
		e.printStackTrace();
	}
}
 

private void assertStatementOK(String dsName, Connection conn, Statement s)
throws SQLException {

    // checks currently only implemented for embedded 
    if (usingEmbedded())
    {
        SecurityCheck.assertSourceSecurity(s, "java.sql.Statement");
    }

    Connection c1 = s.getConnection();
    if (c1 != conn)
    {
        // with DerbyNetClient and any kind of DataSource, this goes wrong
        if (!usingDerbyNetClient() && (dsName.indexOf("DataSource") >= 0))
            fail ("incorrect connection object returned for Statement.getConnection()");
    }

    s.addBatch("insert into intTable values 1");
    s.addBatch("insert into intTable values 2,3");
    int[] states = s.executeBatch();
    if (states[0] != 1)
        fail ("invalid update count for first batch statement");
    if (states[1] != 2)
        fail ("invalid update count for second batch statement");

    ResultSet rs = s.executeQuery("VALUES 1");
    if (rs.getStatement() != s)
        fail ("incorrect Statement object returned for ResultSet.getStatement for " + dsName);
    rs.close();
    s.close();
}
 

private static void createRecordsAndExecuteInsertStatements(
    String tableName,
    Statement st,
    Map<String, Field.Type> columnToFieldType,
    boolean multipleRecordsWithSameOffset
) throws Exception {
  int numberOfRecordsInTable = RANDOM.nextInt(MAX_ROWS_PER_TABLE - 1) + 1;
  IntStream.range(0, numberOfRecordsInTable).forEach(recordNum -> {
    Record record = RecordCreator.create();
    LinkedHashMap<String, Field> rootField = new LinkedHashMap<>();

    final int recordNumDivisor = multipleRecordsWithSameOffset ? 100 : 1;
    final int calculatedRecordNum = recordNum / recordNumDivisor;

    rootField.put(OFFSET_FIELD_NAME, Field.create(calculatedRecordNum));


    columnToFieldType.entrySet().stream()
        .filter(columnToFieldTypeEntry -> !columnToFieldTypeEntry.getKey().equals(OFFSET_FIELD_NAME))
        .forEach(columnToFieldTypeEntry  -> {
          String fieldName = columnToFieldTypeEntry.getKey();
          Field.Type fieldType = columnToFieldTypeEntry.getValue();
          rootField.put(fieldName, Field.create(fieldType, generateRandomData(fieldType)));
        });

    if (multipleRecordsWithSameOffset) {
      rootField.put(OFFSET_FIELD_RAW_NAME, Field.create(recordNum));
    }
    record.set(Field.createListMap(rootField));
    List<Record> records = EXPECTED_TABLES_TO_RECORDS.computeIfAbsent(tableName, t -> new ArrayList<>());
    records.add(record);

    try {
      st.addBatch(getInsertStatement(database, tableName, rootField.values()));
    } catch (Exception e) {
      LOG.error("Error Happened", e);
      Throwables.propagate(e);
    }
  });
  st.executeBatch();
}
 

private static void disableForeignKeyConstraints() {
    if (DBPlugin.url.startsWith("jdbc:oracle:")) {
        DB.execute("begin\n"
                + "for i in (select constraint_name, table_name from user_constraints where constraint_type ='R'\n"
                + "and status = 'ENABLED') LOOP\n"
                + "execute immediate 'alter table '||i.table_name||' disable constraint '||i.constraint_name||'';\n"
                + "end loop;\n"
                + "end;"
        );
        return;
    }

    if (DBPlugin.url.startsWith("jdbc:hsqldb:")) {
        DB.execute("SET REFERENTIAL_INTEGRITY FALSE");
        return;
    }

    if (DBPlugin.url.startsWith("jdbc:h2:")) {
        DB.execute("SET REFERENTIAL_INTEGRITY FALSE");
        return;
    }

    if (DBPlugin.url.startsWith("jdbc:mysql:")) {
        DB.execute("SET foreign_key_checks = 0;");
        return;
    }

    if (DBPlugin.url.startsWith("jdbc:postgresql:")) {
        DB.execute("SET CONSTRAINTS ALL DEFERRED");
        return;
    }

    if (DBPlugin.url.startsWith("jdbc:sqlserver:")) {
        Statement exec=null;

        try {
            List<String> names = new ArrayList<String>();
            Connection connection = DB.getConnection();

            ResultSet rs = connection.getMetaData().getTables(null, null, null, new String[]{"TABLE"});
            while (rs.next()) {
                String name = rs.getString("TABLE_NAME");
                names.add(name);
            }

                // Then we disable all foreign keys
            exec = connection.createStatement();
            for (String tableName:names)
                exec.addBatch("ALTER TABLE " + tableName+" NOCHECK CONSTRAINT ALL");
            exec.executeBatch();
            exec.close();

            return;
        } catch (SQLException ex) {
            throw new DatabaseException("Error while disabling foreign keys", ex);
        }
    }

    // Maybe Log a WARN for unsupported DB ?
    Logger.warn("Fixtures : unable to disable constraints, unsupported database : " + DBPlugin.url);
}
 

public void testTransactionErrorBatch() throws SQLException {

        // conn is just default connection
        Connection conn = getConnection();
        Connection conn2 = openDefaultConnection();
        conn.setAutoCommit(false);
        conn2.setAutoCommit(false);
        // GemStone changes BEGIN
        conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        conn2.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        // GemStone changes END
        Statement stmt = conn.createStatement();
        Statement stmt2 = conn2.createStatement();

        int[] updateCount = null;

        println("Negative Statement: statement testing time out" +
            " while getting the lock in the batch");

        stmt.execute("insert into t1 values(1)");
        stmt2.execute("insert into t1 values(2)");

        stmt.addBatch("update t1 set c1=3 where c1=2");
        stmt2.addBatch("update t1 set c1=4 where c1=1");

        try
        {
            stmt.executeBatch();
            fail ("Batch is expected to fail");
            updateCount = stmt2.executeBatch();
        } catch (BatchUpdateException bue) {
            /* Ensure the exception is time out while getting lock */
            if (usingEmbedded())
                assertSQLState("40XL1", bue);
            else if (usingDerbyNetClient())
                assertSQLState("XJ208", bue);
            updateCount = ((BatchUpdateException)bue).getUpdateCounts();
            if (updateCount != null) {
                if (usingEmbedded())
                    assertEquals("first statement in the batch caused time out" +
                        " while getting the lock, there should be no update count",
                        0, updateCount.length);
                else if (usingDerbyNetClient())
                    /* first statement in the batch caused time out while getting
                     *  the lock, there should be 1 update count of -3 */
                    assertBatchUpdateCounts(new int[] {-3}, updateCount);
            }
        }
        conn.rollback();
        conn2.rollback();
        stmt.clearBatch();
        stmt2.clearBatch();
        stmt.close();
        stmt2.close();
        commit();
        conn2.close();
    }
 

/**
 * @see EntityHome#remove(String)
 */
@Override
public boolean remove(String constrain) {
	// remove all fingerprints for the location, then remove location
	String locationCnst = (constrain != null && constrain.length() > 0) ? constrain : "1=1";
	String fingerprintsCnst = HomeFactory.getFingerprintHome().getTableIdCol() + " IN (SELECT " + HomeFactory.getFingerprintHome().getTableIdCol() + 
																				   " FROM " + HomeFactory.getFingerprintHome().getTableName() +
																				   " WHERE (" + locationCnst + ")) ";
	String measurementsCnst = HomeFactory.getMeasurementHome().getTableIdCol() + " IN (SELECT " + HomeFactory.getFingerprintHome().getTableCols()[1] + 
																				 " FROM " + HomeFactory.getFingerprintHome().getTableName() + 
																				 " WHERE (" + fingerprintsCnst + ")) ";
	String readingInMeasurementCnst = " IN (SELECT readingId FROM readinginmeasurement WHERE (" + measurementsCnst + ")) ";
	
	
	String sql_m = " DELETE FROM " + HomeFactory.getMeasurementHome().getTableName() + " WHERE " + measurementsCnst;
	String sql_wifi = " DELETE FROM " + HomeFactory.getWiFiReadingHome().getTableName() + 
					  " WHERE " + HomeFactory.getWiFiReadingHome().getTableIdCol() + readingInMeasurementCnst;
	String sql_gsm = " DELETE FROM " + HomeFactory.getGSMReadingHome().getTableName() + 
	  				 " WHERE " + HomeFactory.getGSMReadingHome().getTableIdCol() + readingInMeasurementCnst;
	String sql_bluetooth = " DELETE FROM " + HomeFactory.getBluetoothReadingHome().getTableName() + 
	  					   " WHERE " + HomeFactory.getBluetoothReadingHome().getTableIdCol() + readingInMeasurementCnst;
	 
	String sql_rinm = "DELETE FROM readinginmeasurement WHERE " + measurementsCnst;
	String sql_fp = "DELETE FROM " + HomeFactory.getFingerprintHome().getTableName() + " WHERE " + locationCnst;

	String sql_loc = "DELETE FROM " + getTableName() + " WHERE " + locationCnst;
	Statement stat = null;
	
	log.finest(sql_wifi);
	log.finest(sql_gsm);
	log.finest(sql_bluetooth);
	log.finest(sql_rinm);
	log.finest(sql_m);
	log.finest(sql_fp);
	log.finest(sql_loc);
	try {
		int res = -1;
		db.getConnection().setAutoCommit(false);
		stat = db.getConnection().createStatement();
		if (db.getConnection().getMetaData().supportsBatchUpdates()) {
			stat.addBatch(sql_wifi);
			stat.addBatch(sql_gsm);
			stat.addBatch(sql_bluetooth);
			stat.addBatch(sql_rinm);
			stat.addBatch(sql_fp);
			stat.addBatch(sql_m);				
			stat.addBatch(sql_loc);
			int results[] = stat.executeBatch();
			if (results != null && results.length > 0) {
				res = results[results.length - 1];
			}
		} else {
			stat.executeUpdate(sql_wifi);
			stat.executeUpdate(sql_gsm);
			stat.executeUpdate(sql_bluetooth);
			stat.executeUpdate(sql_rinm);
			stat.executeUpdate(sql_fp);
			stat.executeUpdate(sql_m);
			res = stat.executeUpdate(sql_loc);
		}
		db.getConnection().commit();
		return res > 0;
	} catch (SQLException e) {
		log.log(Level.SEVERE, "remove map failed: " + e.getMessage(), e);
	} finally {
		try {
			db.getConnection().setAutoCommit(true);
			if (stat != null) stat.close();
		} catch (SQLException es) {
			log.log(Level.WARNING, "failed to close statement: " + es.getMessage(), es);
		}
	}
	return false;

}