Database connection pool

Introduction to The Connection Pool

What is connection pooling

  • In practice, “getting connections” or “releasing resources” are two processes that consume very much system resources. In order to solve such performance problems, Connection pooling technology is usually adopted to share connections. This way we don’t have to create and release a connection every time. All of this is left to the connection pool.

Benefits of connection pooling

  • Pools manage connections so that they can be reused. When you’re done with Connection, calling the close() method on Connection doesn’t actually close it either, but rather “returns” the Connection to the pool.

JDBC mode and connection pool mode

Common JDBC mode

Connection pool mode

How do I use database connection pools

  • Java provides a common interface for database connection pools: javax.sql.datasource. Vendors need to implement this interface for their own connection pools. This allows the application to easily switch between connection pools from different vendors!
  • Common connection pools are DBCP connection pools, C3P0 connection pools, and Druid connection pools

DBCP connection pool

  • DBCP is also an open source connection pool, which is a member of Apache and is common in enterprise development, as well as tomcat’s built-in connection pool.

Common Configuration Items

attribute describe
driverClassName Database driver name
url Database address
username The user name
password password
maxActive Maximum connection number
maxIdle Maximum free connection
minIdle Minimum idle connection
initialSize Initializing a connection

C3P0 connection pool

  • C3P0 is an open source JDBC connection pool that supports the JDBC3 specification and a standard extension of JDBC2. Currently, open source projects using it include Hibernate, Spring, etc.

Common configuration

classification attribute describe
Item must be user The user name
Item must be password password
Item must be driverClass drive
Item must be jdbcUrl The path
The basic configuration initialPoolSize The number of connections created during connection pool initialization

Default value: 3
The basic configuration maxPoolSize The maximum number of connections in the connection pool

Default value: 15
The basic configuration minPoolSize The minimum number of connections maintained by the connection pool

10
The basic configuration maxIdleTime Maximum idle time of a connection. If this time is exceeded and a database connection is not in use, the connection is disconnected, and if 0, the connection is never disconnected.

Default value: 0

Druid connection pool

  • Druid is a database connection pool developed by Alibaba for monitoring. Druid is the best database connection pool available. It outperforms other database connection pools in terms of functions, performance, and expansibility. In addition, log monitoring is added to monitor DB pool connections and SQL execution.

DBUtils tools

DBUtils profile

  • With JDBC we found that there was too much redundant code, so we chose to use DbUtils to simplify development
  • Commons DbUtils is an open source tool class library provided by the Apache organization that simply encapsulates JDBC. Using it, JDBC application development can be simplified without affecting application performance.

This section describes the core functions of Dbutils

  • Apis for manipulating SQL statements are provided in QueryRunner
  • The ResultSetHandler interface, used to define the select operation, how to encapsulate the result set
  • The DbUtils class, which is a utility class, defines methods for closing resources and transaction processing

Case knowledge

Relationships between tables and classes

  • The entire table can be thought of as a class
  • A row in a table that corresponds to an instance (object) of a class
  • A column in a table that corresponds to a member attribute in a class

JavaBean component

1) JavaBean is a class that is usually used to encapsulate data during development

  • Need to implement serialization interface, Serializable
  • Provide private fields: private variable names;
  • Provide getters and setters
  • Provides an empty parameter construct

Select Employee from Employee

  • We can create an Entity package specifically to house JavaBean classes

DBUtils complete CRUD

QueryRunner core classes

  • A constructor
    • QueryRunner()
    • QueryRunner(DataSource ds), provides the DataSource (connection pool), DBUtils automatically maintains the connection
  • Commonly used method
    • update(Connection conn, String sql, Object… Params), which is used to add, delete and update table data
    • query(Connection conn, String sql, ResultSetHandler rsh, Object… Params), used to complete the table data query operation

QueryRunner creation

  • Manual mode
// Create QueryRunner objects manually
QueryRunner qr = new QueryRunner();
Copy the code
  • Automatic mode
// Automatically create incoming database connection pool objects
QueryRunner qr2 = new QueryRunner(DruidUtils.getDataSource());
Copy the code
  • Automatic mode requires passing in connection pool objects
// Get the connection pool object
public static DataSource getDataSource(a){ 
    return dataSource; 
}
Copy the code

QueryRunner implements add, delete, and change operations

  • Core method

update(Connection conn, String sql, Object... params)

parameter instructions
Connection conn Database connection object, automatic mode create QueryRun can not pass, manual mode must pass
String sql SQL in placeholder form, using? The placeholder
Object… param A variable parameter of type Object, used to set parameters on placeholders
  • steps

1. Create QueryRunner(manual or automatic) 2. Write SQL 3 as placeholders. Set placeholder parameters. 4

QueryRunner implements query operations

ResultSetHandler Interface overview

  • The ResultSetHandler can process the queried ResultSet to meet some business requirements.

ResultSetHandler result set handling class

  • ResultSetHandler interface of several common implementation classes to achieve the increase, delete, change and check database, can greatly reduce the amount of code, optimize the program.
  • Each implementation class represents a way of dealing with a query result set
ResultSetHandler implementation class instructions
ArrayHandler Encapsulate the first record in the result set into an Object[] array, where each element in the array is the value of each field in the record
ArrayListHandler Encapsulate each record in the result set into an Object[] array, which is then encapsulated into a List collection.
BeanHandler Encapsulates the first record in the result set into a specified javaBean
BeanListHandler Encapsulate each record in the result set into the specified Javabeans, which are then encapsulated into the List collection
ColumnListHandler Encapsulates the field values of the columns specified in the result set into a List collection
KeyedHandler Encapsulate each record in the result set into Map<String,Object>, and use this Map set as the value of another Map whose key is the value of the specified field.
MapHandler Encapsulate the first record in the result set into the Map<String,Object> collection, where key is the field name and value is the field value
MapListHandler Encapsulate each record in the result set into a Map<String,Object> set, where key is the field name and value is the field value, and then encapsulate the Map into a List set.
ScalarHandler It is used to encapsulate individual data. For example, select count(*) from a table.

ResultSetHandler is often used to implement class tests

  • QueryRunner’s query method
  • The return value of the Query method is generic, and the return value type varies depending on how the result set is processed
methods instructions
query(String sql, handler ,Object[] param) Automatic mode creates QueryRunner, which executes the query <b
query(Connection con,String sql,handler,Object[] param) Create QueryRunner in manual mode and execute the query

Database batch

What is batch processing

  • Batch operations operate databases
    • Batch processing refers to the execution of multiple SQL statements in a single operation. Batch processing is much more efficient than a single execution.
    • Batch processing is used when adding large amounts of data to a database.
  • Example: Delivery man’s job:
    • When batch processing is not used, the deliveryman can only deliver one item to the merchant at a time.
    • With batch processing, the delivery man drives all the goods to the distribution office for delivery to the customer.

Implement batch processing

  • Preparedstatements and PreparedStatements both support batch operations. Here’s how preparedStatements can be batched:
methods instructions
void addBatch() Adds the given SQL command to the current command list of the Statement object.

The commands in this list can be executed in batches by calling the method executeBatch.
int[] executeBatch() Each time a batch of commands is submitted to the database for execution, if all the commands are successfully executed, an array is returned indicating the number of lines affected by each command

MySql metadata

What is metadata

  • Data other than tables is metadata and can be divided into three categories
    • Query result: Number of records affected by the UPDATE or DELETE statement.
    • Database and table information: Contains information about the structure of databases and tables.
    • MySQL server information: contains the current status and version number of the database server.

Common commands

- Describes metadata commands
-- 1. View the current server status
-- 2. Check the version of MySQl
-- 3. Query the details in the table
-- 4. Display the detailed index information of the table
-- 5. List all databases
-- 6. Display all tables in the current database
-- 7. Get the current database name
Copy the code
  • select version(); Obtain the version information of the mysql server
  • show status; View the server status
  • show columns from table_name; Select * from table_name where table_name = desc
  • show index from table_name; Display table index details, including PRIMARY KEY
  • Show databases: Lists all databases
  • Show tables: Displays all tables of the current database
  • Select database(): Gets the current database name

Get metadata using JDBC

  • Metadata can also be obtained through JDBC, such as database information, or when a program queries an unfamiliar table, we can obtain element data information to learn how many fields are in the table, the name of the field, and the type of the field.

Introduction to Common Classes

  • A class in JDBC that describes metadata
Metadata class role
DatabaseMetaData The metadata object that describes the database
ResultSetMetaData The metadata object that describes the result set
  • GetMetaData ()
    • The connection object calls the getMetaData() method to retrieve the DatabaseMetaData DatabaseMetaData object
    • PrepareStatement preprocessing object calls getMetaData() to retrieve ResultSetMetaData, the ResultSetMetaData object
  • A common method of DatabaseMetaData
Method statement
GetURL (): Gets the URL of the database
GetUserName (): Gets the user name of the current database
GetDatabaseProductName (): Gets the product name of the database
The version number of getDatabaseProductVersion () : to get the data
GetDriverName (): Returns the name of the driver
IsReadOnly (): checks whether the database is read-only. True indicates that the database is read-only
  • ResultSetMetaData common methods
Method statement
GetColumnCount (): The number of columns in the current result set
GetColumnName (int I): Obtains the column name of the specified column number. The parameter is an integer starting from 1
GetColumnTypeName (int I): Obtains the column type of the specified column number. The parameter is an integer starting from 1