今天晚上写了一个Java关于Mysql数据库的事物处理类,实现回滚&&部分回滚功能。
Mysql5很好的支持了事物处理功能。不过支持这个功能的只有两种表类型。
分别是BDB,InnoDB。
先建立一个表,名为Kiss,数据为id (int),name(varchar),pop(varchar)。
下面是源码:
import
java.sql.
*
;
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
public
class
TestCommit
{
![ExpandedSubBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
public static void main(String args[])
{
Connection conn=null;
![ExpandedSubBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
try
{
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/kiss";
conn=DriverManager.getConnection(url,"username","password");
boolean autoCommit=conn.getAutoCommit();
//关闭自动提交功能
conn.setAutoCommit(false);
Statement stmt=conn.createStatement();
stmt.executeUpdate("insert into sun values(15,'Hello','Beijing')");
stmt.executeUpdate("insert into sun values(16,'Hi','shanghai')");
ResultSet rs=stmt.executeQuery("select * from sun");
![ExpandedSubBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
while(rs.next())
{
System.out.print("DeptNo:"+rs.getInt(1));
System.out.print("\tDeptName:"+rs.getString(2));
System.out.println("\tLOC:"+rs.getString(3));
}
//提交事务
conn.commit();
//恢复原来的提交模式
conn.setAutoCommit(autoCommit);
stmt.close();
![ExpandedSubBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
}catch(Exception e)
{
System.out.println("操作失败!!!任务撤销!!!");
![ExpandedSubBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
try
{
//回滚、取消前述操作
conn.rollback();
![ExpandedSubBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
}catch(Exception e1)
{
e1.printStackTrace();
}
![ExpandedSubBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
}finally
{
![ExpandedSubBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
try
{
![ExpandedSubBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
if(conn!=null)
{
conn.close();
}
![ExpandedSubBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
}catch(Exception e1)
{
e1.printStackTrace();
}
}
}
}
执行第一次执行这个类,id如果不冲突,就可以顺利插入数据,第二次插入,id冲突,则实现回滚。
下面是部分回滚:
import
java.sql.
*
;
![](https://i-blog.csdnimg.cn/blog_migrate/f0cd6c7f9e7ae96feae062cb48f670f0.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/34031c708bfe702fe82d01ff5c6593aa.gif)
public
class
TestSavepoint
{
![ExpandedSubBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
public static void main(String args[])
{
Connection conn=null;
![ExpandedSubBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
try
{
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/kiss";
conn=DriverManager.getConnection(url,"username","password");
boolean autoCommit=conn.getAutoCommit();
//关闭自动提交功能
conn.setAutoCommit(false);
Statement stmt=conn.createStatement();
stmt.executeUpdate("insert into sun values(21,'Hello','Beijing')");
stmt.executeUpdate("insert into sun values(22,'Hi','shanghai')");
Savepoint sp1=conn.setSavepoint("p1");
stmt.executeUpdate("insert into sun values(25,'shiyang','xingtai')");
Savepoint sp2=conn.setSavepoint("p2");
stmt.executeUpdate("insert into sun values(60,'shiyang','baoding')");
ResultSet rs=stmt.executeQuery("select avg(id) from sun");
rs.next();
double avg_id=rs.getDouble(1);
![ExpandedSubBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
if(avg_id>100)
{
conn.rollback(sp1);
![ExpandedSubBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
}else if(avg_id>30)
{
conn.rollback(sp2);
}
conn.commit();
rs=stmt.executeQuery("select * from sun");
![ExpandedSubBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
while(rs.next())
{
System.out.print(rs.getInt(1));
System.out.print("\t"+rs.getString(2).trim());
System.out.println("\t"+rs.getString(3));
}
rs.close();
stmt.close();
![ExpandedSubBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
}catch(Exception e)
{
System.out.println("Failure.rollback!!!");
![ExpandedSubBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
try
{
conn.rollback();
![ExpandedSubBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
}catch(Exception e1)
{
e1.printStackTrace();
}
e.printStackTrace();
![ExpandedSubBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
}finally
{
![ExpandedSubBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
try
{
![ExpandedSubBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
if(conn!=null)
{
conn.close();
}
![ExpandedSubBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/3112b7b6526db5bc83e275260ae60525.gif)
}catch(Exception e1)
{
e1.printStackTrace();
}
}
}
}
代码测试通过,完美运行。
施杨出品
!!!