itemdao.java

电子商城：包括两个部分,一是前台销售程序

package com.publish.shop.order.dao;

import java.sql.*;
import java.math.*;
import java.util.Date;
import java.util.ArrayList;
import com.publish.shop.util.dao.SerialNoDAO;
import com.publish.shop.util.db.DbManager;
import com.publish.shop.util.db.DbPool;
//import com.publish.shop.util.javabeans.Pager;
import com.publish.shop.util.javabeans.Debug;
import com.publish.shop.order.javabeans.ItemInfo;

public class ItemDAO {
  public ItemDAO() {
  }

  public int insert(ItemInfo item) throws Exception{
      String sql = "insert into ItemInfo_table(itemId,productId,quantity,listPrice,unitPrice)"
                   +" values(?,?,?,?,?)";
      Connection con = null;
      PreparedStatement stmt = null;
      int itemId = -1;
      try{
        con = DbPool.getConnection();
        stmt = con.prepareStatement(sql);
        con = DbPool.getConnection();
        stmt = con.prepareStatement(sql);
        itemId = SerialNoDAO.getSerialNo(con,"ItemInfo_table");
        stmt.setString(1, Integer.toString(itemId));
        stmt.setString(2, item.getProductId());
        stmt.setInt(3, item.getQuantity());
        stmt.setBigDecimal(4, item.getListPrice());
        stmt.setBigDecimal(5, item.getUnitPrice());
        stmt.executeUpdate();
        con.commit();
      }catch(Exception e){
        con.rollback();
        throw e;
      }
      finally{
        DbPool.closeStatement(stmt);
        DbPool.closeConnection(con);
      }
      return itemId;
    }

    public void update(ItemInfo item) throws Exception{
      String sql = "update ItemInfo_table set productId='"+item.getProductId()+"',quantity='"+item.getQuantity()+"',listPrice='"+item.getListPrice()+"',unitPrice='"+item.getUnitPrice()+"',status='"+item.getStatus()+"'"
          +" where itemId='"+item.getItemId()+"'";
      Connection con = null;
      Statement stmt = null;
      try{
        con = DbPool.getConnection();
        stmt = con.createStatement();
        stmt.executeUpdate(sql);
        con.commit();
      }catch(Exception e){
        throw e;
      }
      finally{
        DbPool.closeStatement(stmt);
        DbPool.closeConnection(con);
      }
    }

    public void delete(String itemId) throws Exception{
        String sql = "delete from ItemInfo_table where itemId='" + itemId +"'";
        Connection con = null;
        PreparedStatement stmt = null;
        try
        {
          con = DbPool.getConnection();
          stmt = con.prepareStatement(sql);
          stmt.executeUpdate();
          con.commit();
        }
        catch(Exception e)
        {
          throw e;
        }
        finally
        {
          DbPool.closeStatement(stmt);
          DbPool.closeConnection(con);
        }
    }

    public ItemInfo query(String itemId) throws Exception{
      ItemInfo item = null;
      String sql = "select itemId,productId,quantity,listPrice,unitPrice,status from LineItem_table where orderId='" + itemId + "'";
      ResultSet rs = null;
      Connection con = null;
      Statement statement = null;
      try{
        con = DbPool.getConnection();
        statement = con.createStatement();
        rs = statement.executeQuery(sql);
        while(rs.next()){
          item = new ItemInfo();
          item.clear();
          item.setItemId(itemId);
          String temp = rs.getString("productId");
          if (temp != null)
            item.setProductId(temp);
          item.setQuantity(rs.getInt("quantity"));
          item.setListPrice(rs.getBigDecimal("listPrice"));
          item.setUnitPrice(rs.getBigDecimal("unitPrice"));
          temp = rs.getString("orderStatus");
          if (temp != null)
            item.setStatus(temp);
        }
      }catch(Exception e){
          throw e;
      }
      finally{
        DbPool.closeResultSet(rs);
        DbPool.closeStatement(statement);
        DbPool.closeConnection(con);
      }
      return item;
    }
}