net.lulihu.jdbc.orm.DefaultSqlStatementExecutor Maven / Gradle / Ivy

Go to download
package net.lulihu.jdbc.orm;

import net.lulihu.ObjectKit.ClassKit;
import net.lulihu.ObjectKit.CollectionKit;
import net.lulihu.ObjectKit.LogKit;
import net.lulihu.dateTime.DateTimeKit;
import net.lulihu.exception.ToolBoxException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 默认Sql语句执行程序
 */
public class DefaultSqlStatementExecutor implements SqlStatementExecutor {

    private static final Logger log = LoggerFactory.getLogger(DefaultSqlStatementExecutor.class);

    @Override
    public Integer executeInsertAndReturnId(Connection connection, String sql, Object... params) throws SQLException {
        Long beforeExecute = null;
        Long afterExecute = null;
        int row = 0;

        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            // 替换命令占位符
            statementReplacement(ps, params);

            if (log.isDebugEnabled())
                beforeExecute = DateTimeKit.current(false);

            row = ps.executeUpdate();

            if (row != 1)
                throw new ToolBoxException("数据保存失败...");

            resultSet = ps.getGeneratedKeys();//获得主键的自增Id
            if (resultSet.next())//Id在结果集中的第一位
                return resultSet.getInt(1);

            if (log.isDebugEnabled())
                afterExecute = DateTimeKit.current(false);

        } finally {
            if (resultSet != null) {
                close(resultSet);
            }
            if (ps != null) {
                close(ps);
            }
            sqlLog(sql, beforeExecute, afterExecute, row, params);
        }
        throw new ToolBoxException("获取自增主键异常...");
    }

    @Override
    public int executeUpdate(Connection connection, String sql, Object... params) throws SQLException {
        Long beforeExecute = null;
        Long afterExecute = null;
        int row = 0;

        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(sql);
            // 替换命令占位符
            statementReplacement(ps, params);

            if (log.isDebugEnabled())
                beforeExecute = DateTimeKit.current(false);

            row = ps.executeUpdate();

            if (log.isDebugEnabled())
                afterExecute = DateTimeKit.current(false);

            return row;
        } finally {
            if (ps != null) {
                close(ps);
            }
            sqlLog(sql, beforeExecute, afterExecute, row, params);
        }
    }

    @Override
    public  T executeQueryOne(Connection connection, Class resultClass, String sql, Object... params) throws SQLException {
        List result = executeQuery(connection, new ArrayList<>(1), resultClass, sql, params);
        int size = result.size();
        if (size == 0) return null;
        if (size == 1) return result.get(0);
        throw new ToolBoxException("查询结果为多个无法封装至单个对象【{}】中...", resultClass);
    }

    @Override
    @SuppressWarnings("unchecked")
    public  List executeQueryList(Connection connection, Class resultListClass, Class resultClass, String sql, Object... params) throws SQLException {
        // 实例化返回集合对象
        List result = ClassKit.newInstanceConstructorsDefaultValue(resultListClass);
        // 封装查询结果
        result = executeQuery(connection, result, resultClass, sql, params);
        return result;
    }

    /**
     * 执行查询
     *
     * @param connection  数据库连接
     * @param result      结果集封装
     * @param resultClass 返回值类型
     * @param sql         sql命令
     * @param params      占位符替换参数
     * @return 查询结果
     */
    private  List executeQuery(Connection connection, List result, Class resultClass, String sql, Object... params) throws SQLException {
        Long beforeExecute = null;
        Long afterExecute = null;
        int row = 0;

        // 执行查询
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            ps = connection.prepareStatement(sql);
            statementReplacement(ps, params);

            if (log.isDebugEnabled())
                beforeExecute = DateTimeKit.current(false);

            resultSet = ps.executeQuery();

            if (log.isDebugEnabled())
                afterExecute = DateTimeKit.current(false);

            // 查询封装
            result = QueryResultSetKit.putResultSet(resultSet, result, resultClass);
            row = result.size();
            return result;
        } catch (Exception e) {
            if (e instanceof SQLException) throw (SQLException) e;
            throw new ToolBoxException(e);
        } finally {
            if (resultSet != null) {
                close(resultSet);
            }
            if (ps != null) {
                close(ps);
            }
            sqlLog(sql, beforeExecute, afterExecute, row, params);
        }
    }


    /**
     * 关闭
     *
     * @param resultSet ResultSet
     */
    private void close(ResultSet resultSet) {
        try {
            resultSet.close();
        } catch (Exception e) {
            LogKit.error(log, "结果集关闭错误...", e);
        }
    }

    /**
     * 关闭
     *
     * @param ps PreparedStatement
     */
    private void close(PreparedStatement ps) {
        try {
            ps.close();
        } catch (Exception e) {
            LogKit.error(log, "数据声明关闭错误...", e);
        }
    }

    /**
     * 打印日志
     *
     * @param sql           sql 声明
     * @param beforeExecute sql执行前的时间戳 毫秒
     * @param afterExecute  sql执行后的时间戳 毫秒
     * @param row           查询结果行数
     * @param params        参数
     */
    private void sqlLog(String sql, Long beforeExecute, Long afterExecute, int row, Object... params) {
        if (!log.isDebugEnabled()) return;

        // 参数拼接
        StringBuilder builder = new StringBuilder();
        if (CollectionKit.isNotEmpty(params)) {
            for (Object param : params) {
                boolean boo = (param == null);
                Object value = boo ? "" : param;
                String simpleName = boo ? "NULL" : param.getClass().getSimpleName();
                builder.append(", ").append(value).append("(").append(simpleName).append(")");
            }
            builder.delete(0, 2);
        }
        // 打印
        LogKit.debug(log, "Statement ==> : {}", beautifySQL(sql));
        LogKit.debug(log, "Params    ==> : {}", builder.toString());
        LogKit.debug(log, "Rows      ==> : {}", row);
        if (afterExecute != null && beforeExecute != null)
            LogKit.debug(log, "consuming ==> : {}/ms", afterExecute - beforeExecute);
    }

    /**
     * sql 美化
     */
    private String beautifySQL(String sql) {
        return sql.replaceAll("[\\s\n ]+", " ");
    }

    /**
     * sql 命令占位符替换
     *
     * @param ps     sql声明
     * @param params 参数
     * @throws SQLException 如果参数索引与SQL语句中的参数标记不对应;
     *                      如果发生数据库访问错误;在封闭的PreparedStatement上调用此方法，
     *                      或者给定对象的类型不明确 抛出异常
     */
    private void statementReplacement(PreparedStatement ps, Object... params) throws SQLException {
        if (params == null) return;

        for (int i = 0, len = params.length; i < len; i++) {
            ps.setObject(i + 1, params[i]);
        }
    }

    private DefaultSqlStatementExecutor() {
        //因为线程安全使用单例减少内存占用 私有化构造函数
    }

    private final static DefaultSqlStatementExecutor defaultSqlStatementExecutor;

    static {
        defaultSqlStatementExecutor = new DefaultSqlStatementExecutor();
    }

    public static synchronized DefaultSqlStatementExecutor getInstance() {
        return defaultSqlStatementExecutor;
    }


}