How to retrieve data from the database using JSP pages?

1 year ago
Emily Johnson
4 minutes

To retrieve database data in a JSP page, the following steps need to be taken:

Import the database driver: First, make sure you have added the JAR file of the database driver to your project. This can be done by copying the JAR file of the driver to the WEB-INF/lib directory.

Setting up a database connection: In a JSP page, you can use Java code to establish a connection with a database. Typically, you should place the database connection logic in a Java class and then call it in the JSP page. When establishing the connection, you need to provide information such as the database URL, username, and password.

Execute SQL queries: Once you have successfully established a connection with the database, you can execute SQL queries to retrieve data. You can use a `java.sql.Statement` or `java.sql.PreparedStatement` object to execute the queries.

Processing query results: After executing the query, you will receive a `java.sql.ResultSet` object containing the data retrieved from the database. The methods of this object, such as `next()`, `getString()`, `getInt()`, can be used to iterate and read the query results.

Here is a simple example demonstrating how to retrieve database data in a JSP page.

<%@ page import="java.sql.*" %>
<%
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
        // 建立数据库连接
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String username = "root";
        String password = "password";
        conn = DriverManager.getConnection(url, username, password);
        // 执行查询
        String sql = "SELECT * FROM mytable";
        stmt = conn.createStatement();
        rs = stmt.executeQuery(sql);
        // 处理查询结果
        while (rs.next()) {
            String name = rs.getString("name");
            int age = rs.getInt("age");
            // 在页面上输出数据
            out.println("Name: " + name + ", Age: " + age);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        // 关闭数据库连接和资源
        if (rs != null) {
            try { rs.close(); } catch (SQLException e) { /* ignored */ }
        }
        if (stmt != null) {
            try { stmt.close(); } catch (SQLException e) { /* ignored */ }
        }
        if (conn != null) {
            try { conn.close(); } catch (SQLException e) { /* ignored */ }
        }
    }
%>

Please note that, for the sake of simplicity in the example, I directly performed the database connection and query operations in the JSP page. However, according to best practices, you should place this logic in a Java class (such as a Servlet) and call them from the JSP page.

bannerAds