orderdao.java

一个常用的电子商城的站点源码

package com.publish.shop.order.dao;

import java.sql.*;
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.util.javabeans.DateTimeUtility;
import com.publish.shop.util.javabeans.DateUtility;
import com.publish.shop.order.javabeans.OrderModel;
import com.publish.shop.security.javabeans.UserProxy;
import com.publish.shop.security.javabeans.UserModel;
import org.apache.struts.util.LabelValueBean;

public class OrderDAO {
  public OrderDAO() {
  }

  public int insert(OrderModel model) throws Exception{
      String sql = "insert into UserOrder_table(orderId,userId,orderTime,orderRecName,orderRecMail,orderRecAddress,orderRecZip,orderTotalPrice)"
                   +" values(?,?,?,?,?,?,?,?)";
      Connection con = null;
      int orderId = -1;
      PreparedStatement stmt = null;
      try{
        con = DbPool.getConnection();
        stmt = con.prepareStatement(sql);
        orderId = SerialNoDAO.getSerialNo(con,"UserOrder_table");
        stmt.setString(1, Integer.toString(orderId));
        stmt.setString(2, model.getUserId());
        String orderTime = DateTimeUtility.getCurTimeStamp();
        stmt.setString(3, orderTime);
        stmt.setString(4, model.getOrderRecName());
        stmt.setString(5, model.getOrderRecMail());
        stmt.setString(6, model.getOrderRecAddress());
        stmt.setString(7, model.getOrderRecZip());
        String orderTotalPrice = model.getOrderTotalPrice();
        if (orderTotalPrice==null)
          orderTotalPrice = "0";
        stmt.setString(8, orderTotalPrice);
        System.out.println("getCurTimeStamp: "+DateTimeUtility.getCurTimeStamp()+"-");
        stmt.executeUpdate();
        con.commit();
      }catch(Exception e){
        con.rollback();
        throw e;
      }
      finally{
        DbPool.closeStatement(stmt);
        DbPool.closeConnection(con);
      }
      return orderId;
    }

    public void update(OrderModel model) throws Exception{
      String sql = "update UserOrder_table set userId='"+model.getUserId()+"',orderTime='"+model.getOrderTime()+"',orderStatus='"+model.getOrderStatus()+"',orderPassTime='"+model.getOrderPassTime()
          +"',orderPassId='"+model.getOrderPassId()+"',orderSendState='"+model.getOrderSendState()+"',orderRecName='"+model.getOrderRecName()+"',orderRecAddress='"+model.getOrderRecAddress()+"',orderRecZip='"+model.getOrderRecZip()
          +"',orderTotalPrice='"+model.getOrderTotalPrice()+"'"
          +" where orderId='"+model.getOrderId()+"'";
      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 confirm(String orderId) throws Exception{
    String sql = "update UserOrder_table set orderStatus='Y' where orderId='"+orderId+"'";
    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 send(String orderId) throws Exception{
      String sql = "update UserOrder_table set orderStatus='Y' and orderSendState='Y' where orderId='"+orderId+"'";
      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 orderId) throws Exception{
        String sql01 = "delete from LineItem_table where orderId='" + orderId + "'";
        String sql = "delete from UserOrder_table where orderId='" + orderId +"'";
        Connection con = null;
        PreparedStatement stmt01 = null;
        PreparedStatement stmt = null;
        try
        {
          con = DbPool.getConnection();
          stmt01 = con.prepareStatement(sql01);
          stmt01.executeUpdate(sql01);
          stmt = con.prepareStatement(sql);
          stmt.executeUpdate();
          con.commit();
        }
        catch(Exception e)
        {
          throw e;
        }
        finally
        {
          DbPool.closeStatement(stmt);
          DbPool.closeConnection(con);
        }
    }

    public ArrayList query(OrderModel model) throws Exception{
        ArrayList list = new ArrayList();
        String sql = "select orderId,userId,orderTime,orderStatus,orderPassTime,orderPassId,orderSendState,orderRecName,orderRecMail,orderRecAddress,orderRecZip,orderTotalPrice from UserOrder_table";
        String sql01 = "";
        if(model.getOrderId()!=null && !model.getOrderId().equals("")){
          sql01 += " orderId like '%"+model.getOrderId()+"%'";
        }
        if(model.getUserId()!=null && !model.getUserId().equals("")){
          if (sql01.equals(""))
              sql01 = " userId like '%"+model.getUserId()+"%'";
          else
              sql01 += " and userId like '%"+model.getUserId()+"%'";
        }
        if(model.getOrderTime()!=null && !model.getOrderTime().equals("")){
          if (sql01.equals(""))
              sql01 = " orderTime like '%"+model.getOrderTime()+"%'";
          else
              sql01 += " and orderTime like '%"+model.getOrderTime()+"%'";
        }
        if(model.getOrderStatus()!=null && !model.getOrderStatus().equals("")){
          if (sql01.equals(""))
              sql01 = "orderStatus like '%"+model.getOrderStatus()+"%'";
          else
              sql01 += " and orderStatus like '%"+model.getOrderStatus()+"%'";
        }
        if(model.getOrderPassTime()!=null && !model.getOrderPassTime().equals("")){
          if (sql01.equals(""))
              sql01 = " orderPassTime like '%"+model.getOrderPassTime()+"%'";
          else
              sql01 += " and orderPassTime like '%"+model.getOrderPassTime()+"%'";
        }
        if(model.getOrderPassId()!=null && !model.getOrderPassId().equals("")){
          if (sql01.equals(""))
              sql01 = " orderPassId like '%"+model.getOrderPassId()+"%'";
          else
              sql01 += " and orderPassId like '%"+model.getOrderPassId()+"%'";
        }
        if(model.getOrderSendState()!=null && !model.getOrderSendState().equals("")){
          if (sql01.equals(""))
              sql01 = "orderSendState like '%"+model.getOrderSendState()+"%'";
          else
              sql01 += " and orderSendState like '%"+model.getOrderSendState()+"%'";
        }
        if (!sql01.equals(""))
          sql = sql + " where " + sql01;
        Debug.println(sql);
        ResultSet rs = null;
         Connection con = null;
         Statement statement = null;
         try{
            con = DbPool.getConnection();
            statement = con.createStatement();
            rs = statement.executeQuery(sql);
            while(rs.next()){
              OrderModel ordermodel = new OrderModel();
              ordermodel.setOrderId(rs.getString("orderId"));
              ordermodel.setUserId(rs.getString("userId"));
              ordermodel.setOrderTime(rs.getString("orderTime"));
              ordermodel.setOrderStatus(rs.getString("orderStatus"));
              ordermodel.setOrderPassTime(rs.getString("orderPassTime"));
              ordermodel.setOrderPassId(rs.getString("orderPassId"));
              ordermodel.setOrderSendState(rs.getString("orderSendState"));
              ordermodel.setOrderRecName(rs.getString("orderRecName"));
              ordermodel.setOrderRecMail(rs.getString("orderRecMail"));
              ordermodel.setOrderRecAddress(rs.getString("orderRecAddress"));
              ordermodel.setOrderRecZip(rs.getString("orderRecZip"));
              ordermodel.setOrderTotalPrice(rs.getString("orderTotalPrice"));
              UserProxy lpProxy = new UserProxy();
              UserModel usermodel = lpProxy.queryUser(ordermodel.getUserId());
              if (usermodel != null)
                ordermodel.setUserName(usermodel.getUserName());
              list.add(ordermodel);
            }
        }catch(Exception e){
            throw e;
        }
        finally{
          DbPool.closeResultSet(rs);
          DbPool.closeStatement(statement);
          DbPool.closeConnection(con);
        }
        return list;
    }

    public OrderModel queryOrderByOrderId(String orderId) throws Exception{
      OrderModel model = null;
      String sql = "select orderId,userId,orderTime,orderStatus,orderPassTime,orderPassId,orderSendState,orderRecName,orderRecAddress,orderRecZip,orderTotalPrice from UserOrder_table";
      sql += " where orderId = '"+orderId+"'";
      ResultSet rs = null;
      Connection con = null;
      Statement statement = null;
      try{
        con = DbPool.getConnection();
        statement = con.createStatement();
        rs = statement.executeQuery(sql);
        while(rs.next()){
          model = new OrderModel();
          model.clear();
          model.setOrderId(orderId);
          String temp = rs.getString("userId");
          if (temp != null)
            model.setUserId(temp);
          temp = rs.getString("orderTime");
          if (temp != null)
            model.setOrderTime(temp);
          temp = rs.getString("orderStatus");
          if (temp != null)
            model.setOrderStatus(temp);
          temp = rs.getString("orderPassTime");
          if (temp != null)
            model.setOrderPassTime(temp);
          temp = rs.getString("orderPassId");
          if (temp != null)
            model.setOrderPassId(temp);
          temp = rs.getString("orderSendState");
          if (temp != null)
            model.setOrderSendState(temp);
          temp = rs.getString("orderRecName");
          if (temp != null)
            model.setOrderRecName(temp);
          temp = rs.getString("orderRecAddress");
          if (temp != null)
            model.setOrderRecAddress(temp);
          temp = rs.getString("orderRecZip");
          if (temp != null)
            model.setOrderRecZip(temp);
          temp = rs.getString("orderTotalPrice");
          if (temp != null)
            model.setOrderTotalPrice(temp);
          UserProxy lpProxy = new UserProxy();
          UserModel usermodel = lpProxy.queryUser(model.getUserId());
          if (usermodel != null)
            model.setUserName(usermodel.getUserName());
        }
      }catch(Exception e){
          throw e;
      }
      finally{
        DbPool.closeResultSet(rs);
        DbPool.closeStatement(statement);
        DbPool.closeConnection(con);
      }
      return model;
    }
}