spring 和 spingmvc 和 mybatis 的集成应用

package cn.sxt.pojo;

public class User {

    private Integer id;
    private String username;
    private String password;
    private String phone;
    private String email;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    public String getPhone() {
        return phone;
    }
    public void setPhone(String phone) {
        this.phone = phone;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public User(Integer id, String username, String password, String phone, String email) {
        super();
        this.id = id;
        this.username = username;
        this.password = password;
        this.phone = phone;
        this.email = email;
    }
    public User() {
        super();
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", username=" + username + ", password=" + password + ", phone=" + phone + ", email="
                + email + "]";
    }
    
}

package cn.sxt.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import cn.sxt.pojo.User;

public interface UserMapper {

    int insert(User user);

    int deleteByPrimaryKey(Integer id);

    int updateByPrimaryKey(User user);

    User selectByPrimaryKey(Integer id);

    List<User> selectList();

    User login(@Param("username")String username,@Param("password") String password);
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  
  <mapper namespace="cn.sxt.mapper.UserMapper">
  <insert id="insert" parameterType="User"
  keyProperty="id" keyColumn="id"
useGeneratedKeys="true"  >
  insert into user (username,password,phone,email) values(#{username},#{password},#{phone},#{email})
  </insert>
  
  <select id="selectList" resultType="User">
  select * from user
  </select>
  <select id="selectByPrimaryKey" parameterType="int" resultType="User">
  select * from user where id = #{id} 
  </select>
  
  <delete id="deleteByPrimaryKey" parameterType="int">
  delete from user where id = #{id}
  </delete>
  <update id="updateByPrimaryKey" parameterType="User">
  update user 
  <set>
      <if test="username != null and username !=‘‘">username = #{username},</if>
              <if test="password != null and password !=‘‘">password = #{password},</if>
              <if test="phone != null and phone !=‘‘">phone = #{phone},</if>
              <if test="email != null and email !=‘‘">email = #{email}</if>
  </set>
  where id = #{id}
  </update>
  
  
        
      <select id="login" parameterType="string" resultType="User">
          select * from user where username = #{username} and password = #{password}
      </select>
  
  </mapper>

package cn.sxt.service;

import java.util.List;

import cn.sxt.pojo.User;

public interface UserService {

    int insert(User user);

    int deleteByPrimaryKey(Integer id);

    int updateByPrimaryKey(User user);

    User selectByPrimaryKey(Integer id);

    List<User> selectList();

    User login(String username, String password);
}

package cn.sxt.service.impl;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import cn.sxt.mapper.UserMapper;
import cn.sxt.pojo.User;
import cn.sxt.service.UserService;
@Service
public class UserServiceImpl implements UserService {
    @Autowired
    private UserMapper userMapper;

    @Override
    public int insert(User user) {
    
        return userMapper.insert(user);
    }

    @Override
    public int deleteByPrimaryKey(Integer id) {
        
        return userMapper.deleteByPrimaryKey(id);
    }

    @Override
    public int updateByPrimaryKey(User user) {
        
        return userMapper.updateByPrimaryKey(user);
    }

    @Override
    public User selectByPrimaryKey(Integer id) {
        
        return userMapper.selectByPrimaryKey(id);
    }

    @Override
    public List<User> selectList() {
    
        return userMapper.selectList();
    }

    @Override
    public User login(String username, String password) {
        
        return userMapper.login(username, password);
    }

}

package cn.sxt.test;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import cn.sxt.pojo.User;
import cn.sxt.service.UserService;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:spring.xml")
public class UserServiceImplTest {
    @Autowired
    private UserService service;

    @Test
    public void testInsert() {
        User user = new User(null, "张三丰", "abc", "135xxxxx", "zhangsan@qq.com");
        service.insert(user);
    }

    @Test
    public void testDeleteByPrimaryKey() {
        service.deleteByPrimaryKey(15);
    }

    @Test
    public void testUpdateByPrimaryKey() {
        User user = new User(15, "张三丰", "abc", "135xxxxx", "zhangsan@qq.com");
        
        service.updateByPrimaryKey(user);
    }

    @Test
    public void testSelectByPrimaryKey() {
service.selectByPrimaryKey(15);
    }

    @Test
    public void testSelectList() {
        service.selectList();
    }

    @Test
    public void testLogin() {
        service.login("", "");
    }

}

package cn.sxt.controller;

import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.List;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;

import cn.sxt.pojo.User;
import cn.sxt.service.UserService;
@Controller
@RequestMapping("/user")
public class UserController {
    @Autowired
   private UserService service;
   
    @RequestMapping("/list")
    public String list(Model m) {
        List<User> selectList = service.selectList();
        m.addAttribute("users",selectList);
        return "user_list";
        
    }
    @RequestMapping("/delete")
    public String delets(Integer id) {
        service.deleteByPrimaryKey(id);
        return "redirect:/user/list.do";
        
    }
    @RequestMapping("/login")
    public String login(String username,String password,Model m,HttpSession session) {
        User user = service.login(username, password);
        
        if(user == null) {
            m.addAttribute("errorMsg", "亲，账号或者密码错误！");
            return "forward:/login.jsp";
        }
        
        
        // 将user对象共享的session中
                session.setAttribute("user", user);
                return "redirect:/user/list.do";
        
    }
    @RequestMapping("/exprot")
    public void exprot(HttpServletResponse response) throws Exception {
        // 1.创建一个工作集
        HSSFWorkbook book = new HSSFWorkbook();
        // 2.创建一个Sheet
        HSSFSheet sheet = book.createSheet();
        // 3.创建一行，从0开始
        HSSFRow createRow = sheet.createRow(0);
        // 3.1创建单元格（列）
        createRow.createCell(0).setCellValue("id");
        createRow.createCell(1).setCellValue("账号");
        createRow.createCell(2).setCellValue("性命");
        createRow.createCell(3).setCellValue("电话");
        createRow.createCell(4).setCellValue("邮箱");
        //循环所有用户信息
        List<User> users = service.selectList();
        for (int i = 0; i < users.size(); i++) {
            //分别获取每一行对象
            User user = users.get(i);
            //创建一列
            HSSFRow createRow2 = sheet.createRow(i+1);
            createRow2.createCell(0).setCellValue(user.getId());
            createRow2.createCell(1).setCellValue(user.getUsername());
            createRow2.createCell(2).setCellValue(user.getPassword());
            createRow2.createCell(3).setCellValue(user.getPhone());
            createRow2.createCell(4).setCellValue(user.getEmail());
        }
        String fileName = "用户信息.xlsx";
        byte[] bytes = fileName.getBytes("UTF-8");
        fileName = new String(bytes, "ISO-8859-1");
        //设置响应头
        response.setHeader("Content-Disposition", "attachment;filename="+fileName);

        //同步数据到本地磁盘形成 Excel 文件
        try {
            //获取响应对象的输出流
            ServletOutputStream outputStream = response.getOutputStream();
            //将excel写给Servlet的输出流对象，输出流最终响应浏览器（下载）
            book.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

package cn.sxt.interceptor;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.web.servlet.HandlerInterceptor;
import org.springframework.web.servlet.ModelAndView;

import cn.sxt.pojo.User;

public class CheckLoginInterceptor implements HandlerInterceptor {

    /*
     * 在控制器方法执行之前执行
     * 返回 true 放行
     * 返回 false 不放行
     * 
     */
    
    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler)
            throws Exception {
        /*
         * 拦截思路，从session中获取 共享的 user对象，
         * 如果有，说明已经登录
         *     放行
         * 如果对象为null，说明没有登录
         *  不放行，跳转到登录让其登录
         */
        //1.从session获取user对象
        User user =(User) request.getSession().getAttribute("user");
        if(user == null) {
            //跳转到登录页面
            //request.getRequestDispatcher("/login.jsp").forward(request, response);
            //重定向
            response.sendRedirect(request.getContextPath()+"/login.jsp");
            return false;
        }
        
        
        return true;
    }
    //postHandle:控制器方法执行后,视图渲染之前执行(可以加入统一的响应信息).
    @Override
    public void postHandle(HttpServletRequest request, HttpServletResponse response, Object handler,
            ModelAndView modelAndView) throws Exception {
        // TODO Auto-generated method stub
        
    }
    //afterCompletion:视图渲染之后执行(处理Controller异常信息,记录操作日志，清理资源等
    @Override
    public void afterCompletion(HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex)
            throws Exception {
        // TODO Auto-generated method stub
        
    }

    
}

jdbc.driverClassName=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT
jdbc.username=root
jdbc.password=gzsxt
jdbc.maxActive=10

# Global logging configuration
log4j.rootLogger=ERROR, stdout
# MyBatis logging configuration...
log4j.logger.cn.sxt.mapper=TRACE
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:p="http://www.springframework.org/schema/p"
    xmlns:mvc="http://www.springframework.org/schema/mvc"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/mvc
        http://www.springframework.org/schema/mvc/spring-mvc.xsd
        ">

    <!-- 开启SpringMVC的注解驱动 -->
    <mvc:annotation-driven />
    <!-- 配置SpringMVC对静态资源的处理 -->
    <mvc:default-servlet-handler />
    <!-- 配置视图解析器 -->
    <bean
        class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <!-- 前缀 -->
        <property name="prefix" value="/WEB-INF/view/" />
        <!-- 后缀 -->
        <property name="suffix" value=".jsp" />
    </bean>


    <!-- 配置springMVC的拦截器 -->
    <mvc:interceptors>
        <!-- 具体拦截器配置 -->
        <mvc:interceptor>
            <!-- 配置拦截的地址 ,只会拦截控制器请求（不会拦截非控制器请求 jsp,html,css,js） /* 只会拦截一级 如 /list.do 
                /delet.do /** 可以拦截多级（两级以及两级以上） /user/list.do /user/delete.do -->
            <mvc:mapping path="/**" />
            <!-- 配置放行的地址 如果放行多个地址 使用 逗号隔开 /xx/a.do,/xxx/b.do,/xxx/c.do -->
            <mvc:exclude-mapping path="/user/login.do" />
            <!-- 拦截器 -->
            <bean class="cn.sxt.interceptor.CheckLoginInterceptor"></bean>
        </mvc:interceptor>

    </mvc:interceptors>

</beans>

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:p="http://www.springframework.org/schema/p"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd
        http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop.xsd
        http://www.springframework.org/schema/tx
        http://www.springframework.org/schema/tx/spring-tx.xsd">

    <!-- 1.配置组件包扫描位置 -->
    <context:component-scan base-package="cn.sxt" />

    <!-- 2.读取数据库连接配置文件 db.properties -->
    <context:property-placeholder
        location="classpath:db.properties" />

    <!-- 3.配置数据库（阿里巴巴连接池） -->
    <bean id="dataSource"
        class="com.alibaba.druid.pool.DruidDataSource"
        init-method="init" destroy-method="close">
        <property name="driverClassName"
            value="${jdbc.driverClassName}" />
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
        <property name="maxActive" value="${jdbc.maxActive}" />
    </bean>

    <!-- 配置SqlSessionFactory MyBatis框架的工厂类 -->
    <bean id="sqlSessionFactory"
        class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <!-- 读取映射文件 -->
        <property name="mapperLocations">
            <array>
                <value>classpath:cn/sxt/mapper/*Mapper.xml</value>
            </array>
        </property>
        <!-- 配置别名包扫描路径 -->
        <property name="typeAliasesPackage" value="cn.sxt.pojo"></property>
    </bean>
    <!-- 创建单个Mapper接口的代理对象 -->
    <!-- <bean id="userMapper" class="org.mybatis.spring.mapper.MapperFactoryBean"> 
        <property name="sqlSessionFactory" ref="sqlSessionFactory"/> <property name="mapperInterface" 
        value="cn.zj.ssm.mapper.UserMapper"/> </bean> -->

    <!-- 使用包扫描创建Mapper接口代理对象 -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <!-- 配置Mapper接口对应的包位置 -->
        <property name="basePackage" value="cn.sxt.mapper"></property>
        <!-- 配置SqlSessionFactory 对象的名称[可选] -->
        <property name="sqlSessionFactoryBeanName"
            value="sqlSessionFactory" />
    </bean>


    <!-- 事务配置 思路： 1，配置事务管理器（事务代理对象），具体对事务处理对象 2，配置事务通知 3，使用aop把事务切入到service层 -->
    <!-- 1，配置事务管理器 -->
    <bean id="transactionManager"
        class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"></property>

    </bean>

    <!-- 2，配置事务通知 -->

    <tx:advice id="txAdvice"
        transaction-manager="transactionManager">
        <tx:attributes>
            <!-- DQL -->
            <tx:method name="select*" read-only="true"
                isolation="REPEATABLE_READ" propagation="REQUIRED" timeout="5" />
            <tx:method name="find*" read-only="true"
                isolation="REPEATABLE_READ" propagation="REQUIRED" timeout="5" />
            <tx:method name="get*" read-only="true"
                isolation="REPEATABLE_READ" propagation="REQUIRED" timeout="5" />
            <tx:method name="query*" read-only="true"
                isolation="REPEATABLE_READ" propagation="REQUIRED" timeout="5" />
            <!-- 其他 DML，DDL -->
            <tx:method name="*" read-only="false"
                isolation="REPEATABLE_READ" propagation="REQUIRED" timeout="5" />
        </tx:attributes>
    </tx:advice>

    <!-- 3，使用aop把事务切入到service层 -->
    <aop:config>
        <!-- 切入点 -->
        <aop:pointcut
            expression="execution(* cn.sxt.service..*.*(..))" id="pt" />
        <!-- 配置切面 = 切入点+通知 -->
        <aop:advisor advice-ref="txAdvice" pointcut-ref="pt" />
    </aop:config>
    <!-- 引入springmvc.xml -->
    <import resource="springmvc.xml" />

</beans>

<?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" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">
  <!-- 配置SpringMVC的前端控制器 -->
  <servlet>
  <servlet-name>MVC</servlet-name>
  <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
 <init-param>
 <param-name>contextConfigLocation</param-name>
 <param-value>classpath:spring*.xml</param-value>
 <!-- 实际开发中 spring 相关配置文件可能有多个
              spring.xml , springmvc.xml 
              如何一次性读取多个配置文件
              方案一 ：使用通配符星号 （*）,就会读取所有以spring开头的文件
                  classpath：spring*.xml
              方案二：如果有多个spring相关的配置文件
                  在配置文件中使用<import resource="springmvc.xml"/>
                  再读取那个引入了其他文件的主配置文件即可
           -->
 </init-param>
 <load-on-startup>1</load-on-startup>
  </servlet>
  <servlet-mapping>
  <servlet-name>MVC</servlet-name>
  <url-pattern>*.do</url-pattern>
  </servlet-mapping>
</web-app>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <!-- 引入jstl标签库的核心包 -->    
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h3>用户列表</h3>

<table border="1" style="width: 700px;border-collapse: collapse;">
    <tr>
        <th>id</th>
        <th>username</th>
        <th>password</th>
        <th>phone</th>
        <th>email</th>
        <th>操作</th>
    </tr>
    
    <c:forEach items="${users}" var="u">
        <tr>
            <td>${u.id}</td>
            <td>${u.username}</td>
            <td>${u.password}</td>
            <td>${u.phone}</td>
            <td>${u.email}</td>
            <td>
                <!--
                    javascript:void(0); js中禁止 a超链接的跳转
                  -->
                <a href="javascript:void(0);" onclick="deleteUserById(${u.id});">删除</a>
                <a href="#">修改</a>
            </td>
        </tr>
    </c:forEach>
    
</table>
<a href="${pageContext.request.contextPath }/user/exprot.do">hhhh</a>
<script type="text/javascript">
    
    function deleteUserById(userId){
        
        if(confirm("您确定要删除此条数据吗？")){
            window.location.href="${pageContext.request.contextPath}/user/delete.do?id="+userId;
        }
        
    }
    
</script>
</body>
</html>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h3>登录页面</h3>
<span style="color: red">${errorMsg}</span>
<form action="${pageContext.request.contextPath}/user/login.do" method="post">
    
    账号：<input name="username"><br>
    密码：<input name="password" type="password"><br>
    <button type="submit">登录</button>
</form>
</body>
</html>