下面列出了java.sql.Connection#getAutoCommit ( ) 实例代码,或者点击链接到github查看源代码,也可以在右侧发表评论。
/**
*
* @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;
}
/**
*
* @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;
}
@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();
}
}
}
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;
}
}
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();
}
}
@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());
}
}
@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);
}
private void configureConnection(Connection conn) throws SQLException {
if (autoCommit != null && autoCommit != conn.getAutoCommit()) {
conn.setAutoCommit(autoCommit);
}
if (defaultTransactionIsolationLevel != null) {
conn.setTransactionIsolation(defaultTransactionIsolationLevel);
}
}
/**
* 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);
}
}
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) {
}
}
/**是否存在事务*/
public boolean hasTransaction() throws SQLException {
Connection conn = this.getConnection();
if (conn == null) {
return false;
}
//AutoCommit被标记为 false 表示开启了事务。
return !conn.getAutoCommit();
}
/**
* 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);
}
}
/**
* 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 commit(Connection connection) throws SQLException {
if (!connection.getAutoCommit()) {
connection.commit();
}
}
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();
}
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);
}
}
}
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);
}
/**
* 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;
}