// 根据id查询 public TRegister searchByID(int id) throws Exception { Connection dbConn = DataBaseTool.getConnection(); String sql = "select * from t_register where id=" + id; ResultSet rs = DataBaseTool.executeQuery(dbConn, sql); TRegister register = null; while (rs.next()) { register = new TRegister(); setOne(register, rs); } DataBaseTool.close(rs, rs.getStatement(), dbConn); return register; }
// 查询所有 public ArrayList<TRegister> searchAll() throws Exception { Connection dbConn = DataBaseTool.getConnection(); String sql = "select * from t_register"; ResultSet rs = DataBaseTool.executeQuery(dbConn, sql); ArrayList<TRegister> list = new ArrayList<TRegister>(); TRegister register = null; while (rs.next()) { register = new TRegister(); setOne(register, rs); list.add(register); } DataBaseTool.close(rs, rs.getStatement(), dbConn); return list; }
// 查询 top number public ArrayList<TTableRegister> searchGuahao() throws Exception { Connection dbConn = DataBaseTool.getConnection(); String sql = "select top 20 a.date as date,d.name as doctorName,room.name as roomName,a.expense as expense,r.id as registerId,r.arrangement_id as arrangementId,r.user_id as userId,r.type as type,r.number as number from t_arrangement as a,t_register as r,t_doctor as d,t_room as room " + " where a.id=r.arrangement_id and a.doctor_id=d.id and a.room_id=room.id and r.type=" + TRegister.TYPE_GUAHAO + " order by date desc"; ResultSet rs = DataBaseTool.executeQuery(dbConn, sql); ArrayList<TTableRegister> list = new ArrayList<TTableRegister>(); TTableRegister item = null; while (rs.next()) { item = new TTableRegister(); setOneTableRegister(item, rs); list.add(item); } DataBaseTool.close(rs, rs.getStatement(), dbConn); return list; }
// 查询预约 public ArrayList<TTableRegister> searchYuyueByCardnum(String cardnum) throws Exception { Connection dbConn = DataBaseTool.getConnection(); String sql = "select a.date as date,d.name as doctorName,room.name as roomName,a.expense as expense,r.id as registerId,r.arrangement_id as arrangementId,r.user_id as userId,r.type as type,r.number as number from t_arrangement as a,t_register as r,t_doctor as d,t_room as room,t_user as u " + "where a.id=r.arrangement_id and a.doctor_id=d.id and a.room_id=room.id and r.user_id=u.id and DATEDIFF(DAY,a.date,GETDATE())=0 and u.cardnum='" + cardnum + "' and r.type=" + TRegister.TYPE_YUYUE; ResultSet rs = DataBaseTool.executeQuery(dbConn, sql); ArrayList<TTableRegister> list = new ArrayList<TTableRegister>(); TTableRegister item = null; while (rs.next()) { item = new TTableRegister(); setOneTableRegister(item, rs); list.add(item); } DataBaseTool.close(rs, rs.getStatement(), dbConn); return list; }
// 查询预约 public ArrayList<TTableRegister> search(int userid, boolean isAll) throws Exception { Connection dbConn = DataBaseTool.getConnection(); StringBuffer sql = new StringBuffer("select "); if (!isAll) { sql.append(" top 3 "); } sql.append( " a.date as date,d.name as doctorName,room.name as roomName,a.expense as expense,r.id as registerId,r.arrangement_id as arrangementId,r.user_id as userId,r.type as type,r.number as number from t_arrangement as a,t_register as r,t_doctor as d,t_room as room " + " where r.user_id=" + userid + " and a.id=r.arrangement_id and a.doctor_id=d.id and a.room_id=room.id and r.type=" + TRegister.TYPE_YUYUE + " order by date desc"); ResultSet rs = DataBaseTool.executeQuery(dbConn, sql.toString()); ArrayList<TTableRegister> list = new ArrayList<TTableRegister>(); TTableRegister item = null; while (rs.next()) { item = new TTableRegister(); setOneTableRegister(item, rs); list.add(item); } DataBaseTool.close(rs, rs.getStatement(), dbConn); return list; }