com.centit.support.database.utils.DatabaseAccess Maven / Gradle / Ivy

Go to download
package com.centit.support.database.utils;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.centit.support.algorithm.*;
import org.apache.commons.codec.binary.Base64;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

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

@SuppressWarnings("unused")
public abstract class DatabaseAccess {

    protected static final Logger logger = LoggerFactory.getLogger(DatabaseAccess.class);

    private DatabaseAccess() {
        throw new IllegalAccessError("Utility class");
    }

    public static SQLException createAccessException(String sql, SQLException e) {
        SQLException exception = new SQLException(sql + " raise " + e.getMessage(), e.getSQLState(), e.getErrorCode(), e.getCause());
        exception.setNextException(e.getNextException());
        exception.setStackTrace(e.getStackTrace());
        return exception;
    }

    private static Object transObjectForSqlParam(Object param) {
        // 避免重复转换
        if (param instanceof java.sql.Date) {
            return param;
        } else if (param instanceof java.util.Date ||
            param instanceof java.time.LocalDate || param instanceof java.time.LocalDateTime) {
            return DatetimeOpt.convertToSqlTimestamp(
                DatetimeOpt.castObjectToDate(param));
        }

        Class paramClass = param.getClass();
        if (Boolean.class.isAssignableFrom(paramClass)) {
            return BooleanBaseOpt.castObjectToBoolean(param, false) ?
                BooleanBaseOpt.ONE_CHAR_TRUE : BooleanBaseOpt.ONE_CHAR_FALSE;
        } else if (paramClass.isEnum()) {
            return EnumBaseOpt.enumToOrdinal(param);
        } else if (ReflectionOpt.isScalarType(paramClass) || param instanceof byte[]) {
            return param;
        } else {
            return JSON.toJSONString(param);
        }
    }

    /**
     * 调用数据库函数
     *
     * @param conn      数据库链接
     * @param procName  procName
     * @param sqlType   返回值类型
     * @param paramObjs paramObjs
     * @return 调用数据库函数
     * @throws SQLException SQLException
     */
    public static Object callFunction(Connection conn, String procName, int sqlType, Object... paramObjs)
        throws SQLException {
        int n = paramObjs.length;
        StringBuilder procDesc = new StringBuilder("{?=call ");
        procDesc.append(procName).append("(");
        for (int i = 0; i < n; i++) {
            if (i > 0)
                procDesc.append(",");
            procDesc.append("?");
        }
        procDesc.append(")}");
        String sqlCen = procDesc.toString();
        QueryLogUtils.printSql(logger, sqlCen);
        try (CallableStatement stmt = conn.prepareCall(sqlCen)) {
            stmt.registerOutParameter(1, sqlType);
            for (int i = 0; i < n; i++) {
                if (paramObjs[i] == null) {
                    stmt.setNull(i + 2, Types.NULL);
                } else {
                    stmt.setObject(i + 2,
                        transObjectForSqlParam(paramObjs[i]));
                }
            }
            stmt.execute();
            return stmt.getObject(1);
        } catch (SQLException e) {
            throw DatabaseAccess.createAccessException(sqlCen, e);
        }
    }

    /**
     * 执行一个存储过程
     *
     * @param conn      数据库链接
     * @param procName  procName
     * @param paramObjs procName
     * @return 返回是否成功
     * @throws SQLException SQLException
     */
    public static boolean callProcedure(Connection conn, String procName, Object... paramObjs)
        throws SQLException {
        int n = paramObjs.length;
        StringBuilder procDesc = new StringBuilder("{call ");
        procDesc.append(procName).append("(");
        for (int i = 0; i < n; i++) {
            if (i > 0) {
                procDesc.append(",");
            }
            procDesc.append("?");
        }
        procDesc.append(")}");
        String sqlCen = procDesc.toString();
        QueryLogUtils.printSql(logger, sqlCen);
        try (CallableStatement stmt = conn.prepareCall(sqlCen)) {
            DatabaseAccess.setQueryStmtParameters(stmt, paramObjs);
            return stmt.execute();
        } catch (SQLException e) {
            throw DatabaseAccess.createAccessException(sqlCen, e);
        }
    }

    /**
     * 直接运行SQL,update delete insert
     *
     * @param conn conn
     * @param sSql sSql
     * @return 返回是否成功
     * @throws SQLException SQLException
     */
    public static boolean doExecuteSql(Connection conn, String sSql) throws SQLException {
        try (PreparedStatement stmt = conn.prepareStatement(sSql)) {
            QueryLogUtils.printSql(logger, sSql);
            return stmt.execute();
        } catch (SQLException e) {
            throw DatabaseAccess.createAccessException(sSql, e);
        }
    }

    public static void setQueryStmtParameters(PreparedStatement stmt, Object[] paramObjs) throws SQLException {
        //query.getParameterMetadata().isOrdinalParametersZeroBased()?0:1;
        if (paramObjs != null) {
            for (int i = 0; i < paramObjs.length; i++) {
                if (paramObjs[i] == null) {
                    stmt.setNull(i + 1, Types.NULL);
                } else {
                    stmt.setObject(i + 1,
                        transObjectForSqlParam(paramObjs[i]));
                }
            }
        }
    }

    public static void setQueryStmtParameters(PreparedStatement stmt, List