如何在JDBC中获取列注释 [英] How to get Column Comments in JDBC

问题描述

我想使用JDBC元数据获取列注释，但是每次返回null时，我都使用Oracle和SqlServer进行了测试，都返回了Null.

I want to fetch Column comments using JDBC Metadata , But everytime it returns null , I tested with Oracle and SqlServer both cases it returning Null.

DatabaseMetaData dmt = con.getMetaData();    
colRs = dmt.getColumns(null, "dbo", 'Student', null);
while (colRs.next()) {
System.out.println(colRs.getString("REMARKS");
   }

虽然我可以获取所有其他数据，例如列名，长度等，完全可以...

While i am getting all other data like column name , length etc absolutely ok ...

推荐答案

对于Oracle，您需要提供一个连接属性 remarksReporting 并将其设置为 true 或调用该方法 setRemarksReporting()启用它.

For Oracle you need to provide a connection property remarksReporting and set that to true or call the method setRemarksReporting() to enable that.

OracleConnection oraCon = (OracleConnection)con;
oraCon.setRemarksReporting(true);

然后， getColumns()将返回结果集的 REMARKS 列中的列(或表)注释.

After that, getColumns() will return the column (or table) comments in the REMARKS column of the ResultSet.

有关更多详细信息，请参见 Oracle的JDBC参考

See Oracle's JDBC Reference for more details

对于SQL Server，这是完全不可能的.

For SQL Server this is not possible at all.

Microsoft和jTDS驱动程序都不公开表或列注释.可能是因为SQL Server中没有对此的SQL支持.使用扩展属性"和属性名称MS_DESCRIPTION的通常方法不可靠.主要是因为我们不需要 MS_DESCRIPTION 作为属性名称.甚至 sp_help 都不会返回这些注释.至少jTDS驱动程序只需在表列中调用 sp_help .我不知道Microsoft驱动程序会做什么.

Neither the Microsoft nor the jTDS driver expose table or column comments. Probably because there is no SQL support for that in SQL Server. The usual approach of using "extended properties" and the property name MS_DESCRIPTION is not reliable. Mainly because there is no requirement to us MS_DESCRIPTION as the property name. Not even sp_help returns those remarks. And at least the jTDS driver simply calls sp_help go the the table columns. I don't know what the Microsoft driver does.

您在那里唯一的选择是使用 fn_listextendedproperty()来检索注释:

The only option you have there, is to use fn_listextendedproperty() to retrieve the comments:

例如:

SELECT objname, cast(value as varchar(8000)) as value 
FROM fn_listextendedproperty ('MS_DESCRIPTION','schema', 'dbo', 'table', 'Student', 'column', null) 

您需要使用用于存储评论的任何属性名称替换 MS_DESCRIPTION .

You need to replace MS_DESCRIPTION with whatever property name you use to store your comments.

这篇关于如何在JDBC中获取列注释的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！