java 如何基于JDBC结果集创建表

How to create table based on JDBC Result Set

提问by Mohammad Dashti

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?

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

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?)

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

回答by Ravinder Reddy

We can extract the nearest matchingstructure 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 ???

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

UPDATE 1:

更新 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 querythat selects proper data from a composite data type like geometry, address, etc.
Examle: select addess.city, address.zipcode, x( geometry_column ), y( geometry_column )

当仅依赖客户端应用程序的结果集时，您无法做任何事情。
它应始终是主要选择查询，从像的复合数据类型选择适当的数据geometry，address等等。
Examle：select addess.city, address.zipcode, x( geometry_column ), y( geometry_column )

To give an example of geometrydata 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数据类型的例子：
MySQL 有其空间支持的定义。但我不确定它们与SQL Server 的 Spatial Data 实现相比有多好。

geometryis 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, identifiabledata formats.
Example: create table geom ( g geometry );
Converting ResultSet from select g from geomusing JAVA to a create table statement would result with an unknwondata type for column g.

geometry是一种复合数据类型，因此无法通过直接查询检索。
您需要从这些数据列解析数据并以可读、可识别的数据格式返回的依赖函数。
示例：create table geom ( g geometry );
将 ResultSet 从select g from geom使用 JAVA 转换为 create table 语句将导致unknwon列的数据类型g。

Create table geom ( g UNKNOWN )

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

在列上使用x(g),y(g)坐标函数g将返回正确和可接受的数据类型。
查询select x(g), y(g) from geom将转换为

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.

更新 2：
结果集可能会使用关系在多个表的组合中生成。结果集字段也有可能由表达式及其别名组成。因此，结果列字段或别名的数据类型是动态决定的。元数据不知道查询中的表、列名及其原始/父数据类型的确切名称。

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.

1. 表的单一名称并使用它。
2. 父列的数据类型并使用它。

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.

注意：这也适用于所有其他跨数据库特定数据类型，如NVARCHAR等。但是，请参阅此帖子以了解 MySQL 中 NVARCHAR 用法的替代方法。

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 Serverfor more information.

此外，请参阅Microsoft Access、MySQL 和 SQL Server 的数据类型和范围以获取更多信息。

回答by Barranka

Maybe this is a little tricky, but I think it might help you.

也许这有点棘手，但我认为它可能对您有所帮助。

JDBC ResultSet objects have a getMetaData()method that returns a ResultSetMetaDataobject that contains, among other things, the column names and column types. You could do something like this:

JDBC ResultSet 对象有一个getMetaData()方法，该方法返回一个ResultSetMetaData对象，其中包含列名和列类型等。你可以这样做：

ResultSet rs;
String strSQL;

...

strSQL = "create table tbl ("
for(i=0;i<rs.getMetaData().getColumnCount(),i++) {
    if(i>0)
        strSQL += ", "
    strSQL += rs.getMetaData().getColumnName(i) 
           + " " 
           + rs.getMetaData().getColumnType(i);
}
strSQL+= ")"

....

You can construct this way a valid SQL DML that has the columns you need. After that, all that remains is populate the table.

您可以通过这种方式构造一个包含您需要的列的有效 SQL DML。之后，剩下的就是填充表。

Hope this helps you.

希望这对你有帮助。

回答by rajesh

You probably have the ResultSet based on an execution of a select query.Now instead of getting the ResultSet and then iterating through it to create the table, we can also execute a single query for table creation.

您可能拥有基于执行选择查询的 ResultSet。现在，不是获取 ResultSet 然后迭代它以创建表，我们还可以执行单个查询来创建表。

Your original query:

您的原始查询：

select * from table_name

select * from table_name

Instead of executing this and iterating, execute this

执行此操作而不是执行此操作并进行迭代

create table new_table_name as (select * from table_name)

create table new_table_name as (select * from table_name)

回答by krakover

As sepcified hereyou can do it in plain SQL like so:

正如这里的sepcified你可以用普通的 SQL 来做，像这样：

CREATE TABLE artists_and_works
  SELECT artist.name, COUNT(work.artist_id) AS number_of_works
  FROM artist LEFT JOIN work ON artist.id = work.artist_id
  GROUP BY artist.id;

回答by Daniel C.

If you want a general solution to deal with types of data that are different between databases but same type in java, you can use JDBCType. Just take Ravinder Reddy solution and change this line:

如果你想要一个通用的解决方案来处理数据库之间不同但java中类型相同的数据类型，你可以使用JDBCType。只需采用 Ravinder Reddy 解决方案并更改此行：

String columnType = rsmd.getColumnTypeName( i );

to

到

String columnType = JDBCType.valueOf(rsmd.getColumnType(i)).getName();

Hope it helps.

希望能帮助到你。