ordersdaoimpl.java

旅馆预订系统.采用jstl+oracle,jsp/servlet,,供初学者学习用.

package com.night.DAO.Impl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;

import oracle.jdbc.OracleTypes;
import Util.DB;
import Util.ListSwap;

import com.night.DAO.OrdersDAO;

public class OrdersDAOImpl implements OrdersDAO {

	public void insertOrUpdateOrder(String housestyle, int id,
			String roomstyle, int num, String custom, String tel, String email) {

		Connection conn = DB.getConn();
		try {
			CallableStatement cstmt = conn
					.prepareCall("{call HotelSystem.insertOrUpdateOrder(?,?,?,?,?,?,?)}");
			cstmt.setString(1, housestyle);
			cstmt.setInt(2, id);
			cstmt.setString(3, roomstyle);
			cstmt.setInt(4, num);
			cstmt.setString(5, custom);
			cstmt.setString(6, tel);
			cstmt.setString(7, email);
			cstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(conn);
		}
	}

	public List showOrders() {
		Connection conn = DB.getConn();
		List list = null;
		try {
			CallableStatement cstmt = conn
					.prepareCall("{call HotelSystem.showOrders(?)}");
			cstmt.registerOutParameter(1, OracleTypes.CURSOR);
			cstmt.execute();
			ResultSet rs = (ResultSet) cstmt.getObject(1);
			list = ListSwap.toArrayList(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(conn);
		}
		return list;
	}

	public void deleteOrderByCustomName(String customName) {

		Connection conn = DB.getConn();
		try {
			CallableStatement cstmt = conn
					.prepareCall("{call HotelSystem.deleteOrderByCustomName(?)}");
			cstmt.setString(1, customName);
			cstmt.execute();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DB.close(conn);
		}
	}

	public List showOrderByOrderId(int id) {
		Connection conn = DB.getConn();
		ResultSet rs = null;
		List list = null;
		String sql="select o.orderid,c.custom,c.tel,c.email,h.city,h.hotelname housename,o.roomstyle,o.num,o.price from orders o,customs c,hotel h where o.customid=c.customid and h.id=o.id and o.orderid=? UNION select o.orderid,c.custom,c.tel,c.email,r.city,r.resortname housename,o.roomstyle,o.num,o.price from orders o,customs c,resort r where o.customid=c.customid and r.id=o.id and o.orderid=?";
		try {
			PreparedStatement pstmt = conn
					.prepareStatement(sql);
			pstmt.setInt(1, id);
			pstmt.setInt(2, id);
			rs = (ResultSet) pstmt.executeQuery();
			list = ListSwap.toArrayList(rs);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DB.close(conn);
		}
		return list;
	}

	public void deleteOrderByOrderId(int id) {
		Connection conn = DB.getConn();
		try {
			CallableStatement cstmt = conn
					.prepareCall("{call HotelSystem.deleteOrderByOrderId(?)}");
			cstmt.setInt(1, id);
			cstmt.execute();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DB.close(conn);
		}
	}

	public static void main(String[] args) {
		OrdersDAOImpl od = new OrdersDAOImpl();
		List list = od.showOrders();
		if (list == null)
			return;
		Iterator iter = list.iterator();
		while (iter.hasNext()) {
			System.out.println(iter.next());
		}
		// od.insertOrUpdateOrder("�ɼٴ�",22,"����",2,"����","13959186701","[e-mail]");
		// od.insertOrUpdateOrder("�ù�", 9,"�;���",1, "��׳�",
		// "13959187267","[e-mail]");
	}
}