| 提交 | 用户 | age | ||
| 826b66 | 1 | package com.hx.mybatisTool; |
| C | 2 | |
| 3 | import com.hx.util.StringUtils; | |
| 4 | ||
| 5 | import java.util.List; | |
| 6 | import java.util.Map; | |
| 7 | ||
| 8 | /** | |
| 9 | * Sql工具类 | |
| 10 | * @author fwq | |
| 11 | */ | |
| 12 | public class SqlStringTool { | |
| 13 | ||
| 14 | /**基础key*/ | |
| 15 | private static final String BASE_KEY = "k"; | |
| 16 | /**AND符号*/ | |
| 17 | private static final String AND_STR = " AND "; | |
| 18 | /**左边括号拼接*/ | |
| 19 | private static final String LEFT_STR = "#{m."; | |
| 20 | /**右边括号拼接*/ | |
| 21 | private static final String RIGHT_STR = "}"; | |
| 22 | /**逗号分隔*/ | |
| 23 | private static final String SIGN_STR = ","; | |
| 24 | /**IN左符号*/ | |
| 25 | private static final String IN_LEFT = " IN ( "; | |
| 26 | /**IN右符号*/ | |
| 27 | private static final String IN_RIGHT = " ) "; | |
| 28 | ||
| 29 | /** | |
| 30 | * 递归key,直到不重复,这个递归是针对同一个sqlMap,防止同方法多个查询时存在key覆盖问题。 | |
| 31 | * PS:建议查询完上一次,清理sqlMap后再传递进来,减少递归的操作 | |
| 32 | */ | |
| 33 | private static String getKey(String key, Map<String, Object> sqlMap) { | |
| 34 | if (sqlMap.get(key) != null) { | |
| 35 | getKey(BASE_KEY + key, sqlMap); | |
| 36 | } | |
| 37 | return key; | |
| 38 | } | |
| 39 | ||
| 40 | /** | |
| 41 | * 拼接sql语句 | |
| 42 | * @param sql StringBuilder的sql语句 | |
| 43 | * @param tableColumn 含别名表字段,例如:user AS u 根据id查询时传递u.id | |
| 44 | * @param dataList 要循环的数值,List集合 | |
| 45 | * @param sqlMap sql语句对应的参数Map | |
| 46 | */ | |
| 47 | public static void handleList(StringBuilder sql, String tableColumn, List<String> dataList, Map<String, Object> sqlMap) { | |
| 48 | if (sql == null || StringUtils.isEmpty(tableColumn) || dataList == null || dataList.size() < 1) { | |
| 49 | return; | |
| 50 | } | |
| 51 | String key = null; | |
| 52 | sql.append(AND_STR).append(tableColumn).append(IN_LEFT); | |
| 53 | for (int i = 0; i < dataList.size(); i++) { | |
| 54 | key = getKey(BASE_KEY + i, sqlMap); | |
| 55 | sql.append(LEFT_STR).append(key).append(RIGHT_STR).append(SIGN_STR); | |
| 56 | sqlMap.put(key, dataList.get(i)); | |
| 57 | } | |
| 58 | sql.deleteCharAt(sql.length() - 1).append(IN_RIGHT); | |
| 59 | } | |
| 60 | ||
| 61 | ||
| 62 | /** | |
| 63 | * 拼接sql语句 | |
| 64 | * @param sql StringBuffer的sql语句 | |
| 65 | * @param tableColumn 含别名表字段,例如:user AS u 根据id查询时传递u.id | |
| 66 | * @param dataList 要循环的数值,List集合 | |
| 67 | * @param sqlMap sql语句对应的参数Map | |
| 68 | */ | |
| 69 | public static void handleList(StringBuffer sql, String tableColumn, List<String> dataList, Map<String, Object> sqlMap) { | |
| 70 | if (sql == null || StringUtils.isEmpty(tableColumn) || dataList == null || dataList.size() < 1) { | |
| 71 | return; | |
| 72 | } | |
| 73 | String key = null; | |
| 74 | sql.append(AND_STR).append(tableColumn).append(IN_LEFT); | |
| 75 | for (int i = 0; i < dataList.size(); i++) { | |
| 76 | key = getKey(BASE_KEY + i, sqlMap); | |
| 77 | sql.append(LEFT_STR).append(key).append(RIGHT_STR).append(SIGN_STR); | |
| 78 | sqlMap.put(key, dataList.get(i)); | |
| 79 | } | |
| 80 | sql.deleteCharAt(sql.length() - 1).append(IN_RIGHT); | |
| 81 | } | |
| 82 | ||
| 83 | /** | |
| 84 | * 拼接sql语句 | |
| 85 | * @param sql StringBuilder的sql语句 | |
| 86 | * @param tableColumn 含别名表字段,例如:user AS u 根据id查询时传递u.id | |
| 87 | * @param dataList 要循环的数值,String数组 | |
| 88 | * @param sqlMap sql语句对应的参数Map | |
| 89 | */ | |
| 90 | public static void handleList(StringBuilder sql, String tableColumn, String[] dataList, Map<String, Object> sqlMap) { | |
| 91 | if (sql == null || StringUtils.isEmpty(tableColumn) || dataList == null || dataList.length < 1) { | |
| 92 | return; | |
| 93 | } | |
| 94 | String key = null; | |
| 95 | sql.append(AND_STR).append(tableColumn).append(IN_LEFT); | |
| 96 | for (int i = 0; i < dataList.length; i++) { | |
| 97 | key = getKey(BASE_KEY + i, sqlMap); | |
| 98 | sql.append(LEFT_STR).append(key).append(RIGHT_STR).append(SIGN_STR); | |
| 99 | sqlMap.put(key, dataList[i]); | |
| 100 | } | |
| 101 | sql.deleteCharAt(sql.length() - 1).append(IN_RIGHT); | |
| 102 | } | |
| 103 | ||
| 104 | /** | |
| 105 | * 拼接sql语句 | |
| 106 | * @param sql StringBuffer的sql语句 | |
| 107 | * @param tableColumn 含别名表字段,例如:user AS u 根据id查询时传递u.id | |
| 108 | * @param dataList 要循环的数值,String数组 | |
| 109 | * @param sqlMap sql语句对应的参数Map | |
| 110 | */ | |
| 111 | public static void handleList(StringBuffer sql, String tableColumn, String[] dataList, Map<String, Object> sqlMap) { | |
| 112 | if (sql == null || StringUtils.isEmpty(tableColumn) || dataList == null || dataList.length < 1) { | |
| 113 | return; | |
| 114 | } | |
| 115 | String key = null; | |
| 116 | sql.append(AND_STR).append(tableColumn).append(IN_LEFT); | |
| 117 | for (int i = 0; i < dataList.length; i++) { | |
| 118 | key = getKey(BASE_KEY + i, sqlMap); | |
| 119 | sql.append(LEFT_STR).append(key).append(RIGHT_STR).append(SIGN_STR); | |
| 120 | sqlMap.put(key, dataList[i]); | |
| 121 | } | |
| 122 | sql.deleteCharAt(sql.length() - 1).append(IN_RIGHT); | |
| 123 | } | |
| 124 | ||
| 125 | /** | |
| 126 | * 拼接sql语句 | |
| 127 | * @param sql StringBuilder的sql语句 | |
| 128 | * @param tableColumn 含别名表字段,例如:user AS u 根据id查询时传递u.id | |
| 129 | * @param dataList 要循环的数值,Map数组对象 | |
| 130 | * @param dataListKey Map数组对象中要循环值对应的key | |
| 131 | * @param sqlMap sql语句对应的参数Map | |
| 132 | */ | |
| 133 | public static void handleList(StringBuilder sql, String tableColumn, List<Map<String, Object>> dataList, String dataListKey, Map<String, Object> sqlMap) { | |
| 134 | if (sql == null || StringUtils.isEmpty(tableColumn) || dataList == null || dataList.size() < 1) { | |
| 135 | return; | |
| 136 | } | |
| 137 | String key = null; | |
| 138 | sql.append(AND_STR).append(tableColumn).append(IN_LEFT); | |
| 139 | for (int i = 0; i < dataList.size(); i++) { | |
| 140 | key = getKey(BASE_KEY + i, sqlMap); | |
| 141 | sql.append(LEFT_STR).append(key).append(RIGHT_STR).append(SIGN_STR); | |
| 142 | sqlMap.put(key, dataList.get(i).get(dataListKey)); | |
| 143 | } | |
| 144 | sql.deleteCharAt(sql.length() - 1).append(IN_RIGHT); | |
| 145 | } | |
| 146 | ||
| 147 | /** | |
| 148 | * 拼接sql语句 | |
| 149 | * @param sql StringBuffer的sql语句 | |
| 150 | * @param tableColumn 含别名表字段,例如:user AS u 根据id查询时传递u.id | |
| 151 | * @param dataList 要循环的数值,Map数组对象 | |
| 152 | * @param dataListKey Map数组对象中要循环值对应的key | |
| 153 | * @param sqlMap sql语句对应的参数Map | |
| 154 | */ | |
| 155 | public static void handleList(StringBuffer sql, String tableColumn, List<Map<String, Object>> dataList, String dataListKey, Map<String, Object> sqlMap) { | |
| 156 | if (sql == null || StringUtils.isEmpty(tableColumn) || dataList == null || dataList.size() < 1) { | |
| 157 | return; | |
| 158 | } | |
| 159 | String key = null; | |
| 160 | sql.append(AND_STR).append(tableColumn).append(IN_LEFT); | |
| 161 | for (int i = 0; i < dataList.size(); i++) { | |
| 162 | key = getKey(BASE_KEY + i, sqlMap); | |
| 163 | sql.append(LEFT_STR).append(key).append(RIGHT_STR).append(SIGN_STR); | |
| 164 | sqlMap.put(key, dataList.get(i).get(dataListKey)); | |
| 165 | } | |
| 166 | sql.deleteCharAt(sql.length() - 1).append(IN_RIGHT); | |
| 167 | } | |
| 168 | ||
| 169 | ||
| 170 | } | |
| 171 | ||
| 172 | ||
