Spring MVC实现mysql数据库增删改查完整实例

package bean;public class Student {  private Integer id;//学生id  private String name;//学生姓名  private Double javaScore;//java成绩  private Double htmlScore;//html成绩  private Double cssScore;//css成绩  private Double totalScore;  public Integer getId() {  return id;  }  public void setId(Integer id) {  this.id = id;  }  public String getName() {  return name;  }  public void setName(String name) {  this.name = name;  }  public Double getJavaScore() {  return javaScore;  }  public void setJavaScore(Double javaScore) {  this.javaScore = javaScore;  }  public Double getHtmlScore() {  return htmlScore;  }  public void setHtmlScore(Double htmlScore) {  this.htmlScore = htmlScore;  }  public Double getCssScore() {  return cssScore;  }  public void setCssScore(Double cssScore) {  this.cssScore = cssScore;  }  public Double getTotalScore() {  return totalScore;  }  public void setTotalScore(Double totalScore) {  this.totalScore = totalScore;  }}

package dao;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Types;import java.util.List;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.RowMapper;import bean.Student;public class StudentDao {  /**   * @Fields jdbcTemplate : TODO   */  private JdbcTemplate jdbcTemplate;  /**   * spring提供的类   *    * @param jdbcTemplate   *      返回值类型： void   * @author janinus   */  public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {  this.jdbcTemplate = jdbcTemplate;  }  /**   * 查询所有学生   *    * @return 返回值类型： List<Student>   * @author janinus   */  public List<Student> queryAll() {  String sql = "select id,name,javaScore,htmlScore,cssScore from student";  //将查询结果映射到Student类中，添加到list中，并返回  return jdbcTemplate.query(sql, new StudentMapper());  }  /**   * 通过姓名查询   *    * @param name   * @return 返回值类型： List<Student>   * @author janinus   */  public List<Student> queryByName(String name) {  String sql = "select id,name,javaScore,htmlScore,cssScore from student where name like '%" + name + "%'";  return jdbcTemplate.query(sql, new StudentMapper());  }  /**   * 添加学生   *    * @param student   * @return 返回值类型： boolean   * @author janinus   */  public boolean addStu(Student student) {  String sql = "insert into student(id,name,javaScore,htmlScore,cssScore) values(0,?,?,?,?)";  return jdbcTemplate.update(sql,    new Object[] { student.getName(), student.getJavaScore(), student.getHtmlScore(),      student.getCssScore() },    new int[] { Types.VARCHAR, Types.DOUBLE, Types.DOUBLE, Types.DOUBLE }) == 1;  }  /**   * 删除学生   *    * @param id   * @return 返回值类型： boolean   * @author janinus   */  public boolean deleteStu(Integer id) {  String sql = "delete from student where id = ?";  return jdbcTemplate.update(sql, id) == 1;  }  /**   * 更新学生信息   *    * @param student   * @return 返回值类型： boolean   * @author janinus   */  public boolean updateStu(Student student) {  String sql = "update student set name=? ,javaScore=?,htmlScore = ? ,cssScore = ? where id = ?";  Object stuObj[] = new Object[] { student.getName(), student.getJavaScore(), student.getHtmlScore(),    student.getCssScore(), student.getId() };  return jdbcTemplate.update(sql, stuObj) == 1;  }  /**   * 返回总成绩前n名学生   *    * @param num   * @return 返回值类型： List<Student>   * @author janinus   */  public List<Student> topNum(int num) {  String sql = "select id,name,javaScore+htmlScore+cssScore from student order by javaScore+htmlScore+cssScore desc ,name asc limit ?";  return jdbcTemplate.query(sql, new RowMapper<Student>() {    @Override    public Student mapRow(ResultSet rs, int rowNum) throws SQLException {    // TODO Auto-generated method stub    Student student = new Student();    student.setId(rs.getInt(1));    student.setName(rs.getString(2));    student.setTotalScore(rs.getDouble(3));    return student;    }  }, num);  }  /**   *    * StudentMapper数据库映射   *    * @ClassName StudentMapper   * @author janinus   * @date 2017年6月27日   * @Version V1.0   */  class StudentMapper implements RowMapper<Student> {  @Override  public Student mapRow(ResultSet rs, int rowNum) throws SQLException {    // TODO Auto-generated method stub    Student student = new Student();    student.setId(rs.getInt(1));    student.setName(rs.getString(2));    student.setJavaScore(rs.getDouble(3));    student.setHtmlScore(rs.getDouble(4));    student.setCssScore(rs.getDouble(5));    return student;  }  }}

package controller;import org.springframework.context.ApplicationContext;import org.springframework.context.support.ClassPathXmlApplicationContext;import org.springframework.stereotype.Controller;import org.springframework.ui.Model;import org.springframework.web.bind.annotation.RequestMapping;import bean.Student;import dao.StudentDao;@Controllerpublic class StudentController {  /**   *    * 从数据库中获取全部学生信息，将数据返回给主页index,jsp   *    * @param model   * @return 返回值类型： String   * @author janinus   */  @RequestMapping(value = "/all")  public String queryAll(Model model) {  ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");  //从ioc容器中获取dao  StudentDao dao = (StudentDao) context.getBean("dao");  model.addAttribute("students", dao.queryAll());  model.addAttribute("tops", dao.topNum(3));  return "index.jsp";  }  /**   * 通过姓名查找学生，使用模糊查找，将结果返回给index.jsp   *    * @param name   * @param model   * @return 返回值类型： String   * @author janinus   */  @RequestMapping(value = "/queryByName")  public String queryByName(String name, Model model) {  ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");  //从ioc容器中获取dao  StudentDao dao = (StudentDao) context.getBean("dao");  model.addAttribute("students", dao.queryByName(name));  model.addAttribute("tops", dao.topNum(3));  return "index.jsp";  }  /**   * 添加新学生，并将结果返回给all页面，由all转发到主页   * @param name   * @param javaScore   * @param htmlScore   * @param cssScore   * @param model   * @return 返回值类型： String   * @author janinus   */  @RequestMapping(value = "/add")  public String addStu(String name, String javaScore, String htmlScore, String cssScore, Model model) {  ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");  StudentDao dao = (StudentDao) context.getBean("dao");  Student student = new Student();  student.setName(name);  student.setJavaScore(Double.parseDouble(javaScore));  student.setHtmlScore(Double.parseDouble(htmlScore));  student.setCssScore(Double.parseDouble(cssScore));  boolean result = dao.addStu(student);  if (result)    model.addAttribute("msg", "<script>alert('添加成功！')</script>");  else    model.addAttribute("msg", "<script>alert('添加成功！')</script>");  return "all";  }  /**   * 通过id删除学生   * @param id   * @param model   * @return 返回值类型： String   * @author janinus   */  @RequestMapping(value = "/deleteById")  public String deleteById(String id, Model model) {  ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");  StudentDao dao = (StudentDao) context.getBean("dao");  boolean result = dao.deleteStu(Integer.parseInt(id));  if (result)    model.addAttribute("msg", "<script>alert('删除成功！')</script>");  else    model.addAttribute("msg", "<script>alert('删除成功！')</script>");  return "all";  }  /**   *    * @param id   * @param name   * @param javaScore   * @param htmlScore   * @param cssScore   * @param model   * @return 返回值类型： String   * @author janinus   */  @RequestMapping(value = "/update")  public String updateStu(String id, String name, String javaScore, String htmlScore, String cssScore, Model model) {  ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");  StudentDao dao = (StudentDao) context.getBean("dao");  Student student = new Student();  student.setId(Integer.parseInt(id));  student.setName(name);  student.setJavaScore(Double.parseDouble(javaScore));  student.setHtmlScore(Double.parseDouble(htmlScore));  student.setCssScore(Double.parseDouble(cssScore));  boolean result = dao.updateStu(student);  if (result)    model.addAttribute("msg", msg("修改成功"));  else    model.addAttribute("msg", msg("修改失败"));  return "all";  }  /**   * 要弹出的页面消息   * @param msg   * @return 返回值类型： String   * @author janinus   */  public String msg(String msg) {  return "<script>alert('" + msg + "')</script>";  }}

<%@ page language="java" contentType="text/html; charset=UTF-8"  pageEncoding="UTF-8"%>  <%@ taglib prefix="fn"       uri="http://java.sun.com/jsp/jstl/functions" %>  <%@ taglib prefix="c"       uri="http://java.sun.com/jsp/jstl/core" %><!DOCTYPE html><html lang="en"><head>  <meta charset="UTF-8">  <title>学生管理</title></head><style type="text/css">  body{    text-align: center;  }  .all{    width:40%;    margin: 20px 100px;    text-align: center;    height: 300px;    float: left;  }  table{    width: 80%;    margin: 20px auto;    font-size: 14px;    overflow: auto;  }  #tab02{    width: 80%;    margin: 20px auto;    font-size: 14px;  }  table th,table td{    border-bottom: 1px #000 solid;    line-height: 23px;  }  #edit_comm{    width: 500px;    margin: 20px auto;    border-left: 3px solid #000;    display: none;  }  #add_comm{    width: 500px;    margin: 20px auto;    border-left: 3px solid #000;  }  #all_comm{    height:600px;  }  .edit_stu{    width:200px;    height: 30px;    background: #fff;    font-family: "微软雅黑 Light", "Arial Black";    font-size: 18px;    border: none;    border-bottom: 1px solid #000;    margin: 20px 10px;  }</style><script src="http://code.jquery.com/jquery-latest.js"></script><script type="text/javascript">  $(function(){    $("#cancel").click(function(){      $("#add_comm").fadeIn();      $("#edit_comm").fadeOut();    })    $("input").addClass("edit_stu");  })  function refush(){     window.location.href="all" rel="external nofollow" rel="external nofollow" ;  }  function add_reg(){     var name = $("#add_edit_name").val();    var javaScore = $("#add_edit_java").val();    var htmlScore = $("#add_edit_html").val();    var cssScore=$("#add_edit_css").val();    var nameNot = name!=null&&name!='';    var javaScoreNot = javaScore!=null && javaScore != '';    var htmlScoreNot = htmlScore!=null && htmlScore !='';    var cssScoreNot = cssScore !=null && cssScore != '';    if(nameNot&&javaScoreNot&&htmlScoreNot&&cssScoreNot)    return true;    else      return false;  }  function delete_stu(id){     var result = confirm("是否删除？");     if(result)       window.location.href="deleteById?id=" rel="external nofollow" +id;  }  function edit_stu(id){    var name = $("#name"+id).text();    var java = $("#java"+id).text();    var html = $("#html"+id).text();    var css = $("#css"+id).text();    $("#edit_id").val( id);    $("#edit_name").val(name);    $("#edit_java").val(java);    $("#edit_html").val(html);    $("#edit_css").val(css);    $("#add_comm").fadeOut();    $("#edit_comm").fadeIn();  }</script><body>${msg }<h1 align="center">学生管理</h1><div id="all_comm" class="all" >  <h2>所有学生</h2>  <table id="items" >    <tr>    <td>id</td>    <td>名称</td>    <td>java分数</td>    <td>html分数</td>    <td>css分数</td>      <td>操作</td>    </tr>    <c:forEach items="${students }" var="student" >      <tr>      <td id="id${student.id }">${student.id }</td>      <td id="name${student.id }">${student.name }</td>      <td id="java${student.id}">${student.javaScore }</td>      <td id="html${student.id }">${student.htmlScore }</td>      <td id="css${student.id}">${student.cssScore }</td>      <td ><a onclick="delete_stu(${student.id})">删除</a>|<a onclick="edit_stu(${student.id})">编辑</a></td>      </tr>    </c:forEach>  </table>  <table id="tab02">  <h2>前三名</h2>  <tr>  <td>排名</td>  <td>id</td>  <td>姓名</td>  <td>总分数</td>  </tr>   <c:forEach items="${tops }" var="student" varStatus="i">      <tr>      <td>第${i.index+1 }名</td>      <td id="id${student.id }t">${student.id }</td>      <td>${student.name }</td>      <td id="name${student.id }t">${student.totalScore }</td>      </tr>    </c:forEach>    </table>  如不显示请：<a onclick="refush()" >点此刷新</a></div><div id="add_comm" class="all">  <h2>查找学生</h2>  <form action="queryByName" method="post" >    <input type="text" placeholder="学生姓名" name="name" >    <input type="submit" value="查找学生" >  </form>  <h2 id="edit_title">添加学生</h2>  <form action="add" method="post" >  <input type="text" placeholder="学生姓名" name="name" />  <input type="text" placeholder="java成绩" name="javaScore" />  <input type="text" placeholder="html成绩" name="htmlScore" />  <input type="text" placeholder="css成绩" name="cssScore" />  <input type="submit" value="确定添加" />  </form></div><div id="edit_comm" class="all">  <h2 id="edit_title">编辑学生</h2>  <form action="update" method="post">  <input type="text" placeholder="要修改的id为" id="edit_id" name="id" value="要修改的id为" readonly="readonly"/><br>   <input type="text" placeholder="学生姓名" id="edit_name" name="name" />  <input type="text" placeholder="java成绩" id="edit_java" name="javaScore" >  <input type="text" placeholder="html成绩" id="edit_html" name="htmlScore" />  <input type="text" placeholder="css成绩" id="edit_css" name="cssScore" />  <input type="submit" value="确定修改" />  <input type="button" value="取消修改" id="cancel" class="edit_stu"/>  </form></div></body></html>

<%@ page language="java" contentType="text/html; charset=UTF-8"  pageEncoding="UTF-8"%><!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><title>Insert title here</title></head><body><h1 align="center"><a href="all" rel="external nofollow" rel="external nofollow" >进入主页</a></h1></body></html>

<?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:context="http://www.springframework.org/schema/context"     xmlns:tx="http://www.springframework.org/schema/tx"     xmlns:aop="http://www.springframework.org/schema/aop"    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd    http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd    http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd"          default-autowire="byName" default-lazy-init="true" >    <!--数据库数据源配置-->    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">      <!--加载驱动类-->      <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>      <!--数据库访问地址-->      <property name="url" value="jdbc:mysql://localhost:3306/test"></property>      <!--数据库访问用户名-->      <property name="username" value="root"></property>      <!--数据库访问密码-->      <property name="password" value="123123"></property>    </bean>    <!-- spring 提供的数据库事务管理 -->    <bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">      <property name="dataSource" ref="dataSource"></property>      </bean>    <tx:annotation-driven transaction-manager="txManager"/>    <!-- 配置javaBean实体类 -->    <bean id="studentBean" class="bean.Student">      <!--属性自动配置 -->    </bean>    <!--spring提供的数据库访问操作类 -->    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"></bean>    <!-- dao层类 -->    <bean id="dao" class="dao.StudentDao"></bean>    <!-- 控制层类 ，这个配置无效-->    <bean id="controller" class="controller.StudentController">      <property name="dao" ref="dao"></property>    </bean> </beans>

<?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:p="http://www.springframework.org/schema/p"  xmlns:context="http://www.springframework.org/schema/context"  xsi:schemaLocation="    http://www.springframework.org/schema/beans     http://www.springframework.org/schema/beans/spring-beans-3.0.xsd    http://www.springframework.org/schema/context     http://www.springframework.org/schema/context/spring-context-3.0.xsd">  <!-- 自动扫描指定包下的类 -->  <context:component-scan base-package="controller" /></beans>

<?xml version="1.0" encoding="UTF-8"?><web-app xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  xsi:schemaLocation="http://java.sun.com/xml/ns/javaee  http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"><!--配置字符编码过滤器 ，由spring提供 --><filter>  <filter-name>encodingFilter</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></filter><!-- 配置欢迎界面 --><welcome-file-list>  <welcome-file>/all</welcome-file>  <welcome-file>index.html</welcome-file>  <welcome-file>index.htm</welcome-file></welcome-file-list><!-- 配置springmvc servlet --><servlet>  <servlet-name>springMVC</servlet-name>  <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>  <load-on-startup>1</load-on-startup></servlet><servlet-mapping>  <servlet-name>springMVC</servlet-name>  <url-pattern>/</url-pattern></servlet-mapping></web-app>