用于特定查询的 jdbc getMetaData

Question

在 clojure/java jdbc 中，我了解到使用 getMetaData 我可以返回大量关于通过 jdbc 连接的数据库的有趣信息。这可以根据目录、架构和表名进行过滤。

(defn get-db-metadata 
    [db-spec ]
    (with-connection (get-db-connection-map db-spec)  
      ; get columns returs the following:
      ;  TABLE_CAT String       => table catalog (may be null)
      ;  TABLE_SCHEM String     => table schema (may be null)
      ;  TABLE_NAME String      => table name
      ;  COLUMN_NAME String     => column name
      ;  DATA_TYPE int          => SQL type from java.sql.Types
      ;  TYPE_NAME String       => Data source dependent type name, for a UDT the type name is fully qualified
      ;  COLUMN_SIZE int        => column size. For char or date types this is the maximum number of characters, for numeric or decimal types this is precision.
      ;  BUFFER_LENGTH          => not used.
      ;  DECIMAL_DIGITS int     => the number of fractional digits
      ;  NUM_PREC_RADIX int     => Radix (typically either 10 or 2)
      ;  NULLABLE int           => is NULL allowed.
      ;  columnNoNulls          => might not allow NULL values
      ;  columnNullable         => definitely allows NULL values
      ;  columnNullableUnknown  => nullability unknown
      ;  REMARKS String         => comment describing column (may be null)
      ;  COLUMN_DEF String      => default value (may be null)
      ;  SQL_DATA_TYPE int      => unused
      ;  SQL_DATETIME_SUB int   => unused
      ;  CHAR_OCTET_LENGTH int  => for char types the maximum number of bytes in the column
      ;  ORDINAL_POSITION int   => index of column in table (starting at 1)
      ;  IS_NULLABLE String     => "NO" means column definitely does not allow NULL values; "YES" means the column might allow NULL values. An empty string means nobody knows.
      ;  SCOPE_CATLOG String    => catalog of table that is the scope of a reference attribute (null if DATA_TYPE isn't REF)
      ;  SCOPE_SCHEMA String    => schema of table that is the scope of a reference attribute (null if the DATA_TYPE isn't REF)
      ;  SCOPE_TABLE String     => table name that this the scope of a reference attribure (null if the DATA_TYPE isn't REF)
      ;  SOURCE_DATA_TYPE short => source type of a distinct type or user-generated Ref type, SQL type from java.sql.Types (null if DATA_TYPE isn't DISTINCT or user-generated REF)
      (into #{}
            (map #(str (% :table_name) "." (% :column_name) "\n")
                 (resultset-seq (->
                                  (connection)
                                  (.getMetaData)
                                  ; Params in are catalog, schemapattern, tablenamepattern  
                                  ;(.getColumns "stuff" "public" nil "%")
                                  (.getColumns "db_catalog" "schema_name" "some_table" "%")
                                  )
                                )
                 )
            )
      )
    )

我感兴趣的不是数据库中每个表的信息，而是特定查询 返回的结果集。在这一点上，我的具体需求是知道检索到的特定列的最大长度，例如以固定宽度格式打印到屏幕。

到目前为止我考虑过的(我确定不理想):

1. 尝试解析入站 sql 语句以找出哪些表正在查询，然后获取这些表的元数据具体来说。结果会很复杂带有函数或公用表表达式等的 select 语句的一部分。我认为它很快就会变得困惑(而且不准确)。

2. 创建一个基于 入站查询...然后我可以获取此 View 上的元数据。这个 但是，如果我只有一个只读连接到 我正在使用的数据库。通常情况下，我相信我正在尝试做的事情。

3. 获取结果，然后为返回的每一列查找 值的最大长度，然后创建我的固定宽度网格 因此。如果我看大的话，这就不太好了 结果集......

是否有更好的方法来确定我从查询返回的所有内容的类型？其他程序如何做到这一点？似乎我应该能够从我在这里以某种方式发出查询请求时获取元数据:

(defn fetch-results 
  "Treat lazy result sets in whole for returning a database query"  
  [db-spec query] 
  (with-connection 
    (get-db-connection-map db-spec) 
    (with-query-results res query 
      ; (get the medata here somehow for columns returned ????)
      (doall res))
    )
  )

提前致谢。

Answer 1

你可以在 scala 中这样做，也许这可以帮助你:

var stmt: PreparedStatement = null
var rs: ResultSetMetaData = null
try {
  stmt = conn.prepareStatement(query)
  rs = stmt.getMetaData()
} finally {
  cleanup(stmt)
}
}