JDBC和Oracle conn.commit和conn.setAutocommit无法正常工作 [英] JDBC and Oracle conn.commit and conn.setAutocommit not working properly
问题描述
我已经创建了一个DBManager类，如下所示：
public class DBManager {
public static String DRIVER =oracle.jdbc.driver.OracleDriver;
public static String URL =jdbc：oracle：thin：@ // localhost：1521 / DB;
public static String USERNAME =afsweb;
public static String PASSWORD =afsweb;
public static String DOCDBUSERNAME =docdb;
public static String DOCDBPASSWORD =docdb;
public static int PORT = 1521;
// static Logger log = Logger.getLogger（ExcelDBManager.class.getName（））;
public static Connection getConnection（String url，String username，String password）{
try {
Class.forName（DRIVER）;
} catch（ClassNotFoundException e）{
// TODO自动生成的catch块
e.printStackTrace（）;
}
Connection con = null;
try {
con = DriverManager.getConnection（url，username，password）;
con.setAutoCommit（false）;
} catch（SQLException e）{
// TODO自动生成的catch块
e.printStackTrace（）;
}
return con;
}
我有方法truncate表中的行
public static void truncate（）throws SQLException {
conn = DBManager.getConnection（DBManager.URL，DBManager.USERNAME，DBManager.PASSWORD）;
System.out.println（conn.getAutoCommit（）+）;
语句pstmnt = null;
ResultSet rs = null;
try {
pstmnt = conn.createStatement（）;
pstmnt.executeQuery（truncate table bd_vehicles_temp_1）;
System.out.println（Query Executed）;
}
catch（SQLException e）{
e.printStackTrace（）;
}
finally {
try {
if（rs！= null）{
rs.close（）;
}
if（pstmnt！= null）{
pstmnt.close（）;
}
if（conn！= null）{
conn.close（）;
}
}
catch（SQLException e）
{
e.printStackTrace（）;
}
}
}
.commit里面我的truncate（）方法。我也有setAutocommit为false。
在执行上述方法时，输出为
false
查询执行
这意味着我的连接自动提交模式假。仍然由truncate方法所做的更改反映在数据库中。什么可能是可能的原因？
<
TRUNCATE 是一个隐式提交的数据定义语言（DDL）命令。如果您使用 DELETE 语句，则它不会有任何提示。 //删除所有行;没有WHERE条款
pstmnt.executeQuery（DELETE FROM bd_vehicles_temp_）;
原因 TRUNCATE 是一个DDL语句即直接删除表数据，而不将其复制到回滚表空间 中。这是为什么 TRUNCATE 更快，但无法回滚。
EDIT ：（为什么我的INSERT也提交？）
这是因为您正在关闭 =http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#rollback%28%29 =nofollow> Connection＃rollback（）。
如果连接未显式提交或回滚 JDBC在这里不特别要求任何东西，因此行为取决于数据库供应商。在Oracle的情况下，会发出一个隐含的提交。
强烈建议应用程序在调用close方法之前显式提交或回滚活动事务。如果调用close方法并且有活动事务，则结果是实现定义的。
SO，只是 rollback（）您在最后块中关闭连接之前的更改
pstmnt = conn.createStatement（）;
pstmnt.executeQuery（DELETE FROM bd_vehicles_temp_1）;
System.out.println（Query Executed）;
conn.rollback（）;
System.out.println（更改回退）;
I have made a DBManager class as shown below
public class DBManager {
public static String DRIVER = "oracle.jdbc.driver.OracleDriver";
public static String URL = "jdbc:oracle:thin:@//localhost:1521/DB";
public static String USERNAME = "afsweb";
public static String PASSWORD = "afsweb";
public static String DOCDBUSERNAME = "docdb";
public static String DOCDBPASSWORD = "docdb";
public static int PORT = 1521;
//static Logger log = Logger.getLogger(ExcelDBManager.class.getName());
public static Connection getConnection(String url ,String username, String password){
try {
Class.forName(DRIVER);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Connection con = null;
try {
con = DriverManager.getConnection(url,username,password);
con.setAutoCommit(false);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return con;
}
And i have method truncate rows in table
public static void truncate() throws SQLException{
conn = DBManager.getConnection(DBManager.URL, DBManager.USERNAME, DBManager.PASSWORD);
System.out.println(conn.getAutoCommit() +"");
Statement pstmnt = null;
ResultSet rs = null;
try{
pstmnt = conn.createStatement();
pstmnt.executeQuery("truncate table bd_vehicles_temp_1");
System.out.println("Query Executed");
}
catch(SQLException e){
e.printStackTrace();
}
finally{
try{
if(rs !=null){
rs.close();
}
if(pstmnt != null){
pstmnt.close();
}
if(conn != null){
conn.close();
}
}
catch(SQLException e)
{
e.printStackTrace();
}
}
}
Now i have not written conn.commit inside my truncate() method. Also i have setAutocommit to false. Even then the changes are reflected in database.
On Executing the above method i get output as
false
Query Executed
Which means my connections autocommit mode is false. Still the changes made by truncate method is reflected in database. What could be the possible reason ?? I am using Oracle Database.
Thanks in Advance !
TRUNCATE is a Data Definition Language (DDL) command which commits implicitly. It wouldn't have committed anything, had you used the DELETE statement instead.
// Deletes ALL Rows; No WHERE Clause
pstmnt.executeQuery("DELETE FROM bd_vehicles_temp_");
The reason TRUNCATE is a DDL statement is that it removes the table data directly without copying it into the Rollback Tablespace. That's why TRUNCATE is faster but cannot be rolled back.
EDIT : (Why my INSERTs are committing as well?)
That's because you're closing your Connection without calling Connection#rollback().
If a Connection is closed without an explicit commit or a rollback; JDBC does not mandate anything in particular here and hence the behaviour is dependent on the database vendor. In case of Oracle, an implict commit is issued.
It is strongly recommended that an application explicitly commits or rolls back an active transaction prior to calling the close method. If the close method is called and there is an active transaction, the results are implementation-defined.
SO, just rollback() your changes before closing your Connection in the finally block
pstmnt = conn.createStatement();
pstmnt.executeQuery("DELETE FROM bd_vehicles_temp_1");
System.out.println("Query Executed");
conn.rollback();
System.out.println("Changes rolled back");
这篇关于JDBC和Oracle conn.commit和conn.setAutocommit无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
