The moose likes JDBC and Relational Databases and the fly likes Create a table using ResultSet ??? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Create a table using ResultSet ???" Watch "Create a table using ResultSet ???" New topic

Create a table using ResultSet ???


Joined: Jun 13, 2001
Posts: 18

I had a topic of "copy table using jdbc". Now I wanted to ask the same question in different way.
I have two connections for two databases on two servers. I got one ResultSet from a table in one connection. Can I use the ResultSet to create exactly the same table ( schema and items ) on another connection???
I think ResultSet has all information to create a table.
Thanks a lot! -Zhining
Jason Menard

Joined: Nov 09, 2000
Posts: 6450
All the info needed to recreate a table is contained in a ResultSetMetaData object which you can get by calling getMetaData() from a ResultSet object. Read the documentation on the ResultSetMetaData interface in the API documentation to see exactly which methods you will need, but everything you need is there.
So to recreate the table do the following:
1. Pull the data from the already populated table (table1) into a ResultSet.
2. Do a getMetaData() on the ResultSet to return you a ResultSetMetaData object.
3. Examine the ResultSetMetaData, extracting out the info you need, and construct the SQL required to create the table.
4. Take the SQL string from step #3 and pass it through JDBC to create your new table (table2).
5. Iterate through your ResultSet and insert the contents into the new table.
That's it. You will probably have to experiment with the ResultSetMetaData object a little bit to figure out what info you need. getColumnType() or getColumnTypeName() will give you what type of Column you need to create in table2 (VARCHAR for example). getColumnClassName() will give you the class which would be returned from a getObject call on a ResultSet column. So really all the info you need is there to recreate any table dynamically.
Hope this helps.

Joined: Jun 13, 2001
Posts: 18
thanks, Jason!!
Frank Carver

Joined: Jan 07, 1999
Posts: 6920
The Java Ranch has thousands of visitors every week, many with surprisingly similar names. To avoid confusion we have a naming convention, described at . We require names to have at least two words, separated by a space, and strongly recommend that you use your full real name. Posts which don't meet our naming standards are not eligible to win books! Please log in with a new name which meets the requirements.


Read about me at ~ Raspberry Alpha Omega ~ Frank's Punchbarrel Blog

Joined: Jun 13, 2001
Posts: 18
Hello, Frank,
Sorry about that! But my real name IS "Zhining Zhang", which is on my dricer license. I used "Zhining Zhang" to register before, but I forgot the password.
I agree. Here's the link:
subject: Create a table using ResultSet ???
jQuery in Action, 3rd edition