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

The following examples show how to use java.sql.Statement#executeUpdate() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source Project: Vert.X-generator   File: ConfigUtil.java    License: MIT License 8 votes vote down vote up
/**
 * 保存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();
	}
}
 
Example 2
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:'"
        );
    }
}
 
Example 3
@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));
}
 
Example 4
Source Project: calcite-avatica   File: RemoteMetaTest.java    License: Apache License 2.0 6 votes vote down vote up
@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();
  }
}
 
Example 5
Source Project: ade   File: H2Database.java    License: GNU General Public License v3.0 5 votes vote down vote up
@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();
    }
}
 
Example 6
Source Project: snowflake-jdbc   File: StatementIT.java    License: Apache License 2.0 5 votes vote down vote up
@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();
}
 
Example 7
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());
    }
}
 
Example 8
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))");
}
 
Example 9
Source Project: bboxdb   File: OSMJDBCNodeStore.java    License: Apache License 2.0 5 votes vote down vote up
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);
	}
}
 
Example 10
Source Project: narjillos   File: PersistentDNALog.java    License: MIT License 5 votes vote down vote up
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);
	}
}
 
Example 11
Source Project: RDFS   File: DBCountPageView.java    License: Apache License 2.0 5 votes vote down vote up
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
  }
}
 
Example 12
Source Project: gemfirexd-oss   File: SQLHelper.java    License: Apache License 2.0 5 votes vote down vote up
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);
  }
}
 
Example 13
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);
    }
  }
}
 
Example 14
Source Project: javalite   File: Db2DBReset.java    License: Apache License 2.0 5 votes vote down vote up
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();
    }
}
 
Example 15
/**
 * 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();
}
 
Example 16
Source Project: attic-apex-malhar   File: JdbcIOAppTest.java    License: Apache License 2.0 5 votes vote down vote up
@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);
  }
}
 
Example 17
/**
 *  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");
}
 
Example 18
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);
    }
}
 
Example 19
/**
 * 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();
}
 
Example 20
private void v2ToV3(Statement statement) throws SQLException {
    statement.executeUpdate(AbstractDb.CREATE_VANITY_ADDRESS_SQL);
}