1: Initial JDBC

JDBC, namely Java Database Connectivity. Is a Java API for executing SQL statements. It is a database connection specification in Java.

JDBC provides developers with a standard programming interface, so that developers can write complete database applications in pure java language.

JDBC is used to let our program operate the database through the network, which plays a very important role; JDBC technology is also one of the core technologies of java.

2: Related concepts

Driver: different databases or different database versions of the same manufacturer will provide different drivers. Any application operates the database through this driver.

Database driver package: different databases provide different database driver packages corresponding to different programming languages. For example, MySQL provides Java driver package MySQL connector Java, which is required to operate MySQL based on Java. Similarly, to operate Oracle database based on Java, Oracle's database driver package ojdbc is required.

 

To operate a database using JDBC:

1) Register driver (only once)

2) Establish Connection

3) Create a Statement that executes SQL

4) Execute SQL using action commands

5) Processing execution results

6) Release resources

3: Detailed introduction

The first step in JDBC usage: register (load) the driver

1. Obtain through the static method of DriverManager (driver management class):

// Load JDBC Driver
Class.forName("com.mysql.jdbc.Driver");
// Create database connection
Connection connection = DriverManager.getConnection(url);

2. Get through the DataSource object. The DataSource object will be used in practical application.  

DataSource ds = new MysqlDataSource();
((MysqlDataSource) ds).setUrl("jdbc:mysql://localhost:3306/test");
((MysqlDataSource) ds).setUser("root");
((MysqlDataSource) ds).setPassword("root");
Connection connection = ds.getConnection();

Using JDBC step 2: establish a connection

1. Establish Connection interface class object

// Create database connection
Connection connection =
DriverManager.getConnection("jdbc:mysql://localhost:3306/test?
user=root&password=root&useUnicode=true&characterEncoding=UTF-8");

2. URL format

//First create a database connection pool, and then obtain the database connection object through the connection pool
DataSource ds = new MysqlDataSource();
//Create database connection pool: during initialization, a certain number of database connections will be created. These connection objects can be reused and more efficient
//The entire url with parameters can only use the setURl method, or set the parameters by calling the method
   ((MysqlDataSource) ds).setURL("jdbc:mysql://localhost:3306/test04 "); / / set the path
   ((MysqlDataSource) ds).setUser("root");
   ((MysqlDataSource) ds).setPassword("111111");
   ((MysqlDataSource) ds).setUseUnicode(true);
   ((MysqlDataSource) ds).setCharacterEncoding("UTF-8");
   ((MysqlDataSource) ds).setUseSSL(false);//No red warning
   conn = ds.getConnection();

JDBC uses step 3: create an object that executes SQL statements

The execution object Statement is responsible for executing SQL statements and is generated by the Connection object. The syntax is as follows:

Statement statement = connection.createStatement();

JDBC uses step 4: execute SQL using operation commands

The execution object Statement provides two common methods to execute SQL statements

1.executeQuery(Stringsql): this method executes the sql statement that implements the query function, and the return type is resultset

ResultSet resultSet= statement.executeQuery(
                "select id, sn, name, qq_mail, classes_id from student");

2.executeUpdate(Stringsql): this method is used to execute sql statements that implement the functions of adding, deleting and modifying. The return type is int, that is, how many rows are affected

  Statement s = null; 
 int n = s.executeUpdate("update student set sn=200 where id =3");
   System.out.println("Successfully modified data:" + n);

JDBC uses step 5: processing execution results

The resultSet object is responsible for saving the query results generated after the Statement is executed

The ResultSet is operated by a cursor.
A cursor is a controllable pointer that can point to any record. With this pointer, we can easily indicate which record in the result set we want to modify, delete, or insert data before which record. A result set object contains only one cursor.

while (resultSet.next()) {
      int id = resultSet.getInt("id");
      String sn = resultSet.getString("sn");
      String name = resultSet.getString("name");
      int classesId = resultSet.getInt("classes_id");
      System.out.println(String.format("Student: id=%d, sn=%s, name=%s, 
classesId=%s", id, sn, name, classesId));
       }

Step 6 of JDBC usage: release resources

  if(r != null)//When an exception occurs, the object may not have been assigned (initialized), and a null pointer exception may occur when calling close
r.close();
  if(s != null)
s.close();
  if(conn != null)
 conn.close();