Two configuration methods for database connection pool

Two configuration methods for database connection pool

Database Connection Pool:
Allocates, manages, and releases database connections, allowing applications to repeat an existing database connection instead of reestablishing one; release database connections with idle time exceeding the maximum idle time to avoid database connection omissions due to the absence of released database connections;
Principles of database connection pool:
The basic idea of the connection pool is to store database connections as objects in the memory during system initialization. When users need to access the database, they do not create a new connection, instead, a created idle connection object is retrieved from the connection pool. After use, the user does not close the connection, but puts the connection back in the connection pool for access by the next request. The connection pool manages connection establishment and disconnection. You can also set connection pool parameters to control the initial connection count, upper and lower limit, maximum usage, and maximum idle time of each connection pool;
Category:
1. the Tomcat data source connection pool depends on the tomcat server of the web container. The DataSource object is obtained using java's JNDI;
2. C3P0 is an open-source JDBC Connection pool, which is released together with Hibernate in the lib directory, including the DataSources object of the Connection and Statement pool that implements the jdbc3 and jdbc2 extension specifications;
3. DBCP is a database connection pool that relies on the commons-pool Object pool mechanism. DBCP can be used directly in applications;
Method:

A traditional method:
Tomcat database connection pool Configuration:
1) first copy the jar package of the connected database to the lib folder under tomcat;
2) modify the tomcat/conf/context. xml file and add the data source configuration;

<Resource name="myshop" auth="Container" type="javax.sql.DataSource" maxActive="25" maxIdle="3" maxWait="10000"    username="sa" password="sa" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"    url="jdbc:sqlserver://localhost:1433;databaseName=yun4jbook"    />

First, use tomcat web management to configure the data source. The attributes in the resource configuration item are described as follows:

Name: name of the data source connection pool

Url: the URL to connect to the database

DriverClassName: Database Driver Class
Username: User Name of the database
Password: indicates the password of the database.

MaxActive: Maximum number of active connections

MaxIdle: Maximum number of idle connections

MaxWait: maximum wait time, in milliseconds
Type: name of the java class to which resource belongs
Auth: specifies who manages the data source

Container indicates that the Container manages
3) Open the project's web. xml and add it to the data source configuration;

<Resource-ref> <! -- The name attribute must be placed first --> <res-ref-name> myshop </res-ref-name> <res-type> javax. SQL. dataSource </res-type> <res-auth> Container </res-auth> </resource-ref>

4) Write dao code and use the java naming and Directory Interface Technology to obtain the data source and operate the database as a data source;

Package com. jinzhi. db; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. preparedStatement; import java. SQL. resultSet; import java. SQL. SQLException; import javax. naming. context; import javax. naming. initialContext; import javax. naming. namingException; import javax. SQL. dataSource;/*** 2017-5-9 * @ author lin **/public class DBManager {// Context object private static context Context; // data source priv Ate static DataSource ds; // database Connection object private Connection conn; // Database SQL statement precompiled object private PreparedStatement ps; // query result set protected ResultSet rs; /*** the connection pool is responsible for connecting to the database */static {try {context = new InitialContext (); ds = (DataSource) context. lookup ("java: comp/env/myshop");} catch (NamingException e) {// TODO Auto-generated catch blocke. printStackTrace () ;}{ System. out. println ("aa");}/*** get the connection object * @ return */pub Lic Connection getConn () {// try {// Class. forName ("com. microsoft. sqlserver. jdbc. SQLServerDriver "); // conn = DriverManager. getConnection ("jdbc: sqlserver: // localhost: 1433; dataBaseName = yun4jbook", "sa", "sa"); //} catch (ClassNotFoundException e) {// TODO Auto-generated catch block // e. printStackTrace (); //} catch (SQLException e) {// TODO Auto-generated catch block // e. printStackTrace (); //} try {conn = Ds. getConnection ();} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();} return conn;}/*** /*/public void closeAll () {try {if (null! = Rs) {rs. close () ;}if (null! = Ps) {ps. close () ;}if (null! = Conn) {conn. close () ;}} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace () ;}/ *** query result set * Note: you cannot close the database connection when querying the result set, otherwise, the connection * @ param SQL * @ param obj * @ return */public ResultSet query (String SQL, Object [] obj) {conn = getConn () cannot be obtained (); try {ps = conn. prepareStatement (SQL); if (obj! = Null & obj. length> 0) {for (int I = 0; I <obj. length; I ++) {ps. setObject (I + 1, obj [I]) ;}} rs = ps.exe cuteQuery ();} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();} return rs;}/*** data update Method * Note: after updating data, close the database connection * @ param SQL * @ param obj * @ return */public int update (String SQL, Object [] obj) {conn = getConn (); int count = 0; try {ps = conn. prepareStatement (SQL); if (obj! = Null & obj. length> 0) {for (int I = 0; I <obj. length; I ++) {ps. setObject (I + 1, obj [I]);} count = ps.exe cuteUpdate ();} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();} finally {closeAll ();} return count ;} /*** override the connection method * @ param conn * @ param SQL * @ param obj * @ return * @ throws SQLException */public ResultSet queryNotClose (Connection conn, String SQL, object [] obj) th Rows SQLException {ps = conn. prepareStatement (SQL); if (obj! = Null & obj. length> 0) {for (int I = 0; I <obj. length; I ++) {ps. setObject (I + 1, obj [I]) ;}} rs = ps.exe cuteQuery (); return rs ;}/ *** to ensure that the operation is in a transaction at a later stage, the update method is overwritten, and connection * @ param conn * @ param SQL * @ param obj * @ return * @ throws SQLException */public int updateNotClose (Connection conn, String SQL, object [] obj) throws SQLException {int count = 0; ps = conn. prepareStatement (SQL); if (obj! = Null & obj. length> 0) {for (int I = 0; I <obj. length; I ++) {ps. setObject (I + 1, obj [I]) ;}count = ps.exe cuteUpdate (); return count ;} /*** test method ** @ param args */public static void main (String [] args) {DBManager db = new DBManager (); System. out. println (db. getConn ());}}

Ii. Druid mode:
1. druid-0.2.15.jar package
2. Compile a xx. properties file.

The following is an oracle configuration file.

url = jdbc:oracle:thin:@127.0.0.1:1521/orclusername = detection1password = detection1initialSize = 5maxActive = 10minIdle = 3maxWait = 60000removeAbandoned = trueremoveAbandonedTimeout = 180timeBetweenEvictionRunsMillis = 60000minEvictableIdleTimeMillis = 300000testWhileIdle = true testOnBorrow = falsetestOnReturn = falsepoolPreparedStatements = truemaxPoolPreparedStatementPerConnectionSize = 50filters = stat

3. Connect to the database

Package testDruid; import java. io. inputStream; import java. SQL. connection; import java. SQL. preparedStatement; import java. SQL. resultSet; import java. SQL. SQLException; import java. text. dateFormat; import java. text. simpleDateFormat; import java. util. date; import java. util. properties; import javax. SQL. dataSource; import com. alibaba. druid. pool. druidDataSourceFactory; public class OracleDB {private static extends C E ds = null; private PreparedStatement ps; private ResultSet rs; private Connection conn; static {try {// read the configuration file oracle. propertiesInputStream in = OracleDB. class. getClassLoader (). getResourceAsStream ("oracle. properties "); Properties props = new Properties (); // load the configuration file props. load (in); // create the data source ds = druidperformancefactory. createDataSource (props);} catch (Exception ex) {ex. printStackTrace () ;}// obtain the database connection object public Conn. Ection openConnection () throws SQLException {return ds. getConnection () ;}// obtain the query result set public ResultSet find (String SQL, Object... objects) {try {conn = ds. getConnection (); ps = conn. prepareStatement (SQL); if (objects! = Null & objects. length> 0) {for (int I = 0; I <objects. length; I ++) {ps. setObject (I + 1, objects [I]) ;}} rs = ps.exe cuteQuery ();} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();} return rs;} // perform data update public int update (String SQL, Object... objects) {int count = 0; try {conn = ds. getConnection (); ps = conn. prepareStatement (SQL); if (objects! = Null & objects. length> 0) {for (int I = 0; I <objects. length; I ++) {ps. setObject (I + 1, objects [I]);} count = ps.exe cuteUpdate ();} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();} return count;} // close the database connection public void close () {try {if (rs! = Null) {rs. close () ;}if (ps! = Null) {ps. close () ;}if (conn! = Null) {conn. close () ;}} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();}}}