java servlet : better way to do multiple inserts and locking tables?

Question

我当前正在使用建议的 PareparedStatement 类在 java servlet 中运行以下代码段。目前，它正在将库存数据插入到会计系统的 SQL Server 数据库中。我有两个问题。

1. 因为insert语句将数据插入到3个不同的表中，我是否应该将这些sql语句移动到数据库上的tsql过程调用并使用Transaction来锁定表？
2. 每当用户打开会计软件库存屏幕时，最后 2 个插入语句都会失败。我猜测当用户打开屏幕时软件客户端会锁定表格。

这是代码:

try {   

        con = java.sql.DriverManager.getConnection(getConnectionUrl()); 
        //get next itemkey 
        CallableStatement cstmt = con.prepareCall("{call spGetNextSurrogateKey (?,?)}");
        cstmt.setString("iTableName","timitem");
        cstmt.registerOutParameter("oNewKey", java.sql.Types.INTEGER);
        cstmt.execute(); 
        int rs4 = cstmt.getInt(2);
        cstmt.close();

        String query = "insert into timitem (itemkey, AllowCostOvrd,AllowDecimalQty,AllowDropShip,AllowPriceOvrd,AllowRtrns,AvailForSale,CompanyID,CreateDate,CreateUserID,CreateType,DateEstab,DfltSaleQty,HazMat,InclOnPackList,InternalLongDesc,IntrnlDeliveryReq,ItemClassKey,ItemID,ItemSubType,ItemType,MinGrossProfitPct,MinSaleQty,PerUsageOrdLimit,PriceSeq,PriceUnitMeaskey,PurchProdLineKey,PurchUnitMeasKey,RcptReq,RestockRate,SaleMultiple,SalesUnitMeasKey,Seasonal,ShelfLife,Status,STaxClasskey,StdPrice,StdUnitCost,StockUnitMeasKey,SubjToTradeDisc,TargetMargin,TrackMeth,UpdateCounter,ValuationMeth,WarrantyDays,WarrantyProvider) values ( '" +rs4 + "', 0,0,1,1,1,1,'ens','" + DateFormat.format(Date) + "','admin',1,'" + DateFormat.format(Date) + "',1,0,1,0,0,"+itemclasskey+",'" + partnumber + "',1,5,0,1,0,0,112,"+PurchProdLineKey+","+UnitMeasKey+",1,0,0,112,0,0,1,12,"+ itemlistprice + ","+itemcost + ",112,0,0,2,0,5,0,0)";
        PreparedStatement pstmt = con.prepareStatement(query); 
        pstmt.executeUpdate(); 
        pstmt.close();            

                String query_descrip = "insert into timitemdescription (itemkey, languageid, longdesc, shortdesc) values ('" + rs4 + "', 1033, '" + itemdescription + "','"+ "_" + "')";

                PreparedStatement pstmt2 = con.prepareStatement(query_descrip); 
        pstmt2.executeUpdate();                         
                pstmt2.close();

                String query_UOM = "insert into timItemUnitOfMeas (itemkey, TargetUnitMeasKey, conversionfactor, unitvolume, unitweight,upc,useforpurchases,useforsales,usestdconv) values ('" + rs4 + "', "+UnitMeasKey+", '1',0,0,NULL,0,0,0)";

                PreparedStatement pstmt3 = con.prepareStatement(query_UOM); 
        pstmt3.executeUpdate();                         
                pstmt3.close();


}catch(java.sql.SQLException e){ e.printStackTrace(); }     //end try 

有什么建议吗？提前致谢。

Answer 1

在低级别使用 JDBC，每个语句在执行时都会自动提交。要在单个事务中执行多个语句，请调用 setAutoCommit(false)关于Connection ，并通过调用 commit() 来完成工作单元在连接上。如果出现故障，请调用rollback()相反。

现在，更常见的是使用像 JPA 这样的持久性机制来管理事务，与框架或容器一起工作。该基础设施可以很好地处理常见的事务场景。