(小项目)校园课室借用系统 Java Swing + SQL server(3)课室查询功能的实现

导读:本篇文章讲解 (小项目)校园课室借用系统 Java Swing + SQL server(3)课室查询功能的实现,文章出自:https://blog.csdn.net/weixin_43316702/article/details/100671227希望对大家有帮助,欢迎收藏,转发!站点地址:www.javazhiyin.com.com

>>强大,10k+点赞的 SpringBoot 后台管理系统竟然出了详细教程!

功能介绍

课室查询功能,是用于查询某一时间、某一栋教学楼空闲的教室,查询到的教室有教室编号和教室大小,教室位置等信息,通过这些信息可以选择是否借用教室。

功能实现原理

通过选择多个信息进行数据库的数据匹配,然后筛选出所需要的数据,例如教室是否有课,有人借用,若有,则是不能显示在表格中的,显示在表格中的教室都是可借用教室。

课室查询功能

/*
	 * 查询指定教学楼、指定人数、指定星期节次没课时的数据
	 */
	public List<ClassData> queryClass(String lou,String scale,int week,String day,String node){
		String sql = "select CRno,CRscale,CRlocation from RoomData where CRno NOT in (SELECT ClassData.CRno FROM RoomData,ClassData where RoomData.CRno=ClassData.CRno and CRlocation like '"+lou+"%' and CRscale = "+scale+" and CweekF<="+week+" and CweekL>="+week+" and Cday like '__"+day+"' and Cnode like '"+node+"') and CRscale = "+scale+" and CRlocation like '"+lou+"%' and CRno not in (select classNo from BCRegister where classNo in (SELECT classNo from RoomData,BCRegister where RoomData.CRno=BCRegister.classNo and CRlocation like '"+lou+"%' and CRscale = "+scale+" and BCweek="+week+" and BCday like '"+day+"' and BCnode like '"+node+"') and BCweek = "+week+" and BCday like '"+day+"' and BCnode like '"+node+"')";
		List<ClassData> list = new ArrayList<ClassData>();
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		ClassData user=null;
		try {
			//通过帮助类获取数据库链接对象
			con=DBUtils.getConnection();
			pst =con.prepareStatement(sql);
			rs =pst.executeQuery();
			//遍历结果集
			while(rs.next()){
				user =new ClassData();
				user.setCRno(rs.getString(1));
				user.setCRscale(rs.getInt(2));
				user.setCRlocation(rs.getString(3));
				/*user.setCweek(rs.getString(4));
				user.setCday(rs.getString(5));
				user.setCnode(rs.getString(6));
				*/
				list.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

查询功能界面:
通过教学楼,人数,时间来确定能借用的教室
在这里插入图片描述

总结

在该部分功能的实现时,遇到的问题有两个。
第一:字符串后面有空格,导致无法匹配成功,在登陆注册功能时也会有这样的情况,解 决办法是要去掉后面的空格,方法如下:

/*
	 * 去掉字符串末尾的空格方法
	 */
	public static String rtrim(String value){   
		if(value==null) 
			return null;   
		return value.replaceAll("\\s+$",""); 
	}

第二:筛选逻辑有问题,我们需要是某时间,某容量的教室没有课的教室,所以匹配到某时间,某容量有课的教室都是不能显示的,在这个问题上逻辑要理顺,要层层递进。

"select CRno,CRscale,CRlocation from RoomData 
where CRno NOT in (SELECT ClassData.CRno FROM RoomData,ClassData 
where RoomData.CRno=ClassData.CRno and CRlocation like '"+lou+"%' and CRscale = "+scale+" and CweekF<="+week+" and CweekL>="+week+" and Cday like '__"+day+"' and Cnode like '"+node+"') 
and CRscale = "+scale+" and CRlocation like '"+lou+"%' 
and CRno not in (select classNo from BCRegister 
where classNo in (SELECT classNo from RoomData,BCRegister 
where RoomData.CRno=BCRegister.classNo and CRlocation like '"+lou+"%' and CRscale = "+scale+" and BCweek="+week+" and BCday like '"+day+"' and BCnode like '"+node+"') and BCweek = "+week+" and BCday like '"+day+"' and BCnode like '"+node+"')"

需要程序源码请查看本人资源