oracle操作工具类

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import java.sql.Connection;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.Properties;

/**
 * Oracle操作工具类
 *
 * @author：saltedFish
 * @create：2023-04-24 15:39
 * @description:
 **/
public class OracleUtil implements AutoCloseable {
    private static final Logger logger = LoggerFactory.getLogger(OracleUtil.class);
    private final NamedParameterJdbcTemplate jdbcTemplate;
    private final HikariDataSource dataSource;
    private static final String JDBC_DRIVER = "oracle.jdbc.OracleDriver";

    /**
     * 使用JDBC URL直接创建
     *
     * @param url      完整的JDBC URL
     * @param username 用户名
     * @param password 密码
     */
    public OracleUtil(String url, String username, String password) {
        validateParams(url, username, password);

        HikariConfig config = createHikariConfig(url, username, password);
        this.dataSource = new HikariDataSource(config);
        this.jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

        logger.info("Oracle连接池初始化成功: {}", url);
    }

    /**
     * 查询列表
     *
     * @param sql      查询语句
     * @param paramMap 查询条件，可以为 null
     * @param clazz    返回对象类型，不能为空
     * @return 查询结果列表
     * @throws IllegalArgumentException 如果 clazz 为 null
     */
    public <T> List<T> queryForList(String sql, Map<String, Object> paramMap, Class<T> clazz) {
        if (clazz == null) {
            throw new IllegalArgumentException("Return type class cannot be null");
        }
        try {
            logger.debug("Executing SQL: {}, Parameters: {}", sql, paramMap);
            return jdbcTemplate.query(sql, paramMap == null ? Collections.emptyMap() : paramMap, new BeanPropertyRowMapper<>(clazz));
        } catch (Exception e) {
            logger.error("SQL执行失败: {} | 错误: {}", sql, e.getMessage(), e);
            throw new RuntimeException("数据库操作失败", e);
        }
    }

    private HikariConfig createHikariConfig(String url, String username, String password) {
        HikariConfig config = new HikariConfig();

        Properties dsProps = new Properties();
        dsProps.setProperty("user", username);
        dsProps.setProperty("password", password);

        // 启用密码包装和禁用OOB
        dsProps.setProperty("oracle.jdbc.enablePasswordWrap", "true");
        dsProps.setProperty("oracle.net.disableOob", "true");

        // 时区和编码设置
        dsProps.setProperty("oracle.jdbc.timezoneAsRegion", "false");
        dsProps.setProperty("fixedString", "true");
        dsProps.setProperty("useUnicode", "true");
        dsProps.setProperty("characterEncoding", "UTF-8");

        config.setDataSourceProperties(dsProps);
        config.setDriverClassName(JDBC_DRIVER);
        config.setJdbcUrl(url);

        // 优化连接池配置
        config.setPoolName("Oracle-Pool");
        config.setMaximumPoolSize(10);
        config.setMinimumIdle(2);
        config.setConnectionTimeout(10000);     // 10秒
        config.setIdleTimeout(600000);          // 10分钟
        config.setMaxLifetime(1800000);         // 30分钟
        config.setLeakDetectionThreshold(10000); // 10秒

        // Oracle专用连接测试
        config.setConnectionTestQuery("SELECT 1 FROM DUAL");
        // 3秒验证超时
        config.setValidationTimeout(3000);

        return config;
    }

    /**
     * 参数验证
     */
    private void validateParams(String url, String username, String password) {
        if (url == null || url.trim().isEmpty()) {
            throw new IllegalArgumentException("Database URL cannot be null or empty");
        }
        if (username == null || username.trim().isEmpty()) {
            throw new IllegalArgumentException("Username cannot be null or empty");
        }
        if (password == null || password.trim().isEmpty()) {
            throw new IllegalArgumentException("password cannot be null or empty");
        }
    }

    @Override
    public void close() {
        if (dataSource != null && !dataSource.isClosed()) {
            dataSource.close();
            logger.info("Oracle连接池已关闭");
        }
    }

    /**
     * 连接测试方法
     */
    public boolean testConnection() {
        // 使用 try-with-resources 确保连接自动关闭
        try (Connection connection = dataSource.getConnection()) {
            return connection.isValid(3);
        } catch (Exception e) {
            logger.error("数据库连接测试失败", e);
            return false;
        }
    }
}