Hello, everyone. Today I'd like to share with you the steps of JDBC connecting to the database.

Steps to connect JDBC to database:

1. Load database driver

2. Get connection to database

3. Gets the execution object used to execute sql commands

4. Execute

5. Processing results

6. Recycling resources

1. Loading database driver: the higher version is compatible with the lower version (that is, the database driver package is version 5.0, and the 8.0 loading driver can be used)

Note: the driver can be loaded after JDBC 4 without displaying the declaration (it can not be written), because there is a configuration file java.sql.Driver in the services package under META-INF. the DriverManager will automatically look for it before obtaining the connection.

//The database driver package is version 8.0
Class.forName("com.mysql.cj.jdbc.Driver");
//The database driver package is version 5.0
Class.forName("com.mysql.jdbc.Driver");

2. Get the connection to the database: parameters in getConnection (): url,user,password. Of course, this method also has other overloaded methods, and the parameters are similar to this one.

Connection connection = DriverManager
    .getConnection(
            "jdbc:mysql://127.0.0.1:3306/mydb?serverTimezone=UTC",
            "root",
            "123456");

3. Get the execution object used to execute sql commands: Statement is generally used to execute static sql statements. When dynamically passing in parameters, the use of Statement will have the risk of sql injection, while PreparedStatement has the function of preprocessing sql statements to avoid the risk of sql injection.

//It is not recommended to use when there are dynamically passed in parameters
Statement stat = conn.createStatement();
//It has the function of preprocessing sql statements
String sql="select * from user where id=?";
PreparedStatement ps = conn.prepareStatement(sql);

4. Execute: there is an execute() method in the Statement, and the return value is boolean type. true means to execute query operation, and false means to execute update operation (addition, deletion and modification)

The executeUpdate () method performs an update operation (add, delete, modify), and the return value is the number of affected rows

The executeQuery () method performs a query operation, and the return value is a result set ResultSet

The PreparedStatement is the same as the Statement method (Note: PreparedStatement inherits the Statement), except for the "? In the sql Statement before execution Assignment.

//For Statement
boolean b = stat.execute("insert into user(username,password,phone) " +
        "values('coco','123456','12345678910')");
int i = stat.executeUpdate("insert into user(username,password,phone) " +
        "values('coco','123456','12345678910')");
ResultSet rs = stat.executeQuery("select * from user");
//For PreparedStatement
String sql="select * from user where id=?";
String sql1="delete from user where id=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1,1);
boolean b1 = ps.execute();
//Used when executing sql1
int i1 = ps.executeUpdate();
//Used when executing sql
ResultSet rs1 = ps.executeQuery();

5. Processing result: it mainly deals with the result set. There is a next() method in the ResultSet, which is equivalent to a cursor. It points to the next data, and the return value is of boolean type, which can judge whether there is a piece of data. getXxx in the ResultSet is used to obtain the specific contents of each piece of data, such as username, password, etc.

//Process the result of execute()
if(!b){
    System.out.println("Added successfully");
}
//Process the result of executeUpdate()
if(i==1){
    System.out.println("Added successfully");
}
//Result of processing executeQuery(): only one piece of data
if(rs1.next()){
    int id = rs.getInt("id");
    String username = rs.getString("username");
    String password = rs.getString("password");
    String phone = rs.getString("phone");
    System.out.println(id+"/"+username+"/"+password+"/"+phone);
}
//Result of processing executeQuery(): multiple pieces of data
while(rs1.next()){
    int id = rs.getInt("id");
    String username = rs.getString("username");
    String password = rs.getString("password");
    String phone = rs.getString("phone");
    System.out.println(id+"/"+username+"/"+password+"/"+phone);
}

6. Recycle resources: if there is a result set, you can close the result set. If there is no result set, you don't need to write this sentence. Closing resources is similar to recursion and stack, that is, the opening order is opposite to the closing order. It is best to use try finally... The statement uses try catch.... finally... sentence.

//Close ResultSet
rs.close();
//Close Statement
stat.close();
//Close Connection
conn.close();