Java language to implement the data in the MySql database to add delete change and check the operation code

Simply describe the steps:

1. Connect to the database

2. Send SQL statements to the database

3. Execute the SQL statement

Here's an example:

In one database, there is an students table with student number (Id), name (Name), gender (Sex), address (Address), phone number (Phone), and major (Dept).

Write this table as 1 student information class (Info_student)

(please make sure you read the examples first, otherwise you may not be able to understand parts of the code.)

To achieve the manipulation we first have to connect to the database, because each operation has to be connected to the operation, so we directly put the operation of the connection in a class, when the need to connect directly call.

Database connection class:

import java.sql.Connection; 
import java.sql.DriverManager; 
 
public class DB_Helper { 
 
  public static Connection connect = null; 
 
  static { 
    try { 
      Class.forName("com.mysql.jdbc.Driver"); //  loading MYSQL JDBC The driver  
      //  Observe the following 2 The difference between two statements,  
      // connect = 
      // DriverManager.getConnection("jdbc:mysql://localhost:3306/students", "root", ""); 
      connect = DriverManager.getConnection( 
          "jdbc:mysql://localhost:3306/students?useUnicode=true&characterEncoding=utf-8", "root", ""); 
 
      System.out.println("Success loading Mysql Driver!"); 
    } catch (Exception e) { 
      System.out.print("Error loading Mysql Driver!"); 
      e.printStackTrace(); 
    } 
  } 
 
  public static Connection getConnection() { 
    return connect; 
  } 
} 

The database is already connected, so the next step is to send the SQL statement and execute the statement.

The send statement USES PreparedStatement object and Connection object operations prepareStatement()

Operation on PreparedStatement object execute()

Tip: the following is a description of some objects, you can first look at the code, come back to see when encountered.

************************

PreparedStatement

Object representing a precompiled SQL statement.

The SQL statement is precompiled and stored in the PreparedStatement object. You can then use this object multiple times to execute the statement efficiently.

*************************

Connection

A connection (session) to a specific database. Execute the SQL statement in the connection context and return the result.

The database of the Connection object can provide information describing its tables, the supported SQL syntax, stored procedures, this connection functionality, and so on.

**********************

The following code is to realize the operation of adding, deleting, changing and checking student information in the database.

Increased 1.

public void add(Info_student student) throws SQLException{ 
  //  A connection (session) to a specific database.  
  Connection conn = (Connection) DB_Helper.getConnection(); 
   
  String sql = "insert into student(Sno,Sname,Ssex,Saddress,Sphone,Sdept) values(?,?,?,?,?,?)"; 
   
  //  create 1 a  PreparedStatement  Object to parameterize  SQL  Statements are sent to the database.  
  PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql); 
  /* 
   * void setBigDecimal(int parameterIndex,BigDecimal x)throws SQLException 
   *  Sets the specified parameter to given  Java String  Value. When this value is sent to the database, the driver converts it to 1 a  SQL VARCHAR 
   *  or  LONGVARCHAR  Value (depending on the parameter relative to the driver in  VARCHAR  The size of the limit on the value.  
   */ 
  ptmt.setString(1, student.getId()); 
  ptmt.setString(2, student.getName()); 
  ptmt.setString(3, student.getSex()); 
  ptmt.setString(4, student.getAddress()); 
  ptmt.setString(5, student.getPhone()); 
  ptmt.setString(6, student.getDept()); 
   
  //  In this  PreparedStatement  Execution in object  SQL  statements  
  ptmt.execute(); 
} 

2. Delete

public void delete(String id) throws SQLException{ 
  Connection conn = (Connection) DB_Helper.getConnection(); 
  String sql = "delete from student where Sno=?"; 
  PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql); 
   
  ptmt.setString(1, id); 
   
  ptmt.execute(); 
} 

3. Change

public void update(Info_student student) throws SQLException{ 
  Connection conn = (Connection) DB_Helper.getConnection(); 
  String sql = "update student set Sname=?,Ssex=?,Saddress=?,Sphone=?,Sdept=? where Sno=?"; 
 
  PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql); 
  ptmt.setString(1, student.getName()); 
  ptmt.setString(2, student.getSex()); 
  ptmt.setString(3, student.getAddress()); 
  ptmt.setString(4, student.getPhone()); 
  ptmt.setString(5, student.getDept()); 
  ptmt.setString(6, student.getId()); 
 
  ptmt.execute(); 
} 

4. Check

public Info_student search(String id) throws SQLException{ 
  Info_student student = null; 
   
  Connection conn = (Connection) DB_Helper.getConnection(); 
  String sql = "select * from student where Sno=?"; 
  PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql); 
   
  ptmt.setString(1, id); 
   
  /* 
   * ResultSet executeQuery()throws SQLException 
   *  In this  PreparedStatement  Execution in object  SQL  Query and return the query generated  ResultSet  Object.  
   */ 
   
  /* 
   * public interface ResultSet extends Wrapper 
   *  A data table representing a database result set, usually generated by executing a query against the database.  ResultSet  Object has a cursor pointing to its current data row.  
   *  Initially, the cursor is placed in the first position 1 Before the line. next  Method moves the cursor down 1 Line; Because the method is in  ResultSet  Object not down 1 row  
   *  return  false So it can be  while  It is used in a loop to iterate over the result set.  
   * 
   */ 
  ResultSet rs = ptmt.executeQuery(); 
 
  /* 
   * boolean next()throws SQLException 
   *  Move the cursor forward from its current position 1 Line.  
   * ResultSet  The cursor is initially at no 1 Before;  
   *  The first 1 Time to call  next  Methods to make the first 1 The row becomes the current row;  
   *  The first 2 This call makes the first 2 The row becomes the current row, and so on.  
   */ 
 
  while(rs.next()){ 
    student = new Info_student(); 
    student.setId(rs.getString("Sno")); 
    student.setName(rs.getString("Sname")); 
    student.setSex(rs.getString("Ssex")); 
    student.setAddress(rs.getString("Saddress")); 
   
    student.setPhone(rs.getString("Sphone")); 
    student.setDept(rs.getString("Sdept")); 
  } 
  return student; 
   
}