云之家审批接口请求后台案列

 oracle语法

package com.kingdee.eas.custom.xzkingdee;

 

import org.apache.log4j.Logger;

import javax.ejb.*;
import java.rmi.RemoteException;
import java.sql.SQLException;

 
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.kingdee.bos.*;
import com.kingdee.bos.util.BOSObjectType;
import com.kingdee.bos.metadata.IMetaDataPK;
import com.kingdee.bos.metadata.rule.RuleExecutor;
import com.kingdee.bos.metadata.MetaDataPK;
//import com.kingdee.bos.metadata.entity.EntityViewInfo;
import com.kingdee.bos.framework.ejb.AbstractEntityControllerBean;
import com.kingdee.bos.framework.ejb.AbstractBizControllerBean;
//import com.kingdee.bos.dao.IObjectPK;
import com.kingdee.bos.dao.IObjectValue;
import com.kingdee.bos.dao.IObjectCollection;
import com.kingdee.bos.service.ServiceContext;
import com.kingdee.bos.service.IServiceContext;

import java.io.Serializable;
import java.lang.String;
import com.kingdee.eas.common.EASBizException;
import com.kingdee.eas.csinterface.syncdatabase.SQLFacadeFactory;
import com.kingdee.jdbc.rowset.IRowSet;

public class BankFacadeControllerBean extends AbstractBankFacadeControllerBean implements Serializable
{
    private static Logger logger =
        Logger.getLogger("com.kingdee.eas.custom.xzkingdee.BankFacadeControllerBean");

	@Override
	protected String _getBankaccounts(Context ctx, String json)
			throws BOSException, EASBizException {
		
		
		 //云之家参数解析：
	     JSONObject yunjsonObj = JSONObject.parseObject(json);
	     String keyword = yunjsonObj.getString("keyword");//搜索关键字
	     Integer pageNumber = Integer.parseInt(yunjsonObj.getString("curPage"));//当前页码
	     
	      StringBuffer sqlcanshuo = new  StringBuffer();
	      if("".equals(keyword)){
	    	  sqlcanshuo.append(" where 1=1 ");;
	      }else{
	    	  sqlcanshuo.append(" where FNAME_L2 like '%"+keyword+"%' ");;
	      }

		 JSONObject jsonObject = new JSONObject();//最外层json对象
		 JSONObject pageList = new JSONObject();
		
		 
		 //列名
		 JSONArray colList = new JSONArray();//id
		 JSONObject  fidColL = new JSONObject();
		 fidColL.put("colEnName", "id");
		 JSONObject  nameColL = new JSONObject();//酒
		 nameColL.put("showName", "true");
		 nameColL.put("colZhName", "酒名称");
		 nameColL.put("widgetType", "textWidget");
		 nameColL.put("colEnName", "name");
		 JSONObject  priceColL = new JSONObject();//单价
		 priceColL.put("colZhName", "单价");
		 priceColL.put("widgetType", "moneyWidget");
		 priceColL.put("colEnName", "price");
		 colList.add(fidColL);
		 colList.add(nameColL);
		 colList.add(priceColL);
		 
	 
		StringBuffer sql = new StringBuffer("  select    count(1)  count  from T_BAS_EntertainWine ");
		sql.append(sqlcanshuo);
		//查询总记录数
		IRowSet rowcount = SQLFacadeFactory.getLocalInstance(ctx).getRowset(sql.toString());
		Integer totalRecouds =0;
		try {
			while(rowcount.next()){
				totalRecouds =rowcount.getInt("count");
			}
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		Integer pageSize=7; //每页显示多少条数据
		 //totalPages 总页数 
		Integer totalPages=totalRecouds%pageSize==0?totalRecouds/pageSize:totalRecouds/pageSize+1;
		//startPage 起始位置 
		Integer startPage= pageSize*(pageNumber-1)+1;
		Integer endPage=startPage+pageSize;
        //sql  拼接分页
		
		//查询数据
	    StringBuffer sqlinfo = new StringBuffer("  select    rownum num ,t.*  from T_BAS_EntertainWine t     ");
	    sqlinfo.append(sqlcanshuo);
	    String  sqlfy= "  select a.* from  (  "+sqlinfo+"  )  a where a.num>='"+startPage+"' and a.num<'"+endPage+"' ";
		IRowSet rowSet = SQLFacadeFactory.getLocalInstance(ctx).getRowset(sqlfy.toString());
		net.sf.json.JSONArray dataList = new net.sf.json.JSONArray();
		String Id = "";
		String FNAME_L2 = "";
		String PRICE = "";
		String TYPE = "";
		try {
			while (rowSet.next()) {
				Id = rowSet.getString("FID");
				FNAME_L2 = rowSet.getString("FNAME_L2");
				PRICE = rowSet.getString("FPRICE");
				TYPE = rowSet.getString("FTYPE");
				JSONObject jsonObj = new JSONObject();
				jsonObj.put("id", Id);
				jsonObj.put("name", FNAME_L2);
				jsonObj.put("price", PRICE);
				dataList.add(jsonObj);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		
		 JSONArray defaultValue = new JSONArray();//默认值
		 JSONObject defaultjsonObj = new JSONObject();
		 defaultjsonObj.put("id", "001");
		 defaultjsonObj.put("name", "洋河大曲");
		 defaultjsonObj.put("price", "10");
		 defaultValue.add(defaultjsonObj);
		 
		 pageList.put("pageCount", totalPages);
		 pageList.put("curPage", pageNumber);
		 pageList.put("rowsCount", 7);
			
		 pageList.put("colList", colList);
		 pageList.put("dataList", dataList);
		 pageList.put("defaultValue", defaultValue);
		  
		 jsonObject.put("pageList", pageList);
		 jsonObject.put("error", "null");
		 jsonObject.put("errorCode", 0);
		 jsonObject.put("success", true);
		 
		
		return jsonObject.toString();
	 
	}
    
    
    
    
}

sqlserver 语法

	/**
	 *  根据项目返回该项目的楼栋列表
	 * @return
	 * @throws BOSException 
	 * @throws EASBizException 
	 */
	public  JSONObject   bulidingsByproject   (  Context ctx,   JSONObject yunjsonObj  ) throws EASBizException, BOSException{
	     
	     Integer pageNumber = Integer.parseInt(yunjsonObj.getString("curPage"));//当前页码
	     String fid =  yunjsonObj.getString("id") ; 
	     StringBuffer sqlcanshuo = new  StringBuffer();
	     
		 JSONObject jsonObject = new JSONObject();//最外层json对象
		 JSONObject pageList = new JSONObject();
		 
	    StringBuffer sql0 = new StringBuffer("   select   fid,   FName_l2   as  楼栋名称 ,   CFAllArea   as  总面积  ,   CFRentArea  as 可出租面积     ,  CFInRentArea  as  在租面积    from   CT_BAS_Building   where  CFProjectID='"+fid+"' ");  
		StringBuffer sql = new StringBuffer("  select    count(1)  count  from   ").append(" (  "+sql0+"  )   as a");
		sql.append(sqlcanshuo);
		//查询总记录数
		IRowSet rowcount = SQLFacadeFactory.getLocalInstance(ctx).getRowset(sql.toString());
		Integer totalRecouds =0;
		try {
			while(rowcount.next()){
				totalRecouds =rowcount.getInt("count");
			}
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		Integer pageSize=10; //每页显示多少条数据
		 //totalPages 总页数 
		Integer totalPages=totalRecouds%pageSize==0?totalRecouds/pageSize:totalRecouds/pageSize+1;
		//startPage 起始位置 
		Integer startPage= pageSize*(pageNumber-1)+1;
		Integer endPage=startPage+pageSize;
        //sql  拼接分页
		
		//查询数据
	    StringBuffer sqlinfo = new StringBuffer("  select   row_number() over(order by t.fid) as   num ,t.*  from  ( "+sql0+" ) as  t     ");
	    sqlinfo.append(sqlcanshuo);
	    String  sqlfy= "     select a.* from  (  "+sqlinfo+"  ) as   a where a.num>='"+startPage+"' and a.num<'"+endPage+"'    ";
	    
	    IRowSet rowSet = SQLExecutorFactory.getLocalInstance(ctx, sqlfy).executeSQL();
		net.sf.json.JSONArray dataList = new net.sf.json.JSONArray();
		String buildId = ""; 
		String buildName = ""; 
		String rentArea = ""; 
		String inRentArea = ""; 
		String allarea = ""; 
		 
		try {
			while (rowSet.next()) {
				buildName = rowSet.getString("楼栋名称");
				allarea = rowSet.getString("总面积");
				rentArea = rowSet.getString("可出租面积");
				inRentArea = rowSet.getString("在租面积");
				buildId = rowSet.getString("fid");
			  
				JSONObject jsonObj = new JSONObject();
				jsonObj.put("buildId", buildId);
				jsonObj.put("buildName", buildName);
				if(null==rentArea) rentArea="0"	 ;
				if(null==allarea) allarea="0"	 ;
				if(null==inRentArea) inRentArea="0"	 ;
					
				 
			    BigDecimal rentAreaDecimal = new BigDecimal(rentArea) ;
			    BigDecimal allareaDecimal = new BigDecimal(allarea) ;
			    BigDecimal inRentAreaDecimal = new BigDecimal(inRentArea) ;
				jsonObj.put("rentArea", rentAreaDecimal.doubleValue()); 
				jsonObj.put("allarea", allareaDecimal.doubleValue()); 
				jsonObj.put("inRentArea", inRentAreaDecimal.doubleValue()); 
				dataList.add(jsonObj);
				
			}
		  } catch (SQLException e) {
			e.printStackTrace();
		 }
		 pageList.put("pageCount", totalPages);
		 pageList.put("curPage", pageNumber);
		 pageList.put("rowsCount", totalRecouds);
		 /*pageList.put("colList", colList);*/
		 pageList.put("dataList", dataList);
		 pageList.put("totalRecouds", totalRecouds);
		 jsonObject = new JSONObject();
		 jsonObject.put("pageList", pageList);
//		 jsonObject.put("error", "null");
//		 jsonObject.put("errorCode", 0);
//		 jsonObject.put("success", true);
		 return jsonObject ;
		 
	}