如何创建基于JDBC结果集的表 [英] How to create table based on JDBC Result Set

问题描述

我正在构建一个报表工具，我需要对远程数据库执行查询，并将结果集存储在我自己的数据库中（因为我没有远程数据库的写入权限，我还需要缓存结果以防止进一步执行）。此外，我需要这个功能，所以我可以将两个结果集合在一起并基于生成的结果生成结果。

现在，我的问题是，我不知道如何基于jdbc ResultSet创建表。是否有任何开源工具或脚本处理这个？

我的应用程序基于Spring 3.1.0并使用JDBC查询本地和远程数据库。我想要存储结果的我的本地数据库是MySQL 5.5.20。 （这是一个好主意存储在MySQL吗？它提供足够的性能吗？）

解决方案

>最近匹配结构，并构造一个表。

但 这不能是精确的副本，在表名，键，引擎类型，字段是否可空，等等。 。

以下代码段可帮助您以某种方式获得适当的结果。

  String sql =select * from visitors 
 ResultSet rs = stmt.executeQuery（sql）; 
 ResultSetMetaData rsmd = rs.getMetaData（）; 
 int columnCount = rsmd.getColumnCount（）; 
 String tableName = null; 
 StringBuilder sb = new StringBuilder（1024）; 
 if（columnCount> 0）{
 sb.append（Create table）.append（rsmd.getTableName（1））.append（（）; 
} 
 for（int i = 1; i <= columnCount; i ++）{
 if（i> 1）sb.append（，）; 
 String columnName = rsmd.getColumnLabel （i）; 
 String columnType = rsmd.getColumnTypeName（i）; 
 
 sb.append（columnName）.append（）.append（columnType）; 
 
 int precision = rsmd.getPrecision（i）; 
 if（precision！= 0）{
 sb.append（（）.append（precision）.append（））; 
} 
} // for columns 
 sb.append（））; 
 
 System.out.println（sb.toString（））; 
  

使用上面部分代码执行，字符串如下：

 创建表访问者（ip VARCHAR（6），bro VARCHAR（6））
  

让我希望这可以帮助你进一步。

类似的示例可以在以下位置找到： 使用ResultSet建立表格

UPDATE 1 ：：

如何处理只存在于一个数据库中且不存在的类型

只有依赖于客户端应用程序的结果集，才能执行任何操作。

应该总是从 geometry ， address 这样的复合数据类型中选择合适的数据 / code>等

Examle ：选择addess.city，address.zipcode，x（geometry_column） / code>

要提供 geometry 数据类型的示例：

MySQL对其 但我不知道与 SQL Server的实施相比，它们有多好的空间数据 。

geometry 是复合数据类型因此无法通过直接查询检索。

您需要解析这些数据列中的数据并返回可读的可读，可识别的

示例： create table geom（g geometry）;

转换来自的ResultSet从geom 中选择g使用JAVA到create table语句将会产生 unknwon 数据类型< c $ c> g 。

 创建表格geom（g UNKNOWN）
  

使用 x（g）， y（g）坐标函数on列 g 将返回正确且可接受的数据类型。

查询从geom选择x（g），y 将转换为

 创建表（x（g）DOUBLE（23，31），y （g）DOUBLE（23，31））
  

UPDATE 2 < br>
结果集可能使用关系的多个表的组合生成。还有一种机会，结果集字段由表达式及其别名组成。因此，所得到的列字段或别名的数据类型被决定为动态的。元数据不知道查询中表，列名称及其原始/父数据类型的确切名称。

因此，不可能得到

1. 表的单一名称并使用它。


2. 父列的数据
：这也适用于所有其他跨数据库特定的数据类型，例如NVARCHAR 等。
但是，请参阅此发布以替代NVARCHAR的使用




在尝试这样的动态数据迁移之前，客户端应用程序负责知道等效数据类型并相应地使用它们。



此外，请参阅 <有关详细信息，请参阅Microsoft Access，MySQL和SQL Server的数据类型和范围 。




I am building a reporting tool and I need to execute queries on remote databases and store the result set in my own database (because I do not have write permission on remote databases and also I need to cache the results to prevent further executions). Moreover, I need this capability, so I can join two result sets together and generate results based on generated results.

Now, my problem is that I do not know how to CREATE TABLE based on jdbc ResultSet. Is there any open source tools or scripts that handles this?

My application is based on Spring 3.1.0 and uses JDBC to query local and remote databases. My local database that I want to store the results is MySQL 5.5.20. (Is this a good idea to store in MySQL? Does it provide the sufficient performance?)

解决方案

We can extract the nearest matching structure from the resultset and construct a table.
But this can't be the exact replica, in terms of table name, keys, engine type, whether a field is nullable or not, etc..

Following code snippet helps you proceed in a way to get an appropriate result.

String sql = "select * from visitors";
ResultSet rs = stmt.executeQuery( sql );
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
String tableName = null;
StringBuilder sb = new StringBuilder( 1024 );
if ( columnCount > 0 ) { 
    sb.append( "Create table " ).append( rsmd.getTableName( 1 ) ).append( " ( " );
}
for ( int i = 1; i <= columnCount; i ++ ) {
    if ( i > 1 ) sb.append( ", " );
    String columnName = rsmd.getColumnLabel( i );
    String columnType = rsmd.getColumnTypeName( i );

    sb.append( columnName ).append( " " ).append( columnType );

    int precision = rsmd.getPrecision( i );
    if ( precision != 0 ) {
        sb.append( "( " ).append( precision ).append( " )" );
    }
} // for columns
sb.append( " ) " );

System.out.println( sb.toString() );

Executing with above part of the code, printed following string:

Create table visitors ( ip VARCHAR( 6 ), bro VARCHAR( 6 ) )

Let me hope this helps you proceed further.

Similar example can be found at: Create a table using ResultSet ???

UPDATE 1:

how can I deal with types that only exist in one database and does not exist in another

You can't do anything when depending only on a resultset at client side application.
It should always be the main select query that selects proper data from a composite data type like geometry, address, etc.
Examle: select addess.city, address.zipcode, x( geometry_column ), y( geometry_column )

To give an example of geometry data type:
MySQL has definitions for its Spatial Support. But I am not sure how far they are good compared to SQL Server's implementation of Spatial Data.

geometry is a composite data type and hence can't be retrieved by direct query.
You require dependent function(s) that parses data from such data columns and return in readable, identifiable data formats.
Example: create table geom ( g geometry );
Converting ResultSet from select g from geom using JAVA to a create table statement would result with an unknwon data type for column g.

Create table geom ( g UNKNOWN )

Using x(g), y(g) co-ordinate functions on column g will return proper and acceptable data types.
Query select x(g), y(g) from geom will be converted to

Create table  ( x(g) DOUBLE( 23, 31 ), y(g) DOUBLE( 23, 31 ) ) 

UPDATE 2:
A resultset might be generated in combination of multiple tables using relations. There is also a chance that the resultset fields are composed of expressions and their aliases. Hence, data types of the resulting column fields or aliases are decided dynamic. Metadata is not aware of exact names of tables, column names and their original/parent data types from the query.

So, it is not possible to get

1. the single name of a table and use it.
2. the parent column's data type and use it.

Note: This is also applicable to all other cross database specific data types, like NVARCHAR, etc.. But, please refer to this posting for an alternative to NVARCHAR usage in MySQL.

Before trying such dynamic data migration, it should be client applications responsibility to know the equivalent data types and use them accordingly.

Also, refer to Data types and ranges for Microsoft Access, MySQL and SQL Server for more information.

这篇关于如何创建基于JDBC结果集的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！