Apache Torque连接多个数据库及其使用

1.参见官网上Torque的初始化和配置

Database handles(数据库句柄)

A database handle is the name attribute that was used in the <database> tag of the schema.xml file. If the name attribute is not used, then the handle would be ‘default’.

In all examples that follow we will use the handle ‘bookstore’. As Torque has the ability to use multiple databases you can tell it which one to use by default thus(由于Torque能够使用多个数据库，因此可以告诉它默认使用哪个数据库):

torque.database.default=bookstore

The handle name ‘default’ is reserved, as Torque uses it as a reference to the default database. So you should not define the handle ‘default’ yourself (but of course you can use it e.g. in generated code).

Multiple databases

The configuration of multiple databases is very similar to the configurations described in the rest of this section, however you will need to define more than one database handle.
多个数据库的配置与本节其余部分中描述的配置非常相似，但是需要定义多个数据库句柄。

2.torque.properties配置

配置文件一般放在classes目录下

# -------------------------------------------------------------------# $Id: Torque.properties,v 1.11.2.2 2004/08/24 04:14:32 seade Exp $## This is the configuration file for Torque.## Note that strings containing "," (comma) characters must backslash# escape the comma (i.e. '\,')## -------------------------------------------------------------------torque.applicationRoot = .# -------------------------------------------------------------------##  L O G G I N G## -------------------------------------------------------------------# We use Log4J for all Torque logging and we embed the log4j# properties within our application configuration.# -------------------------------------------------------------------# This first category is required and the category# must be named 'default'. This is used for all logging# where an explicit category is not specified.log4j.category.org.apache.torque = ALL, org.apache.torquelog4j.appender.org.apache.torque = org.apache.log4j.FileAppender#log4j.appender.org.apache.torque.file = C:/work/jxc/logs/torque.loglog4j.appender.org.apache.torque.layout = org.apache.log4j.PatternLayoutlog4j.appender.org.apache.torque.layout.conversionPattern = %d [%t] %-5p %c - %m%nlog4j.appender.org.apache.torque.append = false# -------------------------------------------------------------------##  T O R Q U E  P R O P E R T I E S## -------------------------------------------------------------------# These are your database settings. Look in the# org.apache.torque.pool.* packages for more information.## The parameters to connect to the default database.  You MUST# configure these properly.# -------------------------------------------------------------------torque.database.default=bookstoretorque.database.bookstore.adapter=oracle## # Using commons-dbcp#torque.dsfactory.bookstore.factory=org.apache.torque.dsfactory.SharedPoolDataSourceFactory#torque.dsfactory.bookstore.pool.maxIdle=10#torque.dsfactory.bookstore.pool.maxActive=50#torque.dsfactory.bookstore.pool.testOnBorrow=true#torque.dsfactory.bookstore.pool.validationQuery=select * from dual#torque.dsfactory.bookstore.connection.driver = oracle.jdbc.driver.OracleDriver#torque.dsfactory.bookstore.connection.url = jdbc:oracle:thin:@192.168.1.100:1521:bookstore#torque.dsfactory.bookstore.connection.user = oracle#torque.dsfactory.bookstore.connection.password = oracle# # Using c3p0torque.dsfactory.bookstore.factory=org.apache.torque.dsfactory.C3P0DataSourceFactory#<!--当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。Default: 3 --> torque.dsfactory.bookstore.pool.acquireIncrement=3#<!--定义在从数据库获取新连接失败后重复尝试的次数。Default: 30 --> torque.dsfactory.bookstore.pool.acquireRetryAttempts=30#<!--两次连接中间隔时间，单位毫秒。Default: 1000 --> #torque.dsfactory.bookstore.pool.acquireRetryDelay=1000#<!--当连接池用完时客户端调用getConnection()后等待获取新连接的时间，超时后将抛出SQLException,如设为0则无限期等待。#单位毫秒。Default: 0 --> torque.dsfactory.bookstore.pool.checkoutTimeout=30000#<!--初始化时获取三个连接，取值应在minPoolSize与maxPoolSize之间。Default: 3 --> torque.dsfactory.bookstore.pool.initialPoolSize=5#<!--最大空闲时间,60秒内未使用则连接被丢弃。若为0则永不丢弃。#单位：秒 Default: 0 --> torque.dsfactory.bookstore.pool.maxIdleTime=0#<!--连接池中保留的最大连接数。Default: 15 --> torque.dsfactory.bookstore.pool.maxPoolSize=50#<!--JDBC的标准参数，用以控制数据源内加载的PreparedStatements数量。但由于预缓存的statements #属于单个connection而不是整个连接池。所以设置这个参数需要考虑到多方面的因素。 #如果maxStatements与maxStatementsPerConnection均为0，则缓存被关闭。Default: 0--> torque.dsfactory.bookstore.pool.maxStatements=150<!--maxStatementsPerConnection定义了连接池内单个连接所拥有的最大缓存statements数。Default: 0 --> torque.dsfactory.bookstore.pool.maxStatementsPerConnection=0#<!--c3p0是异步操作的，缓慢的JDBC操作通过帮助进程完成。扩展这些操作可以有效的提升性能 #通过多线程实现多个操作同时被执行。Default: 3--> torque.dsfactory.bookstore.pool.numHelperThreads=3#<!--每60秒检查所有连接池中的空闲连接。单位：秒 Default: 0 --> torque.dsfactory.bookstore.pool.idleConnectionTestPeriod=600#<!--定义所有连接测试都执行的测试语句。在使用连接测试的情况下这个一显著提高测试速度。注意： #测试的表必须在初始数据源的时候就存在。Default: null--> torque.dsfactory.bookstore.pool.preferredTestQuery=select * from dual#db connnectiontorque.dsfactory.bookstore.connection.driverClass = oracle.jdbc.driver.OracleDrivertorque.dsfactory.bookstore.connection.jdbcUrl = jdbc:oracle:thin:@192.168.1.100:1521:bookstoretorque.dsfactory.bookstore.connection.user = oracletorque.dsfactory.bookstore.connection.password = oracle# Determines if the quantity column of the IDBroker's id_table should# be increased automatically if requests for ids reaches a high# volume.torque.idbroker.clever.quantity=false# Determines whether the managers cache instances of the business objects.# And also whether the MethodResultCache will really cache results.torque.manager.useCache = true## 第二个数据库连接torque.database=ordertorque.database.order.adapter=oracletorque.dsfactory.order.factory=org.apache.torque.dsfactory.C3P0DataSourceFactorytorque.dsfactory.order.pool.acquireIncrement=2torque.dsfactory.order.pool.acquireRetryAttempts=30torque.dsfactory.order.pool.checkoutTimeout=30000torque.dsfactory.order.pool.initialPoolSize=1torque.dsfactory.order.pool.maxIdleTime=0torque.dsfactory.order.pool.maxPoolSize=50torque.dsfactory.order.pool.maxStatements=150torque.dsfactory.order.pool.maxStatementsPerConnection=0torque.dsfactory.order.pool.numHelperThreads=3torque.dsfactory.order.pool.idleConnectionTestPeriod=600torque.dsfactory.order.pool.preferredTestQuery=select sysdate from dualtorque.dsfactory.order.connection.driverClass = oracle.jdbc.driver.OracleDrivertorque.dsfactory.order.connection.jdbcUrl = jdbc:oracle:thin:@192.168.2.200:1521:BABY_ORDERtorque.dsfactory.order.connection.user = scotttorque.dsfactory.order.connection.password = tiger

3.log4j.properties配置

# ------------------------------------------------------------------------## Logging Configuration## $Id: log4j.properties,v 1.10 2005/07/27 04:25:09 huangxq Exp $## ------------------------------------------------------------------------## If we don't know the logging facility, put it into the# sl.log#log4j.rootLogger = error, root, stdout## Application debugging##log4j.category.com.sl = debug, sllog4j.category.com.sl = debug, sl, stdoutlog4j.additivity.com.sl = false## torque goes into torque Log#log4j.category.org.apache.torque = debug, torquelog4j.additivity.org.apache.torque = false########################################################################## Logfile definitions##########################################################################print out to consolelog4j.appender.stdout=org.apache.log4j.ConsoleAppenderlog4j.appender.stdout.layout=org.apache.log4j.PatternLayoutlog4j.appender.stdout.layout.ConversionPattern=%d [%-5p] [%F] %M : %L -- %m%n## root.log# log4j.appender.root = org.apache.log4j.DailyRollingFileAppenderlog4j.appender.root.file = D:/logs/fmanage/fmanage_root.loglog4j.appender.root.DatePattern='.'yyyy-MM-ddlog4j.appender.root.layout = org.apache.log4j.PatternLayoutlog4j.appender.root.layout.conversionPattern =%d [%-5p] [%F] : %L -- %m%nlog4j.appender.root.append = false## sl.log# log4j.appender.sl = org.apache.log4j.DailyRollingFileAppenderlog4j.appender.sl.file = D:/logs/fmanage/fmanage_sl.loglog4j.appender.sl.DatePattern='.'yyyy-MM-ddlog4j.appender.sl.layout = org.apache.log4j.PatternLayoutlog4j.appender.sl.layout.conversionPattern =%d [%-5p] [%F] : %L -- %m%nlog4j.appender.sl.append = true## torque.log#log4j.appender.torque = org.apache.log4j.DailyRollingFileAppenderlog4j.appender.torque.file = D:/logs/fmanage/fmanage_torque.loglog4j.appender.torque.DatePattern='.'yyyy-MM-ddlog4j.appender.torque.layout = org.apache.log4j.PatternLayoutlog4j.appender.torque.layout.conversionPattern =%d [%-5p] [%F] : %L -- %m%nlog4j.appender.torque.append = false

4.初始化Torque

InitServer.java

package com.sl;import java.io.InputStream;import java.util.List;import java.util.Properties;import org.apache.commons.configuration.PropertiesConfiguration;import org.apache.log4j.Logger;import org.apache.log4j.PropertyConfigurator;import com.sl.util.DBUtils;import com.workingdogs.village.Record;public class InitServer {   private static Logger log;   private static final String TORQUE_CONFIG_FILE = "torque.properties";   private static final String LOG4J_CONFIG_FILE = "log4j.properties";   public static boolean init() {       // 初始化       boolean flag = true;       ClassLoader cl = InitServer.class.getClassLoader();       InputStream logIn = null;       InputStream dbIn = null;       try {           logIn = cl.getResourceAsStream(LOG4J_CONFIG_FILE);           Properties p = new Properties();           p.load(logIn);           PropertyConfigurator.configure(p);           log = Logger.getLogger(InitServer.class);           log.debug("InitServer init() log4j init success ---------------");           // 初始化           PropertiesConfiguration dbConfig = new PropertiesConfiguration();           dbIn = cl.getResourceAsStream(TORQUE_CONFIG_FILE);           dbConfig.load(dbIn);           DBUtils.init(dbConfig);           log.debug("InitServer init() Torque init success ---------------");           String testSql = "select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual";           String time = null;           List<Record> list = DBUtils.query(testSql);           if (list != null && list.size() > 0) {               Record record = list.get(0);               time = record.getValue(1).asString();               log.debug("from db init-time: " + time);           }       } catch (Exception ex) {           log.error("InitServer Exception when init() - " + ex);           flag = false;       } finally {           closeInputStream(logIn);           closeInputStream(dbIn);       }       return flag;   }   private static void closeInputStream(InputStream is) {       // 退出前,一定要将文件处理读写器关闭       try {           if (is != null)               is.close();           is = null;       } catch (Exception exx) {           log.error(exx);       }   }}

DBUtils.java

package com.sl.util;import java.sql.Connection;import java.sql.ResultSet;import java.sql.Statement;import java.util.List;import org.apache.commons.configuration.Configuration;import org.apache.log4j.Logger;import org.apache.torque.util.BasePeer;import com.workingdogs.village.Record;import org.apache.torque.Torque;public class DBUtils {   private static Logger log = Logger.getLogger(DBUtils.class.getName());   public DBUtils() {   }   /**    * 初始化    *     * @param dbConfigFile    * @return    */   public static boolean init(String dbConfigFile) {       // 初始化torque       boolean flag = true;       try {           Torque.init(dbConfigFile);           System.out.println("---------- 初始化torque成功---------");       } catch (Exception exx) {           log.error(exx);           flag = false;       }       return flag;   }   /**    * 初始化    *     * @param dbConfig    * @return    */   public static boolean init(Configuration dbConfig) {       // 初始化torque       boolean flag = true;       try {           Torque.init(dbConfig);           System.out.println("---------- 初始化torque成功---------");       } catch (Exception exx) {           log.error(exx);           flag = false;       }       return flag;   }   /**    * 获取数据库连接(默认)    *     * @return    */   public static Connection getDBConn() {       // 取得数据库连接       Connection conn = null;       try {           conn = Torque.getConnection();       } catch (Exception exx) {           log.error(exx);       }       return conn;   }   /**    * 获取指定数据库连接    *     * @param dbName    * @return    */   public static Connection getDBConn(String dbName) {       // 取得数据库连接       Connection conn = null;       try {           conn = Torque.getConnection(dbName);       } catch (Exception exx) {           log.error(exx);       }       return conn;   }   /**    * 获取默认数据库连接，是否支持事务自动提交    *     * @param autoCommitFlag    * @return    */   public static Connection getDBConn(boolean autoCommitFlag) {       // 取得数据库连接       Connection conn = null;       try {           conn = Torque.getConnection();           setAutoCommit(conn, autoCommitFlag);       } catch (Exception exx) {           log.error(exx);       }       return conn;   }   /**    * 获取指定数据库连接，是否支持事务自动提交    *     * @param dbName    * @param autoCommitFlag    * @return    */   public static Connection getDBConn(String dbName, boolean autoCommitFlag) {       // 取得数据库连接       Connection conn = null;       try {           conn = Torque.getConnection(dbName);           setAutoCommit(conn, autoCommitFlag);       } catch (Exception exx) {           log.error(exx);       }       return conn;   }   /**    * 关闭数据库连接    *     * @param conn    */   public static void closeDBConn(Connection conn) {       // 关闭数据库连接       try {           if (conn != null) {               setAutoCommit(conn, true);// 无论如何，都应该在连接关闭前，将AutoCommit 设置为               // true               Torque.closeConnection(conn);               conn = null;           }       } catch (Exception exx) {           log.error(exx);       }   }   /**    * 关闭Statement    *     * @param statement    */   public static void closeDBStatement(Statement statement) {       // 关闭Statement       try {           if (statement != null) {               statement.close();               statement = null;           }       } catch (Exception exx) {           log.error(exx);       }   }   /**    * 关闭Statement,关闭数据库连接    *     * @param statement    * @param conn    */   public static void closeDBObject(Statement statement, Connection conn) {       // 关闭Statement       closeDBStatement(statement);       // 关闭数据库连接       closeDBConn(conn);   }   /**    * 关闭ResultSet    *     * @param resultSet    */   public static void closeDBResultSet(ResultSet resultSet) {       // 关闭ResultSet       try {           if (resultSet != null) {               resultSet.close();               resultSet = null;           }       } catch (Exception exx) {           log.error(exx);       }   }   /**    * 关闭Statement,关闭ResultSet,关闭数据库连接    *     * @param statement    * @param resultSet    * @param conn    * @param autoCommitFlag    */   public static void closeDBObject(Statement statement, ResultSet resultSet, Connection conn) {       // 关闭Statement       closeDBStatement(statement);       // 关闭ResultSet       closeDBResultSet(resultSet);       // 关闭数据库链接       closeDBConn(conn);   }   /**    * 提交事务    *     * @param conn    */   public static void commit(Connection conn) {       try {           if (conn != null) {               if (conn.getMetaData().supportsTransactions() && conn.getAutoCommit() == false) {                   conn.commit();                   conn.setAutoCommit(true);               }           }       } catch (Exception exx) {           log.error(exx);       }   }   /**    * 回滚事务    *     * @param conn    */   public static void rollback(Connection conn) {       try {           if (conn != null) {               if (conn.getMetaData().supportsTransactions() && conn.getAutoCommit() == false) {                   conn.rollback();                   conn.setAutoCommit(true);               }           }       } catch (Exception ee) {           log.error(ee);       }   }   /**    * 设置是否自动提交事务    *     * @param conn    * @param autoCommitFlag    */   public static void setAutoCommit(Connection conn, boolean autoCommitFlag) {       try {           if (conn.getMetaData().supportsTransactions()) {               conn.setAutoCommit(autoCommitFlag);           }       } catch (Exception e) {           log.error(e);       }   }   /**    * 查询返回所有记录    *     * @param querySql    * @return    */   @SuppressWarnings("unchecked")   public static List<Record> query(String querySql) {       List<Record> results = null;       try {           results = BasePeer.executeQuery(querySql);       } catch (Exception ee) {           results = null;           log.error(ee);       }       return results;   }   /**    * 查询     * singleRecord = true 表示只返回一条记录     * singleRecord = false 表示返回所有记录    *     * @param conn    * @param singleRecord    * @param querySql    * @return    */   @SuppressWarnings("unchecked")   public static List<Record> query(Connection conn, boolean singleRecord, String querySql) {       List<Record> results = null;       try {           if (conn != null) {               results = BasePeer.executeQuery(querySql, singleRecord, conn);           }       } catch (Exception ee) {           results = null;           log.error(ee);       } finally {           closeDBConn(conn);       }       return results;   }   /**    * 查询返回所有记录    *     * @param conn    * @param querySql    * @return    */   public static List<Record> query(Connection conn, String querySql) {       List<Record> results = null;       boolean singleRecord = false;       results = query(conn, singleRecord, querySql);       return results;   }   /**    * 查询指定配置的数据库,返回所有记录    *     * @param dbName    * @param querySql    * @return    */   public static List<Record> query(String dbName, String querySql) {       List<Record> results = null;       try {           Connection conn = DBUtils.getDBConn(dbName);           if (conn != null) {               results = query(conn, querySql);           }       } catch (Exception ee) {           results = null;           log.error(ee);       }       return results;   }   /**    * 添加或更新数据库记录    *     * @param insertOrUpdateSql    * @return    */   public static int insertOrUpdate(String insertOrUpdateSql) {       int results = -1;       try {           results = BasePeer.executeStatement(insertOrUpdateSql);       } catch (Exception ee) {           results = -1;           log.error(ee);       }       return results;   }   /**    * 添加或更新数据库记录    *     * @param conn    * @param insertOrUpdateSql    * @return    */   public static int insertOrUpdate(Connection conn, String insertOrUpdateSql) {       int results = -1;       try {           if (conn != null) {               results = BasePeer.executeStatement(insertOrUpdateSql, conn);           }       } catch (Exception ee) {           results = -1;           log.error(ee);       } finally {           closeDBConn(conn);       }       return results;   }}

5.使用

查询默认数据库可直接使用List<Record> list = DBUtils.query(querySql);
也可以List<Record> list = DBUtils.query("bookstore", querySql);

查询指定的数据库使用List<Record> list = DBUtils.query("order", querySql);

注：“order”是自定义的数据库名，可以和真实数据库名不一样，作为Torque的数据库标识。

本文参考:
http://www.blogjava.net/fanyingjie/archive/2008/05/22/202114.html