1 public int addClassRoom(ClassRoom cr) {
 2         String sqls = "select * from t_schoolemp where s_name=?";//当输入的老师名字不存在时 查出数据
 3         ResultSet rs = JdbcUtil.executeQuery(sqls, cr.getTeacher().getName());
 4         try {
 5             if (rs.getRow()==0) {//当那个老师不存在的时候 把这个老师添加进入学校员工表中 
 6                 String sqlAdd = "insert into t_schoolemp values (null,?,?)";
 7                 int num = JdbcUtil.executeUpdate(sqlAdd, cr.getTeacher()
 8                         .getName(), 6);
 9             }
10         } catch (SQLException e) {
11             e.printStackTrace();
12         }//  添加新的班级
13         String sql = " insert into t_classromm (cr_name,sl_id,s_id,cr_time,cr_num) values(?,"
14                 + "(select sl_id from t_shool where sl_name=?) ,"
15                 + "(select s_id from t_schoolemp  where s_name=?),?,?);";
16         int count = JdbcUtil.executeUpdate(sql, cr.getName(), cr.getSchool()
17                 .getName(), cr.getTeacher().getName(), new Date(), cr
18                 .getClassNum());
19         return count;
20 
21     }

 1 public List<Map<String, String>> getStatistical(String c_name) {
 2         List<Map<String, String>> list = new ArrayList<Map<String, String>>();
 3        //统计的sql语句
 4         String sql = "SELECT s.sl_name '学校名称',COUNT(c.cr_name) '网脉班级数量',"
 5                 + "SUM((CASE st.vip WHEN '会员' THEN 1 ELSE 0 END)) '会员数量', "
 6                 + "SUM((CASE st.vip WHEN '非会员' THEN 1 ELSE 0 END)) '非会员数量' "
 7                 + "FROM t_shool s "
 8                 + "LEFT JOIN t_classromm  c on s.sl_id = c.sl_id "
 9                 + "LEFT JOIN t_student st ON st.st_clid = c.cr_id "
10                 + "where s.sl_cid = (select t_city.c_id  from t_city where t_city.c_name=? )"
11                 + "group by s.sl_name; ";
12         ResultSet rs = JdbcUtil.executeQuery(sql, c_name);
13         try {
14             while (rs.next()) {
15                 Map<String, String> map = new HashMap<String, String>();
16                 map.put("学校名称", rs.getString("学校名称"));
17                 map.put("网脉班级数量", rs.getString("网脉班级数量"));
18                 map.put("会员数量", rs.getString("会员数量"));
19                 map.put("非会员数量", rs.getString("非会员数量"));
20                 list.add(map);
21 
22             }
23         } catch (SQLException e) {
24             e.printStackTrace();
25         } finally {
26             JdbcUtil.close();
27         }
28         return list;
29 
30     }