多表连接处理查询结果.docx

要查询的数据存在在多个表中，需要使用这些数据，有三种方式获取（一对多）需要在一个实体类中封装另一个实体类的实体变量，（eg:list）一 通过表连接 一次性查询所有数据 a) 通过list集合获取 public List queryTeacher() /查询教师的信息Connection conn = JdbcUtilConf.getConnection();PreparedStatement pstm = null;ResultSet rs = null;String sql = select t1.user_id,t1.username,t1.password,t1.realname,t1.department,t1.role,c.course_id,c.course_name from (select * from t_user u inner join course_user cu on u.user_id = cu.u_id) t1 inner join t_course c on t1.c_id=c.course_id;List listcourse = null;List listuser = new ArrayList();User user = null;try pstm = conn.prepareStatement(sql);rs = pstm.executeQuery();Course c = null;while(rs.next()Integer cid = rs.getInt(course_id);String course = rs.getString(course_name);c = new Course(cid,course);if(!listuser.isEmpty()for(int i =0;ilistuser.size();i+)user = listuser.get(i);if(user.getUserId()=rs.getInt(user_id)for(int j =0;juser.getListcourse().size();j+)if(user.getListcourse().get(j).getCourseId()!=cid&j=user.getListcourse().size()-1)user.getListcourse().add(c);elseif(i=listuser.size()-1)listcourse = new ArrayList();listcourse.add(c);user = new User(rs.getInt(user_id),rs.getString(username),rs.getString(password),rs.getString(realname),rs.getString(department),rs.getString(role),listcourse);listuser.add(user);elselistcourse = new ArrayList();listcourse.add(c);user = new User(rs.getInt(user_id), rs.getString(username),rs.getString(password),rs.getString(realname),rs.getString(department),rs.getString(role),listcourse);listuser.add(user); catch (SQLException e) System.out.println(异常位置和信息为 +e.getMessage();e.printStackTrace();finallyJdbcUtilConf.release(rs, pstm, conn);return listuser;b) 通过map集合获取 public List findALLCourseAndTeacher() throws Exception Connection conn = null;PreparedStatement pstm = null;ResultSet rs = null;List list = new ArrayList();Map map = new HashMap();try conn=JdbcUtil.getConn();String sql=select * from t_user u join course_user cu on u.user_id = cu.u_id join t_course c on c.course_id = cu.c_id;pstm=conn.prepareStatement(sql);rs = pstm.executeQuery();while(rs.next()if(map.containsKey(rs.getString(username)Course c = new Course();c.setCourseName(rs.getString(course_name);map.get(rs.getString(username).getCourses().add(c);elseUser user = new User();user.setUserId(rs.getInt(user_id);user.setUserName(rs.getString(username);user.setPassword(rs.getString(password);user.setRealName(rs.getString(realname);user.setRole(rs.getString(role);user.setDepartment(rs.getString(department);Course c = new Course();c.setCourseName(rs.getString(course_name);user.getCourses().add(c);map.put(user.getUserName(), user);Set set = map.keySet();for (String key : set) list.add(map.get(key); catch (Exception e) e.printStackTrace();finallyJdbcUtil.release(rs, pstm, conn);return list;二 通过两个表的连接查询到与另一个表关联的字段，返回一个查询结果(所有老师的信息)，再利用这个结果中的某个字段查询另一个关系表表（每个老师的id对应的课程），在servic层封装这两个查询结果，给一个实体类（封装其他实体类的实体类），三 通过不同的实体类，查询通过同一个字段（或者可以查询到相关数据的字段），通过不同的查询方法，将包含需要的信息的返回值封装到对应的对象中，在action层进行处理，将这两个实体类封装给一个集合，将集合传递给页面，遍历使用eg:Accusation acc = ne