多个约束条件的查询

最新推荐文章于 2021-11-22 13:58:53 发布

原创最新推荐文章于 2021-11-22 13:58:53 发布 · 910 阅读

0 ·

CC 4.0 BY-SA版权

SQL 专栏收录该内容

3 篇文章

订阅专栏

 public List getTodayData(String userId, String userName, String deptCode, String startTime, String endTime, boolean pagination) {
        String sql = "select userid,username,deptcode,deptname,to_char(logintime,'yyyy-MM-dd') as logintime from t_import_export_temp where 1=1";
        List paramList = new ArrayList();
        if (StringUtils.isNotBlank(userId)) {
            sql += " and userId=?";
            paramList.add(userId);
        }
        if (StringUtils.isNotBlank(userName)) {
            sql += (" and userName like ?");
            paramList.add("%" + userName + "%");
        }
        if (StringUtils.isNotBlank(deptCode)) {
            sql += " and deptCode=?";
            paramList.add(deptCode);
        }
        if (StringUtils.isNotBlank(startTime)) {
            sql += " and loginTime >=to_date(?,'yyyy-mm-dd')";//oracle的to_date方法
            paramList.add(startTime);
        }
        if (StringUtils.isNotBlank(endTime)) {
            sql += "  and loginTime <=to_date(?,'yyyy-mm-dd')";
            paramList.add(endTime);
        }
        sql += " order by loginTime desc";
        return (pagination) ? getJdbcTemplateWithPagination().queryForListWithPagination(sql, paramList.toArray()) :
                getJdbcTemplate().queryForList(sql, paramList.toArray());
    }