reservationdao.java

一个KTV管理系统

package dao.reservation;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;

import common.dbconnection.DbConnection;

//import common.DbConnection;
//import common.JdbcDemo;
import vo.Reservation;

public class ReservationDao {
  private Connection con = null;
  private Statement sta = null;
  private ResultSet res = null;
  private Vector v = null;
 
  
  public String getGuestName(String roomNumber){
	  v = null;
	    con = null;
	    sta = null;
	    res = null;
	    String temp = null;
	    try {
	        String selectSql = "select * from ktv_reservation_info where room_number = '"+roomNumber+"'";
	        //con = new JdbcDemo().getConnection();
	        con = new DbConnection().getConnection();
	        sta = con.createStatement();
	        res = sta.executeQuery(selectSql);
	        // String temp = null;
	        while (res.next()) {
	          temp = res.getString("GUEST_NAME");
	          // System.out.println(rv);
	        }	        
	      } catch (Exception e) {
	        System.out.println(e.getMessage());
	      } finally {
	        try {
	          con.close();
	        } catch (Exception e1) {
	          System.out.println(e1.getMessage());
	        }
	      }
	      return temp;
  }
  // 取得所有宾客预定信息
  public Vector getAllReservation() {
    v = null;
    con = null;
    sta = null;
    res = null;
    Reservation rv = null;
    try {
      v = new Vector();
      String selectSql = "select * from ktv_reservation_info";
      //con = new JdbcDemo().getConnection();
      con = new DbConnection().getConnection();
      sta = con.createStatement();
      res = sta.executeQuery(selectSql);
      int count = 0;
      // String temp = null;
      while (res.next()) {
        rv = new Reservation();
        rv.setGuestName(res.getString("GUEST_NAME"));
        rv.setGuestTelephone(res.getLong("GUEST_TELEPHONE"));
        rv.setRoomNumber(res.getString("ROOM_NUMBER"));
        rv.setRoomType(res.getString("ROOM_TYPE"));
        rv.setReservationTime(res.getString("RESERVATION_TIME").substring(0, 19));
        rv.setReservationType(res.getString("RESERVATION_TYPE"));
        // System.out.println(rv);
        v.addElement(rv);
        count++;
      }
      if (count == 0) {
        throw new Exception("数据库中不存在这种条件的记录,请你重新输入查询条件");
      }
      
    } catch (Exception e) {
      System.out.println(e.getMessage());
    } finally {
      try {
        con.close();
      } catch (Exception e1) {
        System.out.println(e1.getMessage());
      }
    }
    return v;
  }
  //删除一条预订信息记录
  public boolean deleteReservation(String demo) {
    boolean flag = false;
    String sql = "delete from ktv_reservation_info where ROOM_NUMBER = '"
        + demo + "'";
    try {
      //con = new JdbcDemo().getConnection();
      con = new DbConnection().getConnection();
      sta = con.createStatement();
      res = sta.executeQuery(sql);
      flag = true;
      System.out.println("删除预订信息记录成功");
    } catch (Exception e) {
      System.out.println(e.getMessage());
    } finally {
      try {
        con.close();
      } catch (Exception e1) {
        System.out.println(e1.getMessage());
      }
    }
    return flag;
  }
  //更新一条预订信息记录
  public boolean updateReservation(Reservation demo, String demo2) {
    boolean flag = false;
    try {
      //con = new JdbcDemo().getConnection();
      con = new DbConnection().getConnection();
      con.setAutoCommit(false);
      sta = con.createStatement();
      String sqldelete = "delete from ktv_reservation_info where ROOM_NUMBER = '"
          + demo2 + "'";
      String sqlinsert = "insert into ktv_reservation_info(GUEST_NAME, GUEST_TELEPHONE, ROOM_NUMBER, ROOM_TYPE, RESERVATION_TIME, RESERVATION_TYPE) "
          + "values('"
          + demo.getGuestName()
          + "',"
          + demo.getGuestTelephone()
          + ",'"
          + demo.getRoomNumber()
          + "','"
          + demo.getRoomType()
          + "',to_date('"
          + demo.getReservationTime()
          + "','YYYY-MM-DD HH24:MI:SS'),'" + demo.getReservationType() + "')";
      sta.addBatch(sqldelete);
      sta.addBatch(sqlinsert);
      sta.executeBatch();
      con.commit();
      flag = true;
      System.out.println("更新预订信息记录成功");
    } catch (Exception e) {
      if (flag == false) {
        try {
          con.rollback();
        } catch (Exception e2) {
          System.out.println(e2.getMessage());
        }
      }
    } finally {
      try {
        con.close();
      } catch (Exception e3) {
        System.out.println(e3.getMessage());
      }
    }
    return flag;
  }
  //新增一条预订信息记录
  public boolean insertReservation(Reservation demo) {
    boolean flag = false;
    String sql = "insert into ktv_reservation_info(GUEST_NAME, GUEST_TELEPHONE, ROOM_NUMBER, ROOM_TYPE, RESERVATION_TIME, RESERVATION_TYPE) "
        + "values('"
        + demo.getGuestName()
        + "',"
        + demo.getGuestTelephone()
        + ",'"
        + demo.getRoomNumber()
        + "','"
        + demo.getRoomType()
        + "',to_date('"
        + demo.getReservationTime()
        + "','YYYY-MM-DD HH24:MI:SS'),'" + demo.getReservationType() + "')";
    try {
      //con = new JdbcDemo().getConnection();
      con = new DbConnection().getConnection();
      sta = con.createStatement();
      res = sta.executeQuery(sql);
      flag = true;
      System.out.println("新增预订信息记录成功");
    } catch (Exception e) {
      System.out.println(e.getMessage());
    } finally {
      try {
        con.close();
      } catch (Exception e1) {
        System.out.println(e1.getMessage());
      }
    }
    return flag;
  }
}