在项目中遇到了一种优化场景,既需要条件查询、批量查询,又需要判断进行模糊查询,同时,在某种特殊场景下,会查询大量的数据,在不使用缓存的前提下,针对操作数据库的层级,进行了优化,同时只返回固定数据,减轻数据库的I/O负担。代码如下:
特别说明:考虑过使用jdbcTemplate拼接SQL后的查询效率和使用Mybatis的方式进行比较,但个人比较喜欢jdbcTemplate的方式,高效且灵活,而且支持的数据量也较大,唯一的遗憾就是拼接SQL时,会占据些些内存,不如Mybatis直接替换灵活。
这样说起来,就见仁见智了,各位有好的建议欢迎评论。
......
String sql = "select app_id, name from app where (";
Map<String, Object> params = new HashMap<>();
if(StringUtils.isNotBlank(searchAppName)){
sql += " name = :name ";
// 拼接使用,相当于like操作,进行模糊查询
params.put("name", "%" + searchAppName + "%");
}
if (!userInnerMgr.isLoginUserSuperAdmin()) {
List<String> appIds = null;
// 用户是否是超级管理员
boolean isIsvAdmin = /** 此处是业务代码,判断权限 **/;
if(!sql.endsWith("(")) {
sql += " and ";
}
if(StringUtils.isNotBlank(isvId)){
sql += "isv_id = :isvId ";
params.put("isv", isvId);
}
// 普通用户查询自己的权限
if (!isIsvAdmin && "true".equals(PropertyUtil.getPropertyByKey(DA_ENABLE))) {
try {
// 查询自己的权限
appIds = daService.fetchAuthAppIds(auth, null);
if(!sql.endsWith("(")) {
sql += " and ";
}
if(StringUtils.isNotBlank(userId)){
sql += "user_id = :userId ";
params.put("userId", userId);
}
if(sql.endsWith("(")) {
sql = sql.substring(0, sql.length() - 1);
} else {
sql += ")";
}
if(null != appIds){
if(appIds.size() > 0) {
// 在in操作后面使用变量名替换,进行批量查询
sql += " or app_id in (:appIds)";
params.put("appIds", appIds);
}
}
} catch (Exception e) {
logger.error("查询权限报错:[{}]", e);
}
}
}
if(sql.endsWith("where (")){
sql = sql.substring(0, sql.indexOf("where ("));
}else{
if(!sql.endsWith(")")){
sql += ")";
}
}
// 至此,拼接SQL完成,并且准备好了每个SQL可能存在的条件及对应的参数
// 重中之重,使用NamedParameterJdbcTemplate,以支持变量名替换的查询方法
NamedParameterJdbcTemplate jdbc = new NamedParameterJdbcTemplate(jdbcTemplate);
List<Map<String, Object>> resultMaps = jdbc.queryForList(sql, params);
......
至此,解决需求。
最后,说下优化结果,原本查询1W+的数据,耗时2s多点,通过这里的优化,第一次查询耗时0.8s~0.5s,再次查询将控制在0.2s以下,基本在0.1s上下波动,若再辅以缓存食用,口感更佳。