Directory
Article Directory
foreword
1. What is JDBC
Two, JDBC usage steps
?edit
Graphic:
first step
Graphic:
second step
third step
Step Four, Step Five, Step Six
At this point, the database insertion operation is completed (*^▽^*)
3. Detailed explanation of the functions of each part of JDBC?
Foreword
Welcome to my blog, it’s a pleasure to meet you here! I hope you can feel a relaxed and pleasant atmosphere here, where you can not only get interesting content and knowledge, but also speak freely and share your thoughts and insights.
?
I look forward to exploring, learning and growing together with you in this small online world.
Reminder: The following is the text of this article, the following case is for reference
1. What is JDBC
- JDBC (Java DataBase Connectivity: java database connection) is a Java API for executing SQL statements, which can provide unified access to various relational databases. It is composed of a set of classes and interfaces written in Java language.
- The essence is a set of interface (interface), interface-oriented programming: decoupling, reducing program coupling, improving program scalability
JDBC technology related interface
Function: JDBC needs to operate the database through Java code. JDBC defines various interfaces and types for operating the database:
interface function
Driver driver interface, which defines the way to establish a link
DriverManager tool class, used to manage drivers, can get the link of the database
Connection represents the connection object (interface) established between Java and the database
PreparedStatement is a tool for sending SQL statements
ResultSet result set, used to obtain the result of the query statement
2. JDBC usage steps
The code is as follows (example):
package com.txw.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
@SuppressWarnings("all") // annotation warning information
public class JdbcTest01 {
public static void main(String[] args) throws Exception {
// 1. Load the driver
Class.forName("com.mysql.cj.jdbc.Driver");
// 2 Create a connection with the database
String username = "root";
String password = "123456";
String url = "jdbc:mysql://192.168.64.128:3306/test?useUnicode=true & amp;characterEncoding=UTF-8 & amp;useSSL=false & amp;serverTimezone=Asia/Shanghai";
Connection conn = DriverManager. getConnection(url, username, password);
// 3. Prepare to send SQL
String sql = "select * from t_person";
PreparedStatement pstm = conn. prepareStatement(sql);
// 4. Execute SQL and receive the result set
ResultSet rs = pstm. executeQuery();
// 5 process the result set
while(rs. next()){
/*
rs.getXxx (column order starts from 1) or rs.getXxx("column name") Get the data of the specified column, Xxx is the data type
Column names are often used in actual combat, which is more readable
*/
/*
int personId2 = rs. getInt(1);
String personName2 = rs. getString(2);
int age2 = rs. getInt(3);
String sex2 = rs. getString(4);
String mobile2 = rs. getString(5);
String address2 = rs. getString(6);
System.out.println("personId=" + personId2 + ",personName=" + personName2
+ ",age=" + age2 + ",sex=" + sex2 + ",mobile=" + mobile2 + ",address=" + address2);
*/
int personId1 = rs.getInt("person_id");
String personName1 = rs. getString("person_name");
int age1 = rs.getInt("age");
String sex1 = rs. getString("sex");
String mobile1 = rs. getString("mobile");
String address1 = rs. getString("address");
System.out.println("personId=" + personId1 + ",personName=" + personName1
+ ",age=" + age1 + ",sex=" + sex1 + ",mobile=" + mobile1 + ",address=" + address1);
}
// 6. Release resources
rs. close();
pstm. close();
conn. close();
}
}
Icon:
First step
register driver
package com.jdbc;
import java.sql.*;
@SuppressWarnings("all") // annotation warning information
public class jdbctest {
public static void main(String[] args) throws Exception {
try {
//1. Register driver
Driver driver = new com.mysql.cj.jdbc.Driver();
DriverManager.registerDriver(driver);
}catch (SQLException e){
e.printStackTrace();
}
//2. Get the connection
//3. Get the database operation object
}
}
Icon:
Step 2
get connection
package com.jdbc;
import java.sql.*;
@SuppressWarnings("all") // annotation warning information
public class jdbctest {
public static void main(String[] args) throws Exception {
try {
//1. Register driver
Driver driver = new com.mysql.cj.jdbc.Driver();
DriverManager.registerDriver(driver);
//2. Get the connection
String url="jdbc:mysql:/itcast";
/*
A JDBC URL consists of three parts:
Protocol: The protocol of the JDBC driver.
For MySQL, the protocol is jdbc:mysql://;
For Oracle, the protocol is jdbc:oracle:thin://;
For SQL Server, the protocol is jdbc:sqlserver://.
Host and Port: The address and port number of the database server.
For local databases, the host and port can be omitted;
For remote databases, you need to specify the host and port, for example: jdbc:mysql://192.168.0.1:3306/test.
Database Name: The name of the database to connect to.
For MySQL, the database name can be specified directly after the host and port, for example: jdbc:mysql://localhost:3306/test;
For Oracle, it needs to be specified with "/" after the protocol and host port, for example: jdbc:oracle:thin:@localhost:1521:test;
For SQL Server, it needs to be specified with ";" after the host port, for example: jdbc:sqlserver://localhost:1433;databaseName=test.
*/
String user="root";
String password="123456";
Connection conn=DriverManager.getConnection(url,user,password);
System.out.println("database connection object = " + conn);
//Database connection object = com.mysql.cj.jdbc.ConnectionImpl@6646153
}catch (SQLException e){
e.printStackTrace();
}
//3. Get the database operation object
}
}
Illustration:
The third step
Get database operation object
//3. Get the database operation object (statement executes SQL statement specially)
stmt = conn.createStatement();
Step 4, Step 5, Step 6
Execute SQL statements
Process query result set
Release resources
package com.jdbc;
import java.sql.*;
@SuppressWarnings("all") // annotation warning information
public class jdbctest {
public static void main(String[] args) throws Exception {
Statement stmt = null;
Connection conn = null;
try {
//1. Register driver
Driver driver = new com.mysql.cj.jdbc.Driver();
DriverManager.registerDriver(driver);
//2. Get the connection
String url = "jdbc:mysql:/itcast";
/*
A JDBC URL consists of three parts:
Protocol: The protocol of the JDBC driver.
For MySQL, the protocol is jdbc:mysql://;
For Oracle, the protocol is jdbc:oracle:thin://;
For SQL Server, the protocol is jdbc:sqlserver://.
Host and Port: The address and port number of the database server.
For local databases, the host and port can be omitted;
Both localhost and 127.0.0.1 are local ip
For remote databases, you need to specify the host and port, for example: jdbc:mysql://192.168.0.1:3306/test.
Database Name: The name of the database to connect to.
For MySQL, the database name can be specified directly after the host and port, for example: jdbc:mysql://localhost:3306/test;
For Oracle, it needs to be specified with "/" after the protocol and host port, for example: jdbc:oracle:thin:@localhost:1521:test;
For SQL Server, it needs to be specified with ";" after the host port, for example: jdbc:sqlserver://localhost:1433;databaseName=test.
*/
String user = "root";
String password = "123456";
conn = DriverManager. getConnection(url, user, password);
System.out.println("database connection object = " + conn);
//Database connection object = com.mysql.cj.jdbc.ConnectionImpl@6646153
//3. Get the database operation object (statement executes SQL statement specially)
stmt = conn.createStatement();
//4. Execute the SQL statement
String sql = "insert into tb_user(id,name,age,gender) values(1,'Dashuai',20,1)";
//Specially execute DML statements (insert delete update)
int count = stmt. executeUpdate(sql);
System.out.println(count == 1 ? "saved successfully" : "saved failed");
//5. Process the query result set
} catch (SQLException e) {
e.printStackTrace();
} finally {
//6. Release resources
//In order to ensure that the resource must be released, close the resource in the finally statement
//And follow the order from small to large to close
//try...catch them respectively
try {
if (stmt != null) {
stmt. close();
}
}catch(SQLException e){
e.printStackTrace();
}
try {
if (conn != null) {
conn. close();
}
}catch(SQLException e){
e.printStackTrace();
}
}
}
}
Illustration:
So far, the database insertion operation has been completed (*^▽^*)
3. Detailed explanation of the functions of each part of JDBC
1. Driver Manager
DriverManager: drive management object
Register the driver (tell the program which database driver to use)
Register the given driver: static void registerDriver(Driver driver) (Method of DriverManager)
We did not register the driver in the introductory case just now, and it succeeded. What’s going on?
This is because we used Class.forName: Class.forName(“com.mysql.jdbc.Driver”)
We passed the forName to specify the driver of mysql
It will help us register the driver as follows:
There is a static code block in the com.mysql.jdbc.Driver class (found by viewing the source code)
Driver driver = new com.mysql.cj.jdbc.Driver();
DriverManager.registerDriver(driver);
//2. Get the connection
String url = "jdbc:mysql:/itcast";
String user = "root";
String password = "123456";
conn = DriverManager. getConnection(url, user, password);
Note: We don’t need to call the static method registerDriver() through DriverManager, because as long as the Driver class is used, its static code block will be executed to complete the driver registration
After mysql5, the step of registering the driver can be omitted. In the jar package, there is a java.sql.Driver configuration file, which specifies com.mysql.jdbc.Driver
So later we can actually omit the steps of registering the driver (you can comment out the steps of registering the driver in the previous case, and you can also query the data)
Get the database connection (get the connection to the database and return the connection object)
static Connection getConnection(String url, String user, String password);
Return value: Connection database connection object
parameter
url: Specifies the path of the connection. Syntax: jdbc:mysql://ip address (domain name): port number/database name
user: username
password: password
2. Connection
Connection: database connection object
Get the executor object
Get the common executor object: Statement createStatement();
Get the precompiled executor object: PreparedStatement prepareStatement(String sql);
management affairs
Start the transaction: setAutoCommit(boolean autoCommit); If the parameter is false, the transaction will be started.
Commit the transaction: commit();
Rollback transaction: rollback();
Release resources
Immediately release the database connection object: void close();
Connection conn = null;
if (conn != null) {
conn. close();
}
3. Statement
Statement: The object that executes the SQL statement
Execute DML statement: int executeUpdate(String sql);
Return value int: Returns the number of rows affected.
Parameter sql: insert, update, delete statements can be executed.
Execute DQL statement: ResultSet executeQuery(String sql);
Return value ResultSet: encapsulates the result of the query.
Parameter sql: select statement can be executed.
Release resources
Immediately release the executor object: void close();
stmt = conn.createStatement();
//4. Execute the SQL statement
String sql = "insert into tb_user(id,name,age,gender) values(1,'Dashuai',20,1)";
//Specially execute DML statements (insert delete update)
int count = stmt. executeUpdate(sql);