TedNugent TedNugent - 2 months ago 9
Java Question

Dynamically add columns and column names Jdbc/MySql

Is there a way to add dynamically columns and column names to an existing table using jdbc?

For example:

If NumberOfColumns = 3, I want the column names to be "Column1", Column2", "Column3".

I tried to add dynamically some columns with the name of iterator just for testing my code but it gives me an SQL syntax error.

Below are some parts of my code I just described. If I remove the whole loop, the code works like a charm.

public class something{

//Some Variables Declaration///

//Number of columns in test table//
int NumberOfColumns= 3;

public static void main{

//..... SOME CODE....//

//Create database//
sql = "CREATE DATABASE mydb";
stmt.executeUpdate(sql);

//Create test table//
sql = "CREATE TABLE mydb.table " +
"(id INTEGER not NULL ";

stmt.executeUpdate(sql);

//Add columns dynamically//

for (int i=0; i<NumberOfColumns;i++){

sql = "ALTER TABLE mydb.test ADD'"+i+"' VARCHAR(30)";
stmt.executeUpdate(sql);
}
stmt.executeUpdate(sql);
}
}

Answer

First of all you should to delete your stmt.executeUpdate(sql); after your loop, this can make a problem,

change this lines:

for (int i = 0; i < NumberOfColumns; i++) {

    sql = "ALTER TABLE mydb.test ADD'" + i + "' VARCHAR(30)";
    stmt.executeUpdate(sql);
}
stmt.executeUpdate(sql);

just with this:

for (int i = 0; i < NumberOfColumns; i++) {
    colname = "Column" + i;
    sql = "ALTER TABLE mydatabase.table ADD " + colname + " VARCHAR(30)";
    stmt.executeUpdate(sql);
}

Because you can will get an error that the 3ed column exist

also the name of column not need 'id' you just need to remove the two quots

Here is an exemple can solve your problem:

Requires that you initialize a driver so you can open a communications channel with the database, after create your database your tables and your columns,

package DataBase;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class createdatabase {

    //Number of columns in test table//
    private static int NumberOfColumns = 3;

    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost/";

    static final String USER = "root";
    static final String PASS = "mypass";

    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");

            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            stmt = conn.createStatement();

            String sql = "CREATE DATABASE mydatabase";
            stmt.executeUpdate(sql);

            sql = "CREATE TABLE mydatabase.table (id INTEGER not NULL)";
            stmt.executeUpdate(sql);
            String colname;
            for (int i = 0; i < NumberOfColumns; i++) {
                colname = "Column" + i;
                sql = "ALTER TABLE mydatabase.table ADD " + colname + " VARCHAR(30)";
                stmt.executeUpdate(sql);
            }

        } catch (ClassNotFoundException | SQLException e) {
            System.out.println("Exception = " + e);
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException se2) {
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException se) {
                System.out.println("Exception" + se);
            }
        }
    }
}

Hope this can help you

Comments