ordersdao.java

Java购物车及JSTL的应用

package org.qhit.li.store.dao;

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

import org.qhit.li.store.dbmade.DBMade;
import org.qhit.li.store.dto.OrderDTO;
import org.qhit.li.store.dto.OrderSunDTO;

public class OrdersDAO {
	private int pageSize = 6;

	private int pageNum = 0;

	private int size = 0;

	private int pageId = 0;

	/**
	 * 插入子订单
	 * 
	 * @param info
	 * @return
	 */
	public int insertOrdersun(OrderSunDTO info) {
		int result = 0;
		String sql = "insert into OrderSons values(?,?,?,?,?,getdate())";
		Connection con = null;
		PreparedStatement ps = null;

		try {
			con = DBMade.getCon();
			ps = DBMade.getPds(con, sql);
			ps.setInt(1, info.getOid());
			ps.setString(2, info.getBtid());
			ps.setString(3, info.getBname());
			ps.setDouble(4, info.getSumprice());
			ps.setInt(5, info.getOsnumber());
			result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBMade.close(null, ps, con);
		}
		return result;
	}

	/**
	 * 把信息插入到总订单表中
	 * 
	 * @param info
	 * @return
	 */
	public int insertOrder(OrderDTO info) {
		int oid = 0;
		int result = 0;
		String sql = "insert into Orders values(?,?,1,?,getdate(),0,0)";
		String sqluid = "select top 1 oId from Orders order by oDate desc";
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = DBMade.getCon();
			ps = DBMade.getPds(con, sql);
			ps.setInt(1, info.getUid());
			ps.setDouble(2, info.getTotal());
			ps.setString(3, info.getRemark());

			result = ps.executeUpdate();

			if (result != 0) {
				ps = DBMade.getPds(con, sqluid);
				rs = ps.executeQuery();
				if (rs.next()) {
					oid = rs.getInt(1);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBMade.close(rs, ps, con);
		}

		return oid;
	}

	/**
	 * 得到所有订单的状态信息
	 * 
	 * @return
	 */
	public ArrayList<OrderDTO> selectO_S_U(int pageid) {
		this.pageId = pageid;
		ArrayList<OrderDTO> order = null;
		String sql = "select * from O_U_S";
		Connection con = null;
		Statement stm = null;
		ResultSet rs = null;

		try {
			con = DBMade.getCon();
			stm = con.createStatement(1004, 1008);
			rs = stm.executeQuery(sql);
			order = paginationgoods(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBMade.close(rs, stm, con);
		}

		return order;
	}

	/**
	 * 查询某一类型的定单 
	 * @param pageid
	 * @param status
	 * @return
	 */
	public ArrayList<OrderDTO> selectByType(int pageid,int status){
		this.pageId = pageid;
		ArrayList<OrderDTO> order = null;
		String sql = "select * from O_U_S where oStatus='"+status+"'";
		Connection con = null;
		Statement stm = null;
		ResultSet rs = null;
		try {
			con = DBMade.getCon();
			stm = con.createStatement(1004, 1008);
			rs = stm.executeQuery(sql);
			order = paginationgoods(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBMade.close(rs, stm, con);
		}

		return order;
	}
	
	/**
	 * 得到订单和用户的详细信息
	 * @param oid
	 * @return
	 */
	public OrderDTO selectO_U_S_X(int oid){
		OrderDTO info=null;
		String sql = "select * from O_U_S_X where oId=?";
		Connection con = null;
		PreparedStatement pds=null;
		ResultSet rs = null;
		try {
			con = DBMade.getCon();
			pds=DBMade.getPds(con, sql);
			pds.setInt(1, oid);
			rs=pds.executeQuery();
			if(rs.next()){
				info=new OrderDTO();
				info.setOid(rs.getInt(1));
				info.setTotal(rs.getDouble(2));
				info.setStatus(rs.getString(3));
				info.setDate(rs.getDate(4));
				info.setRemark(rs.getString(5).trim());
				info.setUname(rs.getString(6));
				info.setPhone(rs.getString(7));
				info.setEmail(rs.getString(8));
				info.setZip(rs.getString(9));
				info.setAddress(rs.getString(10));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBMade.close(rs, pds, con);
		}
		return info;
	}
	
	
	/**
	 * 得到子订单的详细
	 * @param oid
	 * @return
	 */
	public ArrayList<OrderSunDTO> selectOrderSon(int oid){
		ArrayList<OrderSunDTO> order = new ArrayList<OrderSunDTO>();
		String sql = "select * from OrderSons where oId=?";
		Connection con = null;
		PreparedStatement pds=null;
		ResultSet rs = null;
		try {
			con = DBMade.getCon();
			pds=DBMade.getPds(con, sql);
			pds.setInt(1, oid);
			rs = pds.executeQuery();
			while (rs.next()) {
				OrderSunDTO tempson=new OrderSunDTO();
				tempson.setBtid(rs.getString(3));
				tempson.setBname(rs.getString(4));
				tempson.setBprice(rs.getDouble(5));
				tempson.setOsnumber(rs.getInt(6));
				tempson.setSumprice(tempson.getOsnumber()*tempson.getBprice());
				order.add(tempson);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBMade.close(rs, pds, con);
		}
		return order;
	}
	
	public int updateStatus(int stid,int oid,int sid){
		int result=0;
		String sql = "update Orders set oStatus=?,sId=? where oId=?";
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = DBMade.getCon();
			ps = DBMade.getPds(con, sql);
			ps.setInt(1, stid);
			ps.setInt(2, sid);
			ps.setInt(3, oid);
			result=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBMade.close(null, ps, con);
		}
		return result;
	}
	
	/**
	 * 删除总订单表的信息
	 * 
	 * @param oid
	 */
	public void deletOrder(int oid) {
		String sql = "delete Orders where oId=?";
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = DBMade.getCon();
			ps = DBMade.getPds(con, sql);
			ps.setInt(1, oid);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBMade.close(null, ps, con);
		}
	}

	/**
	 * 订单分页技术
	 * 
	 * @param rs
	 * @return
	 * @throws Exception
	 */
	public ArrayList<OrderDTO> paginationgoods(ResultSet rs) throws Exception {
		ArrayList<OrderDTO> order = null;
		try {
			if (rs.next()) {
				order = new ArrayList<OrderDTO>();

				// 得到所用行数
				rs.last();
				size = rs.getRow();

				pageNum = (size % pageSize == 0) ? (size / pageSize) : (size
						/ pageSize + 1);

				rs.beforeFirst();

				// 判断页数是否存在
				if (this.pageId == pageNum + 1) {
					this.pageId = 1;
				}

				if (this.pageId == 0) {
					this.pageId = pageNum;
				}

				// 下移游标
				for (int i = 0; i < (pageId - 1) * pageSize; i++) {
					rs.next();
				}

				// 循环取值
				for (int i = 0; i < pageSize; i++) {
					OrderDTO temporder = new OrderDTO();
					if (!rs.next()) {
						break;
					}
					temporder.setUname(rs.getString(1));
					temporder.setOid(rs.getInt(2));
					temporder.setTotal(rs.getDouble(3));
					temporder.setStatus(rs.getString(4));
					temporder.setDate(rs.getDate(5));
					order.add(temporder);
				}
			}
		} catch (Exception e) {
			throw new Exception("操作出现异常");
		}
		return order;
	}

	public int getPageNum() {
		return pageNum;
	}

	public int getPageId() {
		return pageId;
	}

}