Java Code Examples for java.sql.Connection#setAutoCommit()

/**
 * Updates a page record in the database.
 *
 * @param page The page to update
 */
@Override
public void updatePage(IPage page) {
    Connection conn = null;
    try {
        conn = this.getConnection();
        conn.setAutoCommit(false);
        String pageCode = page.getCode();
        this.deleteDraftWidgets(pageCode, conn);
        this.deleteDraftPageMetadata(pageCode, conn);
        this.updatePageRecord(page, conn);
        PageMetadata metadata = page.getMetadata();
        metadata.setUpdatedAt(new Date());
        this.addDraftPageMetadata(pageCode, page.getMetadata(), conn);
        this.addWidgetForPage(page, WidgetConfigDest.DRAFT, conn);
        conn.commit();
    } catch (Throwable t) {
        this.executeRollback(conn);
        _logger.error("Error while updating the page", t);
        throw new RuntimeException("Error while updating the page", t);
    } finally {
        closeConnection(conn);
    }
}
 

/**
 * Stores a "dataversion" record in the ES store.  There is only a single one of these.  The
 * return value of the add will include the version number of the entity.  This version
 * number is what we use to determine whether our cache is stale.
 */
protected void updateDataVersion() {
    Connection conn = null;
    try {
        long newVersion = System.currentTimeMillis();

        conn = ds.getConnection();
        conn.setAutoCommit(false);
        QueryRunner run = new QueryRunner();

        run.update(conn, "DELETE FROM gw_dataversion"); //$NON-NLS-1$
        run.update(conn, "INSERT INTO gw_dataversion (version) VALUES (?)",  //$NON-NLS-1$
                newVersion);

        DbUtils.commitAndClose(conn);
        dataVersion = newVersion;
    } catch (SQLException e) {
        dataVersion = -1;
    }
}
 

@Test
public void testSelectValueWithPointKeyQuery() throws Exception {
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    conn.setAutoCommit(false);
    try {
        initTableValues();
        String query;
        PreparedStatement stmt;
        ResultSet rs;
        
        query = "SELECT * FROM " + TEST_TABLE + " where key_string = 'abc'";
        stmt = conn.prepareStatement(query);
        rs = stmt.executeQuery();
        assertTrue(rs.next());
        assertEquals("abc", rs.getString(1));
        assertEquals(3, rs.getInt(2));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

@Override
public void updateMapping(FriendlyCodeVO vo) {
	Connection conn = null;
	try {
		conn = this.getConnection();
		conn.setAutoCommit(false);
           super.executeQueryWithoutResultset(conn, DELETE_FROM_FRIENDLYCODE, vo.getFriendlyCode());
		this.addRecord(vo, conn);
		conn.commit();
	} catch (Throwable t) {
		this.executeRollback(conn);
		_logger.error("Error update the mapping",  t);
		throw new RuntimeException("Error update the mapping", t);
	} finally {
		this.closeConnection(conn);
	}
}
 

@Test(expected = SQLException.class)
public void testConnectionClosed() throws Exception {
	Connection conn = createMock(Connection.class);
	conn.setAutoCommit(true);
	expect(conn.isClosed()).andReturn(true);
	Driver driver = createMock(Driver.class);
	String url = "jdbc:bar:baz";
	expect(driver.acceptsURL(url)).andReturn(true);
	expect(driver.connect(isA(String.class), isA(Properties.class))).andReturn(conn);
	replay(driver, conn);
	DriverManager.registerDriver(driver);
	try {
		JdbcConnectionSource sds = new JdbcConnectionSource(url, databaseType);
		assertNotNull(sds.getReadOnlyConnection(null));
		sds.getReadOnlyConnection(null);
		sds.close();
		fail("Should not get here");
	} finally {
		DriverManager.deregisterDriver(driver);
	}
}
 

public void testGroupByCount(boolean localIndex) throws Exception {
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    conn.setAutoCommit(false);
    ensureTableCreated(getUrl(), INDEX_DATA_TABLE);
    populateTestTable();
    String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX IDX ON " + INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE
            + " (int_col2)";
    PreparedStatement stmt = conn.prepareStatement(ddl);
    stmt.execute();
    
    ResultSet rs;
    
    rs = conn.createStatement().executeQuery("SELECT int_col2, COUNT(*) FROM " +INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + INDEX_DATA_TABLE + " GROUP BY int_col2");
    assertTrue(rs.next());
    assertEquals(1,rs.getInt(2));
}
 

public static Connection getDBConnection(DataSource dataSource) throws SQLException {
    Connection dbConnection = dataSource.getConnection();
    dbConnection.setAutoCommit(false);
    if (dbConnection.getTransactionIsolation() != Connection.TRANSACTION_READ_COMMITTED) {
        dbConnection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    }
    return dbConnection;
}
 

@After
public void tearDown() throws Exception {
    final Connection c = this.dataSource.getConnection();
    final Statement s = c.createStatement();
    c.setAutoCommit(true);

    for (int i = 0; i < 5; i++) {
        final String sql = String.format("delete from casusers;");
        s.execute(sql);
    }
    c.close();
}
 

@Test
public void testSelectDiff() throws Exception {
    String tableName1 = generateUniqueName();
    String tableName2 = generateUniqueName();
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    conn.setAutoCommit(false);

    try {
        String ddl = "CREATE TABLE " + tableName1 + " " +
                "  (a_string varchar not null, col1 integer" +
                "  CONSTRAINT pk PRIMARY KEY (a_string))\n";
        createTestTable(getUrl(), ddl);

        ddl = "CREATE TABLE " + tableName2 + " " +
                "  (a_string varchar not null, col1 integer" +
                "  CONSTRAINT pk PRIMARY KEY (a_string))\n";
        createTestTable(getUrl(), ddl);

        ddl = "select a_string, col1, col1 from " + tableName1 + " union all select * from " + tableName2 + " union all select a_string, col1 from " + tableName1;
        conn.createStatement().executeQuery(ddl);
        fail();
    }  catch (SQLException e) {
        assertEquals(SQLExceptionCode.SELECT_COLUMN_NUM_IN_UNIONALL_DIFFS.getErrorCode(), e.getErrorCode());
    } finally {
        conn.close();
    }
}
 

@Test
public void testSubqueryWithUpsert() throws Exception {
    String tempTable = generateUniqueName();
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    conn.setAutoCommit(true);
    String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
    String tableName4 = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME);
    try {            
        conn.createStatement().execute("CREATE TABLE " + tempTable 
                + "   (item_id varchar not null primary key, " 
                + "    name varchar)");
        conn.createStatement().execute("UPSERT INTO " + tempTable + "(item_id, name)"
                + "   SELECT \"item_id\", name FROM " + tableName1 
                + "   WHERE \"item_id\" NOT IN (SELECT \"item_id\" FROM " + tableName4 + ")");
        
        String query = "SELECT name FROM " + tempTable + " ORDER BY item_id";
        PreparedStatement statement = conn.prepareStatement(query);
        ResultSet rs = statement.executeQuery();
        assertTrue (rs.next());
        assertEquals(rs.getString(1), "T4");
        assertTrue (rs.next());
        assertEquals(rs.getString(1), "T5");
        assertTrue (rs.next());
        assertEquals(rs.getString(1), "INVALID-1");

        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

@BeforeClass
public static void addDatabaseUserWithPassword() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    Connection conn = DriverManager.getConnection(DATASOURCE_URL, "sa", "");
    conn.setAutoCommit(true);
    Statement st = conn.createStatement();
    st.executeUpdate("CREATE USER " + USER + " PASSWORD '" + PASSWORD + "';");
    st.close();
    conn.commit();
    conn.close();
}
 

public static synchronized Connection getConnection(String protocol,
      Properties props) throws SQLException {

    loadDriverClass(getDriver());
    props = doCommonSetup(props);

    final Connection conn = DriverManager.getConnection(protocol, props);
    
    boolean nonTXTestMode = Boolean.getBoolean(SanityManager.TEST_MODE_NON_TX)
        || Boolean.parseBoolean(System.getenv(SanityManager.TEST_MODE_NON_TX));
//    if (nonTXTestMode) {
      System.out.println("Non-tx test mode.");
      conn.setAutoCommit(false);
      conn.setTransactionIsolation(Connection.TRANSACTION_NONE);
//    }
    LogWriter logger = TestUtil.getLogger();
    if (logger != null) {
      logger.info("TestUtil.getConnection::Autocommit is " + conn.getAutoCommit());
    }
    
    // Read the flag for deleting persistent files only during boot up
    if (jdbcConn == null || jdbcConn.isClosed()) {
      jdbcConn = conn;
      currentUserName = props.getProperty(Attribute.USERNAME_ATTR);
      currentUserName = currentUserName == null ? props
          .getProperty(Attribute.USERNAME_ALT_ATTR) : currentUserName;
      currentUserPassword = props.getProperty(Attribute.PASSWORD_ATTR);
    }
    return conn;
  }
 

public void deleteApplication(String name) {
	Application app = getApplication(name);

	// delete infos
	Connection conn = null;
	boolean autoCommit = true;
	try {
		conn = DBPower.getConnection(table.getId());
		autoCommit = conn.getAutoCommit();
		conn.setAutoCommit(false);

		ApplicationUserType appUserType = new ApplicationUserType();
		appUserType.setAppName(name);
		applicationUserTypeDeletor.delete(conn,
				appNameUserTypeTableWhereEmt, appUserType);

		Application hint = new Application();
		hint.setName(name);
		deletor.delete(conn, appNameWhereEmt, hint);

		conn.commit();
	} catch (SQLException e) {
		DBUtil.rollback(conn);
		throw new DBLevelException(e);
	} finally {
		DBUtil.setCommitMode(conn, autoCommit);
		DBUtil.close(conn);
	}

	changed = true;

	// delete tables
	deleteTablesForApp(app);

	// notify Factory
	org.ralasafe.Factory.applicationChanged(app.getName());
}
 

@Override
protected void doOpen() {
    if (isOpen())
        throw Transaction.Exceptions.transactionAlreadyOpen();
    else {
        try {
            Connection connection = this.sqlgGraph.getConnection();
            connection.setAutoCommit(false);
            if (this.sqlgGraph.getSqlDialect().supportsClientInfo()) {
                String applicationName = Thread.currentThread().getName();
                if (applicationName.length() > 63) {
                    String first = applicationName.substring(0, 30);
                    String last = applicationName.substring(applicationName.length() - 30);
                    applicationName =  first + "..." + last;
                }
                connection.setClientInfo("ApplicationName", applicationName);
            }
            // read default setting for laziness
            boolean lazy = this.sqlgGraph.getConfiguration().getBoolean(QUERY_LAZY, true);
            TransactionCache tc;
            if (supportsBatchMode()) {
                tc = TransactionCache.of(this.cacheVertices, connection, new BatchManager(this.sqlgGraph, ((SqlBulkDialect) this.sqlgGraph.getSqlDialect())), lazy);
            } else {
                tc = TransactionCache.of(this.cacheVertices, connection, lazy);
            }
            tc.setFetchSize(getDefaultFetchSize());
            this.threadLocalTx.set(tc);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
 

@Override
public void run() {
    Connection con = null;
    try {

        List<Map<String, String>> batch = getNextBatch();
        while (!batch.isEmpty()) {
            try {
                if (con == null || con.isClosed()) {
                    con = conPool.getConnection();
                    con.setAutoCommit(false);
                }

                insert(con, batch);
                finshiedCount.addAndGet(batch.size());
            } catch (Exception e) {
                e.printStackTrace();
                try {
                    con.rollback();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                    e1.printStackTrace();
                }
                failedCount.addAndGet(batch.size());
            }
            batch = getNextBatch();
        }
    } finally {
        if (con != null) {
            this.conPool.returnCon(con);
        }
    }

}
 

/**
 * Sets the auto commit to false.
 */
protected void initializeConnection(Connection conn) throws SQLException {
	conn.setAutoCommit(false);
}
 

public void testBatchInsert_FkOnPkViolation() throws Exception {
  Properties props = new Properties();
  System.clearProperty(GfxdConstants.GFXD_ENABLE_BULK_FK_CHECKS);
  props.setProperty(Attribute.ENABLE_BULK_FK_CHECKS, "true");
  startVMs(1, 3, 0, null, props);
  Connection conn = TestUtil.getConnection(props);
  Statement st = conn.createStatement();
  conn.setAutoCommit(false);
  conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

  // create tables
  st.execute("create table parent (col1 int, col2 int, col3 int not null, "
      + "constraint pk1 primary key (col1)) partition by list "
      + "(col3) (VALUES (1), VALUES (2), VALUES (3), values (4))");

  st.execute("create table child (col1 int, col2 int, col3 int not null, "
      + "constraint pk2 primary key (col1), constraint fk1 foreign key "
      + "(col2) references parent (col1)) partition by list "
      + "(col3) (VALUES (1), VALUES (2), VALUES (3), values (4))");

  st.execute("insert into parent values (1, 1, 1), (2, 2, 2), "
      + "(3, 3, 3), (4, 4, 4)");
  conn.commit();
  PreparedStatement pstmt = conn.prepareStatement("insert into child "
      + "values (?, ?, ?)");

  for (int i = 1; i <= 3; i++) {
    pstmt.setInt(1, i);
    pstmt.setInt(2, i);
    pstmt.setInt(3, i);
    pstmt.addBatch();
  }
  
  // this row to cause an FK violation 
  pstmt.setInt(1, 4);
  pstmt.setInt(2, 100); // FK violation
  pstmt.setInt(3, 4);
  pstmt.addBatch();
  // one more row with no error
  pstmt.setInt(1, 5);
  pstmt.setInt(2, 3);
  pstmt.setInt(3, 4);
  pstmt.addBatch();
  
  try {
    int[] ret = pstmt.executeBatch();
    fail("This statement should have failed due to FK violation");
  } catch (java.sql.BatchUpdateException be) {
    assertEquals("23503", be.getSQLState());
  }
  
  // no rows should be inserted
  ResultSet rs = st.executeQuery("select count(*) from child");
  assertTrue(rs.next());
  assertEquals(0, rs.getInt(1));
}
 

@Override
public void initializeConnection(Connection connection) throws SQLException {
	// Disable auto-commit in PostgreSQL to support cursors
	// @see http://jdbc.postgresql.org/documentation/83/query.html
	connection.setAutoCommit(false);
}
 

/**
 * Tests whether re-connect with non-read-only connection can happen.
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testFailoverConnection() throws Exception {

    if (!isServerRunningOnWindows()) { // windows sockets don't work for this test
        Properties props = new Properties();
        props.setProperty("autoReconnect", "true");
        props.setProperty("failOverReadOnly", "false");

        Properties urlProps = new NonRegisteringDriver().parseURL(dbUrl, null);

        String host = urlProps.getProperty(NonRegisteringDriver.HOST_PROPERTY_KEY);
        String port = urlProps.getProperty(NonRegisteringDriver.PORT_PROPERTY_KEY);

        props.setProperty(NonRegisteringDriver.HOST_PROPERTY_KEY + ".1", host);
        props.setProperty(NonRegisteringDriver.PORT_PROPERTY_KEY + ".1", port);
        props.setProperty(NonRegisteringDriver.HOST_PROPERTY_KEY + ".2", host);
        props.setProperty(NonRegisteringDriver.PORT_PROPERTY_KEY + ".2", port);
        props.setProperty(NonRegisteringDriver.NUM_HOSTS_PROPERTY_KEY, "2");

        Connection failoverConnection = null;

        try {
            failoverConnection = getConnectionWithProps(props);

            String originalConnectionId = getSingleIndexedValueWithQuery(failoverConnection, 1, "SELECT connection_id()").toString();
            System.out.println("Original Connection Id = " + originalConnectionId);

            assertTrue("Connection should not be in READ_ONLY state", !failoverConnection.isReadOnly());

            // Kill the connection
            this.stmt.executeUpdate("KILL " + originalConnectionId);

            // This takes a bit to occur

            Thread.sleep(3000);

            try {
                failoverConnection.createStatement().execute("SELECT 1");
                fail("We expect an exception here, because the connection should be gone until the reconnect code picks it up again");
            } catch (SQLException sqlEx) {
                // do-nothing
            }

            // Tickle re-connect

            failoverConnection.setAutoCommit(true);

            String newConnectionId = getSingleIndexedValueWithQuery(failoverConnection, 1, "SELECT connection_id()").toString();
            System.out.println("new Connection Id = " + newConnectionId);

            assertTrue("We should have a new connection to the server in this case", !newConnectionId.equals(originalConnectionId));
            assertTrue("Connection should not be read-only", !failoverConnection.isReadOnly());
        } finally {
            if (failoverConnection != null) {
                failoverConnection.close();
            }
        }
    }
}
 

private static void testTwiceCommited()
	throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
	
	final String dbname = "testTwiceCommitedDB";
	Connection conn = null;
	

	try{
		conn = openConnectionToNewDatabase(dbname);
		createTestTable(conn);

		conn.setAutoCommit(false);
		insertIntoTestTable(conn,
				    1,
				    1000);
		conn.commit();
		insertIntoTestTable(conn,
				    1001,
				    999);
		conn.commit();

		shutdownDatabase(dbname);

	}catch(SQLException e){
		verifyShutdownError(e);
	}
	
	
	conn = null;
	
	try{
		conn = reopenConnectionToDatabase(dbname);
		countRowInTestTable(conn);
		
	}finally{
		if(conn != null){
			conn.close();
			conn = null;
		}
	}
	
}