xscjgl.zip_Gl学生_学生信息管理_成绩

package cn.chen.dao.impl; import java.io.FileOutputStream; import java.io.OutputStream; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import jxl.Workbook; import jxl.write.Label; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import cn.chen.dao.ScoreDao; import cn.chen.jdbc.Dbconnect; import cn.chen.model.Page; import cn.chen.model.Score; import cn.chen.model.ScoreDetails; public class ScoreDaoimpl implements ScoreDao { Dbconnect dc = new Dbconnect(); @Override public int insert(Score score) { String sql = "insert into score values(" + score.getScid() + "," + score.getTid() + "," + score.getSid() + "," + score.getKid() + "," + score.getTimes() + "," + score.getScore() + "," + score.getEnable() + ")"; System.out.println(sql); int x = dc.noSelectInfo(sql); return x; } @Override public boolean Update(Score score) { int scid=score.getScid(); int time=score.getTimes(); float sc=score.getScore(); String sql = "update score set score=" + sc + ",times="+time+" where scid=" +scid; System.out.println(sql); int x = dc.noSelectInfo(sql); if (x <= 0) { return false; } else { return true; } } @Override public Score find(int scid) { String sql = "select * from score where scid=" + scid; ResultSet rs = null; rs = dc.selectInfo(sql); Score score = new Score(); try { while (rs.next()) { score.setScid(rs.getInt(1)); score.setTid(rs.getInt(2)); score.setSid(rs.getInt(3)); score.setKid(rs.getInt(4)); score.setTimes(rs.getInt(5)); score.setScore(rs.getFloat(6)); score.setEnable(rs.getInt(7)); } } catch (SQLException e) { return null; } if (score.getSid() != 0) { return score; } else { return null; } } @Override public boolean delete(int id) { String sql = "delete from score where sid=" + id; System.out.println(sql); int x = dc.noSelectInfo(sql); if (x > 0) { return true; } else { return false; } } @Override public Page search(String type, String key, int pageNow) { ResultSet rs = null; List<Score> list = new ArrayList<Score>(); Page page = new Page(); page.setPageNow(pageNow); String sql = "select * from score"; if (key != null) { if (type.equals("sid") || type.equals("tid") || type.equals("kid")) { sql = sql + " where " + type + "=" + key + " limit " + (page.getPageNow() - 1) + "," + page.getPageSize(); } System.out.println(sql); } int x = 0;// 存储我们共查出了多少条数据 rs = dc.selectInfo(sql); try { while (rs.next()) { Score score = new Score(); score.setScid(rs.getInt(1)); score.setTid(rs.getInt(2)); score.setSid(rs.getInt(3)); score.setKid(rs.getInt(4)); score.setTimes(rs.getInt(5)); score.setScore(rs.getInt(6)); score.setEnable(rs.getInt(7)); list.add(score); x++; } } catch (SQLException e) { } page.setRowCount(x); page.setPageCount(x); page.setDatas(list); return page; } @Override public Page search(int pageNow) { Page page = new Page(); List<Score> list = new ArrayList<Score>(); ResultSet rs = null; String sql = "select * from student limit " + (pageNow - 1) + ", " + page.getPageSize(); String sql1 = "select count(*) from student"; rs = dc.selectInfo(sql1); try { while (rs.next()) { page.setRowCount(rs.getInt(1)); page.setPageCount(rs.getInt(1)); } } catch (SQLException e1) { System.out.println("计算rowcount出错"); } rs = dc.selectInfo(sql); try { while (rs.next()) { Score score = new Score(); score.setScid(rs.getInt(1)); score.setTid(rs.getInt(2)); score.setSid(rs.getInt(3)); score.setKid(rs.getInt(4)); score.setTimes(rs.getInt(5)); score.setScore(rs.getInt(6)); score.setEnable(rs.getInt(7)); list.add(score); } } catch (SQLException e) { System.out.println("sql语句异常"); } page.setPageNow(pageNow); page.setDatas(list); return page; } public Page selectStu(String cid,int pageNow){ Page page=new Page(); String sql= "SELECT s.*,score FROM student s LEFT JOIN score sc using(sid) where score is null and cid="+cid+" limit "+(pageNow-1)*page.getPageSize()+","+page.getPageSize(); System.out.println(sql); System.out.println(sql); return page; } @Override public Page selectScore(String cid, String kid,int pageNow) { List<ScoreDetails> sdl=new ArrayList<ScoreDetails>(); ResultSet rs=null; Page page=new Page(); page.setPageNow(pageNow); String sql1="select count(scid) from student s,banji b,score sc " + "where s.sid=sc.SID and s.cid=b.cid and b.cid="+cid+" and sc.KID="+kid; String sql="select scid,s.sid,sname,c.kid,kname,t.tid,tname,score,times,b.cid,cname " + "from student s,course c,teacher t,banji b,score sc " + "where s.sid=sc.SID and c.KID=sc.KID " + "and t.tid=sc.TID and s.cid=b.cid " + "and b.cid="+cid+" and c.KID="+kid+" limit "+(pageNow-1)*page.getPageSize()+","+page.getPageSize(); System.out.println(sql); System.out.println(sql1); rs=dc.selectInfo(sql1); try { while(rs.next()){ page.setPageCount(rs.getInt(1)); page.setRowCount(rs.getInt(1)); } } catch (SQLException e) { return null; } rs=dc.selectInfo(sql); try { while(rs.next()){ ScoreDetails sd=new ScoreDetails(); sd.setScid(rs.getInt(1)); sd.setSid(rs.getInt(2)); sd.setSname(rs.getString(3)); sd.setKid(rs.getInt(4)); sd.setKname(rs.getString(5)); sd.setTid(rs.getInt(6)); sd.setTname(rs.getString(7)); sd.setScore(rs.getFloat(8)); sd.setTimes(rs.getInt(9)); sd.setCid(rs.getInt(10)); sd.setCname(rs.getString(11)); sdl.add(sd); } } catch (SQLException e) { return null; } page.setDatas(sdl); return page; } @Override public boolean ExportExcel(int uid, String role) throws Exception { String[] title = { "成绩编号","学生学号", "学生姓名", "课程名称", "授课老师", "考试成绩" }; String sql = "select scid,s.sid,sname,kname,tname,score " + " from score sc, course c,teacher t,student s " + "where sc.SID=s.SID and sc.TID=t.TID and sc.KID=c.KID"; String filePath = "D:\\成绩表.xls"; OutputStream os = new FileOutputStream(filePath); WritableWorkbook wwb = Workbook.createWorkbook(os); WritableSheet sheet = wwb.createSheet("学生成绩表", 0); Label label = null; ResultSet rs=null; for (int i = 0; i < title.length; i++) { label = new Label(i, 0, title[i]); sheet.addCell(label); } if(role.equals("teacher")){ sql = sql+ "and sc.TID="+uid; } if(role.equals("student")){ sql = sql +"and sc.SID="+uid; } rs = dc.selectInfo(sql); int x = 0; while (rs.next()) { x++; for (int y = 0; y < title.length; y++) { label = new Label(y, x, rs.getString(y + 1)); sheet.addCell(label); } } wwb.write(); wwb.close(); System.out.println("Excel导出成功"); return true; } @Override public Page referScore(int pageNow) { List<ScoreDetails> sdl=new ArrayList<ScoreDetails>(); ResultSet rs=null; Page page=new Page(); page.setPageNow(pageNow); String sql1="select count(scid) from student s,banji b,score sc " + "where s.sid=sc.SID and s.cid=b.cid "; String sql="select scid,s.sid,sname,c.kid,kname,t.tid,tname,score,times,b.cid,cname " + "from student s,course c,teacher t,banji b,score sc " + "where s.sid=sc.SID and c.KID=sc.KID " + "and t.tid=sc.TID and s.cid=b.cid " + " limit "+(pageNow-1)*page.getPageSize()+","+page.getPageSize(); System.out.println(sql); System.out.println(sql1); rs=dc.selectIn