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.

Smita Kapse

Published on 04-Jul-2019 11:56:31