How to create table based on JDBC Result Set

jdbc program to create table in oracle
how to create table in java using eclipse
jdbc create table
how to retrieve data from resultset in java
resultset java 8
get single row from resultset java
resultset next
how to retrieve multiple rows from resultset in java

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.

How to create table based on JDBC Result Set, 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. How to create a MySQL table based on JDBC Result Set? JDBC Object Oriented Programming Programming The ResultSetMetadata class provides various methods that gives information about the current ResultSet object such as number of columns, name of the table, name of the column, datatype of the column etc…

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.

JDBC - Create Tables Example, JDBC - Create Tables Example - This chapter provides an example on how to create a table Result Sets · JDBC - Data Types · JDBC - Transactions · JDBC - Exceptions The following steps are required to create a new Database using JDBC application − When you run JDBCExample, it produces the following result − JDBC - Updating a Result Set Example - Following is the example, which makes use of the ResultSet.CONCUR_UPDATABLE and ResultSet.TYPE_SCROLL_INSENSITIVE described in the Result Set tutorial.

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)

Retrieving and Modifying Values from Result Sets (The Java , There are four steps for creating a table using JDBC API. Output Table created. View created table in MySQL Command line client - The SELECT statement is the standard way to select rows from a database and view them in a result set. The java.sql.ResultSet interface represents the result set of a database query. A ResultSet object maintains a cursor that points to the current row in the result set.

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;

JDBC, Can I use the ResultSet to create exactly the same table ( schema and items ) on another connection??? I think ResultSet has all information to  A ResultSet object is a table of data representing a database result set, which is usually generated by executing a statement that queries the database. For example, the CoffeeTables.viewTable method creates a ResultSet, rs, when it executes the query through the Statement object, stmt.

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.

Create a table using ResultSet ??? (JDBC and Relational , This tutorial explains how to work with the Java JDBC ResultSet, including how to create a ResultSet, iterate it, access column values, method returns true or false depending on whether the given type is supported or not. New rows may be inserted into a result set table and into the underlying database table using new methods in the JDBC 2.0 core API. To make this possible, the API defines the concept of an insert row .

JDBC ResultSet, a database connection 2) Execute the SQL Query 3) Fetch the data from result set. Pre-requisites include setting up a database schema and creating a table at  Following is the example which makes use of few navigation methods described in the Result Set tutorial. This sample code has been written based on the environment and database setup done in the previous chapters. Import required packages import java.sql.*; public class JDBCExample

JDBC SELECT Query Example, NOTE: The material in this chapter is based on JDBCtm API Tutorial and Reference, The general form of a result set is a table with column headings and the  To run this sample application, you must set the classpath to include the mssql-jdbc jar file. You'll also need access to the sample database. For more information about how to set the classpath, see Using the JDBC Driver.

Java ResultSet, query. Execute the executeUpdate() method of statement object to submit a query to database. Close connection to the database. ResultSet interface. The object of ResultSet maintains a cursor pointing to a row of a table. Initially, cursor points to before the first row. By default, ResultSet object can be moved forward only and it is not updatable.

Comments
  • Actually, I also need to insert the result set in generated table, after creating the table.
  • Possible duplicate of How can I use JDBC to copy schema from one database to another without using Apache DDLUtils?
  • Thanks a lot @Ravinder! This is the answer. But, how can I deal with types that only exist in one database and does not exist in another (keeping in mind that we need a general approach that always works)? For example, MSSQLServer has some geometry type that MySQL does not have.
  • Thanks again for the update on your answer. But another side of my previous question is dealing with different data types between database vendors. For example, SQLServer has NVARCHAR data type, but MySQL does not provide this type. What is the solution for mapping between different databases' types? Because JDBC MetaData will provide vendor specific data, not the standard data type.
  • Thank you! But the general solution was not provided and I think that such thing does not exist currently and needs a script to map these types between different database vendors.
  • Maybe I should be more specific. As I told you, I am building a reporting tool, so the query is unknown and End-User will create the query. Now, currently my program executes this query and now I have the JDBC ResultSet, but I want to store it in my local database, so I should first create the table. Your solution is not good for me, because my query should be executed on a remote database and now I should deal with a JDBC ResultSet.
  • Now I see that the table is from another database - in case it doesn't change a lot, and is not too big - you can copy the relevant tables to your database and run the query
  • No, it is not possible, because I will deal with databases with million records, but the result of user queries are less than a few hundred records. So, do you come up with a better solution?
  • And what is your solution for copying the whole table from a remote database, taking into account that remote databases can be any type of database that can have a JDBC connection (e.g. SQLServer, Oracle, PostgreSQL)?