框架添加mybatis

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.xsdhttp://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsdhttp://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.2.xsdhttp://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsdhttp://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsdhttp://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.2.xsdhttp://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;}@Overridepublic 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;}@Overridepublic 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;@Servicepublic class QryCourseSelectionImpl implements QryCourseSelection {CourseSelectDao courseSelectDao;public CourseSelectDao getCourseSelectDao() {return courseSelectDao;}@Resourcepublic void setCourseSelectDao(CourseSelectDao courseSelectDao) {this.courseSelectDao = courseSelectDao;}@Overridepublic JSONArray getCourseSelectionByschId(long schId) {// TODO Auto-generated method stubreturn 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;@Mybatispublic 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;}@Resourcepublic void setQcs(QryCourseSelection qcs) {this.qcs = qcs;}@RequestMapping(value = "/10010.do", method = RequestMethod.GET)@ResponseBodypublic 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)@ResponseBodypublic 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)@ResponseBodypublic 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)@ResponseBodypublic 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();}}}