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

/**
 * Perform upgrade.
 */
public void upgrade(final Connection conn, final int upgradeToVersion) throws SQLException {
  final boolean savedAutoCommit = conn.getAutoCommit();
  Statement st = null; // NOPMD
  try {
    // create statement
    conn.setAutoCommit(true);
    st = conn.createStatement();

    LOG.debug("Altering table");
    PersistanceUtils.executeDDLs(st, new String[]{
            " alter table TEST_CASE drop constraint TEST_CASE_UC2",
            " alter table TEST_CASE add constraint TEST_CASE_UC2 unique (TEST_PACKAGE_ID, NAME)",
    });

    LOG.debug("Updating version");
    st.executeUpdate("update SYSTEM_PROPERTY set VALUE = '" + upgraderVersion() + "' where NAME = 'parabuild.schema.version' ");

    // finish
    conn.commit();

  } finally {
    IoUtils.closeHard(st);
    conn.setAutoCommit(savedAutoCommit);
  }
}
 

public void testBug51776() throws Exception {
    Properties props = getHostFreePropertiesFromTestsuiteUrl();
    props.setProperty("socketFactory", "testsuite.UnreliableSocketFactory");

    NonRegisteringDriver d = new NonRegisteringDriver();
    Properties parsed = d.parseURL(BaseTestCase.dbUrl, null);
    String db = parsed.getProperty(NonRegisteringDriver.DBNAME_PROPERTY_KEY);
    String port = parsed.getProperty(NonRegisteringDriver.PORT_PROPERTY_KEY);
    String host = getPortFreeHostname(props, d);

    UnreliableSocketFactory.flushAllStaticData();
    UnreliableSocketFactory.mapHost("first", host);

    Connection testConn = getConnectionWithProps("jdbc:mysql://first:" + port + "/" + db, props);
    testConn.setAutoCommit(false);
    testConn.createStatement().execute("SELECT 1");
    UnreliableSocketFactory.downHost("first");
    try {
        testConn.rollback();
        fail("Should receive SQLException on rollback().");
    } catch (SQLException e) {

    }

}
 

public void flush(final List<String> sqlBatch) throws SQLException {
    if (sqlBatch == null || sqlBatch.isEmpty()) {
        return;
    }
    Connection connection = connectionHolder.getValidConnection();
    connection.setAutoCommit(false);

    Statement statement = connection.createStatement();
    for (String sql : sqlBatch) {
        log.info("===>>>statement addBatch sql:{}", sql);
        statement.addBatch(sql);
    }
    int[] updateCountArr = statement.executeBatch();
    if (updateCountArr.length != sqlBatch.size()) {
        throw new ConnectException(String.format("updateCountArr size:(%d) not equals to sqlBatch size:(%d)", updateCountArr.length, sqlBatch.size()));
    }
    connection.commit();
    statement.close();
}
 

/**
 * Make sure to close after using the returned connection
 * (like in a finally block).
 */
protected Connection getConnection(String id) throws SQLException {

    Connection c = DriverManager.getConnection("jdbc:hsqldb:file:"
        + baseDir.getAbsolutePath() + '/' + id + "/dbfile", "SA", "");

    if (verbose) {
        System.err.println("Opening JDBC URL '" + "jdbc:hsqldb:file:"
                           + baseDir.getAbsolutePath() + '/' + id
                           + "/dbfile");
    }

    c.setAutoCommit(false);

    return c;
}
 

/**
 * Get a connection from URL. Initialized with <code>connectionsProperties</code>. Set autoCommit to false in case of UPDATE.
 * @param context
 * @param connectionURL
 * @param connectionsProperties
 * @param autoCommit
 * @return
 * @throws SQLException
 */
@VisibleForTesting
Connection newConnection(final Context context, final String connectionURL, final Properties connectionsProperties,
		boolean autoCommit)
		throws SQLException {
	final Connection conn = DriverManager.getConnection(connectionURL, connectionsProperties);
	conn.setAutoCommit(autoCommit);
	context.getLogger().info("Connected to database: " + connectionURL);
	return conn;
}
 

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

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

				insert(con, batch);
				finshiedCount.addAndGet(batch.size());
			} catch (Exception e) {
				System.out.println("caught err in  thread :"
						+ Thread.currentThread().getId() + " " + e);
				try {
					con.rollback();
				} catch (SQLException e1) {
					System.out.println("caught err in thread :"
							+ Thread.currentThread().getId()
							+ " rollback err " + e1);
				}
				failedCount.addAndGet(batch.size());
			}
			batch = getNextBatch();
		}
	} finally {
		if (con != null) {
			this.conPool.returnCon(con);
		}
	}

}
 

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

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

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

public void setDefaults(Connection connection) throws SQLException {

        connection.setHoldability(this.holdability);

        if (this.transactionIsolation != Connection.TRANSACTION_NONE) {
            connection.setTransactionIsolation(this.transactionIsolation);
        }
        connection.setAutoCommit(this.isAutoCommit);
        connection.setReadOnly(this.isReadOnly);
        connection.setCatalog(this.catalog);
    }
 

private static void doSomeWork() throws SQLException {

        Connection conn = getConnection();
        Statement  stmt = conn.createStatement();

        conn.setAutoCommit(false);
        stmt.execute("INSERT INTO trig_test VALUES (1, 'hello')");
        stmt.execute("INSERT INTO trig_test VALUES (2, 'now what?')");
        stmt.execute("INSERT INTO trig_test VALUES (3, 'unchangable')");
        stmt.execute("INSERT INTO trig_test VALUES (4, 'goodbye')");
        conn.commit();
        dumpTable("trig_test");
        stmt.execute("UPDATE trig_test SET value = 'all done'");
        conn.commit();
        dumpTable("trig_test");
        stmt.execute("DELETE FROM trig_test");
        conn.rollback();
        dumpTable("trig_test");

        try {
            stmt.execute("INSERT INTO trig_test VALUES(11, 'whatever')");
        } catch (SQLException se) {
            se.printStackTrace();
        }

        stmt.execute("INSERT INTO trig_test VALUES(10, 'whatever')");
        conn.commit();
        dumpTable("trig_test");
        stmt.close();
        conn.close();
    }
 

public static Connection getConnection(String poolName) throws Exception {
	Connection conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:" + poolName);
	/*
	switch (getConfigInfo(poolName).DB_TYPE){
		case Constant.DB_TYPE.ASE :
			conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
	}
	*/
	conn.setAutoCommit(false);
	
	return conn;
}
 

public DatabaseUtil(String dbfile) {
	Connection c = null;
      
	try {
		Class.forName("org.sqlite.JDBC");
		c = DriverManager.getConnection("jdbc:sqlite:" + dbfile);
		c.setAutoCommit(false);
	} catch ( Exception e ) {
		e.printStackTrace();
	}
	this.connection = c;
	System.out.println("Opened database successfully");
}
 

protected boolean remove(Object obj, Connection conn) {

		Class clz = obj.getClass();

		String sql = MapperFactory.getSql(clz, Mapper.REMOVE);

		boolean flag = false;
		boolean isNoBizTx = false;

		PreparedStatement pstmt = null;
		try {

			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(sql);

			isNoBizTx = Tx.isNoBizTx();
			if (!isNoBizTx) {
				Tx.add(pstmt);
			}

			Parsed parsed = Parser.get(clz);

			int i = 1;

			SqlUtil.adpterSqlKey(pstmt, parsed.getKeyField(X.KEY_ONE), null, obj, i);

			flag = pstmt.executeUpdate() == 0 ? false : true;

			if (isNoBizTx) {
				conn.commit();
			}

		} catch (Exception e) {
			e.printStackTrace();
			if (isNoBizTx) {
				try {
					conn.rollback();
					System.out.println("line 334 " + e.getMessage());
					e.printStackTrace();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			} else {
				throw new RollbackException("RollbackException: " + e.getMessage());
			}
		} finally {
			if (isNoBizTx) {
				close(pstmt);
				close(conn);
			}
		}

		return flag;
	}
 

public void testBug51643() throws Exception {
    Properties props = new Properties();
    props.setProperty("loadBalanceStrategy", "com.mysql.jdbc.SequentialBalanceStrategy");

    Connection lbConn = getUnreliableLoadBalancedConnection(new String[] { "first", "second" }, props);
    try {
        PreparedStatement cPstmt = lbConn.prepareStatement("SELECT connection_id()");
        PreparedStatement serverPstmt = lbConn.prepareStatement("SELECT connection_id()");
        Statement plainStmt = lbConn.createStatement();

        lbConn.setAutoCommit(false);
        this.rs = cPstmt.executeQuery();
        this.rs.next();
        String cPstmtConnId = this.rs.getString(1);

        this.rs = serverPstmt.executeQuery();
        this.rs.next();
        String serverPstmtConnId = this.rs.getString(1);

        this.rs = plainStmt.executeQuery("SELECT connection_id()");
        this.rs.next();
        String plainStmtConnId = this.rs.getString(1);
        lbConn.commit();
        lbConn.setAutoCommit(false);

        this.rs = cPstmt.executeQuery();
        this.rs.next();
        String cPstmtConnId2 = this.rs.getString(1);
        assertFalse(cPstmtConnId2.equals(cPstmtConnId));

        this.rs = serverPstmt.executeQuery();
        this.rs.next();
        String serverPstmtConnId2 = this.rs.getString(1);
        assertFalse(serverPstmtConnId2.equals(serverPstmtConnId));

        this.rs = plainStmt.executeQuery("SELECT connection_id()");
        this.rs.next();
        String plainStmtConnId2 = this.rs.getString(1);
        assertFalse(plainStmtConnId2.equals(plainStmtConnId));
    } finally {
        lbConn.close();
    }
}
 

/**
 * Performs the application migration process in one go
 *
 * @param context the database context to run the patches in
 * @return the number of patches applied
 * @throws SQLException       if an unrecoverable database error occurs while working with the patches table.
 * @throws MigrationException if an unrecoverable error occurs during the migration
 */
protected int doMigrations(JdbcMigrationContext context) throws SQLException, MigrationException
{
    PatchInfoStore patchTable = createPatchStore(context);

    lockPatchStore(context);

    // Now apply the patches
    int executedPatchCount = 0;
    try
    {

        // remember the auto-commit state, and turn auto-commit off
        Connection conn = context.getConnection();
        boolean commitState = conn.getAutoCommit();
        conn.setAutoCommit(false);

        // run the migrations
        try
        {
            executedPatchCount = migrationProcess.doMigrations(patchTable,
                    context);
        }

        // restore autocommit state
        finally
        {
            if ((conn != null) && !conn.isClosed())
            {
                conn.setAutoCommit(commitState);
            }
        }
    }
    catch (MigrationException me)
    {
        // If there was any kind of error, we don't want to eat it, but we do
        // want to unlock the patch store. So do that, then re-throw.
        patchTable.unlockPatchStore();
        throw me;
    }

    // Do any post-patch tasks
    try
    {
        migrationProcess.doPostPatchMigrations(context);
        return executedPatchCount;
    }
    finally
    {
        try
        {
            patchTable.unlockPatchStore();
        }
        catch (MigrationException e)
        {
            log.error("Error unlocking patch table: ", e);
        }
    }
}
 

public void doZeroTimestampTest(int testNum, boolean expectSuccess,
    String connectString) throws IOException, SQLException {

  LOG.info("Beginning zero-timestamp test #" + testNum);

  try {
    final String TABLE_NAME = "mysqlTimestampTable"
        + Integer.toString(testNum);

    // Create a table containing a full-zeros timestamp.
    SqoopOptions options = new SqoopOptions(connectString, TABLE_NAME);
    options.setUsername(AUTH_TEST_USER);
    options.setPassword(AUTH_TEST_PASS);

    manager = new DirectMySQLManager(options);

    Connection connection = null;
    Statement st = null;

    connection = manager.getConnection();
    connection.setAutoCommit(false);
    st = connection.createStatement();

    // create the database table and populate it with data.
    st.executeUpdate("DROP TABLE IF EXISTS " + TABLE_NAME);
    st.executeUpdate("CREATE TABLE " + TABLE_NAME + " ("
        + "id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, "
        + "ts TIMESTAMP NOT NULL)");

    st.executeUpdate("INSERT INTO " + TABLE_NAME + " VALUES("
        + "NULL,'0000-00-00 00:00:00.0')");
    connection.commit();
    st.close();
    connection.close();

    // Run the import.
    String [] argv = getArgv(true, false, connectString, TABLE_NAME);
    try {
      runImport(argv);
    } catch (Exception e) {
      if (expectSuccess) {
        // This is unexpected. rethrow.
        throw new RuntimeException(e);
      } else {
        // We expected an error.
        LOG.info("Got exception running import (expected). msg: " + e);
      }
    }

    // Make sure the result file is there.
    Path warehousePath = new Path(this.getWarehouseDir());
    Path tablePath = new Path(warehousePath, TABLE_NAME);
    Path filePath = new Path(tablePath, "part-m-00000");

    File f = new File(filePath.toString());
    if (expectSuccess) {
      assertTrue("Could not find imported data file", f.exists());
      BufferedReader r = new BufferedReader(new InputStreamReader(
          new FileInputStream(f)));
      assertEquals("1,null", r.readLine());
      IOUtils.closeStream(r);
    } else {
      assertFalse("Imported data when expected failure", f.exists());
    }
  } finally {
    LOG.info("Finished zero timestamp test #" + testNum);
  }
}
 

/**
   * Create a connection to the database; usually used only from within
   * getConnection(), which enforces a singleton guarantee around the
   * Connection object.
   */
  protected Connection makeConnection() throws SQLException, Exception {

    Connection connection;
    String driverClass = getDriverClass();

    try {
      Class.forName(driverClass);
    } catch (ClassNotFoundException cnfe) {
      throw new RuntimeException("Could not load db driver class: "
          + driverClass);
    }

    String username = options.getString(DBConfiguration.DataSourceInfo.USERNAME_PROPERTY);
    String password = options.getString(DBConfiguration.DataSourceInfo.PASSWORD_PROPERTY);
//    String connectString = options.getString(DBConfiguration.DataSourceInfo.URL_PROPERTY_READ_ONLY);
//    if(!readOnly){
//        connectString = (String)(options.get(DBConfiguration.DataSourceInfo.URL_PROPERTY_READ_WRITE));
//    }
    String connectionParamsStr = options.getString(DBConfiguration.CONNECTION_PARAMS_PROPERTY);
    Properties connectionParams = DBConfiguration.propertiesFromString(connectionParamsStr);
     
    if (connectionParams != null && connectionParams.size() > 0) {
      LOG.debug("User specified connection params. "
              + "Using properties specific API for making connection.");
      
      Properties props = new Properties();
      if (username != null) {
        props.put("user", username);
      }

      if (password != null) {
        props.put("password", password);
      }

      props.putAll(connectionParams);
      connection = DriverManager.getConnection(this.conString, props);
    } else {
      LOG.debug("No connection paramenters specified. "
              + "Using regular API for making connection.");
      if (username == null) {
        connection = DriverManager.getConnection(this.conString);
      } else {
        connection = DriverManager.getConnection(
                        this.conString, username, password);
      }
    }

    // We only use this for metadata queries. Loosest semantics are okay.
    connection.setTransactionIsolation(getMetadataIsolationLevel());
    connection.setAutoCommit(false);

    return connection;
  }
 

public void testAutoCommitLB() throws Exception {
    Properties props = new Properties();
    props.setProperty("loadBalanceStrategy", CountingReBalanceStrategy.class.getName());
    props.setProperty("loadBalanceAutoCommitStatementThreshold", "3");

    String portNumber = new NonRegisteringDriver().parseURL(dbUrl, null).getProperty(NonRegisteringDriver.PORT_PROPERTY_KEY);

    if (portNumber == null) {
        portNumber = "3306";
    }

    Connection conn2 = this.getUnreliableLoadBalancedConnection(new String[] { "first", "second" }, props);
    conn2.setAutoCommit(true);
    CountingReBalanceStrategy.resetTimesRebalanced();
    conn2.createStatement().execute("SELECT 1");
    conn2.createStatement().execute("SELECT 2");
    assertEquals(0, CountingReBalanceStrategy.getTimesRebalanced());
    conn2.createStatement().execute("SELECT 3");
    assertEquals(1, CountingReBalanceStrategy.getTimesRebalanced());
    conn2.setAutoCommit(false);
    CountingReBalanceStrategy.resetTimesRebalanced();
    assertEquals(0, CountingReBalanceStrategy.getTimesRebalanced());
    conn2.createStatement().execute("SELECT 1");
    conn2.createStatement().execute("SELECT 2");
    conn2.createStatement().execute("SELECT 3");
    assertEquals(0, CountingReBalanceStrategy.getTimesRebalanced());
    conn2.close();

    props.remove("loadBalanceAutoCommitStatementThreshold");
    conn2 = this.getUnreliableLoadBalancedConnection(new String[] { "first", "second" }, props);
    conn2.setAutoCommit(true);
    CountingReBalanceStrategy.resetTimesRebalanced();
    conn2.createStatement().execute("SELECT 1");
    conn2.createStatement().execute("SELECT 2");
    conn2.createStatement().execute("SELECT 3");
    assertEquals(0, CountingReBalanceStrategy.getTimesRebalanced());
    conn2.setAutoCommit(false);
    CountingReBalanceStrategy.resetTimesRebalanced();
    assertEquals(0, CountingReBalanceStrategy.getTimesRebalanced());
    conn2.createStatement().execute("SELECT 1");
    conn2.createStatement().execute("SELECT 2");
    conn2.createStatement().execute("SELECT 3");
    assertEquals(0, CountingReBalanceStrategy.getTimesRebalanced());
    conn2.close();

    props.setProperty("loadBalanceAutoCommitStatementThreshold", "3");
    props.setProperty("loadBalanceAutoCommitStatementRegex", ".*2.*");
    conn2 = this.getUnreliableLoadBalancedConnection(new String[] { "first", "second" }, props);
    conn2.setAutoCommit(true);
    CountingReBalanceStrategy.resetTimesRebalanced();
    conn2.createStatement().execute("SELECT 1");
    conn2.createStatement().execute("SELECT 2");
    conn2.createStatement().execute("SELECT 3");
    conn2.createStatement().execute("SELECT 2");
    assertEquals(0, CountingReBalanceStrategy.getTimesRebalanced());
    conn2.createStatement().execute("SELECT 2");
    assertEquals(1, CountingReBalanceStrategy.getTimesRebalanced());
    conn2.close();

}
 

protected Pagination<Map<String, Object>> list(Criteria.Fetch criteriaFetch,
		Pagination<Map<String, Object>> pagination, Connection conn) {

	Class clz = criteriaFetch.getClz();

	List<Object> valueList = criteriaFetch.getValueList();

	String[] sqlArr = CriteriaBuilder.parse(criteriaFetch);

	String sqlCount = sqlArr[0];
	String sql = sqlArr[1];

	long count = 0;
	if (!pagination.isScroll()) {
		count = getCount(sqlCount, valueList);
	}
	pagination.setTotalRows(count);

	int page = pagination.getPage();
	int rows = pagination.getRows();
	int start = (page - 1) * rows;

	sql = Mapper.Dialect.Pagination.match(sql, start, rows);

	sql = sql.replace("*", criteriaFetch.getResultScript());

	System.out.println(sql);

	PreparedStatement pstmt = null;
	try {
		conn.setAutoCommit(true);
		pstmt = conn.prepareStatement(sql);

		int i = 1;
		for (Object obj : valueList) {
			pstmt.setObject(i++, obj);
		}

		List<String> resultKeyList = criteriaFetch.getResultList();
		if (resultKeyList.isEmpty()) {
			resultKeyList = criteriaFetch.listAllResultKey();
		}

		ResultSet rs = pstmt.executeQuery();

		if (rs != null) {
			while (rs.next()) {
				Map<String, Object> mapR = new HashMap<String, Object>();
				pagination.getList().add(mapR);

				for (String property : resultKeyList) {
					String mapper = criteriaFetch.getFetchMapper().mapper(property);
					mapR.put(property, rs.getObject(mapper));
				}

			}
		}

	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		close(pstmt);
		close(conn);
	}

	List<Map<String, Object>> stringKeyMapList = pagination.getList();
	if (!stringKeyMapList.isEmpty()) {
		List<Map<String, Object>> jsonableMapList = BeanMapUtil.toJsonableMapList(stringKeyMapList);
		pagination.setList(jsonableMapList);
	}

	return pagination;
}
 

public void testBug56429() throws Exception {
    Properties props = new Driver().parseURL(BaseTestCase.dbUrl, null);
    props.setProperty("autoReconnect", "true");
    props.setProperty("socketFactory", "testsuite.UnreliableSocketFactory");

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

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

    props.remove(NonRegisteringDriver.HOST_PROPERTY_KEY);
    props.remove(NonRegisteringDriver.NUM_HOSTS_PROPERTY_KEY);
    props.remove(NonRegisteringDriver.HOST_PROPERTY_KEY + ".1");
    props.remove(NonRegisteringDriver.PORT_PROPERTY_KEY + ".1");

    props.setProperty("queriesBeforeRetryMaster", "50");
    props.setProperty("maxReconnects", "1");

    UnreliableSocketFactory.mapHost("master", host);
    UnreliableSocketFactory.mapHost("slave", host);

    Connection failoverConnection = null;

    try {
        failoverConnection = getConnectionWithProps("jdbc:mysql://master:" + port + ",slave:" + port + "/", props);

        String userHost = getSingleIndexedValueWithQuery(1, "SELECT USER()").toString();
        String[] userParts = userHost.split("@");

        this.rs = this.stmt.executeQuery("SHOW PROCESSLIST");

        int startConnCount = 0;

        while (this.rs.next()) {
            if (this.rs.getString("User").equals(userParts[0]) && this.rs.getString("Host").equals(userParts[1])) {
                startConnCount++;
            }
        }

        assert (startConnCount > 0);

        failoverConnection.setAutoCommit(false); // this will fail if state
                                                // not copied over

        for (int i = 0; i < 20; i++) {

            failoverConnection.commit();
        }

        this.rs = this.stmt.executeQuery("SHOW PROCESSLIST");

        int endConnCount = 0;

        while (this.rs.next()) {
            if (this.rs.getString("User").equals(userParts[0]) && this.rs.getString("Host").equals(userParts[1])) {
                endConnCount++;
            }
        }

        assert (endConnCount > 0);

        if (endConnCount - startConnCount >= 20) { // this may be bogus if run on a real system, we should probably look to see they're coming from this
                                                      // testsuite?
            fail("We're leaking connections even when not failed over");
        }
    } finally {
        if (failoverConnection != null) {
            failoverConnection.close();
        }
    }
}