01. proxool屬于第三方驅動了,是以要是使用的話,要下載下傳它的jar包 http://sourceforge.net/projects/proxool,我 使用的是proxool-0.8.3.jar,是以這裡就以proxool-0.8.3.jar為例。下載下傳proxool-0.8.3.jar後就要把他放在/WEB-INF/lib下。這樣就能找到驅動了。
02. 編輯web.xml檔案,加入下面的語句:
<servlet>
<servlet-name>proxool</servlet-name>
<servlet-class>org.logicalcobwebs.proxool.admin.servlet.AdminServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>proxool</servlet-name>
<url-pattern>/Admin/proxool,./</url-pattern>
</servlet-mapping>
這樣你執行執行http://localhost:8080/proxool-test/Admin/proxool,./( proxool-test 是你的應用名稱 ),這樣你就可以監控 connection pooling 的狀态了。
03. proxool.xml檔案的編寫:
<?xml version="1.0" encoding="ISO-8859-1"?>
<!-- the proxool configuration can be embedded within your own application's.
Anything outside the "proxool" tag is ignored. -->
<something-else-entirely>
<proxool>
<alias>xml-db</alias>
<driver-url>jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=shop</driver-url>
<driver-class>com.microsoft.jdbc.sqlserver.SQLServerDriver</driver-class>
<driver-properties>
<property name="user" value="sa"/>
<property name="password" value="sa"/>
</driver-properties>
<!--minimum-connection-count>2</minimum-connection-count-->
<maximum-active-time>30000</maximum-active-time>
<maximum-connection-count>100</maximum-connection-count>
<maximum-connection-lifetime>15000</maximum-connection-lifetime> <!-- 5 hours -->
</proxool>
</something-else-entirely>
04. 要使用這個驅動就要用到2個類,DBConnectionPoolManager.java和Conn.java,Conn.java調用DBConnectionPoolManager.java,
DBConnectionPoolManager.java代碼如下:
import java.sql.*;
import org.logicalcobwebs.proxool.configuration.JAXPConfigurator;
import java.io.*;
public class DBConnectionPoolManager {
private static DBConnectionPoolManager dbcpm = null;
private Connection con = null;
private DBConnectionPoolManager() {
InputStream is = getClass().getResourceAsStream("/proxool.xml");
try{
JAXPConfigurator.configure(new InputStreamReader(is), false);
System.out.println("Configuration file(proxool.xml) has been loaded !");
}catch(Exception e){
System.out.println("Load Configuration failed ! " + e.getMessage());
}finally{
try{
is.close();
}catch(Exception ex){}
}
}
static synchronized public DBConnectionPoolManager getInstance(){
if(null==dbcpm)
dbcpm =new DBConnectionPoolManager();
return dbcpm;
}
public Connection getConnection(){
try{
con = DriverManager.getConnection("proxool.xml-db");
}catch(Exception e){
System.out.println("Connection failed ! "+e.getMessage());
}
return con;
}
}
Conn.java代碼如下:
import java.sql.*;
import com.dcon.DBConnectionPoolManager;
public class Conn
{
private DBConnectionPoolManager dbc = null;
private Connection con = null;
private PreparedStatement ps = null;
private Statement stmt = null;
private ResultSet rs = null;
public Conn()
{
dbc = DBConnectionPoolManager.getInstance();
}
public PreparedStatement prepareStatement(String sql) throws SQLException
{
try
{
con = dbc.getConnection();
ps = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
return ps;
}
catch (Exception e)
{
e.printStackTrace();
System.out.println("psDBA exception !");
return null;
}
}
public Statement createStatement() throws SQLException
{
try
{
con = dbc.getConnection();
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
return stmt;
}
catch (Exception e)
{
e.printStackTrace();
System.out.println("stDBA exception !");
return null;
}
}
public ResultSet resultSet(String sql) throws SQLException
{
rs = createStatement().executeQuery(sql);
return rs;
}
public void close()
{
try
{
if(rs!=null) {rs.close();}
if(stmt!=null){stmt.close();}
if(ps!=null) {ps.close();}
if (con != null)
{
con.close();
}
}
catch (Exception e)
{
}
}
}
使用方法就是使用Conn.java的prepareStatement和createStatement方法。
03. 使用prepareStatement和createStatement方法一般就是下面這種情況:
PreparedStatement ps=dbc.prepareStatement("select * from tt");
rs=ps.executeQuery();
while(rs.next())
{
Test test=new Test();
test.setId(rs.getInt("id"));
test.setName(rs.getString("name"));
}
原來的方法是:
Statement st=con.createStatement();
rs=st.executeQuery("select * from tt");
while(rs.next())
{
Test test=new Test();
test.setId(rs.getInt("id"));
test.setName(rs.getString("name"));
}
大家可以參考一下。
05. 慎用或盡量不要用微軟自帶的sqlserver的jdbc驅動。在商業應用中,很少有公司真正實用微軟的那個什麼com.microsoft.jdbc.sqlserver.SQLServerDriver。
其實發現微軟驅動的不足,也是很偶然的機會。在一次移植應用中,系統報告了[Microsoft][SQLServer JDBC Driver]ResultSet can not re-read row data for column之類的錯誤。為了解決這個錯誤,跟蹤程式忙了大半天,也沒有查出問題所在。隻能确定在在rs.getString("XXX")的時候必然會出現錯誤(rs為ResultSet對象)。那麼現在就模拟以下當時的環境:
資料庫表:TestTable
表字段及類型:
guid char(38)
title varchar(100)
content Text
username varchar(20)
查詢sql語句:
select guid,username,content,title from TestTable where····
java代碼:
rs.getString("guid");
rs.getBinaryStream("content");
rs.getString("username");
-----------------------------------------------------------
上面那段代碼,很不榮幸是。使用微軟的驅動,必然會報錯(就是上面說的那個錯誤)。如果你跟蹤的話,必然是這一行:rs.getString("username")抛出錯誤。
那麼,如果你把rs.getString("username")和rs.getBinaryStream("content");位置互換以下,會怎麼樣呢。結果就是可以正常運作。
為什麼會出現這種情況呢,因為微軟的驅動,在包含了blob或clob類型(就是Image和Text類型)的字段。那麼就必須按照select順序查詢,且不支援重複查詢。