jdbc参考文档以及调试demo

发表于 更新于 分类于 ， ， ， 阅读次数：
jdbc 接口说明规范以及测试 # 接口文档 可以参考微软写的,大厂写的比较详细 [sqlsever jdbc 接口文档](https://docs.microsoft.com/zh-cn/sql/connect/jdbc/reference/gettables-method-sqlserverdatabasemetadata?view=sql-server-ver16)

jdbc 接口测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
package com.lqz.testjava;

import java.sql.*;

public class TablePrintStructure {


    public static void main(String[] args) throws Exception {
//        String driver = "com.mysql.cj.jdbc.Driver";
        String driver = "org.postgresql.Driver";
//        String url = "jdbc:mysql://localhost:3306/book_sc?serverTimezone=GMT";
        String url = "jdbc:postgresql://192.168.0.130:5432/test_db";
        String user = "user_name";
        String password = "pass_word";

        printTableStructure(driver, url, user, password);
    }

    /**
     * 打印所有表结构
     *
     * @param driver   driver
     * @param url      url
     * @param user     user
     * @param password password
     * @throws Exception exception
     */
    private static void printTableStructure(String driver, String url, String user, String password) throws Exception {
        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, password);

        System.out.println(connection);
        DatabaseMetaData metaData = connection.getMetaData();
        System.out.println(metaData);
        // 获取所有表
        ResultSet tableResultSet = metaData.getTables(
                null,
                null,
                "test%",
                new String[]{"TABLE"});
        System.out.println(tableResultSet.toString());

        Statement stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery("select * from test_tab");
        System.out.println("---" + rs.getClass());


        while (tableResultSet.next()) {
            String tableName = tableResultSet.getString("TABLE_NAME");
            System.out.println("table:" + tableName);

            // 获取表字段结构
            ResultSet columnResultSet = metaData.getColumns(null, "%", tableName, "%");
            while (columnResultSet.next()) {
                // 字段名称
                String columnName = columnResultSet.getString("COLUMN_NAME");
                // 数据类型
                String columnType = columnResultSet.getString("TYPE_NAME");
                // 字段长度
                int datasize = columnResultSet.getInt("COLUMN_SIZE");
                // 小数部分位数
                int digits = columnResultSet.getInt("DECIMAL_DIGITS");
                // 是否可为空 1代表可空 0代表不可为空
                int nullable = columnResultSet.getInt("NULLABLE");
                // 描述
                String remarks = columnResultSet.getString("REMARKS");
                System.out.println(columnName + " " + columnType + " " + datasize + " " + digits + " " + nullable + " " + remarks);
            }
            System.out.println("=================================");
        }
    }

}

如下方法可以找到接口组装的语法内容
img.png

img_1.png

详细sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
SELECT NULL              AS TABLE_CAT,
       NULL              AS TABLE_SCHEM,
       c.relname         AS TABLE_NAME,
       CASE c.relname ~ '^pg_'
           WHEN true THEN CASE c.relname ~ '^pg_toast_'
                              WHEN true THEN CASE c.relkind
                                                 WHEN 'r' THEN 'SYSTEM TOAST TABLE'
                                                 WHEN 'i' THEN 'SYSTEM TOAST INDEX'
                                                 ELSE NULL END
                              WHEN false THEN CASE c.relname ~ '^pg_temp_'
                                                  WHEN true THEN CASE c.relkind
                                                                     WHEN 'r' THEN 'TEMPORARY TABLE'
                                                                     WHEN 'i' THEN 'TEMPORARY INDEX'
                                                                     ELSE NULL END
                                                  WHEN false THEN CASE c.relkind
                                                                      WHEN 'r' THEN 'SYSTEM TABLE'
                                                                      WHEN 'v' THEN 'SYSTEM VIEW'
                                                                      WHEN 'i' THEN 'SYSTEM INDEX'
                                                                      ELSE NULL END
                                                  ELSE NULL END
                              ELSE NULL END
           WHEN false THEN CASE c.relkind
                               WHEN 'r' THEN 'TABLE'
                               WHEN 'i' THEN 'INDEX'
                               WHEN 'S' THEN 'SEQUENCE'
                               WHEN 'v' THEN 'VIEW'
                               ELSE NULL END
           ELSE NULL END AS TABLE_TYPE,
       NULL              AS REMARKS
FROM pg_class c
WHERE true
  AND c.relname LIKE 'test%'
  AND (false OR (c.relkind = 'r' AND c.relname !~ '^pg_'))
ORDER BY TABLE_TYPE, TABLE_NAME

自定义sql语句

1
2
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("select * from test_tab");

返回结果可以参考 SQLServerResultSet 关键字结果
SQLServerResultSet
返回结果引用信息方法及变量名查找

img_2.png