1. 复制 JDBC for SQL Server 2005 的 sqljdbc.jar 到 %JBossDir%\server\default\lib
2. 在 %JBossDir%\server\default\deploy 下编写 appname-mssql2005-ds.xml ,内容如下:
3. 编写 Java 方法获得连接
4. 编写 DAO
注意 finally 段中务必保证 RecordSet 、Statement 、Connection 已经依次关闭。由于 SelectMethod=cursor 的作用,不需要担心一个业务逻辑中使用多个 DAO 方法而使得后面的 DAO 方法获取不到 Connection 。
5. 部署应用程序
6. 启动 JBoss
2. 在 %JBossDir%\server\default\deploy 下编写 appname-mssql2005-ds.xml ,内容如下:
<?xml version="1.0" encoding="UTF-8"?>
<!-- ===================================================================== -->
<!-- -->
<!-- JBoss Server Configuration -->
<!-- -->
<!-- ===================================================================== -->
<datasources>
<local-tx-datasource>
<!-- The jndi name of the DataSource, it is prefixed with java:/ -->
<!-- Datasources are not available outside the virtual machine -->
<jndi-name>jdbc/SomeName</jndi-name>
<!-- for in-process persistent db, saved when jboss stops. The
org.jboss.jdbc.HypersonicDatabase mbean is necessary for properly db shutdown
-->
<connection-url>jdbc:sqlserver://127.0.0.1:1433;database=MyDatabaseName;SelectMethod=cursor</connection-url>
<!-- The driver class -->
<driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
<!-- The login and password -->
<user-name>superusername</user-name>
<password>superpassword</password>
<!--example of how to specify class that determines if exception means connection should be destroyed
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.DummyExceptionSorter</exception-sorter-class-name>
-->
<!-- this will be run before a managed connection is removed from the pool for use by a client-->
<check-valid-connection-sql></check-valid-connection-sql>
<!-- The minimum connections in a pool/sub-pool. Pools are lazily constructed on first use -->
<min-pool-size>0</min-pool-size>
<!-- The maximum connections in a pool/sub-pool -->
<max-pool-size>30</max-pool-size>
<!-- The time before an unused connection is destroyed -->
<idle-timeout-minutes>5</idle-timeout-minutes>
<!-- sql to call when connection is created
<new-connection-sql>some arbitrary sql</new-connection-sql>
-->
<!-- example of how to specify a class that determines a connection is valid before it is handed out from the pool
<valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.DummyValidConnectionChecker</valid-connection-checker-class-name>
-->
<!-- Whether to check all statements are closed when the connection is returned to the pool,
this is a debugging feature that should be turned off in production
<track-statements/>
-->
<!-- Use the getConnection(user, pw) for logins -->
<application-managed-security/>
</local-tx-datasource>
</datasources>
<!-- ===================================================================== -->
<!-- -->
<!-- JBoss Server Configuration -->
<!-- -->
<!-- ===================================================================== -->
<datasources>
<local-tx-datasource>
<!-- The jndi name of the DataSource, it is prefixed with java:/ -->
<!-- Datasources are not available outside the virtual machine -->
<jndi-name>jdbc/SomeName</jndi-name>
<!-- for in-process persistent db, saved when jboss stops. The
org.jboss.jdbc.HypersonicDatabase mbean is necessary for properly db shutdown
-->
<connection-url>jdbc:sqlserver://127.0.0.1:1433;database=MyDatabaseName;SelectMethod=cursor</connection-url>
<!-- The driver class -->
<driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
<!-- The login and password -->
<user-name>superusername</user-name>
<password>superpassword</password>
<!--example of how to specify class that determines if exception means connection should be destroyed
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.DummyExceptionSorter</exception-sorter-class-name>
-->
<!-- this will be run before a managed connection is removed from the pool for use by a client-->
<check-valid-connection-sql></check-valid-connection-sql>
<!-- The minimum connections in a pool/sub-pool. Pools are lazily constructed on first use -->
<min-pool-size>0</min-pool-size>
<!-- The maximum connections in a pool/sub-pool -->
<max-pool-size>30</max-pool-size>
<!-- The time before an unused connection is destroyed -->
<idle-timeout-minutes>5</idle-timeout-minutes>
<!-- sql to call when connection is created
<new-connection-sql>some arbitrary sql</new-connection-sql>
-->
<!-- example of how to specify a class that determines a connection is valid before it is handed out from the pool
<valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.DummyValidConnectionChecker</valid-connection-checker-class-name>
-->
<!-- Whether to check all statements are closed when the connection is returned to the pool,
this is a debugging feature that should be turned off in production
<track-statements/>
-->
<!-- Use the getConnection(user, pw) for logins -->
<application-managed-security/>
</local-tx-datasource>
</datasources>
3. 编写 Java 方法获得连接
/**
*
*/
package org.stephencat.test;
import java.sql.*;
import javax.naming.*;
import javax.sql.*;
/**
* @author stephen
*
*/
public class DataConnection {
private static Connection conn;
/**
* 获得数据库连接
* @return Connection 数据库连接
*/
public static Connection getConnection() throws Exception{
InitialContext ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:/jdbc/SomeName");
if(conn==null || conn.isClosed()){
conn = ds.getConnection();
}
return conn;
}
/**
* 关闭数据连接
*/
public static void closeConnection(){
try{
conn.close();
}catch(Exception ex){
}
}
}
*
*/
package org.stephencat.test;
import java.sql.*;
import javax.naming.*;
import javax.sql.*;
/**
* @author stephen
*
*/
public class DataConnection {
private static Connection conn;
/**
* 获得数据库连接
* @return Connection 数据库连接
*/
public static Connection getConnection() throws Exception{
InitialContext ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:/jdbc/SomeName");
if(conn==null || conn.isClosed()){
conn = ds.getConnection();
}
return conn;
}
/**
* 关闭数据连接
*/
public static void closeConnection(){
try{
conn.close();
}catch(Exception ex){
}
}
}
4. 编写 DAO
public void SomeDAO(){
String SQL = "SELECT * FROM SomeTable";
Statement stmt = null;
ResultSet rs = null;
try{
stmt = this.conn.createStatement();
rs = stmt.executeQuery(SQL);
while(rs.next()){
//![](/Images/dot.gif)
}
}catch(Exception ex){
ex.printStackTrace();
}finally{
try{rs.close();}catch(Exception ex){}
try{stmt.close();}catch(Exception ex){}
try{conn.close();}catch(Exception ex){}
}
}
String SQL = "SELECT * FROM SomeTable";
Statement stmt = null;
ResultSet rs = null;
try{
stmt = this.conn.createStatement();
rs = stmt.executeQuery(SQL);
while(rs.next()){
//
![](/Images/dot.gif)
}
}catch(Exception ex){
ex.printStackTrace();
}finally{
try{rs.close();}catch(Exception ex){}
try{stmt.close();}catch(Exception ex){}
try{conn.close();}catch(Exception ex){}
}
}
注意 finally 段中务必保证 RecordSet 、Statement 、Connection 已经依次关闭。由于 SelectMethod=cursor 的作用,不需要担心一个业务逻辑中使用多个 DAO 方法而使得后面的 DAO 方法获取不到 Connection 。
5. 部署应用程序
6. 启动 JBoss