准备工作–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();
}
}
}