后台查询代码拼接问题~高手解答下

 @RequiresUser
    @At("/query")
    public Object query(@Param("keystr")String keystr,@Param("niandu")String niandu,  @Param("..")Pager pager) {
    	 Subject sub = SecurityUtils.getSubject();
	  	  User user = userService.dao().fetchLinks(userService.dao().fetch(User.class, Long.valueOf(sub.getPrincipal().toString())),null);
	  	  userService.dao().fetchLinks(user.getProfile(),null);
	  	  Cnd cnd = Cnd.where("1","=",1).and("niandu", "=",niandu).and("DANWEICODE", "=", user.getProfile().getDeptobj().getCode());
        // cnd = Strings.isBlank(keystr)? cnd.where("1","=",1):( cnd.where("mingcheng","like","%"+keystr+"%").or("baoshenxuhao","like","%"+keystr+"%").or("duifangdw","like","%"+keystr+"%"));
	  	cnd = Strings.isBlank(keystr)? cnd.and("1","=",1):( cnd.and("(mingcheng","like","%"+keystr+"%").or("baoshenxuhao","like","%"+keystr+"%").or("duifangdw","like","%"+keystr+"%)"));
	  	  System.out.println("DANWEICODE:"+user.getProfile().getDeptobj().getCode());
       System.out.println("cnd:"+cnd);
       cnd.desc("baoshenxuhao");
        QueryResult qr = new QueryResult();
        qr.setList(dao.query(Hetong.class, cnd, pager));
        System.out.println("cnd:"+cnd);
        pager.setRecordCount(dao.count(Hetong.class, cnd));
        qr.setPager(pager);
        return qr; //默认分页是第1页,每页20条
    }

select * from WINE_QCYS_HETONG WHERE 1=1 AND niandu='2017' AND DANWEICODE='70025' AND (mingcheng LIKE '%2017-%' OR baoshenxuhao LIKE '%2017-%' OR duifangdw LIKE '%2017-%') ORDER BY baoshenxuhao DESC
错误提示代码如下：[WARN ] 22:07:02.140 org.nutz.mvc.impl.processor.FailProcessor.process(FailProcessor.java:28) - Error@/hetong/query :
org.nutz.dao.DaoException: !Nutz SQL Error: 'SELECT * FROM (SELECT T.*, ROWNUM RN FROM ( SELECT * FROM wine_qcys_hetong WHERE 1=1 AND niandu='2017' AND DANWEICODE='70025' AND (mingcheng LIKE '%2016-%' OR baoshenxuhao LIKE '%2016-%' OR duifangdw LIKE '%2016-%)' ORDER BY baoshenxuhao DESC ) T WHERE ROWNUM <= 20) WHERE RN > 0 '
PreparedStatement:
'SELECT * FROM (SELECT T.*, ROWNUM RN FROM ( SELECT * FROM wine_qcys_hetong WHERE 1=? AND niandu=? AND DANWEICODE=? AND (mingcheng LIKE ? OR baoshenxuhao LIKE ? OR duifangdw LIKE ? ORDER BY baoshenxuhao DESC ) T WHERE ROWNUM <= 20) WHERE RN > 0 '
CaseMessage=ORA-00907: 缺失右括号

这个括号怎么加？

wx_2qdej6bq3uup10lk4o79 1楼•2679天前

select * from WINE_QCYS_HETONG WHERE 1=1 AND niandu='2017' AND DANWEICODE='70025' AND (mingcheng LIKE '%2017-%' OR baoshenxuhao LIKE '%2017-%' OR duifangdw LIKE '%2017-%') ORDER BY baoshenxuhao DESC
错误提示代码如下：[WARN ] 22:07:02.140 org.nutz.mvc.impl.processor.FailProcessor.process(FailProcessor.java:28) - Error@/hetong/query :
org.nutz.dao.DaoException: !Nutz SQL Error: 'SELECT * FROM (SELECT T.*, ROWNUM RN FROM ( SELECT * FROM wine_qcys_hetong WHERE 1=1 AND niandu='2017' AND DANWEICODE='70025' AND (mingcheng LIKE '%2016-%' OR baoshenxuhao LIKE '%2016-%' OR duifangdw LIKE '%2016-%)' ORDER BY baoshenxuhao DESC ) T WHERE ROWNUM <= 20) WHERE RN > 0 '
PreparedStatement:
'SELECT * FROM (SELECT T.*, ROWNUM RN FROM ( SELECT * FROM wine_qcys_hetong WHERE 1=? AND niandu=? AND DANWEICODE=? AND (mingcheng LIKE ? OR baoshenxuhao LIKE ? OR duifangdw LIKE ? ORDER BY baoshenxuhao DESC ) T WHERE ROWNUM <= 20) WHERE RN > 0 '
CaseMessage=ORA-00907: 缺失右括号

wendal

2楼•2679天前

看了N久总算看明白你想干啥了.

mingcheng开始的那个and条件, 你希望3个or条件是一个整体, 然后在mingcheng 前面尝试性地加了个括号...

那样写是不对的, 看下面的写法, 用Exps.

Cnd cnd = Cnd.where("niandu", "=",niandu).and("DANWEICODE", "=", user.getProfile().getDeptobj().getCode());
if (!Strings.isBlank(keystr)) {
    cnd.and(Exps.exps("mingcheng","like","%"+keystr+"%").or("baoshenxuhao","like","%"+keystr+"%").or("duifangdw","like","%"+keystr+"%)"));
}
cnd.desc("baoshenxuhao");

PS: 不需要写1=1啦

wendal

3楼•2679天前

上面写错了, 是Cnd.exps方法

Cnd cnd = Cnd.where("niandu", "=",niandu).and("DANWEICODE", "=", user.getProfile().getDeptobj().getCode());
if (!Strings.isBlank(keystr)) {
    cnd.and(Cnd.exps("mingcheng","like","%"+keystr+"%").or("baoshenxuhao","like","%"+keystr+"%").or("duifangdw","like","%"+keystr+"%)"));
}
cnd.desc("baoshenxuhao");

请先登陆