2024年4月26日发(作者：)

1、SQL 操作

1.1 SQl查询，区别客户端和服务端

StringBuffer sql = new StringBuffer("select sum(calamount)

as sumSubAmount from t_ht_subcontractbill t where actbillid ='");

(()).append("' ");

//

客户端

ISQLExecutor executor =

oteInstance(ng());

//

服务端

ISQLExecutor executor =

alInstance(ctx,ng());

IRowSet rs = eSQL();

if(())// while(())

{

}

//...

1.2 DataUtils使用，注:DbUtil很多模块定义了此工具类，请查看再使用

1.2.1 简单查询

String sql ="select from ct_ht_serviceproject

//

IRowSet rs=eQuery(ctx, sql);

while(()){

//...

}

StringBuffer sb = new StringBuffer();

(" select from ct_ht_serviceproject

(" and =? ");

(" and s =? ");

Object[] params = new Object[2];

params[0] = billID;//方法传递的参数

params[1] = status;//方法传递的参数

return eQuery(ctx, sql, params);

t1 ,CT_HT_ServiceInvitePro t2 where =ameID";

1.2.2 参数查询

t1 ,CT_HT_ServiceInvitePro t2 where =ameID ");

1.2.3 简单处理执行

StringBuffer sql = new StringBuffer(200);

(" /*dialect*/ update CT_HT_ServiceExpertLib t ");

(" set tiondepnumber =(select r from

(" where rtid

(" and tiondeptid is not null ");

t_org_admin a where = tiondeptid) ");

='").append(ert().getId().toString()).append("' ");

e(ctx,sql);

1.2.4 批处理参数传递执行

StringBuffer sb = new StringBuffer();

(" insert into t_table_xx values(?, ?) ");

List paramsList = new ArrayList();

Object[] params1 = new Object[2];

params1[0] = "test1";

params1[1] = "test2";

Object[] params2 = new Object[2];

params2[0] = "test1";

params2[1] = "test2";

(params2);

eBatch(ctx, ng(), paramsList);

//mapDate 为存放数据的Map对像

sql = "/*dialect*/call _procedure('"

+ ("monthBegin").toString() + "','"

+ ("monthEnd").toString() + "','"

+ ("yearBegin").toString() + "','"

+ ("monthEnd").toString() + "','"

+ cuId + "')";

1.2.5 存储过程执行

e(ctx,sql);

1.3 自定义工具类 SQL 更新、执行。

//eFacade

1.3.1 普通sql执行

StringBuffer sql4 = new StringBuffer(200);

(" /*dialect*/ update CT_HT_ServiceExpertLib t ");

(" set tiondepnumber =(select r from

(" where rtid

(" and tiondeptid is not null ");

//客户端

IUpdateFacade update=oteInstance();

//服务端

//IUpdateFacade

eUpdate(ng());

t_org_admin a where = tiondeptid) ");

='").append(ert().getId().toString()).append("' ");

update=alInstance(ctx);

1.3.2 批处理sql执行(解决客户端与服务端多次交互，及事务处理)

String sql1 = new String(" 测试sql ");

String sql2 = new String(" 测试sql ");

String sql3 = new String(" 测试sql ");

List sqlList = new ArrayList();

(sql1);

(sql2);

(sql3);

IUpdateFacade

eBatch(sqlList);

tion conn = getConnection(ctx);

CallableStatement proc = null;

int result = 0;

proc = eCall("{call proSetAuditFalse(?, ?)}");

ing(1, ing("id"));

erOutParameter(2, R);

e();

result = (2);

update=alInstance(ctx);

1.3.3 储存过程执行，通过服务端ctx获取conn，注意手工释放连接proc、conn

2、OQL 对象操作

2.1 EntityViewInfo实体过滤、排序

EntityViewInfo evi = new EntityViewInfo();//建立视图信息

FilterInfo filter = new FilterInfo();//建立过滤条件

ector().add(new SelectorItemInfo("id"));

ector().add(new SelectorItemInfo("*"));

ector().add(new SelectorItemInfo(""));

ector().add(new SelectorItemInfo(""));

2.1.2简单过滤

terItems().add(new

FilterItemInfo("level","3",));

terItems().add(new

terItems().add(new

kString("#0 and #1 and #2");

FilterItemInfo("number","JR%",));

FilterItemInfo("iscu",,));

2.1.3复杂过滤

2.1.3.1

使用

2.1.3.1.1设置过滤条件时使用子查询，该部分会放在where字句，相当于一个子查询，相当

于 in 使用

String sql = "select fid from t_table_xx where fxh1 ='444'";

terItems().add(new FilterItemInfo("id", sql,

));

2.1.3.1.2 数值类型为：（1，2，3）, 相当于 in 使用

String linkStr ="'1','2','3'";

terItems().add( new FilterItemInfo("id", linkStr,

2.1.1设置获取属性

));

2.1.3.2 enotInclude使用，则为 innot in

2.1.3.2.1 使用字符串，如“1，2，3”

String strValues

="+/b6ngKzRF+ckUP4iQUDcDscY4M=,+/b6ngKzRF+ckUP4iQUDcDscY4M=";

terItems().add(new FilterItemInfo("id", strValues,

E));

2.1.3.2.2 使用Set类型，建立一个Set对象，将值一个一个加入

Set values = new HashSet();

("+/b6ngKzRF+ckUP4iQUDcDscY4M=");

("+3YFJ/ZLR6e/NBK7XRDgMTscY4M=");

("+4Ljc5U5Ti2mGkFghvUStDscY4M=");

terItems().add(new FilterItemInfo("id", values,

E));

2.1.3.3 notEXISTS使用

String sql = "select fid from t_table_xx where fxh1 ='444'";

terItems().add(new FilterItemInfo("id", sql,

));

2.1.4 排序

SorterItemCollection sc = new SorterItemCollection();

SorterItemInfo sii =new SorterItemInfo("bizDate");

tType(D);

(sii);

//设置F7

ityViewInfo(evi);

ter(sc);

2.2 OQL 过滤、排序

IServiceAttachmenModelInviteEntry iModelEntry =

oteInstance();

StringBuffer oqls = new StringBuffer( "select * where parent ='");

(modelId).append("'");

(" bizDate >=");

("{ts'").append(beginDate).append('}");

(" order by ");

ServiceAttachmenModelInviteEntryCollection coll =

viceAttachmenModelInviteEntryCollection(n

g());

2.3 OQL 帮助

KSQL参考手册.pdf