orderdao.java

一个简易的网上书店（jsp+sqlservler+struts）。

package com.webshop.db;
import java.math.BigDecimal;
import java.sql.*;
import com.webshop.domain.*;

import com.webshop.exception.ActionException;

import java.util.*;

/**
 * @author w
 *
 * To change this generated comment edit the template variable "typecomment":
 * Window>Preferences>Java>Templates.
 * To enable and disable the creation of type comments go to
 * Window>Preferences>Java>Code Generation.
 */
public class OrderDAO {

	Connection con;//数据库连接
	

	
    /**
     * SQL STATEMENT
     */
	String newOrder="insert into orders values(?,?,?,?,?,?,?,?,?,?,?,?)";
	String newLineItem="insert into lineitem values(?,?,?,?,?)";
	String newOrderStatus="insert into orderstatus values(?,?,?,?)";
	String newPayInfo="insert into payinfo values(?,?,?,?,?)";
	String newCreditCardInfo="insert into creditcardinfo values(?,?,?,?,?,?,?,?,?,?,?)";
	String selectOrdersByUserId="select * from orders where userid=?";
	String selectOrderByOrderId="select * from orders where orderid=?";
	String selectPayInfoByOrderId="select * from payInfo where orderid=?";
	String selectCreditCardById="select * from creditcardinfo where id=?";
	String selectLineItemByOrderId="select * from lineitem where orderid=?";
	/**
	 * Constructor for OrderDAO.
	 */
	public OrderDAO() {
		super();
	}
	
	/**
	 * 生成订单。
	 * 需要同时在多个表中添加记录
	 * 注意事物处理方式。
	 */
	public void createNewOrder(Order order)throws Exception
	{
				
		try
		{
			con=DatabaseConnection.getConnection();
		}
		catch(Exception e)
		{
			throw e;
		}
		
		try
		{
			con.setAutoCommit(false);
			createOrdersRecord(con,order);
			createLineItemRecords(con,order);					
			createPayInfoRecord(con,order);
			con.commit();
			con.setAutoCommit(true);
		}
		catch(Exception e)
		{
			 con.rollback();
     		 throw e;
		}
		finally
		{
			try
			{
				if(con!=null)
				con.close(); 
			}
			catch(Exception e2)
			{
			}
		}
	}
	
	public void createOrderStatusRecord(Connection connection,OrderStatus orderStatus)throws Exception
	{
		PreparedStatement pstmt=connection.prepareStatement(newOrderStatus);
		pstmt.setInt(1,orderStatus.getOrderId());
		pstmt.setInt(2,orderStatus.getLineNumber());
		pstmt.setDate(3, new java.sql.Date(orderStatus.getTimeStamp().getTime()));
		pstmt.setString(4,"预处理");	
		pstmt.executeUpdate();
	}
	public void createLineItemRecords(Connection connection,Order order)throws Exception
	{
		List items=order.getLineItems();
		Iterator it=items.iterator();
		LineItem lineItem; 
		OrderStatus orderStatus;
		while(it.hasNext())
		{
			lineItem =(LineItem)it.next();
			lineItem.setOrderId(order.getOrderId());
			createLineItemRecord(connection,lineItem);	
			orderStatus =new OrderStatus(order.getOrderId(),lineItem.getLineNumber(),new java.util.Date(),Order.ORDER_STATUS_PREPROCESSED);
			createOrderStatusRecord(connection,orderStatus);
		}
	}
	
	public void createLineItemRecord(Connection connection,LineItem lineItem)throws Exception
	{
		PreparedStatement pstmt=connection.prepareStatement(newLineItem);
		pstmt.setInt(1,lineItem.getOrderId());
		pstmt.setInt(2,lineItem.getLineNumber());
		pstmt.setString(3,lineItem.getItemId())	;
		pstmt.setInt(4,lineItem.getQuantity());
		pstmt.setFloat(5,lineItem.getTotal().floatValue());
		pstmt.executeUpdate();
		
	}
	
		
	public void createPayInfoRecord(Connection connection,Order order)throws Exception
	{
		PreparedStatement pstmt=connection.prepareStatement(newPayInfo);
		pstmt.setInt(1,order.getOrderId());
		pstmt.setString(2,order.getPayInfo().getPayType());
		pstmt.setInt(3,0);
		pstmt.setDate(4,new java.sql.Date(new java.util.Date().getTime()));
		if(order.getPayInfo().getCreditCardInfo()!=null) 
		{
			 int nextid=SequenceDAO.getInstance().getNextId("creditcardinfo");
			 pstmt.setInt(5,nextid);
			 createCreditCardRecord(con,order.getPayInfo().getCreditCardInfo(), nextid);
		}
		else
		{
			pstmt.setInt(5,0);
		}
		pstmt.executeUpdate();		
		
	}
	
    public void createCreditCardRecord(Connection connection,CreditCardInfo creditCardInfo,int nextid)throws Exception    
	{
		
		PreparedStatement pstmt=connection.prepareStatement(newCreditCardInfo);
		pstmt.setInt(1,nextid);
		pstmt.setString(2,creditCardInfo.getCreditCard());
		pstmt.setString(3,creditCardInfo.getExpiryDate());
		pstmt.setString(4,creditCardInfo.getCardType());
		pstmt.setString(5,creditCardInfo.getBillAddr1());
		pstmt.setString(6,creditCardInfo.getBillAddr2());
		pstmt.setString(7,creditCardInfo.getBillCity());
		pstmt.setString(8,creditCardInfo.getBillState());
		pstmt.setString(9,creditCardInfo.getBillZip());
		pstmt.setString(10,creditCardInfo.getBillCountry());
		pstmt.setString(11,creditCardInfo.getBillToName());
		pstmt.executeUpdate();
		
	}

	public void createOrdersRecord(Connection connection,Order order)throws Exception
	{
		
			PreparedStatement pstmt=connection.prepareStatement(newOrder);
			pstmt.setInt(1,order.getOrderId());
			pstmt.setString(2,order.getUserId());
			pstmt.setDate(3,new java.sql.Date(order.getOrderDate().getTime()));
			pstmt.setString(4,order.getShipAddress1());
			pstmt.setString(5,order.getShipAddress2());
			pstmt.setString(6,order.getShipCity());
			pstmt.setString(7,order.getShipState());
			pstmt.setString(8,order.getShipZip());
			pstmt.setString(9,order.getShipCountry());
			pstmt.setString(10,order.getShipToName());
			pstmt.setString(11,order.getCourier());
			pstmt.setFloat(12,order.getTotalPrice().floatValue());
			pstmt.executeUpdate();			
	}
	
	public java.util.Collection getOrdersByUserId(String userId)throws Exception
	{
		
		java.util.Collection orders=new java.util.ArrayList();
		try
		{
			con=DatabaseConnection.getConnection();
		}
		catch(Exception e)
		{
			throw e;
		}
		
		try
		{
			PreparedStatement pstmt=con.prepareStatement(selectOrdersByUserId);
			pstmt.setString(1,userId);
			ResultSet rst=pstmt.executeQuery(); 
            Order order;
            while(rst.next())
            {
            	order=new Order();
            	order.setOrderId(rst.getInt("orderid"));
            	order.setOrderDate(rst.getDate("orderdate"));
            	order.setTotalPrice( rst.getBigDecimal("totalprice"));
            	orders.add(order);
            }
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw e;
		}
		finally
		{
			try
			{
				if(con!=null)
				con.close();
			}
			catch(Exception e2)
			{
			}
		}
		return orders;
	}
	
	public Order getOrderByOrderId(int orderId)throws Exception 
	{
		 Order order=null;
		try
		{
			con=DatabaseConnection.getConnection();
		}
		catch(Exception e)
		{
			throw e;
		}
		
		try
		{		
			PreparedStatement pstmt=con.prepareStatement(selectOrderByOrderId);
			pstmt.setInt(1,orderId);
			ResultSet rst=pstmt.executeQuery(); 
           
            if(rst.next())
            {
            	order=new Order();
            	order.setOrderId(rst.getInt("orderid"));
            	order.setOrderDate(rst.getDate("orderdate"));
            	order.setTotalPrice( rst.getBigDecimal("totalprice"));
            	order.setUserId(rst.getString("userid"));
            	order.setShipAddress1(rst.getString("shipaddr1"));
            	order.setShipAddress2(rst.getString("shipaddr2"));
            	order.setShipCity(rst.getString("shipcity"));
            	order.setShipState(rst.getString("shipstate"));
            	order.setShipZip(rst.getString("shipzip"));
            	order.setShipCountry(rst.getString("shipcountry"));
            	order.setShipToName(rst.getString("shiptoname"));
            	order.setCourier(rst.getString("courier"));
            	order.setLineItems(getLineItemsByOrderId(orderId));
            	order.setPayInfo(getPayInfoByOrderId(orderId));
            
            }
		}
		catch(Exception e)
		{
			throw e;
		}
		finally
		{
			try
			{
				if(con!=null)
				con.close();
			}
			catch(Exception e2)
			{
			}
		}
		return order;
		
	}
	
	public PayInfo getPayInfoByOrderId(int orderId)throws Exception
	{
	
		PayInfo payInfo=null;
		try
		{
			con=DatabaseConnection.getConnection();
		}
		catch(Exception e)
		{
			throw new ActionException("不能获得连接，"+e.getMessage());
		}
		try
		{
			PreparedStatement pstmt=con.prepareStatement(selectPayInfoByOrderId);
			pstmt.setInt(1,orderId);
			ResultSet rst=pstmt.executeQuery();
          
			if(rst.next())
			{
				payInfo=new PayInfo(orderId);		
				payInfo.setPayType(rst.getString("paytype"));
				payInfo.setIsFinished(rst.getInt("isfinished")==0?false:true);
				payInfo.setTransactionDate(rst.getDate("transactiondate"));
				if("CreditCard".equals(payInfo.getPayType())) 				
				payInfo.setCreditCardInfo(getCreditCardInfoById(rst.getInt("creditcardinfo")));
			
			  
			}
				//con.close();
		}
		catch(SQLException e)
		{
			 throw new ActionException("执行数据库操作发生错误，"+e.getMessage());
		}
		finally
		{
			try
			{
				if(con!=null)
				con.close();
			}
			catch(Exception e2)
			{
			}
		}
		return payInfo;
	}
	
	public java.util.List getLineItemsByOrderId(int orderid)throws Exception
	{
		
		java.util.List lineItems=new java.util.ArrayList();
		ItemDAO itemDAO=new ItemDAO();
		OrderStatusDAO orderStatusDAO=new OrderStatusDAO();
		try
		{
			con=DatabaseConnection.getConnection();
		}
		catch(Exception e)
		{
			throw e;
		}
		
		try
		{
			PreparedStatement pstmt=con.prepareStatement(selectLineItemByOrderId);
			pstmt.setInt(1,orderid);
			ResultSet rst=pstmt.executeQuery(); 
            LineItem lineItem;
            while(rst.next())
            {
            	lineItem=new LineItem();
            	lineItem.setOrderId(rst.getInt("orderid"));
            	lineItem.setLineNumber(rst.getInt("linenum"));
            	lineItem.setItemId(rst.getString("itemid"));
            	lineItem.setQuantity(rst.getInt("quantity"));
            	lineItem.setUnitPrice(rst.getBigDecimal("unitprice"));   
            	lineItem.setItem(itemDAO.getItemById(lineItem.getItemId()));   
            	lineItem.setOrderStatus(orderStatusDAO.getOrderStatusById(lineItem.getOrderId(),lineItem.getLineNumber()))  ;    	
            	lineItems.add(lineItem);
            }
		}
		catch(Exception e)
		{
			throw e;
		}
		finally
		{
			try
			{
				if(con!=null)
				con.close();
			}
			catch(Exception e2)
			{
			}
		}
		System.out.println("lienItems:\n"+lineItems.size());
		return lineItems;
	}	
	
	public CreditCardInfo getCreditCardInfoById(int id)throws Exception
	{
		CreditCardInfo creditCardInfo=null;
		try
		{
			con=DatabaseConnection.getConnection();
		}
		catch(Exception e)
		{
			throw new ActionException("不能获得连接，"+e.getMessage());
		}
		try
		{
			PreparedStatement pstmt=con.prepareStatement(selectCreditCardById);
			pstmt.setInt(1,id);
			ResultSet rst=pstmt.executeQuery();
          
			if(rst.next())
			{
				creditCardInfo=new CreditCardInfo();
				creditCardInfo.setId(String.valueOf(id));
				creditCardInfo.setCreditCard(rst.getString("creditcard"));
				creditCardInfo.setExpiryDate(rst.getString("exprdate"));
				creditCardInfo.setCardType(rst.getString("cardtype"));
				creditCardInfo.setBillAddr1(rst.getString("billaddr1"));
				creditCardInfo.setBillAddr2(rst.getString("billaddr2"));
				creditCardInfo.setBillCity(rst.getString("billcity"));
				creditCardInfo.setBillState(rst.getString("billstate"));
				creditCardInfo.setBillZip(rst.getString("billzip"));
				creditCardInfo.setBillCountry(rst.getString("billcountry"));
                creditCardInfo.setBillToName(rst.getString("billtoname"));
			}
		}
		catch(Exception e)
		{
			throw e;
		}
		finally
		{
			try
			{
				if(con!=null)
				con.close();
			}
			catch(Exception e2)
			{
			}
		}
		return creditCardInfo;
	}

}