Things in Java and connection pooling

First, things

What is a thing?

Business, usually refers to the things to do or do. In computer terminology, it refers to a program execution unit that accesses and possibly updates various data items in a database. These units are either all successful, or they are all unsuccessful.

To do one thing, this one thing has multiple constituent units, which are either successful at the same time or fail at the same time. A account transferred to B account money, the operation of the transfer of a account and B account transfer money into a transaction, or the two actions at the same time success, on behalf of the successful transfer, or two actions failed at the same time, on behalf of the transfer failed.

The role of transactions in development

Here's an example of what a transaction looks like:

Real-life Bank transfer business, Zhang three to John Doe transfer 1000 Yuan, and in the programmer's eyes two SQL statements can be done, as follows:

L minus 1000 yuan for Zhang San's account;

L Add 1000 yuan to John Doe's account;

If in the transfer of business, the success of the Zhang San account minus 1000 yuan, and in the account of John Doe to add 1000 yuan, the program has a problem, John Doe account does not add 1000 yuan, and Zhang San's account has been lost 1000 yuan, in real life, this situation is certainly not allowed to exist. When we put this transfer business in one transaction, there is no such situation.

There are multiple operations in the transaction, these operations either all succeed, or all failed, that is, to Zhang San account minus 1000 yuan if successful, then to John Doe's account plus 1000 yuan operation must be successful, otherwise give Zhang San minus 1000 yuan, and to John Doe plus 1000 yuan must be a failure

MySQL's transaction control

MySQL default transaction is automatically committed, a SQL is a transaction

when a transaction is manually opened, the auto-commit of the database default transaction is temporarily invalidated manually opening the transaction: Start transaction

Commit TRANSACTION: Commit

All SQL statements between the COMMIT transaction and the open transaction are in effect

ROLLBACK TRANSACTION: Rollback

All SQL operations from ROLLBACK TRANSACTION to open transaction are invalid

Transactional control of the JDBC API

You can control transactions by connection objects

In JDBC, the way to control transactions is to control the JDBC Update database API method---executeupdate

Open transaction: Connection.setautocommit (FALSE);

Commit TRANSACTION: Connection.commit ();

ROLLBACK TRANSACTION: Connection.rollback ();

Official Introduction Business Features

The four characteristics of a transaction are referred to as acid (atomicity consistency isolation durability), respectively:

L Atomicity: The atomic counterpart of English is atomicity, which means that all operations in a transaction are non-divisible atomic units. All operations in the transaction either execute successfully or all fail;

Consistency: Consistency corresponds to the English consistency, after the transaction executes, the database state is consistent with the other business rules. For example, if the transaction is executed successfully, the sum of the two account balances of the transfer should be the same;

Isolation: The corresponding English is isolation, refers to in the concurrency operation, the different transactions should be isolated, so that each concurrency in the transaction will not interfere with each other;

L Persistence: The English for persistence corresponds to durability, which means that once the transaction commits successfully, all data operations in the transaction must be persisted to the database, even if the database crashes immediately after committing the transaction, and when the database is restarted, it must be guaranteed to recover the data through some mechanism.

Different transactions, their consistency of expression is different, the other three major features of the transaction are in fact for the consistency of services.

Summary of four characteristics of business

Atomicity: The smallest unit of operation of a database is a transaction

Consistency: Result data for multiple operations in one transaction is consistent, both successful and simultaneous failures

Isolation: Operations between multiple transactions do not affect each other

Persistence: The update operation is persisted to disk after a transaction is committed

Isolation levels and issues for transactions

An introduction to issues that do not consider isolation

n Dirty read one transaction read to another transaction uncommitted data .

n cannot be read repeatedly within a transaction, the data read from two times is inconsistent . ( Update)

N Virtual Read ( Phantom read ) Two reads inconsistent data (insert)

four types of isolation levels for transactions

Four isolation levels are defined within the database to address three isolation issues

U 1 Serializable: Can avoid dirty reading, non-repeatable reading, the occurrence of false reading. (serialization)

U 2 repeatable Read: Can avoid dirty read, non-repeatable read situation occurs. (Repeatable Read) can not avoid false reading

U 3 Read Committed: Prevents dirty reads from occurring (Read Committed)

U 4 Read uncommitted: lowest level, none of the above is guaranteed. ( Read not submitted )

The default transaction isolation level of the MySQL database -----REPEATABLE Read level .

Oracle Data Default transaction ISOLATION LEVEL ----Read Committed

SET TRANSACTION ISOLATION Level

n mysql in Settings

The database has a transaction isolation level by default , and the isolation level in MySQL to view and modify transactions

U Set session Transaction isolation level SET TRANSACTION isolation Levels

U SELECT @ @tx_isolation Query the current transaction isolation level

SET TRANSACTION ISOLATION level in n JDBC

Available in the java.sql.Connection interface

u settransactionisolation (int level);

Parameters can take one of the Connection constants:

Connection.transaction_read_uncommitted,

Connection.transaction_read_committed,

Connection.transaction_repeatable_read

Connection.transaction_serializable.

(Note that Connection.transaction_nonecannot be usedbecause it specifies an unsupported transaction.) )

Note: Transaction control must be in the service layer

Second, the connection pool

What is a connection pool

Database connectivity is a critical, limited, and expensive resource that is particularly prominent in multi-user Web applications. The management of database connections can significantly affect the scalability and robustness of the entire application, affecting the performance of the program. The database connection pool was raised for this issue. The database connection pool is responsible for allocating, managing, and freeing the database connection, which allows the application to reuse an existing database connection instead of re-establishing a database connection that is idle for more than the maximum idle time to avoid missing the database connection due to the absence of a database connection being freed. This technology can significantly improve the performance of database operations.

The following is a comparison legend that uses connection pooling and does not use connection pooling

Advantages of connection pooling

Save create connection and release connection performance consumption

Connection pooling plays a role in multiplexing, providing program performance

the principle of connection pooling

Connection pooling The basic idea is that when the system is initialized, the database connection is stored as an object in memory, and when the user needs to access the database, instead of establishing a new connection, it pulls out an established idle connection object from the connection pool. After use, the user does not close the connection, but instead puts the connection back into the connection pool for use by the next request. Connection pooling is managed by the connection pool itself, while connections are established and disconnected. You can also control the number of initial connections in the connection pool, the upper and lower limits of connections, the maximum number of uses per connection, maximum idle time, and so on, by setting parameters for the connection pool. It is also possible to monitor the number, usage, etc. of database connections through its own management mechanism.

connection Pooling Rules

there's a DataSource under the Javax.sql bag .

All java -enabled connection pools should implement the javax.sql.DataSource Interface, which provides a method in this interface getconnection () It is to get a connection object.

If the Connection object Connection is obtained through a connection pool, when the close () method is called through the Connection Object , the Instead of destroying the connection object, the connection object is put back into the connection pool.

Common Open Source connection pooling technology

Dbcp:apache Foundation's Open source connection pooling Technology Commons Project Neutron project

C3P0: Open source connection Pooling technology

D bcp connection pool

DBCP Connection Pool Introduction

DBCP is an Open source Connection pool implementation under the Apache Software fund, using the DBCP Data source, the application should add the following two jars to the system File:

• Commons-dbcp.jar: Implementation of connection Pooling
• Commons-pool.jar: Dependency libraries for connection pooling implementations

The connection pool for Tomcat is implemented with this connection pool. The database connection pool can be used either in combination with the application server or independently by the application. DBCP is an Open source Connection pool implementation under the Apache Software fund, using the DBCP Data source, the application should add the following two to the system jar file:

Commons-dbcp.jar: Implementation of connection Pooling

Commons-pool.jar: Dependency libraries for connection pooling implementations

The connection pool for Tomcat is implemented with this connection pool. The database connection pool can be used either in combination with the application server or independently by the application.

dbcp Connection Pool encoding configuration

1. To create a connection pool object

Basicdatasource ds = new Basicdatasource ();

2. Setting related properties

Ds.setdriverclassname ("Com.mysql.jdbc.Driver");

Ds.seturl ("Jdbc:mysql:///day13");

Ds.setusername ("root");

Ds.setpassword ("abc");

dbcp connection pooling using configuration files

1. Load configuration information

Properties Props = new properties ();

Props.load (New FileInputStream (Properties configuration file path );

Properties File Contents

Driverclassname=com.mysql.jdbc.driver

Url=jdbc:mysql:///day13

Username=root

Password=abc

2. through basicdatasourcefactory Gets a connection pool object

DataSource ds = Basicdatasourcefactory.createdatasource (props);

C 3p0 Connection pool

c3p0 Connection Pool Introduction

C3P0 is an open source JDBC Connection pool that implements the data source and JNDI bindings, supports the JDBC3 specification and Standard extension of the JDBC2. The open source projects that currently use it are Hibernate,Spring , and so on.

The difference between c3p0 and dbcp

DBCP does not automatically reclaim idle connection functions

C3P0 has automatic recycle idle connection function

When you use the c3p0 Connection Pool , the c3p0 jar is copied web-inf/lib , the version we use

C3p0-0.9.1.2.jar

c3p0 Connection Pool encoding configuration

Combopooleddatasource ds = new Combopooleddatasource ();

2. Manual configuration Parameters

Ds.setdriverclass ("Com.mysql.jdbc.Driver");

Ds.setjdbcurl ("Jdbc:mysql:///day13");

Ds.setuser ("root");

Ds.setpassword ("abc");

c3p0 connection pooling using configuration files

As long as the c3p0.properties or c3p0-config.xml name configuration fileis created under src ,c3p0 will automatically find .

Combopooleddatasource ds = new Combopooleddatasource ();

Auto-Find configuration file

In src/c3p0-config.xml

<c3p0-config>

<default-config>

<property name= "Driverclass" >com.mysql.jdbc.Driver</property>

<property name= "Jdbcurl" >jdbc:mysql:///day17</property>

<property name= "User" >root</property>

<property name= "Password" >abc</property>

</default-config>

</c3p0-config>

Things in Java and connection pooling