java-jdbc

JDBC

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

String driverClassName = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false";
String user = "user01";
String password = "user01";

Class.forName(driverClassName).newInstance();
Connection con = DriverManager.getConnection(url, user, password);

// 插入
String sql = "insert into user(name, password, age) values (?, ?, ?) ;";
PreparedStatement pst = con.prepareStatement(sql);
pst.setString(1, name);
pst.setString(2, password);
pst.setInt(3, age);
num = pst.executeUpdate();
pst.close();
con.close();

// 查询
String sql = "select count(*) as count from table_user ;";
PreparedStatement pst = secondCon.prepareStatement(sql);
ResultSet rs = pst.executeQuery();
while (rs.next()) {
// TODO
}
rs.close();
pst.close();
con.close();

jdbc-pool

Tomcat 在 7.0 以前的版本都是使用 commons-dbcp 做为连接池的实现,但是 dbcp 饱受诟病,原因有:

1
2
3
4
5
dbcp 是单线程的,为了保证线程安全会锁整个连接池
dbcp 性能不佳
dbcp 太复杂,超过 60 个类
dbcp 使用静态接口,在 JDK 1.6 编译有问题
dbcp 发展滞后

因此很多人会选择一些第三方的连接池组件,例如 c3p0 , bonecp, druid (@wenshao ) 等。

为此,Tomcat 从 7.0 开始引入一个新的模块:Tomcat jdbc pool

1
2
3
4
5
6
7
8
9
tomcat jdbc pool 近乎兼容 dbcp ,性能更高
异步方式获取连接
tomcat jdbc pool 是 tomcat 的一个模块,基于 tomcat JULI,使用 Tomcat 的日志框架
使用 javax.sql.PooledConnection 接口获取连接
支持高并发应用环境
超简单,核心文件只有8个,比 c3p0 还
更好的空闲连接处理机制
支持 JMX
支持 XA Connection

tomcat jdbc pool 的优点远不止这些,详情请看 官网

tomcat jdbc pool 可在 Tomcat 中直接使用,也可以在独立的应用中使用。

在独立的应用中使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;

public class SimplePOJOExample {

public static void main(String[] args) throws Exception {
PoolProperties p = new PoolProperties();
p.setUrl("jdbc:mysql://localhost:3306/mysql");
p.setDriverClassName("com.mysql.jdbc.Driver");
p.setUsername("root");
p.setPassword("password");
p.setJmxEnabled(true);
p.setTestWhileIdle(false);
p.setTestOnBorrow(true);
p.setValidationQuery("SELECT 1");
p.setTestOnReturn(false);
p.setValidationInterval(30000);
p.setTimeBetweenEvictionRunsMillis(30000);
p.setMaxActive(100);
p.setInitialSize(10);
p.setMaxWait(10000);
p.setRemoveAbandonedTimeout(60);
p.setMinEvictableIdleTimeMillis(30000);
p.setMinIdle(10);
p.setLogAbandoned(true);
p.setRemoveAbandoned(true);
p.setJdbcInterceptors(
"org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+
"org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
DataSource datasource = new DataSource();
datasource.setPoolProperties(p);

Connection con = null;
try {
con = datasource.getConnection();
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("select * from user");
int cnt = 1;
while (rs.next()) {
System.out.println((cnt++)+". Host:" +rs.getString("Host")+
" User:"+rs.getString("User")+" Password:"+rs.getString("Password"));
}
rs.close();
st.close();
} finally {
if (con!=null) try {con.close();}catch (Exception ignore) {}
}
}

}

异步获取连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Connection con = null;
try {
Future<Connection> future = datasource.getConnectionAsync();
while (!future.isDone()) {
System.out.println("Connection is not yet available. Do some background work");
try {
Thread.sleep(100); //simulate work
}catch (InterruptedException x) {
Thread.currentThread().interrupted();
}
}
con = future.get(); //should return instantly
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("select * from user");

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<dependency>  
<groupId>org.apache.tomcat</groupId>
<artifactId>tomcat-jdbc</artifactId>
<version>7.0.29</version>
</dependency>
<dependency>
<groupId>org.apache.tomcat</groupId>
<artifactId>tomcat-juli</artifactId>
<version>7.0.29</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.tomcat/tomcat-jdbc -->
<dependency>
<groupId>org.apache.tomcat</groupId>
<artifactId>tomcat-jdbc</artifactId>
<version>8.5.15</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.tomcat/tomcat-juli -->
<dependency>
<groupId>org.apache.tomcat</groupId>
<artifactId>tomcat-juli</artifactId>
<version>8.5.15</version>
</dependency>

References

JDBC
[1] http://www.yiibai.com/jdbc/jdbc_quick_guide.html
[2] http://www.cnblogs.com/DreamDrive/p/5757693.html
[3] http://blog.csdn.net/whucyl/article/details/20838079
[4] http://javastudyeye.iteye.com/blog/835448

jdbc-pool
[1] https://stackoverflow.com/questions/24559468/neo4j-jdbc-connection-pool
[2] http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html
[3] https://www.oschina.net/question/12_36910
[4] https://github.com/alibaba/druid