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>");
}