jdbc的通讯录CRUD
基于JDBC的通讯录练手：
项目以MVC模式开发，包名：
cn.itcast.txl.domain;
cn.itcast.txl.dao;
cn.itcast.txl.dao.impl;
cn.itcast.txl.service;
cn.itcast.txl.service.impl;
cn.itcast.txl.controller;
domain层
User.java
public void User(){
private int id;
private String name;
private String sex;
private String phone;
private String QQ;
private String Email;
private String address;
//提供有参函数，无参函数
//提供set和get方法
}
dao层
UserDao.java
public interface UserDao {
public void save(User user);
public void delete(int id);
public void update(User user);
public Object query();
//根据id获取联系人
public User get(int id);
}
UserDaoImpl.java
//添加联系人
public void save(User user) {
//定义变量
Connection conn = null;
PreparedStatement state = null;
try {
//初始化参数
conn = DBUtils.getConnection();
String sql = "insert into t_user (name,sex,phone,QQ,Email,address) values (?,?,?,?,?,?)";
//设置参数
state = conn.prepareStatement(sql);
state.setString(1, user.getName());
state.setString(2, user.getSex());
state.setString(3, user.getPhone());
state.setString(4, user.getQQ());
state.setString(5, user.getEmail());
state.setString(6, user.getAddress());
//执行
state.execute();
System.out.println("添加联系人成功.......................");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new RuntimeException("添加联系人失败....................");
}finally{
//释放资源
DBUtils.closeAll(null, state, conn);
}
}
public void update(User user) {
//定义变量
Connection conn = null;
PreparedStatement state = null;
try {
//初始化参数
conn = DBUtils.getConnection();
String sql = "update t_user set name=?,sex=?,phone=?,QQ=?,Email=?,address=? where id=?";
//设置参数
state = conn.prepareStatement(sql);
state.setString(1, user.getName());
state.setString(2, user.getSex());
state.setString(3, user.getPhone());
state.setString(4, user.getQQ());
state.setString(5, user.getEmail());
state.setString(6, user.getAddress());
state.setInt(7, user.getId());
System.out.println("id="+user.getId());
//执行
state.execute();
System.out.println("修改联系人成功.......................");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new RuntimeException("修改联系人失败....................");
}finally{
//释放资源
DBUtils.closeAll(null, state, conn);
}
}
public Object query() {
//定义变量
String sql = null;
Connection conn = null;
PreparedStatement state = null;
ResultSet set = null;
User user = null;
Map<String, User> users = new LinkedHashMap<String, User>();
//初始化
try {
conn = DBUtils.getConnection();
sql = "select id,name,sex,phone,QQ,Email,address from t_user";
state = conn.prepareStatement(sql);
//执行
set = state.executeQuery();
//获取
while(set.next()){
int id = set.getInt(1);
String name = set.getString(2);
String sex = set.getString(3);
String phone = set.getString(4);
String QQ = set.getString(5);
String Email = set.getString(6);
String address = set.getString(7);
//封装为user对象
user = new User(id,name, sex, phone, QQ, Email, address);
//存储到Map集合中
users.put(name, user);
}
//成功
System.out.println("获取所有联系人成功.........");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new RuntimeException("获取所有联系人失败.........");
}finally{
DBUtils.closeAll(set, state, conn);
}
return users;
}
public void delete(int id) {
String sql = null;
Connection conn = null;
PreparedStatement state = null;
try {
conn = DBUtils.getConnection();
sql = "delete from t_user where id = ?";
//设置参数
state=conn.prepareStatement(sql);
state.setInt(1, id);
//执行
state.execute();
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("删除失败了........"+e);
}finally{
DBUtils.closeAll(null, state, conn);
}
}
public User get(int id) {
String sql = null;
Connection conn = null;
PreparedStatement state = null;
ResultSet set = null;
User user =null;
//初始化
try {
conn = DBUtils.getConnection();
sql = "select name,sex,phone,QQ,Email,address from t_user where id = ?";
state = conn.prepareStatement(sql);
state.setInt(1, id);
//执行
set = state.executeQuery();
//获取
while(set.next()){
String name = set.getString(1);
String sex = set.getString(2);
String phone = set.getString(3);
String QQ = set.getString(4);
String Email = set.getString(5);
String address = set.getString(6);
//封装为user对象
user = new User(name, sex, phone, QQ, Email, address);
break;
}
user.setId(id);
//成功
System.out.println("获取单个人成功.........");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new RuntimeException("获取单个联系人失败.........",e);
}finally{
DBUtils.closeAll(set, state, conn);
}
return user;
}
}
DBUtils.java
/*
* 该工具类主要任务是：
* 1、加载配置文件
* 2、返回数据库的连接
* 3、释放数据库连接的所有资源
*/
public class DBUtils {
//使用连接池对象
private static ComboPooledDataSource ds = new ComboPooledDataSource();
//提供获取Connection连接方法
public static Connection getConnection(){
Connection conn = null;
try {
conn = ds.getConnection();
System.out.println("测试：连接池中的连接对象......");
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("连接失败....");
}
return conn;
}
//提供一个释放所有资源的方法
public static void closeAll(ResultSet set,Statement state,Connection conn){
try {
if( set != null )
set.close();
} catch (Exception e) {
set = null;
}
try {
if( state != null )
state.close();
} catch (Exception e) {
state = null;
}
try {
if( conn != null )
conn.close();
} catch (Exception e) {
conn = null;
}
}
}
UserService.java
public interface UserService(){
public void save(User user);
public void delete(int id);
public void update(User user);
public Map<String,User> list();
public User get(int id);
}
UserServiceImpl.java
public class UserServiceImpl implement UserService{
private UserDao userDao = new UserDaoImpl();
.......
}
UserServlet.java
public class UserServlet extends HttpServlet{
//定义服务对象
private UserService service = new UserServiceImpl();
doget(){
this.dopost(request,response);
};
dopost(){
request.setCharactorEncoding("UTF-8");
String method = request.getParameter("method");
if(method.equals("list")){
list(request,response);
}else if(method.equals("save")){
save(request,response);
}else if(method.equals("saveAdd")){
saveAdd(request,response);
}else if(method.equals("updateUser")){
updateUser(request, response);
}else if(method.equals("get")){
get(request, response);
}else if(method.equals("delete")){
delete(request, response);
}
};
//处理用户提交查询的请求
public void list(HttpServletRequest request,HttpServletResponse response){
LinkedHashMap<String, User> user = (LinkedHashMap<String, User>)service.list();
request.setAttribute("users",user);
String path = "/WEB-INF/list.jsp";
request.getRequestDispatcher(path).forward(request,response);
}
//接收用户的添加请求，转发到增加页面
public void save(HttpServletRequest request,HttpServletResponse response){
String path = "/WEB-INF/addUser.jsp";
request.getRequestDispatcher(path).forward(request,response);
}
public void saveAdd(HttpServletRequest request,HttpServletResponse response){
User user = new User();
user.setName(request.getParameter("name"));
user,setSex(request.getParameter("sex"));
user.setPhone(request.getParameter("phone"));
user.setQQ(request.getParameter("QQ"));
user.setEmail(request.getParameter("Email"));
user.setAddress(request.getParameter("address"));
service.save(user);
String path = "/UserServlet?method=list";
request.getRequestDispathcher(path).forward(request,response);
}
public void delete(HttpServletRequest request,HttpServletResponse response){
int id = Integer.parseInt(request.getParameter("id"));
sevice.delete(id);
String path = "/UserServlet?method=list";
request.getRequestDispatcher(path).forward(request,response);
}
//获取跳转到修改页面的请求带ID
public void get(HttpServletRequest request,HttpServletResponse response){
String path = "/WEB-INF/updateUser.jsp";
User user = service.get(Integer.parseInt(request.getParameter("id")));
request.setAttribute("user",user);
request.getRequsetDispatcher(path).forward(request,response);
}
public void update(HttpServletRequest request,HttpServletResponse response){
User user = new User();
user.setId(Integer.parseInt(request,getParameter("id")));
user.setName(request.getParameter("name"));
user.setSex(request.getParameter("sex"));
user.setPhone(request.getParameter("phone"));
user.setQQ(request.getParameter("QQ"));
user.setEmail(request.getParameter("Email"));
user.setAddress(request.getParameter("address"));
service.update(user);
String path = "/Userservlet?method=list";
request.getRequestDispatcher(path).forward(request,response);
}
}
jsp页面
list.jsp
<html>
<head>
<title>所有联系人</title>
</head>
<body>
<center>
<h1 style="">通讯录</h1>
<table border="1" style="border-collapse: collapse; border-color: blue;">
<!-- 表头 -->
<tr>
<th>姓名</th>
<th>性别</th>
<th>电话</th>
<th>QQ</th>
<th>Email</th>
<th>地址</th>
<th>操作</th>
</tr>
<!-- 显示数据列表 -->
<c:forEach items="${requestScope.users}" var="user">
<tr>
<td>${user.value.name}</td>
<td>${user.value.sex}</td>
<td>${user.value.phone}</td>
<td>${user.value.QQ}</td>
<td>${user.value.email}</td>
<td>${user.value.address}</td>
<td>
<a href="${pageContext.request.contextPath}/UserServlet?method=delete&id=${user.value.id}" onclick="return confirm('确定要删除本联系人吗？')">删除</a>
<a href="${pageContext.request.contextPath}/UserServlet?method=get&id=${user.value.id}">修改</a>
</td>
</tr>
</c:forEach>
<!-- 其他操作 -->
<tr>
<td colspan="7">
<a href="${pageContext.request.contextPath}/UserServlet?method=save">添加</a>
</td>
</tr>
</table>
</center>
</body>
</html>
addUser.jsp
<html>
<head>
<head>
<title>添加联系人</title>
<style type="text/css">
th, td{
padding: 5px;
}
</style>
</head>
<body>
<center>
<h1 style="">通讯录</h1>
<form action="${pageContext.request.contextPath}/UserServlet?method=saveAdd" method="post">
<table border="1" style="border-collapse: collapse; border-color: blue;">
<!-- 表单内容 -->
<tr>
<td>姓名</td>
<td><input type="text" name="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" name="phone"></td>
</tr>
<tr>
<td>QQ号码</td>
<td><input type="text" name="QQ"></td>
</tr>
<tr>
<td>Email</td>
<td><input type="text" name="Email"></td>
</tr>
<tr>
<td>地址</td>
<td><input type="text" name="address"></td>
</tr>
<!-- 其他操作 -->
<tr>
<td colspan="2" align="center">
<input type="submit" value="添加">
</td>
</tr>
</table>
</form>
</center>
</body>
</html>
updateUser.jsp
html>
<head>
<title>修改联系人</title>
<style type="text/css">
th, td{
padding: 5px;
}
</style>
</head>
<body>
<center>
<h1 style="">通讯录</h1>
<form action="${pageContext.request.contextPath}/UserServlet?method=update" method="post">
<table border="1" style="border-collapse: collapse; border-color: blue;">
<!-- 表单内容 -->
<tr>
<td>姓名</td>
<td><input type="text" name="name" value="${requestScope.user.name}">
<input type="hidden" name="id" value="${requestScope.user.id}"></td>
</tr>
<tr>
<td>性别</td>
<td>
<input type="radio" name="sex" value="男" ${(user.sex eq '男') ? "checked" : "" }>男
<input type="radio" name="sex" value="女" ${(user.sex eq '女') ? "checked" : "" }>女
</td>
</tr>
<tr>
<td>电话号码</td>
<td><input type="text" name="phone" value="${requestScope.user.phone}"></td>
</tr>
<tr>
<td>QQ号码</td>
<td><input type="text" name="QQ" value="${requestScope.user.QQ}"></td>
</tr>
<tr>
<td>Email</td>
<td><input type="text" name="Email" value="${requestScope.user.email}"></td>
</tr>
<tr>
<td>地址</td>
<td><input type="text" name="address" value="${requestScope.user.address}"></td>
</tr>
<!-- 其他操作 -->
<tr>
<td colspan="2" align="center">
<input type="submit" value="修改">
</td>
</tr>
</table>
</form>
</center>
</body>
</html>
最后配置一个数据库连接池c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///tongxunlu</property>
<property name="user">root</property>
<property name="password">root</property>
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">15</property>
<property name="minPoolSize">1</property>
<property name="maxIdleTime">2000</property>
</default-config>
</c3p0-config>
