[java learning summary] database connection pool and operation tool class

Shi Nian 2021-10-14 09:05:16
java learning summary database connection


Compared with the original database operation , Use connection pool technology and database operation tool class , Can often be more efficient 、 More concise , This article mainly introduces the connection pool tool c3po Package and database operation tool class commons-dbutils.

One 、 The original database operation mode

The original database operation is as follows , The process of establishing a database object is more complex ,IP、 port 、 account number 、 The password needs to be defined in the code , Each execution requires a connection , Close the connection .

 try {
// Get the database driver 
Class.forName("com.mysql.cj.jdbc.Driver");
// Get database connection object 
conn = DriverManager.getConnection(
"jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&useSSL=false&serverTimezone=UTC", "root", "123456");
// Definition sql
String sql = "select * from User_test";
// Obtain the database execution object through the database connection object 
Statement statment = conn.createStatement();
// perform sql, Store the return value in ResultSet in 
ResultSet res_set = statment.executeQuery(sql);
// Traverse ResultSet object 
while(res_set.next()){
String id_str = res_set.getString("id");
String username = res_set.getString("username");
String password = res_set.getString("password");
String newline = id_str+","+username+","+password+"\n";
System.out.println(newline);
FileUtils.write(file, newline, "utf8", true);
}
} catch (Exception e) {
e.printStackTrace();
}
finally {
// Close database connection 
 conn.close();
}

 

Two 、 Database connection pool (c3po)

The database connection pool is a container , Container for database connection , When the system is initialized , The container is created , Some connection objects will be requested in the container , When the user comes to access the database , Get the connection object from the container , After the user visits , The connection object is returned to the container .

Compared with the original connection , Database connection pool technology can save resources 、 The advantage of efficient user access . Common connection pooling technologies are c3po、Druid, This article describes c3po Implementation process .

With maven Project as an example

1. stay pom Add the corresponding dependency to the file

<dependency>
<groupId>c3p0</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.1.2</version>
</dependency>

2、 Define configuration file

Name and path : src/main/resources/c3p0-config.xml 

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!-- The default configuration , It can only appear once -->
<!-- according to name Get the configuration of the corresponding configuration <named-config name="mysql"> -->
<default-config>
<!-- To configure JDBC Four basic attributes -->
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&amp;useSSL=false&amp;serverTimezone=UTC</property>
<property name="user">root</property>
<property name="password">123456</property>
<!-- When the connections in the connection pool are exhausted c3p0 Number of connections acquired at one time .Default: 3 -->
<property name="acquireIncrement">3</property>
<!-- Number of connections when initializing the database connection pool -->
<property name="initialPoolSize">5</property>
<!-- The minimum number of database connections in the database connection pool -->
<property name="minPoolSize">2</property>
<!-- The maximum number of database connections in the database connection pool -->
<property name="maxPoolSize">10</property>
</default-config>
</c3p0-config>

3、 Create database connection pool object ComboPooledDataSource And get the database connection getConnection

// get c3p0 Connect pool objects 
private static ComboPooledDataSource ds = new ComboPooledDataSource("mysql");
// Get the database connection object 
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
// get c3p0 Connect pool objects 
public static DataSource getDataSource() {
return ds;
}

The database connection pool tool only manages database connection objects , It will not affect the subsequent operation , The simplification of subsequent operations can be realized with the help of database operation tool classes , such as commons-dbutils.

3、 ... and 、 Database operation tool class (commons-dbutils)

commons-dbutils It's open source JDBC Tool library , It's right JDBC Simple encapsulation , The cost of learning is extremely low , And use dbutils Can greatly simplify JDBC The amount of coding work , At the same time, it will not affect the performance of the program .

add to pom File dependency

<dependency>
<groupId>commons-dbutils</groupId>
<artifactId>commons-dbutils</artifactId>
<version>1.7</version>
</dependency>

Basic operation

// commons-dbutils The core api, The database connection object needs to be passed in during construction ( It can be the connection object obtained from the connection pool in the above )
QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
// Definition sql
String sql = "select * from user_test";
// perform sql, return DbUser Class ( Note that here is as like as two peas model class , The query results can be mapped one by one )
List list = (List)runner.query(sql, new BeanListHandler(DbUser.class));
DbUser object 
package com.lxs.db2excel;
public class DbUser2 {
private String uid;
private String username;
private String password;
public String getUid() {
return uid;
}
public void setUid(String uid) {
this.uid = uid;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "DbUser [uid=" + uid + ", username=" + username + ", password=" + password + "]";
}
}

 

版权声明
本文为[Shi Nian]所创,转载请带上原文链接,感谢
https://javamana.com/2021/10/20211002155636235d.html

  1. Usage relations and differences of count (1), count (*) and count (a field) in MySQL
  2. 2021 Ali Java advanced interview questions sharing, Java Architect interview materials
  3. Mybatis - dynamic SQL statement - if usage - MySQL series learning notes
  4. [go to Dachang series] deeply understand the use of where 1 = 1 in MySQL
  5. [secret room escape game theme ranking list] Based on spring MVC + Spring + mybatis
  6. Redis log: the killer mace of fearless downtime and rapid recovery
  7. 5 minutes to build redis cluster mode and sentinel mode with docker
  8. Java小白入门200例106之遍历ArrayList的几种方式
  9. Java小白入门200例105之Java ArrayList类
  10. Java小白入门200例104之JDK自带记录日志类logging
  11. Practice of high availability architecture of Tongcheng travel network based on rocketmq
  12. Chapter 9 - Linux learning will - file archiving and compression tar --- zip
  13. Java小白入門200例104之JDK自帶記錄日志類logging
  14. JDK avec journalisation de classe dans 200 cas 104
  15. Java ArrayList Class for Introduction to Java LITTLE WHITE 200 example 105
  16. Plusieurs façons de traverser ArrayList à partir de 200 exemples 106
  17. Provectus / Kafka UI: open source Apache Kafka's Web GUI Graphical interface management tool
  18. Design pattern series: Singleton pattern
  19. Java小白入門200例105之Java ArrayList類
  20. Understanding Java record types
  21. Five load balancing algorithms implemented in Java
  22. Data structure must be an example to understand dynamic programming (with universal Python code)
  23. The idea and implementation of recursion in data structure (Python)
  24. The idea and implementation of linked list (Python)
  25. Data structure must be queue and double ended queue (Python)
  26. Idea and implementation of data structure must be able stack (Python)
  27. Data structure | time complexity (with video explanation)
  28. 20 flutter libraries you should know
  29. Case sharing: Online failure caused by Dubbo 2.7.12 bug
  30. Open source | didi open source, general functional components for Java authentication, authentication, management and task scheduling
  31. Flutter multi engine supports platformview and thread merging solution
  32. In depth understanding of netty: viewing netty traffic control from occasional downtime
  33. Spring AOP internal skill cultivation
  34. Interviewer: is Tomcat a symbolic parent delegation mechanism?
  35. Expérimentez la première tablette de consommation Linux. La puce et le système d'origine sont tous faits maison
  36. 2021 summary of the latest Java common open source libraries, Java interview handwritten code
  37. 2021 latest Java factory interview true questions, Kafka introduction video
  38. 01 javase - première connaissance de l'installation de Java et de l'environnement de développement
  39. The sales volume in September broke the record: Weilai and Xiaopeng both exceeded 10000, with an ideal month on month decrease of 24.7%
  40. Required for interview: HBase block cache
  41. Redis core principle and practice: implementation principle of hash type and dictionary structure
  42. MySQL deep dive: analyzing performance schema memory management
  43. Redis cache: kill interviewer 25 asked
  44. Solution of Chinese garbled code in idea integrated Tomcat console
  45. 2021 the latest java development interview, 46 interview questions take you to understand the advanced Java interview
  46. 2021 spring recruitment bat interview questions are explained in detail and explained clearly
  47. 2021 latest java developer learning route, 2021 latest Ali Java advanced interview questions and answers
  48. 2021 summary of the latest Java common open source libraries, the top java development tools necessary for developers
  49. Java communique avec les capteurs industriels via socket et DTu, RTU
  50. Does Hadoop have to run on Linux? (the root cause is the permission opening advantage of the operating system Linux)
  51. Java communique avec les capteurs industriels via socket et DTu, RTU
  52. 2021 summary of the latest java knowledge system. It took 8 days to finally understand JVM tuning
  53. Introduction au tissu de service Azure
  54. 【.Net vs Java? 】 先来看一下Java和C#的数据类型区别。
  55. 2021 summary of the latest java knowledge system, will you only have the technology of junior engineers
  56. 【.Net vs Java? 】 先來看一下Java和C#的數據類型區別。
  57. [.NET vs Java?] regardez d'abord les différences de type de données entre Java et C #.
  58. 2021 latest Java interview questions and answers, Java interviewer's favorite garbage collection mechanism
  59. 2021 latest Java interview written test, distributed ID generation algorithm in Seata project of source code analysis
  60. 2021 latest Java experience sharing, what java knowledge do we need to learn