SQL Reader Class

SQLToDTOReader.java

package com.sql.reader

import java.util.List;
import java.util.Optional;

public interface SQLToDTOReader {

  <T> List<T> fetchList(String sql, Class<T> dto);

  <T> Optional<T> fetch(String sql, Class<T> dto);

  /**
   * @param query SQL queries with single column and single result only
   * @param resultType Return class name, Double, Integer, Long, String, LocalDate, LocalDateTime.
   * @return Optional of ResultType
   */

  <T> Optional<T> fetchSingleColumn(String query, Class<T> resultType);

  /**
   * @param query SQL query with single column
   * @param resultType Return class name, Double, Integer, Long, String, LocalDate, LocalDateTime.
   * @return List of ResultType
   */
  <T> List<T> fetchSingleColumnList(String query, Class<T> resultType);


}

SQLToDTOReaderImpl.java

package com.sql.reader

import com.sql.Util;
import com.google.common.base.CaseFormat;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.beanutils.BeanUtilsBean;
import org.apache.commons.beanutils.ConvertUtilsBean;
import org.postgresql.jdbc.PgResultSetMetaData;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.SingleColumnRowMapper;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.Assert;

import javax.sql.DataSource;
import java.lang.reflect.InvocationTargetException;
import java.sql.Date;
import java.sql.*;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.*;


/**
 * @author Samir Momin
 * @see SQLReaderImpl return DTO from SQL passed.
 * SQL column with '_' will be converted to camelcase properties and mapped to Dto Class and return as Optional<T> or List<T>
 * SQLReaderImpl get connection source from @DataSource bean in spring boot.
 */
@Slf4j
@Transactional(readOnly = true)
@Component
public class SQLToDTOReaderImpl implements SQLToDTOReader {

    @Autowired
    private DataSource dataSource;

    @Override
    public <T> List<T> fetchList(String sql, Class<T> clazz) {
        List<T> outList = new ArrayList<>();
        Map<String, Class<?>> propertiesMap = Util.convertToPropertyMapper(clazz);
        try (var statement = getConnection().prepareStatement(sql)) {
            log.info(sql);
            var resultSet = statement.executeQuery();
            while (resultSet.next()) {
                outList.add(autoMapModel(resultSet, dto, propertiesMap));
            }
        } catch (SQLException | InvocationTargetException | IllegalAccessException e) {
            e.printStackTrace();
        }
        return outList;
    }

    @Override
    public <T> Optional<T> fetch(String sql, Class<T> clazz) {
        Map<String, Class<?>> propertiesMap = Util.convertToPropertyMapper(clazz);
        try (var statement = getConnection().prepareStatement(sql)) {
            log.info(sql);
            var resultSet = statement.executeQuery();
            if (resultSet.next()) {
                return Optional.ofNullable(autoMapModel(resultSet, dto, propertiesMap));
            }
        } catch (EmptyResultDataAccessException | SQLException | InvocationTargetException | IllegalAccessException e) {
            e.printStackTrace();
        }
        return Optional.empty();
    }

    @Override
    public <T> Optional<T> fetchSingleColumn(String query, Class<T> resultType) {
        try (var statement = getConnection().prepareStatement(query)) {
            log.info(query);
            var resultSet = statement.executeQuery();
            if (resultSet.next()) {
                return Optional.ofNullable(new SingleColumnRowMapper<>(resultType).mapRow(resultSet, 0));
            } else {
                log.info("No result present in Database");
                return Optional.empty();
            }
        } catch (EmptyResultDataAccessException | SQLException e) {
            e.printStackTrace();
            log.info("Empty result exception, No record found in database for {}", e.getMessage());
        }
        return Optional.empty();
    }

    @Override
    public <T> List<T> fetchSingleColumnList(String query, Class<T> resultType) {
        try (var statement = getConnection().prepareStatement(query)) {
            Assert.notNull(getConnection(), "Connection is not null");
            log.info(query);
            var result = new ArrayList<T>();
            var resultSet = statement.getResultSet();
            while (resultSet.next()) {
                result.add(new SingleColumnRowMapper<>(resultType).mapRow(resultSet, 1));
            }
            return result;
        } catch (EmptyResultDataAccessException | SQLException e) {
            log.debug("No record found in database for {}", e.getMessage());
            return Collections.emptyList();
        }
    }

    protected Connection getConnection() throws SQLException {
        return DataSourceUtils.getConnection(dataSource);
    }

    private <T> T autoMapModel(ResultSet resultSet, Class<T> clazz, Map<String, Class<?>> propertiesMap) throws SQLException, InvocationTargetException, IllegalAccessException {
        PgResultSetMetaData metaData = (PgResultSetMetaData) resultSet.getMetaData();
        Map<String, Object> dataMap = new HashMap<>();

        for (int j = 1; j <= metaData.getColumnCount(); j++) {

            //this get label, if Label is pulled from modelToTable class alias will be like 'tableName_fieldName'
            String columnLabel = metaData.getColumnLabel(j);

            //now split attributeName with '_' and get field name, field name is in lower case.
            String columnProperty = CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.LOWER_CAMEL, columnLabel);
            //find Column name using attribute name
            String fieldName = propertiesMap.keySet().stream().filter(f -> f.equalsIgnoreCase(columnProperty)).findFirst()
                    .orElseThrow(() -> new IllegalArgumentException("Property not found with name " + columnProperty));

            // Map put value in to dataMap of by converting to data value.
            switch (metaData.getColumnType(j)) {
                case Types.TINYINT, Types.SMALLINT, Types.INTEGER -> {
                    int intValue = resultSet.getInt(columnLabel);
                    dataMap.put(fieldName, resultSet.wasNull() ? null : intValue);
                }
                case Types.BIGINT -> {
                    long longValue = resultSet.getLong(columnLabel);
                    boolean isNull = resultSet.wasNull();
                    dataMap.put(fieldName, isNull ? null : longValue);
                }
                case Types.FLOAT -> {
                    float floatValue = resultSet.getFloat(columnLabel);
                    dataMap.put(fieldName, resultSet.wasNull() ? null : floatValue);
                }
                case Types.REAL, Types.DOUBLE, Types.DECIMAL, Types.NUMERIC -> {
                    double doubleValue = resultSet.getDouble(columnLabel);
                    dataMap.put(fieldName, resultSet.wasNull() ? null : doubleValue);
                }
                case Types.CHAR, Types.VARCHAR, Types.LONGVARCHAR, Types.NCHAR, Types.NVARCHAR, Types.LONGNVARCHAR -> dataMap.put(fieldName, resultSet.getString(columnLabel));
                case Types.DATE -> dataMap.put(fieldName, date(resultSet.getDate(columnLabel)));
                case Types.TIME -> dataMap.put(fieldName, resultSet.getTime(columnLabel));
                case Types.TIME_WITH_TIMEZONE, Types.TIMESTAMP_WITH_TIMEZONE, Types.TIMESTAMP -> dataMap.put(fieldName, time(resultSet.getTimestamp(columnLabel)));
                case Types.BIT, Types.BOOLEAN -> dataMap.put(fieldName, resultSet.getBoolean(columnLabel));
                case Types.NULL -> dataMap.put(fieldName, null);
            }
        }

        return getInstance(dataMap, clazz);
    }

    @SuppressWarnings("unchecked")
    private <T> T getInstance(Map<String, Object> dataMap, Class<T> clazz) throws IllegalAccessException, InvocationTargetException {
        T t = getInstance(dto);

        BeanUtilsBean beanUtilsBean = new BeanUtilsBean(new ConvertUtilsBean() {
            @Override
            public Object convert(String value, Class clazzInstance) {
                if (clazz.isEnum()) {
                    return Enum.valueOf(clazzInstance, value.toUpperCase());
                } else {
                    return super.convert(value, clazzInstance);
                }
            }
        });
        beanUtilsBean.getConvertUtils().register(false, true, 0);
        beanUtilsBean.populate(t, dataMap);
        return t;
    }

    protected <T> T getInstance(Class<T> clazz) {
        try {
            return clazz.getDeclaredConstructor().newInstance();
        } catch (InstantiationException | IllegalAccessException | InvocationTargetException | NoSuchMethodException e) {
            e.printStackTrace();
            return null;
        }
    }


    public LocalDateTime time(java.sql.Timestamp date) {
        if (date == null) return null;
        return date.toLocalDateTime();
    }

    public LocalDate date(Date date) {
        if (date == null) return null;
        return date.toLocalDate();
    }
  
}

Util.java

package com.sql.reader

public class Util {
    public static <T> Map<String, Class<?>> convertToPropertyMapper(Class<T> clazz) {
        Map<String, Class<?>> fieldMap = new HashMap<>();

        List<Field> fields = new ArrayList<>();
        getFields(fields, clazz);

        for (Field f : fields) {
            fieldMap.put(f.getName(), f.getType());
        }
        return fieldMap;
    }
    
    private static <T> void getFields(List<Field> fields, Class<?> clazz) {
        while (clazz != Object.class) {
            fields.addAll(Arrays.asList(clazz.getDeclaredFields()));
            clazz = clazz.getSuperclass();
            getFields(fields, clazz);
        }
    }    
}