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

@Override
public synchronized void releaseConnection(Connection conn) {
    if (conn == null || !initialized) {
        return;
    }
    /* Try to roll back if necessary */
    try {
        if (!conn.getAutoCommit()) {
            conn.rollback();
        }
    } catch (SQLException e) {
        /* Roll back failed, close and discard connection */
        try {
            conn.close();
        } catch (SQLException e1) {
            /* Nothing needs to be done */
        }
        reservedConnections.remove(conn);
        return;
    }
    reservedConnections.remove(conn);
    availableConnections.add(conn);
}
 

/**
 * 
 * @Title:       executeUpdate   
 * @Description: TODO(带可变参数, 执行sql，返回执行影响的记录条数)   
 * @param:       @param sql 执行的sql语句
 * @param:       @param para 参数
 * @param:       @return
 * @param:       @throws SQLException      
 * @return:      int      
 * @throws
 */
public int executeUpdate(String sql, Object... para) throws SQLException {
	logger.debug("executeUpdate: {}, para: {}", sql, ToStringBuilder.reflectionToString(para));
	QueryRunner runner = new QueryRunner();
	Connection conn = null;
	int count = 0;
	try {
		conn = getConnection();
		count = runner.update(conn, sql, para);
	} catch (SQLException e) {
		logger.error("------executeUpdate error: {}------", e.getMessage());
		throw e;
	} finally {
		if (conn != null && conn.getAutoCommit() == true) {
			freeConnection();
		}
	}
	return count;
}
 

@Override
protected void setupDefaults(final Connection connection, final String userName) throws SQLException {
    final Boolean defaultAutoCommit = isDefaultAutoCommit();
    if (defaultAutoCommit != null && connection.getAutoCommit() != defaultAutoCommit.booleanValue()) {
        connection.setAutoCommit(defaultAutoCommit.booleanValue());
    }

    final int defaultTransactionIsolation = getDefaultTransactionIsolation();
    if (defaultTransactionIsolation != UNKNOWN_TRANSACTIONISOLATION) {
        connection.setTransactionIsolation(defaultTransactionIsolation);
    }

    final Boolean defaultReadOnly = isDefaultReadOnly();
    if (defaultReadOnly != null && connection.isReadOnly() != defaultReadOnly.booleanValue()) {
        connection.setReadOnly(defaultReadOnly.booleanValue());
    }
}
 

public static void loadTestData(Connection conn, String tablename) throws SQLException {
    try (PreparedStatement statement = conn.prepareStatement("insert into " + tablename + " values(?,?)")) {
        statement.setInt(1, 1);
        statement.setString(2, "wangwei");

        statement.executeUpdate();

        statement.setInt(1, 2);
        statement.setString(2, " gaoyan ");

        statement.executeUpdate();

        statement.setInt(1, 3);
        statement.setString(2, "dabao");

        statement.executeUpdate();
    }

    if (!conn.getAutoCommit()) {
        conn.commit();
    }
}
 

/**
 * 
 * @Title:       insertSql   
 * @Description: TODO(带可变参数, 执行sql插入，返回新增记录的自增主键。注意： 若插入的表无自增主键则返回 0，异常的话则返回 null)   
 * @param:       @param sql 执行的sql语句
 * @param:       @param para 参数
 * @param:       @return
 * @param:       @throws SQLException      
 * @return:      Long      
 * @throws
 */
public Long insertSql(String sql, Object... para) throws SQLException {
	logger.debug("InsertSql: {}, para: {}", sql, ToStringBuilder.reflectionToString(para));
	QueryRunner runner = new QueryRunner();
	Connection conn = null;
	Long id = null;
	try {
		conn = getConnection();
		id = (Long) runner.insert(conn, sql, new ScalarHandler<Object>(), para);
	} catch (SQLException e) {
		logger.error("------insertSql error: {}------", e.getMessage());
		throw e;
	} finally {
		if (conn != null && conn.getAutoCommit() == true) {
			freeConnection();
		}
	}
	return id;
}
 

public static boolean commit(Connection connection, boolean checkAutocommit) {
    
    if (connection == null) {
        return false;
    }
    
    try {
        if (!checkAutocommit) connection.commit(); 
        else if (!connection.getAutoCommit()) connection.commit(); 
        return true;
    }
    catch (Exception e) {
        logger.error(e.toString() + System.lineSeparator() + StackTrace.getStringFromStackTrace(e));
        return false;
    }
    
}
 

@Override
public synchronized Connection getConnection() throws SQLException {
    Connection connection = dataSource.getConnection();
    if (!connection.isValid(5)) {
        connection.close();
        if (dataSource instanceof HikariDataSource) {
            ((HikariDataSource) dataSource).close();
        }
        try {
            setupDataSource();
            // get new connection after restarting pool
            connection = dataSource.getConnection();
        } catch (DBInitException e) {
            throw new DBOpException("Failed to restart DataSource after a connection was invalid: " + e.getMessage(), e);
        }
    }
    if (connection.getAutoCommit()) connection.setAutoCommit(false);
    return connection;
}
 

/**
 * 
 * @Title:       executeBatch   
 * @Description: TODO(批量更新)   
 * @param:       @param sql 执行的sql语句
 * @param:       @param params 二维数组参数
 * @param:       @throws SQLException      
 * @return:      void      
 * @throws
 */
public void executeBatch(String sql, Object[][] params) throws SQLException {
	logger.debug("executeBatch: {}, params:{}", sql, ToStringBuilder.reflectionToString(params));
	QueryRunner runner = new QueryRunner();
	Connection conn = null;
	try {
		conn = getConnection();
		runner.batch(conn, sql, params);
	} catch (SQLException e) {
		logger.error("------executeBatch Error:{}------", e.getMessage());
		throw e;
	} finally {
		if (conn != null && conn.getAutoCommit() == true) {
			freeConnection();
		}
	}
}
 

/**
 * Initializes the sql query environment from the SqlResources file. Will
 * look for conf/sqlResources.xml.
 * 
 * @param conn
 *            The connection for accessing the database
 * @param sqlConfiguration
 *            The sqlResources configuration document
 * @throws Exception
 *             If any error occurs
 */
public void initSqlQueries(Connection conn, Document sqlConfiguration) throws Exception {
    try {
        if (conn.getAutoCommit()) {
            conn.setAutoCommit(false);
        }

        sqlQueries.init(sqlConfiguration, JDBCBayesianAnalyzer.class.getName(), conn, getSqlParameters());

        checkTables(conn);
    } finally {
        theJDBCUtil.closeJDBCConnection(conn);
    }
}
 

private void configureConnection(Connection conn) throws SQLException {
  if (autoCommit != null && autoCommit != conn.getAutoCommit()) {
    conn.setAutoCommit(autoCommit);
  }
  if (defaultTransactionIsolationLevel != null) {
    conn.setTransactionIsolation(defaultTransactionIsolationLevel);
  }
}
 

/**
 * Run some database queries within a transaction with a helpful message if something goes wrong.
 */
public <E> E transaction(String actionDescription, DBAction<E> action) throws RuntimeException {
    try {
        Connection db = borrowConnection();
        DBConnection dbc = wrapConnection(db);
        boolean autocommit = db.getAutoCommit();

        try {
            db.setAutoCommit(false);

            return action.call(dbc);
        } finally {

            if (!dbc.wasResolved()) {
                log.warn("**************\nDB Transaction was neither committed nor rolled back.  Committing for you.");
                dbc.commit();
            }

            if (autocommit) {
                db.setAutoCommit(true);
            }
            db.close();
        }

    } catch (SQLException e) {
        throw new RuntimeException("Failure in database action: " + actionDescription, e);
    }
}
 

/**是否存在事务*/
public boolean hasTransaction() throws SQLException {
    Connection conn = this.getConnection();
    if (conn == null) {
        return false;
    }
    //AutoCommit被标记为 false 表示开启了事务。
    return !conn.getAutoCommit();
}
 

public TransactionProxy(String dataSourceId, Connection connection, DataSource dataSource) {
    this.connection = connection;
    this.dataSource = dataSource;
    this.dataSourceId = dataSourceId;
    this.isConnectionTransactional = DataSourceUtils.isConnectionTransactional(connection, dataSource);
    try {
        this.autoCommit = connection.getAutoCommit();
    } catch (SQLException e) {
    }
}
 

/**
 * Closes a {@link Connection} only if the connection is in auto commit mode
 * and logs exceptions without throwing. Does nothing if connection is null.
 * 
 * @param connection
 */
static boolean closeQuietlyIfAutoCommit(Connection connection) {
    try {
        if (connection != null && !connection.isClosed() && connection.getAutoCommit()) {
            closeQuietly(connection);
            return true;
        } else
            return false;
    } catch (SQLException e) {
        throw new SQLRuntimeException(e);
    }
}
 

private void readUnittypeParameters(Unittypes unittypes) throws SQLException {
  Statement s = null;
  ResultSet rs = null;
  String sql;
  boolean wasAutoCommit = false;
  Connection connection = null;
  try {
    Map<Integer, UnittypeParameter> idMap = null;
    Map<String, UnittypeParameter> nameMap = null;
    Unittype lastUnittype = null;
    sql =
        "SELECT unit_type_id, unit_type_param_id, name, flags FROM unit_type_param ORDER BY unit_type_id ASC";
    connection = getDataSource().getConnection();
    wasAutoCommit = connection.getAutoCommit();
    connection.setAutoCommit(false);
    s = connection.createStatement();
    s.setQueryTimeout(60);
    rs = s.executeQuery(sql);
    int counter = 0;
    while (rs.next()) {
      counter++;
      Unittype unittype = unittypes.getById(rs.getInt("unit_type_id"));
      Integer unittypeParamId = rs.getInt("unit_type_param_id");
      String name = rs.getString("name");
      UnittypeParameterFlag unittypeParameterFlag =
          new UnittypeParameterFlag(rs.getString("flags"), true);
      UnittypeParameter unittypeParameter =
          new UnittypeParameter(unittype, name, unittypeParameterFlag);
      unittypeParameter.setId(unittypeParamId);
      if (lastUnittype == null || lastUnittype != unittype) {
        idMap = new HashMap<>();
        nameMap = new MapWrapper<UnittypeParameter>(isStrictOrder()).getMap();
        unittype.setUnittypeParameters(new UnittypeParameters(idMap, nameMap, unittype));
        lastUnittype = unittype;
      }
      nameMap.put(name, unittypeParameter);
      idMap.put(unittypeParamId, unittypeParameter);
      unittype.getUnittypeParameters().updateInternalMaps(unittypeParameter);
    }
    if (logger.isDebugEnabled()) {
      logger.debug("Read " + counter + " unittype params");
    }
  } finally {
    if (rs != null) {
      rs.close();
    }
    if (s != null) {
      s.close();
    }
    if (connection != null) {
      connection.setAutoCommit(wasAutoCommit);
      connection.close();
    }
  }
}
 

/**
	 * Run the test, using the resource as the input.
	 * Compare to the master file using a very simple
	 * line by line comparision. Fails at the first
	 * difference. If a failure occurs the output
	 * is written into the current directory as
	 * testScript.out, otherwise the output is only
	 * kept in memory.
	 * @throws Throwable 
	 */
	public void runTest() throws Throwable
	{
		String resource =
			"com/splicemachine/dbTesting/functionTests/tests/"
			+ getArea() + "/"
			+ getName() + ".sql";
		
		String canon =
			"com/splicemachine/dbTesting/functionTests/master/"
			+ getName() + ".out";

		URL sql = getTestResource(resource);
		assertNotNull("SQL script missing: " + resource, sql);
		
		InputStream sqlIn = openTestResource(sql);

		Connection conn;

		if (user != null) {
			conn = openUserConnection(user);
		} else {
			conn = getConnection();
		}

        final String outputEnc;
        final String derby_ui_codeset = getSystemProperty("derby.ui.codeset");

        if (derby_ui_codeset != null) {
            // IJ should format output according to the db.ui.codeset
            // variable. If we pass in an encoding explicitly to runScript(),
            // we won't test that db.ui.codeset is obeyed. Therefore,
            // leave it as null.
            outputEnc = null;
            assertEquals(
                    "Requested output encoding and db.ui.codeset differ",
                    outputEncoding, derby_ui_codeset);
        } else {
            // db.ui.codeset isn't set. Tell runScript() which output
            // encoding to use.
            outputEnc = outputEncoding;
        }
        
//		com.splicemachine.db.tools.ij.runScript(
//				conn,
//				sqlIn,
//				inputEncoding,
//                getOutputStream(),
//				outputEnc,
//                useSystemProperties);
		
		if (!conn.isClosed() && !conn.getAutoCommit())
		    conn.commit();
		
		sqlIn.close();
        
        this.compareCanon(canon);
	}
 

public void moveToFolders(Connection conn, String thread_name) {
	// Changing the folder id of randomly selected rows
	try {
		saveAutoCommit = conn.getAutoCommit();
		conn.setAutoCommit(false);
		Statement stmt = conn.createStatement();
		PreparedStatement moveToFolder = conn
				.prepareStatement(Statements.movefolder);
		ResultSet rs = stmt.executeQuery(Statements.getRowCount);
		if (!(rs.next()))
			MailJdbc.logAct.logMsg(LogFile.INFO + thread_name + " : "
					+ "no message in the REFRESH.INBOX to move");
		else {
			int message_id = 0;
			int count = rs.getInt(1);
			int folder_id = Rn.nextInt(5 - 1);
			if (count == 0)
				message_id = 0;
			else
			    message_id = Rn.nextInt(count - 1);
			moveToFolder.setInt(1, folder_id);
			moveToFolder.setInt(2, message_id);
			long s_folder = System.currentTimeMillis();
			moveToFolder.executeUpdate();
			long e_folder = System.currentTimeMillis();
			log.logMsg(LogFile.INFO + thread_name + " : "
					+ "Time taken to move a mail to the folder :"
					+ PerfTime.readableTime(e_folder - s_folder));
			MailJdbc.logAct.logMsg(LogFile.INFO + thread_name + " : "
					+ "Mail with id : " + message_id
					+ " is moved to folder with id : " + folder_id);
		}
		stmt.close();
		moveToFolder.close();
		rs.close();
		conn.commit();
		conn.setAutoCommit(saveAutoCommit);
	} catch (SQLException sqe) {
		MailJdbc.logAct.logMsg(LogFile.ERROR + thread_name + " : "
				+ "Exception while moving mail to folders: "
				+ sqe.getMessage());
		sqe.printStackTrace();
		errorPrint(sqe);
		try {
			conn.rollback();
		} catch (SQLException sq) {
			MailJdbc.logAct.logMsg(LogFile.ERROR + thread_name + " : "
					+ "Exception while rolling back: " + sq);
			sq.printStackTrace();
			errorPrint(sq);
		}
	}

}
 

/**
 * Method to add a new certificate to the database.
 *
 * @param alias    : Alias for the new certificate.
 * @param endpoint : The endpoint/ server url which the certificate will be mapped to.
 * @param tenantId : The Id of the tenant who uploaded the certificate.
 * @return : True if the information is added successfully, false otherwise.
 * @throws CertificateManagementException if existing entry is found for the given endpoint or alias.
 */
public boolean addCertificate(String alias, String endpoint, int tenantId) throws CertificateManagementException,
        CertificateAliasExistsException {

    boolean result = false;
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    String addCertQuery = SQLConstants.CertificateConstants.INSERT_CERTIFICATE;

    //Check whether any certificate is uploaded for the same alias or endpoint by another user/ tenant.
    CertificateMetadataDTO existingCertificate = getCertificate(alias);

    if (existingCertificate != null) {
        if (log.isDebugEnabled()) {
            log.debug("A certificate for the endpoint " + endpoint + " has already added with alias " +
                    existingCertificate.getAlias());
        }
        String message = "Alias or Endpoint exists in the database!";
        if (existingCertificate.getAlias().equals(alias)) {
            throw new CertificateAliasExistsException(message);
        }
    }

    try {
        connection = APIMgtDBUtil.getConnection();
        initialAutoCommit = connection.getAutoCommit();
        connection.setAutoCommit(false);
        preparedStatement = connection.prepareStatement(addCertQuery);
        preparedStatement.setInt(1, tenantId);
        preparedStatement.setString(2, endpoint);
        preparedStatement.setString(3, alias);
        result = preparedStatement.executeUpdate() == 1;
        connection.commit();
    } catch (SQLException e) {
        handleConnectionRollBack(connection);
        if (log.isDebugEnabled()) {
            log.debug("Error occurred while adding certificate metadata to database.", e);
        }
        handleException("Error while persisting certificate metadata.", e);
    } finally {
        APIMgtDBUtil.setAutoCommit(connection, initialAutoCommit);
        APIMgtDBUtil.closeAllConnections(preparedStatement, connection, null);
    }
    return result;
}
 

public void testSqlProcedures() throws SQLException {
    Connection conn = getConnection();
    Statement s = createStatement();

    s.execute("create table t1(i int not null primary key, b char(15))");
    s
            .execute("create procedure ir(p1 int) MODIFIES SQL DATA dynamic result sets 0 language java external name 'org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.insertRow' parameter style java");
    s
            .execute("create procedure ir2(p1 int, p2 char(10)) language java external name 'org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.insertRow' MODIFIES SQL DATA parameter style java");

    String[] sysaliasDefinition = { "APP.IR AS org.apache.derbyTesting.functionTeststs.tests.lang.LangProcedureTest.insertRow(IN P1 INTEGER) LANGUAGE JAVA PARAMETER STYLE JAVA MODIFIES SQL DATA" };
    String[] dbMetadataDefinition = { "APP.IR AS org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.insertRow type procedureNoResult" };
    String[] columnDefinition = { "procedureColumnIn P1 INTEGER" };
    checkMatchingProcedures(conn, "IR1", sysaliasDefinition,
            dbMetadataDefinition, columnDefinition);

    sysaliasDefinition = new String[] { "APP.IR2 AS org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.insertRow(IN P1 INTEGER,IN P2 CHAR(10)) LANGUAGE JAVA PARAMETER STYLE JAVA MODIFIES SQL DATA" };
    dbMetadataDefinition = new String[] { "APP.IR2 AS org.apache.derbyTesting.functionTests.tests.lang.LangProcedureTest.insertRow type procedureNoResult" };
    columnDefinition = new String[] { "procedureColumnIn P1 INTEGER",
            "procedureColumnIn P2 CHAR" };
    checkMatchingProcedures(conn, "IR2", sysaliasDefinition,
            dbMetadataDefinition, columnDefinition);
    assertCallError("42Y03", "CALL IR()");

    CallableStatement ir1 = prepareCall("CALL IR(?)");

    ir1.setInt(1, 1);
    ir1.execute();

    ir1.setInt(1, 2);
    ir1.execute();
    try {
        ir1.execute();
        fail("FAIL - duplicate key insertion through ir");
    } catch (SQLException sqle) {
        assertSQLState("23505", sqle);
    }

    ir1.setString(1, "3");
    ir1.execute();

    ir1.close();

    ir1 = conn.prepareCall("CALL APP.IR(?)");
    ir1.setInt(1, 7);
    ir1.execute();

    CallableStatement ir2 = conn.prepareCall("CALL IR2(?, ?)");

    ir2.setInt(1, 4);
    ir2.setInt(2, 4);
    ir2.execute();

    ir2.setInt(1, 5);
    ir2.setString(2, "ir2");
    ir2.execute();

    ir2.setInt(1, 6);
    ir2.setString(2, "'012345678990'");
    ir2.execute();

    ir1.close();
    ir2.close();

    if (!conn.getAutoCommit())
        conn.commit();

    String[][] t1Results = { { "1", "int" }, { "2", "int" },
            { "3", "int" }, { "7", "int" }, { "4", "4" }, { "5", "ir2" },
            { "6", "'012345678" } };
    ResultSet rs = s.executeQuery("select * from t1");
    JDBC.assertFullResultSet(rs, t1Results);

    if (!conn.getAutoCommit())
        conn.commit();

    assertCallError("38000", "CALL IR2(2, 'no way')");
    assertCallError("07000", "CALL IR2(?, 'no way')");
    assertCallError("07000", "CALL IR2(2, ?)");

    s.execute("drop procedure IR");
    s.execute("drop procedure IR2");

    s.close();
}
 

private void commit(Connection connection) throws SQLException {
    if (!connection.getAutoCommit()) {
        connection.commit();
    }
}