自动售货系统（SSH框架）

package com.talkweb.dao.impl; import java.math.BigDecimal; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import com.talkweb.dao.GoodsDao; import com.talkweb.model.Page; import com.talkweb.model.TGoodsinfo; import com.talkweb.util.DBUtil; public class GoodsDaoImpl extends BaseDaoImpl implements GoodsDao{ private Connection conn; private ResultSet rs; private PreparedStatement ps; public Page getGoodsPage(int pageId) { Page page=new Page(); if(pageId<1) { pageId=1; } page.setPageId(pageId); conn=DBUtil.getConn(); int num=0; try { ps=conn.prepareStatement("select * from t_goodsinfo", ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY); rs=ps.executeQuery(); rs.last(); num=rs.getRow(); rs.first(); page.setTotalNum(num); page.setPageNum(num/Page.pageSize+(num%Page.pageSize>0?1:0)); page.setPagePre(pageId-1); if(page.getPagePre()<1) { page.setPagePre(1); } page.setPageNext(pageId+1); if(page.getPageNext()>page.getPageNum()) { page.setPageNext(page.getPageNum()); } if(pageId>page.getPageNum()) { page.setPageId(page.getPageNum()); } ps.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } return page; } public List<TGoodsinfo> queryGoodsByPage(Page page) { List<TGoodsinfo> list=new ArrayList<TGoodsinfo>(); conn=DBUtil.getConn(); try { ps=conn.prepareStatement("select * from t_goodsinfo", ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY); rs=ps.executeQuery(); rs.first(); for(int i=0;i<(page.getPageId()-1)*Page.pageSize;i++) { rs.next(); } if(page.getPageNum()>0) { for(int i=0;i<Page.pageSize;i++) { TGoodsinfo goods=new TGoodsinfo(); goods.setCostprice(rs.getDouble("costprice")); goods.setGoodsid(new BigDecimal(rs.getInt("goodsid"))); goods.setGoodsname(rs.getString("goodsname")); goods.setPrice(rs.getDouble("price")); goods.setStock((long)rs.getInt("stock")); list.add(goods); if(!rs.next()) { break; } } } rs.close(); ps.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } return list; } public TGoodsinfo queryGoodsById(int goodsid) { conn=DBUtil.getConn(); TGoodsinfo goods=new TGoodsinfo(); try { ps=conn.prepareStatement("select * from t_goodsinfo where goodsid=?"); ps.setInt(1,goodsid); rs=ps.executeQuery(); while(rs.next()) { goods.setGoodsid(new BigDecimal(rs.getInt("goodsid"))); goods.setCostprice(rs.getDouble("costprice")); goods.setGoodsname(rs.getString("goodsname")); goods.setPrice(rs.getDouble("price")); goods.setStock((long)rs.getInt("stock")); } rs.close(); ps.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } return goods; } public double queryCardBalance(int cardid, String pwd) { double bal=-1; conn=DBUtil.getConn(); try { ps=conn.prepareStatement("select balance from t_cardinfo where cardid=? and pwd=?"); ps.setInt(1,cardid); ps.setString(2, pwd); rs=ps.executeQuery(); while(rs.next()) { bal=rs.getDouble("balance"); } rs.close(); ps.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } return bal; } public boolean buyGoods(int goodsid, int num, int cardid) { boolean flag=false; conn=DBUtil.getConn(); try { conn.setAutoCommit(false); ps=conn.prepareStatement("insert into t_orderinfo values(seq_orderid.nextval,?,sysdate,?,?)"); ps.setInt(1,num); ps.setInt(2,goodsid); ps.setInt(3,cardid); int n=ps.executeUpdate(); ps=conn.prepareStatement("update t_goodsinfo set stock=stock-? where goodsid=?"); ps.setInt(1,num); ps.setInt(2,goodsid); int n1=ps.executeUpdate(); if(n==1&&n1==1) { flag=true; conn.commit(); } else { conn.rollback(); } conn.setAutoCommit(true); ps.close(); conn.close(); } catch (SQLException e) { try { conn.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } e.printStackTrace(); } return flag; } public List<TGoodsinfo> queryGoodsByName(String text) { List<TGoodsinfo> list=new ArrayList<TGoodsinfo>(); conn=DBUtil.getConn(); try { ps=conn.prepareStatement("select * from t_goodsinfo where goodsname like ?"); ps.setString(1, text+"%"); rs=ps.executeQuery(); while(rs.next()) { TGoodsinfo goods=new TGoodsinfo(); goods.setCostprice(rs.getDouble("costprice")); goods.setGoodsid(new BigDecimal(rs.getInt("goodsid"))); goods.setGoodsname(rs.getString("goodsname")); goods.setPrice(rs.getDouble("price")); goods.setStock((long)rs.getInt("stock")); list.add(goods); } rs.close(); ps.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } return list; } public boolean updateGoods(TGoodsinfo goods) { boolean flag=false; conn=DBUtil.getConn(); try { ps=conn.prepareStatement("update t_goodsinfo set goodsname=?,price=?,costprice=?,stock=? where goodsid=?"); ps.setString(1,goods.getGoodsname()); ps.setDouble(2, goods.getPrice()); ps.setDouble(3, goods.getCostprice()); ps.setLong(4, goods.getStock()); ps.setBigDecimal(5, goods.getGoodsid()); int n=ps.executeUpdate(); if(n==1) flag=true; ps.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } return flag; } public boolean deleteGoodsById(int parseInt) { boolean flag=false; conn=DBUtil.getConn(); try { ps=conn.prepareStatement("delete from t_goodsinfo where goodsid=?"); ps.setInt(1, parseInt); int n=ps.executeUpdate(); if(n==1) flag=true; ps.close(); conn.close(); } catch (SQLException e) { // e.printStackTrace(); } return flag; } public boolean insertGoodsInfo(String goodsName, double costPrice, double price, long stock) { boolean flag=false; conn=DBUtil.getConn(); try { ps=conn.prepareStatement("insert into t_goodsinfo values(seq_goodsid.nextval,?,?,?,?)"); ps.setString(1, goodsName); ps.setDouble(2, costPrice); ps.setDouble(3, price); ps.setLong(4, stock ); int n=ps.executeUpdate(); if(n==1) flag=true; ps.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } return flag; } public boolean insertAndUpdateGoodsOrder(String[] goodsid, String[] num, int cardid) { boolean flag=true; conn=DBUtil.getConn(); try { conn.setAutoCommit(false); for(int i=0;i<goodsid.length;i++){ ps=conn.prepareStatement("insert into t_orderinfo values(seq_orderid.nextval,?,sysdate,?,?)"); ps.setInt(1,Integer.parseInt(num[i])); ps.setInt(2,Integer.parseInt(goodsid[i])); ps.setInt(3,cardid); int n=ps.executeUpdate(); ps=conn.prepareStatement("update t_goodsinfo set stock=stock-? where goodsid=?"); ps.setInt(1,Integer.parseInt(num[i])); ps.setInt(2,Integer.parseInt(goodsid[i])); int n1=ps.executeUpdate(); if(n!=1||n1!=1) { flag=false; } } if(flag){ conn.commit(); } else{ conn.rollback(); } conn.setAutoCommit(true); ps.close(); conn.close(); } catch (SQLException e) { try { conn.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } e.printStackTrace(); } return flag; } public void insertGoodsInfo(TGoodsinfo goods) { this.openConnection().save(goods); } }