JavaWeb JDBC + MySql 通讯录实现简单的增删改查

开发工具：Eclipse + Navicat 

项目源码：Github:https://github.com/Sunjinhang/JavaWeb

一、新建项目

在Eclipse中新建一个Web项目，至于如何新建Web项目以及如何添加Tomcat服务器的就不赘述了，项目的目录如下

 

最终实现的效果如下所示：

点击新增可以进行联系人的新增，点击修改/删除可以进行 联系人的修改和删除

部分代码如下

数据库连接：在测试数据库连接时，需要注意mysql 时区的设置，安装mysql时默认的时区时美国时间，与本地相差8个小时，所以如果不修改则在链接数据库时会报错。

package pers.contact.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class BaseDao {

	private static final String DRIVER = "com.mysql.jdbc.Driver";
	public static final String URL = "jdbc:mysql://localhost:3306/demo?rewriteBatchedStatements=true&useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=GMT%2B8";
	public static final String USER = "root";
	public static final String PASSWORD = "sasa";
	
	
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	public  void getConnection() {
		try {
			// 加载数据库驱动
			Class.forName(DRIVER);
			// 获得数据库连接
			conn = DriverManager.getConnection(URL, USER, PASSWORD);
		} 
		catch (ClassNotFoundException e) {
			e.printStackTrace();
		} 
		catch (SQLException e) {
			e.printStackTrace();
		}
	}	
	
	public int executeUpdate(String sql, Object... obj) {
		int num = 0;
		getConnection();
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			for (int i = 0; i < obj.length; i++) {
				pstmt.setObject(i + 1, obj[i]);
			}
			num = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeAll();
		}
		return num;
	}
	
	
	public ResultSet executeQuery(String sql, Object... obj) {
		getConnection();
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			for (int i = 0; i < obj.length; i++) {
				pstmt.setObject(i + 1, obj[i]);
			}
			rs = pstmt.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
	
	
	public void closeAll() {
		try {
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			pstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
}

联系人：

package pers.contact.entity;

import java.util.Date;

public class Contact {

	public Contact(int id, String name, int age, String phone, Date date, String favorite) {
		super();
		this.id = id;
		this.name = name;
		this.age = age;
		this.phone = phone;
		this.date = date;
		this.favorite = favorite;
	}
		
	private int id;
	private String name;
	private int age;
	private String phone;
	private Date date;
	private String favorite;
	
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public String getPhone() {
		return phone;
	}
	public void setPhone(String phone) {
		this.phone = phone;
	}
	public Date getDate() {
		return date;
	}
	public void setDate(Date date) {
		this.date = date;
	}
	public String getFavorite() {
		return favorite;
	}
	public void setFavorite(String favorite) {
		this.favorite = favorite;
	}
	
	
}

增删改查的实现：

package pers.contact.service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import pers.contact.entity.Contact;

import pers.contact.dao.BaseDao;;

public class ContactService extends BaseDao {

	ResultSet rs = null;
	
	public List<Contact> GetAllContact(){

		List<Contact> list = new ArrayList();
		String sql = "select * from contact";
		rs = executeQuery(sql);
		try {
			while (rs.next()) {
				Contact f = new Contact(rs.getInt(1), rs.getString(2),
						rs.getInt(3), rs.getString(4), rs.getDate(5),
						rs.getString(6));
				list.add(f);
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}	
		return list;
	}
	
	public int AddContact(Contact contact)
	{
		int num = 0;
		String sql = "insert into contact(name,age,phone,date,favorite) values(?,?,?,?,?)";
		try {
			num = executeUpdate(sql, contact.getName(), contact.getAge(), contact.getPhone(),
					contact.getDate(), contact.getFavorite());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return num;
	}
	
	public int DeleteContact(int id)
	{
		int num = 0;
		String sql = "delete from contact where id = ?";
		try {
			num = executeUpdate(sql, id);
		}
		catch(Exception ex) {
			ex.printStackTrace();
		}
		return num;
	}
	
	public Contact GetContact(int id) {
		
		String sql = "select * from contact where id = ?";
		Contact contact = null;
		rs = executeQuery(sql, id);
		try {
			while(rs.next()) {
				contact = new Contact(rs.getInt(1),rs.getString(2),rs.getInt(3),rs.getString(4),rs.getDate(5),rs.getString(6));
			}
		}
		catch(SQLException ex){
			ex.printStackTrace();
		}
		
		return contact;	
	}
	
	public int UpdateContact(Contact contact) {
		int num = 0;
		String sql = "update contact set name = ?,age = ?,phone = ?,date = ?,favorite = ? where id = ?";
		try {
			num = executeUpdate(sql, contact.getName(),contact.getAge(),contact.getPhone(),contact.getDate(),contact.getFavorite(),contact.getId());
		}
		catch(Exception ex) {
			ex.printStackTrace();
		}
		
		return num;
	}
}

Servlet:

package pers.contact.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import pers.contact.entity.Contact;
import pers.contact.service.ContactService;

/**
 * Servlet implementation class ContactServlet
 */
@WebServlet("/ContactServlet")
public class ContactServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public ContactServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request,response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		response.setContentType("text/html;charset=utf-8");
		request.setCharacterEncoding("utf-8");
        
		HttpSession session = request.getSession();
		PrintWriter out = response.getWriter();
		
		
		ContactService ud = new ContactService();
		// 获得do属性
		String dos = request.getParameter("do");
		if (dos == null || dos.equals("")) {
			dos = "index";
		}
		// 主页
		if (dos.equals("index")) {
			List<Contact> ulist = ud.GetAllContact();
			request.setAttribute("ulist", ulist);
			request.getRequestDispatcher("/index.jsp").forward(request, response);
			return;
		}
		if(dos.equals("add")) {
			String name = request.getParameter("name");
			int age = Integer.parseInt(request.getParameter("age"));
			String phone = request.getParameter("phone");
			
			
			String dates = request.getParameter("date");
			SimpleDateFormat sdf = new SimpleDateFormat("yy-MM-dd");
			Date date = null;
			
			try {
				date = (Date)sdf.parse(dates);
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			//爱好获取
			String favorite = request.getParameter("favorite");
			
			Contact contact = new Contact(0,name,age,phone,date,favorite);
			
			ud.AddContact(contact);
			out.print("<script>alert('新增成功！');window.location='ContactServlet?do=index';</script>");				
		}
		
		if(dos.equals("del")) {
			
			String ids = request.getParameter("id");
			int id = Integer.parseInt(ids);
			ud.DeleteContact(id);
			out.print("<script>alert('删除成功！');window.location='ContactServlet?do=index';</script>");	
		}
		
		if(dos.equals("editbefore")) {
			int id =  Integer.parseInt(request.getParameter("id"));
			Contact f = ud.GetContact(id);
			session.setAttribute("edituser", f);
			response.sendRedirect("edit.jsp");
			return;
		}
		if(dos.equals("edit")) {
			try {
				
				int id = Integer.parseInt(request.getParameter("id"));
				
				String name = request.getParameter("name");
				int age = Integer.parseInt(request.getParameter("age"));
				String phone = request.getParameter("phone");
				
				
				String dates = request.getParameter("date");
				SimpleDateFormat sdf = new SimpleDateFormat("yy-MM-dd");
				Date date = null;
				date = (Date)sdf.parse(dates);
				String favorite = request.getParameter("favorite");
				
				Contact contact = new Contact(id,name,age,phone,date,favorite);
				
				ud.UpdateContact(contact);
				out.print("<script>alert('修改成功！');window.location='ContactServlet?do=index';</script>");	
				
			}
			catch(ParseException ex) {
				
				ex.printStackTrace();
			}
			
		}
		
	}

}

JSP页面

index 页面，此页面需要添加 jstl.jar 和standard.jar ，否则无法引用 taglib

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ page import="pers.contact.entity.Contact"%>
<%@ page import="pers.contact.service.ContactService"%>

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%
	String path = request.getContextPath();
	String basePath = request.getScheme() + "://"
			+ request.getServerName() + ":" + request.getServerPort()
			+ path + "/";
	//下面的语句初始为初始化页面，如果不加下面语句访问主页不会显示数据库中保存的数据
	ContactService ud = new ContactService();
	List<Contact> ulist = ud.GetAllContact();
	request.setAttribute("ulist", ulist);
%>  
    

<!DOCTYPE html>
<html>
<head>
<base href="<%=basePath%>">

<meta charset="ISO-8859-1">

<link rel="stylesheet"
	href="https://cdn.bootcss.com/foundation/5.5.3/css/foundation.min.css">
<script src="https://cdn.bootcss.com/jquery/2.1.1/jquery.min.js"></script>
<script
	src="https://cdn.bootcss.com/foundation/5.5.3/js/foundation.min.js"></script>
<script
	src="https://cdn.bootcss.com/foundation/5.5.3/js/vendor/modernizr.js"></script>
<style type="text/css">
table {
	margin: auto;
}
td {
	text-align: center;
}
h1 {
	margin-left: 40%;
}
a#add {
	margin-left: 45%;
}
</style>
<title>Insert title here</title>
</head>
<body style="padding:20px;">
	<h1>通讯录主页</h1>
	<a id="add" href="add.jsp">新增小伙伴</a>
	<table>
		<thead>
			<tr>
				<th>序号</th>
				<th>姓名</th>
				<th>年龄</th>
				<th>电话</th>
				<th>生日</th>
				<th>爱好</th>
				<th>操作</th>
			</tr>
			<c:forEach var="U" items="${ulist}">
			<tr>
				<th>${U.id}</th>
				<th>${U.name}</th>
				<th>${U.age}</th>
				<th>${U.phone}</th>
				<th>${U.date}</th>
				<th>${U.favorite}</th>
				<th><a href="ContactServlet?do=editbefore&id=${U.id}">修改</a> <a href="ContactServlet?do=del&id=${U.id}">删除</a> </th>
			</tr>
			</c:forEach>
		</thead>
		<%--<c:forEach/>标签遍历List--%>
		
	</table>
</body>
</html>

Add页面

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
	String path = request.getContextPath();
	String basePath = request.getScheme() + "://"
			+ request.getServerName() + ":" + request.getServerPort()
			+ path + "/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'add.jsp' starting page</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet"
	href="https://cdn.bootcss.com/foundation/5.5.3/css/foundation.min.css">
<script src="https://cdn.bootcss.com/jquery/2.1.1/jquery.min.js"></script>
<script
	src="https://cdn.bootcss.com/foundation/5.5.3/js/foundation.min.js"></script>
<script
	src="https://cdn.bootcss.com/foundation/5.5.3/js/vendor/modernizr.js"></script>
</head>
<body>
	<h1>新增页面</h1>
<form action="ContactServlet?do=add" method="post" style="width:50%;"> 
   <fieldset> 
    <label>姓名 <input type="text" placeholder="name" required="required" name="name" /> </label> 
    <label>年龄 <input type="number" placeholder="age" required="required"  min="1" max="133" name="age" /> </label> 
    <label>电话<input type="text" placeholder="phonenum" required="required" name="phone" /> </label> 
    <label>生日<input type="date" placeholder="date" required="required" name="date" /> </label> 
    <label>爱好<input type="text" placeholder="favorite" required="required" name="favorite" /> </label> 
    <input type="submit" value="新增" class="button" /> 
    <input type="reset" class="button" /> 
   </fieldset> 
  </form>   
</body>
</html>

Edit页面

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
	String path = request.getContextPath();
	String basePath = request.getScheme() + "://"
			+ request.getServerName() + ":" + request.getServerPort()
			+ path + "/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'add.jsp' starting page</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet"
	href="https://cdn.bootcss.com/foundation/5.5.3/css/foundation.min.css">
<script src="https://cdn.bootcss.com/jquery/2.1.1/jquery.min.js"></script>
<script
	src="https://cdn.bootcss.com/foundation/5.5.3/js/foundation.min.js"></script>
<script
	src="https://cdn.bootcss.com/foundation/5.5.3/js/vendor/modernizr.js"></script>
</head>
<body>
	<h1>修改页面</h1>
	<form action="ContactServlet?do=edit&id=${edituser.id}" method="post" style="width:50%">
		<fieldset>
			<label>姓名 <input type="text"  placeholder="name"  name="name" value = "${edituser.name}">
			</label> 
			<label>年龄 <input type="text"  placeholder="age" name="age" value = "${edituser.age}">
			</label>
			<label>电话<input type="text"  placeholder="phone" name="phone" value = "${edituser.phone}">
			</label>
			<label>生日<input type="date"  placeholder="date" name="date" value = "${edituser.date}">
			</label>
			<label>爱好<input type="text"  placeholder="favorite" name="favorite" value = "${edituser.favorite}">
			</label>
			<input type="submit" value="修改" class="button"> <input type="reset" class="button">
		</fieldset>
	</form>
</body>
</html>