当键为复合键时，metaData.getPrimaryKeys()返回一行 [英] metaData.getPrimaryKeys() returns a single row when the key is composite

问题描述

使用SQLite驱动程序的JDBC中的复合主键存在问题.

I have an issue with a compound primary key in JDBC using SQLite driver.

当我确认键实际上是由两列组成的复合键时，DatabaseMetaData对象的getPrimaryKeys()方法返回单行.

The getPrimaryKeys() method from a DatabaseMetaData object returns a single row when I have verified the key is actually a compound key consisting of two columns.

有人对如何检索主键的真实列表有任何建议/选择吗?

Does any one have any suggestions / alternatives how the true listing of primary keys can be retrieved?

这是代码:

DatabaseMetaData meta = con.getMetaData();

ResultSet pks = meta.getPrimaryKeys(null, null, "work_on");
ResultSetMetaData rsmd = pks.getMetaData();
while(pks.next()) {
    for (int i = 1; i < rsmd.getColumnCount(); i++) {
        System.out.print(pks.getString(i) + " ");
    }
    System.out.println();
}

推荐答案

似乎您已经遇到了这个问题: https://bitbucket.org/xerial/sqlite-jdbc/issues/107/databasemetadatagetprimarykeys-does-not

It seems you have run into this issue: https://bitbucket.org/xerial/sqlite-jdbc/issues/107/databasemetadatagetprimarykeys-does-not

JDBC驱动程序中的错误是与您的SQL字符串匹配的错误正则表达式.正则表达式期望KEY关键字和左括号之间至少有一个空格.如果您这样写:

The bug in the JDBC driver is a bad regular expression matching your SQL string. The regular expression expects at least one whitespace between the KEY keyword and the opening parenthesis. If you write this:

create table work_on (
  s_id varchar(4), 
  p_id varchar(4), 
  x varchar(4), 
  primary key(s_id, p_id)
)

无法正确报告主键(因为当正则表达式不匹配任何内容时，回退逻辑中还有另一个错误，该其他错误只会导致报告 last PK列).因此，要变通解决此问题，您可以精心设计表，使其始终具有以下空格:

The primary key won't be reported correctly (because there's another bug in the fallback logic when the regular expression fails to match anything, that other bug results in only the last PK column to be reported). So, to work around this problem, you could carefully design your tables to always have this whitespace:

create table work_on (
  s_id varchar(4), 
  p_id varchar(4), 
  x varchar(4), 
  primary key (s_id, p_id)
  --         ^ whitespace here!
)

不使用JDBC API的解决方法

您始终可以自己在此处运行此查询(这是JDBC驱动程序的后备查询):

Workaround by not using the JDBC API

You can always run this query here yourself (which is the JDBC driver's fallback query):

pragma table_info('work_on');

，然后收集所有将pk标志设置为true的行.例如，下表

And then collect all those rows that have the pk flag set to true. For instance, the following table

create table work_on (
  s_id varchar(4), 
  p_id varchar(4), 
  x varchar(4), 
  primary key(s_id, p_id)
)

...产生以下输出:

... produces this output:

+----+----+----------+-------+----------+----+
| cid|name|type      |notnull|dflt_value|  pk|
+----+----+----------+-------+----------+----+
|   0|s_id|varchar(4)|      0|{null}    |   1|
|   1|p_id|varchar(4)|      0|{null}    |   2|
|   2|x   |varchar(4)|      0|{null}    |   0|
+----+----+----------+-------+----------+----+

这篇关于当键为复合键时，metaData.getPrimaryKeys()返回一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！