毕业设计-酒店管理系统

/** * */ package edu.fzu.hotelsys.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import edu.fzu.hotelsys.domain.Hotel; import edu.fzu.hotelsys.domain.Room; import edu.fzu.hotelsys.exception.DataAccessException; import edu.fzu.hotelsys.service.RoomQueryHelper; import edu.fzu.hotelsys.utils.DBUtils; /** * @author Administrator *create table room( room_id int primary key auto_increment, room_no varchar(10) not null, room_type char(1) not null, room_equip varchar(20) not null, room_status char(1) not null, room_memo varchar(200), hotel_no int not null, constraint FK_ROOM_HOTEL FOREIGN KEY (hotel_no) references hotel(hotel_no), constraint UK_HOTEL_ROOM UNIQUE(room_no,hotel_no) ); */ public class RoomDaoImpl implements RoomDao { private static final String SQL_ADD="insert into room(room_no,room_type,room_equip,room_status,room_memo,hotel_no) values(?,?,?,?,?,?)"; private static final String SQL_LOADALL="select * from room order by room_id desc"; private static final String SQL_RENOVE="delete from room where room_id=?"; private static final String SQL_LOADBYID="select * from room where room_id=?"; private static final String SQL_UPDATE="update room set room_no=?,room_type=?,room_equip=?,room_status=?,room_memo=?,hotel_no=? where room_id=?"; /* (non-Javadoc) * @see edu.fzu.hotelsys.dao.RoomDao#add(edu.fzu.hotelsys.domain.Room) */ public void add(Room room) { Connection conn = DBUtils.getInstance().getConn(); PreparedStatement pstmt = null; try { pstmt = conn.prepareStatement(SQL_ADD); pstmt.setString(1, room.getRoomNo()); pstmt.setString(2, room.getRoomType()); StringBuffer sb=new StringBuffer(); for(String equip:room.getRoomEquip()) sb.append(equip).append("|"); //{a,b,c} => a|b|c| if(sb.length()>0) sb.deleteCharAt(sb.length()-1); pstmt.setString(3, sb.toString()); pstmt.setString(4, room.getRoomStatus()); pstmt.setString(5, room.getRoomMemo()); pstmt.setInt(6, room.getHotel().getHotelNo()); pstmt.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { DBUtils.getInstance().releaseRes(conn, pstmt, null); } } /* (non-Javadoc) * @see edu.fzu.hotelsys.dao.RoomDao#loadall() */ public List<Room> loadRooms(RoomQueryHelper helper) { Connection conn = DBUtils.getInstance().getConn(); PreparedStatement pstmt = null; ResultSet rs = null; List<Room> roomList = null; try { pstmt = conn.prepareStatement(this.buildSqlByHelper(helper)); rs = pstmt.executeQuery(); roomList = new ArrayList<Room>(); while (rs.next()) { Room room = new Room(); room.setRoomId(rs.getInt("room_id")); room.setRoomNo(rs.getString("room_no")); room.setRoomType(rs.getString("room_type")); room.setRoomEquip(rs.getString("room_equip").split("\\|")); room.setRoomStatus(rs.getString("room_status")); room.setRoomMemo(rs.getString("room_memo")); HotelDao hotelDao=new HotelDaoJDBCImpl(); room.setHotel(hotelDao.getHotelByNo(rs.getInt("hotel_no"))); roomList.add(room); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { DBUtils.getInstance().releaseRes(conn, pstmt, rs); } return roomList; } public void del(Integer roomId) { Connection conn = DBUtils.getInstance().getConn(); PreparedStatement pstmt = null; try { pstmt = conn.prepareStatement(SQL_RENOVE); pstmt.setInt(1, roomId); pstmt.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { DBUtils.getInstance().releaseRes(conn, pstmt, null); } } public Room getRoomById(Integer roomId) { Connection conn = DBUtils.getInstance().getConn(); PreparedStatement pstmt = null; ResultSet rs = null; Room room= null; try { pstmt = conn.prepareStatement(SQL_LOADBYID); pstmt.setInt(1,roomId); rs = pstmt.executeQuery(); if (rs.next()) { room = new Room(); room.setRoomId(rs.getInt("room_id")); room.setRoomNo(rs.getString("room_no")); room.setRoomType(rs.getString("room_type")); room.setRoomEquip(rs.getString("room_equip").split("\\|")); room.setRoomStatus(rs.getString("room_status")); room.setRoomMemo(rs.getString("room_memo")); HotelDao hotelDao=new HotelDaoJDBCImpl(); room.setHotel(hotelDao.getHotelByNo(rs.getInt("hotel_no"))); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { DBUtils.getInstance().releaseRes(conn, pstmt, rs); } return room; } public void update(Room room) { Connection conn = DBUtils.getInstance().getConn(); PreparedStatement pstmt = null; try { pstmt = conn.prepareStatement(SQL_UPDATE); pstmt.setString(1, room.getRoomNo()); pstmt.setString(2, room.getRoomType()); StringBuffer sb=new StringBuffer(); for(String equip:room.getRoomEquip()) sb.append(equip).append("|"); //{a,b,c} => a|b|c| if(sb.length()>0) sb.deleteCharAt(sb.length()-1); pstmt.setString(3, sb.toString()); pstmt.setString(4, room.getRoomStatus()); pstmt.setString(5, room.getRoomMemo()); pstmt.setInt(6, room.getHotel().getHotelNo()); pstmt.setInt(7, room.getRoomId()); pstmt.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); if(e.getMessage().contains("UK_HOTEL_ROOM")){ HotelDao hotelDao=new HotelDaoJDBCImpl(); Hotel hotel=hotelDao.getHotelByNo(room.getHotel().getHotelNo()); throw new DataAccessException(hotel.getHotelName()+room.getRoomNo()+"房间已经存在，无法执行修改!"); } } finally { DBUtils.getInstance().releaseRes(conn, pstmt, null); } } private String buildSqlByHelper(RoomQueryHelper helper){ String sql="select * from room where 1=1 "; if(helper.getHotelNo()!=null){ sql+=" and hotel_no="+helper.getHotelNo(); } if(helper.getRoomType()!=null){ sql+=" and room_type='"+helper.getRoomType()+"'"; } if(helper.getRoomStatus()!=null){ sql+=" and room_status='"+helper.getRoomStatus()+"'"; } sql+=" order by room_id desc"; return sql; } public Long countRooms(RoomQueryHelper helper) { String sql=this.buildSqlByHelper(helper); sql=sql.replace("*","count(*) as total"); sql=sql.substring(0, sql.lastIndexOf("order")); System.out.println("sql-count:"+sql); Connection conn = DBUtils.getInstance().getConn(); PreparedStatement pstmt = null; ResultSet rs = null; long count=0L; try { pstmt = conn.prepareStatement(sql); rs=pstmt.executeQuery(); if(rs.next()){ count=rs.getLong("total"); } } catch (SQLException e) { e.printStackTrace(); }finally{ DBUtils.getInstance().releaseRes(conn, pstmt, rs); } return count; } public List<Room> loadScopedRooms(RoomQueryHelper helper, int startIndex,int fetchSize) { String sql=this.buildSqlByHelper(helper); sql+=" limit ?,?"; System.out.println(sql); Connection conn = DBUtils.getInstance().getConn(); PreparedStatement pstmt = null; ResultSet rs = null; List<Room> roomList = null; try { pstmt = conn.prepareStatement(sql); pstmt.setInt(1,startIndex-1); pstmt.setInt(2,fetchSize); rs = pstmt.executeQuery(); roomList = new ArrayList<Room>(); while (rs.next()) { Room room = new Room(); room.setRoomId(rs.getInt("room_id")); room.setRoomNo(rs.getString("room_no")); room.setRoomType(rs.getString("room_type")); room.setRoomEquip(rs.getString("room_equip").split("\\|"));