本文介绍传统JDBC连接方式及其存在的问题，并详细讲解如何使用连接池提高系统性能，包括连接池的实现原理和具体代码示例。
传统的JDBC连接(oracle)：
String driver = “oracle.jdbc.driver.OracleDriver”;
String url = "jdbc:oracle:thin:@192.16.1.101:1521:ora9";
String username="xxf";
String password="xxf";
String sql="insert into users (username,password) values(?,?)";
try{
Class.forName(driver);
Connection conn=DriverManager.getConnection(url,username,password);
PreparedStateMent pstmt=conn.prepareStatement(sql);
pstmt.setString(1,"xiangxiaofeng");
pstmt.setString(2,"tmac");
pstmt.excuteUpdate();
pstmt.close();
conn.close();
}catch(ClassNotFoundException e){
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}
使用JDBC数据库连接池，系统性能更好，对Connection对象在内存当中进行有效的缓冲。
连接池的是实现：
1. dbpooling.properties文件
driverClassName=oracle.jdbc.driver.OracleDriver
username=xxf
password=xxf
url=jdbc:oracle:thin:@192.168.1.20:1521:ora9
poolSize=10
2.ConnectionPool.java
public class ConnectionPool {
private Vector<Connection> pool;
private String url;
private String username;
private String password;
private String driverClassName;
/**
* 连接池的大小，也就是连接池中有多少个数据库连接。
*/
private int poolSize = 1;
private static ConnectionPool instance = null;
/**
* 私有的构造方法，禁止外部创建本类的对象，要想获得本类的对象，通过<code>getIstance</code>方法。
* 使用了设计模式中的单子模式。
*/
private ConnectionPool() {
init();
}
/**
* 连接池初始化方法，读取属性文件的内容 建立连接池中的初始连接
*/
private void init() {
pool = new Vector<Connection>(poolSize);
readConfig();
addConnection();
}
/**
* 返回连接到连接池中
*/
public synchronized void release(Connection conn) {
pool.add(conn);
}
/**
* 关闭连接池中的所有数据库连接
*/
public synchronized void closePool() {
for (int i = 0; i < pool.size(); i++) {
try {
((Connection) pool.get(i)).close();
} catch (SQLException e) {
e.printStackTrace();
}
pool.remove(i);
}
}
/**
* 返回当前连接池的一个对象
*/
public static ConnectionPool getInstance() {
if (instance == null) {
instance = new ConnectionPool();
}
return instance;
}
/**
* 返回连接池中的一个数据库连接
*/
public synchronized Connection getConnection() {
if (pool.size() > 0) {
Connection conn = pool.get(0);
pool.remove(conn);
return conn;
} else {
return null;
}
}
/**
* 在连接池中创建初始设置的的数据库连接
*/
private void addConnection() {
Connection conn = null;
for (int i = 0; i < poolSize; i++) {
try {
Class.forName(driverClassName);
conn = java.sql.DriverManager.getConnection(url, username, password);
pool.add(conn);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 读取设置连接池的属性文件
*/
private void readConfig() {
try {
String path = System.getProperty("user.dir") + "//dbpool.properties";
FileInputStream is = new FileInputStream(path);
Properties props = new Properties();
props.load(is);
this.driverClassName = props.getProperty("driverClassName");
this.username = props.getProperty("username");
this.password = props.getProperty("password");
this.url = props.getProperty("url");
this.poolSize = Integer.parseInt(props.getProperty("poolSize"));
} catch (Exception e) {
e.printStackTrace();
System.err.println("读取属性文件出错. ");
}
}
}
3.ConnectionPoolTest.java文件
public class ConnectionPoolTest {
public static void main(String[] args) throws Exception {
String sql = "select id,name,phone from guestbook";
long start = System.currentTimeMillis();
ConnectionPool pool = null;
for (int i = 0; i < 100; i++) {
pool = ConnectionPool.getInstance();
Connection conn = pool.getConnection();
Statement stmt = createStatement();
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
}
rs.close();
stmt.close();
pool.release(conn);
}
pool.closePool();
System.out.println("经过100次的循环调用，使用连接池花费的时间:" +
(System.currentTimeMillis() - start)+"ms/n");
String hostName = "192.168.1.20";
String driverClass = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@" + hostName + ":1521:ora9";
String user = "xxf";
String password = "xxf";
start = System.currentTimeMillis();
for (int i = 0; i < 100; i++) {
Class.forName(driverClass);
Connection conn = DriverManager.getConnection(url, user, password);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
}
rs.close();
stmt.close();
conn.close();
}
System.out.println("经过100次的循环调用，不使用连接池花费的时间:" +
(System.currentTimeMillis() - start) + "ms");
}
}
连接池的对象的获取是由数据源对象来获取的，数据源是来管理多个连接池的对象
下面为Tomcat的连接池和数据库，
1. 配置连接池，修改context.xml
<Context reloadable="true">
<WatchedResource>WEB-INF/web.xml</WatchedResource>
<Resource name="jdbc/oracleds" auth="Container" type="javax.sql.DataSource"
maxIdle="30" maxWait="10000" username="xiangxiaofeng" driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@192.168.1.101:1521:ora9"/>
</Context>
2.
String sql="insert into users (username,password) values(?,?)";
try{
Context context = new InitialContext() //JNDI的内容
DataSource ds= context.lookup("java:/comp/env/jdbc/oracleds");
Connection conn=ds.getConnection();
PreparedStateMent pstmt=conn.prepareStatement(sql);
pstmt.setString(1,"xiangxiaofeng");
pstmt.setString(2,"tmac");
pstmt.excuteUpdate();
pstmt.close();
conn.close();
}catch(NamingException e){
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}
--------------------------------------------------------------------------------------------------------------------------------
拓展：使用dbutils组件
CommonsdbUtils中主要有下面几个类
*DbUtils类
*ResultSetHandler接口
*MapListHandler类
*BeanListHandler类
*QueryRunner类
public static void main(String[] args){
String sql="select * from db_user order by id desc";
try{
Context context= new InitialContext();
DataSource ds = (DataSource)context.lookup("java:/comp/env/jdbc/oracleds");
QueryRunner qr= new QueryRunner();
List results =(List)qr.query(sql,new(BeanListHandler(javabean对象名.class)));
Jbean jb=null;
for(int i=0;i<results.size();i++){
jb=(Jbean)results.get(i);
System.out.println("id"+jb.getId());
System.out.println("name"+jb.getIName());
System.out.println("password"+jb.getPassword());
}
}catch(Exception e){
e.printStackTrace()；
}
}
}
扫一扫
910
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
