忙完了自己的事，继续之前的酒店管理系统。基本每天挤一点时间出来做这个系统。顺便训练自己的基础
在贴代码之前写一件事，中午在测试房间分页的时候，一直显示getConn（）为空，所用的连接池是proxool，搞了好久，一步一步debug才突然想起来，连接池是需要跟服务器挂钩的，直接在本地用java写测试类调用是不行的。。解决的办法就是在servlet里面测试
Hotel的基本就是之前的，开始准备Room的
贴上room的dao实现：
package edu.fjnu.hotelsys.dao.impl;
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.fjnu.hotelsys.dao.HotelDao;
import edu.fjnu.hotelsys.dao.RoomDao;
import edu.fjnu.hotelsys.domain.Hotel;
import edu.fjnu.hotelsys.domain.Room;
import edu.fjnu.hotelsys.exception.DataAccessException;
import edu.fjnu.hotelsys.service.impl.RoomQueryHelper;
import edu.fjnu.hotelsys.utils.DBUtils;
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_LOAD="select * from room order by room_id desc";
private static final String SQL_REMOVE="delete 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 = ?";
private static final String SQL_FINDBYID="select * from room where room_id = ?";
private static final String SQL_LOAD_BASE="select * from room where 1=1";
private HotelDao hotelDao = new HotelDaoImpl();
private Connection conn;
private PreparedStatement pstmt;
private ResultSet rset;
/**
* 添加房间
* @param room
* @author Harry
*/
public void addRoom(Room room) {
conn =DBUtils.getInstance().getConn();
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("|");
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().getHotelId());
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
DBUtils.getInstance().ReleaseRes(conn, pstmt, null);
}
}
/**
* 删除房间
* @param id
* @author Harry
*/
public void removeRoom(Integer id) {
conn = DBUtils.getInstance().getConn();
try {
pstmt = conn.prepareStatement(SQL_REMOVE);
pstmt.setInt(1, id);
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
DBUtils.getInstance().ReleaseRes(conn, pstmt, null);
}
}
/**
* 更新房间
* @param room
* @author Harry
*/
public void updateRoom(Room room){
conn = DBUtils.getInstance().getConn();
try {
pstmt = conn.prepareStatement(SQL_UPDATE);
StringBuffer sb = new StringBuffer();
for(String equip: room.getRoomEquip())
sb.append(equip).append("|");
if(sb.length()>0)
sb.deleteCharAt(sb.length()-1);
//update room set room_no=?,room_type=?,room_equip=?,room_status=?,room_memo=?,hotel_no=? where room_id = ?";
pstmt.setString(1, room.getRoomNo());
pstmt.setString(2, room.getRoomType());
pstmt.setString(3, sb.toString());
pstmt.setString(4, room.getRoomStatus());
pstmt.setString(5, room.getRoomMemo());
pstmt.setInt(6, room.getHotel().getHotelId());
pstmt.setInt(7, room.getRoomId());
pstmt.executeUpdate();
}catch (SQLException e) {
e.printStackTrace();
if(e.getMessage().contains("UK_HOTEL_ROOM")){
HotelDao hotelDao = new HotelDaoImpl();
Hotel hotel = hotelDao.findHotelById(room.getHotel().getHotelId());
throw new DataAccessException(hotel.getHotelName()+"编号为"+room.getRoomNo()+"的客房已经存在，不能允许同编号客房存在!");
}
}finally{
DBUtils.getInstance().ReleaseRes(conn, pstmt, null);
}
}
/**
* 通过Roomid查找房间
* @param id
* @author Harry
*/
public Room findRoomById(Integer id) {
conn = DBUtils.getInstance().getConn();
Room room =null ;
try {
pstmt = conn.prepareStatement(SQL_FINDBYID);
pstmt.setInt(1, id);
rset=pstmt.executeQuery();
room = new Room();
if(rset.next()) {
room.setHotel(hotelDao.findHotelById(rset.getInt("hotel_no")));
room.setRoomEquip(rset.getString("room_equip").split("\\|"));
room.setRoomId(rset.getInt("room_id"));
room.setRoomMemo(rset.getString("room_memo"));
room.setRoomNo(rset.getString("room_no"));
room.setRoomStatus(rset.getString("room_status"));
room.setRoomType(rset.getString("room_type"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally{
DBUtils.getInstance().ReleaseRes(conn, pstmt, rset);
}
return room ;//作用域
}
/**
* 显示所有房间
* @return
* @author Harry
*/
public List<Room> loadAllRoom() {
return this.loadAllRooms(null);
}
/**
* 在有条件的情况显示符合该条件的所有房间
* @param helper
* @return
* @author Harry
*/
public List<Room> loadAllRooms(RoomQueryHelper helper) {
conn = DBUtils.getInstance().getConn();
List<Room> roomList = new ArrayList<Room>();
try {
pstmt = conn.prepareStatement(this.getSQlByCondition(helper));//这个地方需要修改
rset = pstmt.executeQuery();
while(rset.next()){
Room room = new Room();
room.setRoomId(rset.getInt("room_id"));
room.setRoomNo(rset.getString("room_no"));
room.setRoomType(rset.getString("room_type"));
room.setRoomEquip(rset.getString("room_equip").split("\\|"));
room.setRoomStatus(rset.getString("room_status"));
room.setRoomMemo(rset.getString("room_memo"));
Hotel roomHotel = hotelDao.findHotelById(rset.getInt("hotel_no"));
room.setHotel(roomHotel);
roomList.add(room);
}
} catch (SQLException e) {
e.printStackTrace();
} finally{
DBUtils.getInstance().ReleaseRes(conn, pstmt, rset);
}
return roomList;
}
/**
* 根据条件生成不同的sql语句
* @param helper
* @return
* @author Harry
*/
private String getSQlByCondition(RoomQueryHelper helper){
String sql = SQL_LOAD_BASE;
if(helper != null){
if(helper.getHotelNo()!=null)
sql+="and hotel_no = "+helper.getHotelNo();
if(helper.getRoomStatus()!=null)
sql+="and room_status="+helper.getRoomStatus();
if(helper.getRoomType()!=null)
sql+="and room_type =" +helper.getRoomType();
}
sql +=" order by room_id desc ";
System.out.println("the sql is "+sql);
return sql;
}
/**
* 统计符合条件的房间个数
* @param helper
* @return
* @author Harry
*/
public Long countRooms(RoomQueryHelper helper){
Long count = 0L;
String sql = this.getSQlByCondition(helper);//得到条件sql
sql.replaceAll("\\*", "count(*) total ");//select count(*) total from room where 1=1
sql.substring(0, sql.indexOf("order by"));//删除order by 后的东西（包括order by）
conn = DBUtils.getInstance().getConn();
System.out.println(conn);
try {
pstmt = conn.prepareStatement(sql);
rset = pstmt.executeQuery();
if(rset.next()){
count = rset.getLong("total");
}
} catch (SQLException e) {
e.printStackTrace();
} finally{
DBUtils.getInstance().ReleaseRes(conn, pstmt, rset);
}
return count;
}
/**
* 分页查找数据
* @param helper
* @param startIndex
* @param endIndex
* @return
*/
public List<Room> loadScopedRooms(RoomQueryHelper helper, int startIndex,int endIndex) {
String qrtsql = this.getSQlByCondition(helper);
conn = DBUtils.getInstance().getConn();
System.out.println("所得到的conn 为"+conn);
List<Room> roomList = new ArrayList<Room> () ;
String sql = "select * from room limit ?, ? ";
//where ("+qrtsql+")
try {
System.out.println(sql);
System.out.println(conn.prepareStatement(sql));
pstmt =conn.prepareStatement(sql);
pstmt.setInt(1, startIndex);
pstmt.setInt(2, endIndex);
rset = pstmt.executeQuery();
if(rset.next()){
Room room = new Room();
room.setRoomId(rset.getInt("room_id"));
room.setRoomNo(rset.getString("room_no"));
room.setRoomType(rset.getString("room_type"));
room.setRoomEquip(rset.getString("room_equip").split("\\|"));
room.setRoomStatus(rset.getString("room_status"));
room.setRoomMemo(rset.getString("room_memo"));
Hotel roomHotel = hotelDao.findHotelById(rset.getInt("hotel_no"));
room.setHotel(roomHotel);
roomList.add(room);
}
} catch (SQLException e) {
e.printStackTrace();
} finally{
DBUtils.getInstance().ReleaseRes(conn, pstmt, rset);
}
return roomList ;
}
}
再贴上一个新建room的界面
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title></title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<link rel="stylesheet" type="text/css" href="<c:url value="/css/application.css"></c:url>">
<link rel="stylesheet" type="text/css" href="<c:url value="/css/form.css"></c:url>">
</head>
<body>
<div id="wrapper">
<div id="f_title">酒店房间登记</div>
<form action="<c:url value="/RoomMgrServlet"/>" method="post">
<input type="hidden" name="act" value="createRoom"/>
<div class="f_row">
<span>房间编号:</span>
<input type="text" name="room_no" size="20"/>
</div>
<div class="f_row">
<span>所属分店:</span>
<select name="hotel_id">
<option value="0">==请选择==</option>
<c:forEach var="hotel" items="${hotelList}">
<option value="${hotel.hotelId}">${hotel.hotelName}</option>
</c:forEach>
</select>
</div>
<div class="f_row">
<span>房间类型:</span>
<input type="radio" name="room_type" value="a"/> 普单人间
<input type="radio" name="room_type" value="b"/> 普双人间
<input type="radio" name="room_type" value="c"/> 三人间
<input type="radio" name="room_type" value="d"/> 商务套房
<input type="radio" name="room_type" value="e"/> 贵宾套房
</div>
<div class="f_row">
<span>房间设施:</span>
<input type="checkbox" name="room_equip" value="a" />平面液晶电视
<input type="checkbox" name="room_equip" value="b" checked/>冰箱
<input type="checkbox" name="room_equip" value="c" checked/>空调
<input type="checkbox" name="room_equip" value="d"/>卫星电视
<input type="checkbox" name="room_equip" value="e" checked/>互联网接入
<input type="checkbox" name="room_equip" value="f"/>冲浪浴缸
<input type="checkbox" name="room_equip" value="g"/>观海景
</div>
<div class="f_row">
<span>房间状态:</span>
<select name="room_status">
<option value="a"> 未入住</option>
<option value="b"> 有住客</option>
<option value="c"> 已预订</option>
<option value="d"> 保洁中</option>
<option value="e"> 已退房未保洁</option>
<option value="f"> 维护中 </option>
</select>
</div>
<div class="f_row">
<span>备注说明:</span>
<textarea rows="8" cols="60" name="room_memo">${param.room_memo}</textarea>
</div>
<div class="f_row">
<input type="submit" value="保存信息"/>
</div>
</form>
</div>
</body>
</html>
大致这些，还写了一个添加房间的Servlet实现，也放上来
/**
* 跳转到创建房间的界面及之前的一些准备
* @param request
* @param response
* @author Harry
* @throws IOException
* @throws ServletException
*/
private void toInput(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{
request.setAttribute("hotelList", hotelService.loadAllHotel());
request.getRequestDispatcher("/jsps/room/input_room.jsp").forward(request, response);
}
/**
* 创建分店的房间
* @param request
* @param response
* @author Harry
*/
private void createRoom(HttpServletRequest request, HttpServletResponse response){
Room room = new Room();
String roomNo = request.getParameter("room_no");
Integer hotelId = Integer.parseInt(request.getParameter("hotel_id"));
String roomType = request.getParameter("room_type");
String[] roomEquip = request.getParameterValues("room_equip");
String roomStatus =request.getParameter("room_status");
String roomMemo = request.getParameter("room_memo");
room.setRoomEquip(roomEquip);
room.setRoomMemo(roomMemo);
room.setRoomNo(roomNo);
room.setRoomStatus(roomStatus);
room.setRoomType(roomType);
room.setHotel(hotelService.findHotelById(hotelId));
roomService.addRoom(room);
}