求一将数据同时插入多个表的事务处理例程?

Question

小弟学习中,但这方面的例程缺乏,哪位能提供一下.
或着按这个帮写一个:数据有A,B,C将A插入表D,将B插入表E,将C插入表F,三个操作要求用事务提交,若有一个失败则重来.
发到邮箱也可:[email protected]

Answer 1

JDBC 2.0有批处理的功能,用它可以满足你的需求,下边是例子,修改就可以实现你的要求,当然你要自己设置自动提交为FALSE,然后你CONN.COMMIT();
public void insertBatchData() throws SQLException,IOException{
        BufferedReader br = new BufferedReader(new FileReader("data.txt"));  //data.txt中包含了测试用数据.
        statement = connection.createStatement();
        try{
                do{
                        title = br.readLine();
                        if(title == null) break;
                        leadActor = br.readLine();
                        leadActress = br.readLine();
                        type = br.readLine();
                        dateOfRelease = br.readLine();
 
                        String sqlString = "INSERT INTO CATALOG VALUES('" + title + ",'" +
                                                leadActor + "','" + leadActress + "','" +
                                                type + "','" + dateOfRelease + "')";
                        statement.addBatch(sqlString);
                } while(br.readLine() != null);
                statement.executeBatch();
        }catch (EOFException e){
        }finally{
                statement.close();
                br.close();
        }
}

Answer 2

我这样写了一个，可以同时插入，但不知道起到了事务的效果没有
------------------------------------------------------------
<%@ page contentType="text/html; charset=gb2312" language="java"import="java.sql.*,java.io.*"%> 
<%@ page import="DBcon.dbconn"session="true"%>
<jsp:useBean id="DBcon"scope="page"class="DBcon.dbconn"/>
<% request.setCharacterEncoding("GBK"); %> 
<%   Connection conn = null;
  conn = DBcon.getConn(); 
    
  String id=(String)session.getAttribute("id_a");
  String title=(String)session.getAttribute("title_a");
  String price=(String)session.getAttribute("price_a");
  String name=(String)session.getAttribute("name_a");
  
  Statement stmt;
  if(id==""||title==""||price==""||name=="")
  {
   out.println("请输入数据");
  }
  else{
       String sql1="INSERT INTO book (id,title,price) VALUES ('"+id+"','"+title+"','"+price+"')";
       String sql2="INSERT INTO book1 (name) VALUES ('"+name+"')";
try{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
}catch(java.lang.ClassNotFoundException e){
System.err.print("ClassNotFoundException:");
System.err.println(e.getMessage());
}
                try{
                  conn.setAutoCommit(false);
                  stmt=conn.createStatement();
                  stmt.executeUpdate(sql1);
                  stmt.executeUpdate(sql2);
                  conn.commit();
                }
                catch(Exception e){
                  e.printStackTrace();
             }   
}
%> 

Answer 3

用触发器..调用过程做..如果这个表操作,,commit数据..否则回滚数据

Answer 4

建议用存储过程。存储过程其实蛮好写。如果实在不会，就用jdbc的事务处理，贴一个批量运行sql的代码:
其中：参数sqlStrs为String数组，内容为你要执行的sql语句/**
    * Excute sql
    */
public boolean doStatement(String[] sqlStrs)
{
boolean flag = false;
if (sqlStrs.length < 1)
return false;
try
{
InitialContext initial = new InitialContext();
ds = (DataSource) initial.lookup(dbjndi);
con = ds.getConnection();
con.setAutoCommit(false);
stmt = con.createStatement();//System.out.println("sqlStrs.length:" + sqlStrs.length);int count = sqlStrs.length / 7000 + 1;
System.out.println("count:" + count);
int end = 0;
for (int ii = 0; ii < count; ii++)
{
stmt.clearBatch();
end = (ii + 1) * 7000;
if (end > sqlStrs.length)
end = sqlStrs.length;
for (int i = ii * 7000; i < end; i++)
{
//modify by lcl 
if (sqlStrs[i] != null && sqlStrs[i].trim().length() > 0)
stmt.addBatch(sqlStrs[i]);
System.out.println("in DbOperate.doStatement sql : " + sqlStrs[i]);
}
stmt.executeBatch();
//System.out.println("excute back:" + ii + "Strins begin:" + ii * 7000 + " end :" + end);
}con.commit();
System.out.println("excute sql finished!");
flag = true;
}
catch (NamingException ee)
{
//System.out.println("DbOperate.java : find data source error, " + ee.toString());
stmtStr = "find data source error";
flag = false;
}
catch (Exception ex)
{
stmtStr = StringTool.removeNewline(ex.toString());
ex.printStackTrace();
if (ex.toString().indexOf("ORA") > 0)
stmtStr = StringTool.removeNewline(ex.toString().substring(ex.toString().indexOf("ORA") + 10));
//System.out.println("err String :" + stmtStr);
flag = false;try
{
con.rollback();
}
catch (Exception ee)
{
ee.printStackTrace();
stmtStr = StringTool.removeNewline(ee.toString());
}
flag = false;
}
finally
{try
{
con.setAutoCommit(true);
}
catch (SQLException e)
{
// TODO ???? catch ?
e.printStackTrace();
}
if (stmt != null)
{
try
{
stmt.close();
}
catch (SQLException e1)
{
// TODO ???? catch ?
e1.printStackTrace();
}
}
if (con != null)
{
try
{
con.close();
}
catch (SQLException e1)
{
// TODO ???? catch ?
e1.printStackTrace();
}
}}
return flag;
}

Answer 5

try{
                  conn.setAutoCommit(false);
                  stmt=conn.createStatement();
                  stmt.executeUpdate(sql1);
                  stmt.executeUpdate(sql2);
                  stmt.executeUpdate(sql3);
                  conn.commit();
                }
                catch(Exception e){
                  conn.rollback();
                  e.printStackTrace();
             }   finally{
                 try{
                   conn.setAutoCommit(true);
                   if(stmt != null){
                      stmt.close();
                   }
                   if(conn != null){
                         conn.close();
                   }
                   }catch(SQLException e){
                       e.printStackTrance();
                   }
                }