package nuc.ss.dao;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import nuc.ss.entity.Sc;
import nuc.ss.entity.Student;
import nuc.ss.utils.PropertiesUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
public class ScDao {
public int[] add(List<Integer> cIdArray,Integer stuId) throws SQLException {
DataSource dataSource = PropertiesUtils.getDataSource();
Connection connection = dataSource.getConnection();
connection.setAutoCommit(false);
QueryRunner queryRunner = new QueryRunner(dataSource);
String _sql = "delete from sc where stuId = ?";
queryRunner.update(connection,_sql, stuId);
// System.out.println(1/0);
Object[][] object = new Object[cIdArray.size()][2];
//将cIdArray与stuID保存为二维数组
for(int i=0;i<cIdArray.size();i++) {
object[i][0] = stuId;
object[i][1] = cIdArray.get(i);
}
String sql = "insert into sc(stuId,cid) values(?,?)";
int[] arr = queryRunner.batch(connection,sql, object);
connection.commit();
return arr;
}
public void delete(Integer scId) throws SQLException {
QueryRunner queryRunner = new QueryRunner(PropertiesUtils.getDataSource());
String sql = "delete from sc where scId = ?";
queryRunner.update(sql, scId);
}
public void update(String[] stuIdArr,String[] scoreArr,Integer cId) throws SQLException {
DataSource dataSource = PropertiesUtils.getDataSource();
Connection connection = dataSource.getConnection();
connection.setAutoCommit(false);
QueryRunner queryRunner = new QueryRunner(dataSource);
Object[][] objects = new Object[stuIdArr.length][3];
for(int i=0;i<stuIdArr.length;i++) {
objects[i][0] = Integer.parseInt(scoreArr[i]==null?"0":scoreArr[i]) ;
objects[i][1] = cId;
objects[i][2] = Integer.parseInt(stuIdArr[i]);
}
String sql = "update sc set score = ? where cId = ? and stuId = ?";
queryRunner.batch(sql,objects);
connection.commit();
}
public List<Sc> list(Sc sc) throws SQLException {
QueryRunner queryRunner = new QueryRunner(PropertiesUtils.getDataSource());
String sql = "select * from sc";
List<Sc> list = queryRunner.query(sql, new BeanListHandler<>(Sc.class));
return list;
}
public List<Sc> listByStuId(Integer stuId) throws SQLException {
QueryRunner queryRunner = new QueryRunner(PropertiesUtils.getDataSource());
String sql = "select * from sc where stuId = ?";
List<Sc> list = queryRunner.query(sql, new BeanListHandler<>(Sc.class),stuId);
return list;
}
public Sc findById(Integer scId) throws SQLException {
QueryRunner queryRunner = new QueryRunner(PropertiesUtils.getDataSource());
String sql = "select * from sc where SCID = ?";
Sc sc = queryRunner.query(sql,new BeanHandler<>(Sc.class),scId);
return sc;
}
public List<Student> listStudentByCId(Integer cId) throws SQLException {
QueryRunner queryRunner = new QueryRunner(PropertiesUtils.getDataSource());
String sql = "select student.*,score from sc,student where sc.stuId = student.stuId and cId = ?";
List<Student> list = queryRunner.query(sql, new BeanListHandler<>(Student.class),cId);
return list;
}
public List<Map<String, Object>> query_range() throws SQLException {
QueryRunner queryRunner = new QueryRunner(PropertiesUtils.getDataSource());
String sql = "select course.cid,cname,ifnull(bad,0) bad,ifnull(common,0) common,ifnull(good,0) good,ifnull(best,0) best" +
" from course" +
" left join (" +
" select cid,count(*) bad from sc where score<60 group by cid" +
" ) A on course.cid = A.cid" +
" left join (" +
" select cid,count(*) common from sc where score>=60 and score<=70 group by cid" +
" ) B on course.cid = B.cid" +
" left join(" +
" select cid,count(*) good from sc where score>70 and score<=85 group by cid" +
" ) C on course.cid = C.cid" +
" left join (" +
" select cid,count(*) best from sc where score>85 and score<=100 group by cid" +
" ) D on course.cid =D.cid ";
List<Map<String, Object>> list = queryRunner.query(sql, new MapListHandler());
return list;
}
public List<Map<String, Object>> query_jgl() throws SQLException {
QueryRunner queryRunner = new QueryRunner(PropertiesUtils.getDataSource());
String sql = "select A.cid,(" +
" select cname from course where A.cid = course.cid " +
" ) cname,jgnum,allnum,round(jgnum/allnum,2)*100 jgl from (" +
" select cid, count(*) jgnum from sc where score>=60 group by cid " +
" ) A,(" +
" select cid, count(*) allnum from sc group by cid " +
" ) B where A.cid = B.cid ";
List<Map<String, Object>> list = queryRunner.query(sql, new MapListHandler());
return list;
}
public List<Map<String, Object>> query_rangeByTid(Integer tId) throws SQLException {
QueryRunner queryRunner = new QueryRunner(PropertiesUtils.getDataSource());
String sql = "select course.cid,cname,ifnull(bad,0) bad,ifnull(common,0) common,ifnull(good,0) good,ifnull(best,0) best" +
" from course" +
" left join (" +
" select cid,count(*) bad from sc where score<60 group by cid" +
" ) A on course.cid = A.cid" +
" left join (" +
" select cid,count(*) common from sc where score>=60 and score<=70 group by cid" +
" ) B on course.cid = B.cid" +
" left join(" +
" select cid,count(*) good from sc where score>70 and score<=85 group by cid" +
" ) C on course.cid = C.cid" +
" left join (" +
" select cid,count(*) best from sc where score>85 and score<=100 group by cid" +
" ) D on course.cid =D.cid where tid = ?";
List<Map<String, Object>> list = queryRunner.query(sql, new MapListHandler(),tId);
return list;
}
public List<Map<String, Object>> query_jglByTid(Integer tId) throws SQLException {
QueryRunner queryRunner = new QueryRunner(PropertiesUtils.getDataSource());
String sql = "select * from course,(" +
" select A.cid,jgnum,allnum,round(jgnum/allnum,2)*100 jgl from (" +
" select cid, count(*) jgnum from sc where score>=60 group by cid " +
" ) A,(" +
" select cid, count(*) allnum from sc group by cid " +
" ) B where A.cid = B.cid" +
" ) C where course.cid = C.cid " +
" and tid = ?";
List<Map<String, Object>> list = queryRunner.query(sql, new MapListHandler(),tId);
return list;
}
public List<Map<String, Object>> top5() throws SQLException {
QueryRunner queryRunner = new QueryRunner(PropertiesUtils.getDataSource());
String sql = "select stuId,(select stuName from student where student.stuId = sc.stuId ) stuName,sum(score) sumx from sc group by stuId order by sumx desc limit 0,5";
List<Map<String, Object>> list = queryRunner.query(sql, new MapListHandler());
return list;
}
}
学生选课信息管理系统完整资料 （547个子文件） 
student-course-teacher.cdm 74KB ScDao.class 8KB ScDao.class 8KB CourseDao.class 7KB CourseDao.class 7KB StudentServlet.class 7KB StudentServlet.class 7KB SCServlet.class 6KB SCServlet.class 6KB CourseServlet.class 6KB CourseServlet.class 6KB StudentServlet2.class 6KB StudentServlet2.class 6KB StudentDao.class 6KB StudentDao.class 6KB TeacherDao.class 5KB TeacherDao.class 5KB TeacherServlet.class 5KB TeacherServlet.class 5KB LoginServlet.class 3KB LoginServlet.class 3KB SCQueryServlet.class 3KB SCQueryServlet.class 3KB AdminDao.class 3KB AdminDao.class 3KB PermissionFilter.class 3KB PermissionFilter.class 3KB PageInfo.class 3KB PageInfo.class 3KB DaoFactory.class 3KB DaoFactory.class 3KB PwdServlet.class 2KB PwdServlet.class 2KB Sc.class 2KB Sc.class 2KB Student.class 2KB Student.class 2KB Teacher.class 2KB Teacher.class 2KB Admin.class 2KB Admin.class 2KB MainServlet.class 2KB MainServlet.class 2KB MD5.class 2KB MD5.class 2KB PathFilter.class 2KB PathFilter.class 2KB Course.class 2KB Course.class 2KB CharacterEncodingFilter.class 2KB CharacterEncodingFilter.class 2KB PropertiesUtils.class 1KB PropertiesUtils.class 1KB LogoutServlet.class 959B LogoutServlet.class 959B PathUtils.class 954B PathUtils.class 954B font-awesome.css 39KB font-awesome.css 39KB font-awesome.min.css 30KB font-awesome.min.css 30KB styles.css 5KB styles.css 5KB fontawesome-webfont.eot 162KB fontawesome-webfont.eot 162KB .gitignore 218B stct.iml 3KB
mysql-connector-java-8.0.15.jar 2.04MB mysql-connector-java-8.0.15.jar 2.04MB commons-lang3-3.8.1.jar 490KB commons-lang3-3.8.1.jar 490KB jstl-1.2.jar 405KB jstl-1.2.jar 405KB commons-beanutils-1.8.0.jar 226KB commons-beanutils-1.8.0.jar 226KB commons-dbcp2-2.7.0.jar 204KB commons-dbcp2-2.7.0.jar 204KB commons-pool2-2.8.0.jar 127KB commons-pool2-2.8.0.jar 127KB commons-logging-1.2.jar 60KB commons-logging-1.2.jar 60KB commons-dbutils-1.3.jar 40KB commons-dbutils-1.3.jar 40KB ScDao.java 7KB CourseServlet.java 6KB StudentServlet.java 5KB StudentServlet2.java 5KB CourseDao.java 5KB SCServlet.java 5KB TeacherServlet.java 5KB StudentDao.java 4KB TeacherDao.java 4KB LoginServlet.java 3KB SCQueryServlet.java 3KB DaoFactory.java 2KB AdminDao.java 2KB PermissionFilter.java 2KB PageInfo.java 2KB MD5.java 2KB PwdServlet.java 1KB共 547 条
- 1
- 2
- 3
- 4
- 5
- 6
资源评论
