dbcon.java

一个酒店房间登记系统

package db;

import java.sql.*;

import javax.swing.*;

/**
 * <p>
 * Title:
 * </p>
 * 
 * <p>
 * Description:
 * </p>
 * 
 * <p>
 * Copyright: Copyright (c) 2007
 * </p>
 * 
 * <p>
 * Company:
 * </p>
 * 
 * @author not attributable
 * @version 1.0
 */
public class DBCon {
	private String dbUrl;

	private String user;

	private String password;

	private Connection con;

	private Statement statement;

	public DBCon() {

	}

	public void connect(String dbUrl) {
		this.dbUrl = dbUrl;
	}

	public void connect(String dbUrl, String user, String password) {
		this.dbUrl = dbUrl;
		this.user = user;
		this.password = password;
	}

	public Connection getConnection() {
		connect("jdbc:odbc:restaurant");
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			return DriverManager.getConnection(dbUrl);
		} catch (Exception e) {
			JOptionPane.showMessageDialog(null, "can't connect database", null,
					1);
		}
		return null;
	}

	public ResultSet excuteQuery(String sql) {

		ResultSet rs = null;
		try {
			con = getConnection();
			statement = con.createStatement();
			rs = statement.executeQuery(sql);
			return rs;
		} catch (Exception e) {

		}
		return null;
	}

	public int excuteUpdate(String sql) {
		try {
			con = getConnection();
			statement = con.createStatement();
			return statement.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		}
	}

	public void close() {
		try {
			statement.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public int roomclassnum(String roomclass){
		String sql="select count(*) from room,roomclass where class='"+roomclass+"' and room.classno=roomclass.classno;";
		ResultSet rs=null;
		int num=0;
		try{
			rs=excuteQuery(sql);
			if(rs.next()){
				num=rs.getInt(1);
				rs.close();
				close();
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return num;
	}
	
	
	public String roomclass(String roomno){
		String sql = "select class from roomclass,room where roomno='" + roomno + "' and room.classno=roomclass.classno;";
		ResultSet rs = null;
		String roomclass = null;
		try {
			rs = excuteQuery(sql);
			if (rs.next()) {
				roomclass = rs.getString(1);
				rs.close();
				close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return roomclass;
		
	}
	
	public int emptyroomnum(String roomclass){
		String sql="select count(roomno) from room,roomclass,roomstate where room.roomcode=roomstate.roomcode" +
				" and room.classno=roomclass.classno and roomclass.class='" +roomclass+"'" +
						"and roomstate.roomstate='r'";
		ResultSet rs=null;
		int num=0;
		try{
			rs=excuteQuery(sql);
			if(rs.next()){
				num=rs.getInt(1);
				rs.close();
				close();
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return num;
				
	}
	public int beds(String roomno) {
		String sql = "select beds from room,roomclass where roomclass.classno=room.classno and roomno='"
				+ roomno + "';";
		ResultSet rs = null;
		int num = 0;
		try {
			rs = excuteQuery(sql);
			if (rs.next()) {
				num = rs.getInt(1);
				rs.close();
				close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return num;
	}

	public int guestnum(String roomno) {
		String sql = "select count(*) from guests,room,roomclass where room.roomno='"
				+ roomno
				+ "' and room.roomcode=guests.roomcode and room.classno=roomclass.classno";
		ResultSet rs = null;
		int num = 0;
		try {
			rs = excuteQuery(sql);
			if (rs.next()) {
				num = rs.getInt(1);
				rs.close();
				close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return num;
	}

	public boolean hasEmptyBed(String roomno) {

		int num = beds(roomno) - guestnum(roomno);
		if (num == 0) {
			return false;
		}
		return true;

	}

	public String roomcode(String roomno) {
		String sql = "select roomcode from room where roomno='" + roomno + "';";
		ResultSet rs = null;
		String roomcode = null;
		try {
			rs = excuteQuery(sql);
			if (rs.next()) {
				roomcode = rs.getString(1);
				rs.close();
				close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return roomcode;
	}
	
	public String roomno(String roomcode){
		String sql = "select roomno from room where roomcode='" + roomcode + "';";
		ResultSet rs = null;
		String roomno = null;
		try {
			rs = excuteQuery(sql);
			if (rs.next()) {
				roomno = rs.getString(1);
				rs.close();
				close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return roomno;
	
	}
	
	public String roomstate(String roomno){
		String sql="select roomstate from room,roomstate where room.roomno='"+
		roomno+"' and room.roomcode=roomstate.roomcode;";
		ResultSet rs=null;
		String state=null;
		try{
			rs=excuteQuery(sql);
			if(rs.next()){
				state=rs.getString(1);
				rs.close();
				close();
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return state;
	}

	public static void main(String a[]) {
		DBCon db = new DBCon();
		System.out.println(db.roomclass("R100"));
		System.out.println(db.emptyroomnum("豪华套"));
		System.out.println(db.roomstate("R100"));

	}
}