1. What is a data connection pool
Database connection pool( Database Connection Pooling)When the program is initialized, a certain number of database connection objects are created and saved in a memory area, which allows the application to reuse an existing database connection instead of re establishing one; Release the database connection whose idle time exceeds the maximum idle time to avoid database connection omission caused by not releasing the database connection.
What is JDBC
The full name of JDBC is Java Database Connectivity, which is a java API for executing SQL statements. SQL statements can be used to query, update, add and delete the data in the database.
JDBC common API s
1. Driver interface
Driver Interfaces are all JDBC The interface that the driver must implement, This interface is specially provided for database manufacturers. implement SQL Statement and get the return result. There is no need to directly access the implementation in the program Driver Interface, but by the driver manager class (java.sql.DriverManager)To call these Driver realization.
2. Driver Manager class
The Driver Manager class is used to load the JDBC driver and create a connection with the database. Two important static methods are defined in the Driver Manager class
3. Connection interface
The Connection interface represents the Connection between the java program and the database. Only after obtaining the Connection object can you access the database and operate the data table.
4. Statement interface
The Statement interface is used to execute static SQL statements and return a result object. The object to change the interface is obtained through the create Statement() method of the Connection instance.
5. PrearedStatement interface
The Statement interface encapsulates the method of JDBC executing SQL statements, which can complete the operation of java programs executing SQL statements.
String sql="INSERT INTO user(id,name,email) VALUES(?,?,?)"; PrearedStatement preStmt=conn.PrearedStatement(sql); preStmt.setInt(1,1); preStmt.setString(2,"zhangsan"); preStmt.setObject(3,"zs@sina.com"); preStmt.executeUpdata();
6. ResultSet interface
The ResultSet interface is used to save the result set returned when JDBC executes the query, which is encapsulated in a logical table.
Implement a JDBC program
(1) Load and register database drivers
(2) Get database connection through DriverManager
(3) Method to get Statement through Connection object
- createStatement(): creates a basic Statement object
- prepareStatement(): creates a PreparedStatement object
- prepareCall(): create CallableStatement object
(4) Execute SQL statements using Statement
- execute(): any SQL statement can be executed
- executeQuery(): usually execute a query statement and return the ResultSet object representing the result set after execution
- executeUpdata(): used to execute DML and DDL statements.
(5) Operation Result result set
(6) Close the connection and free up resources
1. Build database
2. Create the project environment and import the database driver
3. Write JDBC program
This class is used to read the users table in the database and output the results to the console
public class Example01 {
public static void main(String[] args) throws SQLException {
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
try {
// 1. Driver of registration database
Class.forName("com.mysql.jdbc.Driver");
// 2. Get database connection through DriverManager
String url = "jdbc:mysql://localhost:3306/jdbc";
String username = "root";
String password = "itcast";
conn = DriverManager.getConnection (url, username,
password);
// 3. Obtain the Statement object through the Connection object
stmt = conn.createStatement();
// 4. Use Statement to execute SQL Statement.
String sql = "select * from users";
rs = stmt.executeQuery(sql);
// 5. Operate ResultSet result set
System.out.println("id | name | password | email | birthday");
while (rs.next()) {
int id = rs.getInt("id"); // Gets the value of the specified field from the column name
String name = rs.getString("name");
String psw = rs.getString("password");
String email = rs.getString("email");
Date birthday = rs.getDate("birthday");
System.out.println(id + " | " + name + " | " + psw + " | " + email
+ " | " + birthday);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally{
// 6. Recycle database resources
if(rs!=null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
if(stmt!=null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
stmt = null;
}
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
}
}
2. PreparedStatement object
The Statement object is compiled each time it executes SQL. When the same SQL Statement is executed multiple times, the Statement object will make the database compile the same SQL Statement frequently, thus reducing the access efficiency of the database.
To solve this problem, Statement provides a subclass PreparedStatement.
Create a class in which the PreparedStatement object is used to insert data.
public class Example02 {
public static void main(String[] args) throws SQLException {
Connection conn = null;
PreparedStatement preStmt = null;
try {
// Load database driver
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/jdbc";
String username = "root";
String password = "itcast";
// Create a Connection object for the application to connect to the database
conn = DriverManager.getConnection(url, username, password);
// Executed SQL statement
String sql = "INSERT INTO users(name,password,email,birthday)"
+ "VALUES(?,?,?,?)";
// Create a PreparedStatement object that executes SQL statements
preStmt = conn.prepareStatement(sql);
preStmt.setString(1, "zl");
preStmt.setString(2, "123456");
preStmt.setString(3, "zl@sina.com");
preStmt.setString(4, "1789-12-23");
preStmt.executeUpdate();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally { // Release resources
if (preStmt != null) {
try {
preStmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
preStmt = null;
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
}
}
ResultSet object
ResultSet is mainly used to store the result set. You can obtain the data in the result set one by one from the front to the back through the next () method.
The ResultSet is generally returned by the executeQuery() method of the database operation Statement, and the While loop is generally used to traverse the ResultSet Use the next() method of ResultSet.
Common methods:
Create an example03 Java class, which uses the ResultSet object to retrieve the information of the specified data.
public class Example02 {
public static void main(String[] args) throws SQLException {
Connection conn = null;
PreparedStatement preStmt = null;
try {
// Load database driver
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/jdbc";
String username = "root";
String password = "itcast";
// Create a Connection object for the application to connect to the database
conn = DriverManager.getConnection(url, username, password);
// Executed SQL statement
String sql = "INSERT INTO users(name,password,email,birthday)"
+ "VALUES(?,?,?,?)";
// Create a PreparedStatement object that executes SQL statements
preStmt = conn.prepareStatement(sql);
preStmt.setString(1, "zl");
preStmt.setString(2, "123456");
preStmt.setString(3, "zl@sina.com");
preStmt.setString(4, "1789-12-23");
preStmt.executeUpdate();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally { // Release resources
if (preStmt != null) {
try {
preStmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
preStmt = null;
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
}
}