JSP页面查询及增丶删功能实现

一、查询语句

public class PhoneInfoDAL {

//查询所有信息
public List<Phone> getAllPhone(){
//获取连接对象
Connection conn= Dbutils.getConn();
//获取操作对象
PreparedStatement stat=null;
//获取结果集对象
ResultSet rs=null;
//创建集合
List<Phone> phoneList=new ArrayList<Phone>();
//定义SQL语句
String sql="select phoneid,phonename,phonenumber,qq,address,otherinfo from phoneinfo";
//获取操作对象,预处理SQL语句
try {
stat= conn.prepareStatement(sql);
//执行SQL语句,返回结果集
rs= stat.executeQuery();
while (rs.next()) {
//创建对象
Phone p=new Phone();
p.setPhoneId(rs.getInt("phoneid"));
p.setPhoneName(rs.getString("phonename"));
p.setPhoneNumber(rs.getString("phonenumber"));
p.setQq(rs.getString("qq"));
p.setAddress(rs.getString("address"));
p.setOtherInfo(rs.getString("otherinfo"));
//将所查询的信息保存到集合
phoneList.add(p);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
Dbutils.colse(rs, stat, conn);
}
return phoneList;
}

//删除信息
public int deletePhoneByNo(int pNo){
//获取数据库连接
Connection conn= Dbutils.getConn();
//获取操作对象
PreparedStatement stat=null;
//返回受影响的行数
int row=0;
//定义SQL语句
String sql="DELETE from phoneinfo WHERE phoneid=?";
try {
//获取链接对象,预处理SQL语句
stat=conn.prepareStatement(sql);
stat.setInt(1, pNo);
//执行SQL语句，返回受影响的行数
row=stat.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
Dbutils.colse(null, stat, conn);
}
return row;
}

//添加信息
public int addPhone(Phone p){
//获取数据库连接
Connection conn= Dbutils.getConn();
//获取操作对象
PreparedStatement stat=null;
int row=0;
//定义SQL语句
String sql="insert into phoneinfo(phonename,phonetype,phonenumber,qq,address,otherinfo) VALUES(?,?,?,?,?,?)";
try {
//获取链接对象,预处理SQL语句
stat=conn.prepareStatement(sql);
stat.setString(1, p.getPhoneName());
stat.setInt(2, p.getPhoneType());
stat.setString(3, p.getPhoneNumber());
stat.setString(4, p.getQq());
stat.setString(5, p.getAddress());
stat.setString(6, p.getOtherInfo());
//执行SQL语句，返回受影响的行数
row=stat.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
Dbutils.colse(null, stat, conn);
}
return row;
}

}

二、servlet类

//删除信息

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


/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
this.doPost(request, response);
}


/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
//设置获取值得格式
request.setCharacterEncoding("UTF-8");
//获取值
int phoneId=Integer.parseInt(request.getParameter("id").toString());
//调用删除方法
PhoneInfoDAL dal=new PhoneInfoDAL();
dal.deletePhoneByNo(phoneId);
//删除后显示数据
request.getRequestDispatcher("ShowPhone.jsp").forward(request, response);
}


}

//添加信息

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


/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
this.doPost(request, response);
}


/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
request.setCharacterEncoding("UTF-8"); 
//获取界面传过来的值
String phoneName=request.getParameter("txtName");
int phonetype=Integer.parseInt(request.getParameter("txtPeo"));
String phoneNumber=request.getParameter("txtPhone");
String qq=request.getParameter("txtQQ");
String address=request.getParameter("txtAddress");
String otherInfo=request.getParameter("txtOther");

Phone p=new Phone();
p.setPhoneName(phoneName);
p.setPhoneType(phonetype);
p.setPhoneNumber(phoneNumber);
p.setQq(qq);
p.setAddress(address);
p.setOtherInfo(otherInfo);
//调用添加方法
PhoneInfoDAL dal=new PhoneInfoDAL();
dal.addPhone(p);
//添加成功后跳转到首页显示
request.getRequestDispatcher("ShowPhone.jsp").forward(request, response);
}


}

三、web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">
  <display-name>PhoneManagement</display-name>
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
    <welcome-file>index.htm</welcome-file>
    <welcome-file>index.jsp</welcome-file>
    <welcome-file>default.html</welcome-file>
    <welcome-file>default.htm</welcome-file>
    <welcome-file>default.jsp</welcome-file>
  </welcome-file-list>
  <servlet>
    <servlet-name>deletePhone</servlet-name>
    <servlet-class>com.xnzy1501.Phone.DeletePhoneServlet</servlet-class>
  </servlet>
  <servlet>
    <servlet-name>addPhone</servlet-name>
    <servlet-class>com.xnzy1501.Phone.AddPhoneServlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>addPhone</servlet-name>
    <url-pattern>/addPhone</url-pattern>
  </servlet-mapping>
  <servlet-mapping>
    <servlet-name>deletePhone</servlet-name>
    <url-pattern>/deletePhone</url-pattern>
  </servlet-mapping>
</web-app>

四丶页面布局及功能实现

//查询丶删除

<%@page import="java.util.List"%>
<%@page import="com.xnzy1501.Phone.Phone"%>
<%@page import="com.xnzy1501.Phone.PhoneInfoDAL"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!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>
<style type="text/css">
td{
text-align: center;
width:100px;
height:50px;
}
#tdAdd{
text-align: right;
}
</style>
</head>
<body>
<%
PhoneInfoDAL dal=new PhoneInfoDAL();
List<Phone> phoneList=dal.getAllPhone();
%>
<p align="center">通讯录信息展示</p>
<table border="1" align="center">
<tr>
<td id="tdAdd" colspan="5" ><a href="AddPhone.jsp">添加通讯录信息</a></td>
</tr>
<tr>
<td>姓名</td>
<td>移动电话</td>
<td>QQ</td>
<td>通讯地址</td>
<td>备注</td>
<td>操作</td>
</tr>
<%
for(Phone p: phoneList ){
%>
<tr>

<td><%=p.getPhoneName()%></td>
<td><%=p.getPhoneNumber()%></td>
<td><%=p.getQq()%></td>
<td><%=p.getAddress()%></td>
<td><%=p.getOtherInfo()%></td>
<td>
<a href="deletePhone?id=<%=p.getPhoneId()%>">删除</a>
</td>
</tr>
<% }%>
</table>
</body>
</html>

//添加

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!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>
<style type="text/css">
td{
text-align: center;
width:150px;
height:30px;
}
#tdAdd{
text-align: right;
}
</style>
</head>
<body>
<!-- 提交到addPhone Servlet类处理 -->
<form action="addPhone" method="post">
<table border="1" align="center">
<tr>
<td id="tdAdd" colspan="5" >添加“缘来是你”</td>
</tr>
<tr>
<td>姓名：</td>
<td><input name="txtName" type="text"></td>
</tr>
<tr>
<td>群组：</td>
<td>
<select name="txtPeo">
<option>1</option>
<option>2</option>
</select>
</td>
</tr>
<tr>
<td>移动电话：</td>
<td><input name="txtPhone" type="text"></td>
</tr>
<tr>
<td>QQ：</td>
<td><input name="txtQQ" type="text"></td>
</tr>
<tr>
<td>通讯地址：</td>
<td><input name="txtAddress" type="text"></td>
</tr>
<tr>
<td>备注：</td>
<td><input name="txtOther" type="text"></td>
</tr>
<tr>
<td align="center" colspan="2">
<input type="submit" value="保存">
</td>
</tr>
</table>
</form>

</body>
</html>