An example explains the method of obtaining data from ResultSet result set by JSP

  • 2021-09-20 21:16:21
  • OfStack

Get all the records


<%@page language="java" contentType="text/html;charset=gb2312"%>
<%@page import="java.sql.*" %>
<!DOCTYPE html>
<html>
  <head>
    <title> Query the records of all users </title>
  </head>
  <body>
    <%
      String url = "jdbc:mysql://localhost:3306/javaweb";// Connecting to the database url Address 
      String user = "root";// User name for logging in to the database 
      String password = "zhangda890126;;";// Password for the user name that logs in to the database 
      Connection conn = null;
      try{
        Class.forName("com.mysql.jdbc.Driver");// Loading JDBC Driver 
        conn = DriverManager.getConnection(url,user,password);// Linked database 
      }catch(ClassNotFoundException e){
        out.println(" Driver class not found ");// Prompt information when an exception is thrown 
      }catch(SQLException e){
        out.println(" Link MySQL Database failure ");// Deal with SQLException Anomaly 
      }
      try{
         
        Statement stmt = conn.createStatement();// Create a statement object Statement
        String queryAll = "SELECT * FROM user";// Query all users 
        ResultSet rs = stmt.executeQuery(queryAll);
        while(rs.next()){
          int userid = rs.getInt(1);// Get the 1 Fields userid Value of 
          String username = rs.getString(2);// Get the 2 Fields username Value of 
          String userpassword = rs.getString(3);// Get the 3 Fields password Value of 
           
          // Print out all user information 
          out.println(" Users ID : "+userid+"  User name: "+username+"  User's password  "+userpassword+"<br />");
        }
      }catch(SQLException e){
        out.println(" Failed to query all user information ");
      }
    %>
  </body>
</html>

Get the record of the specified field in all records


<%@page language="java" contentType="text/html;charset=gb2312"%>
<%@page import="java.sql.*" %>
<!DOCTYPE html>
<html>
  <head>
    <title> Users who query records of all users id And user name </title>
  </head>
  <body>
    <%
      String url = "jdbc:mysql://localhost:3306/javaweb";// Connecting to the database url Address 
      String user = "root";// User name for logging in to the database 
      String password = "zhangda890126;;";// Password for the user name that logs in to the database 
      Connection conn = null;
      try{
        Class.forName("com.mysql.jdbc.Driver");// Loading JDBC Driver 
        conn = DriverManager.getConnection(url,user,password);// Linked database 
      }catch(ClassNotFoundException e){
        out.println(" Driver class not found ");// Prompt information when an exception is thrown 
      }catch(SQLException e){
        out.println(" Link MySQL Database failure ");// Deal with SQLException Anomaly 
      }
      try{
         
        Statement stmt = conn.createStatement();// Create a statement object Statement
        String queryAll = "SELECT userid,username FROM user";// Query all users 
        ResultSet rs = stmt.executeQuery(queryAll);
        while(rs.next()){
          int userid = rs.getInt(1);// Get the 1 Fields userid Value of 
          String username = rs.getString(2);// Get the 2 Fields username Value of 
           
           
          // Print out all user information 
          out.println(" Users ID : "+userid+"  User name: "+username+"<br />");
        }
      }catch(SQLException e){
        out.println(" Failed to query all user information ");
      }
    %>
  </body>
</html>

Get the record with the specified starting position and number of records


<%@page language="java" contentType="text/html;charset=gb2312"%>
<%@page import="java.sql.*" %>
<!DOCTYPE html>
<html>
  <head>
    <title> Get the first 2 Beginning with a record 3 A record </title>
  </head>
  <body>
    <%
      String url = "jdbc:mysql://localhost:3306/javaweb";// Connecting to the database url Address 
      String user = "root";// User name for logging in to the database 
      String password = "zhangda890126;;";// Password for the user name that logs in to the database 
      Connection conn = null;
      try{
        Class.forName("com.mysql.jdbc.Driver");// Loading JDBC Driver 
        conn = DriverManager.getConnection(url,user,password);// Linked database 
      }catch(ClassNotFoundException e){
        out.println(" Driver class not found ");// Prompt information when an exception is thrown 
      }catch(SQLException e){
        out.println(" Link MySQL Database failure ");// Deal with SQLException Anomaly 
      }
      try{
         
        Statement stmt = conn.createStatement();// Create a statement object Statement
        String queryAll = "SELECT * FROM user limit 1,3";// Query all users 
        ResultSet rs = stmt.executeQuery(queryAll);
        while(rs.next()){
          int userid = rs.getInt(1);// Get the 1 Fields userid Value of 
          String username = rs.getString(2);// Get the 2 Fields username Value of 
          String userpassword = rs.getString(2);// Get the 3 Of the fields password Value of 
           
          // Print out all user information 
          out.println(" Users ID : "+userid+"  User name: "+username+"  User password: "+userpassword+"<br />");
        }
      }catch(SQLException e){
        out.println(" Failed to query all user information ");
      }
    %>
  </body>
</html>

Traverse the data in ResultSet and transform it into a table
Looking for a long time on the Internet to traverse the data in ResultSet and fill it into a web page table in turn. It is said that ResultSet data will be converted into a 2-dimensional array and then output in turn. However, the 2-digit array needs to specify the storage size in advance, which is inconvenient to expand. In fact, the following methods can be used:


while(rs.next()){
    out.println("<tr><td>"+rs.getString(1)+"</td><td>" +rs.getString(2)+"</td><td>"+rs.getString(3)+"</td><td>"
        +rs.getString(4)+"</td><td>"+rs.getString(5)+"</td><td>"+rs.getString(6)+"</td><td>"
        +rs.getString(7)+"</td><td>"+rs.getString(8)+"</td><td>"+rs.getString(9)+"</td><td>"
        +rs.getString(10)+"</td><td>"+rs.getString(11)+"</td><td>"+rs.getString(12)+"</td><tr>");
}


Related articles: