Spring MVC實(shí)現(xiàn)mysql數(shù)據(jù)庫增刪改查完整實(shí)例
最近剛學(xué)了springmvc框架,感覺確實(shí)方便了不少,減少了大量的冗余代碼。就自己做了個(gè)小項(xiàng)目練練手,這是個(gè)初級的springmvc應(yīng)用的項(xiàng)目,沒有用到mybatis,項(xiàng)目功能還算完善,實(shí)現(xiàn)了基本的增刪改查的功能。
項(xiàng)目環(huán)境:
-系統(tǒng):win10
-開發(fā)環(huán)境:eclipseOxygenReleaseCandidate3(4.7)
-jdk版本:java1.8(121)
-mysql:5.7
-spring:4.0
-tomcat:8.5
用到的技術(shù):
springmvcspringjspjdbcjavaBeanjsjstl
訪問地址:http://localhost:8080/你的項(xiàng)目名/all
聲明:我只是一個(gè)剛?cè)腴T不久的新手,所寫代碼難免有出錯(cuò)之處,如發(fā)現(xiàn)歡迎各位指出,謝謝大家。
下面就貼上詳細(xì)過程
1.首先創(chuàng)建一個(gè)web項(xiàng)目(DynamicWebProject)
項(xiàng)目名字就自己寫了,不再詳細(xì)寫

2. 這是我的已完成項(xiàng)目結(jié)構(gòu)
我只是為了實(shí)現(xiàn)功能,沒有用到接口,只用了簡單的三個(gè)類,bean包下的實(shí)體類,dao層數(shù)據(jù)庫訪問類,controller層的界面控制類,

所有引用的jar包都在/WebContent/WEB-INF/lib文件夾下,這點(diǎn)與普通的java項(xiàng)目不同。
3. 具體java代碼
1.Student類,實(shí)體類 首先要寫一個(gè)javaBean,我的是Student作為javaBean,詳細(xì)代碼如下:
package bean;
public class Student {
private Integer id;//學(xué)生id
private String name;//學(xué)生姓名
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,數(shù)據(jù)庫訪問操作類 然后是dao層即數(shù)據(jù)訪問層的代碼,這里使用的是spring封裝的一個(gè)類(JdbcTemplate),里面有一些操作數(shù)據(jù)庫的方法,不用再自己寫大量重復(fù)代碼,只要寫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;
}
/**
* 查詢所有學(xué)生
*
* @return 返回值類型: List<Student>
* @author janinus
*/
public List<Student> queryAll() {
String sql = "select id,name,javaScore,htmlScore,cssScore from student";
//將查詢結(jié)果映射到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());
}
/**
* 添加學(xué)生
*
* @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;
}
/**
* 刪除學(xué)生
*
* @param id
* @return 返回值類型: boolean
* @author janinus
*/
public boolean deleteStu(Integer id) {
String sql = "delete from student where id = ?";
return jdbcTemplate.update(sql, id) == 1;
}
/**
* 更新學(xué)生信息
*
* @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名學(xué)生
*
* @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數(shù)據(jù)庫映射
*
* @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 ,前后端交互類 最后是與用戶交互有關(guān)的控制層StudentController類,這個(gè)類主要用來將前后端聯(lián)合,實(shí)現(xiàn)完整的交互。下面是具體代碼:
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 {
/**
*
* 從數(shù)據(jù)庫中獲取全部學(xué)生信息,將數(shù)據(jù)返回給主頁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";
}
/**
* 通過姓名查找學(xué)生,使用模糊查找,將結(jié)果返回給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";
}
/**
* 添加新學(xué)生,并將結(jié)果返回給all頁面,由all轉(zhuǎn)發(fā)到主頁
* @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刪除學(xué)生
* @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代碼已經(jīng)完成,下面只剩下具體的xml配置和前端頁面。
4.前端頁面
由于是一個(gè)簡單的小項(xiàng)目,我的js,css都在同一個(gè)頁面,沒有分開,只有兩個(gè)頁面,
1.index.jsp
主頁,截圖

編輯

詳細(xì)代碼:
<%@ 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>學(xué)生管理</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">學(xué)生管理</h1>
<div id="all_comm" class="all" >
<h2>所有學(xué)生</h2>
<table id="items" >
<tr>
<td>id</td>
<td>名稱</td>
<td>java分?jǐn)?shù)</td>
<td>html分?jǐn)?shù)</td>
<td>css分?jǐn)?shù)</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>總分?jǐn)?shù)</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()" >點(diǎn)此刷新</a>
</div>
<div id="add_comm" class="all">
<h2>查找學(xué)生</h2>
<form action="queryByName" method="post" >
<input type="text" placeholder="學(xué)生姓名" name="name" >
<input type="submit" value="查找學(xué)生" >
</form>
<h2 id="edit_title">添加學(xué)生</h2>
<form action="add" method="post" >
<input type="text" placeholder="學(xué)生姓名" 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">編輯學(xué)生</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="學(xué)生姓名" 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" >進(jìn)入主頁</a></h1> </body> </html>
5. 詳細(xì)文件配置
1. applicationContext.xml
這是spring的ioc容器的配置文件,用來實(shí)現(xiàn)依賴注入,下面是具體代碼:
<?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" >
<!--數(shù)據(jù)庫數(shù)據(jù)源配置-->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<!--加載驅(qū)動類-->
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<!--數(shù)據(jù)庫訪問地址-->
<property name="url" value="jdbc:mysql://localhost:3306/test"></property>
<!--數(shù)據(jù)庫訪問用戶名-->
<property name="username" value="root"></property>
<!--數(shù)據(jù)庫訪問密碼-->
<property name="password" value="123123"></property>
</bean>
<!-- spring 提供的數(shù)據(jù)庫事務(wù)管理 -->
<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"></property>
</bean>
<tx:annotation-driven transaction-manager="txManager"/>
<!-- 配置javaBean實(shí)體類 -->
<bean id="studentBean" class="bean.Student">
<!--屬性自動配置 -->
</bean>
<!--spring提供的數(shù)據(jù)庫訪問操作類 -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"></bean>
<!-- dao層類 -->
<bean id="dao" class="dao.StudentDao"></bean>
<!-- 控制層類 ,這個(gè)配置無效-->
<bean id="controller" class="controller.StudentController">
<property name="dao" ref="dao"></property>
</bean>
</beans>
2. springMVC-servlet.xml,spring mvc配置類,
為我們實(shí)現(xiàn)了servlet的大部分代碼,我們只需要寫業(yè)務(wù)實(shí)現(xiàn)即可。下面是具體代碼
<?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.項(xiàng)目總結(jié)及附錄
這個(gè)項(xiàng)目是個(gè)我的日常練習(xí)項(xiàng)目,為了更加熟練,我把完整的過程又回顧了一遍,又熟悉了很多,
項(xiàng)目用的jar包附錄:
除了spring的包外,還有mysql-jbdc的jar包和jstl的jar包
下載地址:
spring框架jar包(可選版本):spring官網(wǎng)
mysql-jdbc.jar(可選版本):MySQL官網(wǎng)
jstl.jar(可選版本):maven官方地址
以上就是本文關(guān)于Spring MVC實(shí)現(xiàn)mysql數(shù)據(jù)庫增刪改查完整實(shí)例的全部內(nèi)容,希望對大家有所幫助。感興趣的朋友可以繼續(xù)參閱本站:
springmvc Rest風(fēng)格介紹及實(shí)現(xiàn)代碼示例
SpringMVC攔截器實(shí)現(xiàn)單點(diǎn)登錄
如有不足之處,歡迎留言指出。感謝朋友們對本站的支持!
相關(guān)文章
Java NIO Path接口和Files類配合操作文件的實(shí)例
下面小編就為大家分享一篇Java NIO Path接口和Files類配合操作文件的實(shí)例,具有很好的參考價(jià)值,希望對大家有所幫助。一起跟隨小編過來看看吧2017-11-11
springboot使用jasypt對配置文件加密加密數(shù)據(jù)庫連接的操作代碼
這篇文章主要介紹了springboot使用jasypt對配置文件加密加密數(shù)據(jù)庫連接的操作代碼,本文給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友參考下吧2024-01-01
JavaCV使用ffmpeg實(shí)現(xiàn)錄屏功能
這篇文章主要介紹了JavaCV如何使用ffmpeg實(shí)現(xiàn)錄屏功能,本文通過實(shí)例代碼給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2023-06-06
Java實(shí)現(xiàn)導(dǎo)出pdf格式文件的示例代碼
這篇文章主要為大家詳細(xì)介紹了Java實(shí)現(xiàn)導(dǎo)出pdf格式文件的相關(guān)知識,文中的示例代碼講解詳細(xì),感興趣的小伙伴可以跟隨小編一起學(xué)習(xí)一下2024-02-02

