欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2024-02-25 08:54:10
最近刚学了springmvc框架,感觉确实方便了不少,减少了大量的冗余代码。就自己做了个小项目练练手,这是个初级的springmvc应用的项目,没有用到mybatis,项目...

最近刚学了springmvc框架,感觉确实方便了不少,减少了大量的冗余代码。就自己做了个小项目练练手,这是个初级的springmvc应用的项目,没有用到mybatis,项目功能还算完善,实现了基本的增删改查的功能。

项目环境:

-系统:win10

-开发环境:eclipseoxygenreleasecandidate3(4.7)

-jdk版本:java1.8(121)

-mysql:5.7

-spring:4.0

-tomcat:8.5

用到的技术:

springmvcspringjspjdbcjavabeanjsjstl

访问地址:http://localhost:8080/你的项目名/all

声明:我只是一个刚入门不久的新手,所写代码难免有出错之处,如发现欢迎各位指出,谢谢大家。

下面就贴上详细过程

1.首先创建一个web项目(dynamicwebproject)

项目名字就自己写了,不再详细写

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

2. 这是我的已完成项目结构

我只是为了实现功能,没有用到接口,只用了简单的三个类,bean包下的实体类,dao层数据库访问类,controller层的界面控制类,

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

所有引用的jar包都在/webcontent/web-inf/lib文件夹下,这点与普通的java项目不同。

3. 具体java代码

1.student类,实体类 首先要写一个javabean,我的是student作为javabean,详细代码如下:

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;
  }
}

2. studentdao,数据库访问操作类 然后是dao层即数据访问层的代码,这里使用的是spring封装的一个类(jdbctemplate),里面有一些操作数据库的方法,不用再自己写大量重复代码,只要写sql语句。下面是具体代码:

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;
  }

  }
}

3. studentcontroller ,前后端交互类 最后是与用户交互有关的控制层studentcontroller类,这个类主要用来将前后端联合,实现完整的交互。下面是具体代码:

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;

@controller
public 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>";
  }
}

所有的java代码已经完成,下面只剩下具体的xml配置和前端页面。

4.前端页面

由于是一个简单的小项目,我的js,css都在同一个页面,没有分开,只有两个页面,

1.index.jsp

主页,截图

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

编辑

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

详细代码:

<%@ 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>

2. login.jsp

<%@ 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>

5. 详细文件配置

1. applicationcontext.xml

这是spring的ioc容器的配置文件,用来实现依赖注入,下面是具体代码:

<?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>

2. springmvc-servlet.xml,spring mvc配置类,

为我们实现了servlet的大部分代码,我们只需要写业务实现即可。下面是具体代码

<?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>

3. web.xml

这是web工程的配置文件,下面是主要代码:

<?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>

6.项目总结及附录

这个项目是个我的日常练习项目,为了更加熟练,我把完整的过程又回顾了一遍,又熟悉了很多,

项目用的jar包附录:

除了spring的包外,还有mysql-jbdc的jar包和jstl的jar包

下载地址:

spring框架jar包(可选版本):spring官网

mysql-jdbc.jar(可选版本):mysql官网

jstl.jar(可选版本):maven官方地址

以上就是本文关于spring mvc实现mysql数据库增删改查完整实例的全部内容,希望对大家有所帮助。感兴趣的朋友可以继续参阅本站:

spring之web模块配置详解

springmvc rest风格介绍及实现代码示例

springmvc拦截器实现单点登录

如有不足之处,欢迎留言指出。感谢朋友们对本站的支持!