菜单
登录注册
欢 迎
登录
自动登录
忘记密码?
新朋友
注册
注册
老朋友
登录
笔记内容为空!
TA的笔记树 >>
java基于spring的NamedParameterJdbcTemplate封装的获取sql工具类
Java工具类
本工具类用于NamedParameterJdbcTemplate的便捷获取sql操作 拷贝到项目中即可直接用,无任何其他依赖 ```java import java.lang.reflect.Field; import java.util.*; import java.util.function.Function; public class NamedSqlUtil { /** * 构建分页语句 * * @param limit 每页最大数据量(>0) * @param page 第几页(>=1) * @return limit xx offset xx */ public static String buildLimitOffset(int limit, int page) { if (limit <= 0 || page < 1) { return ""; } return String.format("limit %s offset %s", limit, (page - 1) * limit); } /** * 动态执行分页sql * * @param sourceSql 源sql,不含分页 * @param limit 每页最大数据量 * @param sqlHandler sql处理 * @return 执行页数 */ public static int executePageSql(String sourceSql, int limit, Function
sqlHandler) { if (sourceSql == null || sourceSql.isEmpty()) { return 0; } int currPage = 1; for (; ; ) { final String sql = sourceSql + "\n" + buildLimitOffset(limit, currPage); final Integer dataSize = sqlHandler.apply(sql); if (dataSize <= 0 || dataSize < limit) { break; } currPage++; } return currPage; } /** * 获取单表insert语句 * * @param poCls 表对应的po * @param paramMap 要插入的数据 * @return insert sql */ public static String sqlOfInsert(Class> poCls, Map
paramMap) { return sqlOfInsert(getTableName(poCls), paramMap); } /** * 获取单表insert语句 * * @param tableName 表名 * @param paramMap key须表中字段对应 * @return insert sql */ public static String sqlOfInsert(String tableName, Map
paramMap) { if (tableName == null || paramMap == null || paramMap.isEmpty()) { return null; } final StringBuilder sqlField = new StringBuilder(128); final StringBuilder sqlValue = new StringBuilder(128); for (String field : paramMap.keySet()) { sqlField.append(",").append(field); sqlValue.append(",:").append(field); } return "INSERT INTO " + tableName + "(" + sqlField.substring(1) + ") " + "VALUES(" + sqlValue.substring(1) + ")"; } /** * 根据实例对象,构建insert sql以及NamedJdbcTemplate所需的参数map * * @param po 对应表的实例对象 * @param dbType 为空字段是否需要放到sql中 * @param
实例对象类型 * @return sql + param */ public static
SqlInfo sqlOfInsert(T po, DBType dbType) { return sqlOfInsertCommon(po, getTableName(po.getClass()), dbType); } /** * 根据实例对象,构建insert sql以及NamedJdbcTemplate所需的参数map * * @param tableName 表名 * @param po 对应表的实例对象 * @param dbType 为空字段是否需要放到sql中 * @param
实例对象类型 * @return sql + param */ public static
SqlInfo sqlOfInsert(String tableName, T po, DBType dbType) { return sqlOfInsertCommon(po, getTableName(po.getClass()), dbType); } /** * 获取单表更新SQL语句,WHERE条件统一为 AND 连接(where 条件最少有一个) * * @param poCls 表名 * @param paramMap 需要更新值的字段 + 条件字段,key须与表中字段对应 * @param lastFieldIndex 最后一个<需要要更新值字段>的索引 * @return update sql */ public static String sqlOfUpdate(Class> poCls, LinkedHashMap
paramMap, int lastFieldIndex) { return sqlOfUpdate(getTableName(poCls), paramMap, lastFieldIndex); } /** * 获取单表更新SQL语句,WHERE条件统一为 AND 连接(where 条件最少有一个) * * @param tableName 表名 * @param paramMap 需要更新值的字段 + 条件字段,key须与表中字段对应 * @param lastFieldIndex 最后一个<需要要更新值字段>的索引 * @return update sql */ public static String sqlOfUpdate(String tableName, LinkedHashMap
paramMap, int lastFieldIndex) { if (tableName == null || paramMap == null || paramMap.isEmpty() || lastFieldIndex < 0 || lastFieldIndex >= (paramMap.size() - 1)) { return null; } final StringBuilder sqlField = new StringBuilder(128); final StringBuilder sqlCondition = new StringBuilder(128); int indexTemp = 0; for (String field : paramMap.keySet()) { if (indexTemp++ <= lastFieldIndex) { sqlField.append(",").append(field).append("=:").append(field); } else { sqlCondition.append(" AND ").append(field).append("=:").append(field); } } final StringBuilder sql = new StringBuilder(sqlField.length() + sqlCondition.length() + 64); sql.append("UPDATE ").append(tableName); sql.append(" SET "); sql.append(sqlField.substring(1)); if (sqlCondition.length() != 0) { sql.append(" WHERE"); sql.append(sqlCondition.substring(4)); } return sql.toString(); } /** * 获取删除表数据SQL语句 * * @param poCls 表名 * @param conditionMap 条件(最少一个) * @return delete sql */ public static String sqlOfDelete(Class> poCls, Map
conditionMap) { return sqlOfDelete(getTableName(poCls), conditionMap); } /** * 获取删除表数据SQL语句 * * @param tableName 表名 * @param conditionMap 条件(最少一个) * @return delete sql */ public static String sqlOfDelete(String tableName, Map
conditionMap) { if (tableName == null || conditionMap == null || conditionMap.isEmpty()) { return null; } return "DELETE FROM " + tableName + " WHERE " + buildAndCondition(conditionMap); } public static String sqlOfSelect(String tableName, String fieldInfo, Map
conditionMap) { return getSelectSQLCommon(tableName, fieldInfo, conditionMap); } public static String sqlOfSelect(Class> cls, Map
conditionMap) { return getSelectSQLCommon(getTableName(cls), buildDBColumn(cls), conditionMap); } public static String sqlOfSelect(Class> tableNameCls, String fieldInfo, Map
conditionMap) { return getSelectSQLCommon(getTableName(tableNameCls), fieldInfo, conditionMap); } private static String getSelectSQLCommon(String tableName, String fieldInfo, Map
conditionMap) { if (tableName == null || tableName.isEmpty() || fieldInfo == null || fieldInfo.isEmpty()) { return ""; } final int mapSize = conditionMap == null ? 0 : conditionMap.size(); final StringBuilder sql = new StringBuilder(64 + fieldInfo.length() + mapSize * 10); sql.append("SELECT ").append(fieldInfo).append(" FROM ").append(tableName); if (conditionMap != null) { sql.append(" WHERE ").append(buildAndCondition(conditionMap)); } return sql.toString(); } /** * 构建AND连接条件 * * @param conditionMap and条件参数 * @return and sql */ public static String buildAndCondition(Map
conditionMap) { if (conditionMap == null || conditionMap.isEmpty()) { return ""; } final StringBuilder sqlCondition = new StringBuilder(128); for (String condition : conditionMap.keySet()) { if (condition.equals("platform") && (Integer) conditionMap.get(condition) == 0) { sqlCondition.append(" AND ").append(condition).append(">=:").append(condition); } else { sqlCondition.append(" AND ").append(condition).append("=:").append(condition); } } return sqlCondition.substring(5); } /** * 构建in条件 * * @param fieldName 字段名 * @param coll List/Set 数据 * @return in sql */ public static String buildInCondition(final String fieldName, final Collection> coll) { if (coll == null || coll.isEmpty()) return null; final int size = coll.size(); final Object first = coll.iterator().next(); if (first instanceof Number) { if (size == 1) { return fieldName + "=" + first; } else { return fieldName + coll.toString().replace("[", " in(").replace("]", ")"); } } else { if (size == 1) { return String.format("%s='%s'", fieldName, first); } else { final StringBuilder sb = new StringBuilder(size * 10); coll.forEach(f -> sb.append(",").append("'").append(f).append("'")); return fieldName + " in(" + sb.substring(1) + ")"; } } } public static String buildDBColumn(Class> cls) { if (cls == null) { return null; } return buildDBColumn(cls.getDeclaredFields()); } private static String buildDBColumn(Field[] fs) { if (fs == null || fs.length == 0) { return null; } final StringBuilder result = new StringBuilder(); final char delim = '_'; final char delim2 = ','; String nameTemp; boolean isLowerCaseTemp; StringBuilder nameChangedTemp; for (Field f : fs) { nameTemp = f.getName(); nameChangedTemp = new StringBuilder(); for (char c : nameTemp.toCharArray()) { isLowerCaseTemp = Character.isDigit(c) || Character.isLowerCase(c); if (isLowerCaseTemp) { nameChangedTemp.append(c); } else { nameChangedTemp.append(delim).append(Character.toLowerCase(c)); } } result.append(delim2).append(nameChangedTemp); } return result.substring(1); } private static String getTableName(Class> cls) { return humpToUnderline(cls.getSimpleName()); } private static String humpToUnderline(String fieldName) { final char delim = '_'; final StringBuilder builder = new StringBuilder(fieldName.length() + 4); final char[] fnChars = fieldName.toCharArray(); for (int i = 0, len = fnChars.length; i < len; i++) { final Character c = fnChars[i]; if (i == 0) { builder.append(Character.toLowerCase(c)); } else { if (Character.isUpperCase(c)) { builder.append(delim).append(Character.toLowerCase(c)); } else { builder.append(c); } } } return builder.toString(); } private static
SqlInfo sqlOfInsertCommon(T po, String tableName, DBType dbType) { if (po == null) { return null; } final Class> cls = po.getClass(); if (tableName == null) { tableName = getTableName(cls); } if (dbType == null) { dbType = DBType.ALL; } final Field[] fs = cls.getDeclaredFields(); final int fsLen = fs.length; if (fsLen == 0) { return null; } final Map
paramMap = new LinkedHashMap<>(fsLen); final StringBuilder fieldBuilder = new StringBuilder(fsLen * 10); final StringBuilder valueBuilder = new StringBuilder(fsLen * 12); Field f; String fn; try { for (Field field : fs) { f = field; fn = f.getName(); if (!f.isAccessible()) { f.setAccessible(true); } Object fv = f.get(po); if (dbType == DBType.ALL || (dbType == DBType.NOT_NULL && fv != null)) { paramMap.put(fn, fv); fieldBuilder.append(",").append(fn); valueBuilder.append(",:").append(fn); } } } catch (IllegalAccessException e) { throw new RuntimeException(e); } String sqlBuilder = "INSERT INTO " + tableName + "(" + fieldBuilder.substring(1) + ")" + " VALUES(" + valueBuilder.substring(1) + ")"; return new SqlInfo(sqlBuilder, paramMap); } public static void main(String[] args) { LinkedHashMap
params = new LinkedHashMap<>(); params.put("int1", 123); params.put("int2", 456); params.put("date", new Date()); params.put("long1", 2345L); params.put("long2", 3456L); System.out.println("=========== insert ============"); System.out.println(sqlOfInsert("test", params)); System.out.println(sqlOfInsert(TestPo.class, params)); System.out.println("================================"); System.out.println("=========== update ============"); System.out.println(sqlOfUpdate("test", params, 2)); System.out.println(sqlOfUpdate(TestPo.class, params, 2)); System.out.println("================================"); System.out.println("=========== delete ============"); System.out.println(sqlOfDelete("test", params)); System.out.println(sqlOfDelete(TestPo.class, params)); System.out.println("================================"); System.out.println("=========== select =============="); System.out.println(sqlOfSelect("test", "id, username", params)); System.out.println(sqlOfSelect(TestPo.class, params)); System.out.println(sqlOfSelect(TestPo.class, "id, username", params)); System.out.println("=================================="); System.out.println("=========== buildAndCondition ============"); System.out.println(buildAndCondition(params)); System.out.println("=========================================="); System.out.println("=========== buildInCondition ============"); System.out.println(buildInCondition("id", Collections.singletonList(1))); System.out.println(buildInCondition("id", Arrays.asList(1, 2, 3, 4))); System.out.println(buildInCondition("id", Arrays.asList(1L, 2L))); System.out.println(buildInCondition("id", Collections.singletonList("a"))); System.out.println(buildInCondition("id", Arrays.asList("a", "b"))); System.out.println("=========================================="); System.out.println("=========== buildDBColumn ============"); System.out.println(buildDBColumn(TestPo.class)); System.out.println("======================================="); TestPo testPo = new TestPo(); testPo.int2 = 123; testPo.long1 = 111L; testPo.str = "test-value"; System.out.println("=========== insert by obj ============"); System.out.println(sqlOfInsert(testPo, DBType.ALL)); System.out.println(sqlOfInsert(testPo, DBType.NOT_NULL)); System.out.println(sqlOfInsert("test", testPo, DBType.ALL)); System.out.println(sqlOfInsert("test", testPo, DBType.NOT_NULL)); System.out.println("======================================="); } public enum DBType { NOT_NULL, ALL } public static class SqlInfo { private final String sql; private final Map
params; public SqlInfo(String sql, Map
params) { this.sql = sql; this.params = params; } public String getSql() { return sql; } public Map
getParams() { return params; } @Override public String toString() { return "sql: " + sql + " | params: " + params; } } /** * 测试用 */ private static class TestPo { private Integer int1; private int int2; private Long long1; private long long2; private String str; private Date date; } } ```
vanki
等待,不是为了你能回来,而是找个借口不离开。
浏览:
1438
创建:
2021-09-16 18:04:55
更新:
2021-09-16 18:19:18
TA的最新笔记
spring-boot配置redis多数据源
linux源修改(阿里)
python安装postgresql依赖
arthas使用
java基于spring的NamedParameterJdbcTemplate封装的获取sql工具类
Impala添加负载
S3常用使用
redis常用操作
hdfs相关命令
crontab使用
TA的最热笔记
java异步http请求工具类(org.asynchttpclient)
iTerm2主题配置与常用技巧
java基于spring.redisTemplate实现分布式锁工具类
Kotlin + SpringBoot + JPA(Hibernate) + Repository自定义方法
IDEA汉化
Sequel Pro连接mysql8打开数据库报错
centos-Hadoop2.7.3完全分布式搭建(HA)
SpringBoot上传文件报错(The temporary upload location [..] is not valid)
mac常用软件
kotlin对象属性值拷贝工具类