如何使用动态列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?

推荐答案

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

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

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

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

更新：

//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）方法。也可以使用元数据库信息检索列类型，然后使用该信息强制执行类型检查，但这会使代码更加复杂

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

这篇关于如何使用动态列Jdbc / Mysql在表中插入值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！