页面历史

import smartbi.freequery.metadata.SQLPart;

import smartbi.freequery.util.SQLPartType;

import smartbi.util.StringUtil;

public class Process1000LimitOfIn extends Function {

	@Override

	public boolean checkParams() {

		if (params.length==2)

 
			return true;

		else
			return false;
	}
	@Override
	public void execute() {

		String fieldName  = params[0].getSqlStr().trim();

		fieldName = trim(fieldName,"\"");

		fieldName = trim(fieldName,"'");

		String fieldValue = params[1].getSqlStr().trim();

		String sep = ",";

		String[] fieldValueArray = fieldValue.split(sep);

		int size = 1000;

		long count = Math.round(Math.ceil(((double)fieldValueArray.length)/size));

		if(count ==1){

			result.add(new SQLPart(SQLPartType.SQLSTR, fieldName + " in (" + fieldValue +")"));

			return;

		}
		//TODO

 
		StringBuilder sqlSb = new StringBuilder();

		sqlSb.append("(");

		for(int i =0; i<count; i++){

			int jLen = (i+1)*size ;

			jLen = (jLen>fieldValueArray.length)?fieldValueArray.length:jLen;

			StringBuilder sb = new StringBuilder();

			for(int j = i*size; j<jLen; j++){

				sb.append(fieldValueArray[j]).append(sep);

			}
			String sbStr = sb.toString();

			if(i>0){

				sqlSb.append(" or ");

			}
			sqlSb.append(fieldName + " in (" + sbStr.substring(0, sbStr.length()-sep.length())+")");

		}
		sqlSb.append(" ) ");

		result.add(new SQLPart(SQLPartType.SQLSTR, sqlSb.toString()));

	}
	
	private String trim(String srcStr, String toTrimStr){

		if(StringUtil.isNullOrEmpty(srcStr)){

			return srcStr;

		}
		srcStr = srcStr.trim();

		while(srcStr.startsWith(toTrimStr)){

			srcStr = srcStr.substring(toTrimStr.length());

			srcStr = srcStr.trim();

		}
		while(srcStr.endsWith(toTrimStr)){

			srcStr = srcStr.substring(0, srcStr.length()-toTrimStr.length());

			srcStr = srcStr.trim();

		}
		return srcStr;
	}
	@Override
	public String getMDXValue() {

		return "";

	}
}

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.sql.Timestamp;

import org.apache.log4j.Logger;

import smartbi.repository.UpgradeTask;

import smartbi.util.DBType;

/**
 *

 
 */

public class UpgradeTask_New extends UpgradeTask {

	/** */

	private static final Logger LOG = Logger.getLogger(UpgradeTask_New.class);

	@Override

	public boolean doUpgrade(Connection conn, DBType type) {

		PreparedStatement prep = null;

		try {

			Statement sta = conn.createStatement();

			ResultSet rs = sta.executeQuery("select c_resid from t_restree where c_resid = 'func_Process1000LimitOfIn'");

			if(!rs.next()){

				prep = conn.prepareStatement("insert into t_restree(c_resid, c_resname, c_resalias, c_pid, c_restype, c_order, c_perm, c_resdesc, c_created, c_lastmodified) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

				prep.setString(1, "func_Process1000LimitOfIn");

				prep.setString(2, "Process1000LimitOfIn");

				prep.setString(3, "Process1000LimitOfIn");

				prep.setString(4, "catalog_string");

				prep.setString(5, "FUNCTION");

				prep.setInt(6, 9000);

				prep.setString(7, "<Permissions inherited=\"YES\" role=\"ADMINS\"><Permission perm=\"READ\" descend=\"FOLDER_FILE\"/></Permissions>");

				prep.setString(8, "处理多选树参数或下拉框参数超过1000时报错问题，如Process1000LimitOfIn(\"A\",下拉参数)。<br/> 参数1：要过滤的字段名（如 table1.A），如果是可视化查询直接拖字段，如果是原生sql，字段最好加英文双引号,如\"table1.A\"；参数2：树参数或下拉框参数 <br/>返回值：每超过1000个值生成一个or(如(A in (a,b,c。。。) or A in (d,e,f。。。)) )");

				prep.setTimestamp(9, new Timestamp(new java.util.Date().getTime()));

				prep.setTimestamp(10, new Timestamp(new java.util.Date().getTime()));

				prep.executeUpdate();

				prep.close();

			}
			rs.close();

			sta.close();

			return true;

		} catch (SQLException e) {

			LOG.error("Upgrade UpgradeTask_New '" + this.getClass().getPackage().getName() + "' to " + getNewVersion() + " fail.", e);

			return false;

		}
	}
	@Override
	public String getNewVersion() {

		return "0.0.1";

	}

}