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
资源评论
芳华丨
- 粉丝: 3
- 资源: 3
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功