bbossgroups持久层ConfigSQLExecutor组件的典型用法-预编译操作

本文介绍bbossgroups持久层ConfigSQLExecutor组件的典型用法-预编译操作
本文分三部分：
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;
	}
}

评论

4 楼 yin_bp 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>  

3 楼 ainidehsj 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”是什么意思？

2 楼 yin_bp 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>

1 楼 yin_bp 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>