spring 配置: 配置了两个数据源
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd">
<bean id="lobHandler" class="org.springframework.jdbc.support.lob.DefaultLobHandler" />
<!-- ibatis sqlMapClient config -->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName"><value>oracle.jdbc.driver.OracleDriver</value></property>
<!--property name="url"><value>jdbc:oracle:thin:@219.237.253.27:6666:lottery</value></property-->
<property name="url"><value>jdbc:oracle:thin:@192.168.1.198:1521:orcl</value></property>
<!--property name="url"><value>jdbc:oracle:thin:@127.0.0.1:1521:orcl11g</value></property-->
<property name="username"><value>drugoss</value></property>
<property name="password"><value>drugoss</value></property>
</bean>
<bean id="dataSource_drug" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName"><value>oracle.jdbc.driver.OracleDriver</value></property>
<!--property name="url"><value>jdbc:oracle:thin:@219.237.253.27:6666:lottery</value></property-->
<property name="url"><value>jdbc:oracle:thin:@192.168.1.198:1521:orcl</value></property>
<!--property name="url"><value>jdbc:oracle:thin:@127.0.0.1:1521:orcl11g</value></property-->
<property name="username"><value>dportal</value></property>
<property name="password"><value>dportal</value></property>
</bean>
<bean id="sqlMapClient_drug" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<property name="configLocation">
<value>classpath:applicationContext-sqlmap-drug.xml
</value>
</property>
<property name="dataSource" ref="dataSource_drug" />
<property name="lobHandler" ref="lobHandler" />
</bean>
<bean id="sqlMapClient"
class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<property name="configLocation">
<value>classpath:applicationContext-sqlmap.xml</value>
</property>
<property name="dataSource" ref="dataSource" />
<property name="lobHandler" ref="lobHandler" />
</bean>
<bean id="tradefundsaccountdao" class="com.rc.portal.dao.TradeFundsAccountDaoImpl">
<property name="sqlmapclientportal" ref="sqlMapClient_drug" />
<property name="sqlmapclientjdposs" ref="sqlMapClient" />
</bean>
</beans>
ibatis sqlmap 配置
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd" >
<sqlMap namespace="trade_account">
<resultMap id="trade_funds_page" class="com.rc.portal.vo.TradeFundsAccount" >
<result column="ORDERNO" property="orderno" jdbcType="VARCHAR" />
<result column="ORDERNAME" property="ordername" jdbcType="VARCHAR" />
<result column="username" property="username" jdbcType="VARCHAR" />
<result column="pay_dt" property="pay_dt" jdbcType="TIMESTAMP" />
<result column="AMOUNT" property="amount" jdbcType="DECIMAL" />
<result column="useramount" property="useramount" jdbcType="DECIMAL" />
<result column="merchantamount" property="merchantamount" jdbcType="DECIMAL" />
<result column="recommendamount" property="recommendamount" jdbcType="DECIMAL" />
<result column="kuaiqianamount" property="kuaiqianamount" jdbcType="DECIMAL" />
<result column="tradeamount" property="tradeamount" jdbcType="DECIMAL" />
<result column="acceptanceamount" property="acceptanceamount" jdbcType="DECIMAL" />
<result column="faowamount" property="flowamount" jdbcType="DECIMAL" />
</resultMap>
<resultMap id="trade_funds_account" class="com.rc.portal.vo.TradeFundsAccount" >
<result column="ORDERNO" property="orderno" jdbcType="VARCHAR" />
<result column="ORDERNAME" property="ordername" jdbcType="VARCHAR" />
<result column="username" property="username" jdbcType="VARCHAR" />
<result column="CREATE_DT" property="pay_dt" jdbcType="TIMESTAMP" />
<result column="AMOUNT" property="amount" jdbcType="DECIMAL" />
<result column="FLOWAMOUNT" property="flowamount" jdbcType="DECIMAL" />
</resultMap>
<select id="trade_cleartime" resultClass="java.lang.String">
select clearingtime from d_time
</select>
<select id="trade_current_dt" resultClass="java.lang.String">
select to_char(current_dt,'yyyy-mm-dd') from d_balancedate
</select>
<insert id="trade_insert" parameterClass="com.rc.portal.vo.TradeFundsAccount" >
<selectKey resultClass="java.lang.Long" keyProperty="id" type="pre">
SELECT trade_orderid.nextval AS id FROM DUAL
</selectKey>
insert into trade_funds_account (id,username, orderno, userid, ordername, merchantid, pay_dt, amount, faowamount, useramount, merchantamount, recommendamount, tradeamount, acceptanceamount, kuaiqianamount )
values(#id:DECIMAL#,#username:VARCHAR#,#orderno:VARCHAR#,#userid:DECIMAL#,#ordername:VARCHAR#,#merchantid:DECIMAL#,#pay_dt:TIMESTAMP#,#amount:DECIMAL#,#flowamount:DECIMAL#,#useramount:DECIMAL#,#merchantamount:DECIMAL#,#recommendamount:DECIMAL#,#tradeamount:DECIMAL#,#acceptanceamount:DECIMAL#,#kuaiqianamount:DECIMAL#)
</insert>
<select id="tradeFunds_count" resultClass="java.lang.Integer" parameterClass="java.util.Map" >
select count(*) from trade_funds_account
where 1=1
<isNotNull property="startime">
and pay_dt between to_date('$startime1$','yyyy-MM-dd hh24:mi;ss') and to_date('$endtime$','yyyy-MM-dd hh24:mi:ss')
</isNotNull>
</select>
<select id="tradeFunds" resultMap="trade_funds_account" parameterClass="java.util.Map" >
select b.orderno,c.username as ordername,u.username,b.amount,b.flowamount,t.create_dt from d_bill b inner join d_bill_track t on b.orderid =t.billid inner join userinfo u on b.merchantid=u.userid inner join userinfo c on b.userid=c.userid
where t.track_type=10
and t.create_dt between to_date('$starttime$','yyyy-MM-dd hh24:mi;ss') and to_date('$endtime$','yyyy-MM-dd hh24:mi:ss')
</select>
<select id="selectByPage" resultMap="trade_funds_page" parameterClass="java.util.Map" >
select
*
from
(
select A.*,ROWNUM as rn from
(
select orderno,username, ordername,pay_dt, amount, faowamount, useramount, merchantamount, recommendamount, tradeamount, acceptanceamount, kuaiqianamount from trade_funds_account
where 1=1
<isNotNull property="startime">
and pay_dt between to_date('$startime1$','yyyy-MM-dd hh24:mi;ss') and to_date('$endtime$','yyyy-MM-dd hh24:mi:ss')
</isNotNull>
) A
<![CDATA[ where ROWNUM<= ($pageInfo.page$*$pageInfo.pageSize$) ]]>
<![CDATA[ ) where rn > ($pageInfo.page$ - 1) * $pageInfo.pageSize$]]>
</select>
</sqlMap>
public class TradeFundsAccountDaoImpl implements TradeFundsAccountDao {
// 由spring负责注入
private SqlMapClient sqlmapclientportal;
private SqlMapClient sqlmapclientjdposs;
public SqlMapClient getSqlmapclientportal() {
return sqlmapclientportal;
}
public void setSqlmapclientportal(SqlMapClient sqlmapclientportal) {
this.sqlmapclientportal = sqlmapclientportal;
}
public SqlMapClient getSqlmapclientjdposs() {
return sqlmapclientjdposs;
}
public void setSqlmapclientjdposs(SqlMapClient sqlmapclientjdposs) {
this.sqlmapclientjdposs = sqlmapclientjdposs;
}
@Override
public String getClearTime() throws SQLException {
// TODO Auto-generated method stub
Object b = sqlmapclientportal
.queryForObject("trade_account.trade_cleartime");
return b.toString();
}
@Override
public String getCurrentTime() throws SQLException {
Object b = sqlmapclientportal
.queryForObject("trade_account.trade_current_dt");
return b.toString();
}
@Override
public List<TradeFundsAccount> getTradeFunds(Map<String, String> map)
throws SQLException {
List<TradeFundsAccount> b = sqlmapclientportal.queryForList(
"trade_account.tradeFunds", map);
return b;
}
@Override
public List<TradeFundsAccount> getTradeFundsByPage(Map<String, Object> map)
throws SQLException {
List<TradeFundsAccount> b = sqlmapclientjdposs.queryForList(
"trade_account.selectByPage", map);
return b;
}
@Override
public int insertTradeFundsAccount(final List<TradeFundsAccount> ls)
throws SQLException {
SqlMapClientTemplate template = new SqlMapClientTemplate(
this.sqlmapclientjdposs);
return (Integer) template.execute(new SqlMapClientCallback() {
@Override
public Object doInSqlMapClient(SqlMapExecutor executor)
throws SQLException {
executor.startBatch();
int batch = 0;
for (TradeFundsAccount tr : ls) {
executor.insert("trade_account.trade_insert", tr);
batch++;
if (batch == 200) {
executor.executeBatch();
batch = 0;
}
}
executor.executeBatch();
return 1;
}
});
}
@Override
public int getTradeFundsAccountCount(Map<String, Object> map) throws SQLException {
int b = (Integer)sqlmapclientjdposs.queryForObject(
"trade_account.tradeFunds_count", map);
return b;
}
}