对于查询代码的进一步优化
本次没有相应模板，
//index.jsp
<%@ page contentType="text/html;charset=UTF-8" pageEncoding="UTF-8" language="java" %>
<html>
<head>
<title>查询界面</title>
</head>
<body bgcolor="LightCyan">
<h4>输入查询条件！</h4>
<form action="print.jsp" method="post">
<table border="1">
<tr>
<td>课程名称:</td>
<td><input type="text" name="cla"/></td>
</tr>
<tr>
<td>教师姓名:</td>
<td><input type="text" name="teacher"/></td>
</tr>
<tr>
<td>授课地点:</td>
<td><input type="text" name="whe"/></td>
</tr>
<tr>
<td><input type="submit" value="查询"></td>
<td><input type="reset" value="重置"></td>
</tr>
</table>
</form>
</body>
</html>
//print.jsp
<%@ page import="org.example.user" %>
<%@ page import="java.sql.*" %>
<%--
Created by IntelliJ IDEA.
User: 12140
Date: 2022/10/11
Time: 0:14
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" pageEncoding="UTF-8" language="java" %>
<html>
<head>
<title>显示界面</title>
</head>
<body>
<%
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
String url = "jdbc:mysql://localhost:3306/ad?useUnicode=true&characterEncoding=UTF-8";
String username="root";
String password="20214063";
Connection conn= null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;//执行sql语句用来返回单个 ResultSet 对象
try {
conn = DriverManager.getConnection(url,username,password);
request.setCharacterEncoding("UTF-8");//设置字符编码，避免出现乱码
String cla = request.getParameter("cla");
String teacher=request.getParameter("teacher");
String whe=request.getParameter("whe");
String sql = "select * from super1 where cla=? and teacher=? and whe=?";
preparedStatement = conn.prepareStatement(sql);
preparedStatement.setString(1,cla);
preparedStatement.setString(2,teacher);
preparedStatement.setString(3,whe);
resultSet = preparedStatement.executeQuery();
resultSet.last();
} catch (SQLException e) {
e.printStackTrace();
}
%>
<center>
<table border="2" bgcolor="ccceee" width="650">
<tr>
<td>课程名称</td>
<td>教师姓名</td>
<td>授课地点</td>
</tr>
<%
resultSet.beforeFirst();
while (resultSet.next()){
%> <tr align="center">
<td><%=resultSet.getString("cla")%></td>
<td><%=resultSet.getString("teacher")%></td>
<td><%=resultSet.getString("whe")%></td>
</tr>
<%}%>
</table>
</center>
<%
if (resultSet!=null){
resultSet.close();
}
if (preparedStatement!=null){
preparedStatement.close();
}
if (conn!=null){
conn.close();
}
%>
</body>
</html>
//user.java
package org.example;
import java.sql.*;
public class user {
private static String cla;
private static String teacher;
private static String whe;
static StringBuffer queryResultBy;
public void setCla(String cla){
this.cla=cla;
}
public String getCla(){
return cla;
}
public void setTeacher(String teacher){
this.teacher=teacher;
}
public String getTeacher(){
return teacher;
}
public void setWhe(String whe){
this.whe=whe;
}
public String getWhe(){
return whe;
}
//实现条件查询
public static StringBuffer getQueryResultBy(){
queryResultBy=new StringBuffer();
Connection con = null;
Statement st = null;
ResultSet rs=null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
System.out.println("注册驱动失败！");
e.printStackTrace();
}
try {
con= DriverManager.getConnection("jdbc:mysql://localhost:3306/ad?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC","root","20214063");
System.out.println("数据库连接成功！");
st=con.createStatement();
String sql="select * from super1 where 1=1 ";
if(cla!=null){
sql=sql+" and cla='"+cla+"'";
}
if(teacher!=null){
sql=sql+" and teacher='"+teacher+"'";
}
if(whe!=null){
sql=sql+" and whe='"+whe+"'";
}
rs=st.executeQuery(sql);
queryResultBy.append("<table border=1 >");
queryResultBy.append("<table bgcolor=\"#ffffdd\" border=\"auto\"width=\"auto\" >");
queryResultBy.append("<tr>");
queryResultBy.append("<th>课程名称</th>");
queryResultBy.append("<th>教师姓名</th>");
queryResultBy.append("<th>授课地点</th>");
queryResultBy.append("</tr>");
while(rs.next()){
queryResultBy.append("<tr>");
queryResultBy.append("<td>").append(rs.getString(1)).append("</td>");
queryResultBy.append("<td>").append(rs.getString(2)).append("</td>");
queryResultBy.append("<td>").append(rs.getString(3)).append("</td>");
queryResultBy.append("</tr>");
}
queryResultBy.append("</table >");
}catch (SQLException e) {
e.printStackTrace();
} finally{
try{
if(rs!=null){
rs.close();
}
if(st!=null){
st.close();
}
if(con!=null){
con.close();
}
}catch (SQLException e) {
e.printStackTrace();
}
}
return queryResultBy;
}
}
