How to create table based on JDBC Result Set

Question

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

Answer 1

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.

Answer 2

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

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

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.

Hope this helps you.

Answer 3

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.

Your original query:

select * from table_name

Instead of executing this and iterating, execute this

create table new_table_name as (select * from table_name)

Answer 4

As sepcified here you can do it in plain SQL like so:

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;

Answer 5

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:

String columnType = rsmd.getColumnTypeName( i );

to

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

Hope it helps.