QSqlQuery插入完整的结构而不是单个字段？

Following QSqlQuery Class documentation one can implemented an insert operation in a MySQL database, i.e. like this:

在QSqlQuery类文档之后，可以在MySQL数据库中实现插入操作，即如下所示：

QSqlQuery query;
query.prepare("INSERT INTO person (id, forename, surname) "
              "VALUES (:id, :forename, :surname)");
query.bindValue(":id", 1001);
query.bindValue(":forename", "Bart");
query.bindValue(":surname", "Simpson");
query.exec();

It works. But it's fragile, isn't? It will stop working (because names are hard coded) when a column name mismatch occurs, if the database programmer changes a column name in the MySQL script, i.e. surname to lastname.

有用。但它很脆弱，不是吗？如果数据库程序员在MySQL脚本中更改了列名，即姓氏到姓氏，则当列名不匹配时，它将停止工作（因为名称是硬编码的）。

What came to my mind was to change the whole mechanism so that instead of inserting individual fields one big object (perhaps a struct composed by the individual fields) should be inserted. And then, in the db side, the fields should be split back into a set of fields, i.e. using a view or a trigger.

我想到的是改变整个机制，以便不应插入单个字段，而应插入一个大对象（可能是由各个字段组成的结构）。然后，在db端，应将字段拆分回一组字段，即使用视图或触发器。

Am I going in the right direction?

我正朝着正确的方向前进吗？

I'll appreciate general comments on the issue.

我将很感激对这个问题的一般评论。

1 个解决方案

#1

Well, there is a way, but you must be sure that structure of the table and column meaning wouldn't change and your query has data of each column to insert.

好吧，有一种方法，但您必须确保表和列含义的结构不会改变，并且您的查询包含要插入的每列的数据。

Firstly, you must select all information about your table:

首先，您必须选择有关您的表的所有信息：

SELECT c.column_name FROM information_schema.columns c 
WHERE c.table_name = 'some_table_name' 
ORDER BY c.ordinal_position ASC

Secondly, your code can be change like this. I am using universal method that meaning you have already known data:

其次，您的代码可以像这样更改。我正在使用通用方法，这意味着您已经知道数据：

QList<QVariant> valuesList { QVariant(1), QVariant("Bart"), QVariant("Simpson") }; 
QList<QPair<QString, QVariant>> varList;
QSqlQuery query;
query.exec("SELECT @rownum := @rownum + 1 AS row_num, c.column_name 
            FROM information_schema.columns c, (SELECT @rownum := -1) r
            WHERE c.table_name = 'some_table_name' 
            ORDER BY c.ordinal_position ASC");
while(query.next())
    colList << QPair<QString, QString>(query.value(1).toString(), 
               valuesList.at(query.value(0).toInt()));

QString queryString = "INSERT INTO person (%1) "
                      "VALUES (%2)";

QString insertColsString;
QString bindColsString;

for(int i = 0; i < colList.size(); i++) {
    insertColsString += colList.at(i).first + ", ";
    bindColsString += ":" colList.at(i).first + ", ";
}

if(!insertColsString.isEmpty()) {
    insertColsString.chop(2);
    bindColsString.chop(2);
}

query.prepare(queryString.arg(insertColsString, bindColsString));

for(int i = 0; i < colList.size(); i++)
    query.bindValue(":" + colList.at(i).first, colList.at(i).second);

query.exec();

#1