根据用户输入的商品号从数据库中查询该商品信息,或者查询所有商品信息。遵循MVC设计模式,其中视图有queryProduct.jsp、displayProduct.jsp、displayAllProduct.jsp和error.jsp几个页面,Product类实现模型,QueryProductServlet类实现控制器。数据库表为products。连接数据库的写法
时间: 2023-12-06 16:44:30 浏览: 153
运用数据库和JSP实现商品查询
以下是连接数据库的代码示例,其中使用了JDBC连接MySQL数据库:
```
import java.sql.*;
public class DBConnection {
private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
private static final String DB_URL = "jdbc:mysql://localhost/products";
private static final String USER = "root";
private static final String PASS = "password";
public static Connection getConnection() {
Connection conn = null;
try {
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(DB_URL, USER, PASS);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
}
```
在以上代码中,我们定义了JDBC驱动的名称、数据库的URL、用户名和密码。getConnection方法用于获取数据库的连接,并返回一个Connection对象。
在QueryProductServlet类中,我们可以使用上述的DBConnection类获取数据库连接,并执行SQL语句查询数据,然后将结果存储到request对象中,用于在JSP页面中显示。具体的代码实现可以参考以下示例:
```
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
@WebServlet("/QueryProductServlet")
public class QueryProductServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String productId = request.getParameter("productId");
List<Product> productList = new ArrayList<>();
try (Connection conn = DBConnection.getConnection()) {
String sql;
PreparedStatement pstmt;
if (productId == null) {
// 查询所有商品信息
sql = "SELECT * FROM products";
pstmt = conn.prepareStatement(sql);
} else {
// 根据商品号查询商品信息
sql = "SELECT * FROM products WHERE id = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, productId);
}
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
Product product = new Product();
product.setId(rs.getInt("id"));
product.setName(rs.getString("name"));
product.setPrice(rs.getDouble("price"));
productList.add(product);
}
rs.close();
} catch (SQLException e) {
e.printStackTrace();
request.getRequestDispatcher("error.jsp").forward(request, response);
}
if (productId == null) {
// 显示所有商品信息
request.setAttribute("productList", productList);
request.getRequestDispatcher("displayAllProduct.jsp").forward(request, response);
} else if (productList.size() == 0) {
// 商品号不存在
request.getRequestDispatcher("error.jsp").forward(request, response);
} else {
// 显示单个商品信息
request.setAttribute("product", productList.get(0));
request.getRequestDispatcher("displayProduct.jsp").forward(request, response);
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
```
以上代码中,我们首先获取用户输入的商品号,然后使用DBConnection类获取数据库连接,根据用户输入的商品号,执行不同的SQL语句查询数据。查询结果存储到List<Product>中,然后根据查询结果跳转到不同的JSP页面显示查询结果。如果查询出错,则跳转到error.jsp页面显示错误信息。
阅读全文