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="男" checked>男
<input type="radio" name="sex"
value="女">女
</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>
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
