Java使用类加载器加载外部Jar使用JDBC

准备工作–jdbc驱动jar

官网或者mvn仓库下载好驱动jar到你的本地文件夹下
示例{C:\Users\WTCL\IdeaProjects\decision_analysis_system\drivers\kingbase8-9.0.0.jar}

在这里插入图片描述

准备一个数据库的SQL脚本用于一会儿执行

例如-随便来一个SQL

SELECT 
    col.column_name AS ColumnName,
    col.data_type AS DataType,
    com.comments AS ColumnDescription
FROM
    all_tab_columns col
LEFT JOIN
    all_col_comments com
ON
    col.owner = com.owner
    AND col.table_name = com.table_name
    AND col.column_name = com.column_name
WHERE
    col.owner = 'API_GATEWAY' 
    AND col.table_name = 'API_MARKET';

我这边使用人大金仓的数据库的SQL查询指定表的表字段,字段注释,字段类型信息

   // 执行查询
            String query = "SELECT\n" +
                           "    a.attname AS COLUMN_NAME,\n" +
                           "    format_type(a.atttypid, a.atttypmod) AS DATA_TYPE,\n" +
                           "    d.description AS COLUMN_COMMENT\n" +
                           "FROM \n" +
                           "    pg_class c\n" +
                           "JOIN \n" +
                           "    pg_attribute a ON c.oid = a.attrelid\n" +
                           "LEFT JOIN \n" +
                           "    pg_description d ON a.attrelid = d.objoid AND a.attnum = d.objsubid\n" +
                           "WHERE \n" +
                           "    c.relname = 'test_table'\n" +
                           "    AND c.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')\n" +
                           "    AND a.attnum > 0;";

第一步加载jar的file

			private final String FILE_PATH = "C:\\Users\\WTCL\\IdeaProjects\\decision_analysis_system\\drivers\\kingbase8-9.0.0.jar";
  			File jarFile = new File(FILE_PATH);
            if (!jarFile.exists()) {
                throw new FileNotFoundException("驱动文件不存在");
            }
            // 加载 JAR 文件
            URL jarUrl = jarFile.toURI().toURL();

第二步类加载器加载jar文件的资源

			 // 使用反射调用 addURL 方法
            URLClassLoader classLoader = new URLClassLoader(new URL[]{jarUrl}, ClassLoader.getSystemClassLoader());
            // 强制加载驱动类并触发注册
            Class<?> driverClass = Class.forName(className, true, classLoader);
            Driver driver = (Driver) driverClass.getDeclaredConstructor().newInstance();
            DriverManager.registerDriver(driver);
            Properties props = new Properties();
            props.setProperty("user", username);
            props.setProperty("password", password);
            Connection connection = driver.connect(url, props);

第三步JDBC代码操作数据源

			  // 数据库连接信息
        String url = "jdbc:kingbase8://23.99.11.74:54321/test"; // 替换为实际的主机、端口、数据库名
        String username = "admin";  // 替换为你的数据库用户名
        String password = "QY~NBE6HX7RLJWS";  // 替换为你的数据库密码

        // JDBC 连接和查询
        try (Connection connection = new TestMain().init("com.kingbase8.Driver", url, username, password);
             Statement statement = connection.createStatement()) {

            // 执行查询
            String query = "SELECT\n" +
                           "    a.attname AS COLUMN_NAME,\n" +
                           "    format_type(a.atttypid, a.atttypmod) AS DATA_TYPE,\n" +
                           "    d.description AS COLUMN_COMMENT\n" +
                           "FROM \n" +
                           "    pg_class c\n" +
                           "JOIN \n" +
                           "    pg_attribute a ON c.oid = a.attrelid\n" +
                           "LEFT JOIN \n" +
                           "    pg_description d ON a.attrelid = d.objoid AND a.attnum = d.objsubid\n" +
                           "WHERE \n" +
                           "    c.relname = 'test_table'\n" +
                           "    AND c.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')\n" +
                           "    AND a.attnum > 0;";

            ResultSet resultSet = statement.executeQuery(query);

            // 处理结果集
            while (resultSet.next()) {
                String columnData = resultSet.getString("COLUMN_NAME");
                String va1 = resultSet.getString("DATA_TYPE");
                String va2 = resultSet.getString("COLUMN_COMMENT");

                // 打印列的具体内容
                System.out.println("Column Name: " + columnData);
                System.out.println("Data Type: " + va1);
                System.out.println("Column Comment: " + va2);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (InstantiationException | IllegalAccessException e) {
            throw new RuntimeException(e);
        } catch (Exception e) {
            e.printStackTrace();
        }

第四步运行查看jdbc查询到数据没得

在这里插入图片描述
去数据库对比一下数据是否准确
在这里插入图片描述

完整示例

package com.gt.demo.service;

import org.apache.commons.lang3.StringUtils;

import java.io.File;
import java.io.FileNotFoundException;
import java.net.URL;
import java.net.URLClassLoader;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * @author WTCL
 */
public class TestMain {

    private final String FILE_PATH = "C:\\Users\\WTCL\\IdeaProjects\\decision_analysis_system\\drivers\\kingbase8-9.0.0.jar";

    public Connection init(String className, String url, String username, String password) throws Exception {
        Connection connection = null;
        try {
            File jarFile = new File(FILE_PATH);
            if (!jarFile.exists()) {
                throw new FileNotFoundException("驱动文件不存在");
            }
            // 加载 JAR 文件
            URL jarUrl = jarFile.toURI().toURL();
            // 使用反射调用 addURL 方法
            URLClassLoader classLoader = new URLClassLoader(new URL[]{jarUrl}, ClassLoader.getSystemClassLoader());
            // 强制加载驱动类并触发注册
            Class<?> driverClass = Class.forName(className, true, classLoader);
            Driver driver = (Driver) driverClass.getDeclaredConstructor().newInstance();
            DriverManager.registerDriver(driver);
            Properties props = new Properties();
            props.setProperty("user", username);
            props.setProperty("password", password);
            connection = driver.connect(url, props);
        } catch (Exception e) {
            System.out.println("Error initializing connection: " + e.getMessage());
            throw e;
        }
        return connection;
    }

    public static void main(String[] args) {
        // 数据库连接信息
        String url = "jdbc:kingbase8://23.99.11.74:54321/test"; // 替换为实际的主机、端口、数据库名
        String username = "admin";  // 替换为你的数据库用户名
        String password = "QY~NBE6HX7RLJWS";  // 替换为你的数据库密码

        // JDBC 连接和查询
        try (Connection connection = new TestMain().init("com.kingbase8.Driver", url, username, password);
             Statement statement = connection.createStatement()) {

            // 执行查询
            String query = "SELECT\n" +
                           "    a.attname AS COLUMN_NAME,\n" +
                           "    format_type(a.atttypid, a.atttypmod) AS DATA_TYPE,\n" +
                           "    d.description AS COLUMN_COMMENT\n" +
                           "FROM \n" +
                           "    pg_class c\n" +
                           "JOIN \n" +
                           "    pg_attribute a ON c.oid = a.attrelid\n" +
                           "LEFT JOIN \n" +
                           "    pg_description d ON a.attrelid = d.objoid AND a.attnum = d.objsubid\n" +
                           "WHERE \n" +
                           "    c.relname = 'test_table'\n" +
                           "    AND c.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')\n" +
                           "    AND a.attnum > 0;";

            ResultSet resultSet = statement.executeQuery(query);

            // 处理结果集
            while (resultSet.next()) {
                String columnData = resultSet.getString("COLUMN_NAME");
                String va1 = resultSet.getString("DATA_TYPE");
                String va2 = resultSet.getString("COLUMN_COMMENT");

                // 打印列的具体内容
                System.out.println("Column Name: " + columnData);
                System.out.println("Data Type: " + va1);
                System.out.println("Column Comment: " + va2);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (InstantiationException | IllegalAccessException e) {
            throw new RuntimeException(e);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

然后还可以借鉴第三方适配器 {calcite}操作不同的jdbc连接的数据源,实现高度扩展

在这里插入图片描述

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值