运动会积分管理系统（Java）

package cc.bean.sport.daoImpl; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.Collections; import java.util.Iterator; import java.util.LinkedList; import java.util.List; import cc.bean.sport.DBconect.JdbcConnect; import cc.bean.sport.dao.SportDAO; import cc.bean.sport.vo.Record; import cc.bean.sport.vo.Result; public class SportDAOImpl implements SportDAO { // 根据sId查询出该学校的所有信息 public List<Record> queryBysId(String id) throws Exception { String sql = null;//sql语句 PreparedStatement pst = null; JdbcConnect jdbc = null; ResultSet rs = null; Record record = null; List<Record> all = new ArrayList<Record>(); sql = "SELECT sName,proName,proInt,proResult FROM record WHERE sId=?"; try { jdbc = new JdbcConnect(); pst = jdbc.getConnection().prepareStatement(sql); pst.setString(1, id); rs = pst.executeQuery(); while ( rs.next() ) { record = new Record(); record.setSName((String) rs.getString("sName")); record.setProName((String) rs.getString("proName")); record.setProResult((String) rs.getString("proResult")); record.setProInt((Integer) rs.getInt("proInt")); all.add(record); } pst.close(); } catch (Exception e) { e.printStackTrace(); } finally { jdbc.close(); } return all; } // 更新学校比赛项目的成绩及单项积分 public void updateBasic(Record record) throws Exception { String sql = null;//sql语句 PreparedStatement pst = null; JdbcConnect jdbc = null; sql = "UPDATE record SET proResult=?,proInt=? WHERE sId=? and proName=?"; try { jdbc = new JdbcConnect(); pst = jdbc.getConnection().prepareStatement(sql); pst.setString(1, record.getProResult()); pst.setInt(2, record.getProInt()); pst.setString(3, record.getSId()); pst.setString(4, record.getProName()); pst.executeUpdate(); pst.close(); } catch (Exception e) { e.printStackTrace(); } finally { jdbc.close(); } } // 统计某个学校的总分 public void updateSumInt(Result result) throws Exception { String sql = null;//sql语句 PreparedStatement pst = null; JdbcConnect jdbc = null; List<Record> all = new ArrayList<Record>(); Record record = null; int mInt = 0, //记录男子团体总积分 wInt = 0, //记录女子团体总积分 sumInt = 0;//记录该学校团体总积分 all = queryBysId(result.getSId());//获取某学校的积分情况 Iterator<Record> it = all.iterator(); while ( it.hasNext() ) { record = it.next(); if ((record.getProName()).startsWith("w")) { wInt = wInt + record.getProInt(); } else { mInt = mInt + record.getProInt(); } } sumInt = wInt + mInt; result.setMInt(mInt); result.setWInt(wInt); result.setSumInt(sumInt); sql = "UPDATE result SET mInt=?,wInt=?,sumInt=? WHERE sId=?"; try { jdbc = new JdbcConnect(); pst = jdbc.getConnection().prepareStatement(sql); pst.setInt(1, mInt); pst.setInt(2, wInt); pst.setInt(3, sumInt); pst.setString(4, result.getSId()); pst.executeUpdate(); pst.close(); } catch (Exception e) { e.printStackTrace(); } finally { jdbc.close(); } } // 按不同排序方式输出所有学校 public List<Result> querySortsumIntByAll(int id) throws Exception { Comp_mIntImpl comp_mInt = new Comp_mIntImpl(); Comp_wIntImpl comp_wInt = new Comp_wIntImpl(); String sql = null;//sql语句 PreparedStatement pst = null; JdbcConnect jdbc = null; ResultSet rs = null; Result result = null; List<Result> all = new LinkedList<Result>(); sql = "SELECT * FROM result"; try { jdbc = new JdbcConnect(); pst = jdbc.getConnection().prepareStatement(sql); rs = pst.executeQuery(); while ( rs.next() ) { result = new Result(); result.setSId((String) rs.getString(1)); result.setSName((String) rs.getString(2)); result.setMInt((Integer) rs.getInt(3)); result.setWInt((Integer) rs.getInt(4)); result.setSumInt((Integer) rs.getInt(5)); all.add(result); } pst.close(); } catch (Exception e) { e.printStackTrace(); } finally { jdbc.close(); } if (id == 4) { Collections.sort(all);//按学校团体积分排序 return all; } else if (id == 2) { Collections.sort(all, comp_mInt);//按男子团体积分排序 return all; } else if (id == 3) { Collections.sort(all, comp_wInt);//按女子团体积分排序 return all; } else { return all;//按编号 } } // 按照项目名称查询,取前三名的学校的情况 public List<Record> querySortIntByPro(String proName) throws Exception { String sql = null;//sql语句 PreparedStatement pst = null; JdbcConnect jdbc = null; ResultSet rs = null; Record record = null; List<Record> all = new LinkedList<Record>(); sql = "SELECT sId,sName,proResult,proInt FROM record WHERE proName=?"; try { jdbc = new JdbcConnect(); pst = jdbc.getConnection().prepareStatement(sql); pst.setString(1, proName); rs = pst.executeQuery(); while ( rs.next() ) { record = new Record(); record.setSId((String) rs.getString("sId")); record.setSName((String) rs.getString("sName")); record.setProResult((String) rs.getString("proResult")); record.setProInt((Integer) rs.getInt("proInt")); all.add(record); } pst.close(); } catch (Exception e) { e.printStackTrace(); } finally { jdbc.close(); } Collections.sort(all); return all; } }