create database addressdb;
use addressdb;
create table addressList(
id int primary key,
name varchar(20),
phone varchar(20),
address varchar(100),
email varchar(40)
);
//AddressBean.java
package beans;
public class AddressBean {
private int id;
private String name;
private String phone;
private String address;
private String email;
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 String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
}
//AddressDemo.java
package beans;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
public class AddressDemo extends ConnBean {
private Connection connection = null;
public void closePstmt(PreparedStatement pstmt) {
try {
if (connection != null)
connection.close();
connection = null;
} catch (SQLException e3) {
e3.printStackTrace();
}
}
public AddressBean[] getAllRecords() {
ResultSet rs = null;
PreparedStatement pstmt = null;
Collection list = new ArrayList();
try {
connection = getConnection();
pstmt = connection.prepareStatement("select * from addresslist");
rs = pstmt.executeQuery();
while (rs.next()) {
AddressBean address = new AddressBean();
address.setId(rs.getInt(1));
address.setName(rs.getString(2));
address.setPhone(rs.getString(3));
address.setAddress(rs.getString(4));
address.setEmail(rs.getString(5));
list.add(address);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
closePstmt(pstmt);
closeConnection(connection);
}
AddressBean[] records = (AddressBean[]) list
.toArray(new AddressBean[0]);
return records;
}
public boolean insertRecord(AddressBean record) {
PreparedStatement pstmt = null;
String insertStr = "insert into addressList values(?,?,?,?,?)";
try {
connection = getConnection();
pstmt = connection.prepareStatement(insertStr);
pstmt.setInt(1, record.getId());
pstmt.setString(2, ChineseStr(record.getName()));
pstmt.setString(3, ChineseStr(record.getPhone()));
pstmt.setString(4, ChineseStr(record.getAddress()));
pstmt.setString(5, ChineseStr(record.getEmail()));
pstmt.execute();
} catch (SQLException e) {
e.printStackTrace();
} finally {
closePstmt(pstmt);
closeConnection(connection);
}
return true;
}
public String ChineseStr(String str) { //过滤函数
if (str == null) {
str = "";
} else {
try {
str = (new String(str.getBytes("iso-8859-1"), "utf-8")).trim();
} catch (Exception e) {
e.printStackTrace(System.err);
}
}
return str;
}
public boolean deleteRecord(AddressBean record) {
PreparedStatement pstmt = null;
String deleteStr = "delete from addressList where id=?";
if (record == null)
return false;
try {
connection=getConnection();
pstmt = connection.prepareStatement(deleteStr);
pstmt.setInt(1, record.getId());
pstmt.execute();
} catch (SQLException e) {
e.printStackTrace();
} finally {
closePstmt(pstmt);
closeConnection(connection);
}
return true;
}
public boolean updateRecord(AddressBean ro, AddressBean rn) {
if (ro == null || rn == null)
return false;
PreparedStatement pstmt = null;
String updateStr = "update addressList set name=?,phone=?,address=?,email=?";
updateStr = updateStr + "where id=" + ro.getId();
try {
connection = getConnection();
pstmt = connection.prepareStatement(updateStr);
pstmt.setString(1, rn.getName());
pstmt.setString(2, rn.getPhone());
pstmt.setString(3, rn.getAddress());
pstmt.setString(4, rn.getEmail());
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
closePstmt(pstmt);
closeConnection(connection);
}
return true;
}
}
//ConnBean.java
package beans;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class ConnBean {
private String driver = "com.mysql.jdbc.Driver";
private String jdbcurl = "jdbc:mysql://localhost:3306/";
private String database = "addressDB";
private String userName = "root";
private String password = "110";
private Connection connection = null;
public ConnBean() {
getConnection();
}
public Connection getConnection() {
try {
Class.forName(driver);
connection = DriverManager.getConnection(jdbcurl + database,
userName, password);
} catch (ClassNotFoundException e1) {
e1.printStackTrace();
} catch (SQLException e2) {
e2.printStackTrace();
}
return connection;
}
public void closeConnection(Connection connection) {
try {
if (connection != null)
connection.close();
connection = null;
} catch (SQLException e3) {
e3.printStackTrace();
}
}
public String getDriver() {
return driver;
}
public void setDriver(String driver) {
this.driver = driver;
}
public String getJdbcurl() {
return jdbcurl;
}
public void setJdbcurl(String jdbcurl) {
this.jdbcurl = jdbcurl;
}
public String getDatabase() {
return database;
}
public void setDatabase(String database) {
this.database = database;
}
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;
}
}
// AddressLook.jsp查询
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="beans.*"%>
<%@ page import="java.sql.*"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>通讯录</title>
</head>
<!-- <script language="javascript">
function add() {
document.frm1.action="Index.jsp"
document.frm1.submit()
}
</script> -->
<body>
<jsp:useBean id="conn" scope="session" class="beans.AddressDemo" />
<center>
<table border="1">
<caption>通讯录</caption>
<tr>
<td>编号</td>
<td>姓名</td>
<td>电话</td>
<td>地址</td>
<td>E-mail</td>
</tr>
<%
request.setCharacterEncoding("UTF-8");
AddressBean[] records = conn.getAllRecords();
if (records != null) {
for (int i = 0; i < records.length; i++) {
out.println("<tr>");
out.println("<td>" + records[i].getId() + "</td>");
out.println("<td>" + records[i].getName() + "</td>");
out.println("<td>" + records[i].getPhone() + "</td>");
out.println("<td>" + records[i].getAddress() + "</td>");
out.println("<td>" + records[i].getEmail() + "</td>"
+ "</tr>");
}
}
%>
</table>
<hr>
<hr>
<hr>
<input type="button" value="添加"
οnclick="javascript:location.href='AddressInsert.jsp'" /> <input
type="button" value="删除"
οnclick="javascript:location.href='AddressDelete.jsp'" />
</center>
</body>
</html>
//AddressInsert.jsp 添加
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="beans.AddressBean,beans.ConnBean"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>添加纪录</title>
</head>
<body>
<table>
<form action="" method="post">
<center>添加记录</center>
<tr>
<td>编号</td>
<td><input type="text" name="id" colspan="2" /></td>
</tr>
<tr>
<td>姓名</td>
<td><input type="text" name="name" colspan="2" /></td>
</tr>
<tr>
<td>电话</td>
<td><input type="text" name="phone" colspan="2" /></td>
</tr>
<tr>
<td>地址</td>
<td><input type="text" name="address" colspan="2"></td>
</tr>
<tr>
<td>E-mail</td>
<td><textarea name="email" cols="20" rows="5" colspan="2"></textarea></td>
</tr>
<tr>
<td><input type="submit" value="添加"></td>
<td><input type="reset" value="重置"></td>
<td><input type="button" value="查看"
οnclick="javascript:location.href='AddressLook.jsp'" /></td>
</tr>
</form>
</table>
<jsp:useBean id="add" scope="session" class="beans.AddressBean"></jsp:useBean>
<jsp:setProperty property="*" name="add" />
<jsp:useBean id="conn" scope="session" class="beans.AddressDemo"></jsp:useBean>
<%
if (add.getId() != 0 && conn.insertRecord(add))
out.println("添加成功!");
%>
</body>
</html>
//AddressDelete.jsp 删除
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="beans.*"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>删除数据</title>
</head>
<body>
<p>
<jsp:useBean id="conn" scope="session" class="beans.AddressDemo" />
<form action="" method="post">
<table border="1">
<caption>通讯录</caption>
<tr>
<th>编号</th>
<th>姓名</th>
<th>电话</th>
<th>地址</th>
<th>E-mail</th>
<th>选择</th>
</tr>
<%
AddressBean[] records = conn.getAllRecords();
if (records != null) {
for (int i = 0; i < records.length; i++) {
out.println("<tr>" + "<td>" + records[i].getId() + "</td>");
out.println("<td>" + records[i].getName() + "</td>");
out.println("<td>" + records[i].getPhone() + "</td>");
out.println("<td>" + records[i].getAddress() + "</td>");
out.println("<td>" + records[i].getEmail() + "</td>");
%>
<td><input type="checkbox" name="<%="check" + i%>"
value="<%=i%>"></td>
<%
out.println("</tr>");
}
}
%>
</table>
<input type="submit" value="提交" />
</form>
<%
String[] check = new String[records.length];
for (int i = 0; i < check.length; i++) {
check[i] = request.getParameter("check" + i);
if (check[i] == null)
check[i] = "";
if (check[i].equals("" + i)) {
if (conn.deleteRecord(records[i])) {
out.println("<hr>删除成功!");
response.setHeader("refresh", "1");
}
}
}
%>
</p>
</body>
</html>
注意:两种乱码。一种为网数据库写入的信息为乱码,可以采用“过滤函数”。另一种为接收数据显示乱码,可以用代码转换“request.setCharacterEncoding("UTF-8");”。