在項目中遇到了一種優化場景,既需要條件查詢、批量查詢,又需要判斷進行模糊查詢,同時,在某種特殊場景下,會查詢大量的資料,在不使用緩存的前提下,針對操作資料庫的層級,進行了優化,同時隻傳回固定資料,減輕資料庫的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上下波動,若再輔以緩存食用,口感更佳。