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<String, Integer> 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<String, ?> paramMap) {
        return sqlOfInsert(getTableName(poCls), paramMap);
    }

/**
     * 获取单表insert语句
     *
     * @param tableName 表名
     * @param paramMap  key须表中字段对应
     * @return insert sql
     */
    public static String sqlOfInsert(String tableName, Map<String, ?> 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 <T>    实例对象类型
     * @return sql + param
     */
    public static <T> 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 <T>       实例对象类型
     * @return sql + param
     */
    public static <T> 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<String, ?> 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<String, ?> 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<String, ?> conditionMap) {
        return sqlOfDelete(getTableName(poCls), conditionMap);
    }

/**
     * 获取删除表数据SQL语句
     *
     * @param tableName    表名
     * @param conditionMap 条件（最少一个）
     * @return delete sql
     */
    public static String sqlOfDelete(String tableName, Map<String, ?> 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<String, ?> conditionMap) {
        return getSelectSQLCommon(tableName, fieldInfo, conditionMap);
    }

public static String sqlOfSelect(Class<?> cls, Map<String, ?> conditionMap) {
        return getSelectSQLCommon(getTableName(cls), buildDBColumn(cls), conditionMap);
    }

public static String sqlOfSelect(Class<?> tableNameCls, String fieldInfo, Map<String, ?> conditionMap) {
        return getSelectSQLCommon(getTableName(tableNameCls), fieldInfo, conditionMap);
    }

private static String getSelectSQLCommon(String tableName, String fieldInfo, Map<String, ?> 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<String, ?> 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 <T> 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<String, Object> 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<String, Object> 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<String, ?> params;

public SqlInfo(String sql, Map<String, ?> params) {
            this.sql = sql;
            this.params = params;
        }

public String getSql() {
            return sql;
        }

public Map<String, ?> 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

有些人适合牵手，而有些人适合孤独地走。
愿岁月静好，
愿年华依旧，
愿有人用其一生。挽你衣袖！

创建： 2021-09-16 18:04:55

更新： 2021-09-16 18:19:18

java基于spring的NamedParameterJdbcTemplate封装的获取sql工具类 Java工具类

TA的最新笔记

TA的最热笔记

欢 迎

登录

新朋友 注册

注册

老朋友 登录

java基于spring的NamedParameterJdbcTemplate封装的获取sql工具类 Java工具类

TA的最新笔记

TA的最热笔记

欢迎

新朋友注册

老朋友登录