nc57数据库语句笔记-查找

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());