不用spring框架,只使用jdbcTemplate + proxool

jdbcTemplate + proxool

只使用jdbcTemplate, 连接池用proxool.

1.下载地址
proxool:
http://proxool.sourceforge.net/



2.导入的包
asm-3.3.1.jar
cglib-2.2.2.jar
commons-logging-1.1.1.jar
log4j-1.2.16.jar
mysql-connector-java-5.1.8-bin.jar
org.springframework.beans-3.1.1.RELEASE.jar
org.springframework.core-3.1.1.RELEASE.jar
org.springframework.jdbc-3.1.1.RELEASE.jar
org.springframework.transaction-3.1.1.RELEASE.jar
proxool-0.9.1.jar
proxool-cglib.jar

3.配置文件 /WEB-INF/proxool.xml
<?xml version="1.0" encoding="UTF-8"?>
<something-else-entirely>
<proxool>
<alias>dbname</alias>
<driver-url>jdbc:mysql://118.186.210.242:447/wucaiju?user=root&amp;password=root&amp;useUnicode=true&amp;characterEncoding=utf-8</driver-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<driver-properties>
<property name="user" value="root" />
<property name="password" value="root" />
</driver-properties>
<maximum-connection-count>100</maximum-connection-count>
<minimum-connection-count>10</minimum-connection-count>
<house-keeping-sleep-time>30000</house-keeping-sleep-time>
<maximum-new-connections>10</maximum-new-connections>
<prototype-count>5</prototype-count>
</proxool>
</something-else-entirely>

4.web.xml

<servlet>
<servlet-name>ServletConfigurator</servlet-name>
<servlet-class>
org.logicalcobwebs.proxool.configuration.ServletConfigurator
</servlet-class>
<init-param>
<param-name>xmlFile</param-name>
<param-value>WEB-INF/proxool.xml</param-value>
</init-param>
<load-on-startup>1</load-on-startup>
</servlet>
<servlet>
<servlet-name>datasource_dbname</servlet-name>
<servlet-class>org.logicalcobwebs.proxool.admin.servlet.AdminServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>datasource_dbname</servlet-name>
<url-pattern>/datasource_dbname</url-pattern>
</servlet-mapping>
<!-- 配置受保护域，只有Tomcat管理员才能察看连接池的信息 -->
<security-constraint>
<web-resource-collection>
<web-resource-name> proxool </web-resource-name>
<url-pattern>/datasource_dbname</url-pattern>
</web-resource-collection>
<auth-constraint>
<role-name>manager</role-name>
</auth-constraint>
</security-constraint>
<login-config>
<auth-method>BASIC</auth-method>
<realm-name>proxool manager Application</realm-name>
</login-config>
<security-role>
<description>
The role that is required to log in to the Manager
Application
</description>
<role-name> manager </role-name>
</security-role>


5.测试类
public class TestDao {

public void test()throws Exception{
Context context = new InitialContext();
//DataSource ds = (DataSource)context.lookup("java:/comp/env/jdbc/mysql");
//DataSource ds = (DataSource)context.lookup("proxool.dbname");
DataSource ds = new ProxoolDataSource("dbname") ; //此处是alias的名称,而不是"proxool.别名"

JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
jdbcTemplate.afterPropertiesSet();
System.out.println(jdbcTemplate);

String sql = "select * from t_pic";
List list = jdbcTemplate.queryForList(sql);

for(int i=0; i<list.size(); i++){
Map map = (Map)list.get(i);
System.out.println(map.get("createTime"));
}
}
}


6.直接jdbc用proxool

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.logicalcobwebs.proxool.ProxoolException;
import org.logicalcobwebs.proxool.ProxoolFacade;
import org.logicalcobwebs.proxool.admin.SnapshotIF;
import org.logicalcobwebs.proxool.configuration.JAXPConfigurator;

public class PoolManager{

private static int activeCount = 0 ;


public PoolManager(){

}
/**
* 获取连接
* getConnection
* @param name
* @return
*/
public Connection getConnection() {
try {
Class.forName("org.logicalcobwebs.proxool.ProxoolDriver" ); //proxool驱动类
Connection conn = DriverManager.getConnection("proxool.dbname" ); // 注意,此处用的是proxool + 别名
//此处的datasource1是在proxool.xml中配置的连接池别名，当然根据需要可以用datasource2
showSnapshotInfo();

return conn;
}catch (Exception ex){
ex.printStackTrace();
}
return null ;
}
/**
* 此方法可以得到连接池的信息
* showSnapshotInfo
*/
private void showSnapshotInfo(){
try {
SnapshotIF snapshot = ProxoolFacade.getSnapshot("dbname" , true );
int curActiveCount=snapshot.getActiveConnectionCount(); //获得活动连接数
int availableCount=snapshot.getAvailableConnectionCount(); //获得可得到的连接数
int maxCount=snapshot.getMaximumConnectionCount() ; //获得总连接数
if (curActiveCount!=activeCount) //当活动连接数变化时输出的信息
{
System.out.println("活动连接数:" +curActiveCount+ "(active) 可得到的连接数:" +availableCount+ "(available) 总连接数:" +maxCount+ "(max)" );
activeCount=curActiveCount;
}
}catch (ProxoolException e){
e.printStackTrace();
}
}
/**
* 获取连接
* getConnection
* @param name
* @return
*/
public Connection getConnection(String name){
return getConnection();
}
/**
* 释放连接
* freeConnection
* @param conn
*/
public void freeConnection(Connection conn){
if (conn!= null ){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 释放连接
* freeConnection
* @param name
* @param con
*/
public void freeConnection (String name,Connection con){
freeConnection(con);
}

public void getQuery() {
try {
Connection conn = getConnection();
if (conn != null ){
Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery("select * from t_pic" );
int c = rs.getMetaData().getColumnCount();
while (rs.next()){
System.out.println();
for ( int i= 1 ;i<=c;i++){
System.out.print(rs.getObject(i));
}
}
rs.close();
}
freeConnection(conn);
} catch (SQLException e) {
e.printStackTrace();
}

}

public static void main(String args[]){
PoolManager pm = new PoolManager();
pm.getQuery();
}

}