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

Question

I'm currently running the follow code segment in a java servlet using the suggested PareparedStatement class. It is currently inserting inventory data into a SQL Server database of an accounting system. I have two problems.

1. Because the insert statements insert data into 3 different tables, should I move these sql statements to tsql procedure call on the database and use Transaction to lock the table?
2. Whenever a user has the accounting software inventory screen opened, the last 2 insert statements fail. I'm guessing the software client locks the table while the user has the screen open.

here is the code:

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

any suggestions? thanks in advance.

Answer 1

Working with JDBC at a low level, each statement is automatically committed when it is executed. To execute multiple statements in a single transaction, invoke setAutoCommit(false) on the Connection, and finish the unit of work with a call to commit() on the connection. If there is a failure, call rollback() instead.

Nowadays, it's more usual to have a persistence mechanism like JPA manage transactions, working with the framework or container. This infrastructure can handle common transactional scenarios quite well.

Answer 2

I would suggest that you not do this in a servlet. Move the code into a persistence tier that you can test off-line, without the servlet container.

I'd also recommend Spring and JPA. Layering your app so the web and persistence tiers aren't one will be a good idea.

http://java.sun.com/developer/technicalArticles/J2EE/jpa/ http://www.springsource.org/