java 如何在具有动态列的表中插入值 Jdbc/Mysql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39464293/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert values in a table with dynamic columns Jdbc/Mysql
提问by TedNugent
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?
回答by dsp_user
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).
一旦你有了列名，你就可以像往常一样使用它（List.size() 当然会给出列数）。
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
此代码假定您将正确类型的对象传递给 PreparedStatement.setObject(Object o) 方法。也可以使用元数据库信息检索列类型，然后使用该信息来强制执行类型检查，但这会使您的代码更加复杂
回答by verhie
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)
如果您知道要插入的列数，则可以使用与创建 CREATE TABLE 相同的方式进行插入查询。明确命名要添加数据的列，并确保您留空的列允许为空（NULL 或默认值=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
您需要一个表格，其中每组值都有一个 ID： - ID - 级别 - 值
You could have a separate table where you can register each ID: - ID (bigInt, auto increment, primary key) - info field - timestamp
您可以有一个单独的表，您可以在其中注册每个 ID： - ID（bigInt，自动增量，主键） - 信息字段 - 时间戳
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:
现在，对于要插入的每组数据，首先插入需要一个唯一 ID。如果你使用第二个表，插入一个新行会给你一个新的 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:
这将为您提供一个新 ID (last_inserted_id)。现在使用此 ID 在另一个表中插入所有值：
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;";
