如何从DB2中的逻辑表中获取主键?

huangapple go评论74阅读模式
英文:

How can I get the Primary Keys from logical Tables in DB2?

问题

以下是您要翻译的内容:

所以我正在处理 AS400DB2 系统
我编写了一个方法,它可以为我提供每个 物理 表的 主键。但是在某些表上,主键只设置在 逻辑 表上。在那种情况下,我的方法不起作用。

@Override
public ArrayList<Field> getPKS(String lib) {
    ArrayList<Field> pkList = new ArrayList<>();
    try (Connection connection = DriverManager.getConnection("jdbc:as400://" + ConnectionData.SYSTEM + ";naming=system;libraries=*" + lib + ";",
            ConnectionData.USER, ConnectionData.PASSWORD);
                                
            ResultSet rs = connection.getMetaData().getPrimaryKeys(null, connection.getSchema(), "LSAVSLA")){
            while (rs.next()) {
                pkList.add(new Field(rs.getString("COLUMN_NAME")));
            }
       
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return pkList;
}

对于物理表,它是有效的,但对于逻辑表则不然。
您是否有任何想法,如何从逻辑表获取主键

英文:

So I am working on AS400, DB2 System.
I wrote a method which provides me the Primary Keys of every physical table. But on some tables the primary keys are only set on the logical table. There my method does not work.

@Override
	public ArrayList&lt;Field&gt; getPKS(String lib) {
		ArrayList&lt;Field&gt; pkList = new ArrayList&lt;&gt;();
		try (Connection connection = DriverManager.getConnection(&quot;jdbc:as400://&quot; + ConnectionData.SYSTEM + &quot;;naming=system;libraries=*&quot; + lib + &quot;;&quot;,
				ConnectionData.USER, ConnectionData.PASSWORD);
							
				ResultSet rs = connection.getMetaData().getPrimaryKeys(null, connection.getSchema(), &quot;LSAVSLA&quot;)){
		        while (rs.next()) {
		        	pkList.add(new Field(rs.getString(&quot;COLUMN_NAME&quot;)));
		        }
		   
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return pkList;
	
	}

For a pysical table it's working, but for a logical Table it is not.
Do you have any idea how to get the primary keys from the logical table.

答案1

得分: 0

逻辑文件不包含数据。它们包含了在一个或多个物理文件中找到的记录的描述。逻辑文件是一个或多个物理文件的视图或表示,正如手册所述。与传统关系型数据库管理系统中的视图类似,你不能为逻辑文件定义主键。

英文:

> Logical files do not contain data. They contain a description of records that are found in one or more physical files. A logical file is a view or representation of one or more physical files

says the manual. Similarly to a view in a conventional RDBMS, you cannot define a primary key for a logical file.

答案2

得分: 0

基本上,您所拥有的是在没有主键的情况下定义的物理文件(或SQL表),以及在具有唯一键的情况下定义的逻辑文件或(SQL索引)。

在IBM i上,逻辑文件可以同时充当SQL索引、SQL视图或两者。正如Mustaccio所提到的,对象中实际上没有任何实际数据。

您最好的选择可能是查询SYSTABLEINDEXS目录视图,查找给定表上的主键或唯一索引。

您还可以查看getIndexes()方法。

英文:

So basically what you have are physical files (or SQL tables) defined without a primary key and logical files or (SQL indexes) defined with a unique key.

On the IBM i, a logical file can act as either an SQL index, and SQL View or both at the same time. As Mustaccio mentions, there isn't any actual data in the object.

You best bet, might be to query the SYSTABLEINDEXS catalog view looking for primary key or unique indexes over a given table.

You could also take a look at the getIndexes() method.

答案3

得分: 0

我从“QSYS.QADBKATR”中选择了“DBKFLD”字段找到了解决方案。
SQL查询:
SELECT DBKFLD FROM QSYS.QADBKATR WHERE DBKLIB = "Your lib" AND DBKFIL = "Your table"

Java代码:

Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet resultSetQuery = statement.executeQuery("select DBKFLD from QSYS.QADBKATR where DBKLIB = '" + lib + "' and DBKFIL = '" + tablename + "'");

ResultSetMetaData metadata = resultSetQuery.getMetaData();
int columnCount = metadata.getColumnCount();

while (resultSetQuery.next()) {
    for (int i = 1; i <= columnCount; i++) {
        String pk = resultSetQuery.getString(i);
        pk = pk.replaceAll("\\s+", "");
        pkList.add(new Feld(pk));
    }
}

return pkList;
英文:

I found a solution by selecting the "DBKFLD" field from "QSYS.QADBKATR"<br>
The SQL Query: <br>SELECT DBKFLD FROM QSYS.QADBKATR WHERE DBKLIB = "Your lib" AND DBKFIL = "Your table"

The Java Code:

Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet resultSetQuery = statement.executeQuery(&quot;select DBKFLD from QSYS.QADBKATR where DBKLIB = &#39;&quot; + lib + &quot;&#39; and DBKFIL = &#39;&quot; + tablename + &quot;&#39;&quot;)) {
    
    ResultSetMetaData metadata = resultSetQuery.getMetaData();
        			int columnCount = metadata.getColumnCount();
        
        			while (resultSetQuery.next()) {
        
        				
        				for (int i = 1; i &lt;= columnCount; i++) {
        
        					String pk = resultSetQuery.getString(i);
        					pk = pk.replaceAll(&quot;\\s+&quot;, &quot;&quot;);
        
        					pkList.add(new Feld(pk));
        
        				}
        				
        			}
        
        			return pkList;

huangapple
  • 本文由 发表于 2020年10月16日 17:20:37
  • 转载请务必保留本文链接:https://go.coder-hub.com/64386370.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定