com.codingapi.springboot.fast.jdbc.JdbcQuery Maven / Gradle / Ivy

package com.codingapi.springboot.fast.jdbc;

import org.apache.commons.text.CaseUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JdbcQuery {

    private final org.springframework.jdbc.core.JdbcTemplate jdbcTemplate;

    public JdbcQuery(org.springframework.jdbc.core.JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    private static class CamelCaseRowMapper implements RowMapper> {

        @Override
        public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            Map map = new HashMap<>(columnCount);
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnLabel(i);
                map.put(CaseUtils.toCamelCase(columnName, false), rs.getObject(i));
            }
            return map;
        }
    }

    public List> queryForList(String sql, Object... params) {
        return jdbcTemplate.query(sql, params, new CamelCaseRowMapper());
    }

    public  List queryForList(String sql, Class clazz, Object... params) {
        return jdbcTemplate.query(sql, params, new BeanPropertyRowMapper<>(clazz));
    }

    public  Page queryForPage(String sql, String countSql, Class clazz, PageRequest pageRequest, Object... params) {
        List list = jdbcTemplate.query(sql, params, new BeanPropertyRowMapper<>(clazz));
        long count = this.countQuery(countSql, params);
        return new PageImpl<>(list, pageRequest, count);
    }

    public  Page queryForPage(String sql, Class clazz, PageRequest pageRequest, Object... params) {
        String countSql = "select count(1) "+sql;
        return this.queryForPage(sql, countSql, clazz, pageRequest, params);
    }

    public Page> queryForPage(String sql, String countSql, PageRequest pageRequest, Object... params) {
        List> list = jdbcTemplate.query(sql, params, new CamelCaseRowMapper());
        long count = this.countQuery(countSql, params);
        return new PageImpl<>(list, pageRequest, count);
    }

    public Page> queryForPage(String sql, PageRequest pageRequest, Object... params) {
        String countSql = "select count(1) "+sql;
        return this.queryForPage(sql, countSql, pageRequest, params);
    }


    private long countQuery(String sql, Object... params) {
        int paramsLength = params.length;
        int countSqlParamsLength = sql.split("\\?").length - 1;
        Object[] newParams = new Object[countSqlParamsLength];
        if (paramsLength > countSqlParamsLength) {
            System.arraycopy(params, 0, newParams, 0, countSqlParamsLength);
        }
        return jdbcTemplate.queryForObject(sql, newParams, Long.class);
    }
}