CODEWITHSUNDEEP
javamysqljdbc
TedNugent
TedNugent

Reputation: 21

How to insert values in a table with dynamic columns Jdbc/Mysql

I want to add values in a table that has dynamic columns. I managed to create a table with dynamic columns but I cannot figure out how to insert data.

//Create Table

sql = "CREATE TABLE MyDB.myTable" +
        "(level INTEGER(255) )";

        int columnNumber = 5; //Number of columns

          //Add columns

        for (i=0;i<columnNumber;i++){
              String columnName = "Level_" +i:
              String sql = "ALTER TABLE MyDB.myTable ADD " + columnName + " INTEGER(30)";
    }

//Insert Data

//How to insert data dynamically, without knowing the number of columns?

Upvotes: 0

Views: 19263

Answers (2)

verhie
verhie

Reputation: 1318

If you know the number of columns you want to insert, you can make your insert query same way you made your CREATE TABLE. Explicitly name the columns you want to add your data into, and make sure the columns you leave empty are allowed to be empty (NULL or default=0)

INSERT INTO MyDB.myTable (Level_1, Level_2, ...) VALUES (Val_1, Val_2, ...);

The alternative approach would be to store each inserted value in a separate row. In that way you don't vhave to change the number of columns in your table.

You need a table where you have a ID for every group of values: - ID - Level - Value

You could have a separate table where you can register each ID: - ID (bigInt, auto increment, primary key) - info field - timestamp

Now, for every set of data you want to insert, first insert need a Unique ID. If you use the second table, inserting a new row would give you a new ID:

INSERT INTO register_table (ID, info, timestamp) VALUES (NULL, 'some info', NOW());

This will give you a new ID (last_inserted_id). With this ID now insert all values in the other table:

for(i=0i<columnNumber;i++){
    "INSERT INTO _table (ID, Level, _Value) VALUES ("+ the_ID +", "+ i +", "+ the_VALUE +");";
}

If you want to fetch the data:

"SELECT Level, _Value FROM _table WHERE ID="+ the_ID +" ORDER BY Level;";

Upvotes: 0

dsp_user
dsp_user

Reputation: 2119

You can also use database metadata to get the column names. This has the advantage that you even don't need to know the column names, rather they are retrieved dynamically in your code.

public static List<String> getColumns(String tableName, String schemaName) throws  SQLException{

    ResultSet rs=null;

    ResultSetMetaData rsmd=null;
    PreparedStatement stmt=null;
    List<String> columnNames =null;
    String qualifiedName = (schemaName!=null&&!schemaName.isEmpty())?(schemaName+"."+tableName):tableName;
    try{
        stmt=conn.prepareStatement("select * from "+qualifiedName+" where 0=1");
        rs=stmt.executeQuery();//you'll get an empty ResultSet but you'll still get the metadata
        rsmd=rs.getMetaData();
        columnNames = new ArrayList<String>(); 
        for(int i=1;i<=rsmd.getColumnCount();i++)
            columnNames.add(rsmd.getColumnLabel(i));    
    }catch(SQLException e){
        throw e;//or log it
    }
    finally{
        if(rs!=null)
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                throw e
            }
        if(stmt!=null)
            try {
                stmt.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                throw e
            }
    }
    return columnNames;
}

Once you have the column names, you can use it as you normally would (List.size() would of course give the number of columns).

UPDATE: 

//I will assume that your values (data to be inserted) is a List of Object types and that it is already populated



List<Object> data = new ArrayList<>();
    //you will populate this list

    //getting the column names
    List<String> columnNames = getColumns("MyTable", "MyDB");

    String insertColumns = ""; 
    String insertValues = "";

    if(columnNames != null && columnNames.size() > 0){
        insertColumns += columnNames.get(0);
        insertValues += "?";
    }


    for(int i = 1; i < columnNames.size();i++){
      insertColumns += ", " + columnNames.get(i) ;
      insertValues += "?";
    }

    String insertSql = "INSERT INTO MyDB.MyTable (" + insertColumns + ") values(" + insertValues + ")"; 

    try{
    PrepareStatement ps = conn.prepareStatement(insertSql);

    for(Object o : data){
     ps.setObject(o); //you must pass objects of correct type
    }
    ps.execute(); //this inserts your data
    }catch(SQLException sqle){
      //do something with it
    }

This code assume that you pass objects of correct types to PreparedStatement.setObject(Object o) method. It's also possible to retrieve column types using metadatabase information and then use that info to enforce type checking but that would make your code much more complicated

Upvotes: 5

Related Questions