jspservlet多表查询
时间: 2023-07-30 21:08:13 浏览: 48
JSP和Servlet是Java Web开发中的两个重要组件,可以结合使用完成多表查询的操作。以下是一个简单的示例:
1. 创建一个JSP页面,用于接收查询参数和显示查询结果。
2. 在JSP页面中编写表单,包含需要查询的字段和条件。
3. 在Servlet中获取表单提交的参数,并使用JDBC连接数据库执行查询操作。
4. 在Servlet中将查询结果存储到List或Map等数据结构中,并将其作为属性存储到request中。
5. 在JSP页面中使用EL表达式获取request中的属性,并使用JSTL标签库显示查询结果。
示例代码如下:
JSP页面:
```
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<html>
<head>
<title>多表查询</title>
</head>
<body>
<form action="queryServlet" method="post">
<label>查询条件:</label>
<input type="text" name="condition">
<input type="submit" value="查询">
</form>
<c:if test="${not empty result}">
<table>
<c:forEach var="row" items="${result}">
<tr>
<td>${row.id}</td>
<td>${row.name}</td>
<td>${row.age}</td>
</tr>
</c:forEach>
</table>
</c:if>
</body>
</html>
```
Servlet代码:
```
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class QueryServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
private static final String URL = "jdbc:mysql://localhost:3306/test";
private static final String USER = "root";
private static final String PASSWORD = "root";
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
String condition = request.getParameter("condition");
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(URL, USER, PASSWORD);
String sql = "SELECT * FROM table1 t1, table2 t2 WHERE t1.id = t2.id AND t1.name LIKE ?";
stmt = conn.prepareStatement(sql);
stmt.setString(1, "%" + condition + "%");
rs = stmt.executeQuery();
List<Result> result = new ArrayList<Result>();
while (rs.next()) {
Result row = new Result();
row.setId(rs.getInt("id"));
row.setName(rs.getString("name"));
row.setAge(rs.getInt("age"));
result.add(row);
}
request.setAttribute("result", result);
request.getRequestDispatcher("result.jsp").forward(request, response);
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
if (conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private static class Result {
private int id;
private String name;
private int age;
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 int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
}
}
```
在上面的示例中,我们使用了MySQL数据库,同时查询了两个表的数据,并将查询结果显示在JSP页面上。需要根据实际情况调整SQL语句和表名等参数。