orderbeanbo.java

这是一个购物网站可以实现购物的功能还有后台管理

package com.my.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class OrderBeanBO 
{
	private ResultSet rs=null;
	   private Connection ct=null;
	   private PreparedStatement ps=null;
	   public ArrayList showOrderDetail(String ordersId)
	   {   //System.out.println(ordersId);
		   ArrayList<OrderDetailBean> al=new ArrayList<OrderDetailBean>();
	     
		   try
		   {
			   ct=new ConnDB().getConn();
			   ps=ct.prepareStatement("select * from orderdetail where ordersId='"+ordersId+"'");
			   rs=ps.executeQuery();
			   while(rs.next())
			   {
				   OrderDetailBean odb=new OrderDetailBean();
				   odb.setOrderId(rs.getLong(1));
				   odb.setGoodsId(rs.getLong(2));
				   odb.setNums(rs.getInt(3));				  
				   al.add(odb);
			   }
		   }
		   catch(Exception e)
		   {
			   e.printStackTrace();
		   }finally
		   {
			   this.close();
		   }
		   return al;
	   }
     public OrderInfoBean addOrder(MyCartBO mbo,String userId)
     {
    	 OrderInfoBean ob=new OrderInfoBean();
    	 try
    	 {
    		 ct=new ConnDB().getConn();
    		 ps=ct.prepareStatement("insert into orders (userId,orderDate,isPayed,totalPrice) values('"+userId+"',now(),'0','"+mbo.getAllPrice()+"')");
    	 int a=ps.executeUpdate();
    	 if(a==1)
    	 { //取出ORDERS表的ID号
    		   ps=ct.prepareStatement("select max(ordersId) from orders");
    		   rs=ps.executeQuery();
    		   int ordersId=0;
    		   if(rs.next())
    		   {
    			   ordersId=rs.getInt(1);
    			   //System.out.println(ordersId);
    		   }
    		 //orders表添加成功
    		 //添加orderdetail表
    		 ArrayList al=mbo.showMyCart();
    		 //循环添加比较不好
    		 //使用批量方法提高操作数据库的效率
    		 Statement sm=ct.createStatement();
    		 for(int i=0;i<al.size();i++)
    		 {
    			 GoodsBean gb=(GoodsBean)al.get(i);
    			 sm.addBatch("insert into orderDetail (ordersId,goodsId,nums) values('"+ordersId+"','"+gb.getGoodsId()+"','"+mbo.getGoodsNumBygoodsId(gb.getGoodsId()+"")+"') ");
    		 }
    		 //开始批量执行
    		 sm.executeBatch();    		 
    		 String sql="select ordersId,truename,address,postcode,phone,totalPrice,username,email from users1,orders where ordersId='"+ordersId+"'and users1.userId=(select orders.userId from orders where ordersId='"+ordersId+"')";
    		 ps=ct.prepareStatement(sql);
    		 rs=ps.executeQuery();
    		 while(rs.next())
    		 {
    			//将RS封装到OB中
    			 ob.setOrdersId(rs.getLong(1));
    			 ob.setTruename(rs.getString(2));
    			 ob.setAddress(rs.getString(3));
    			 ob.setPostcode(rs.getString(4));
    			 ob.setPhone(rs.getString(5));
    			 ob.setTotalPrice(rs.getFloat(6));
    			 ob.setUsername(rs.getString(7));
    			 ob.setEmail(rs.getString(8));
    		 }	  		 
    	 }
    	 }
    	 catch(Exception e)
    	 {
  
    		 e.printStackTrace();
    	 }finally
    	 {
    		 this.close();
    	 }
    	 
    	 return ob;
     }
     
     public void close() {
 		try {
 			if (rs != null) {
 				rs.close();
 				rs = null;
 			}
 			if (ps != null) {
 				ps.close();
 				ps = null;
 			}
 			if (ct != null) {
 				ct.close();
 				ct = null;
 			}
 		} catch (SQLException e) {

 			e.printStackTrace();
 		}
 	}
}