Shulou(Shulou.com)06/01 Report--

1. JDBC driver

Loading of JDBC driver:

1. Load through the container:

For Java applications with containers, you can directly place the corresponding driver jar package in the container's lib directory. For example, copy the driver to the lib subdirectory of tomcat in the web application that is used as a container in Tomcat.

2. Load the application at run time:

If you want the application to load the corresponding driver itself, you need maven to specify the classpath for searching the driver jar when typing the jar/war package for the application. Please refer to:

Https://maven.apache.org/shared/maven-archiver/examples/classpath.html

Https://www.cnblogs.com/snaildev/p/8341610.html

Example 1 (there is a lib directory under the executable jar package to store dependent jar packages):

Image org.apache.maven.plugins maven-jar-plugin 2.4 true lib/ some.package.MainClass

When the business loads the JDBC driver itself, you need to load the driver by performing the following operations before the application uses JDBC:

Class.forName ("com.mysql.jdbc.Driver"); / / Then the following begin to use jdbc

Manage connections to the database through connection pooling (a type of DataSource)

At present, the commonly used connection pool is Druid or Hikari, which are described as follows:

1. Druid connection pool:

Maven dependencies:

Com.alibaba druid 1.1.11

Sample code:

DruidDataSource createSource (String name, String jdbcUrl, String userName, String password, maxActive, minIdle, maxWait, scanInterval, minActiveTime) {DruidDataSource src = DruidDataSource (); src.setName (name); src.setUrl (jdbcUrl); src.setUsername (userName); src.setPassword (password); src.setDriverClassName (); src.setInitialSize (minIdle); src.setMaxActive (maxActive); src.setMinIdle (minIdle); src.setMaxWait (maxWait); src.setTimeBetweenEvictionRunsMillis (scanInterval) Src.setMinEvictableIdleTimeMillis (minActiveTime); src.setTestWhileIdle (); src.setTestOnBorrow (); src.setTestOnReturn (); src.setPoolPreparedStatements (); src.setMaxPoolPreparedStatementPerConnectionSize (); src.setValidationQuery (); src.setRemoveAbandoned (); src.setRemoveAbandonedTimeout (); src.setKeepAlive (); if (! isDataSourceOk (src)) {LOGGER.error ("Data source" + name + "test failed");} return src } private static boolean isDataSourceOk (DataSource source) {try (Connection connection = source.getConnection (); PreparedStatement stmt = connection.prepareStatement ("select 1"); ResultSet resultSet = stmt.executeQuery () {resultSet.next (); return true;} catch (Throwable e) {return false;}}

2. Hikari connection pool

Maven dependencies:

Com.zaxxer HikariCP 3.2.0

Sample code:

HikariDataSource createSource (String name, String jdbcUrl, String userName, String password, connectionTimeout, idleTimeout, maxLifeTime, poolSize) {HikariDataSource src = HikariDataSource (); src.setPoolName (name); src.setJdbcUrl (jdbcUrl); src.setUsername (userName); src.setPassword (password); src.setDriverClassName (); src.setConnectionTimeout (connectionTimeout); src.setIdleTimeout (idleTimeout); src.setMaxLifetime (maxLifeTime); src.setMaximumPoolSize (poolSize) If (! isDataSourceOk (src)) {LOGGER.error ("Data source" + name + "test failed");} return src;}

Third, deal with connections

1. First obtain the connection through the connection pool, for example:

Try (Connection getConnection () SQLException {assert DataSourceManager.getDataSource ()! = null; return DataSourceManager.getDataSource () .getConnection ();}

2. The SQL request submission method for the connection

After obtaining the Connection object from the connection pool (no round DruidDataSource or HirakiDataSource), the default submission method is automatic submission, that is, true must be returned by calling connection.getAutoCommit () at this time

After the Connection object returns the connection pool, the next time you apply for a Connection from the connection pool, the default submission method is automatic submission. You need to know when to use autocommit and when it is appropriate to submit manually.

The overview is to see what is done with the same Connection object between applying for the Connection object and returning the Connection object:

(1) only read operations, no write operations: auto-submit

(2) write to a single record of a single table only once: with automatic submission

An example of autocommitted code is as follows

Try (Connection connection = ()) {block.apply (connection); / / Do all query or only one update for only one record}

(3) write to multiple records of the same table, or write to records of different tables separately: determine whether transactionality needs to be supported according to whether rollback and performance requirements are required; if you want to support physical properties, you must submit it manually.

Examples of manually submitted operations:

Try (Connection connection = ()) {boolean success = false; try {T t = block.apply (connection); / / Use this connection process one transaction doCommit (connection); success = true; return t;} finally {if (! success) {doRollback (connection) / / If possible, support rollback when failed} the submission mode of connection may have been modified in the block of the above complex process. In order to maintain code compatibility, the designs of the above doCommit () and doRollback () are as follows: public static void doCommit (Connection connection) throws SQLException {if (! connection.getAutoCommit ()) {connection.commit () } private static void doRollback (Connection connection) throws SQLException {if (SUPPORT_ROLLBACK & &! connection.getAutoCommit ()) {connection.rollback ();}}

(4) because of the time-consuming operation and a large amount of data, the physical properties and rollback depending on the database can no longer be achieved. In this case, it should be submitted multiple times, and the application layer should provide rollback.

Examples are as follows:

Try (Connection connection = ()) {boolean success = false; try {block1.accept (connection); doCommit (connection); block2.accept (connection); doCommit (connection); success = true;} finally {if (! success) {block2.clear (connection); block1.clear (connection);}

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.