roomsettingdao.java

一个KTV管理系统

package dao.roomsetting;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;
import javax.swing.JOptionPane;
import vo.Room;
import common.dbconnection.DbConnection;

public class RoomSettingDao {

	public boolean insertRoomInfo(Room value) {
		boolean flag = false;
		Connection con = null;
		Statement stmt = null;
		ResultSet set = null;
		String sql = "insert into ktv_room_info(ROOM_NUMBER, ROOM_TYPE, ROOM_PRICE,"
				+ "ROOM_NAME,ROOM_STATEMENT,ROOM_CAPACITY,CONSUMED_ID)"
				+ " values('"
				+ value.getRoomNumber()
				+ "','"
				+ value.getRoomType()
				+ "','"
				+ value.getRoomPrice()
				+ "','"
				+ value.getRoomName()
				+ "','"
				+ value.getRoomStatement()
				+ "','"
				+ value.getRoomCapacity()
				+ "','"
				+ value.getConsumedId() + "')";
		System.out.println(sql);
		try {
			con = new DbConnection().getConnection();
			stmt = con.createStatement();
			set = stmt.executeQuery(sql);
			if (set.next()) {
				flag = true;
			}
		} catch (Exception e) {
			JOptionPane.showMessageDialog(null, "增加的房间"
					+ e.getMessage().substring(10), "插入提示",
					JOptionPane.YES_OPTION);
		}
		return flag;
	}

	public boolean deleteRoomInfo(String roomNumber) {
		boolean flag = false;
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet set = null;
		String sql = "delete from ktv_room_info where room_number =?";
		System.out.println(sql);
		try {
			con = new DbConnection().getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, roomNumber);
			stmt.executeUpdate();
			flag = true;
		} catch (Exception e) {
			System.out.println("异常信息: " + e.getMessage());
		}
		return flag;
	}

	// 更新房间信息
	public boolean updateRoomInfo(Room value) {
		boolean flag = false;
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet set = null;
		try {
			con = new DbConnection().getConnection();
			String sql = "update ktv_room_info set ROOM_TYPE='"
					+ value.getRoomType().trim() + "', ROOM_PRICE='"
					+ value.getRoomPrice() + "'," + "ROOM_NAME='"
					+ value.getRoomName() + "',ROOM_STATEMENT='"
					+ value.getRoomStatement() + "',ROOM_CAPACITY='"
					+ value.getRoomCapacity() + "',CONSUMED_ID="
					+ value.getConsumedId() + " where room_number = '"
					+ value.getRoomNumber() + "'";
			System.out.println(sql);
			stmt = con.prepareStatement(sql);
			set = stmt.executeQuery(sql);
			flag = true;
		} catch (Exception e) {
			System.out.println("异常信息: " + e.getMessage());
		}
		return flag;
	}

	// 取得一条房间信息记录
	public Room getARoom(String value) {
		Room room = null;
		Connection con = null;
		Statement stmt = null;
		ResultSet set = null;
		String sql = "select * from KTV_ROOM_INFO where room_number= '"
				+ value.trim() + "'";
		try {
			con = new DbConnection().getConnection();
			stmt = con.createStatement();
			set = stmt.executeQuery(sql);
			while (set.next()) {
				String roomNumber = set.getString(1);
				String roomType = set.getString(2);
				int roomPrice = set.getInt(3);
				String roomName = set.getString(4);
				String roomStatement = set.getString(5);
				int roomCapacity = set.getInt(6);
				long consumedId = set.getLong(7);
				room = new Room(roomNumber, roomType, roomPrice, roomName,
						roomStatement, roomCapacity, consumedId);
			}
		} catch (Exception e) {
			System.out.println("异常信息: " + e.getMessage());
		}
		return room;
	}

	public Vector getRoomStatementInfo(String value) {
		Vector v = null;
		Connection con = null;
		Statement stmt = null;
		ResultSet set = null;
		String sql = "select * from KTV_ROOM_INFO where room_type = '"
				+ value.trim() + "'";
		System.out.println(sql);
		try {
			con = new DbConnection().getConnection();
			stmt = con.createStatement();
			set = stmt.executeQuery(sql);
			v = new Vector();
			while (set.next()) {
				String roomNumber = set.getString(1);
				String roomType = set.getString(2);
				int roomPrice = set.getInt(3);
				String roomName = set.getString(4);
				String roomStatement = set.getString(5);
				int roomCapacity = set.getInt(6);
				long consumedId = set.getLong(7);
				v.addElement(new Room(roomNumber, roomType, roomPrice,
						roomName, roomStatement, roomCapacity, consumedId));
			}
		} catch (Exception e) {
			System.out.println("异常信息: " + e.getMessage());
		}
		return v;
	}

	public Vector getRoomInfo() {
		Vector v = null;
		Connection con = null;
		Statement stmt = null;
		ResultSet set = null;
		String sql = "select * from KTV_ROOM_INFO";
		System.out.println(sql);
		try {
			con = new DbConnection().getConnection();
			stmt = con.createStatement();
			set = stmt.executeQuery(sql);
			v = new Vector();
			while (set.next()) {
				String roomNumber = set.getString(1);
				String roomType = set.getString(2);
				int roomPrice = set.getInt(3);
				String roomName = set.getString(4);
				String roomStatement = set.getString(5);
				int roomCapacity = set.getInt(6);
				long consumedId = set.getLong(7);
				v.addElement(new Room(roomNumber, roomType, roomPrice,
						roomName, roomStatement, roomCapacity, consumedId));
			}
		} catch (Exception e) {
			System.out.println("异常信息: " + e.getMessage());
		}
		return v;
	}

	public Vector findRoomInfo(Room room) {
		Vector v = null;
		Connection con = null;
		Statement stmt = null;
		ResultSet set = null;
		String sql = "select '" + room.getRoomNumber() + "' from ktv_room_info";
		System.out.println(sql);
		try {
			con = new DbConnection().getConnection();
			stmt = con.createStatement();
			set = stmt.executeQuery(sql);
			v = new Vector();
			while (set.next()) {
				String roomNumber = set.getString(1);
				String roomType = set.getString(2);
				int roomPrice = set.getInt(3);
				String roomName = set.getString(4);
				String roomStatement = set.getString(5);
				int roomCapacity = set.getInt(6);
				long consumedId = set.getLong(7);
				v.addElement(new Room(roomNumber, roomType, roomPrice,
						roomName, roomStatement, roomCapacity, consumedId));
			}
		} catch (Exception e) {
			System.out.println("异常信息: " + e.getMessage());
		}
		return v;
	}

	public Vector getAvailableRoomInfo(String roomType) {
		Vector v = null;
		Connection con = null;
		Statement stmt = null;
		ResultSet set = null;
		String sql = "select room_number from KTV_ROOM_INFO where Room_statement = '可用' and Room_type = '"
				+ roomType + "'";
		System.out.println(sql);
		try {
			con = new DbConnection().getConnection();
			stmt = con.createStatement();
			set = stmt.executeQuery(sql);
			v = new Vector();
			while (set.next()) {
				String roomNumber = set.getString(1);
				v.addElement(roomNumber);
			}
		} catch (Exception e) {
			System.out.println("异常信息: " + e.getMessage());
		}
		return v;
	}

	public boolean exchangeRoomInfo(String oldroom, String newroom,
			long consumedId) {
		boolean flag = false;
		Connection con = null;
		Statement stmt0 = null;
		Statement stmt = null;
		Statement stmt1 = null;
		// PreparedStatement stmt2 = null;
		// PreparedStatement stmt3 = null;
		ResultSet set = null;
		ResultSet set0 = null;
		try {
			con = new DbConnection().getConnection();
			String sql1 = "select room_price from ktv_room_info where room_number = '"
					+ newroom + "'";
			System.out.println(sql1);
			stmt = con.createStatement();
			set = stmt.executeQuery(sql1);
			String sql0 = "select * from ktv_guest_consumed_info where consumed_id = "
					+ consumedId;
			System.out.println(sql0);
			stmt0 = con.createStatement();
			set0 = stmt0.executeQuery(sql0);
			double ct = 0;
			while (set0.next()) {
				String consumedtime = set0.getString("consumed_time").trim();
				System.out.println(consumedtime);
				String[] temp = consumedtime.split(":");
				int numberhour = Integer.parseInt(temp[0]);
				int numberminute = Integer.parseInt(temp[1]);
				int numbersecond = Integer.parseInt(temp[2]);
				if (numberminute < 30) {
					if (numberminute == 0) {
						if (numbersecond > 0) {
							ct = (double) (numberhour + 0.5);
						}
					} else {
						ct = (double) (numberhour + 0.5);
					}
				} else {
					ct = (double) (numberhour + 1);
				}
				System.out.println(ct);
				while (set.next()) {
					int roomPrice = Integer.parseInt(set.getString(1).trim());
					System.out.println(consumedId);
					System.out.println(roomPrice);
					con.setAutoCommit(false);
					String sql2 = "update ktv_guest_consumed_info set room_number = '"
							+ newroom
							+ "',room_money = '"
							+ roomPrice
							+ "' * "
							+ ct + " where consumed_id = '" + consumedId + "'";
					String sql3 = "update ktv_room_info set room_statement = '可用',consumed_Id = 0 where room_number = '"
							+ oldroom + "'";
					String sql4 = "update ktv_room_info set room_statement = '占用',consumed_Id = '"
							+ consumedId
							+ "' where room_number = '"
							+ newroom
							+ "'";
					System.out.println(sql2);
					System.out.println(sql3);
					System.out.println(sql4);
					stmt1 = con.createStatement();
					stmt1.addBatch(sql2);
					stmt1.addBatch(sql3);
					stmt1.addBatch(sql4);
					stmt1.executeBatch();
					flag = true;
					con.commit();
				}
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
			try {
				con.rollback();
			} catch (Exception ex) {
				System.out.println(ex.getMessage());
			}
		}
		return flag;
	}

	public boolean updateRoomStatement(String roomNumber, String roomStatement) {
		boolean flag = false;
		Vector v = null;
		Connection con = null;
		Statement stmt = null;
		ResultSet set = null;
		String sql = "update KTV_ROOM_INFO set Room_statement = '"
				+ roomStatement.trim() + "' where room_number = '"
				+ roomNumber.trim() + "'";
		System.out.println(sql);
		try {
			con = new DbConnection().getConnection();
			stmt = con.createStatement();
			set = stmt.executeQuery(sql);
			flag = true;
		} catch (Exception e) {
			System.out.println("异常信息: " + e.getMessage());
		}
		return flag;
	}

	public Vector getRoomPrice(String roomType) {
		Vector v = null;
		Connection con = null;
		Statement stmt = null;
		ResultSet set = null;
		String sql = "select room_PRICE from KTV_ROOM_INFO where room_type = '"
				+ roomType + "'";
		System.out.println(sql);
		try {
			con = new DbConnection().getConnection();
			stmt = con.createStatement();
			set = stmt.executeQuery(sql);
			v = new Vector();
			while (set.next()) {
				String roomPrice = set.getString(1);
				v.addElement(roomPrice);
			}
		} catch (Exception e) {
			System.out.println("异常信息: " + e.getMessage());
		}
		return v;
	}

	public Vector RoomType() {
		return null;
	}

}