论坛首页 → Java企业应用论坛 →

bbossgroups持久层框架ConfigSQLExecutor组件api实例

浏览 2301 次
锁定老帖子 主题：bbossgroups持久层框架ConfigSQLExecutor组件api实例
精华帖 (0) :: 良好帖 (0) :: 新手帖 (0) :: 隐藏帖 (4)
作者 正文
  • yin_bp
  • 等级: 四钻会员
  • 性别:
  • 文章: 713
  • 积分: 1950
  • 来自: 长沙
   发表时间：2011-07-02   最后修改：2011-12-23
本文简单介绍bbossgroups持久层框架ConfigSQLExecutor组件api实例，以下就是相关接口的使用实例： 
/*
 *  Copyright 2008 biaoping.yin
 *
 *  Licensed under the Apache License, Version 2.0 (the "License");
 *  you may not use this file except in compliance with the License.
 *  You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
 */
package com.frameworkset.sqlexecutor;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.junit.Before;
import org.junit.Test;

import com.frameworkset.common.poolman.ConfigSQLExecutor;
import com.frameworkset.common.poolman.SQLParams;

public class ConfigSQLExecutorTest {
	private ConfigSQLExecutor executor ;
	@Before
	public void init()
	{
		executor = new ConfigSQLExecutor("com/frameworkset/sqlexecutor/sqlfile.xml");
		try {
			String sql = executor.getSql("sqltemplate");
			sql = executor.getSql("bspf","sqltemplate");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	@Test
	public void queryMap() throws SQLException
	{
		
		
		Map dbBeans  =  executor.queryObject(HashMap.class, "sqltest");
		System.out.println(dbBeans);
	}
	
	
	@Test
	public void queryField() throws SQLException
	{
		 ListBean bean = new ListBean();
		    bean.setFieldName("阿斯顿飞");
		 //<property name="refresh_interval" value="10000"/>
		 String result = executor.queryFieldBean("sqltemplate", bean);
		 System.out.println(result);
		 
		 result = executor.queryFieldBean("sqltemplate", bean);
		 System.out.println(result);
		 
	}
	
	@Test
	public void dynamicquery() throws SQLException
	{
		 ListBean bean = new ListBean();
		    bean.setFieldName("阿斯顿飞");
		 //<property name="refresh_interval" value="10000"/>
		 List<ListBean> result = executor.queryListBean(ListBean.class, "dynamicsqltemplate", bean);
		 System.out.println(result.size());
		  bean.setFieldName("");
		 result = (List<ListBean>) executor.queryListBean(ListBean.class,"dynamicsqltemplate", bean);
		 System.out.println(result.size());
		 
		 bean.setFieldName(null);
		 result = (List<ListBean>) executor.queryListBean(ListBean.class,"dynamicsqltemplate", bean);
		 System.out.println(result.size());
		 
	}
	
	@Test
	public void dynamicqueryWithSQLParams() throws SQLException
	{
		 SQLParams params = new SQLParams();
		 params.addSQLParam("fieldName", "阿斯顿飞", SQLParams.STRING);
		 
		 //<property name="refresh_interval" value="10000"/>
		 List<ListBean> result = executor.queryListBean(ListBean.class, "dynamicsqltemplate", params);
		 System.out.println(result.size());
		 params = new SQLParams();
		 params.addSQLParam("fieldName", "", SQLParams.STRING);
		 result = (List<ListBean>) executor.queryListBean(ListBean.class,"dynamicsqltemplate", params);
		 System.out.println(result.size());
		 
		 params = new SQLParams();
		 params.addSQLParam("fieldName", null, SQLParams.STRING);
		 result = (List<ListBean>) executor.queryListBean(ListBean.class,"dynamicsqltemplate", params);
		 System.out.println(result.size());
		 
	}
	
	
	@Test
	public void dynamicqueryidWithbean() throws SQLException
	{
		 ListBean bean = new ListBean();
		    bean.setId(139);
		 //<property name="refresh_interval" value="10000"/>
		 List<ListBean> result = executor.queryListBean(ListBean.class, "dynamicsqltemplateid", bean);
		 System.out.println(result.size());
		  bean.setId(-1);
		 result = (List<ListBean>) executor.queryListBean(ListBean.class,"dynamicsqltemplateid", bean);
		 System.out.println(result.size());
		 
		 bean.setId(0);
		 result = (List<ListBean>) executor.queryListBean(ListBean.class,"dynamicsqltemplateid", bean);
		 System.out.println(result.size());
		 
	}
	@Test
	public void dynamicqueryidWithSQLParams() throws SQLException
	{
		 
		    
		 SQLParams params = new SQLParams();
		 params.addSQLParam("id", 139, SQLParams.INT);
		 //<property name="refresh_interval" value="10000"/>
		 List<ListBean> result = executor.queryListBean(ListBean.class, "dynamicsqltemplateid", params);
		 System.out.println(result.size());
		 params = new SQLParams();
		 params.addSQLParam("id", -1, SQLParams.INT);
		 result = (List<ListBean>) executor.queryListBean(ListBean.class,"dynamicsqltemplateid", params);
		 System.out.println(result.size());
		 params = new SQLParams();
		 params.addSQLParam("id", 0, SQLParams.INT);
		 result = (List<ListBean>) executor.queryListBeanWithDBName(ListBean.class,"mysql","dynamicsqltemplateid", params);
//		 result = (List<ListBean>) SQLExecutor.queryListBeanWithDBName(ListBean.class,"dbname","sql", params);
		 System.out.println(result.size());
		 
	}
	
	
	@Test
	public void updateWithSQLParams() throws SQLException
	{
		 
		    
		 SQLParams params = new SQLParams();
		 params.addSQLParam("id", 139, SQLParams.INT);
		 params.addSQLParam("fieldName", "duoduo139", SQLParams.STRING);
		 List<SQLParams> params_ = new ArrayList<SQLParams>(2);
		 params_.add(params);
		 //<property name="refresh_interval" value="10000"/>
		 
		 
		 params = new SQLParams();
		 params.addSQLParam("id", 140, SQLParams.INT);
		 params.addSQLParam("fieldName", "duoduo140", SQLParams.STRING);
		 params_.add(params);
		
		 params = new SQLParams();
		 params.addSQLParam("id", 141, SQLParams.INT);
		 
		 params.addSQLParam("fieldName", "duoduo141", SQLParams.STRING);
		 params_.add(params);
		 executor.updateBeans("updatesqltemplate", params_);		 
//		 result = (List<ListBean>) SQLExecutor.queryListBeanWithDBName(ListBean.class,"dbname","sql", params);
//		 System.out.println(result.size());
		 
	}
	
	@Test
	public void queryFieldWithSQLParams() throws SQLException
	{
		 
		
		 int count = executor.queryTField(int.class, "queryFieldWithSQLParams");
		 System.out.println("count:" + count);
		 
		 
		 long count_long = executor.queryTField(long.class, "queryFieldWithSQLParams");
		 System.out.println("count_long:" + count_long);
		 
		 short count_short = executor.queryTField(short.class, "queryFieldWithSQLParams");
		 System.out.println("count_short:" + count_short);
		 
		 double count_double = executor.queryTField(double.class, "queryFieldWithSQLParams");
		 System.out.println("count_double:" + count_double);
		 
		 float count_float = executor.queryTField(float.class, "queryFieldWithSQLParams");
		 System.out.println("count_float:" + count_float);
		 
		 BigDecimal count_BigDecimal = executor.queryTField(BigDecimal.class, "queryFieldWithSQLParams");
		 System.out.println("count_BigDecimal:" + count_BigDecimal.intValue());
		 BigDecimal[] count_BigDecimals = executor.queryTField(BigDecimal[].class, "queryFieldWithSQLParams");
		 System.out.println("count_BigDecimals[0]:" + count_BigDecimals[0].intValue());
//		 result = (List<ListBean>) SQLExecutor.queryListBeanWithDBName(ListBean.class,"dbname","sql", params);
//		 System.out.println(result.size());
		 
	}
	
	@Test
	public void updateWithMapParams() throws SQLException
	{
		 
		 Map datas = new HashMap();
		 datas.put("id", 139);
		 datas.put("fieldName", "updateWithMapParams139");
		 List<Map> params_ = new ArrayList<Map>();
		 params_.add(datas);
		 datas = new HashMap();
		 datas.put("id", 140);
		 datas.put("fieldName", "updateWithMapParams140");
		 params_.add(datas);		
		 datas = new HashMap();
		 datas.put("id", 141);
		 datas.put("fieldName", "updateWithMapParams141");
		 params_.add(datas);		
		 executor.updateBeans("updatesqltemplate", params_);		 
//		 result = (List<ListBean>) SQLExecutor.queryListBeanWithDBName(ListBean.class,"dbname","sql", params);
//		 System.out.println(result.size());
		 
	}

}


呵呵，反正方法名称里面带Bean的就是针对条件在bean对象/List<bean>、SQLParams/List<SQLParams>和Map/List<Map>对象中的这种情况，并且sql语句是模板sql

不带Bean的就是预编译sql语句，变量以?号代替,参数就可以在后面追加

如果要指定dbname的话就在相应的方法后面添加WithDBName

分页方法时需要指定起始地址和每页记录数

还有指定行处理器和空行处理器、Field行处理器的一组接口，呵呵

没办法为了满足各种要求必须提供这么多接口，我估计bboss的persistent应该是业界接口最全的一套persistent framework了，呵呵
返回顶楼
         
  • yin_bp
  • 等级: 四钻会员
  • 性别:
  • 文章: 713
  • 积分: 1950
  • 来自: 长沙
   发表时间：2011-07-02  
sql配置文件如下： 
<?xml version="1.0" encoding='gb2312'?>
<properties>
	<description>
<![CDATA[
	sql配置文件
	可以通过名称属性name配置默认sql，特定数据库的sql通过在
	名称后面加数据库类型后缀来区分，例如：
	sqltest
	sqltest-oracle
	sqltest-derby
	sqltest-mysql
	等等，本配置实例就演示了具体配置方法
 ]]>
	</description>
	<property name="sqltest"><![CDATA[select * from LISTBEAN]]>
	</property>
	<property name="sqltest-oracle"><![CDATA[select * from LISTBEAN]]>
	</property>
	<property name="sqltemplate"><![CDATA[select FIELDNAME from LISTBEAN where FIELDNAME=#[fieldName]]]>
	</property>
	<property name="sqltemplate-oracle"><![CDATA[select FIELDNAME from LISTBEAN where FIELDNAME=#[fieldName]  ]]></property>
	<!-- 动态sql，如果FIELDNAME 不等于null，并且FIELDNAME不为""将FIELDNAME作为查询条件-->
	<property name="dynamicsqltemplate"><![CDATA[select *  from LISTBEAN  where 1=1 
					#if($fieldName && !$fieldName.equals("")) and FIELDNAME = #[fieldName] #end  ]]>
	</property>
	
	
	<!-- 动态sql，如果id 不等于-1，并且id不为0将id作为查询条件-->
	<property name="dynamicsqltemplateid"><![CDATA[select *  from LISTBEAN  where 1=1 
					#if($id != -1 && $id != 0) and id = #[id] #end  ]]>
	</property>
	
	<property name="updatesqltemplate"><![CDATA[update LISTBEAN  set FIELDNAME = #[fieldName] where id = #[id] ]]>
	</property>
	
	<property name="queryFieldWithSQLParams"><![CDATA[select count(1)  from LISTBEAN ]]>
	</property>
</properties>
返回顶楼
          回帖地址
0 请登录后投票
  • yin_bp
  • 等级: 四钻会员
  • 性别:
  • 文章: 713
  • 积分: 1950
  • 来自: 长沙
   发表时间：2011-07-02  
对应的值对象： 
package com.frameworkset.sqlexecutor;

import com.frameworkset.orm.annotation.PrimaryKey;

/**
 * CREATE
    TABLE LISTBEAN
    (
        ID INTEGER NOT NULL,
        FIELDNAME VARCHAR(300),
        FIELDLABLE VARCHAR(300),
        FIELDTYPE VARCHAR(300),
        SORTORDER VARCHAR(300),
        ISPRIMARYKEY INTEGER,
        REQUIRED INTEGER,
        FIELDLENGTH INTEGER,
        ISVALIDATED INTEGER,
        CONSTRAINT LISTBEANKEY PRIMARY KEY (ID)
    )
    
    insert into TABLEINFO (TABLE_NAME, TABLE_ID_NAME, TABLE_ID_INCREMENT, TABLE_ID_VALUE, TABLE_ID_GENERATOR, TABLE_ID_TYPE, TABLE_ID_PREFIX) values ('LISTBEAN', 'id', 1, 0, null, 'int', null);
 * @author Administrator
 *
 */
public class ListBean {
	@PrimaryKey(pkname="ListBean",auto=true)
	private int id ;
	private String fieldName;
//	@Column(name="fileNamezzz",dataformat="格式转换" ,type="clob")
	private String fieldLable;
	private String fieldType;
	private String sortorder;
	private boolean isprimaryKey;
	private boolean required;
	private int fieldLength;
	private int isvalidated;
	/**
	 * @return the fieldName
	 */
	public String getFieldName() {
		return fieldName;
	}
	/**
	 * @param fieldName the fieldName to set
	 */
	public void setFieldName(String fieldName) {
		this.fieldName = fieldName;
	}
	/**
	 * @return the fieldLable
	 */
	public String getFieldLable() {
		return fieldLable;
	}
	/**
	 * @param fieldLable the fieldLable to set
	 */
	public void setFieldLable(String fieldLable) {
		this.fieldLable = fieldLable;
	}
	/**
	 * @return the fieldType
	 */
	public String getFieldType() {
		return fieldType;
	}
	/**
	 * @param fieldType the fieldType to set
	 */
	public void setFieldType(String fieldType) {
		this.fieldType = fieldType;
	}
	
	/**
	 * @return the isprimaryKey
	 */
	public boolean isIsprimaryKey() {
		return isprimaryKey;
	}
	/**
	 * @param isprimaryKey the isprimaryKey to set
	 */
	public void setIsprimaryKey(boolean isprimaryKey) {
		this.isprimaryKey = isprimaryKey;
	}
	/**
	 * @return the required
	 */
	public boolean isRequired() {
		return required;
	}
	/**
	 * @param required the required to set
	 */
	public void setRequired(boolean required) {
		this.required = required;
	}
	/**
	 * @return the fieldLength
	 */
	public int getFieldLength() {
		return fieldLength;
	}
	/**
	 * @param fieldLength the fieldLength to set
	 */
	public void setFieldLength(int fieldLength) {
		this.fieldLength = fieldLength;
	}
	/**
	 * @return the isvalidated
	 */
	public int getIsvalidated() {
		return isvalidated;
	}
	/**
	 * @param isvalidated the isvalidated to set
	 */
	public void setIsvalidated(int isvalidated) {
		this.isvalidated = isvalidated;
	}
	/**
	 * @return the id
	 */
	public int getId() {
		return id;
	}
	/**
	 * @param id the id to set
	 */
	public void setId(int id) {
		this.id = id;
	}
	/**
	 * @return the sortorder
	 */
	public String getSortorder() {
		return sortorder;
	}
	/**
	 * @param sortorder the sortorder to set
	 */
	public void setSortorder(String sortorder) {
		this.sortorder = sortorder;
	}
	
	

}
返回顶楼
          回帖地址
0 请登录后投票
  • yin_bp
  • 等级: 四钻会员
  • 性别:
  • 文章: 713
  • 积分: 1950
  • 来自: 长沙
   发表时间：2011-07-02  
bbossgroups框架提供的另外一个SQLExecutor组件的api实例文档为：
http://bbossgroups.group.iteye.com/group/wiki/3094-persistent
返回顶楼
          回帖地址
0 请登录后投票
  • yin_bp
  • 等级: 四钻会员
  • 性别:
  • 文章: 713
  • 积分: 1950
  • 来自: 长沙
   发表时间：2011-07-09  
bbossgroups项目新域名开通：
http://www.bbossgroups.com
返回顶楼
          回帖地址
0 请登录后投票
论坛首页 → Java企业应用版

跳转论坛:
Global site tag (gtag.js) - Google Analytics