联系数据库 电话本例子
login.jsp
<body>
<form action="LoginServlet" method="post">
<table>
<tr>
<td>用户名: </td>
<td><input type="text" name="username" /></td>
</tr>
<tr>
<td>密码: </td>
<td><input type="text" name="password" /></td>
</tr>
<tr>
<td><input type="submit" value="登录" /></td>
<td><a href="register.jsp">前往注册</a></td>
</tr>
</table>
</form>
</body>
register.jsp
<body>
<form action="RegisterServlet" method="post">
<table>
<tr>
<td>用户名: </td>
<td><input type="text" name="username" /></td>
</tr>
<tr>
<td>密码: </td>
<td><input type="text" name="password" /></td>
</tr>
<tr>
<td>确认密码: </td>
<td><input type="text" name="password1" /></td>
</tr>
<tr>
<td>姓名: </td>
<td><input type="text" name="realname" /></td>
</tr>
<tr>
<td><input type="submit" value="注册" /></td>
<td><a href="login.jsp">返回登陆</a></td>
</tr>
</table>
</form>
</body>
message.jsp
<body>
<%
String code = request.getParameter("code");
if ("0".equals(code)) {
out.print("<h1>请先登录 !</h1>");
}
if ("1".equals(code)) {
out.print("<h1>输入正确的参数 !</h1>");
}
if ("2".equals(code)) {
out.print("<h1>两次输入的密码不一致 !</h1>");
}
if ("3".equals(code)) {
out.print("<h1>注册成功 !</h1>");
}
if ("4".equals(code)) {
out.print("<h1>后台出现异常 !</h1>");
}
if ("5".equals(code)) {
out.print("<h1>用户不存在或者密码错误 !</h1>");
}
if ("6".equals(code)) {
out.print("<h1>删除联系人信息失败 !</h1>");
}
%>
<hr>
<a href="login.jsp">登录</a>
<a href="register.jsp">注册</a>
<a href="ShowContactInfoServlet">前往主页</a>
</body>
index.jsp
<head>
<%
ArrayList<ContactBook> list = null;
ContactUser cu = (ContactUser) session.getAttribute("currentUser");
String realname = "没有登录";
if (cu == null) {
response.sendRedirect("message.jsp?code=0");
} else {
realname = cu.getRealname();
list = (ArrayList<ContactBook>) session.getAttribute("cbList");
}
%>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Insert title here</title>
</head>
<body>
欢迎, [<%=realname%>]
<hr>
<form id="addOrUpdateForm" action="InsertContactBookServlet" method="post">
<input type="hidden" name="id" />
<table>
<tr>
<td>联系人名称:</td>
<td><input type="text" name="cname" /></td>
</tr>
<tr>
<td>电话:</td>
<td><input type="text" name="tel" /></td>
</tr>
<tr>
<td>性别:</td>
<td><select name="sex">
<option value="">未选择</option>
<option value="男">男</option>
<option value="女">女</option>
<option value="其他">其他</option>
</select></td>
</tr>
<tr>
<td>分组:</td>
<td><select name="tcgroup">
<option value="">未选择</option>
<option value="同事">同事</option>
<option value="朋友">朋友</option>
<option value="同学">同学</option>
<option value="家人">家人</option>
</select></td>
</tr>
<tr>
<td><input type="submit" value="保存"></td>
<td><input type="button" value="重置" /></td>
</tr>
</table>
</form>
<hr>
<h2>查询联系人</h2>
<form action="ShowContactInfoServlet" method="post">
<input type="hidden" name="isSearch" value="do" />
联系人名称:<input type="text" name="cname" />
电话:<input type="text" name="tel" />
性别:
<select name="sex">
<option value="">未选择</option>
<option value="男">男</option>
<option value="女">女</option>
<option value="其他">其他</option>
</select>
分组: <select name="tcgroup">
<option value="">未选择</option>
<option value="同事">同事</option>
<option value="朋友">朋友</option>
<option value="同学">同学</option>
<option value="家人">家人</option>
</select>
<input type="submit" value="查询" />
<input type="button" id="btn_delMultiple" value="删除选中记录" onclick="confirmMultiDel()" />
</form>
<hr>
<%
if (list != null && list.size() > 0) {
out.print("<table style='text-align:center;' width='70%' cellpadding='0' cellspacing='0' border='1'>");
out.print("<tr><th>联系人姓名</th><th>电话号码</th><th>性别</th><th>分组</th><th>添加时间</th><th>管理</th><th><input type='checkbox' id='leader' onclick='getMultiDel()'></th></tr>");
for (ContactBook c : list) {
out.print("<tr>");
out.print("<td>" + c.getName() + "</td><td>" + c.getTel() + "</td><td>" + c.getSex() + "</td><td>"
+ c.getTcgroup() + "</td><td>" + DateConvertor.putDate2String(c.getAddtime())
+ "</td><td><a href='DelContactBookServlet?id="+c.getId()+"' onclick='return confirmDel()'>删除</a> <a href='javascript:void(0)' onclick='loadForm(\""+c.getId()+"\",\""+c.getName()+"\", \""+c.getTel()+"\", \""+c.getSex()+"\", \""+c.getTcgroup()+"\")'>修改</a></td><td><input value='"+c.getId()+"' class='select' type='checkbox'></td>");
out.print("</tr>");
}
out.print("</table>");
} else {
out.print("没有任何记录 !");
}
%>
<script type="text/javascript">
function confirmDel() {
var r = confirm("确定删除吗?");
return r;
}
function getMultiDel() {
var c = document.getElementById("leader");
var r = c.checked;
var checks = document.getElementsByClassName("select");
for(var i=0;i<checks.length;i++) {
checks[i].checked = r;
}
}
function loadForm(id, cname, tel, sex, tcgroup) {
var f = document.getElementById("addOrUpdateForm");
f.id.value=id;
f.cname.value = cname;
f.tel.value = tel;
f.sex.value = sex;
f.tcgroup.value = tcgroup;
}
function confirmMultiDel() {
var checks = document.getElementsByClassName("select");
var arrayObjs = [];
for(var j=0;j<checks.length;j++) {
if(checks[j].checked) {
arrayObjs.push(checks[j].value);
}
}
var r1 = confirm("选中了"+arrayObjs.length+"条数据, 确定要删除吗 ?");
if(r1) {
window.location.href="DelContactBookServlet?ids="+arrayObjs;
}
}
</script>
</body>
RegiserServler.java
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
response.setContentType("text/html; charset=utf-8");
String username = request.getParameter("username");
String password = request.getParameter("password");
String password1 = request.getParameter("password1");
String realname = request.getParameter("realname");
DataBaseMethodDal dbmd = new DataBaseMethodDal();
if(DBHelper.checkParam(username, password, password1)) {
if(password.equals(password1)) {
ContactUser cu = new ContactUser(username, password, realname);
int a = dbmd.insertUser(cu);
if(a > 0) {
r(response, "message.jsp", 3);
} else {
r(response, "message.jsp", 4);
}
} else {
r(response, "message.jsp", 2);
}
} else {
r(response, "message.jsp",1);
}
}
LoginServler.java
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
response.setContentType("text/html; charset=utf-8");
String username = request.getParameter("username");
String password = request.getParameter("password");
DataBaseMethodDal dbmd = new DataBaseMethodDal();
if (DBHelper.checkParam(username, password)) {
ContactUser user = dbmd.selectUser(username, password);
if (user != null) {
request.getSession().setAttribute("currentUser", user);
r(response, "ShowContactInfoServlet", -1);
} else {
r(response, "message.jsp", 5);
}
} else {
r(response, "message.jsp", 1);
}
}
ShowContactInfoServler.java
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
response.setContentType("text/html; charset=utf-8");
DataBaseMethodDal dbmd = new DataBaseMethodDal();
ContactUser cu = (ContactUser)request.getSession().getAttribute("currentUser");
String isSearch = request.getParameter("isSearch");
ContactBook cb = null;
if(isSearch!=null&&"do".equals(isSearch)) {
String cname = request.getParameter("cname");
String tel = request.getParameter("tel");
String sex = request.getParameter("sex");
String tcgroup = request.getParameter("tcgroup");
cb = new ContactBook(cname, tel, sex, tcgroup);
}
if(cu!=null) {
List<ContactBook> cbList = dbmd.selectAllContactInfo(cb, cu.getUsername());
if(cbList!=null) {
request.getSession().setAttribute("cbList", cbList);
}
r(response,"index.jsp",0);
} else {
r(response,"message.jsp",0);
}
}
InsertContactBookServler.java
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
response.setContentType("text/html; charset=utf-8");
ContactUser cu = (ContactUser) request.getSession().getAttribute("currentUser");
String id = request.getParameter("id");
String cname = request.getParameter("cname");
String tel = request.getParameter("tel");
String sex = request.getParameter("sex");
String tcgroup = request.getParameter("tcgroup");
DataBaseMethodDal dbmd = new DataBaseMethodDal();
ContactBook cb = new ContactBook(cname, tel, sex, tcgroup);
if (id == null || id.trim().length() <= 0) {
if (DBHelper.checkParam(cname, tel, sex, tcgroup)) {
int a = dbmd.insertBook(cb, cu.getUsername());
if (a > 0) {
r(response, "ShowContactInfoServlet", 1);
} else {
r(response, "message.jsp", 4);
}
} else {
r(response, "message.jsp", 1);
}
} else {
cb.setId(Integer.parseInt(id));
int a = dbmd.updateBook(cb, cu.getUsername());
if (a > 0) {
r(response, "ShowContactInfoServlet", 1);
} else {
r(response, "message.jsp", 4);
}
}
}
DelContactBookServler.java
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
response.setContentType("text/html; charset=utf-8");
ContactUser cu = (ContactUser) request.getSession().getAttribute("currentUser");
String id = request.getParameter("id");
String cname = request.getParameter("cname");
String tel = request.getParameter("tel");
String sex = request.getParameter("sex");
String tcgroup = request.getParameter("tcgroup");
DataBaseMethodDal dbmd = new DataBaseMethodDal();
ContactBook cb = new ContactBook(cname, tel, sex, tcgroup);
if (id == null || id.trim().length() <= 0) {
if (DBHelper.checkParam(cname, tel, sex, tcgroup)) {
int a = dbmd.insertBook(cb, cu.getUsername());
if (a > 0) {
r(response, "ShowContactInfoServlet", 1);
} else {
r(response, "message.jsp", 4);
}
} else {
r(response, "message.jsp", 1);
}
} else {
cb.setId(Integer.parseInt(id));
int a = dbmd.updateBook(cb, cu.getUsername());
if (a > 0) {
r(response, "ShowContactInfoServlet", 1);
} else {
r(response, "message.jsp", 4);
}
}
}
ContactBook.java
import java.io.Serializable;
import java.util.Date;
public class ContactBook implements Serializable {
private static final long serialVersionUID = 1L;
private Integer id;
private String name;
private String tel;
private String sex;
private Date addtime;
private String tcgroup;
private String username;
public ContactBook() {
super();
}
public ContactBook(String name, String tel, String sex, String tcgroup) {
super();
this.name = name;
this.tel = tel;
this.sex = sex;
this.tcgroup = tcgroup;
}
public ContactBook(Integer id, String name, String tel, String sex, Date addtime, String tcgroup, String username) {
super();
this.id = id;
this.name = name;
this.tel = tel;
this.sex = sex;
this.addtime = addtime;
this.tcgroup = tcgroup;
this.username = username;
}
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 String getTel() {
return tel;
}
public void setTel(String tel) {
this.tel = tel;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getAddtime() {
return addtime;
}
public void setAddtime(Date addtime) {
this.addtime = addtime;
}
public String getTcgroup() {
return tcgroup;
}
public void setTcgroup(String tcgroup) {
this.tcgroup = tcgroup;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
@Override
public String toString() {
return "ContactBook [id=" + id + ", name=" + name + ", tel=" + tel + ", sex=" + sex + ", addtime=" + addtime
+ ", tcgroup=" + tcgroup + ", username=" + username + "]";
}
}
ContactUser.java
import java.io.Serializable;
import java.util.Date;
public class ContactUser implements Serializable {
private static final long serialVersionUID = 1L;
private String username;
private String password;
private String realname;
private Date createtime;
public ContactUser() {
}
public ContactUser(String username, String password, String realname, Date createtime) {
this.username = username;
this.password = password;
this.realname = realname;
this.createtime = createtime;
}
public ContactUser(String username, String password, String realname) {
this.username = username;
this.password = password;
this.realname = realname;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getRealname() {
return realname;
}
public void setRealname(String realname) {
this.realname = realname;
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
}
DBHelper.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBHelper {
public static final String USERNAME = "test";
public static final String PASSWORD = "test";
public static final String DRIVER = "oracle.jdbc.OracleDriver";
public static final String URL = "jdbc:oracle:thin:@localhost:1521:xe";
public static Connection getConnection() {
Connection conn = null;
try {
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void destroy(Connection conn, Statement sm, ResultSet rs) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
if (sm != null) {
try {
sm.close();
} catch (SQLException e) {
e.printStackTrace();
}
sm = null;
}
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
}
public static boolean checkParam(String... args) {
for (String s : args) {
if (s == null || s.trim().length() < 1) {
return false;
}
}
return true;
}
}
DateConvertor.java
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
public class DateConvertor {
private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
public static Date putString2Date(String str) {
Date date = null;
if(str.trim().length()<=0) {
return null;
}
try {
date = new Date(sdf.parse(str).getTime());
} catch (ParseException e) {
e.printStackTrace();
}
return date;
}
public static String putDate2String(Date dd) {
return sdf.format(dd);
}
}
DataBaseMethodDal.java
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.hanqi.maya.model.ContactBook;
import com.hanqi.maya.model.ContactUser;
public class DataBaseMethodDal {
private Connection conn;
private PreparedStatement ps;
private ResultSet rs;
public void init(String sql) {
conn = DBHelper.getConnection();
try {
ps = conn.prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
}
}
public int insertUser(ContactUser cu) {
String sql = "insert into tc_user values(?,?,?,sysdate)";
init(sql);
int a = -1;
try {
ps.setString(1, cu.getUsername());
ps.setString(2, cu.getPassword());
ps.setString(3, cu.getRealname());
a = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return a;
}
public ContactUser selectUser(String username, String password) {
String sql = "select * from tc_user t where t.username=? and t.password=?";
init(sql);
ContactUser user = null;
try {
ps.setString(1, username);
ps.setString(2, password);
rs = ps.executeQuery();
while (rs.next()) {
user = new ContactUser(rs.getString("username"), rs.getString("password"), rs.getString("realname"),
rs.getDate("createtime"));
}
} catch (SQLException e) {
e.printStackTrace();
}
return user;
}
public List<ContactBook> selectAllContactInfo(ContactBook book, String username) {
String sqlplus = "";
if (book != null) {
if (book.getName().trim().length() > 0) {
sqlplus += " and t.cname like '%" + book.getName() + "%' ";
}
if (book.getTel().trim().length() > 0) {
sqlplus += " and t.tel like '%" + book.getTel() + "%' ";
}
if (book.getSex().trim().length() > 0) {
sqlplus += " and t.sex = '" + book.getSex() + "' ";
}
if (book.getTcgroup().trim().length() > 0) {
sqlplus += " and t.tcgroup = '" + book.getTcgroup() + "' ";
}
}
String sql = "select * from TC_CONTACT t where t.username = ?" + sqlplus;
System.out.println(sql);
init(sql);
List<ContactBook> cbList = null;
try {
ps.setString(1, username);
rs = ps.executeQuery();
if (rs != null) {
cbList = new ArrayList<ContactBook>();
while (rs.next()) {
ContactBook cb = new ContactBook(rs.getInt("id"), rs.getString("cname"), rs.getString("tel"),
rs.getString("sex"), rs.getTimestamp("addtime"), rs.getString("tcgroup"),
rs.getString("username"));
cbList.add(cb);
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return cbList;
}
public int insertBook(ContactBook cb, String username) {
String sql = "insert into TC_CONTACT values(test.nextval, ?,?,?,sysdate,?,?)";
init(sql);
int a = -1;
try {
ps.setString(1, cb.getName());
ps.setString(2, cb.getTel());
ps.setString(3, cb.getSex());
ps.setString(4, cb.getTcgroup());
ps.setString(5, username);
a = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return a;
}
public int delContactBook(String ids) {
String sql = "delete TC_CONTACT t where t.id in (" + ids + ")";
init(sql);
int a = -1;
try {
a = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return a;
}
public int updateBook(ContactBook cb, String username) {
String sql = "update TC_CONTACT t set t.cname=?, t.sex=?, t.tel=?, t.tcgroup=? "
+ "where t.username=? and t.id=?";
init(sql);
int a = -1;
try {
ps.setString(1, cb.getName());
ps.setString(2, cb.getSex());
ps.setString(3, cb.getTel());
ps.setString(4, cb.getTcgroup());
ps.setString(5, username);
ps.setInt(6, cb.getId());
a = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return a;
}
}
