Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Create a table using ResultSet ???

 
ZhiningZhang
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Sheriff
Posts: 6450
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
Jason
 
ZhiningZhang
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks, Jason!!
-ZZ
 
Frank Carver
Sheriff
Posts: 6920
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"ZhiningZhang",
The Java Ranch has thousands of visitors every week, many with surprisingly similar names. To avoid confusion we have a naming convention, described at http://www.javaranch.com/name.jsp . 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.

Thanks.
 
ZhiningZhang
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
-Zhining
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic