roomeraccess.java

Athena酒店小组_Athena酒店管理系统

/*
 * RoomerAccess.java
 *
 * Created on 2007年6月5日, 上午1:39
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package lambert;

import java.sql.*;
import java.util.*;

import plugin.*;
import helper.*;

/**
 *
 * @author Lambert
 */
public class RoomerAccess {
    private IDBResource dbRes;
    
    /** Creates a new instance of EmployeeAccess */
    public RoomerAccess(IDBResource dbRes) {
        this.dbRes = dbRes;
    }
    
    public int insert(RoomerInfo roomerInfo) {
        try {
            Connection conn = dbRes.getConnection();
            String sql = "INSERT INTO RoomerInfo(RoomerBillNum, Account, RoomNum, HouseType, StartTime, EndTime ,Remark) " +
                    " VALUES(?, ?, ?, ?, ?, ?, ?)";
            PreparedStatement ps = conn.prepareStatement(sql);
            
            ps.setString(1, roomerInfo.getRoomerBillNum());
            ps.setString(2, roomerInfo.getAccount());
            ps.setString(3, roomerInfo.getRoomNum());
            ps.setString(4, roomerInfo.getHouseType());
            ps.setObject(5, roomerInfo.getStartTime());
            ps.setObject(6, roomerInfo.getEndTime());
            ps.setString(7, roomerInfo.getRemark());
            
            int rs = ps.executeUpdate();
            return rs;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return -1;
        }
    }
    
    public int update(RoomerInfo roomerInfo) {
        try {
            Connection conn = dbRes.getConnection();
            String sql = "UPDATE RoomerInfo SET Account = ?, RoomNum = ?, HouseType = ?, StartTim = ?, EndTime = ?, Remark = ? " +
                    "WHERE RoomerBillNum = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            
            ps.setString(1, roomerInfo.getRoomerBillNum());
            ps.setString(2, roomerInfo.getAccount());
            ps.setString(3, roomerInfo.getRoomNum());
            ps.setString(4, roomerInfo.getHouseType());
            ps.setObject(5, roomerInfo.getStartTime());
            ps.setObject(6, roomerInfo.getEndTime());
            ps.setString(7, roomerInfo.getRemark());
            
            int rs = ps.executeUpdate();
            return rs;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return -1;
        }
    }
    
    public int delete(RoomerInfo roomerInfo) {
        try {
            Connection conn = dbRes.getConnection();
            String sql = "DELETE FROM RoomerInfo WHERE RoomerBillNum = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            
            ps.setString(1, roomerInfo.getRoomerBillNum());
            
            int rs = ps.executeUpdate();
            return rs;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return -1;
        }
    }
    
    public RoomerInfo getRoomerByRoomerBillNum(String roomerBillNum) {
        try {
            Connection conn = dbRes.getConnection();
            String sql = "SELECT * FROM RoomerInfo WHERE RoomerBillNum = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, roomerBillNum);
            ResultSet rs = ps.executeQuery();
            ArrayList<RoomerInfo> roomers = new ArrayList<RoomerInfo>();
 	    readData(roomers, rs);
            if (roomers.size() != 1) {
                return null;
            }
            return roomers.get(0);
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        }
    }
    
    public ArrayList<RoomerInfo> getAllRoomers() {
        try {
            Connection conn = dbRes.getConnection();
            String sql = "SELECT * FROM RoomerInfo";
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            ArrayList<RoomerInfo> roomers = new ArrayList<RoomerInfo>();
            readData(roomers, rs);
            return roomers;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        }
    } 

    private void readData(ArrayList<RoomerInfo> roomers, ResultSet rs) throws SQLException {
        while(rs.next()) {
                RoomerInfo roomer = new RoomerInfo();
                roomer.setRoomerBillNum(rs.getString("RoomerBillNum"));
                roomer.setAccount(rs.getString("Account"));
                roomer.setRoomNum(rs.getString("RoomNum"));
                roomer.setHouseType(rs.getString("HouseType"));
                roomer.setStartTime(rs.getDate("StartTime"));
                roomer.setEndTime(rs.getDate("EndTime"));
                roomer.setRemark(rs.getString("Remark"));
                roomers.add(roomer);
        }
    }
    
    
    
    
    
    
    
}