Spring MVC實現mysql數據庫增刪改查完整實例

最近剛學了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)

項目名字就自己寫了，不再詳細寫

2. 這是我的已完成項目結構

我只是為了實現功能，沒有用到接口，只用了簡單的三個類，bean包下的實體類，dao層數據庫訪問類，controller層的界面控制類，

所有引用的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;@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>"; }}

所有的java代碼已經完成，下面只剩下具體的xml配置和前端頁面。

4.前端頁面

由於是一個簡單的小項目，我的js，css都在同一個頁面，沒有分開，只有兩個頁面，

1.index.jsp

主頁，截圖

編輯

詳細代碼：

 <%@ 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" > <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"> <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"> <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"/> </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"> <!--加載驅動類--> <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"> <property name="dataSource" ref="dataSource"></property> </bean> <tx:annotation-driven transaction-manager="txManager"/> <!-- 配置javaBean實體類--> <bean id="studentBean"> <!--屬性自動配置--> </bean> <!--spring提供的數據庫訪問操作類--> <bean id="jdbcTemplate"></bean> <!-- dao層類--> <bean id="dao"></bean> <!-- 控制層類，這個配置無效--> <bean id="controller"> <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攔截器實現單點登錄

如有不足之處，歡迎留言指出。感謝朋友們對本站的支持！