orderdao.java

一套购物车项目。电子商务系统。实现了前台和后台的业务逻辑。

package com.softfz.jn0708.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import com.softfz.jn0708.bean.ItemBean;
import com.softfz.jn0708.bean.OrderBean;
import com.softfz.jn0708.bean.OrderDetailBean;
import com.softfz.jn0708.bean.UserBean;
import com.softfz.jn0708.util.SqlUtil;


/**
 * 订单操作DAO
 * @author admin
 *
 */
public class OrderDAO {

	/**
	 * 获取订单编号
	 * @return
	 */
	public String getOrderID(){
		String orderid = null;
		Connection conn = SqlUtil.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String Sql = null;
		
		try {
			Sql = "select SEQ_T_ORDERINFO.NEXTVAL as orderid FROM  dual";
			ps = conn.prepareStatement(Sql);
			rs = ps.executeQuery();
			
			Calendar cd = Calendar.getInstance();
			DateFormat df = new SimpleDateFormat("yyyyMMdd");
			String id = df.format(cd.getTime());
			
			if(rs==null){							
				orderid = id+"001" ;
			}else{				
				if (rs.next()) {
					if(rs.getInt("orderid")<10){
						orderid = id+"00"+String.valueOf(rs.getInt("orderid"));	
					}
					if(9<rs.getInt("orderid")&&rs.getInt("orderid")<100){
						orderid = id+"0"+String.valueOf(rs.getInt("orderid"));	
					}
				}
			}		

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			SqlUtil.tryToClose(rs, ps, conn);
		}		
		return orderid;
	}
	/**
	 * 获取订单明细编号
	 * @return
	 */
	public static String getOrderDetailID(){
		String orderdetailid = null;
		Connection conn = SqlUtil.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String Sql = null;
		
		try {
			Sql = "select seq_t_orderdetail.NEXTVAL as orderdetailid FROM  dual";
			ps = conn.prepareStatement(Sql);
			rs = ps.executeQuery();
			
			Calendar cd = Calendar.getInstance();
			DateFormat df = new SimpleDateFormat("yyyyMMdd");
			String id = df.format(cd.getTime());
			
			if(rs==null){							
				orderdetailid = id+"001" ;
			}else{				
				if (rs.next()) {
					if(rs.getInt("orderdetailid")<10){
						orderdetailid = id+"00"+String.valueOf(rs.getInt("orderdetailid"));	
					}
					if(9<rs.getInt("orderdetailid")&&rs.getInt("orderdetailid")<100){
						orderdetailid = id+"0"+String.valueOf(rs.getInt("orderdetailid"));	
					}
				}
			}		

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			SqlUtil.tryToClose(rs, ps, conn);
		}		
		return orderdetailid;
	}
	/**
	 * 用PreparedStatement对象执行多条插入语句
	 * @param sqls sql语句集
	 * @return
	 */
	public boolean executeUpdate(String[] sqls){
		Connection conn = SqlUtil.getConn();
		Statement st = null;
		try {
			conn.setAutoCommit(false);//设置不自动提交方式
			st = conn.createStatement();

			for(int i=0;i<sqls.length;i++){
				st.addBatch(sqls[i]);//把要更新的数据库语句加入
			}
			st.executeBatch();
			//加入完成后提交事物
			conn.commit();
			return true;
		} catch (SQLException e) {		
			e.printStackTrace();
			//出错回滚
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}finally{
			//关闭连接
			SqlUtil.tryToClose(null,null,conn);
		}		
		return false;
	}	
	
	/**
	 * 添加订单
	 * @param order 订单bean对象
	 * @param list 订单列表
	 * @return
	 */
	public boolean addorder(OrderBean order, List list) {
		boolean flag = false;
		String[] sqls;
		String orderSql = "";
		if(list!=null&&list.size()!=0){
			int len = list.size() +1;
			if(order!=null){
				sqls = new String[len];
				for(int i=0 ; i<list.size();i++){
					ItemBean item = (ItemBean)list.get(i);
					String sql = null ;
					sql="insert into t_orderdetail(orderdetailid,orderid,itemid,title,itemcount,price)";
					sql= sql+" values(SEQ_T_ORDERDETAIL.NEXTVAL,'"+order.getOrderid()+"',"+item.getItemid()+",";
					sql= sql+"'"+item.getTitle()+"',"+item.getQuantity()+","+item.getPrice()+")";
					sqls[i]=sql;
				}
				orderSql="insert into t_orderinfo(ORDERID,USERID,USERNAME,USERADDRESS,USERCODE,USERPHONE,USEREMAIL,ORDERTIME,";
				orderSql=orderSql+ "PAYTYPE,CARRIEDTYPE,HASAPPROVED,RESULT,APPROVEDUSERID,APPROVEDDATE)";
				orderSql=orderSql+ " values('";
				orderSql=orderSql+order.getOrderid()+"',"+order.getUserid()+",'"+order.getUsername()+"','"+order.getUseraddress()+"','";
				orderSql=orderSql+order.getUsercode()+"','"+order.getUserphone()+"','"+order.getUseremail()+"','"+order.getOrdertime()+"','";
				orderSql=orderSql+order.getPaytype()+"','"+order.getCarriedtype()+"','0','"+order.getResult()+"',";
				orderSql=orderSql+order.getApproveduserid()+",'"+order.getApproveddate();
				orderSql=orderSql+"')";				
				sqls[list.size()]=orderSql;
				flag = this.executeUpdate(sqls);			
				
			}
		}
		
		
		return flag;	
	}
	/**
	 * 获取订单列表
	 * @param currentPage 当前页码
	 * @param count 当前显示页数
	 * @param order 订单Bean对象
	 * @return
	 */
	public List queryorder(int currentPage, int count, OrderBean order) {
		// TODO 自动生成方法存根

		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		List list = null;		
		StringBuffer centent = new StringBuffer();
		centent.append(" select o.* from t_Orderinfo o ");
		centent.append(" where 1=1 ");	
		
		if(order.getOrderid()!=null){
			
		}

		if(order.getOrderid()!=null&&!order.getOrderid().trim().equals("")){
			centent.append(" and o.orderid = '").append(order.getOrderid().trim()).append("'");
		}
		if(order.getUsername()!=null&&!order.getUsername().trim().equals("")){
			centent.append(" and o.username like '%").append(order.getUsername().trim()).append("%'");
		}
		if(order.getOrdertime()!=null&&!order.getOrdertime().trim().equals("")){
			centent.append(" and o.ordertime = '").append(order.getOrdertime()).append("'");
		}		
		
		
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM(")
		.append("SELECT  m.*,ROWNUM AS num FROM (")
		.append(centent)
		.append(") m ) t WHERE ");
		sql.append(" t.num<=").append(currentPage*count);
		sql.append(" AND t.num>").append((currentPage-1)*count);
		sql.append(" order by t.orderid");
				
		StringBuffer classsql = new StringBuffer();
		classsql.append("SELECT * FROM t_Orderinfo");	
		
		try {
			conn = SqlUtil.getConn();
			st = conn.createStatement();
			rs = st.executeQuery(sql.toString());
			list = new ArrayList();
			while (rs.next()) {
				order = new OrderBean();
				order.setOrderid(rs.getString("orderid"));
				order.setUsername(rs.getString("username"));
				order.setUserphone(rs.getString("userphone"));
				order.setOrdertime(rs.getString("ordertime"));
				order.setHasapproved(rs.getString("hasapproved"));
				order.setResult(rs.getString("result"));
				list.add(order);
			}
				
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			SqlUtil.tryClose(rs,st,conn);
		}
		
		return list;
	}
	/**
	 * 获取订单列表
	 * @param currentPage 当前页码
	 * @param count 当前显示页数
	 * @param order 订单Bean对象
	 * @return
	 */
	public List queryorder(int currentPage, int count, OrderBean order,HttpServletRequest request) {
		// TODO 自动生成方法存根
		HttpSession session = request.getSession();
		UserBean user = (UserBean)session.getAttribute("user");
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		List list = null;		
		StringBuffer centent = new StringBuffer();
		centent.append(" select o.* from t_Orderinfo o ");
		centent.append(" where 1=1 and userid =").append(user.getUserid());	
		
		if(order.getOrderid()!=null){
			
		}

		if(order.getOrderid()!=null&&!order.getOrderid().trim().equals("")){
			centent.append(" and o.orderid = '").append(order.getOrderid().trim()).append("'");
		}
		if(order.getUsername()!=null&&!order.getUsername().trim().equals("")){
			centent.append(" and o.username like '%").append(order.getUsername().trim()).append("%'");
		}
		if(order.getOrdertime()!=null&&!order.getOrdertime().trim().equals("")){
			centent.append(" and o.ordertime = '").append(order.getOrdertime()).append("'");
		}		
		
		
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM(")
		.append("SELECT  m.*,ROWNUM AS num FROM (")
		.append(centent)
		.append(") m ) t WHERE ");
		sql.append(" t.num<=").append(currentPage*count);
		sql.append(" AND t.num>").append((currentPage-1)*count);
		sql.append(" order by t.orderid");
				
		StringBuffer classsql = new StringBuffer();
		classsql.append("SELECT * FROM t_Orderinfo");	
		
		try {
			conn = SqlUtil.getConn();
			st = conn.createStatement();
			rs = st.executeQuery(sql.toString());
			list = new ArrayList();
			while (rs.next()) {
				order = new OrderBean();
				order.setOrderid(rs.getString("orderid"));
				order.setUsername(rs.getString("username"));
				order.setUserphone(rs.getString("userphone"));
				order.setOrdertime(rs.getString("ordertime"));
				order.setHasapproved(rs.getString("hasapproved"));
				order.setResult(rs.getString("result"));
				list.add(order);
			}
				
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			SqlUtil.tryClose(rs,st,conn);
		}
		
		return list;
	}
	/**获取订单表Bean
	 * @param orderid
	 * @return
	 */
	public OrderBean getorderByID(String orderid) {
		OrderBean orderbean =null;
		Connection conn = SqlUtil.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String Sql = null;
		
		try {
			Sql = "select * from t_orderinfo where orderid = '"+orderid+"'";
			ps = conn.prepareStatement(Sql);
			rs = ps.executeQuery();			
			while (rs.next()) {
				orderbean = new OrderBean();
				orderbean.setOrderid(rs.getString("orderid"));
				orderbean.setUsername(rs.getString("username"));
				orderbean.setUserphone(rs.getString("userphone"));
				orderbean.setUseraddress(rs.getString("useraddress"));
				orderbean.setUsercode(rs.getString("usercode"));
				orderbean.setUseremail(rs.getString("useremail"));				
				orderbean.setPaytype(rs.getString("paytype"));
				orderbean.setCarriedtype(rs.getString("carriedtype"));
				orderbean.setOrdertime(rs.getString("ordertime"));
			}		

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			SqlUtil.tryToClose(rs, ps, conn);
		}
		
		return orderbean;
	}
	
	/**
	 * 获取明细表的Bean
	 * @param orderid
	 * @return
	 */
	public List getdetailorderbyid(String orderid) {
		OrderDetailBean detailbean = null;
		Connection conn = SqlUtil.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String Sql = null;
		List list = null;
		try {
			Sql = "select * from t_orderdetail where orderid = '"+orderid+"'";
			ps = conn.prepareStatement(Sql);
			rs = ps.executeQuery();			
			list = new ArrayList();
			while (rs.next()) {
				detailbean = new OrderDetailBean();
				detailbean.setOrderdetailid(rs.getString("orderdetailid"));
				detailbean.setOrderid(rs.getString("orderid"));
				detailbean.setItemid(rs.getString("itemid"));
				detailbean.setTitle(rs.getString("title"));
				detailbean.setItemcount(rs.getString("itemcount"));
				detailbean.setPrice(rs.getString("price"));
				list.add(detailbean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			SqlUtil.tryToClose(rs, ps, conn);
		}		
		
		return list;
	}
	
	/**
	 * 订单审核
	 * @param orderid 订单编号
	 * @param orderbean 订单bean对象
	 * @param flag 订单审核标志
	 * @return
	 */
	public boolean orderpass(String orderid, OrderBean orderbean,int flag) {
		
		StringBuffer sql = new StringBuffer();
		sql.append("update t_orderinfo set");
		sql.append(" RESULT='").append(orderbean.getResult()).append("',");
		sql.append(" HASAPPROVED='").append(String.valueOf(flag)).append("',");
		sql.append(" APPROVEDUSERID='").append(orderbean.getApproveduserid()).append("',");
		sql.append(" APPROVEDDATE='").append(orderbean.getApproveddate()).append("'");
		sql.append(" where orderid=").append(orderid);
		return SqlUtil.execteUpdate(sql.toString());

	}
	
	/**
	 * 修改订单
	 * @param order
	 * @return
	 */
	public boolean modifyorder(OrderBean order) {
		StringBuffer sql = new StringBuffer();
		sql.append("update t_orderinfo set");
		sql.append(" username='").append(order.getUsername()).append("',");
		sql.append(" userphone='").append(order.getUserphone()).append("',");
		sql.append(" useraddress='").append(order.getUseraddress()).append("',");
		sql.append(" usercode='").append(order.getUsercode()).append("',");
		sql.append(" ordertime='").append(order.getOrdertime()).append("',");		
		sql.append(" useremail='").append(order.getUseremail()).append("',");
		sql.append(" paytype='").append(order.getPaytype()).append("',");
		sql.append(" carriedtype='").append(order.getCarriedtype()).append("'");
		sql.append(" where orderid='").append(order.getOrderid()).append("'");
		return SqlUtil.execteUpdate(sql.toString());
	}
}