- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to create a MySQL table based on JDBC Result Set?
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…
To prepare a CREATE query you need to get −
- Name of the table, using the getTableName() method.
- Column count, to iterate the columns using the getColumnCount() method.
- Name of the each column using the getColumnName() method.
- Data type of each column using the getColumnTypeName() method.
- Precision of each column using the getPrecision() method.
Example
Let us create a table with name customers in MySQL database using the CREATE query as shown below −
CREATE TABLE Customers ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, SALARY DECIMAL (18, 2), ADDRESS VARCHAR (25), PRIMARY KEY (ID) );
Following JDBC program creates another table with name customers2 in the database.
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; public class CreatingTableFromReusultSet { public static void main(String args[]) throws SQLException { //Registering the Driver DriverManager.registerDriver(new com.mysql.jdbc.Driver()); //Getting the connection String mysqlUrl = "jdbc:mysql://localhost/sampledatabase"; Connection con = DriverManager.getConnection(mysqlUrl, "root", "password"); System.out.println("Connection established......"); //Creating the Statement Statement stmt = con.createStatement(); //Query to retrieve records String query = "Select * from Customers"; //Executing the query ResultSet rs = stmt.executeQuery(query); //retrieving the ResultSetMetaData object ResultSetMetaData resultSetMetaData = rs.getMetaData(); //Retrieving the column count of the current table int columnCount = resultSetMetaData.getColumnCount(); String createQuery = "CREATE TABLE " +resultSetMetaData.getTableName(1)+"2 ("; String createQuery2 = ""; for(int i = 1; i<columnCount; i++ ) { createQuery2 = createQuery2+ resultSetMetaData.getColumnName(i)+ " "+resultSetMetaData.getColumnTypeName(i) +"("+resultSetMetaData.getPrecision(i)+"), "; } createQuery2 = createQuery2+ resultSetMetaData.getColumnName(columnCount)+ " "+resultSetMetaData.getColumnTypeName(columnCount) +"("+resultSetMetaData.getPrecision(columnCount)+") ) "; System.out.println("Query to create new table: "); System.out.println(createQuery+createQuery2); stmt.execute(createQuery+createQuery2); System.out.println("Table created ......."); } }
Output
Connection established...... Query to create new table: CREATE TABLE customers2 (ID INT(11), NAME VARCHAR(20), AGE INT(11), SALARY DECIMAL(18), ADDRESS VARCHAR(25) ) Table created ......
Note − This may not work for datatypes for which you need not declare precision. For example, Date.
- Related Articles
- How to generate a “create table” command based on an existing table in MySQL?
- How to create a table in JDBC using another table?
- How to create a table with auto-increment column in MySQL using JDBC?
- How to create a table in Oracle using JDBC?
- How to create a table in JavaDB using JDBC?
- Example to create a table with all datatypes in MySQL using JDBC?
- How to create a table with decimal values using JDBC?
- How to create a table in a database using JDBC API?
- SET only two values for all the rows in a MySQL table based on conditions?
- Update a table based on StudentId value in MySQL?
- Select total from a MySQL table based on month
- MySQL query to get result by month and year based on condition?
- How can we create MySQL view by selecting data based on pattern matching from base table?
- How can I create a MySQL view that takes the values from a table based on some condition(s)?
- How can we set time in a table in JDBC?

Advertisements