学习sql很好的例子
4A-server：UserDao
/**
* 根据角色和租户查询用户
* @param roleIds
* @param tenantId
* @return
*/
public List getUserByRoleIdAndTenantId(List 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 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 getPage(int start, int end, Map param, String orgTypeId, long... tenantId) {
StringBuffer sql = new StringBuffer(128);
List 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));
}
版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。
暂时没有评论，来抢沙发吧~