1.查询某个字段的值
1.1:函数的使用-max:
String c = " select max(dbdh) dbdh from t_fhmx where length(trim(dbdh))=11 and dbdh like '"+newnum+"%' ";
String c1 =(String) daojcxs.executeQuery(c, new ColumnProcessor("dbdh"));
1.2:函数的使用-subStr:
String custcode =(String) dao.executeQuery("select subStr(custcode,2) from bd_cubasdoc where pk_cubasdoc='"+invbasdoc.getPk_srccubasdoc()+"'", new ColumnProcessor());
1.3:函数的使用-trim:
String sqlbx="select trim(bh) from t_bx";
List<String> bxlistvalue=(List<String>) getDMO().executeQuery(sqlbx, new ColumnListProcessor());
1.4:VO查询-BeanListProcessor:
String sql = "select * from ic_general_h where isnull( bistransjg,'N')='N' and isnull( freplenishflag,'N')='N' and pk_channel_v is not null and vjingaocode is null and cbilltypecode='4C'and isnull( dr,0)=0 AND CBIZTYPE = '0001A410000000002Q20'";
ArrayList<GeneralBillHeaderVO> hvointclist = (ArrayList<GeneralBillHeaderVO>) getDao().executeQuery(sql,new BeanListProcessor(GeneralBillHeaderVO.class));
1.5:VO查询-ColumnProcessor:
String sql = "select a.pk_cumandoc from bd_cubasdoc b left join bd_cumandoc a on a.pk_cubasdoc = b.pk_cubasdoc where a.dr=0 and b.dr=0 and b.custcode = '"
+ Code + "'";
String pk_cumandoc = (String) getBaseDao().executeQuery(sql, new ColumnProcessor());
1.6:VO查询-MapProcessor:
Map<String, Object> value = (Map<String, Object>) qryService
.executeQuery(
"select k.pk_invbasdoc,k.dcreatecatdate,i.invname,j.pk_invmandoc from (select pk_invbasdoc,dcreatecatdate from (select a.pk_invbasdoc,b.dcreatecatdate from hn_bookarch2inv a,bd_invbasdoc b where a.pk_invbasdoc=b.pk_invbasdoc and a.pk_bookarch=? and isnull(a.dr,0)=0 and isnull(b.dr,0)=0 order by a.boldstock,b.dcreatecatdate desc) where rownum=1) k,bd_invbasdoc i ,bd_invmandoc j where k.pk_invbasdoc=i.pk_invbasdoc and i.pk_invbasdoc=j.pk_invbasdoc and isnull(i.dr,0)=0 and isnull(j.dr,0)=0 and j.pk_corp=?",
parameter, new MapProcessor());
1.7:VO 查询-ArrayListProcessor:
List result = ((List) qryService.executeQuery("select pk_issue,vbookname from hn_bookarch where pk_bookarch=? and isnull(dr,0)=0", parameter,
new ArrayListProcessor()));
Object[] values = (Object[]) result.get(0);
1.8:VO查询-ColumnProcessor:
Integer value = (Integer) qryService.executeQuery("select count(1) from hn_bookarch2inv where pk_bookarch=? and isnull(dr,0)=0", parameter,
new ColumnProcessor());
String qryTsSql="select ts from hn_bookorder where pk_bookorder='"+pk_bookorder+"' and isnull(dr,0)=0";
Object tsVal=qryService.executeQuery(qryTsSql, new ColumnProcessor());
sql = "select dsubscridateto from hn_afsubsbill where pk_afsubsbill=? and isnull(dr,0)=0";
parameter.clearParams();
parameter.addParam(pk_linkbill);
Object afsDatato = qryService.executeQuery(sql, parameter, new ColumnProcessor());
bExceedExpireDate = UFBoolean.valueOf(loginDate.compareTo(UFDate.getDate(afsDatato.toString())) > 0);
19:VO查询-ResultSetProcessor:
StringBuilder sql = new StringBuilder();
sql.append("select ").append(bOrderBill ? "pk_bookarch" : "pk_cumandoc").append(",pk_cusorder,nordercount from hn_cusorder_b where isnull(dr,0)=0");
sql.append(" and ").append(Toolkit.getWherePartByKeys("pk_cusorder", pk_cusorder));
HashMap<String, Object> refDataMapInfo = (HashMap<String, Object>) qryService.executeQuery(sql.toString(), new ResultSetProcessor() {
sql=new StringBuilder("select c.pk_bookarch,b.pk_cumandoc, a.custname,a.pk_cubasdoc from hn_bookarch c, bd_cubasdoc a, bd_cumandoc b,").
append(innerTableSql).append(" d where c.pk_bookarch=d.pk_object and c.pk_supplier=a.pk_cubasdoc and a.pk_cubasdoc = b.pk_cubasdoc and b.pk_corp='").
append(InvocationInfoProxy.getInstance().getCorpCode()).append("' and (b.custflag='1' or b.custflag='3') and isnull(a.dr, 0) = 0 and isnull(b.dr, 0) = 0 and isnull(c.dr,0)=0").toString();
HashMap<String, HashMap<String, String>> tmpValues=(HashMap<String, HashMap<String, String>>) qryService.executeQuery(sql.toString(), new ResultSetProcessor() {
public Object handleResultSet(ResultSet rs) throws SQLException {
HashMap<String, HashMap<String, String>> value = new HashMap<String, HashMap<String, String>>();
while (rs.next()) {
HashMap<String, String> temp=new HashMap<String, String>();
temp.put("pk_srccubasdoc", (String)rs.getObject(2));
temp.put("srccubasdoc", (String)rs.getObject(3));
temp.put("pk_srccubasdoc1", (String)rs.getObject(4));
value
.put((String)rs.getObject(1), temp);
}
return value;
}
});
20:VO查询-SpecialProcessor:
sql = new StringBuilder("select a.pk_invbasdoc,a.invname,a.pk_srccubasdoc from bd_invbasdoc a,").append(innerTableSql).append(
" b where a.pk_invbasdoc=b.pk_object and isnull(a.dr,0)=0").toString();
HashMap<String, HashMap<String, Object>> invBasMap = (HashMap<String, HashMap<String, Object>>) qryService.executeQuery(sql, new SpecialProcessor());
for (HashMap<String, Object> inv : invBasMap.values())
srcCubasdoc.add((String) inv.get("pk_srccubasdoc"));
StringBuilder qrySrccubasdocSql = new StringBuilder(
"select a.pk_cubasdoc,a.custname,b.pk_cumandoc from bd_cubasdoc a,bd_cumandoc b where a.pk_cubasdoc=b.pk_cubasdoc and b.pk_corp='").append(
pk_corp).append("' and isnull(a.dr,0)=0 and isnull(b.dr,0)=0 and (b.custflag='1' or b.custflag='3') and ").append(
Toolkit.getWherePartByKeys("a.pk_cubasdoc", srcCubasdoc.toArray(new String[0])));
// 获取供应商信息
HashMap<String, HashMap<String, Object>> cusMap = (HashMap<String, HashMap<String, Object>>) qryService.executeQuery(qrySrccubasdocSql.toString(),
new SpecialProcessor());