J2EE SSH

[size=large]
public List getPage(ListForm listForm, int row, int limit)
throws HibernateException {

_log.trace("getPage enter.");
JchListForm jchListForm = (JchListForm) listForm;
if (_session == null) {
_session = DbSessionUtil.getSession();
}
StringBuffer hql = new StringBuffer();
boolean flag = false;[color=blue]
hql.append("SELECT ");
hql.append(" jch.jchDt,");
hql.append(" jch.destination.code1,");
hql.append(" jch.destination.code2,");
hql.append(" jch.destination.code3,");
hql.append(" jch.dstnNacNo,");
hql.append(" jch.jchNo,");
hql.append(" jch.tempShipNum,");
hql.append(" jch.status,");
hql.append(" SUM(jchItem.jchQtyFix),");
hql.append(" SUM(shipPlanItem.rcvRsvQty),");
hql.append(" SUM(shipPlanItem.stkRsvQty),");
hql.append(" SUM(shipPlanItem.nonRsvQty),");
hql.append(" SUM(shipPlanItem.actQty),");
hql.append(" MAX(shipPlanItem.delayFlg)");
hql.append(" FROM ");
hql.append(" Jch jch ");
hql.append(" INNER JOIN ");
hql.append(" jch.details jchItem ");
hql.append(" INNER JOIN ");
hql.append(" jchItem.shipPlanItem shipPlanItem ");
hql.append("WHERE ");
hql.append(" jchItem.jch.jchNo = jch.jchNo ");
hql.append(" AND jchItem.cmpltKb = 0 ");
hql.append(
" AND shipPlanItem.shipPlan.shipPlanNo = jch.shipPlan.shipPlanNo ");

if (jchListForm.getCompCd() != null
&& !jchListForm.getCompCd().trim().equals("")) {
hql.append("AND jch.compCd = '");
hql.append(jchListForm.getCompCd().trim());
hql.append("' ");
} else {
if (!jchListForm.getCompCdList().isEmpty()) {
List list = jchListForm.getCompCdList();
hql.append("AND jch.compCd in (");
for (int i = 0; i < list.size(); i++) {
hql.append("'");
hql.append(list.get(i).toString().trim());
if (i + 1 == list.size()) {
hql.append("') ");
} else {
hql.append("', ");
}
}
}
}


if (jchListForm.getListOrder().equals("1")
&& !jchListForm.getJchDt().trim().equals("")) {
hql.append("AND jch.jchDt <= '");
hql.append(
ConvertUtil.formatDateStr(jchListForm.getJchDt().trim(), 1));
hql.append("' ");
} else {

if (jchListForm.getJchDt() != null
&& !jchListForm.getJchDt().trim().equals("")) {
hql.append("AND jch.jchDt = '");
hql.append(
ConvertUtil.formatDateStr(
jchListForm.getJchDt().trim(),
1));
hql.append("' ");
}
}


if (jchListForm.getListOrder().equals("2")) {
if ((jchListForm.getCustomerCodeForm().getCd1() != null
&& !jchListForm.getCustomerCodeForm().getCd1().trim().equals(
""))) {
hql.append("AND jch.destination.code1 >= '");
hql.append(jchListForm.getCustomerCodeForm().getCd1().trim());
hql.append("' ");
}
if ((jchListForm.getCustomerCodeForm().getCd2() != null
&& !jchListForm.getCustomerCodeForm().getCd2().trim().equals(
""))) {
hql.append("AND jch.destination.code2 >= '");
hql.append(jchListForm.getCustomerCodeForm().getCd2().trim());
hql.append("' ");
}
if ((jchListForm.getCustomerCodeForm().getCd3() != null
&& !jchListForm.getCustomerCodeForm().getCd3().trim().equals(
""))) {
hql.append("AND jch.destination.code3 >= '");
hql.append(jchListForm.getCustomerCodeForm().getCd3().trim());
hql.append("' ");
}
} else {

if ((jchListForm.getCustomerCodeForm().getCd1() != null
&& !jchListForm.getCustomerCodeForm().getCd1().trim().equals(
""))) {
hql.append("AND jch.destination.code1 = '");
hql.append(jchListForm.getCustomerCodeForm().getCd1().trim());
hql.append("' ");
}
if ((jchListForm.getCustomerCodeForm().getCd2() != null
&& !jchListForm.getCustomerCodeForm().getCd2().trim().equals(
""))) {
hql.append("AND jch.destination.code2 = '");
hql.append(jchListForm.getCustomerCodeForm().getCd2().trim());
hql.append("' ");
}
if ((jchListForm.getCustomerCodeForm().getCd3() != null
&& !jchListForm.getCustomerCodeForm().getCd3().trim().equals(
""))) {
hql.append("AND jch.destination.code3 = '");
hql.append(jchListForm.getCustomerCodeForm().getCd3().trim());
hql.append("' ");
}
}


if (jchListForm.getListOrder().equals("3")
&& !jchListForm.getJchNo().trim().equals("")) {
hql.append("AND jch.jchNo >= '");
hql.append(jchListForm.getJchNo().trim());
hql.append("' ");
} else {

if (jchListForm.getJchNo() != null
&& !jchListForm.getJchNo().trim().equals("")) {
hql.append("AND jch.jchNo = '");
hql.append(jchListForm.getJchNo().trim());
hql.append("' ");
}
}


if (jchListForm.getInputPrsn() != null
&& !jchListForm.getInputPrsn().trim().equals("")) {
hql.append("AND jch.inputPrsn = '");
hql.append(jchListForm.getInputPrsn().trim());
hql.append("' ");
}


if (jchListForm.getExhibition() != null
&& !jchListForm.getExhibition().trim().equals("")) {
hql.append("AND jch.exhibition = '");
hql.append(jchListForm.getExhibition().trim());
hql.append("' ");
}


if (jchListForm.getJchModeKb() != null
&& !jchListForm.getJchModeKb().equals("")) {
hql.append("AND jch.jchModeKb = '");
hql.append(jchListForm.getJchModeKb().trim());
hql.append("' ");
}


if (jchListForm.isJchConfirmFlg()) {
hql.append("AND jch.jchConfirmFlg = 0 ");
}


if (jchListForm.getShipKb() != null
&& !jchListForm.getShipKb().toString().equals("")) {
hql.append("AND jchItem.shipKb = '");
hql.append(jchListForm.getShipKb().trim());
hql.append("' ");
}


if (jchListForm.getSalesPrsn() != null
&& !jchListForm.getSalesPrsn().toString().trim().equals("")) {
hql.append("AND jchItem.salesPrsn = '");
hql.append(jchListForm.getSalesPrsn().trim());
hql.append("' ");
}


if (jchListForm.getMlkKb() != null
&& !jchListForm.getMlkKb().toString().equals("")) {
hql.append("AND jchItem.mlkKb = '");
hql.append(jchListForm.getMlkKb().trim());
hql.append("' ");
}


if (jchListForm.getBrandCd() != null
&& !jchListForm.getBrandCd().toString().trim().equals("")) {
hql.append("AND jchItem.brand.brandCd = '");
hql.append(jchListForm.getBrandCd().trim());
hql.append("' ");
}


if (jchListForm.isNonRsvFlg()) {
hql.append("AND jchItem.status = '0' ");
}


if (jchListForm.isNonShipFlg()) {
hql.append("AND jchItem.status <> '9' ");
}

if (jchListForm.isDelayFlg()) {
hql.append("AND shipPlanItem.delayFlg = 1 ");
}


if (jchListForm.isStkRsvQty()) {
hql.append("AND shipPlanItem.stkRsvQty <> 0 ");
}


if (jchListForm.getListOrder().equals("1")) {
hql.append("ORDER BY ");
hql.append(" jch.jchDt DESC, ");
hql.append(" jch.destination.code1 ASC, ");
hql.append(" jch.destination.code2 ASC, ");
hql.append(" jch.destination.code3 ASC, ");
hql.append(" jch.jchNo ASC ");
}


if (jchListForm.getListOrder().equals("2")) {
hql.append("ORDER BY ");
hql.append(" jch.destination.code1 ASC, ");
hql.append(" jch.destination.code2 ASC, ");
hql.append(" jch.destination.code3 ASC, ");
hql.append(" jch.jchNo ASC ");
}


if (jchListForm.getListOrder().equals("3")) {
hql.append("ORDER BY ");
hql.append(" jch.jchNo ASC ");
}
hql.append("GROUP BY ");
hql.append(" jch.jchNo,");
hql.append(" jch.jchDt,");
hql.append(" jch.destination.code1,");
hql.append(" jch.destination.code2,");
hql.append(" jch.destination.code3,");
hql.append(" jch.tempShipNum,");
hql.append(" jch.status,");
hql.append(" jch.dstnNacNo");[color=blue][/color]

Query query = _session.createQuery(hql.toString());
_log.debug(query.getQueryString());

return query.setMaxResults(limit).setFirstResult(row).list();
}

[/size]
这是我老师的曾经在日本NTT国内分公司做过的一个项目写的一个HQL复查查询语句,今天我做项目的时候刚好要用到随机任选几个字段来查询数据库 其中刚好参考了这个方法,所以拿出来和大家分享