首先是设计思路，对于数据库的增删改查，我们借助Ecilipse来进行前端和后端的编写。Ecilipse是可以进行java web项目的操作的。
前端，我们选择用使用jsp，所谓的jsp就是可以嵌入其他语言的html,各种标识语言和html语法一致，但为了在本界面检测各种信息的正确性，需要嵌入java的语句进行判断。
对于数据库的增删改查，我们使用sql语句，并且导入了相应的mysql的jar包，方便我们用java对数据库进行操作。
整体来说，我们编写使用了MVC模式(model-view-controler)来实现各种设计。具体的解释不再赘述，下面是一些效果图：
(主界面)
（添加信息界面，即增删改查中的增）
（修改界面，即增删改查中的改）
（删除界面，即增删改查中的删）
（查询界面，即查。该界面设计了一个综合查询功能，即可以进行多条件的综合查询）
下面是各个界面和后端的代码：
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>石家庄铁道大学青年志愿者服务网</title>
</head>
<body>
<div align="center" style="color:#6C3365;position:relative;top:85px;font-size:25px">
<p>青年志愿服务网</p>
</div>
<div align="center" style="position:relative;top:100px">
<button onclick="window.location.href='add.jsp'">志愿者信息登记</button>
</div>
<div align="center" style="position:relative;top:110px">
<button onclick="window.location.href='revise.jsp'">修改志愿者信息</button>
</div>
<div align="center" style="position:relative;top:120px;">
<button onclick="window.location.href='delete.jsp'">删除志愿者信息</button>
</div>
<div align="center" style="position:relative;top:130px">
<button onclick="window.location.href='search.jsp'">查询志愿者信息</button>
</div>
<div align="center" style="position:relative;top:140px">
<button onclick="window.location.href='Servlet?method=show'">志愿者信息浏览</button>
</div>
<div align="center" style="color:#6C3365;position:relative;top:150px">
<p>Designed By wushen</p>
</div>
</body>
</html>
主界面代码
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>志愿者信息录取界面</title>
<STYLE TYPE="text/css">
<!--
table{margin:auto}
-->
</STYLE>
<script type="text/javascript">
/*表单校验*/
function check() {
f=0;
var name = document.getElementById("name").value;
if(name.length!=0){
f++;
}
else {alert("请输入姓名!");return false;}
var sex=document.getElementById("sex").value;
if(sex.length!=0){
f++;
}
else {alert("请输入性别!");return false;}
var nation=document.getElementById("nation").value;
if(nation.length!=0){
f++;
}
else {alert("请输入民族!");return false;}
var time =document.getElementById("time").value;
if(time.length!=0){
f++;
}
else {alert("请输入注册时间!");return false;}
var political =document.getElemById("political").value;
if(political.length!=0||political.value!="--请选择--"){
f++;
}
else {alert("请选择政治面貌!");return false;}
if(f>=6){
alert("添加志愿者信息成功!");
return true;
}
else{
alert("添加失败,请检查信息是否正确!");
return false;
}
} </script>
</head>
<body>
<h1>添加志愿者信息界面</h1>
<p align="right"><a href="main.jsp">返回主界面</a></p><hr>
<form action="Servlet?method=add" method="post" onsubmit="return check()"><br/><br/><br/><br/><br/><br/><br/>
<table frame= "box">
<tr>
<td>姓名</td>
<td><input type="text" id="name" name="name" value="" ></td>
</tr>
<tr>
<td>性别</td>
<td>
<input type="radio" name="sex" value="男">男
<input type="radio" name="sex" value="女">女
</td>
</tr>
<tr>
<td>民族</td>
<td><input type="text" id="nation" name="nation" value=""></td>
</tr>
<tr>
<td>注册时间</td>
<td><input type="text" id="time" name="time" value=""></td>
</tr>
<tr>
<td>年龄</td>
<td><input type="text" id="year" name="year" value=""></td>
</tr>
<tr>
<td>政治面貌:</td>
<td>
<select id="political" name="political" >
<option>--请选择--</option>
<option value="群众">群众</option>
<option value="共青团员">共青团员</option>
<option value="中*员">中*员</option>
</select>
</td>
</tr>
<tr>
<td>服务类别(最多四项)</td>
<td>
<div>
<input type="checkbox" name="type" value="扶危济贫">扶危济贫
<input type="checkbox" name="type" value="敬老助残">敬老助残
<input type="checkbox" name="type" value="社区服务">社区服务
<input type="checkbox" name="type" value="秩序维护">秩序维护
<input type="checkbox" name="type" value="文体服务">文体服务
</div>
<div>
<input type="checkbox" name="type" value="环境保护">环境保护
<input type="checkbox" name="type" value="治安防范">治安防范
<input type="checkbox" name="type" value="医疗救治">医疗救治
<input type="checkbox" name="type" value="法律援助">法律援助
<input type="checkbox" name="type" value="大型活动">大型活动
</div>
<div>
<input type="checkbox" name="type" value="心理疏导">心理疏导
<input type="checkbox" name="type" value="精神抚慰">精神抚慰
<input type="checkbox" name="type" value="支教支医">支教支医
<input type="checkbox" name="type" value="科学普及">科学普及
<input type="checkbox" name="type" value="应急救援">应急救援
</div>
<div>
<input type="checkbox" name="type" value="便民服务">便民服务
<input type="checkbox" name="type" value="民事调解">民事调解
<input type="checkbox" name="type" value="文明引导">文明引导
<input type="checkbox" name="type" value="安全生产">安全生产
<input type="checkbox" name="type" value="禁毒宣传">禁毒宣传
</div>
</td>
</tr>
<tr>
<td><td>
<td> <button type="submit" >提 交</button></td>
</tr>
<tr>
<td colspan="2">
<font color="red">${message}</font>
</td>
</tr>
</table>
</form>
</body>
</html>
添加信息界面
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>志愿者信息修改界面</title>
</head>
<body>
<h1 align="left">按姓名查询并修改界面</h1><p align="right"><a href="main.jsp">返回主界面</a></p><hr>
<form action="Servlet?method=ch_u" method="post" >
<table align="center" bgcolor="#FFFFFF">
<tr>
<td>
姓名
</td>
<td >
<input type="text" name="name" id="name">
</td>
</tr>
<tr>
<td colspan="2">
<input class="btn btn-default" type="submit" value="查询">
</td>
</tr>
<tr>
<td colspan="2">
<font color="green">${message}</font>
</td>
</tr>
</table>
</form>
<c:if test="${message eq '查询成功'}">
<form action="Servlet?method=revise" method="post" onsubmit="return check()"><br/><br/><br/><br/><br/><br/><br/>
<table frame= "box">
<tr>
<td>姓名</td>
<td><input type="text" id="name" name="name" value="${U.name}" ></td>
</tr>
<tr>
<td>性别</td>
<td>
<input type="radio" name="sex" value="男">男
<input type="radio" name="sex" value="女">女
</td>
</tr>
<tr>
<td>民族</td>
<td><input type="text" id="nation" name="nation" value="${U.nation}"></td>
</tr>
<tr>
<td>注册时间</td>
<td><input type="text" id="time" name="time" value="${U.time}"></td>
</tr>
<tr>
<td>年龄</td>
<td><input type="text" id="year" name="year" value="${U.year}"></td>
</tr>
<tr>
<td>政治面貌:</td>
<td>
<select id="political" name="political" >
<option value="${U.political}"></option>
<option value="群众">群众</option>
<option value="共青团员">共青团员</option>
<option value="中*员">中*员</option>
</select>
</td>
</tr>
<tr>
<td>服务类别(最多四项)</td>
<td>已选:${U.type}</td><br>
<td>
<input type="checkbox" name="type" value="扶危济贫">扶危济贫
<input type="checkbox" name="type" value="敬老助残">敬老助残
<input type="checkbox" name="type" value="社区服务">社区服务
<input type="checkbox" name="type" value="秩序维护">秩序维护
<input type="checkbox" name="type" value="文体服务">文体服务
<input type="checkbox" name="type" value="环境保护">环境保护
<input type="checkbox" name="type" value="治安防范">治安防范
<input type="checkbox" name="type" value="医疗救治">医疗救治
<input type="checkbox" name="type" value="法律援助">法律援助
<input type="checkbox" name="type" value="大型活动">大型活动
<input type="checkbox" name="type" value="心理疏导">心理疏导
<input type="checkbox" name="type" value="精神抚慰">精神抚慰
<input type="checkbox" name="type" value="支教支医">支教支医
<input type="checkbox" name="type" value="科学普及">科学普及
<input type="checkbox" name="type" value="应急救援">应急救援
<input type="checkbox" name="type" value="便民服务">便民服务
<input type="checkbox" name="type" value="民事调解">民事调解
<input type="checkbox" name="type" value="文明引导">文明引导
<input type="checkbox" name="type" value="安全生产">安全生产
<input type="checkbox" name="type" value="禁毒宣传">禁毒宣传
</td>
</tr>
<tr>
<td><td>
<td> <button type="submit" >修 改</button></td>
</tr>
<tr>
<td colspan="2">
<font color="green">${message}</font>
</td>
</tr>
</table>
</form>
</c:if>
</body>
</html>
修改信息界面
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>志愿者信息删除界面</title>
</head>
<body>
<h1 align="left">按姓名查询并删除界面</h1><p align="right"><a href="main.jsp">返回主界面</a></p><hr>
<form action="Servlet?method=d_u" method="post" >
<table align="center" bgcolor="#FFFFFF">
<tr>
<td>
姓名
</td>
<td >
<input type="text" name="name" id="name">
</td>
</tr>
<tr>
<td colspan="2">
<input class="btn btn-default" type="submit" value="查询">
</td>
</tr>
<tr>
<td colspan="2">
<font color="red">${message}</font>
</td>
</tr>
</table>
</form>
<c:if test="${message eq '查询成功'}">
<form action="Servlet?method=delete" method="post" onSubmit="return confirm('真的要删除吗?');"><br/><br/><br/><br/><br/><br/><br/>
<table frame= "box">
<tr>
<td>姓名</td>
<td><input type="text" id="name" name="name" value="${U.name}" ></td>
</tr>
<tr>
<td>性别</td>
<td>
<input type="radio" name="sex" value="男">男
<input type="radio" name="sex" value="女">女
</td>
</tr>
<tr>
<td>民族</td>
<td><input type="text" id="nation" name="nation" value="${U.nation}"></td>
</tr>
<tr>
<td>注册时间</td>
<td><input type="text" id="time" name="time" value="${U.time}"></td>
</tr>
<tr>
<td>年龄</td>
<td><input type="text" id="year" name="year" value="${U.year}"></td>
</tr>
<tr>
<td>政治面貌:</td>
<td>
<select id="political" name="political" >
<option value="${U.political}"></option>
<option value="群众">群众</option>
<option value="共青团员">共青团员</option>
<option value="中*员">中*员</option>
</select>
</td>
</tr>
<tr>
<td>服务类别(最多四项)</td>
<td>已选:${U.type}</td><br>
<td>
<input type="checkbox" name="type" value="扶危济贫">扶危济贫
<input type="checkbox" name="type" value="敬老助残">敬老助残
<input type="checkbox" name="type" value="社区服务">社区服务
<input type="checkbox" name="type" value="秩序维护">秩序维护
<input type="checkbox" name="type" value="文体服务">文体服务
<input type="checkbox" name="type" value="环境保护">环境保护
<input type="checkbox" name="type" value="治安防范">治安防范
<input type="checkbox" name="type" value="医疗救治">医疗救治
<input type="checkbox" name="type" value="法律援助">法律援助
<input type="checkbox" name="type" value="大型活动">大型活动
<input type="checkbox" name="type" value="心理疏导">心理疏导
<input type="checkbox" name="type" value="精神抚慰">精神抚慰
<input type="checkbox" name="type" value="支教支医">支教支医
<input type="checkbox" name="type" value="科学普及">科学普及
<input type="checkbox" name="type" value="应急救援">应急救援
<input type="checkbox" name="type" value="便民服务">便民服务
<input type="checkbox" name="type" value="民事调解">民事调解
<input type="checkbox" name="type" value="文明引导">文明引导
<input type="checkbox" name="type" value="安全生产">安全生产
<input type="checkbox" name="type" value="禁毒宣传">禁毒宣传
</td>
</tr>
<tr>
<td><td>
<td> <button type="submit" >删 除</button></td>
</tr>
<tr>
<td colspan="2">
<font color="blue">${message}</font>
</td>
</tr>
</table>
</form>
</c:if>
</body>
</html>
删除信息界面
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>志愿者信息查找界面</title>
</head>
<body>
<h1 align="left">查询界面</h1><p align="right"><a href="main.jsp">返回主界面</a></p><hr>
<form action="Servlet?method=q_u" method="post" >
<table align="center" bgcolor="#FFFFFF">
<tr>
<td>
姓名
</td>
<td>
<input type="text" name="name" id="name">
</td>
<td>
<select id="type1" name="type1" >
<option>--请选择--</option>
<option value="0">精确查询</option>
<option value="1">模糊查询</option>
</select>
</td>
</tr>
<tr>
<td >
性别
</td>
<td>
<input type="text" name="sex" id="sex" >
</td>
</tr>
<tr>
<td>
民族
</td>
<td>
<input type="text" name="nation" id="nation">
</td>
</tr>
<tr>
<td>政治面貌</td>
<td><input type="text" name="political" id="political"> </td>
</tr>
<tr>
<td>服务类别</td>
<td><input type="text" name="type" id="type"></td>
</tr>
<tr>
<td>注册时间</td>
<td><input type="text" name="time" id="time"></td>
</tr>
<tr>
<td colspan="2">
<input class="btn btn-default" type="submit" value="查询">
</td>
</tr> <tr>
<td colspan="2">
<font color="red">${message}</font>
</td>
</tr>
</table>
</form>
<c:if test="${message eq '查询成功'}">
<table align="center" bgcolor="#FFFFFF" class="table table-bordered" >
<tr>
<td>
姓名
</td>
<td>
${U.name}
</td>
</tr>
<tr>
<td>
性别
</td>
<td>
${U.sex}<br>
</td>
</tr>
<tr>
<td>
民族
</td>
<td>
${U.nation}
</td>
<tr>
<td>
注册时间
</td>
<td>
${U.time}
</td>
<tr>
<td>
年龄
</td>
<td>
${U.year}
</td>
</tr>
<tr>
<td>
政治面貌
</td>
<td>
${U.political}
</td>
</tr>
<tr>
<td>
服务类别
</td>
<td>
${U.type}
</td>
</tr>
</table>
</c:if>
</body>
</html>
查询信息界面
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> <!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>志愿者信息浏览界面</title>
</head>
<body>
<h2 align=center>所有志愿者信息</h2><p align="right"><a href="main.jsp">返回主界面</a></p><hr>
<form action="Servlet?method=showinfo" method="post">
<table align=center>
<tr>
<th class="w1">姓名</th>
<th class="w1">性别</th>
<th class="w1">民族</th>
<th class="w1">政治面貌</th>
</tr>
<c:forEach items="${userlist}" var="item">
<tr>
<td class="w1"><a href="Servlet?method=showinfo&name=${item.name}">${item.name}</a></td>
<td class="w1">${item.sex}</td>
<td class="w1">${item.nation}</td>
<td class="w1">${item.political}</td>
</tr>
</c:forEach>
</table>
</form>
</body>
</html>
浏览志愿者信息简洁界面
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>志愿者详细信息浏览界面</title>
</head>
<body>
<h2 align=center>志愿者信息</h2><p align="right"><a href="main.jsp">返回主界面</a></p><hr>
<table align=center>
<tr>
<th class="w1">姓名</th>
<th class="w1">性别</th>
<th class="w1">民族</th>
<th class="w1">政治面貌</th>
<th class="w1">注册时间</th>
<th class="w1">年龄</th>
<th class="w1">服务类别</th>
</tr>
<tr>
<td class="w1">${U.name}</td>
<td class="w1">${U.sex}</td>
<td class="w1">${U.nation}</td>
<td class="w1">${U.political}</td>
<td class="w1">${U.time}</td>
<td class="w1">${U.year}</td>
<td class="w1">${U.type}</td> </tr>
</table>
</body>
</html>
详细信息界面
Sevlet层：
package servlet;
import java.io.IOException;
import user.User;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List; import dao.Dao;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class Servlet extends HttpServlet{
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public Servlet() {
super();
}
Dao dao = new Dao();
private void add(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
User user = new User(req.getParameter("name"),req.getParameter("sex"),req.getParameter("nation"),req.getParameter("time"),req.getParameter("year"),req.getParameter("political"),req.getParameter("type")); if(dao.add(user.getname(),user.getsex(),user.getnation(),user.gettime(),user.getyear(),user.getpolitical(),user.gettype() )) {
req.setAttribute("message", "添加志愿者信息成功");
req.getRequestDispatcher("show.jsp").forward(req,resp);
}else {
req.setAttribute("message", "添加志愿者信息失败");
req.getRequestDispatcher("add.jsp").forward(req,resp);
}
}
private void show(HttpServletRequest req,HttpServletResponse resp)throws ServletException,IOException{
req.setCharacterEncoding("utf-8");
List<User>userlist = dao.show();
req.setAttribute("userlist", userlist);
req.getRequestDispatcher("show.jsp").forward(req,resp);
}
private void showinfo(HttpServletRequest req,HttpServletResponse resp)throws ServletException,IOException{
req.setCharacterEncoding("utf-8");
User user = dao.Q_U(req.getParameter("name"),req.getParameter("sex"),req.getParameter("nation"),req.getParameter("time"),req.getParameter("political"),req.getParameter("type"));
System.out.println(req.getParameter("name"));
req.setAttribute("U",user);
req.getRequestDispatcher("showinfo.jsp").forward(req,resp);
}
public void delete(HttpServletRequest req,HttpServletResponse resp) throws ServletException,IOException{
req.setCharacterEncoding("UTF-8");
String name=req.getParameter("name");
if(dao.delete(name)) {//进行数据库的删除操作
req.setAttribute("message", "删除成功");
}else {
req.setAttribute("message", "删除失败");
}
req.getRequestDispatcher("delete.jsp").forward(req, resp);
}
public void d_u(HttpServletRequest req, HttpServletResponse resp)throws IOException, ServletException {
User user =dao.Q_U(req.getParameter("name"),req.getParameter("sex"),req.getParameter("nation"),req.getParameter("time"),req.getParameter("political"),req.getParameter("type"));
if(user!=null) {
req.setAttribute("message", "查询成功");
req.setAttribute("U", user);
req.getRequestDispatcher("delete.jsp").forward(req,resp);
}else {
req.setAttribute("message", "该志愿者信息不存在");
req.setAttribute("U", user);
req.getRequestDispatcher("delete.jsp").forward(req,resp);
}
}
public void ch_u(HttpServletRequest req, HttpServletResponse resp)throws IOException, ServletException {
User user =dao.Q_U(req.getParameter("name"),req.getParameter("sex"),req.getParameter("nation"),req.getParameter("time"),req.getParameter("political"),req.getParameter("type"));
if(user!=null) {
req.setAttribute("message", "查询成功");
req.setAttribute("U", user);
req.getRequestDispatcher("revise.jsp").forward(req,resp);
}else {
req.setAttribute("message", "该志愿者信息不存在");
req.setAttribute("U", user);
req.getRequestDispatcher("revise.jsp").forward(req,resp);
}
}
public void revise(HttpServletRequest req,HttpServletResponse resp)throws IOException,ServletException{
req.setCharacterEncoding("utf-8");
String name = req.getParameter("name");
String sex = req.getParameter("sex");
String nation=req.getParameter("nation");
String time=req.getParameter("time");
String year=req.getParameter("year");
String political = req.getParameter("political");
String type =req.getParameter("type");
User user=new User(name,sex,nation,time,year,political,type);
dao.revise(user.getname(),user.getsex(),user.getnation(),user.gettime(),user.getyear(),user.getpolitical(),user.gettype());//进行数据库的修改操作
req.setAttribute("message", "修改成功");
req.getRequestDispatcher("revise.jsp").forward(req,resp);
}
public void q_u(HttpServletRequest req, HttpServletResponse resp)throws IOException, ServletException {
String type=req.getParameter("type1");
if(type.equals("0")) {
User user =dao.Q_U(req.getParameter("name"),req.getParameter("sex"),req.getParameter("nation"),req.getParameter("time"),req.getParameter("political"),req.getParameter("type"));
if(user!=null) {
req.setAttribute("message", "查询成功");
req.setAttribute("U", user);
req.getRequestDispatcher("search.jsp").forward(req,resp);
}else {
req.setAttribute("message", "该志愿者信息不存在");
req.setAttribute("U", user);
req.getRequestDispatcher("search.jsp").forward(req,resp);
}
}
else if(type.equals("1")) { }
if(type!="1"&&type!="0"){
req.setAttribute("message","请选择查询方式");
req.getRequestDispatcher("search.jsp").forward(req,resp);
}
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
String method = request.getParameter("method");
System.out.println(method);
if("add".equals(method)) {
add(request,response);
}
else if("show".equals(method)) {
show(request,response);
}
else if("delete".equals(method)) {
delete(request,response);
}
else if("d_u".equals(method)) {
d_u(request,response);
}
else if("revise".equals(method)) {
revise(request,response);
}
else if("q_u".equals(method)) {
q_u(request,response);
}
else if("ch_u".equals(method)) {
ch_u(request,response);
}
else if("showinfo".equals(method)) {
showinfo(request,response);
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
Servlet
封装用户属性的java bean
package user;
public class User {
private String name;
private String sex;
private String nation;
private String time;
private String year;
private String political;
private String type;
public User(String name,String sex,String nation,String time,String year,String political,String type){
this.name=name;
this.sex=sex;
this.nation=nation;
this.time=time;
this.year=year;
this.political=political;
this.type=type;
}
public String getname() {
return name;
}
public String getsex() {
return sex;
}
public String getnation() {
return nation;
}
public String gettime() {
return time;
}
public String getyear() {
return year;
}
public String getpolitical() {
return political;
}
public String gettype() {
return type;
}
}
User
Dao层
package dao; import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List; import database.DB;
import user.User; public class Dao {
public static boolean add(String name,String sex,String nation,String time,String year,String political,String type) {
Connection conn = null;
PreparedStatement pstmt = null;
boolean pd = false;
System.out.println("add run!");
try {
//获取连接
conn = DB.getConn();
//编写语句
String sql = "insert into volunteer values(?,?,?,?,?,?,?)";
//预编译
pstmt = conn.prepareStatement(sql);
//设置数据
pstmt.setString(1, name);
pstmt.setString(2, sex);
pstmt.setString(3, nation);
pstmt.setString(4,time);
pstmt.setString(5,year);
pstmt.setString(6,political);
pstmt.setString(7,type);
//执行
int res = pstmt.executeUpdate();
//判断
if(res>0) {
pd=true;
}else {
pd=false;
}
} catch (Exception e) {
e.printStackTrace();
}finally { DB.close(pstmt,conn);
}
return pd;
}
public boolean delete(String name) {
boolean pd=false;
Connection connection = DB.getConn();
String sql = "delete from volunteer where name = ?";
PreparedStatement preparedStatement = null;
System.out.println("detelte run!");
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, name);
int res=preparedStatement.executeUpdate();
if(res>0) {
pd=true;
}
else {
pd=false;
}
} catch (Exception e) { e.printStackTrace();
}finally {
DB.close(preparedStatement,connection);
}
return pd;
}
public void revise(String name,String sex,String nation,String time,String year,String political,String type) { Connection connection = DB.getConn();
//准备sql语句
String sql = "update volunteer set name = ? , sex=?,nation = ?,time=?,year=?,political=?,type=?";
//创建语句传输对象
PreparedStatement preparedStatement = null;
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, name);
preparedStatement.setString(2, sex);
preparedStatement.setString(3, nation);
preparedStatement.setString(4,time);
preparedStatement.setString(5,year);
preparedStatement.setString(6,political);
preparedStatement.setString(7,type);
preparedStatement.executeUpdate();
} catch (Exception e) { e.printStackTrace();
}finally {
DB.close(preparedStatement,connection);
} }
public List<User> show() {
List<User> userlist =new ArrayList<>();
Connection conn = DB.getConn();
ResultSet rs= null;
String sql="select * from volunteer";
User user =null;
Statement stmt=null;
try {
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);
while(rs.next()){
String name=rs.getString("name");
String sex=rs.getString("sex");
String nation=rs.getString("nation");
String time=rs.getString("time");
String year=rs.getString("year");
String political =rs.getString("political");
String type=rs.getString("type");
user = new User(name,sex,nation,time,year,political,type);
userlist.add(user);
}
}catch(Exception e) {
e.printStackTrace();
}
finally {
DB.close(rs,stmt,conn);
}
return userlist;
}
public User Q_U(String name,String sex1,String nation1,String time1,String political1,String type1){
String sql="select * from volunteer where 1 = 1";
Connection conn=null;
PreparedStatement pstmt = null;
ResultSet rs= null;
if(name!=null) {
sql+="and name= ? ";
}
if(sex1!=null) {
sql+="and sex= ? ";
}
System.out.println(sql);
try {
conn=DB.getConn();
pstmt=(PreparedStatement) conn.prepareStatement(sql);
pstmt.setString(1,name);
pstmt.setString(2,sex1);
rs =(ResultSet)pstmt.executeQuery();
while(rs.next()) {
String name1=rs.getString("name");
String sex=rs.getString("sex");
String nation=rs.getString("nation");
String time =rs.getString("time");
String year=rs.getString("year");
String political =rs.getString("political");
String type=rs.getString("type");
User user = new User(name1,sex,nation,time,year,political,type);
return user;
}
}catch(Exception e) {
e.printStackTrace();
}finally {
DB.close(rs,pstmt,conn);
}
return null;
} }
连接数据库的java bean
package database; import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement; public class DB {
public static String db_url = "jdbc:mysql://localhost:3306/student?useSSL=false&serverTimezone=UTC&characterEncoding=UTF-8&useOldAliasMetadataBehavior=true";
public static String db_user = "root";
public static String db_pass = "abc456"; public static Connection getConn () {
Connection conn = null; try {
Class.forName("com.mysql.cj.jdbc.Driver");//加载驱动
conn = DriverManager.getConnection(db_url, db_user, db_pass);
} catch (Exception e) {
e.printStackTrace();
} return conn;
} /**
* 关闭连接
* @param state
* @param conn
*/
public static void close (Statement state, Connection conn) {
if (state != null) {
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
} if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
} public static void close (ResultSet rs, Statement state, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
} if (state != null) {
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
} if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
} public static void main(String[] args) throws SQLException {
Connection conn = getConn();
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql ="select * from USER";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
if(rs.next()){
System.out.println("空");
}else{
System.out.println("不空");
}
}
}
Database