1. What is JDBC?
Java database connectivity
2. What is the essence of JDBC?
JDBC is a set of interfaces set at the top of sun company
java.sql.*; (there are many interfaces under this software package)
Interfaces have callers and implementers
Interface oriented calling and interface oriented writing implementation classes belong to interface oriented programming
Thinking: Why did sun set up a set of JDBC interfaces?
Because the underlying implementation principle of each database is different
Oracle database has its own principle
Mysql database also has its own principle
SqlServer databases also have their own principles
...
Each database product has its own unique implementation principle
The schematic diagram is as follows:
Thinking: why interface oriented programming?
Decoupling: reduce the coupling degree of the program and improve the expansion force of the program
Polymorphic mechanism is very typical: Abstract oriented programming (not concrete oriented programming)
//Recommendations: Animal a = new Cat(); Animal a = new Dog(); //Not recommended: Dog d = new Dog(); Cat c = new Cat();
3. Preparation before development
To prepare for JDBC development, first download the corresponding driver jar package from the official website, and then configure it into the environment variable classpath (if you use the IDEA tool, you do not need to configure the environment variable, and the configuration environment variable is only for text editor Development).
The address of the jar package downloaded from the official website
Configure the environment variable classpath
If we use the IDEA tool, the operation is very simple
If you are creating a MAVEN project, we can directly add dependencies in the pom.xml file
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.26</version>
</dependency>
</dependencies>4. Six steps of JDBC programming (memorizing)
Step 1: register the driver (tell the Java program which brand of database is to be connected)
Step 2: get the connection (it means that the channel between the JVM process and the database process is open, which belongs to the communication between processes. For heavyweight, the channel must be closed after use)
Step 3: get the database operation object (the object specially executing sql statements)
Step 4: execute sql statement (DQL DML...)
Step 5: process the query result set (only when the select statement is executed in step 4, the query result set will be processed in step 5)
Step 6: release the resources (after using the resources, be sure to close the resources. Java and database belong to inter process communication, and be sure to close them after opening)
5. Concrete realization
1. Register driver
We first check the jdk help document and find that there are two methods to register drivers
We check the specific explanation of the method and find that an SQLException will be thrown ， We use try..catch to catch exceptions
Write code:
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
public class JDBCTest01 {
public static void main(String[] args) throws SQLException {
try {
//1. Register driver
Driver driver = new com.mysql.cj.jdbc.Driver();//The parent type reference points to a child type object
DriverManager.registerDriver(driver);
} catch (SQLException e) {
e.printStackTrace();
}
//2. Get connection
//3. Get database operation object
//4. Execute sql
//5. Processing query results
//6. Close the connection
}
}
2. Get connection
Let's look at the jdk help documentation, where there are three ways to get a connection
We often use the third one. Check the detailed explanation of the third one
You can see that a Connection object is returned, which is the obtained Connection object
Write program:
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
public class JDBCTest01 {
public static void main(String[] args) throws SQLException {
try {
//1. Register driver
Driver driver = new com.mysql.cj.jdbc.Driver();//The parent type reference points to a child type object
DriverManager.registerDriver(driver);
//2. Get connection
/*
url:Uniform resource locator (absolute path of a resource in the network)
URL Which parts are included?
agreement
IP
PORT
Resource name
http://220.181.38.251:80/index.html
http:// communication protocol
220.181.38.251 Server IP address
80 Port for the software on the server
index.html A resource name on the server
jdbc:mysql://127.0.0.1:3306/demodatabase2
jdbc:mysql:// agreement
127.0.0.1 IP address
3306 mysql Port number of the database
demodatabase2 Specific database instance name
Note: localhost and 127.0.0.1 are local IP addresses
What is a communication protocol and what is its use?
Communication protocol is the data transmission format set in advance before communication
The format of the data packet is set in advance
*/
String url = "jdbc:mysql://127.0.0.1:3306/demodatabase2";
String user = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println("Database connection object:" + connection);
} catch (SQLException e) {
e.printStackTrace();
}
//3. Get database operation object
//4. Execute sql
//5. Processing query results
//6. Close the connection
}
}
Run the following console output:
3. Get database operation object
View jdk help documentation
It is found that there are three methods. The first method is commonly used. Let's see the specific explanation
You can find that this method returns a Statement object, which is used to send sql statements to the database
Write code:
import java.sql.*;
public class JDBCTest01 {
public static void main(String[] args) throws SQLException {
try {
//1. Register driver
Driver driver = new com.mysql.cj.jdbc.Driver();//The parent type reference points to a child type object
DriverManager.registerDriver(driver);
//2. Get connection
/*
url:Uniform resource locator (absolute path of a resource in the network)
URL Which parts are included?
agreement
IP
PORT
Resource name
http://220.181.38.251:80/index.html
http:// communication protocol
220.181.38.251 Server IP address
80 Port for the software on the server
index.html A resource name on the server
jdbc:mysql://127.0.0.1:3306/demodatabase2
jdbc:mysql:// agreement
127.0.0.1 IP address
3306 mysql Port number of the database
demodatabase2 Specific database instance name
Note: localhost and 127.0.0.1 are local IP addresses
What is a communication protocol and what is its use?
Communication protocol is the data transmission format set in advance before communication
The format of the data packet is set in advance
*/
String url = "jdbc:mysql://127.0.0.1:3306/demodatabase2";
String user = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println("Database connection object:" + connection);
//3. Get database operation object
Statement stmt = connection.createStatement();
} catch (SQLException e) {
e.printStackTrace();
}
//4. Execute sql
//5. Processing query results
//6. Close the connection
}
}
4. Execute sql to close the connection
Let's look at the jdk help documentation
First, we perform an INSERT operation. We can see the method marked in red. Click in to see the specific explanation
The returned is an int type, indicating that the number of records in the database is affected
Here, we use insert to insert the student table in the database
The student table in the database is designed like this
Write code:
import java.sql.*;
public class JDBCTest01 {
public static void main(String[] args) throws SQLException {
Statement stmt = null;
Connection connection = null;
try {
//1. Register driver
Driver driver = new com.mysql.cj.jdbc.Driver();//The parent type reference points to a child type object
DriverManager.registerDriver(driver);
//2. Get connection
/*
url:Uniform resource locator (absolute path of a resource in the network)
URL Which parts are included?
agreement
IP
PORT
Resource name
http://220.181.38.251:80/index.html
http:// communication protocol
220.181.38.251 Server IP address
80 Port for the software on the server
index.html A resource name on the server
jdbc:mysql://127.0.0.1:3306/demodatabase2
jdbc:mysql:// agreement
127.0.0.1 IP address
3306 mysql Port number of the database
demodatabase2 Specific database instance name
Note: localhost and 127.0.0.1 are local IP addresses
What is a communication protocol and what is its use?
Communication protocol is the data transmission format set in advance before communication
The format of the data packet is set in advance
*/
String url = "jdbc:mysql://127.0.0.1:3306/demodatabase2";
String user = "root";
String password = "root";
connection = DriverManager.getConnection(url, user, password);
System.out.println("Database connection object:" + connection);
//3. Get database operation object
stmt = connection.createStatement();
//4. Execute sql
String sql = "INSERT INTO student(id,age,name) VALUES(16,65,\"Wei sang\")";
//Specify the of the DML statement (insert delete update)
//The return value is "affect the number of records in the database"
int count = stmt.executeUpdate(sql);
System.out.println(count == 1 ? "Saved successfully" : "Save failed");
//5. Process the query result set (only the select statement can be used)
} catch (SQLException e) {
e.printStackTrace();
} finally {
//6. Close the connection
//To ensure that the resources are released, close the resources in the finally statement block
//And it should be closed from small to large
//try...catch
if(stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
Console output:
We checked the student table and found that the inserted data has been inserted.
6. JDBC performs deletion and update
1. Delete
Following the steps in step 5, let's delete the data with id No. 16 added in the previous step in the student table
Write code
import com.mysql.cj.jdbc.Driver;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCTest02 {
public static void main(String[] args) {
Statement stmt = null;
Connection conn = null;
try {
//1. Register driver
Driver driver = new Driver();
DriverManager.registerDriver(driver);
//2. Get connection
String url = "jdbc:mysql://localhost:3306/demodatabase2";
String user = "root";
String password = "root";
conn = DriverManager.getConnection(url,user,password);
//3. Get database operation object
stmt = conn.createStatement();
//4. Execute sql statement
String sql = "DELETE FROM student WHERE id = 16";
int count = stmt.executeUpdate(sql);
System.out.println(count == 1 ? "Delete succeeded" : "Deletion failed");
} catch (SQLException e) {
e.printStackTrace();
} finally {
//6. Release resources
if(stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
Console output:
Check the student table. The data with id 16 has been deleted.
2. Update operation
We directly change the code based on the previous step, and change the name with id 15 to Wei sang
Write code:
import com.mysql.cj.jdbc.Driver;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCTest03 {
public static void main(String[] args) {
Statement stmt = null;
Connection conn = null;
try {
//1. Register driver
Driver driver = new Driver();
DriverManager.registerDriver(driver);
//2. Get connection
String url = "jdbc:mysql://localhost:3306/demodatabase2";
String user = "root";
String password = "root";
conn = DriverManager.getConnection(url,user,password);
//3. Get database operation object
stmt = conn.createStatement();
//4. Execute sql statement
String sql = "UPDATE student SET name = \"Wei sang\" WHERE id = 15";
int count = stmt.executeUpdate(sql);
System.out.println(count == 1 ? "Update succeeded" : "Update failed");
} catch (SQLException e) {
e.printStackTrace();
} finally {
//6. Release resources
if(stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
Console output:
We can see from the student table that the data with id 15 has been updated successfully
7. Register the driver by loading the class
Let's look at the Driver interface implemented by mysql
DriverManager.registerDriver(new Driver()) is in a static code block
If we want to load the static code block of a class, we only need to load the class. Obviously, we can implement it through reflection.
Write code:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCTest04 {
public static void main(String[] args) {
try {
//1. Register driver
//This is the first way to write a registration driver
//DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
//This is the second way, and the most conventional way, through the class loading mechanism
//Why is this method most commonly used? Because the parameter is a string, the string can be written to the xxx.properties file
//The following method does not need to receive the return value, because we only want to load the action with its class
Class.forName("com.mysql.cj.jdbc.Driver");
//2. Get connection
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/demodatabase2", "root", "root");
System.out.println(conn);
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
}
8. Read the connection database information from the attribute resource file
We create a jdbc.properties file and fill it with driver, URL, user and password
Rewrite the code
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JDBCTest05 {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
Properties properties = new Properties();
FileReader fr = null;
try {
fr = new FileReader("java-15-JDBC\\src\\main\\java\\jdbc.properties");
} catch (FileNotFoundException e) {
e.printStackTrace();
}
try {
properties.load(fr);
} catch (IOException e) {
e.printStackTrace();
}
System.out.println(properties.getProperty("driver"));
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
String user = properties.getProperty("user");
String password = properties.getProperty("password");
try {
//1. Register driver
Class.forName(driver);
//2. Get connection
conn = DriverManager.getConnection(url,user,password);
//3. Get operation object
stmt = conn.createStatement();
//4. Execute sql
String sql = "INSERT INTO student(id,age,name) VALUES(16,45,\"Zhou Xingchi\")";
int count = stmt.executeUpdate(sql);
System.out.println(count == 1 ? "Insert successful" : "Insert failed");
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
if(stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
Or we can use resource binders to optimize the code
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.ResourceBundle;
public class JDBCTest06 {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
String driver = bundle.getString("driver");
String url = bundle.getString("url");
String user = bundle.getString("user");
String password = bundle.getString("password");
try {
//1. Register driver
Class.forName(driver);
//2. Get connection
conn = DriverManager.getConnection(url,user,password);
//3. Get operation object
stmt = conn.createStatement();
//4. Execute sql
String sql = "INSERT INTO student(id,age,name) VALUES(17,45,\"Zhou Xingchi\")";
int count = stmt.executeUpdate(sql);
System.out.println(count == 1 ? "Insert successful" : "Insert failed");
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
if(stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
9. Process query result set
We check the jdk help document and return a ResultSet result set object
Let's see the detailed explanation
Let's look at the ResultSet class, which has a next method
Let's improve the code:
package com.lu.jdbc;
import java.sql.*;
import java.util.ResourceBundle;
public class JDBCTest07 {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
String driver = bundle.getString("driver");
String user = bundle.getString("user");
String url = bundle.getString("url");
String password = bundle.getString("password");
try {
//1. Register driver
Class.forName(driver);
//2. Get connection
conn = DriverManager.getConnection(url,user,password);
//3. Get database operation object
stmt = conn.createStatement();
//4. Execute sql statement
String sql = "SELECT * FROM student";
rs = stmt.executeQuery(sql);//Methods dedicated to DQL
//5. Processing query result set
while (rs.next()) {
String id = rs.getString("id");//All subscripts in JDBC start from 1, not 0
String age = rs.getString("age");
String name = rs.getString("name");
System.out.print("id: " + id);
System.out.print(" age: " + age);
System.out.println(" name: " + name);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
Console output:
10. Use of Preparedstatement
To solve the sql injection problem, it belongs to the precompiled database operation object
The principle of PreparedStatement is to compile the framework of SQL statement in advance, and then pass "value" to SQL statement
Let's write the code
package com.lu.jdbc;
import java.sql.*;
import java.util.ResourceBundle;
public class JDBCTest08 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
String driver = bundle.getString("driver");
String user = bundle.getString("user");
String url = bundle.getString("url");
String password = bundle.getString("password");
try {
//1. Register driver
Class.forName(driver);
//2. Get connection
conn = DriverManager.getConnection(url,user,password);
//3. Get database operation object
String sql = "SELECT * FROM student WHERE id = ?";
ps = conn.prepareStatement(sql);
//Give placeholders? Value transfer (the first question mark subscript is 1, the second question mark subscript is 2, and all subscripts in JDBC start with 1)
ps.setInt(1,15);
//4. Execute sql statement
rs = ps.executeQuery();
//5. Processing query result set
while (rs.next()) {
String id = rs.getString("id");//All subscripts in JDBC start from 1, not 0
String age = rs.getString("age");
String name = rs.getString("name");
System.out.print("id: " + id);
System.out.print(" age: " + age);
System.out.println(" name: " + name);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
Difference between Statement and PreparedStatement
1.PreparedStatement is precompiled, which can greatly improve the efficiency for batch processing. It is also called JDBC stored procedure
2. Use the Statement object. When only one-time access is performed to the database, the Statement object is used for processing. PreparedStatement objects are more expensive than statements and do not bring additional benefits for one-time operations.
3.statement each time an sql statement is executed, the relevant database must compile the sql statement. The preparedstatement is pre compiled, preparedstatement supports batch processing
Conclusion:
PreparedStatement is widely used, and only a few cases require Statement