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

/**
 * 保存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();
	}
}
 

@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();
  }
}
 

@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));
}
 

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:'"
        );
    }
}
 

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);
  }
}
 

/**
 *  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");
}
 

@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);
  }
}
 

/**
 * 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();
}
 

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();
    }
}
 

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);
    }
  }
}
 

@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();
    }
}
 

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
  }
}
 

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);
	}
}
 

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);
	}
}
 

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 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());
    }
}
 

@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();
}
 

/**
 * 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);
}
 

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);
    }
}