If there are any improper articles, please correct them. If you like reading on wechat, you can also follow my wechat official account: Learn Java well and get quality learning resources.
A, JDBC
JAVA Database Connectivity JAVA Database Connectivity.
JDBC (Java DataBase Connectivity) is a Java API for executing SQL statements. It provides unified access to multiple relational databases. It consists of a set of classes and interfaces written in the Java language. JDBC provides a benchmark against which more advanced tools and interfaces can be built to enable database developers to write database applications, and it is also a brand name.
Why JDBC
A database access rule and specification provided by SUN. Because there are many kinds of databases and the Java language is widely used, SUN provides a specification for other database providers to implement the underlying access rules. Our Java program only needs to use the JDBC driver provided by Sun.
Third, database driver
After we have installed the database, our application can not directly use the database, must be through the corresponding database driver, through the driver to deal with the database. In fact, it is the DATABASE vendor’s JDBC interface implementation, that is, the Connection interface implementation class JAR file.
4. Common interfaces
1. The Driver interface
The Driver interface is provided by the database vendor, and as a Java developer, you only need to use the Driver interface. In programming to connect to the database, you must first load the database driver of a specific vendor, different databases have different loading methods. Such as:
-
Class. ForName (” com.mysql.jdbc.driver “);
-
Load the Oracle driver: Class. Class.forname (“… The Oracle JDBC driver OracleDriver “);
2. The Connection interface
Connection A Connection (session) to a particular database in which SQL statements are executed and results are returned. DriverManager. GetConnection (url, user, password) method to set up the database Connection of defined in the JDBC url Connection.
-
Connect the MySql database: Connection conn = DriverManager getConnection (” JDBC: MySql: / / host: port/database “, “user”, “password”);
-
Connection: Oracle database Connection conn = DriverManager. GetConnection (” JDBC: Oracle: thin: @ host: port: database “, “user”, “password”);
-
Connection is essentially database: Connection conn = DriverManager. GetConnection (” JDBC: Microsoft: essentially: / / host: port; DatabaseName=database”, “user”, “password”);
Common methods:
- CreateStatement () : Creates a statement object that sends SQL to the database.
- PrepareStatement (SQL) : Creates a PrepareSatement object that sends precompiled SQL to the database.
- PrepareCall (SQL) : Creates the callableStatement object that executes the stored procedure.
- SetAutoCommit (Boolean autoCommit) : Sets whether a transaction is committed automatically.
- Commit () : Commits the transaction on the link.
- Rollback () : Rolls back the transaction on this link.
3. The Statement interface
An object used to execute a static SQL statement and return the results it generates.
There are three Statement classes:
- Statement: The Statement is created by createStatement and is used to send simple SQL statements (without parameters).
- PreparedStatement: Inherited from the Statement interface and created by PreparedStatement, it is used to send SQL statements containing one or more parameters. PreparedStatement objects are more efficient than Statement objects and can prevent SQL injection, so we generally use PreparedStatements.
- CallableStatement: Inherits from the PreparedStatement interface, created by the method prepareCall, and is used to call stored procedures.
Common Statement methods:
- Execute (String SQL): Runs the statement and returns whether there is a result set
- ExecuteQuery (String SQL) : Runs a SELECT statement and returns a ResultSet.
- ExecuteUpdate (String SQL) : Run insert/update/delete and return the number of updated rows.
- AddBatch (String SQL) : Adds multiple SQL statements to a batch.
- ExecuteBatch () : sends a batch of SQL statements to the database to execute.
4. The ResultSet interface
A ResultSet provides methods for retrieving different types of fields. The commonly used methods are:
- GetString (int index) and getString(String columnName) : Obtain data objects of the types vARCHAR and CHAR in the database.
- GetFloat (int Index), getFloat(String columnName) : Get data objects that are of type Float in the database.
- GetDate (int index), getDate(String columnName) : Obtain data of the Date type in the database.
- GetBoolean (int index), getBoolean(String columnName) : Get data in the database that is of Boolean type.
- GetObject (int index), getObject(String columnName) : Obtains data of any type in the database.
A ResultSet also provides a way to scroll through a ResultSet:
- Next () : Moves to the next line
- Previous() : Moves to the Previous row
- Absolute (int row) : Moves to a specified row
- BeforeFirst () : Moves the first of a resultSet.
- AfterLast () : Moves to the end of the resultSet.
Close the object and Connection in sequence: ResultSet → Statement → Connection
Basic steps for using JDBC
1. Register the driver
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Copy the code2. Establish a connection
//DriverManager.getConnection("jdbc:mysql://localhost/test? user=SIHAI&password=SIHAI"); //2. Establish a connection. Parameter 1: protocol + access database, parameter 2: user name, and parameter 3: password. conn = DriverManager.getConnection("jdbc:mysql://localhost/student"."root"."root");
Copy the code3. Create a statement
//3. St = conn.createstatement ();Copy the code4. Run the SQL to obtain the ResultSet
//4. Run the query and get the result set String SQL ="select * from t_stu";
rs = st.executeQuery(sql);
Copy the code5. Iterate over the result set
//5. Iterate to query each recordwhile(rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
System.out.println("id="+id + "===name="+name+"==age="+age);
}
Copy the code6. Release resources
if(rs ! = null) { try { rs.close(); } catch (SQLException sqlEx) { } // ignore rs = null; }Copy the codeVi. JDBC tool class construction
1. Integration of resource release efforts
@conn @param st @param rs public static void release(Connection conn, Statement st, ResultSet rs){ closeRs(rs); closeSt(st); closeConn(conn); } private static void closeRs(ResultSet rs){ try {if(rs ! = null){ rs.close(); } } catch (SQLException e) { e.printStackTrace(); }finally{ rs = null; } } private static void closeSt(Statement st){ try {if(st ! = null){ st.close(); } } catch (SQLException e) { e.printStackTrace(); }finally{ st = null; } } private static void closeConn(Connection conn){ try {if(conn != null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
conn = null;
}
}
Copy the code2. Prevent driver secondary registration
/** * get the connection object * @return
*/
public static Connection getConn(){ Connection conn = null; try { Class.forName(driverClass); // Static code block --> Class loaded, execute. java.sql.DriverManager.registerDriver(new Driver()); //DriverManager.registerDriver(new com.mysql.jdbc.Driver()); //DriverManager.getConnection("jdbc:mysql://localhost/test? user=monty&password=greatsqldb"); //2. Establish a connection. Parameter 1: protocol + access database, parameter 2: user name, and parameter 3: password. conn = DriverManager.getConnection(url, name, password); } catch (Exception e) { e.printStackTrace(); }return conn;
}
Copy the code3. Use the Properties configuration file
- Declare a file xxx.properties under SRC and spit out the contents:
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost/student
name=root
password=root
Copy the code- Inside the utility class, use static code blocks to read properties
Static {try {//1. Create a Properties configuration object Properties = new Properties(); InputStream is = new FileInputStream("jdbc.properties"); // Use the class loader to read the resource file under SRC. Behind the servlet / / corresponding files in the SRC directory under / / InputStream is = JDBCUtil. Class. GetClassLoader (). GetResourceAsStream ("jdbc.properties"); // Import the input stream. properties.load(is); DriverClass = properties.getProperty(driverClass = properties.getProperty(driverClass = properties.getProperty)"driverClass");
url = properties.getProperty("url");
name = properties.getProperty("name");
password = properties.getProperty("password"); } catch (Exception e) { e.printStackTrace(); }}Copy the codeSource code is as follows:
public class JDBCUtil { static String driverClass = null; static String url = null; static String name = null; static String password= null; Static {try {//1. Create a Properties configuration object Properties = new Properties(); InputStream is = new FileInputStream("jdbc.properties"); // Use the class loader to read the resource file under SRC. Behind at the servlet / / InputStream is = JDBCUtil. Class. GetClassLoader () getResourceAsStream ("jdbc.properties"); // Import the input stream. properties.load(is); DriverClass = properties.getProperty(driverClass = properties.getProperty(driverClass = properties.getProperty)"driverClass");
url = properties.getProperty("url");
name = properties.getProperty("name");
password = properties.getProperty("password"); } catch (Exception e) { e.printStackTrace(); }} /** * get the connection object * @return
*/
public static Connection getConn(){ Connection conn = null; try { Class.forName(driverClass); // Static code block --> Class loaded, execute. java.sql.DriverManager.registerDriver(new Driver()); //DriverManager.registerDriver(new com.mysql.jdbc.Driver()); //DriverManager.getConnection("jdbc:mysql://localhost/test? user=monty&password=greatsqldb"); //2. Establish a connection. Parameter 1: protocol + access database, parameter 2: user name, and parameter 3: password. conn = DriverManager.getConnection(url, name, password); } catch (Exception e) { e.printStackTrace(); }returnconn; Public static void release(Connection conn, Statement st, ResultSet rs){ closeRs(rs); closeSt(st); closeConn(conn); } private static void closeRs(ResultSet rs){ try {if(rs ! = null){ rs.close(); } } catch (SQLException e) { e.printStackTrace(); }finally{ rs = null; } } private static void closeSt(Statement st){ try {if(st ! = null){ st.close(); } } catch (SQLException e) { e.printStackTrace(); }finally{ st = null; } } private static void closeConn(Connection conn){ try {if(conn ! = null){ conn.close(); } } catch (SQLException e) { e.printStackTrace(); }finally{ conn = null; }}}Copy the code
7. CRUD of database
- Insert operations
INSERT INTO t_stu (NAME , age) VALUES ('wangqiang',28)
INSERT INTO t_stu VALUES (NULL,'wangqiang2'28),Copy the codeConn = jdbcutil.getConn (); // 1. // 2. Statement st = conn.createstatement (); //3. Execute add String SQL ="insert into t_stu values(null , 'aobama' , 59)"; // Number of affected rows,, if greater than 0, the operation succeeded. Int result = st.executeUpdate(SQL);if(result >0 ){
System.out.println("Added successfully");
}else{
System.out.println("Add failed");
}
Copy the code- The delete operation
DELETE FROM t_stu WHERE id = 6
Copy the codeConn = jdbcutil.getConn (); // 1. // 2. Statement st = conn.createstatement (); //3. Execute add String SQL ="delete from t_stu where name='aobama'"; // Number of affected rows,, if greater than 0, the operation succeeded. Int result = st.executeUpdate(SQL);if(result >0 ){
System.out.println("Deleted successfully");
}else{
System.out.println("Delete failed");
}
Copy the code- Query operation
SELECT * FROM t_stu
Copy the codeConn = jdbcutil.getConn (); // 1. // 2. Statement st = conn.createstatement (); // 3. Run the SQL statement and return ResultSet String SQL ="select * from t_stu"; rs = st.executeQuery(sql); // 4. Iterate over the result setwhile (rs.next()) {
String name = rs.getString("name");
int age = rs.getInt("age");
System.out.println(name + "" + age);
}
Copy the code- The update operation
UPDATE t_stu SET age = 38 WHERE id = 1;
Copy the codeConn = jdbcutil.getConn (); // 1. // 2. Statement st = conn.createstatement (); //3. Execute add String SQL ="update t_stu set age = 26 where name ='qyq'"; // Number of affected rows,, if greater than 0, the operation succeeded. Int result = st.executeUpdate(SQL);if(result >0 ){
System.out.println("Update successful");
}else{
System.out.println("Update failed");
}
Copy the codeUse unit tests to test your code
1. Define a class, TestXXX, that defines the method TestXXX.
This naming is not necessary, but this naming is easier to understand the meaning of the test, so it is recommended to name the meaning.
2. Add junit support.
Right-click project – Add Library – Junit – Junit4
3. Add a comment at the top of a method, which is essentially a tag.
/** * public class TestDemo {@test public voidtestQuery() {// query Connection conn = null; Statement st = null; ResultSet rs = null; Conn = jdbcutil.getConn (); // 2. Statement st = conn.createstatement (); // 3. Run the SQL statement and return ResultSet String SQL ="select * from t_stu"; rs = st.executeQuery(sql); // 4. Iterate over the result setwhile (rs.next()) {
String name = rs.getString("name");
int age = rs.getInt("age");
System.out.println(name + "" + age);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.release(conn, st, rs);
}
}
@Test
public void testInsert(){// query Connection conn = null; Statement st = null; Conn = jdbcutil.getConn (); // 2. Statement st = conn.createstatement (); //3. Execute add String SQL ="insert into t_stu values(null , 'aobama' , 59)"; // Number of affected rows,, if greater than 0, the operation succeeded. Int result = st.executeUpdate(SQL);if(result >0 ){
System.out.println("Added successfully");
}else{
System.out.println("Add failed");
}
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCUtil.release(conn, st);
}
}
@Test
public void testDelete(){// query Connection conn = null; Statement st = null; Conn = jdbcutil.getConn (); // 2. Statement st = conn.createstatement (); //3. Execute add String SQL ="delete from t_stu where name='aobama'"; // Number of affected rows,, if greater than 0, the operation succeeded. Int result = st.executeUpdate(SQL);if(result >0 ){
System.out.println("Deleted successfully");
}else{
System.out.println("Delete failed");
}
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCUtil.release(conn, st);
}
}
@Test
public void testUpdate(){// query Connection conn = null; Statement st = null; Conn = jdbcutil.getConn (); // 2. Statement st = conn.createstatement (); //3. Execute add String SQL ="update t_stu set age = 26 where name ='qyq'"; // Number of affected rows,, if greater than 0, the operation succeeded. Int result = st.executeUpdate(SQL);if(result >0 ){
System.out.println("Update successful");
}else{
System.out.println("Update failed"); } } catch (Exception e) { e.printStackTrace(); }finally{ JDBCUtil.release(conn, st); }}}Copy the code4. Select the method name and right-click to execute the unit test. Or open the Outline view and right-click the method to execute.
Dao mode
Data Access Object Data Access Object
DAO(Data Access Object) is an object-oriented database interface that exposes the Microsoft Jet database engine (used by Microsoft Access), And allows Visual Basic developers to connect directly to Access tables through ODBC as they would to any other database. Daos are best suited for single-system applications or small scale locally distributed use.
1. Create a DAO interface to declare database access rules
Public interface UserDao {/** * query all */ void findAll(); }Copy the code2. Create a DAO implementation class to implement the rules defined earlier
public class UserDaoImpl implements UserDao{
@Override
public void findAll() { Connection conn = null; Statement st = null; ResultSet rs = null; Conn = jdbcutil.getConn (); //2. Create a statement object st = conn.createstatement (); String sql ="select * from t_user";
rs = st.executeQuery(sql);
while(rs.next()){
String userName = rs.getString("username");
String password = rs.getString("password");
System.out.println(userName+"="+password); } } catch (Exception e) { e.printStackTrace(); }finally { JDBCUtil.release(conn, st, rs); }}}Copy the code3. Use the implementation directly
@Test
public void testFindAll(){
UserDao dao = new UserDaoImpl();
dao.findAll();
}
Copy the codeStatement security
1. Statement execution, in fact, concatenates SQL statements. The SQL statements are concatenated and then executed together.
String sql = "select * from t_user where username='"+ username +"' and password='"+ password +"'";
UserDao dao = new UserDaoImpl();
dao.login("admin"."100234khsdf88' or '1=1");
SELECT * FROM t_user WHERE username='admin' AND PASSWORD='100234khsdf88' or '1 = 1'Mysql > select * from 'SQL'; mysql > select * from 'SQL'; mysql > select * from 'SQL'; Not considered a normal string. rs = st.executeQuery(sql);Copy the codePrepareStatement
This object replaces the previous Statement object.
- In contrast to previous statements, a given SQL statement is pre-processed and its syntax checked. Use in SQL statement? Placeholders to replace variables to be passed in later. Subsequent variable values will be treated as strings and will not generate any keywords.
String sql = "insert into t_user values(null , ? , ?)"; ps = conn.prepareStatement(sql); // Assign placeholder values from left to right, 1 represents the first question mark, always you are 1 to start. ps.setString(1, userName); ps.setString(2, password);Copy the codePreparedStatement is compared with Statement
(1) In PreparedStatement, the code is more readable and maintainable than Statement.
(2) PreparedStatement can maximize performance.
DBServer provides performance optimizations for precompiled statements. Because a precompiled statement can be called repeatedly, the statement is cached in the executing code compiled by the DBServer compiler, so the next call does not need to be compiled as long as the same precompiled statement is passed the parameters directly into the compiled statement executing code.
In a statement, even though the operation is the same, the data content is different. Therefore, the entire statement itself cannot match, so it does not have the meaning of caching statements. The fact is that no database caches the code executed after compiling ordinary statements. This will compile the incoming statement each time it is executed.
(3) PreparedStatement can ensure security, but Statement has security problems such as SQL injection.
Database transactions
1. An overview of the
In a database, a transaction is a set of logical units of operation that change data from one state to another.
In order to ensure the consistency of the data in the database, data manipulation should be discrete groups of logical units: when it is complete, can maintain the consistency of the data, and as part of the unit operation fails, the entire transaction shall all be regarded as wrong, all from the starting point of operation should be back to the start state.
Operation of a transaction: you define a transaction and then make changes to the data. If you COMMIT, the changes are saved permanently. If you ROLLBACK, the DATABASE management system will discard all changes and return to the state where you started the transaction.
2. ACID properties of transactions
2.1 Atomicity
Atomicity refers to the fact that a transaction is an indivisible unit of work in which all or none of the operations occur.
2.2 Consistency
Transactions must move the database from one consistent state to another. (Data is not destroyed)
2.3 Isolation (Isolation)
The isolation of a transaction means that the execution of a transaction cannot be interfered by other transactions, that is, the operations and data used within a transaction are isolated from other concurrent transactions, and the concurrent transactions cannot interfere with each other.
2.4 Durability
Persistence means that once a transaction is committed, its changes to the data in the database are permanent and should not be affected by subsequent operations or database failures.
3. JDBC transactions
In JDBC, transactions are committed automatically by default. Each time an SQL statement is executed, if it succeeds, it is automatically committed to the database and cannot be rolled back.
To execute multiple SQL statements as one transaction, call setAutoCommit(false) on the Connection object; To cancel the auto-commit transaction:
conn.setAutoCommit(false);
Copy the codeAfter all SQL statements have successfully executed, call commit(); Method commit transaction
conn.commit();
Copy the codeRollback () is called when an exception occurs; Method to roll back the transaction, usually in the CATCH module.
conn.rollback();
Copy the codeThe commit mode of the current transaction can be obtained through the getAutoCommit() method of Connection.
Note: InnoDB, the database storage engine in MySQL, supports transactions, while MyISAM does not.
Batch processing JDBC statements
1. An overview of the
When records need to be batch inserted or updated. You can use Java’s batch update mechanism, which allows multiple statements to be submitted to the database for batch processing at once. It is usually more efficient than commit processing alone.
JDBC bulk processing statements include the following two methods:
- AddBatch (String) : adds SQL statements or parameters to be processed in batches.
- ExecuteBatch (); Execute batch processing statements;
There are usually two types of batch execution of SQL statements:
- Batch processing of multiple SQL statements;
- Batch parameter transfer of an SQL statement;
2. Statement batch processing
Statement sm = conn.createStatement(); sm.addBatch(sql1); sm.addBatch(sql2); . Sm.executebatch () // Clear the list of parameters in sm.clearBatch();Copy the code3. PreparedStatement Batch parameter transfer
preparedStatement ps = conn.preparedStatement(sql);
for(int i=1; i<100000; i++){ ps.setInt(1, i); ps.setString(2,"name"+i);
ps.setString(3, "email"+i);
ps.addBatch();
if((I +1)%1000==0){ps.executeBatch(); // Clear SQL ps.clearBatch(); }}Copy the codeNote: MySQL does not support batch processing.
There should be an upper limit for batch processing, when the SQL in the batch list has accumulated enough, it should be executed, and when the batch list is finished, it should be cleared.
Batch processing is typically used when Excel imports data.
Use JDBC to handle metadata
1. An overview of the
Java through JDBC Connection, get a Connection object, you can get a variety of information about the database management system from this object, including each table in the database, each column in the table, data types, triggers, stored procedures and other aspects of the information. Based on this information, JDBC can access a database that the implementation has no prior knowledge of.
The methods to retrieve this information are implemented on objects of the DatabaseMetaData class, which are retrieved on Connection objects.
2. Obtain database metadata
The DatabaseMetaData class provides a number of methods to retrieve information about a data source. These methods provide a detailed look at the database:
- GetURL () : Returns a String object representing the URL of the database.
- GetUserName () : Returns the user name for connecting to the current database management system.
- IsReadOnly () : Returns a Boolean indicating whether the database allows only read operations.
- GetDatabaseProductName () : Returns the product name of the database.
- GetDatabaseProductVersion () : returns the database version number.
- GetDriverName () : Returns the name of the driver driver.
- GetDriverVersion () : Returns the version number of the driver.
3. ResultSetMetaData
An object that can be used to get information about the types and attributes of columns in a ResultSet object:
- GetColumnName (int column) : Obtains the name of the specified column
- GetColumnCount () : Returns the number of columns in the current ResultSet object.
- GetColumnTypeName (int column) : Retrieves the database-specific type name of the specified column.
- GetColumnDisplaySize (int column) : indicates the maximum standard width of the specified column, in characters.
- IsNullable (int column) : Indicates whether a value in a specified column can be null.
- IsAutoIncrement (int column) : Indicates whether the specified column is automatically numbered so that the column is still read-only.
Create scrollable, updatable recordsets
1. Statement
Statement stmt = conn.createStatement(type,concurrency);
Copy the code2. PreparedStatement
PreparedStatement stmt = conn.prepareStatement(sql,type,concurrency);
Copy the codeType:
| The Type of the ResultSet | instructions |
|---|---|
| TYPE_FORWARD_ONLY | Result sets cannot be scrolled, only forward |
| TYPE_SCROLL_INSENSITIVE | Two-way scrolling, but not updated in time, that is, if the data in the database has been modified, it is not reflected in the ResultSet |
| TYPE_SCROLL_SENSITIVE | Scrolling in both directions and keeping track of database updates to change the data in the ResultSet |
Concurrency Concurrency description:
| Concurrency for ResultSet | instructions |
|---|---|
| CONCUR_READ_ONLY | The result set cannot be used to update the database |
| CONCUR_UPDATABLE | The result set can be used to update the database |
3. Use ResultSet to scroll the ResultSet
First: Moves pointer to the First row of this ResultSet object. Last: Moves pointer to the Last row of this ResultSet object. BeforeFirst: Moves pointer to the First row of this ResultSet object. AfterLast: Moves the pointer to the end of this ResultSet object, just after the last line isFirst: IsBeforeFirst: Whether the retrieve pointer is located before the first row of this ResultSet isAfterLast: isBeforeFirst: Whether the retrieve pointer is located before the first row of this ResultSet isAfterLast: Relative: Moves the pointer by the Relative number of rows (either positive or negative) Next: moves the pointer down one row from the current position Previous: Moves the pointer to the Previous row of this ResultSet object Absolute: Moves the pointer to the given row number of this ResultSet object
Such as:
rs.absolute(80); // Move the pointer to line 80 of the ResultSet object.Copy the code** Note: ** This feature works for Oralce data. This is invalid in Mysql databases, which only supports TYPE_SCROLL_INSENSITIVE,CONCUR_READ_ONLY.
JDBC connection pool
1. Why use JDBC connection pooling
Normal JDBC database connections are obtained using DriverManager, and each time a Connection is established to the database, the Connection is loaded into memory and the user name and password are verified. When you need a database connection, you ask the database for one, and then disconnect when it’s done. This way will consume a lot of resources and time. The connection resources of the database are not well reused. If hundreds or even thousands of people are online at the same time, frequent database connection operations will occupy a lot of system resources, and even cause server crash.
For each database connection, disconnect after use. Otherwise, if the program fails to close due to an exception, it will cause a memory leak in the database system, which will eventually cause the database to restart.
This development does not control the number of connection objects that are created, and system resources can be allocated recklessly, such as too many connections, memory leaks, and server crashes.
To solve the problem of database connection in traditional development, database connection pool technology can be used.
2. Database connection pool
The basic idea of database connection pooling is to create a “buffer pool” for database connections. A certain number of connections are placed in the buffer pool beforehand, and when a database connection needs to be established, you simply take one out of the buffer pool and put it back in.
Database connection pooling is responsible for allocating, managing, and releasing database connections, and allows applications to reuse an existing database connection rather than re-creating one.
Database connection pool initialization creates a number of database connections into the pool, the number of database connections is set by the minimum number of database connections. Whether or not these database connections are used, the connection pool is always guaranteed to have at least this many connections. The maximum number of database connections in a connection pool limits the maximum number of connections that the pool can hold, and when applications exceed the maximum number of connections requested from the pool, those requests are queued.
3. Working principle of database connection pool
4. Advantages of using database connection pools
(1) Resource reuse:
Because database connections are reused, frequent creation is avoided, freeing up a lot of the performance overhead associated with connections. On the basis of reducing system consumption, on the other hand, it also increases the stability of system running environment.
(2) Faster system response speed
Database connection pool During initialization, several database connections are created and stored in the connection pool. The initialization of the connection is complete. For business request processing, the existing available connections are directly utilized to avoid the time overhead of database connection initialization and release process, thus reducing the response time of the system.
(3) New means of resource allocation
For a system where multiple applications share the same database, you can configure the database connection pool at the application layer to limit the maximum number of available database connections for an application, preventing an application from monopolizing all database resources.
(4) Unified connection management, avoid database connection leakage in the more perfect database connection pool implementation, according to the preset occupation timeout setting, forced recovery of occupied connection, so as to avoid the common database connection operation may appear resource leakage.
5. Common database connection pools
JDBC’s database connection pool is represented by javax.sql.DataSource. A DataSource is an interface that is usually implemented by servers (Weblogic, WebSphere, Tomcat) and some open source organizations.
- DBCP Database connection pool
- C3P0 Database connection pool
- Proxpool Indicates the database connection pool
Among them, DBCP and C3P0 are used more frequently.
Tomcat prior to 7.0 used commons-DBCP as the connection pooling implementation.
Unlike database connections, data sources do not need to be created multiple times; they are factories that generate database connections, so the entire application only needs one.
When the database access is complete, the program closes the database connection as before: conn.close(); But it does not close the physical connection to the database, it merely releases the database connection back to the database connection pool.
About the basic is so much, I hope to help you, there are questions can communicate.