• Home
• io.github.shmilyjxs
• jdb-tool
• 1.35
• source code
• BaseSqlDao.java

×

Project download

Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $

You can buy this project and download/modify it how often you want.

io.github.shmilyjxs.core.impl.BaseSqlDao Maven / Gradle / Ivy

package io.github.shmilyjxs.core.impl;

import com.google.common.collect.Lists;
import io.github.shmilyjxs.utils.PageResult;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.util.LinkedCaseInsensitiveMap;

import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

public abstract class BaseSqlDao extends BaseNativeDao {

    private static final Logger logger = LoggerFactory.getLogger(BaseSqlDao.class);

    private static final String SELECT_PREFIX = "SELECT * FROM ";

    private static final String DELETE_PREFIX = "DELETE FROM ";

    private static final int SAFE_SIZE = 1000;

    private static Map.Entry buildSql(String prefix, String tableName, Map columnMap, String... orderBy) {
        Collection valueList = Collections.emptyList();
        StringBuilder stringBuilder = new StringBuilder(prefix);
        stringBuilder.append(tableName);
        if (ObjectUtils.isNotEmpty(columnMap)) {
            stringBuilder.append(" WHERE ");
            stringBuilder.append(columnMap.keySet().stream().map(e -> e.concat(" = ?")).collect(Collectors.joining(" AND ")));
            valueList = columnMap.values();
        }
        orderSql(stringBuilder, orderBy);
        return new AbstractMap.SimpleImmutableEntry<>(stringBuilder.toString(), valueList.toArray());
    }

    private static  Map.Entry> buildSql(String prefix, String tableName, String columnName, Collection columnValues, String... orderBy) {
        StringBuilder stringBuilder = new StringBuilder(prefix);
        stringBuilder.append(tableName);
        stringBuilder.append(" WHERE ");
        Map paramMap;
        List values = columnValues.stream().distinct().collect(Collectors.toList());
        if (values.size() > SAFE_SIZE) {
            List> partitionList = Lists.partition(values, SAFE_SIZE);
            Map map = new LinkedHashMap<>(partitionList.size());
            String str = IntStream.range(0, partitionList.size()).mapToObj(index -> {
                String key = columnName + (index + 1);
                map.put(key, partitionList.get(index));
                return columnName + " IN (:" + key + ")";
            }).collect(Collectors.joining(" OR ", "( ", " )"));
            paramMap = map;
            stringBuilder.append(str);
        } else if (values.size() > 1) {
            paramMap = Collections.singletonMap(columnName, values);
            stringBuilder.append(columnName).append(" IN (:").append(columnName).append(")");
        } else {
            paramMap = Collections.singletonMap(columnName, values.iterator().next());
            stringBuilder.append(columnName).append(" = :").append(columnName);
        }
        orderSql(stringBuilder, orderBy);
        return new AbstractMap.SimpleImmutableEntry<>(stringBuilder.toString(), paramMap);
    }

    private static void orderSql(StringBuilder stringBuilder, String... orderBy) {
        Optional.ofNullable(orderBy)
                .filter(e -> e.length > 0)
                .map(e -> Arrays.stream(e).filter(StringUtils::isNotBlank).collect(Collectors.toList()))
                .filter(e -> e.size() > 0)
                .ifPresent(e -> {
                    stringBuilder.append(" ORDER BY ");
                    stringBuilder.append(String.join(" , ", e));
                });
    }

    @Override
    public int insert(String tableName, Map columnMap) {
        if (ObjectUtils.isNotEmpty(columnMap)) {
            StringBuilder stringBuilder = new StringBuilder("INSERT INTO ");
            stringBuilder.append(tableName);
            stringBuilder.append(columnMap.keySet().stream().collect(Collectors.joining(" , ", " ( ", " ) ")));
            stringBuilder.append("VALUES");
            stringBuilder.append(columnMap.keySet().stream().map(e -> "?").collect(Collectors.joining(" , ", " ( ", " ) ")));
            return nativeUpdate(stringBuilder.toString(), columnMap.values().toArray());
        }
        return 0;
    }

    @Override
    public int update(String tableName, Map columnMap, String columnName) {
        return update(tableName, columnMap, Collections.singleton(columnName));
    }

    @Override
    public int update(String tableName, Map columnMap, Collection columns) {
        if (ObjectUtils.isNotEmpty(columnMap)) {
            if (ObjectUtils.isNotEmpty(columns)) {
                Map map = new LinkedCaseInsensitiveMap<>(columnMap.size());
                map.putAll(columnMap);
                Map whereMap = columns.stream().map(String::toLowerCase).distinct().collect(Collectors.toMap(e -> e, map::remove));
                return update(tableName, map, whereMap);
            }
        }
        return 0;
    }

    @Override
    public int update(String tableName, Map columnMap, Map whereMap) {
        if (ObjectUtils.isNotEmpty(columnMap)) {
            if (ObjectUtils.isNotEmpty(whereMap)) {
                Map map = new LinkedCaseInsensitiveMap<>(whereMap.size());
                map.putAll(whereMap);
                Set sets = columnMap.keySet();
                Set keys = sets.stream()
                        .filter(map::containsKey)
                        .filter(e -> Objects.equals(columnMap.get(e), map.get(e)))
                        .collect(Collectors.toSet());
                Map setMap = new LinkedHashMap<>();
                sets.stream().filter(e -> keys.stream().noneMatch(key -> Objects.equals(key, e))).forEach(e -> setMap.put(e, columnMap.get(e)));
                if (ObjectUtils.isNotEmpty(setMap)) {
                    StringBuilder stringBuilder = new StringBuilder("UPDATE ");
                    stringBuilder.append(tableName);
                    stringBuilder.append(" SET ");
                    stringBuilder.append(setMap.keySet().stream().map(e -> e.concat(" = ?")).collect(Collectors.joining(" , ")));
                    stringBuilder.append(" WHERE ");
                    stringBuilder.append(map.keySet().stream().map(e -> e.concat(" = ?")).collect(Collectors.joining(" AND ")));
                    return nativeUpdate(stringBuilder.toString(), ArrayUtils.addAll(setMap.values().toArray(), map.values().toArray()));
                }
            }
        }
        return 0;
    }

    @Override
    public  int delete(String tableName, String columnName, C columnValue) {
        return delete(tableName, Collections.singletonMap(columnName, columnValue));
    }

    @Override
    public int delete(String tableName, Map columnMap) {
        if (ObjectUtils.isNotEmpty(columnMap)) {
            Map.Entry entry = buildSql(DELETE_PREFIX, tableName, columnMap);
            return nativeUpdate(entry.getKey(), entry.getValue());
        }
        return 0;
    }

    @Override
    public  int batchDelete(String tableName, String columnName, Collection columnValues) {
        if (ObjectUtils.isNotEmpty(columnValues)) {
            Map.Entry> entry = buildSql(DELETE_PREFIX, tableName, columnName, columnValues);
            logger.info("sql = {}", entry.getKey());
            logger.info("paramMap = {}", entry.getValue());
            return getNamedJdbcTemplate().update(entry.getKey(), entry.getValue());
        }
        return 0;
    }

    @Override
    public  T getBean(String tableName, String columnName, C columnValue, Class mappedClass) {
        return getBean(tableName, Collections.singletonMap(columnName, columnValue), mappedClass);
    }

    @Override
    public  T getBean(String tableName, Map columnMap, Class mappedClass) {
        if (ObjectUtils.isNotEmpty(columnMap)) {
            Map.Entry entry = buildSql(SELECT_PREFIX, tableName, columnMap);
            return selectBean(entry.getKey(), mappedClass, entry.getValue());
        }
        return null;
    }

    @Override
    public  List getBeans(String tableName, String columnName, Collection columnValues, Class mappedClass, String... orderBy) {
        if (ObjectUtils.isNotEmpty(columnValues)) {
            Map.Entry> entry = buildSql(SELECT_PREFIX, tableName, columnName, columnValues, orderBy);
            logger.info("sql = {}", entry.getKey());
            logger.info("paramMap = {}", entry.getValue());
            return getNamedJdbcTemplate().query(entry.getKey(), entry.getValue(), new BeanPropertyRowMapper<>(mappedClass));
        }
        return Collections.emptyList();
    }

    @Override
    public  List getBeans(String tableName, Map columnMap, Class mappedClass, String... orderBy) {
        Map.Entry entry = buildSql(SELECT_PREFIX, tableName, columnMap, orderBy);
        return selectBeans(entry.getKey(), mappedClass, entry.getValue());
    }

    @Override
    public  PageResult selectPage(String tableName, Map columnMap, long pageNum, long pageSize, Class mappedClass, String... orderBy) {
        Map.Entry entry = buildSql(SELECT_PREFIX, tableName, columnMap, orderBy);
        return selectPage(entry.getKey(), pageNum, pageSize, mappedClass, entry.getValue());
    }

    @Override
    public  List downRecursiveSql(String tableName, String startColumn, C columnValue, String joinColumn, Class mappedClass, String... orderBy) {
        String sql = getDBType().getDialect().downRecursiveSql(tableName, startColumn, joinColumn);
        StringBuilder stringBuilder = new StringBuilder(sql);
        orderSql(stringBuilder, orderBy);
        return selectBeans(stringBuilder.toString(), mappedClass, columnValue);
    }

    @Override
    public  List upRecursiveSql(String tableName, String startColumn, C columnValue, String joinColumn, Class mappedClass, String... orderBy) {
        String sql = getDBType().getDialect().upRecursiveSql(tableName, startColumn, joinColumn);
        StringBuilder stringBuilder = new StringBuilder(sql);
        orderSql(stringBuilder, orderBy);
        return selectBeans(stringBuilder.toString(), mappedClass, columnValue);
    }

    @Override
    public  Map getMap(String tableName, String columnName, C columnValue) {
        return getMap(tableName, Collections.singletonMap(columnName, columnValue));
    }

    @Override
    public Map getMap(String tableName, Map columnMap) {
        if (ObjectUtils.isNotEmpty(columnMap)) {
            Map.Entry entry = buildSql(SELECT_PREFIX, tableName, columnMap);
            return selectMap(entry.getKey(), entry.getValue());
        }
        return null;
    }

    @Override
    public  List> getList(String tableName, String columnName, Collection columnValues, String... orderBy) {
        if (ObjectUtils.isNotEmpty(columnValues)) {
            Map.Entry> entry = buildSql(SELECT_PREFIX, tableName, columnName, columnValues, orderBy);
            logger.info("sql = {}", entry.getKey());
            logger.info("paramMap = {}", entry.getValue());
            return getNamedJdbcTemplate().queryForList(entry.getKey(), entry.getValue());
        }
        return Collections.emptyList();
    }

    @Override
    public List> getList(String tableName, Map columnMap, String... orderBy) {
        Map.Entry entry = buildSql(SELECT_PREFIX, tableName, columnMap, orderBy);
        return selectList(entry.getKey(), entry.getValue());
    }

    @Override
    public PageResult> selectPage(String tableName, Map columnMap, long pageNum, long pageSize, String... orderBy) {
        Map.Entry entry = buildSql(SELECT_PREFIX, tableName, columnMap, orderBy);
        return selectPage(entry.getKey(), pageNum, pageSize, entry.getValue());
    }

    @Override
    public  List> downRecursiveSql(String tableName, String startColumn, C columnValue, String joinColumn, String... orderBy) {
        String sql = getDBType().getDialect().downRecursiveSql(tableName, startColumn, joinColumn);
        StringBuilder stringBuilder = new StringBuilder(sql);
        orderSql(stringBuilder, orderBy);
        return selectList(stringBuilder.toString(), columnValue);
    }

    @Override
    public  List> upRecursiveSql(String tableName, String startColumn, C columnValue, String joinColumn, String... orderBy) {
        String sql = getDBType().getDialect().upRecursiveSql(tableName, startColumn, joinColumn);
        StringBuilder stringBuilder = new StringBuilder(sql);
        orderSql(stringBuilder, orderBy);
        return selectList(stringBuilder.toString(), columnValue);
    }
}