要把数据插入到多张表?
我有个问题:我要把一条数据插入3张表中,如何控制插入出错时全部回滚,我现在的代码是这样的:
PreparedStatement pstmt = con.prepareStatement(
"insert into Xz_jbxxb(xz_id,xx_id,xk_id,xz_cxrq,dc_id,xz_cxc,xz_zt) values (?,?,?,?,?,?,?) ");
pstmt.setString(1, (String) listxh.get(i));
pstmt.setString(2, (String) listzyxxx.get(1));
pstmt.setString(3, (String) listzyxxx.get(2));
pstmt.setString(4, (String) listzyxxx.get(3));
pstmt.setString(5, (String) listzyxxx.get(4));
pstmt.setString(6, (String) listzyxxx.get(5));
pstmt.setString(7, "ZYX ");
pstmt.execute();
pstmt.clearParameters();
pstmt = con.prepareStatement(
"insert into Xz_zyx(xz_id,xz_zt,xz_cbj,xz_dcf,xz_qtfy,xz_zjj,xz_czjg,dt_id) values (?,?,?,?,?,?,?,?) ");
pstmt.setString(1, (String) listxh.get(i));
pstmt.setString(2, "ZYX ");
pstmt.setFloat(3, Float.parseFloat((String)listzyxxx.get(6)));
pstmt.setFloat(4, Float.parseFloat((String) listzyxxx.get(8)));
pstmt.setFloat(5, Float.parseFloat((String) listzyxxx.get(9)));
pstmt.setFloat(6, Float.parseFloat((String) listzyxxx.get(7)));
pstmt.setFloat(7, Float.parseFloat((String) listzyxxx.get(10)));
pstmt.setString(8, "dt002 "); //动态
pstmt.execute();
pstmt.clearParameters();
pstmt = con.prepareStatement(
"insert into dcxzxx(xz_id,dc_id) values (?,?) ");
pstmt.setString(1, (String) listxh.get(i));
pstmt.setString(2, (String) listzyxxx.get(4));
pstmt.execute();
比如要执行到第2个SQL语句时出错,但第一个SQL语句已经执行,数据已经插进去了.
[解决办法]
用事务。
[解决办法]
用sql中的事务。
[解决办法]
贴个例子给lz，一看就明白：
***************************************
public class OrderHandler extends HttpServlet {
public void doPost(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
res.setContentType( "text/plain ");
PrintWriter out = res.getWriter();
Connection con = null;
try {
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver ");
con = DriverManager.getConnection( "jdbc:odbc:ordersdb ", "user ", "passwd ");
// Turn on transactions
con.setAutoCommit(false);
Statement stmt = con.createStatement();
stmt.executeUpdate(
"UPDATE INVENTORY SET STOCK = (STOCK - 10) WHERE PRODUCTID = 7 ");
stmt.executeUpdate(
"UPDATE SHIPPING SET SHIPPED = (SHIPPED + 10) WHERE PRODUCTID = 7 ");
chargeCard(); // method doesn 't actually exist...
con.commit();
out.println( "Order successful! Thanks for your business! ");
}
catch (Exception e) {
// Any error is grounds for rollback
try {
con.rollback();
}
catch (SQLException ignored) { }
out.println( "Order failed. Please contact technical support. ");
}
finally {
// Clean up.
try {
if (con != null) con.close();
}
catch (SQLException ignored) { }
}
}
}
[解决办法]
事务控制
con.setAutocommit(false)
try
{
...
con.commit();
}
catch(Exception e)
{
con.rollback();
}
finally
{
con.close();
}
