1、添加jar包
2、web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
id="WebApp_ID" version="2.5">
<display-name>Doofen</display-name>
<context-param>
<param-name>webAppRootKey</param-name>
<param-value>Doofen</param-value>
</context-param>
<!-- set session timeout for 1 minute <url-pattern>/*.atom</url-pattern> -->
<!-- <session-config>
<session-timeout>300</session-timeout>
<tracking-mode>COOKIE</tracking-mode>
</session-config>
-->
<context-param>
<param-name>log4jConfigLocation</param-name>
<param-value>/WEB-INF/config/log4j.properties</param-value>
</context-param>
<context-param>
<param-name>contextConfigLocation</param-name>
<param-value>/WEB-INF/config/app/applicationContext.xml</param-value>
</context-param>
<!--
- Loads the root application context of this web app at startup,
- by default from "/WEB-INF/applicationContext.xml".
-
- The root context is the parent of all servlet-specific contexts.
- This means that its beans are automatically available in these child contexts,
- both for getBean(name) calls and (external) bean references.
-->
<listener>
<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>
<listener>
<listener-class>org.springframework.web.util.Log4jConfigListener</listener-class>
</listener>
<!-- Reads request input using UTF-8 encoding -->
<filter>
<filter-name>characterEncodingFilter</filter-name>
<filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
<init-param>
<param-name>encoding</param-name>
<param-value>UTF-8</param-value>
</init-param>
<init-param>
<param-name>forceEncoding</param-name>
<param-value>true</param-value>
</init-param>
</filter>
<filter-mapping>
<filter-name>characterEncodingFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
<filter>
<filter-name>urlFilter</filter-name>
<filter-class>com.zxt.framework.sercurity.filter.Url4CNFilter</filter-class>
<init-param>
<param-name>DEFAULT_URI_ENCODE</param-name>
<param-value>UTF-8</param-value>
</init-param>
</filter>
<filter-mapping>
<filter-name>urlFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
<!-- Filter request input included xss/sql hacking -->
<filter>
<filter-name>XssSqlFilter</filter-name>
<filter-class>com.zxt.framework.sercurity.filter.XssHttpServletRequestFilter</filter-class>
</filter>
<filter-mapping>
<filter-name>XssSqlFilter</filter-name>
<url-pattern>/*</url-pattern>
<dispatcher>REQUEST</dispatcher>
</filter-mapping>
<!-- Handles all requests into the application -->
<servlet>
<servlet-name>dispatcher</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<init-param>
<param-name>contextConfigLocation</param-name>
<param-value>/WEB-INF/config/app/servlet-context.xml</param-value>
</init-param>
<load-on-startup>1</load-on-startup>
</servlet>
<servlet>
<servlet-name>springmvc</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<init-param>
<param-name>contextConfigLocation</param-name>
<param-value>/WEB-INF/config/app/application.xml</param-value>
</init-param>
<load-on-startup>1</load-on-startup>
</servlet>
<servlet-mapping>
<servlet-name>dispatcher</servlet-name>
<url-pattern>/</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>springmvc</servlet-name>
<url-pattern>*.do</url-pattern>
</servlet-mapping>
<welcome-file-list>
<!-- <welcome-file>index.html</welcome-file> -->
<welcome-file>login.html</welcome-file>
</welcome-file-list>
<!-- <error-page>
<error-code>400</error-code>
<location>/page_500.html</location>
</error-page>
<error-page>
<error-code>500</error-code>
<location>/page_500.html</location>
</error-page>
<error-page>
<error-code>403</error-code>
<location>/page_403.html</location>
</error-page>
<error-page>
<error-code>404</error-code>
<location>/page_404.html</location>
</error-page> -->
<!-- TCP通讯Server端启动类 -->
<servlet>
<servlet-name>upload</servlet-name>
<servlet-class>com.cicada.servlet.FileUploadServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>upload</servlet-name>
<url-pattern>/upload</url-pattern>
</servlet-mapping>
<listener>
<listener-class>com.doofen.Startup</listener-class>
</listener>
</web-app>
3、application.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:jee="http://www.springframework.org/schema/jee"
xmlns:util="http://www.springframework.org/schema/util"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:mvc="http://www.springframework.org/schema/mvc"
xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd
http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.2.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
http://www.springframework.org/schema/mvc
http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd
http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.2.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-4.0.xsd">
<!-- context-3.0.xsd 的版本为3.0,其他的为3.2,3.0都可以,否则<tx:advice>和<aop:config>里面的属性点不出来-->
<!-- 1.扫描包路径 -->
<context:component-scan base-package="cn"></context:component-scan>
<!-- 2.开启注解驱动 -->
<mvc:annotation-driven></mvc:annotation-driven>
<!-- 3.连接数据库的参数 -->
<util:properties id="prop" location="classpath:../config/db/jdbc.properties"></util:properties>
<!-- 4.配置数据源(连接池) -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="#{prop.driver}"></property>
<property name="url" value="#{prop.url}"></property>
<property name="username" value="#{prop.username}"></property>
<property name="password" value="#{prop.password}"></property>
</bean>
<!-- 5.配置SqlSessionFactory,Mybatis的入口,整合包中提供了这个类的子类 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!-- 注入连接池对象 -->
<property name="dataSource" ref="dataSource"></property>
<!-- 指定实体映射文件 -->
<property name="mapperLocations" value="classpath:cn/doofen/mapper/xml/*.xml"></property>
</bean>
<!-- 6.整合中提供了一个类(专门用来生成接口的实例化对象-不是直接生成) -->
<bean id="mapper" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<!-- 指定需要生成接口的实例化对象的包路径 -->
<property name="basePackage" value="cn.doofen.mapper"></property>
<!-- 注入 SqlSessionFactory,可以不写,它会自动注入-->
<property name="sqlSessionFactory" ref="sqlSessionFactory"></property>
<!-- 指定注解的类,只有该接口上有相应的注解的才会由容器生成实例化对象 -->
<property name="annotationClass" value="cn.doofen.util.Mybatis"></property>
</bean>
<!-- 7.配置视图解析器 -->
<bean id="view" class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<property name="prefix" value="/"></property>
<property name="suffix" value=".html"></property>
</bean>
</beans>
4、建立目录
4、建立MySQL表
5、根据MySQL表创建实体类
package cn.doofen.entity;
public class OptionalCourse {
private long id;
private long schId;
private long clsId;
private long stuId;
private String stuName;
private long categoryId;
private OptionalCourseCategory occ;
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public long getSchId() {
return schId;
}
public void setSchId(long schId) {
this.schId = schId;
}
public long getClsId() {
return clsId;
}
public void setClsId(long clsId) {
this.clsId = clsId;
}
public long getStuId() {
return stuId;
}
public void setStuId(long stuId) {
this.stuId = stuId;
}
public String getStuName() {
return stuName;
}
public void setStuName(String stuName) {
this.stuName = stuName;
}
public long getCategoryId() {
return categoryId;
}
public void setCategoryId(long categoryId) {
this.categoryId = categoryId;
}
public OptionalCourseCategory getOcc() {
return occ;
}
public void setOcc(OptionalCourseCategory occ) {
this.occ = occ;
}
public OptionalCourse() {
super();
// TODO Auto-generated constructor stub
}
public OptionalCourse(long id, long schId, long clsId, long stuId,
String stuName, long categoryId, OptionalCourseCategory occ) {
super();
this.id = id;
this.schId = schId;
this.clsId = clsId;
this.stuId = stuId;
this.stuName = stuName;
this.categoryId = categoryId;
this.occ = occ;
}
@Override
public String toString() {
return "OptionalCourse [id=" + id + ", schId=" + schId + ", clsId="
+ clsId + ", stuId=" + stuId + ", stuName=" + stuName
+ ", categoryId=" + categoryId + ", occ=" + occ + "]";
}
}
package cn.doofen.entity;
public class OptionalCourseCategory {
private long id;
private String categoryName;
private String subjects;
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getCategoryName() {
return categoryName;
}
public void setCategoryName(String categoryName) {
this.categoryName = categoryName;
}
public String getSubjects() {
return subjects;
}
public void setSubjects(String subjects) {
this.subjects = subjects;
}
public OptionalCourseCategory() {
super();
// TODO Auto-generated constructor stub
}
public OptionalCourseCategory(long id, String categoryName, String subjects) {
super();
this.id = id;
this.categoryName = categoryName;
this.subjects = subjects;
}
@Override
public String toString() {
return "OptionalCourseCategory [id=" + id + ", categoryName="
+ categoryName + ", subjects=" + subjects + "]";
}
}
6、创建service
QryCourseSelection
package cn.doofen.service;
import com.alibaba.fastjson.JSONArray;
public interface QryCourseSelection {
JSONArray getCourseSelectionByschId(long schId);
}
package cn.doofen.service.impl;
import javax.annotation.Resource;
import org.springframework.stereotype.Service;
import cn.doofen.mapper.CourseSelectDao;
import cn.doofen.service.QryCourseSelection;
import com.alibaba.fastjson.JSONArray;
@Service
public class QryCourseSelectionImpl implements QryCourseSelection {
CourseSelectDao courseSelectDao;
public CourseSelectDao getCourseSelectDao() {
return courseSelectDao;
}
@Resource
public void setCourseSelectDao(CourseSelectDao courseSelectDao) {
this.courseSelectDao = courseSelectDao;
}
@Override
public JSONArray getCourseSelectionByschId(long schId) {
// TODO Auto-generated method stub
return courseSelectDao.getCourseSelectByschId(schId);
}
}
7、util
package cn.doofen.util;
public @interface Mybatis {
}
8.mapper
package cn.doofen.mapper;
import org.apache.ibatis.annotations.Param;
import cn.doofen.util.Mybatis;
import com.alibaba.fastjson.JSONArray;
@Mybatis
public interface CourseSelectDao {
JSONArray getCourseSelectByschId(@Param("schId")long schId);
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"
"http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
<!-- namespace指定UserMapper接口 -->
<mapper namespace="cn.doofen.mapper.CourseSelectDao">
<resultMap type="cn.doofen.entity.OptionalCourse" id="StuResultMap">
<id property="id" column="id"/>
<result property="schId" column="sch_id"/>
<result property="clsId" column="cls_id"/>
<result property="stuId" column="stu_id"/>
<result property="stuName" column="stu_name"/>
<result property="categoryId" column="category_id"/>
<association property="occ" javaType="cn.doofen.entity.OptionalCourseCategory">
<id property="id" column="id"/>
<result property="categoryName" column="category_name"/>
<result property="subjects" column="subjects"/>
</association>
</resultMap>
<select id="getCourseSelectByschId" resultMap="StuResultMap">
select * from t_optional_course as c1 inner join t_optional_course_category as c2
on c1.category_id = c2.id AND sch_id = #{schId}
</select>
</mapper>
7.controller
package cn.doofen.ctrl;
import java.io.OutputStream;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.cicada.report.RptExamsitExcel;
import com.cicada.seo.query.impl.QryIntegReportImpl;
import com.cicada.seo.query.managerCheckTch.QryTeacherByManager;
import com.cicada.seo.query.managerCheckTch.impl.QryTeacherByMngSEOImpl;
import com.doofen.audit.AuditContants;
import com.doofen.ctrl.rpt.RptConst;
import com.zxt.framework.mvc.controller.VOController;
import cn.doofen.entity.Group;
import cn.doofen.entity.GroupPerson;
import cn.doofen.entity.OptionalCourse;
import cn.doofen.entity.OptionalCourseCategory;
import cn.doofen.service.QryCourseSelection;
import cn.doofen.service.QryGroupsByTchGid;
@Controller
@RequestMapping("/{schId}/rpt100")
public class CourseSelectController extends VOController {
QryCourseSelection qcs;
public QryCourseSelection getQcs() {
return qcs;
}
@Resource
public void setQcs(QryCourseSelection qcs) {
this.qcs = qcs;
}
@RequestMapping(value = "/10010.do", method = RequestMethod.GET)
@ResponseBody
public JSONArray getGradeScoreTable(
@PathVariable(value = "schId" ) Long schId,
@RequestParam(value = "examId", required = true) Long examId,
@RequestParam(value = "gradeId", required = false) Long gradeId,
@RequestParam(value = "clsId", required = false) Long clsId,
HttpServletRequest request
) {
logger.debug("[CICADA][CONTROLLER] EXEC 获取考情报表—成绩汇总表");
try {
JSONArray jarr = new JSONArray();
JSONArray ja = qcs.getCourseSelectionByschId(schId);
if(ja.size() != 0){
for (Object obj : ja) {
OptionalCourse oc = (OptionalCourse) obj;
long id = oc.getId();
long schid = oc.getSchId();
long clsid = oc.getClsId();
long stuid = oc.getStuId();
String StuName = oc.getStuName();
long categoryid = oc.getCategoryId();
OptionalCourseCategory occ = oc.getOcc();
String subjects = occ.getSubjects();
String categoryName = occ.getCategoryName();
JSONObject jso = new JSONObject();
jso.put("id", id);
jso.put("schid", schid);
jso.put("clsid", clsid);
jso.put("stuid", stuid);
jso.put("StuName", StuName);
jso.put("categoryid", categoryid);
jso.put("subjects", subjects);
jso.put("categoryName", categoryName);
jarr.add(jso);
}
}
QryIntegReportImpl opr = new QryIntegReportImpl();
return opr.getTable( schId, examId, clsId, gradeId,jarr);
} catch (Exception e) {
e.printStackTrace();
//throw new Exception(ERR_DATA_EXP);
logger.warn(e.getMessage());
return new JSONArray();
}
}
/**
* 获取综合考情报告—成绩汇总表
*
* @param examId
* 考试ID
* @param gradeId
* 年级ID
* @return 综合考情汇总表数据
* @
*/
@RequestMapping(value = "/10011.do", method = RequestMethod.GET)
@ResponseBody
public JSONArray getNewScoreTable(
@PathVariable(value = "schId" ) Long schId,
@RequestParam(value = "examId", required = true) Long examId,
@RequestParam(value = "gradeId", required = false) Long gradeId,
@RequestParam(value = "clsId", required = false) Long clsId,
HttpServletRequest request
) {
logger.debug("[CICADA][CONTROLLER] EXEC 获取考情报表—成绩汇总表");
try {
JSONArray jarr = new JSONArray();
JSONArray ja = qcs.getCourseSelectionByschId(schId);
if(ja.size() != 0){
for (Object obj : ja) {
OptionalCourse oc = (OptionalCourse) obj;
long id = oc.getId();
long schid = oc.getSchId();
long clsid = oc.getClsId();
long stuid = oc.getStuId();
String StuName = oc.getStuName();
long categoryid = oc.getCategoryId();
OptionalCourseCategory occ = oc.getOcc();
String subjects = occ.getSubjects();
String categoryName = occ.getCategoryName();
JSONObject jso = new JSONObject();
jso.put("id", id);
jso.put("schid", schid);
jso.put("clsid", clsid);
jso.put("stuid", stuid);
jso.put("StuName", StuName);
jso.put("categoryid", categoryid);
jso.put("subjects", subjects);
jso.put("categoryName", categoryName);
jarr.add(jso);
}
}
QryIntegReportImpl opr = new QryIntegReportImpl();
return opr.getNewTable( schId, examId, clsId, gradeId,jarr);
} catch (Exception e) {
e.printStackTrace();
//throw new Exception(ERR_DATA_EXP);
logger.warn(e.getMessage());
return new JSONArray();
}
}
/**
* 特优生
* @param examId 考试ID
* @param gradeId 年级ID
* @param response
* @
*/
@RequestMapping(value = "/xls/10010.do", method = RequestMethod.GET)
@ResponseBody
public void exportExcel_GradeScoreTable(
@PathVariable(value = "schId" ) Long schId,
@RequestParam(value = "examName", required = true) String examName,
@RequestParam(value = "examId", required = true) Long examId,
@RequestParam(value = "gradeId", required = false) Long gradeId,
@RequestParam(value = "clsId", required = false) Long clsId,
@RequestParam(value = "topn", required = false) Integer topn,
HttpServletRequest request,
HttpServletResponse response) {
logger.debug("[DOOFEN][CONTROLLER] EXPORT 综合考情报告—"+RptConst.RPT_1000_NAME);
try {
//注意协议头有字节数限制,所以fileName不能超长
String fileName = "";
if( topn == null){
fileName = getExcelName( request, examName, RptConst.RPT_1001_NAME);
}else{
fileName = getExcelName( request, examName, RptConst.RPT_1000_NAME);
}
response.reset();
response.setContentType("application/vnd.ms-excel"); // 改成输出excel文件
response.setHeader("Content-disposition",
"attachment; filename="+fileName );
OutputStream os = response.getOutputStream();
JSONArray jarr = new JSONArray();
JSONArray ja = qcs.getCourseSelectionByschId(schId);
if(ja.size() != 0){
for (Object obj : ja) {
OptionalCourse oc = (OptionalCourse) obj;
long id = oc.getId();
long schid = oc.getSchId();
long clsid = oc.getClsId();
long stuid = oc.getStuId();
String StuName = oc.getStuName();
long categoryid = oc.getCategoryId();
OptionalCourseCategory occ = oc.getOcc();
String subjects = occ.getSubjects();
String categoryName = occ.getCategoryName();
JSONObject jso = new JSONObject();
jso.put("id", id);
jso.put("schid", schid);
jso.put("clsid", clsid);
jso.put("stuid", stuid);
jso.put("StuName", StuName);
jso.put("categoryid", categoryid);
jso.put("subjects", subjects);
jso.put("categoryName", categoryName);
jarr.add(jso);
}
}
RptExamsitExcel opr = new RptExamsitExcel();
opr.createRptExcel1(os, schId, examId, clsId, gradeId, jarr, topn);
} catch (Exception e) {
//throw new Exception(ERR_DATA_EXP);
logger.warn(e.getMessage());
e.printStackTrace();
//return new JSONArray();
}
}
/**
* 特优生
* @param examId 考试ID
* @param gradeId 年级ID
* @param response
* @
*/
@RequestMapping(value = "/xls/10011.do", method = RequestMethod.GET)
@ResponseBody
public void exportExcel_GradeScoreTable1(
@PathVariable(value = "schId" ) Long schId,
@RequestParam(value = "examName", required = true) String examName,
@RequestParam(value = "examId", required = true) Long examId,
@RequestParam(value = "gradeId", required = false) Long gradeId,
@RequestParam(value = "clsId", required = false) Long clsId,
@RequestParam(value = "topn", required = false) Integer topn,
HttpServletRequest request,
HttpServletResponse response) {
logger.debug("[DOOFEN][CONTROLLER] EXPORT 综合考情报告—"+RptConst.RPT_1000_NAME);
try {
//注意协议头有字节数限制,所以fileName不能超长
String fileName = "";
if( topn == null){
fileName = getExcelName( request, examName, RptConst.RPT_1001_NAME);
}else{
fileName = getExcelName( request, examName, RptConst.RPT_1000_NAME);
}
response.reset();
response.setContentType("application/vnd.ms-excel"); // 改成输出excel文件
response.setHeader("Content-disposition",
"attachment; filename="+fileName );
OutputStream os = response.getOutputStream();
JSONArray jarr = new JSONArray();
JSONArray ja = qcs.getCourseSelectionByschId(schId);
if(ja.size() != 0){
for (Object obj : ja) {
OptionalCourse oc = (OptionalCourse) obj;
long id = oc.getId();
long schid = oc.getSchId();
long clsid = oc.getClsId();
long stuid = oc.getStuId();
String StuName = oc.getStuName();
long categoryid = oc.getCategoryId();
OptionalCourseCategory occ = oc.getOcc();
String subjects = occ.getSubjects();
String categoryName = occ.getCategoryName();
JSONObject jso = new JSONObject();
jso.put("id", id);
jso.put("schid", schid);
jso.put("clsid", clsid);
jso.put("stuid", stuid);
jso.put("StuName", StuName);
jso.put("categoryid", categoryid);
jso.put("subjects", subjects);
jso.put("categoryName", categoryName);
jarr.add(jso);
}
}
RptExamsitExcel opr = new RptExamsitExcel();
opr.createRptExcel2(os, schId, examId, clsId, gradeId, jarr, topn);
} catch (Exception e) {
//throw new Exception(ERR_DATA_EXP);
logger.warn(e.getMessage());
e.printStackTrace();
//return new JSONArray();
}
}
}