学习sql很好的例子

4A-server：UserDao

    /**
     * 根据角色和租户查询用户
     * @param roleIds
     * @param tenantId
     * @return
     */
    public List<User> getUserByRoleIdAndTenantId(List<String> roleIds, Long tenantId) {

        StringBuffer sql = new StringBuffer(128);
        sql.append("SELECT * FROM GAP_USER WHERE TENANT_ID = " + tenantId + "");
        sql.append(" AND ID IN (SELECT USER_ID FROM GAP_USER_ROLE_RULE WHERE ROLE_ID ");

        List<Object> params = new LinkedList<>();
        sql.append(" IN ( ");
        for (String roleId : roleIds) {
            sql.append(" ?, ");
            params.add(roleId);
        }
        sql.deleteCharAt(sql.length() - 2).append(" )");

        sql.append(")");
        return queryForList(sql.toString(), params.toArray(),new ComplexMapper(User.class));
    }

4A-server：RoleDao

public List<RolePo> getPage(int start, int end, Map<String,Object> param, String orgTypeId, long... tenantId) {
    StringBuffer sql = new StringBuffer(128);
    List<Object> params = new LinkedList<>();
    sql.append(" SELECT ").append(RolePo.BASE_COLUMNS).append(" , T.NAME as tenant_name FROM GAP_ROLE R ");
    sql.append(" LEFT JOIN GAP_SYSTEM_TENANT T ON T.ID =R.TENANT_ID ");
    sql.append(" WHERE R.IS_DELETED = 0 ");
    if (StringUtil.isNotNull(orgTypeId) && !"0".equals(orgTypeId)) {
        params.add(orgTypeId);
        sql.append(" AND R.ORG_TYPE_ID = ? ");
    }
    if (null != tenantId && tenantId.length > 0) {
        sql.append(" AND R.TENANT_ID IN ( ");
        for (long t : tenantId) {
            sql.append(" ?, ");
            params.add(t);
        }
        sql.deleteCharAt(sql.length() - 2).append(" ) ");
    }
    if (!CollectionUtils.isMapEmpty(param)){
        if (StringUtil.isNotNull(param.get("code"))){
            sql.append(" AND ( R.CODE LIKE '%" + param.get("code") + "%')");
        }
        if (StringUtil.isNotNull(param.get("tenantName"))){
            sql.append(" AND ( T.NAME LIKE '%" + param.get("tenantName") + "%')");
        }
        if (StringUtil.isNotNull(param.get("remark"))){
            sql.append(" AND ( R.REMARK LIKE '%" + param.get("remark") + "%')");
        }
        if (StringUtil.isNotNull(param.get("name"))){
            sql.append(" AND ( R.NAME LIKE '%" + param.get("name") + "%')");
        }
    }
    //sql.append(Utils.generateSqlSegment("R",param));
    sql.append(" ORDER BY R.CODE ASC ");
    return queryForList(sql.toString(),start, end, params.toArray(), new ComplexMapper(RolePo.class));
}