且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

如何使用动态列Jdbc / Mysql在表中插入值

更新时间:2022-12-17 19:04:51

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

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