模糊查询程序如下:
<%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page import="java.sql.*" contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>模糊查询</title>
</head>
<body>
<%--根据输入学生姓名模糊查询,如果查找不到就显示“查无此人”--%>
<!--创建一个文本输入框和按钮-->
<form action="Search.jsp" method="post">
查找的姓名 <input type="text" name="stuName"><br>
<input type="submit" value="查询">
</form>
<%
try {
request.setCharacterEncoding("UTF-8");
String stuName = request.getParameter("stuName");
if(stuName != null && !stuName.isEmpty()){
//导入JDBC驱动
Class.forName("com.mysql.jdbc.Driver");
//创建数据库连接
String URL = "jdbc:mysql://localhost:3306/dbjsp";
String account = "root";
String passWord = "1234567890";
Connection connection = DriverManager.getConnection(URL, account, passWord);
//创建statement类和查询
//JDBC中 TYPE_FORWARD_ONLY 类型的结果集只能向前移动,而 ResultSet.TYPE_SCROLL_SENSITIVE 类型的结果集则允许向前和向后移动。
String selectSQL = "select * from student where name like ?";
PreparedStatement statement = connection.prepareStatement(selectSQL,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
statement.setString(1, "%" + stuName + "%");
ResultSet rs = statement.executeQuery();
// 创建HTML表格
out.println("<table border='1'>");
out.println("<tr>");
out.println("<th>id</th>");
out.println("<th>pId</th>");
out.println("<th>no</th>");
out.println("<th>name</th>");
out.println("<th>sex</th>");
out.println("<th>birthdate</th>");
out.println("</tr>");
//使用rs.getMetaData()获取ResultSet的元数据,然后使用getColumnCount()方法获取列数
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
// 遍历ResultSet并打印出每条记录的信息
if (rs.next()) {
//rs.previous();
do{
out.println("<tr>");
for (int i = 1; i <= columnCount; i++) {
out.println("<td>" + rs.getString(i) + "</td>");
}
out.println("</tr>");
}while(rs.next());
// while (rs.next()) {
// out.println("<tr>");
// for (int i = 1; i <= columnCount; i++) {
// out.println("<td>" + rs.getString(i) + "</td>");
// }
// out.println("</tr>");
// }
}else{
out.println("查无此人");
}
// 4.关闭ResultSet、Statement和Connection
rs.close();
statement.close();
connection.close();
}
}
catch (Exception e) {
out.print(e);
}
%>
</body>
</html>
使用do...while循环就不需要使用previous()方法了。
if (rs.next()) {
do{
out.println("<tr>");
for (int i = 1; i <= columnCount; i++) {
out.println("<td>" + rs.getString(i) + "</td>");
}
out.println("</tr>");
}while(rs.next());
}else{
out.println("查无此人");
}
如果使用rs.previous();的话,
PreparedStatement需要添加TYPE_FORWARD_ONLY 和ResultSet.TYPE_SCROLL_SENSITIVE 。
PreparedStatement statement = connection.prepareStatement(selectSQL,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
if (rs.next()) {
rs.previous();
while (rs.next()) {
out.println("<tr>");
for (int i = 1; i <= columnCount; i++) {
out.println("<td>" + rs.getString(i) + "</td>");
}
out.println("</tr>");
}
}else{
out.println("查无此人");
}