Detailed explanation of the statement object Statement in JSP and the use example of MySQL operation

2021-09-20 21:18:02
OfStack

The statement object Statement contains two main methods: the executeUpdate () method performs data update operations (add records, delete records, update records), and the executeQuery () method performs data query operations (query records)

Add a record


<%@page language="java" contentType="text/html;charset=gb2312"%>
<%@page import="java.sql.*" %>
<!DOCTYPE html>
<html>
  <head>
    <title> Add a user 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{
        // Create a statement object Statement
        Statement stmt = conn.createStatement();
         
        String adduser = "INSERT INTO user(userid,username,password) VALUES (null,'James','1234')";// Add User 
         
        stmt.executeUpdate(adduser);// Execute statement 
      }catch(SQLException e){
        out.println(" Failed to add user information ");
      }
    %>
  </body>
</html>

<html>
  <head>
    <title> Add multiple user records </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{
        // Create a statement object Statement
        Statement stmt = conn.createStatement();
        // Delete userid For 1 User information of 
        for(int i=2;i<6;i++){
          String username = "zhangda_"+i;
          String adduser = "INSERT INTO user (userid,username,password) VALUES (null,'"+username+"','1234')";// Add User 
          stmt.executeUpdate(adduser);// Execute statement 
        }
      }catch(SQLException e){
        out.println(" Failed to add user information ");
      }
    %>
  </body>
</html>

Update record


<%@page language="java" contentType="text/html;charset=gb2312"%>
<%@page import="java.sql.*" %>
<!DOCTYPE html>
<html>
  <head>
    <title> Add a user 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{
        // Create a statement object Statement
        Statement stmt = conn.createStatement();
        // Update userid For 1 User information, update its password to 12345
        String updateuser = "UPDATE user SET password='12345' WHERE userid=1;";// Add User 
         
        stmt.executeUpdate(updateuser);// Execute statement 
      }catch(SQLException e){
        out.println(" Failed to update user information ");
      }
    %>
  </body>
</html>

Delete a record


<%@page language="java" contentType="text/html;charset=gb2312"%>
<%@page import="java.sql.*" %>
<!DOCTYPE html>
<html>
  <head>
    <title> Add a user 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{
        // Create a statement object Statement
        Statement stmt = conn.createStatement();
        // Delete userid For 1 User information of 
        String deleteuser = "DELETE FROM user WHERE userid=1;";// Add User 
         
        stmt.executeUpdate(deleteuser);// Execute statement 
      }catch(SQLException e){
        out.println(" Failed to delete user information ");
      }
    %>
  </body>
</html>