社区医院信息管理系统的设计

package com.cxd.hospital.dao.imp; 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 com.cxd.hospital.dao.BuildDao; import com.cxd.hospital.db.DB; import com.cxd.hospital.jopo.Bed; import com.cxd.hospital.jopo.Building; import com.cxd.hospital.jopo.Floor; import com.cxd.hospital.jopo.Room; import com.cxd.hospital.jopo.Sicker; public class BuildDaoImp implements BuildDao { private Connection conn; private PreparedStatement pstmt; private ResultSet rs; public List<Building> getAllBuilding() { List<Building> list = new ArrayList<Building>(); conn = DB.getConnection(); String sql = "select id,name from building"; try { pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); while(rs.next()){ Building b = new Building(); b.setId(rs.getInt(1)); b.setName(rs.getString(2)); list.add(b); } } catch (SQLException e) { e.printStackTrace(); }finally{ DB.close(rs, pstmt, conn); } return list; } public List<Floor> getFloorByBuildingId(int id) { List<Floor> list = new ArrayList<Floor>(); conn = DB.getConnection(); String sql = "select id,name from floors where building_id=?"; try { pstmt = conn.prepareStatement(sql); pstmt.setInt(1, id); rs = pstmt.executeQuery(); while(rs.next()){ Floor f = new Floor(); f.setId(rs.getInt(1)); f.setName(rs.getString(2)); list.add(f); } } catch (SQLException e) { e.printStackTrace(); }finally{ DB.close(rs, pstmt, conn); } return list; } public List<Room> getRoomByFloorId(int id) { List<Room> list = new ArrayList<Room>(); conn = DB.getConnection(); String sql = "select id,name from room where f_id=?"; try { pstmt = conn.prepareStatement(sql); pstmt.setInt(1, id); rs = pstmt.executeQuery(); while(rs.next()){ Room r = new Room(); r.setId(rs.getInt(1)); r.setName(rs.getString(2)); list.add(r); } } catch (SQLException e) { e.printStackTrace(); }finally{ DB.close(rs, pstmt, conn); } return list; } public List<Bed> getBedByRoomId(int id) { List<Bed> list = new ArrayList<Bed>(); conn = DB.getConnection(); String sql = "select b.id,b.name,b.price,b.descr,b.stat,s.id,s.name,s.age,s.sex,s.workplace from bed b,sicker s where s.id=b.s_id and r_id=?"; try { pstmt = conn.prepareStatement(sql); pstmt.setInt(1, id); rs = pstmt.executeQuery(); while(rs.next()){ Bed bed = new Bed(); Sicker sicker = new Sicker(); bed.setSicker(sicker); bed.setId(rs.getInt(1)); bed.setName(rs.getString(2)); bed.setPrice(rs.getFloat(3)); bed.setDesc(rs.getString(4)); bed.setState(rs.getInt(5)); sicker.setId(rs.getInt(6)); sicker.setName(rs.getString(7)); sicker.setAge(rs.getInt(8)); sicker.setSex(rs.getInt(9)); sicker.setWorkPlace(rs.getString(10)); list.add(bed); } sql = "select id,name,price,descr,stat from bed where r_id=? and s_id is null"; pstmt = conn.prepareStatement(sql); pstmt.setInt(1, id); rs = pstmt.executeQuery(); while(rs.next()){ Bed bed = new Bed(); Sicker sicker = new Sicker(); bed.setSicker(sicker); bed.setId(rs.getInt(1)); bed.setName(rs.getString(2)); bed.setPrice(rs.getFloat(3)); bed.setDesc(rs.getString(4)); bed.setState(rs.getInt(5)); list.add(bed); } } catch (SQLException e) { e.printStackTrace(); }finally{ DB.close(rs, pstmt, conn); } return list; } public void addRoom(String name, int floor) { conn = DB.getConnection(); String sql = "insert into room(name,f_id) values(?,?)"; try { pstmt = conn.prepareStatement(sql); pstmt.setString(1, name); pstmt.setInt(2, floor); pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally{ DB.close(rs, pstmt, conn); } } public void addFloor(String name, int building) { conn = DB.getConnection(); String sql = "insert into floors(name,building_id) values(?,?)"; try { pstmt = conn.prepareStatement(sql); pstmt.setString(1, name); pstmt.setInt(2, building); pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally{ DB.close(rs, pstmt, conn); } } public void addBuilding(String name) { conn = DB.getConnection(); String sql = "insert into building(name) values(?)"; try { pstmt = conn.prepareStatement(sql); pstmt.setString(1, name); pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally{ DB.close(rs, pstmt, conn); } } public void deleteBuilding(int id) { conn = DB.getConnection(); String sql = "delete from building where id=?"; try { pstmt = conn.prepareStatement(sql); pstmt.setInt(1, id); pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally{ DB.close(rs, pstmt, conn); } } public void deleteFloor(int id) { conn = DB.getConnection(); String sql = "delete from floors where id=?"; try { pstmt = conn.prepareStatement(sql); pstmt.setInt(1, id); pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally{ DB.close(rs, pstmt, conn); } } public void deleteRoom(int id) { conn = DB.getConnection(); String sql = "delete from room where id=?"; try { pstmt = conn.prepareStatement(sql); pstmt.setInt(1, id); pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally{ DB.close(rs, pstmt, conn); } } public void deleteBed(int id) { conn = DB.getConnection(); String sql = "delete from bed where id=?"; try { pstmt = conn.prepareStatement(sql); pstmt.setInt(1, id); pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally{ DB.close(rs, pstmt, conn); } } public void updateBuilding(int id, String name) { conn = DB.getConnection(); String sql = "update building set name=? where id=?"; try { pstmt = conn.prepareStatement(sql); pstmt.setString(1, name); pstmt.setInt(2, id); pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally{ DB.close(rs, pstmt, conn); } } public void updateFloor(int id, String name) { conn = DB.getConnection(); String sql = "update floors set name=? where id=?"; try { pstmt = conn.prepareStatement(sql); pstmt.setString(1, name); pstmt.setInt(2, id); pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally{ DB.close(rs, pstmt, conn); } } public void updateRoom(int id, String name) { conn = DB.getConnection(); String sql = "update room set name=? where id=?"; try { pstmt = conn.prepareStatement(sql); pstmt.setString(1, name); pstmt.setInt(2, id); pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally{ DB.close(rs, pstmt, conn); } } public void updateBed(int id, String name, float price) { conn = DB.getConnection(); String sql = "update bed set name=?,price=? where id=?"; try { pstmt = conn.prepareStatement(sql); pstmt.setString(1, name); pstmt.setFloat(2, price); pstmt.setInt(3, id); pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally{ DB.close(rs, pstmt, conn); } } }