如何在具有动态列的表中插入值Jdbc / Mysql

Question

我想在具有动态列的表中添加值。我设法用动态列创建了一个表，但无法弄清楚如何插入数据。

//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?

Answer 1

您还可以使用数据库元数据获取列名。这样的好处是，您甚至不需要知道列名，而是可以在您的代码中动态检索它们。

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;
}

一旦有了列名，就可以像平常一样使用它（List.size（）当然会给出列数）。

更新：

//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
    }

此代码假定您将正确类型的对象传递给PreparedStatement.setObject（Object
o）方法。也可以使用元数据数据库信息来检索列类型，然后使用该信息来进行类型检查，但这会使您的代码复杂得多