orderdao.java

JavaBean+Jsp 开发的《手机商城系统》

package com.test.ps.order;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import com.test.ps.goods.*;
import com.test.ps.cart.*;
import com.test.ps.user.userBean;
import com.test.ps.util.DBUtil;


public class OrderDao {
	 private static OrderDao oda = null;
	 // 单例模式
    public static OrderDao getInstance() {
        if(oda == null) {
            oda = new OrderDao();
        }
        return oda;
    } 
    public static String dateToString(Date date){
    	  java.util.Date lastmodify = new java.util.Date() ;
    	    Calendar  c = Calendar.getInstance(); 
    	    c.setTime(lastmodify); 
    	    String datetime = ""+(c.get(c.YEAR)); 
    	    datetime = datetime+""+(c.get(c.MONTH)+1);
    	    datetime = datetime+""+c.get(c.DAY_OF_MONTH); 
    	    return datetime; 
    }
   // **************生成订单并返回订单号*****************
	public String buy(Cart c,String user,String addr) {
		Sale s = new Sale();
		s.setUserName(user);
		System.out.println(addr);
		s.setSaleAddr(addr);
		s.setSaleStatus(0);		
		s.setSaleDate(new Date());
		List<SaleItem> salesItems = new ArrayList<SaleItem>();
		List<CartItem> cartItems = c.getItems();
		for(int i=0; i<cartItems.size(); i++) {
			SaleItem si = new SaleItem();
			CartItem ci = cartItems.get(i);
			si.setGoodsName(ci.getGoods().getGoodsName());
			si.setGoodsCode(ci.getGoods().getGoodsCode());
			si.setGoods(ci.getGoods());
			si.setSaleNum(ci.getNum());
			si.setPrice(ci.getGoods().getGoodsPrice());
			salesItems.add(si);
		}
		s.setSi(salesItems);
		String sid=dateToString(new Date())+"001";			
	 	int id=Integer.valueOf(sid);
	 	while(saleCheck(id)){
		    	id=id+1;
	 	}
	 	sid=Integer.toString(id);
	 	s.setSaleId(sid);
		return add(s);              //调用增加订单的方法
	}
	//*****************检查订单号是否已经存在*****************************88
	public boolean saleCheck(int i)
	{
		Connection conn  =DB.getConnection();
        String sql = "select * from sale where saleId='"+i+"'";
        ResultSet rs = null;
		try {
			rs=DB.executeQuery(conn, sql);
			if(rs.next())
				return true;
			DB.closeResultSet(rs);
			DB.closeConnection(conn);
		 } catch (SQLException e) {
			e.printStackTrace();
		 }
		return false;
	}

 
 //***************增加订单,并更新数据库中********************
	public String add(Sale s) {
		 Connection conn  = null;
	     String sql = "insert into sale values (?, ?, ?, ? ,?)";
	     PreparedStatement preStmt = null;
	     ResultSet rsKey = null;
		String orderId = s.getSaleId();
		//System.out.println("成功到add方法");
		try{
			conn  = DB.getConnection();
            // 事务处理
            conn.setAutoCommit(false);
            preStmt = DB.getPreStatement(conn, sql, true);
			//System.out.println("成功到try 方法");
			preStmt.setString(1, s.getSaleId());
			preStmt.setString(2, s.getUserName());
			preStmt.setTimestamp(3, new Timestamp(s.getSaleDate().getTime()));
			preStmt.setInt(4,s.getSaleStatus());
			preStmt.setString(5,s.getSaleAddr());
			preStmt.executeUpdate();
			String sqlitem = "insert into saleItem(goodsCode,pNum,sPrice,saleId,goodsName) values (?, ?, ?, ?,?)";
			//System.out.println("成功到第二查询中方法");
			preStmt = DB.getPreStatement(conn, sqlitem);
			List<SaleItem> items = s.getSi();
			Iterator<SaleItem> it = items.iterator();
			while (it.hasNext()) {
				SaleItem si = it.next();
				   //System.out.println(si.getGoodsName());
				//preStmt.setInt(1, si.getGoods().getGId());
				preStmt.setString(1, si.getGoodsCode());
				preStmt.setInt(2, si.getSaleNum());
				preStmt.setDouble(3, si.getPrice());
				preStmt.setString(4, orderId);
				preStmt.setString(5, si.getGoodsName());
				  //System.out.println(si.getGoodsName());
				preStmt.executeUpdate();
				  //System.out.println(si.getGoodsName());
			}
			 conn.commit();
	         conn.setAutoCommit(true);
			 
		}catch (SQLException e) {
				e.printStackTrace();
				 try {
		                conn.setAutoCommit(true);
		                // 事务处理过程中出现问题，进行回滚
		                conn.rollback();
		            } catch (SQLException e1) {
		                e1.printStackTrace();
		            }
		}finally {
			DB.closeStatement(preStmt);
			DB.closeConnection(conn);
	}
		return orderId;
	}
//*********************根据订单ID删除订单*************************
	    public void deleteOrder(String s){

	        Connection conn = null;
			String sql;
			try {
				conn = DB.getConnection();
				sql ="delete from sale where saleId='"+s+"'";
				DB.executeUpdate(conn, sql);
				sql="delete from saleitem where saleId='"+s+"'";
				DB.executeUpdate(conn, sql);
			} finally {
				DB.closeConnection(conn);
			}
	    }
//***************************根据商品ID删除订单中的商品*************************************
	    public void deleteGoods(String id,String sid)
	    {
	    	Connection conn = null;
			String sql;
			try {
				conn = DB.getConnection();
				sql = "delete from saleitem where goodsCode='"+id +"' and saleId='"+sid+"'";
				DB.executeUpdate(conn, sql);
			} finally {
				DB.closeConnection(conn);
			}
	    }
	    //************得到所有订单************/
	    public List<Sale> getAllOrders()
	    {
	    	Connection conn = DB.getConnection();
	        Statement stmt = DB.getStatement(conn);
	    	Sale s;
	    	ArrayList<Sale> al=new ArrayList<Sale>();
	    	ResultSet rs = DB.getResultSet(stmt, "select * from sale");
	    	try{
	    		 //ResultSet rs = DB.getResultSet(stmt, "select * from sale");
	    		while(rs.next()){
	    			s=new Sale();
	    			String sid=rs.getString(1);
	    			String user=rs.getString(2);
	    			//Date d=rs.getDate(4);
	    			int ss=rs.getInt(4);
	    			String add=rs.getString(5);  
	    			s.setSaleId(sid);
	    			s.setUserName(user);
	    			s.setSaleDate(rs.getTimestamp("sDate"));
	    			s.setSaleStatus(ss);
	    			s.setSaleAddr(add);
	    			al.add(s);
	    		}
	    	}catch(Exception e){
	    		e.printStackTrace();
	    	} finally {
	            DB.closeResultSet(rs);
	            DB.closeStatement(stmt);
	            DB.closeConnection(conn);
	        }

	    	return al;
	    }
	    
	    //***************通过用户名得到订单********************/
	    public List<Sale> getOrdersByUserName(String s){
	    	Sale sa;
	    	ArrayList<Sale> al=new ArrayList<Sale>();
	    	 Connection conn = DB.getConnection();
	    	 Statement stmt = DB.getStatement(conn);
	    	 System.out.println("kjfdsf");
	         String sql = "select * from sale where username= '"+s+"'";
	         ResultSet rs = DB.getResultSet(stmt, sql);
	         System.out.println("kjfdsf");
	    	try{	    		 
	    		while(rs.next()){
	    			System.out.println("kjfdsf");
	    			sa=new Sale();
	    			sa.setSaleId(rs.getString(1));
	    			sa.setUserName(rs.getString(2));
	    			sa.setSaleDate(rs.getDate("sDate"));
	    			sa.setSaleStatus(rs.getInt(4)); 
	    			sa.setSaleAddr(rs.getString(5));
	    			al.add(sa);
	    		}
	    	}catch(Exception e){
	    		e.printStackTrace();
	    	} finally {
	            DB.closeResultSet(rs);
	            DB.closeStatement(stmt);
	            DB.closeConnection(conn);
	        }
	    	return al;
	    }
	    
	    /************根据订单ID得到订单************/
	    public Collection<Sale> getOrdersByOrderId(String s){
	    	 Connection conn = DB.getConnection();
	         Statement stmt = DB.getStatement(conn);
	         int id=Integer.parseInt(s);
	         String sql = "select * from sale where saleId="+id;
	         ResultSet rs = DB.getResultSet(stmt, sql);
	    	Sale sa;
	    	ArrayList<Sale> al=new ArrayList<Sale>();
	    	try{
	    		while(rs.next()){
	    			sa=new Sale();
	    			String sid=rs.getString(1);
	    			String user=rs.getString(2);
	    			//Date d=rs.getDate(4);
	    			int ss=rs.getInt(5);
	    			String add=rs.getString(6);  
	    			sa.setSaleId(sid);
	    			sa.setUserName(user);
	    			sa.setSaleDate(rs.getTimestamp("sDate"));
	    			sa.setSaleStatus(ss);
	    			sa.setSaleAddr(add);
	    			al.add(sa);
	    		}
	    	}catch(Exception e){
	    		e.printStackTrace();
	    	} finally {
	            DB.closeResultSet(rs);
	            DB.closeStatement(stmt);
	            DB.closeConnection(conn);
	        }
	    	return al;
	    }
	// ***********************************************   
		public Sale loaDBUtilyId(String id) {
			 Connection conn = DB.getConnection();
			 String sql = "select sale.saleId, sale.username, sale.sDate, sale.saleAddr, sale.sStatus , " +
 			 " user.id uid, user.username, user.password, user.address uaddr, user.phone, user.rdate from sale " +
 			 " join user on (sale.username = user.username) where sale.saleId = " + id; 
	        ResultSet rs = DB.executeQuery(conn, sql);
			Sale s =new Sale();
			try {
				if(rs.next()) {
					userBean u = new userBean();
					u.setUserId(rs.getInt("uid"));
					u.setAddress(rs.getString("uaddr"));
					u.setUserName(rs.getString("username"));
					u.setpassword(rs.getString("password"));
					u.setPhone(rs.getString("phone"));
					u.setRdate(rs.getTimestamp("rdate").toString());
					s.setSaleId(rs.getString("saleId"));
					s.setSaleAddr(rs.getString("saleAddr"));
					s.setSaleDate(rs.getTimestamp("sDate"));
					s.setSaleStatus(rs.getInt("sStatus"));
					s.setUserName(rs.getString("user.username"));					
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
	            DB.closeResultSet(rs);
	            //DB.closeStatement(stmt);
	            DB.closeConnection(conn);
	        }			
			return s;
		}
//*************根据订单号得到订单明细*****************88
		public List<SaleItem> getSalesItems(String orderId) {
			 Connection conn = DB.getConnection();
	         Statement stmt = DB.getStatement(conn);
			ResultSet rs = null;
			List<SaleItem> items = new ArrayList<SaleItem>();
			try {
				String sql = "select sale.saleId, sale.username, sale.sDate, sale.saleAddr, sale.sStatus , " +
							 " saleItem.saleItemId itemid, saleItem.goodsCode,saleItem.sPrice, saleItem.pNum, saleItem.saleId, " +
							 " goods.goodsCode , goods.goodsName, goods.descr, goods.goodsPrice, goods.gDate, goods.categoryId" +
							 " from sale join saleItem on (sale.saleId = saleItem.saleId)" +
							 " join goods on (saleItem.goodsCode = goods.goodsCode) where sale.saleId = " + orderId;
	            //System.out.println(sql);
				rs = DB.executeQuery(conn, sql);
				while(rs.next()) {
					Goods g = new Goods();
					g.setGoodsCode(rs.getString("goods.goodsCode"));
					g.setCategoryId(rs.getInt("categoryId"));
					g.setGoodsName(rs.getString("goods.goodsName"));
					g.setDescr(rs.getString("descr"));
					g.setGDate(rs.getTimestamp("gDate"));
					g.setGoodsPrice(rs.getFloat("goodsPrice"));

					
					SaleItem si = new SaleItem();
					si.setSaleId(orderId);
					si.setId(rs.getInt("itemid"));
					si.setPrice(rs.getFloat("goodsPrice"));
					si.setSaleNum(rs.getInt("pNum"));
					si.setGoods(g);
					si.setGoodsCode(rs.getString("saleItem.goodsCode"));
					si.setGoodsName(rs.getString("goods.goodsName"));
					items.add(si);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
	            DB.closeResultSet(rs);
	            DB.closeStatement(stmt);
	            DB.closeConnection(conn);
	        }
			
			return items;
		}

		public void updateStatus(Sale order) {
			 Connection conn = DB.getConnection();
	        // Statement stmt = DB.getStatement(conn);
			ResultSet rs = null;
			try {
				String sql = "update sale set sStatus = " + order.getSaleStatus() + " where saleId = " + order.getSaleId();
				DB.executeUpdate(conn, sql);
			}  finally {
	            DB.closeConnection(conn);
	        }		
		}
		public boolean changeSale(String saleid,int status){
			 Connection conn = DB.getConnection();
			//ResultSet rs = null;
			try{
				Sale s=new Sale();
				 String sql ="update sale set sStatus=" + status + " where saleId=" + saleid;
				DB.executeUpdate(conn,sql);
				 s.setSaleStatus(status);
			}catch(Exception e){
				e.printStackTrace();
			}
			return true;
			
		}
		public int getSaleStatus(String id) {
			 Connection conn = DB.getConnection();
	         Statement stmt = DB.getStatement(conn);
			ResultSet rs = null;
			int status=0;
	      	try{

	      	  rs=stmt.executeQuery("select sStatus from sale where saleId="+id);
	      		while(rs.next()){
	      			status=rs.getInt("sStatus");
	      		}
	      	}catch(Exception e){
	      		e.printStackTrace();
	      	} finally {
	            DB.closeResultSet(rs);
	            DB.closeStatement(stmt);
	            DB.closeConnection(conn);
	        }      	
			return status;
		}
		//得到未处理订单数量
		public int getUndealOrderCount(){
			int n=0;
	    	ResultSet rs=DBUtil.getResult("select * from sale where sStatus=0");
	    	try {
				rs.last();
				n=rs.getRow();
			} catch (SQLException e) {
				e.printStackTrace();
			}
	    	return n;
		}

}