1, JDBC overview

1. What is JDBC?

JDBC (JavaDataBase Connectivity) is a java database connection. In other words, it uses the Java language to operate the database. It turns out that we operate the database by using SQL statements on the console, and JDBC sends SQL statements to the database in Java language.

2. JDBC principle

In the early days, the geniuses of SUN wanted to write a set of API that can connect all databases in the world, but when they just started, they found that this was an impossible task because the database servers of various manufacturers were too different. Later, SUN began to discuss with database manufacturers. The final conclusion is that SUN provides a set of specifications for accessing the database (i.e. a set of interfaces) and provides protocol standards for connecting to the database. Then, each database manufacturer will follow SUN's specifications and provide a set of APIs for accessing its own company's database server. The specification provided by SUN is named JDBC, and the API provided by various manufacturers that follow the JDBC specification and can access their own database is called driver! JDBC is the interface, and the jdbc driver is the implementation of the interface. Without the driver, the database connection cannot be completed! Each database manufacturer has its own driver to connect to its own company's database.

3. JDBC essence

JDBC is essentially a set of interfaces

4. Introduction to main interfaces, classes and methods in JDBC

Driver Interface
    - Driver The interface is provided by the database manufacturer Java For developers, just use Driver Interface is OK.
　　- To connect to the database in programming, you must first load the database driver of a specific manufacturer. Different databases have different loading methods.
　　- Driver: it is implemented by various manufacturers Sun Proposed by the company JDBC Interface. That is right Connection Implementation class of such interface jar Documents, jar The package contains many class File.
　　- load mysql Drive: Class.forName("com.mysql.jdbc.Driver");
　　- load oracle Drive: Class.forName("com.jdbc.driver.OracleDriver");

DriverManager Interface
    - DriverManager Interface is JDBC Management layer, which acts between users and drivers. 
    - DriverManager Tracks available drivers and establishes a connection between the database and the corresponding driver.

Connection Interface
    - Connection Connection to a specific database(conversation)，Execute in connection context SQL Statement and returns the result.
    - DriverManager of getConnection()The method is based on JDBC URL Database defined in Connection Connect
    -Mysql
          Connection con = DriverManager.getConnection("jdbc:mysql://host:port/database", "user", "password");
    -Oracle
          Connection con = DriverManager.getConnection("jdbc:oracle:thin@host:port/database", "user", "password");

Statement Interface
    - Used to perform static SQL Statement and returns the result it generates.
    - Three kinds Statement Class:
      - Statement: 
         from createStatement Create for sending simple SQL Statement (without parameters)
      
      - PreparedStatement: 
      - Inherited from Statement Interface, yes preparedStatement Created to send a message containing one or more input parameters SQL sentence. PreparedStatement Object ratio Statement Objects are more efficient and prevent SQL Injection. We usually use PreparedStatement. 

　　　 - CallableStatement: 
　　　 - Inherited from preparedStatement. By method prePareCall Created to call a stored procedure.

　　- frequently-used Statement method 
　　   - execute(): Run the statement to return whether there is a result set.
      - executeQuery(): function select Statement, return ResultSet Result section
      - executeUpdate(): function insert/update/delete Operation to return the number of updated rows.
    

5. JDBC access database process

1. Register driver
class.forName("com.mysql.cj.jdbc.Driver");//Start the class loading mechanism, because the Driver internally registers the Driver code and puts it in the static code block, which is executed during class loading

2. Get database connection
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306 / database name? Servertimezone = GMT "," root "," password ");

3. Get database operation object
Statement stmt = conn.createStatment();

4. implement sql sentence
int count = stmt.executeUpdate("sql sentence"); //The executeUpdate() method returns the number of statements executed

5. Process query result set
 If DQL Statement, execute sql Statement executeQuery()method
 Return value ResultSet rs To receive
 Output call rs of getString method

6. close resource
conn.close();
stmt.close();

Note: exception handling (SQLException) should be performed on conn and stmt objects

2, Problems when connecting to the database for the first time

1. Configure the environment variable classpath

1. Download from the official website jar package
2. classpath=.;jar Package path

2. java.sql.SQLException: null, message from server: "Host 'xxx' is not allowed to connect" error occurs

Reason: Mysql database does not support remote links by default, that is, the remote server does not allow Java programs to access its database, so you can set the remote, that is, modify the permissions.

resolvent:
1. Login with command line mysql
2. input use mysql
3. mysql There is one in this database user Table, input select host from user;
4. input update user set host = "%" where host = "user";
5. input flush privileges Or restart mysql auxiliary 

The results of the modified user table are as follows

+-----------+
| host      |
+-----------+
| %         |
| localhost |
| localhost |
| localhost |
+-----------+

3. Java.sql.sqlexception appears: "unable to load authentication plugin 'caching"_ sha2_ Password 'error

Reason: the JDBC version does not match the Mysql version. The password encryption method of Mysql5 and Mysql8 has changed. The encryption method of Mysql5 is mysql_native_password, while the encryption method of Mysql8 is caching_sha2_password

Solution: install the new Jar package on the official website and overwrite the original Jar package

4. The Loading 'class com.mysql.jdbc.Driver'. This is deprecated. The new driver class is' com.mysql.cj.jdbc.Driver '. Error appears

Reason: the driver class com.mysql.jdbc.Driver is outdated. The new version of JDBC has a new driver class com.mysql.cj.jdbc.Driver.

resolvent:
    take com.mysql.jdbc.Driver Change to com.mysql.cj.jdbc.Driver

5. Com.mysql.cj.exceptions. Invalid connection attribute exception: the server time zone... Error appears

Reason: the new version of JDBC requires the time zone to be added after the database url

resolvent:
    url Add time zone after"jdbc:mysql://localhost:3306/mydatabase?serverTimezone=GMT"

6. Attention

Be sure to pay attention to the format of the url and don't write it wrong!!!