下面列出了java.sql.Statement#executeUpdate ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
/**
* 保存SqlAssist配置文件信息
*
* @param Config
* @throws Exception
*/
public static int saveSqlAssistConfig(SqlAssistConfig config, String name) throws Exception {
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
try {
conn = getConnection();
stat = conn.createStatement();
String jsonStr = JSON.toJSONString(config);
String sql = String.format("replace into SqlAssistConfig (name,value) values('%s', '%s')", name, jsonStr);
int result = stat.executeUpdate(sql);
return result;
} finally {
if (rs != null)
rs.close();
if (stat != null)
stat.close();
if (conn != null)
conn.close();
}
}
private void updateFreeSpaceDefaultName(DatabaseInfo.DbProduct database, Statement stmt) throws SQLException
{
if (database == MYSQL || database == MARIADB)
{
stmt.executeUpdate(
"UPDATE " + TBL_STOR_POOL +
" SET " + NEW_SP_FREE_SPACE_MGR_DSP_NAME + " = CONCAT(" + NODE_NAME + ", ':', " + POOL_NAME + "), " +
NEW_SP_FREE_SPACE_MGR_NAME + " = CONCAT(" + NODE_NAME + ", ':', " + POOL_NAME + ")" +
" WHERE " + NEW_SP_FREE_SPACE_MGR_DSP_NAME + " = 'SYSTEM:'"
);
}
else
{
stmt.executeUpdate(
"UPDATE " + TBL_STOR_POOL +
" SET " + NEW_SP_FREE_SPACE_MGR_DSP_NAME + " = " + NODE_NAME + "|| ':' || " + POOL_NAME + ", " +
NEW_SP_FREE_SPACE_MGR_NAME + " = " + NODE_NAME + " || ':' || " + POOL_NAME +
" WHERE " + NEW_SP_FREE_SPACE_MGR_DSP_NAME + " = 'SYSTEM:'"
);
}
}
@Test
public void testReadList() throws Exception
{
if (LOG.isDebugEnabled()) LOG.debug("Test: 'testReadList'\n");
Statement statement = con.createStatement();
String insert = "INSERT INTO testcollection (k,L) VALUES( 1,[1, 3, 12345]);";
statement.executeUpdate(insert);
ResultSet result = statement.executeQuery("SELECT * FROM testcollection WHERE k = 1;");
result.next();
AssertJUnit.assertEquals(1, result.getInt("k"));
Object myObj = result.getObject("l");
if (LOG.isDebugEnabled()) LOG.debug("l = '{}'\n", myObj);
List<Long> myList = (List<Long>) myObj;
AssertJUnit.assertEquals(3, myList.size());
AssertJUnit.assertTrue(12345L == myList.get(2));
AssertJUnit.assertTrue(myObj instanceof ArrayList);
//myList = (List<Long>) extras(result).getList("l");
statement.close();
//AssertJUnit.assertTrue(3L == myList.get(1));
}
@Test public void testRemoteStatementInsert() throws Exception {
ConnectionSpec.getDatabaseLock().lock();
try {
final String t = AvaticaUtils.unique("TEST_TABLE2");
AvaticaConnection conn = (AvaticaConnection) DriverManager.getConnection(url);
Statement statement = conn.createStatement();
final String create =
String.format(Locale.ROOT, "create table if not exists %s ("
+ " id int not null, msg varchar(255) not null)", t);
int status = statement.executeUpdate(create);
assertEquals(status, 0);
statement = conn.createStatement();
final String update =
String.format(Locale.ROOT, "insert into %s values ('%d', '%s')",
t, RANDOM.nextInt(Integer.MAX_VALUE), UUID.randomUUID());
status = statement.executeUpdate(update);
assertEquals(status, 1);
} finally {
ConnectionSpec.getDatabaseLock().unlock();
}
}
@Override
public void dropDatabase() throws SQLException {
Connection conn = DriverManager.getConnection(getUrl());
Statement stmt = conn.createStatement();
try {
stmt.executeUpdate("DROP ALL OBJECTS DELETE FILES");
} finally {
conn.close();
}
}
@Test
public void testExecuteUpdateAndDelete() throws SQLException
{
Connection connection = getConnection();
Statement statement = connection.createStatement();
statement.execute("create or replace table test_update(cola number, colb string) " +
"as select 1, 'str1'");
statement.execute("insert into test_update values(2, 'str2')");
int updateCount;
boolean success;
updateCount = statement.executeUpdate("update test_update set COLB = 'newStr' where COLA = 1");
assertEquals(1, updateCount);
success = statement.execute("update test_update set COLB = 'newStr' where COLA = 2");
assertFalse(success);
assertEquals(1, statement.getUpdateCount());
assertEquals(1L, statement.getLargeUpdateCount());
assertNull(statement.getResultSet());
updateCount = statement.executeUpdate("delete from test_update where colA = 1");
assertEquals(1, updateCount);
success = statement.execute("delete from test_update where colA = 2");
assertFalse(success);
assertEquals(1, statement.getUpdateCount());
assertEquals(1L, statement.getLargeUpdateCount());
assertNull(statement.getResultSet());
statement.execute("drop table if exists test_update");
statement.close();
connection.close();
}
public static void tstInterruptBatch() throws Exception {
Connection c = DriverManager.getConnection("jdbc:default:connection");
Statement s = c.createStatement();
s.executeUpdate("create table tmp(i int)");
PreparedStatement ps = c.prepareStatement("insert into tmp values (?)");
// fill batch:
for (int i=0; i < 10; i++) {
s.addBatch("insert into tmp values (" + i + ")");
}
s.executeBatch(); // should work OK, since no interrupt present
// refill batch:
for (int i=0; i < 10; i++) {
s.addBatch("insert into tmp values (" + i + ")");
}
try {
Thread.currentThread().interrupt();
s.executeBatch();
fail("expected CONN_INTERRUPT");
} catch (SQLException e) {
assertSQLState("expected CONN_INTERRUPT", "08000", e);
// assertTrue(c.isClosed()); // DERBY-4993
assertTrue(Thread.interrupted());
}
}
public static void decorate(Statement stmt)
throws SQLException
{
// create base tables t1 and t2
stmt.executeUpdate(
"create table t1(i int not null, s smallint, f float, dp "
+ "double precision, v varchar(10) not null)");
stmt.executeUpdate("create table t2 (a int, s varchar(5))");
}
public OSMJDBCNodeStore(final List<String> baseDir, final long inputLength) {
// 5 instances per GB
instances = (int) ((inputLength / (Math.pow(1024, 3)) * 5));
System.out.println("Using DB instances: " + instances);
try {
// Prepare DB_Instances
for(int i = 0; i < instances; i++) {
final String workfolder = baseDir.get(i % baseDir.size());
final Connection connection = DriverManager.getConnection("jdbc:h2:nio:" + workfolder + "/osm_" + i + ".db" + DB_FLAGS);
Statement statement = connection.createStatement();
statement.executeUpdate("DROP TABLE if exists osmnode");
statement.executeUpdate("CREATE TABLE osmnode (id BIGINT PRIMARY KEY, data BLOB)");
statement.close();
final PreparedStatement insertNode = connection.prepareStatement("INSERT into osmnode (id, data) values (?,?)");
final PreparedStatement selectNode = connection.prepareStatement("SELECT data from osmnode where id = ?");
insertNodeStatements.add(insertNode);
selectNodeStatements.add(selectNode);
connection.commit();
connections.add(connection);
}
} catch (SQLException e) {
throw new IllegalArgumentException(e);
}
}
private void createDnaTable() {
Statement statement = createStatement();
try {
String sql = "CREATE TABLE IF NOT EXISTS DNA "
+ "(ID INT PRIMARY KEY NOT NULL,"
+ " GENES STRING NOT NULL,"
+ " PARENT_ID INT NOT NULL,"
+ " IS_DEAD INT NOT NULL)";
statement.executeUpdate(sql);
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
close(statement);
}
}
private void dropTables() {
String dropAccess = "DROP TABLE Access";
String dropPageview = "DROP TABLE Pageview";
try {
Statement st = connection.createStatement();
st.executeUpdate(dropAccess);
st.executeUpdate(dropPageview);
connection.commit();
st.close();
}catch (SQLException ex) {
//ignore
}
}
public static void executeSQL(Connection conn, String sqlCommand, boolean doLog)
throws SQLException{
Statement stmt = conn.createStatement();
if (doLog) {
Log.getLogWriter().info("sql command: " + sqlCommand);
}
stmt.executeUpdate(sqlCommand);
if (doLog) {
Log.getLogWriter().info("completed sql command: " + sqlCommand);
}
}
public void testBug42488() throws Exception {
System.clearProperty(GfxdConstants.GFXD_DISABLE_STATEMENT_MATCHING);
SelectQueryInfo.setTestFlagIgnoreSingleVMCriteria(true);
Connection conn = getConnection();
Statement s = conn.createStatement();
s.execute("create table TESTTABLE (ID int not null , "
+ "DESCRIPTION varchar(1024) , ADDRESS varchar(1024))");
String query1 = "Select * from TESTTABLE where ID >= 1 ";
String query2 = "Select * from TESTTABLE where ID >= 2 ";
s.executeUpdate("insert into testtable values(1,'1','1')");
GemFireXDQueryObserver old = null;
try {
//old = GemFireXDQueryObserverHolder .setInstance(new GemFireXDQueryObserverAdapter() {});
try {
s.executeQuery(query1);
s.executeQuery(query2);
}
catch (SQLException e) {
throw new SQLException(e.toString()
+ " Exception in executing query = " + query1, e.getSQLState());
}
//assertTrue(this.callbackInvoked);
}
finally {
if (old != null) {
GemFireXDQueryObserverHolder.setInstance(old);
}
}
}
static void resetSchema(String[] statements) throws SQLException {
Connection connection = Base.connection();
createDropTableProcedure(connection);
for (String statement: statements) {
if(Util.blank(statement)) continue;
Statement st = connection.createStatement();
st.executeUpdate(statement);
st.close();
}
}
/**
* Inserts a duplicate key of a deleted key within same transaction.
* @throws java.sql.SQLException
*/
public void testWithDeletedKey() throws SQLException {
Connection con = getConnection();
Statement stmt = con.createStatement();
//create unique constraint without not null
stmt.executeUpdate("alter table constraintest add constraint " +
"u_con unique (val1, val2, val3)");
PreparedStatement ps = con.prepareStatement("insert into " +
"constraintest (val1, val2, val3, val4) values (?, ?, ?, ?)");
ps.setString(1, "part1");
ps.setString(2, "part2");
ps.setString(3, "part3");
ps.setString(4, "should pass");
ps.execute();
//delete a record within transaction and try inserting same record
con.setAutoCommit(false);
stmt.executeUpdate("delete from constraintest where " +
"val1 = 'part1' and val2 = 'part2' and val3 = 'part3'");
//insert same record
ps.setString(1, "part1");
ps.setString(2, "part2");
ps.setString(3, "part3");
ps.setString(4, "should pass");
ps.execute();
stmt.close();
ps.close();
con.commit();
}
@BeforeClass
public static void setup()
{
try {
Class.forName(DB_DRIVER).newInstance();
Connection con = DriverManager.getConnection(URL);
Statement stmt = con.createStatement();
String createMetaTable = "CREATE TABLE IF NOT EXISTS " + JdbcTransactionalStore.DEFAULT_META_TABLE + " ( "
+ JdbcTransactionalStore.DEFAULT_APP_ID_COL + " VARCHAR(100) NOT NULL, "
+ JdbcTransactionalStore.DEFAULT_OPERATOR_ID_COL + " INT NOT NULL, "
+ JdbcTransactionalStore.DEFAULT_WINDOW_COL + " BIGINT NOT NULL, " + "UNIQUE ("
+ JdbcTransactionalStore.DEFAULT_APP_ID_COL + ", " + JdbcTransactionalStore.DEFAULT_OPERATOR_ID_COL + ", "
+ JdbcTransactionalStore.DEFAULT_WINDOW_COL + ") " + ")";
stmt.executeUpdate(createMetaTable);
Class.forName(DB_DRIVER).newInstance();
String createTable = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME
+ " (ACCOUNT_NO INTEGER, NAME VARCHAR(255),AMOUNT INTEGER)";
stmt.executeUpdate(createTable);
insertEventsInTable(10, 0);
String createOutputTable = "CREATE TABLE IF NOT EXISTS " + OUTPUT_TABLE_NAME
+ " (ACCOUNT_NO INTEGER, NAME VARCHAR(255),AMOUNT INTEGER)";
stmt.executeUpdate(createOutputTable);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
/**
* Savepoint and Rollback behavior - 3 (3C)
* In the transaction Create savepoint1 and declare temp table t1.
* Create savepoint2 and declare temp table t2.
* Release savepoint 1 and select from temp table t1 and t2 should pass.
* Create savepoint3 and rollback savepoint3(should not touch t1 and t2)
* select from temp tables t1 and t2 should pass.
* Rollback transaction and select from temp tables t1 and t2 should fail.
*
* @throws SQLException
*/
public void testSavepointRollbackbehaviour3() throws SQLException {
Statement s = createStatement();
//set first savepoint
Savepoint savepoint1 = getConnection().setSavepoint();
s.executeUpdate("declare global temporary table SESSION.t1(" +
"c11 int, c12 int) on commit preserve rows not logged");
//set second savepoint
Savepoint savepoint2 = getConnection().setSavepoint();
s.executeUpdate("declare global temporary table SESSION.t2(" +
"c21 int, c22 int) on commit preserve rows not logged");
//release the first savepoint
getConnection().releaseSavepoint(savepoint1);
JDBC.assertSingleValueResultSet(s.executeQuery(
"select count(*) from SESSION.t1") , "0");
JDBC.assertSingleValueResultSet(s.executeQuery(
"select count(*) from SESSION.t2") , "0");
//set third savepoint
Savepoint savepoint3 = getConnection().setSavepoint();
//rollback to third savepoint - (should not touch t1 and t2)
getConnection().rollback(savepoint3);
JDBC.assertSingleValueResultSet(s.executeQuery(
"select count(*) from SESSION.t1") , "0");
JDBC.assertSingleValueResultSet(s.executeQuery(
"select count(*) from SESSION.t2") , "0");
//rollback the entire transaction - this rolls back create statements
//also hence t1 , t2 got deleted
rollback();
assertStatementError("42X05" , s , "select * from SESSION.t1");
assertStatementError("42X05" , s , "select * from SESSION.t2");
}
private void helpTestQueryTimeout(String procedure, int timeoutSecs, int sleepSecs) throws Exception {
// we expect a query timeout if we're to sleep longer or equal to the timeout we're setting
boolean expectTimeout = (sleepSecs >= timeoutSecs);
String sqlText = String.format("SELECT * FROM %s.%s", schemaWatcher.schemaName, "A");
// get a representative expected result. we'll use this to compare with later result
// to make sure a roll back occurred if there was a query timeout.
ResultSet rs = classWatcher.executeQuery(sqlText);
String expectedResults = TestUtils.FormattedResult.ResultFactory.toStringUnsorted(rs);
rs.close();
String updateTxt = String.format("insert into %s.%s values ("+timeoutSecs+","+sleepSecs+")",
schemaWatcher.schemaName, "A");
Statement statement = null;
String updateSQL = String.format(procedure, updateTxt, sleepSecs);
try {
statement = classWatcher.getStatement();
// set the provided timeout on the Statement
statement.setQueryTimeout(timeoutSecs);
statement.executeUpdate(updateSQL);
if (expectTimeout) {
// we updated the table and expected to get a query timeout exception
Assert.fail(updateSQL+"\nExpected the update to timeout. Timeout set to: "+timeoutSecs+" sec; slept: "+sleepSecs+" sec.");
}
} catch (SQLException e) {
// can happen if procedure is not compiled/jarred/installed correctly
Assert.assertFalse(e.getMessage(), e.getMessage().contains("is not recognized as a function or procedure"));
Assert.assertFalse(e.getMessage(), e.getMessage().contains("does not exist or is inaccessible."));
if (expectTimeout) {
// We timed out and caught an exception. Make sure we get the correct exception
Assert.assertEquals("Expected a query timeout.", "XCL52", e.getSQLState());
// Make sure the update txn got rolled back
rs = classWatcher.executeQuery(sqlText);
Assert.assertEquals("Expected update should have rolled back.\n" + updateSQL + "\n", expectedResults,
TestUtils.FormattedResult.ResultFactory.toStringUnsorted(rs));
} else {
// unexpected
Assert.fail(String.valueOf(e));
}
} finally {
closeQuietly(rs);
}
}
/**
* Test that table functions can be invoked from inside jar files stored in
* the database.
*/
public void testTableFunctionInJar() throws SQLException, MalformedURLException
{
String jarName = "EMC.DUMMY_VTI";
installJar("dummy_vti.jar", jarName );
setDBClasspath( jarName );
Statement s = createStatement();
// register a scalar function
s.executeUpdate
(
"create function reciprocal( original double ) returns double\n" +
"language java\n" +
"parameter style java\n" +
"no sql\n" +
"external name 'DummyVTI.reciprocal'"
);
// register the table function
s.executeUpdate
(
"create function dummyVTI()\n" +
"returns table( tablename varchar( 128 ) )\n" +
"language java\n" +
"parameter style DERBY_JDBC_RESULT_SET\n" +
"reads sql data\n" +
"external name 'DummyVTI.dummyVTI'\n"
);
// register another table function in a class which doesn't exist
s.executeUpdate
(
"create function dummyVTI2()\n" +
"returns table( tablename varchar( 128 ) )\n" +
"language java\n" +
"parameter style DERBY_JDBC_RESULT_SET\n" +
"reads sql data\n" +
"external name 'MissingClass.dummyVTI'\n"
);
// invoke the scalar function
JDBC.assertFullResultSet(
s.executeQuery
(
"values ( reciprocal( 2.0 ) )"
),
new String[][] {
{"0.5"},
});
// invoke the table function
JDBC.assertFullResultSet(
s.executeQuery
(
"select * from table( dummyVTI() ) s where tablename='SYSTABLES'"
),
new String[][] {
{"SYSTABLES"},
});
// verify that a missing class raises an exception
try {
s.executeQuery
(
"select * from table( dummyVTI2() ) s where tablename='SYSTABLES'"
);
fail( "Should have seen a ClassNotFoundException." );
} catch (SQLException e) {
assertSQLState("XJ001", e);
}
// drop the useless function
s.executeUpdate( "drop function dummyVTI2\n" );
setDBClasspath(null);
s.close();
}
private void v2ToV3(Statement statement) throws SQLException {
statement.executeUpdate(AbstractDb.CREATE_VANITY_ADDRESS_SQL);
}