数据库student中有一个学生信息表student(没错数据库和表的名字一样),如下:
其中id是主码,设置成自动递增且不为null;name也设置成不为null。现欲通过JDBC的Statement和PreparedStatement,简单实现对该表的增删改查。
首先是主菜单页面(index.jsp):
<%@ page language="java" import="java.util.*,java.sql.*" pageEncoding="UTF-8"%>
<html>
<body>
<center>
<h2>请选择:</h2>
<table>
<tr>
<td><a href="a.jsp">增</a></td>
<td><a href="b.jsp">删</a></td>
<td><a href="c.jsp">改</a></td>
<td>查(<a href="f.jsp">列出表中所有信息</a>,<a href="d.jsp">按姓名查询</a>)</td>
</tr>
</table>
</center>
</body>
</html>
然后是四个分别实现了增删改查的用户界面:
增(a.jsp):
<%@ page language="java" import="java.util.*,java.sql.*" pageEncoding="UTF-8"%>
<html>
<body>
<%
session.setAttribute("operation","add");
%>
<center>
<h2>请输入要添加的信息</h2>
<form action="e.jsp" method="post">
<table>
<tr><td>姓名(必填):</td><td><input type="text" name="name"></td></tr>
<tr><td>年龄(选填):</td><td><input type="text" name="age"></td></tr>
<tr><td>性别(选填):</td><td><input type="text" name="sex"></td></tr>
<tr><td colspan="2" align="center"><input type="submit" value="添加"></td></tr>
</table>
</form>
<a href="index.jsp">主菜单</a>
</center>
</body>
</html>
删(b.jsp):
<%@ page language="java" import="java.util.*,java.sql.*" pageEncoding="UTF-8"%>
<html>
<body>
<%
session.setAttribute("operation","delete");
%>
<center>
<h2>请输入</h2>
<form action="e.jsp" method="post">
<table>
<tr><td>姓名:</td><td><input type="text" name="name"></td><td><input type="submit" value="删除"></td></tr>
</table>
</form>
<a href="index.jsp">主菜单</a>
</center>
</body>
</html>
改(c.jsp):
<%@ page language="java" import="java.util.*,java.sql.*" pageEncoding="UTF-8"%>
<html>
<body>
<%
session.setAttribute("operation","update");
%>
<center>
<h2>请输入要修改的信息</h2>
<form action="e.jsp" method="post">
<table>
<tr><td>要修改的姓名(必填):</td><td><input type="text" name="oldname"></td></tr>
<tr><td>修改后的姓名(选填):</td><td><input type="text" name="newname"></td></tr>
<tr><td>修改后的年龄(选填):</td><td><input type="text" name="age"></td></tr>
<tr><td>修改后的性别(选填):</td><td><input type="text" name="sex"></td></tr>
<tr><td colspan="2" align="center"><input type="submit" value="修改"></td></tr>
</table>
</form>
<a href="index.jsp">主菜单</a>
</center>
</body>
</html>
查(d.jsp):
<%@ page language="java" import="java.util.*,java.sql.*" pageEncoding="UTF-8"%>
<html>
<body>
<%
session.setAttribute("operation","select");
%>
<center>
<h2>请输入</h2>
<form action="e.jsp" method="post">
<table>
<tr><td>姓名:</td><td><input type="text" name="name"></td><td><input type="submit" value="查询"></td></tr>
</table>
</form>
<a href="index.jsp">主菜单</a>
</center>
</body>
</html>
然后是操作处理页(e.jsp):
<%@ page language="java" import="java.util.*,java.sql.*,java.io.*" pageEncoding="UTF-8"%>
<html>
<body>
<center>
<%
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/student?user=root&password=1");
String operation=(String)session.getAttribute("operation");
String sql="";//简单初始化
PreparedStatement ps=con.prepareStatement(sql);//简单初始化
switch (operation) {
case "add":
sql="insert into student(name,age,sex) values(?,?,?)";
ps=con.prepareStatement(sql);
ps.setString(1,request.getParameter("name"));
ps.setInt(2,Integer.parseInt(request.getParameter("age")));
ps.setString(3,request.getParameter("sex"));
ps.executeUpdate();
response.sendRedirect("http://localhost:8080/jsp/success.jsp");
break;
case "delete":
sql="delete from student where name=?";
ps=con.prepareStatement(sql);
ps.setString(1,request.getParameter("name"));
ps.executeUpdate();
response.sendRedirect("http://localhost:8080/jsp/success.jsp");
break;
case "update":
boolean flag=false;//一旦修改完某个属性,就把flag置为true
String oldname=request.getParameter("oldname");
String newname=request.getParameter("newname");
String age=request.getParameter("age");
String sex=request.getParameter("sex");
sql="update student set ";
if(!newname.isEmpty()){
sql=sql+"name='"+newname+"'";
flag=true;
}
if(!age.isEmpty()){
if(flag){//之前修改过某属性
sql+=",";
}
sql=sql+"age="+age;
flag=true;
}
if(!sex.isEmpty()){
if(flag){//之前修改过某属性
sql+=",";
}
sql=sql+"sex='"+sex+"'";
flag=true;//可以省略,因为sex是最后一个属性
}
sql=sql+" where name='"+oldname+"'";
Statement st=con.createStatement();
st.executeUpdate(sql);
st.close();
response.sendRedirect("http://localhost:8080/jsp/success.jsp");
// out.print(sql);
break;
case "select":
sql="select id,name,age,sex from student where name=?";
ps=con.prepareStatement(sql);
ps.setString(1,request.getParameter("name"));
ResultSet rs=ps.executeQuery();
while(rs.next()){
out.print("id:"+rs.getInt("id")+" name:"+rs.getString("name")+" age:"+rs.getString("age")+" sex:"+rs.getString("sex"));
}
break;
}
ps.close();
con.close();
%>
</center>
</body>
</html>
因为我把查询分成了“列出表中所有信息”和“按姓名查询”这两种方式,所以我把前者的实现单独写在了一个页面(f.jsp),后者的实现仍然放在操作处理页e.jsp里面。
f.jsp:
<%@ page language="java" import="java.util.*,java.sql.*" pageEncoding="UTF-8"%>
<html>
<body>
<head><title>列出表中所有信息</title></head>
<center>
<h2>表中所有信息如下:</h2>
<table>
<th>id</th><th>name</th><th>age</th><th>sex</th>
<%
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/student?user=root&password=1");
Statement st=con.createStatement();
ResultSet rs=st.executeQuery("select id,name,age,sex from student");
while(rs.next()){
%>
<tr>
<td><%=rs.getInt("id") %></td>
<td><%=rs.getString("name") %></td>
<td><%=rs.getInt("age") %></td>
<td><%=rs.getString("sex") %></td>
</tr>
<%
}
rs.close();
st.close();
con.close();
%>
</table>
<a href="index.jsp">主菜单</a>
</center>
</body>
</html>
最后是操作成功页面(success.jsp):
<%@ page language="java" import="java.util.*,java.sql.*" pageEncoding="UTF-8"%>
<html>
<body>
<center>
<h2>操作成功!</h2>
<a href="index.jsp">主菜单</a>
</center>
</body>
</html>
一点心得:PreparedStatement预编译貌似在一个jsp页只能执行一次。如果在同一jsp页上存在多条预编译语句,程序只会选择性的执行其中一条语句(一般是最后一条)。此时只能使用效率较低的Statement。