浏览 1879 次
该帖已经被评为新手帖
|
|
---|---|
作者 | 正文 |
发表时间:2011-06-29
本文分三部分: 1.dao层写法 2.sql配置文件配置方法(可以支持多种数据库sql配置) 3.涉及的datasource的配置 第一部分 dao层写法 package com.chinacreator.tjbb.dao.impl; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import com.chinacreator.tjbb.dao.SjxftjDao; import com.chinacreator.tjbb.dto.DssjxfTj; import com.chinacreator.tjbb.dto.SjxftjDto; import com.frameworkset.common.poolman.ConfigSQLExecutor; import com.frameworkset.common.poolman.Record; import com.frameworkset.common.poolman.handle.NullRowHandler; public class SjxftjDaoImpl implements SjxftjDao{ public static final String DBNAME = "stsmc"; private static ConfigSQLExecutor executor = new ConfigSQLExecutor("com/chinacreator/tjbb/dao/impl/stxftj.xml"); public List<SjxftjDto> findSjxftjList() throws Exception{ return executor.queryListWithDBName(SjxftjDto.class, DBNAME, "findSjxftjList"); } public List<DssjxfTj> findDstjList(String startDate,String endDate, String tbname) throws Exception{ return executor.queryListWithDBName(DssjxfTj.class, DBNAME, "findDstjList", startDate,endDate,tbname); } public List<String> findDsMcList(String date) throws Exception{ final List<String> list = new ArrayList<String>(); executor.queryWithDBNameByNullRowHandler(new NullRowHandler() { @Override public void handleRow(Record record) throws Exception { list.add(record.getString("DSMC")); } }, DBNAME, "findDsMcList", date); return list; } public Map<String, String> findDsMcMap(String startDate,String endDate) throws Exception { final Map<String, String> map = new HashMap<String, String>(); executor.queryWithDBNameByNullRowHandler(new NullRowHandler() { @Override public void handleRow(Record record) throws Exception { String mc = record.getString("DSMC"); String dm = record.getString("DSDM"); map.put(dm, mc); } }, DBNAME, "findDsMcMap", startDate,endDate); return map; } } 声明:ITeye文章版权属于作者,受法律保护。没有作者书面许可不得转载。
推荐链接
|
|
返回顶楼 | |
发表时间:2011-06-29
第二部分.sql配置文件配置方法(可以支持多种数据库sql配置)
<?xml version="1.0" encoding="UTF-8"?> <properties> <property name="findSjxftjList"> <![CDATA[ SELECT * FROM T_TJ_SJXFLBXX WHERE 1=1 ]]> </property> <property name="findDsMcMap"> <![CDATA[ select distinct DSMC,DS_MC DSDM FROM T_LOG_COUNT where startdate = ( SELECT MAX(b.startdate) FROM T_LOG_COUNT b where to_date(b.startdate,'yyyy-mm-dd') >= to_date(?,'yyyy-mm-dd') and to_date(b.startdate,'yyyy-mm-dd') <= to_date(?,'yyyy-mm-dd')) ORDER BY DSDM ]]> </property> <property name="findDsMcMap-mysql"> <![CDATA[ select distinct DSMC,DS_MC DSDM FROM T_LOG_COUNT where startdate = ( SELECT MAX(b.startdate) FROM T_LOG_COUNT b where b.startdate >= ? and b.startdate <= ?) ORDER BY DSDM ]]> </property> <property name="findDsMcList"> <![CDATA[ select distinct dsmc FROM T_LOG_COUNT b where b.startdate = ? ]]> </property> <property name="findDstjList"> <![CDATA[ SELECT b.ds_mc DSDM, sum(b.lines_output) XFZL, sum(NVL(b.ds_lines_output, 0)) RKZL, sum(NVL(b.ds_lines_updated, 0)) GXZL FROM T_LOG_COUNT b where to_date(b.startdate, 'yyyy-mm-dd') >= to_date(?, 'yyyy-mm-dd') and to_date(b.startdate, 'yyyy-mm-dd') <= to_date(?, 'yyyy-mm-dd') and data_type = ? group by b.ds_mc order by DSDM ]]> </property> <property name="findDstjList-mysql"> <![CDATA[ SELECT b.ds_mc DSDM,sum(b.lines_output) XFZL,sum(ifnull(b.ds_lines_output, 0)) RKZL,sum(ifnull(b.ds_lines_updated, 0)) GXZL FROM T_LOG_COUNT b where b.startdate >= ? and b.startdate <= ? and data_type = ? group by b.ds_mc order by DSDM ]]> </property> </properties> |
|
返回顶楼 | |
发表时间:2011-06-29
第三部分.涉及的datasource的配置
<?xml version="1.0" encoding="gb2312"?> <poolman> <datasource> <dbname>stsmc</dbname> <loadmetadata>false</loadmetadata> <jndiName>jdbc/mysql-ds</jndiName> <driver>com.mysql.jdbc.Driver</driver> <url>jdbc:mysql://172.16.33.46:3306/etl</url> <username>root</username> <password>123456</password> <txIsolationLevel>READ_COMMITTED</txIsolationLevel> <nativeResults>true</nativeResults> <poolPreparedStatements>false</poolPreparedStatements> <initialConnections>2</initialConnections> <minimumSize>0</minimumSize> <maximumSize>10</maximumSize> <!--控制connection达到maximumSize是否允许再创建新的connection true:允许,缺省值 false:不允许--> <maximumSoft>false</maximumSoft> <!-- 是否检测超时链接(事务超时链接) true-检测,如果检测到有事务超时的链接,系统将强制回收(释放)该链接 false-不检测,默认值 --> <removeAbandoned>true</removeAbandoned> <!-- 链接使用超时时间(事务超时时间) 单位:秒 --> <userTimeout>50</userTimeout> <!-- 系统强制回收链接时,是否输出后台日志 true-输出,默认值 false-不输出 --> <logAbandoned>true</logAbandoned> <!-- 数据库会话是否是readonly,缺省为false --> <readOnly>false</readOnly> <!-- 对应属性:timeBetweenEvictionRunsMillis the amount of time (in milliseconds) to sleep between examining idle objects for eviction --> <skimmerFrequency>10</skimmerFrequency> <!--对应于minEvictableIdleTimeMillis 属性: minEvictableIdleTimeMillis the minimum number of milliseconds an object can sit idle in the pool before it is eligable for evcition 单位:秒 空闲链接回收时间,空闲时间超过指定的值时,将被回收 --> <connectionTimeout>60</connectionTimeout> <!-- numTestsPerEvictionRun the number of idle objects to examine per run within the idle object eviction thread (if any) 每次回收的链接个数 --> <shrinkBy>5</shrinkBy> <!-- /** * 检测空闲链接处理时,是否对空闲链接进行有效性检查控制开关 * true-检查,都检查到有无效链接时,直接销毁无效链接 * false-不检查,缺省值 */ --> <testWhileidle>true</testWhileidle> <!-- 定义数据库主键生成机制 缺省的采用系统自带的主键生成机制, 外步程序可以覆盖系统主键生成机制 由值来决定 auto:自动,一般在生产环境下采用该种模式, 解决了单个应用并发访问数据库添加记录产生冲突的问题,效率高,如果生产环境下有多个应用并发访问同一数据库时,必须采用composite模式 composite:结合自动和实时从数据库中获取最大的主键值两种方式来处理,开发环境下建议采用该种模式, 解决了多个应用同时访问数据库添加记录时产生冲突的问题,效率相对较低, 如果生产环境下有多个应用并发访问同一数据库时,必须采用composite模式 --> <keygenerate>composite</keygenerate> <!--poolman的日志信息输出改用log4j来输出到日志文件,相关的配置见log4j.properties文件--> <!--<logFile>dbaccess.log</logFile> <debugging>true</debugging>--> <!-- 请求链接时等待时间,单位:秒 客服端程序请求链接等待时间超过指定值时,后台包等待超时异常 --> <maxWait>60</maxWait> <!-- 链接有效性检查sql语句 --> <validationQuery>select 1</validationQuery> <autoprimarykey>false</autoprimarykey> <showsql>false</showsql> </datasource> </poolman> |
|
返回顶楼 | |
发表时间:2011-06-29
public List<DssjxfTj> findDstjList(String startDate,String endDate, String tbname) throws Exception{
27. return executor.queryListWithDBName(DssjxfTj.class, DBNAME, "findDstjList", startDate,endDate,tbname); 28. } 请问楼主:“findDstjList”是什么意思? |
|
返回顶楼 | |
发表时间:2011-06-29
ainidehsj 写道 public List<DssjxfTj> findDstjList(String startDate,String endDate, String tbname) throws Exception{ return executor.queryListWithDBName(DssjxfTj.class, DBNAME, "findDstjList", startDate,endDate,tbname); } 请问楼主:“findDstjList”是什么意思? 这个代表dao方法的一个名字,同时也代表sql配置文件中相应的sql语句的配置名称: <property name="findDstjList"> <![CDATA[ SELECT b.ds_mc DSDM, sum(b.lines_output) XFZL, sum(NVL(b.ds_lines_output, 0)) RKZL, sum(NVL(b.ds_lines_updated, 0)) GXZL FROM T_LOG_COUNT b where to_date(b.startdate, 'yyyy-mm-dd') >= to_date(?, 'yyyy-mm-dd') and to_date(b.startdate, 'yyyy-mm-dd') <= to_date(?, 'yyyy-mm-dd') and data_type = ? group by b.ds_mc order by DSDM ]]> </property> |
|
返回顶楼 | |
发表时间:2011-07-09
bbossgroups新域名开通:
http://www.bbossgroups.com |
|
返回顶楼 | |