I have developed a stock intelligent analysis software with powerful functions. If you need to click the following link to get it:
https://www.cnblogs.com/bclshuai/p/11380657.html
Qt use database
catalogue
one Development environment... 1
two Configure work... 1
two point one Database driven configuration... 1
two point two Create database add database table... 2
three Qt using database... 2
three point one Include header and library files... 3
three point two Initialize database... 3
three point three Open database... 4
three point four Insert data... 4
three point five Update data... 5
three point six Query data... 6
three point seven Delete data... 9
one development environment
Qt5.9.6 vs2015, use QSQLITE to save data to local database file. Realize the function of adding, deleting, modifying and querying.
two Configuration work
two point one Database driven configuration
Install Qt in D: \ Qt \ Qt5.9.6 \ 5.9.6 \ msvc2015_ Copy the database driver folder sqldrivers of 64 \ plugins \ to your development program exe path, as shown in the following figure.
two point two Create database and add database tables
Use the Sqlite tool DB Browser for SQLite.exe to create a database and create a new table for the database.
three Qt use database
three point one Contains header and library files
three point two Initialize database
db_ = QSqlDatabase::addDatabase("QSQLITE");
QString dbpath = QCoreApplication::applicationDirPath() + "/localSqliteDb";
db_.setDatabaseName(dbpath);
three point three Open database
if (!db_.open())
{
msg = tr("open local database fail");
break;
}
three point four insert data
Query is a database request. You can execute SQL statements, organize data into a string, and insert data according to SQL syntax. You can also bind values with aliases in SQL templates through bindValue function, such as binding the value name in task and the label: name in SQL template. When executing SQL statements, you will replace: name with the value name in task, Execute SQL statements through the exec function.
int LocalDb::onSaveTask(QVariantMap & task,QString& strMsg) { int errorCode = -1; do { QSqlQuery query; query.prepare("INSERT INTO alarmTask (id, name,type,target,similarity,time,createtime) " "VALUES ( :id,:name,:type,:target,:similarity,:time,:createtime)"); query.bindValue(":id", QUuid::createUuid()); query.bindValue(":name", task.value("name")); query.bindValue(":type", task.value("type")); query.bindValue(":target", task.value("target")); query.bindValue(":time", task.value("time")); query.bindValue(":similarity", task.value("similarity")); query.bindValue(":createtime", task.value("createtime")); if (!query.exec()) { QSqlError error = query.lastError(); errorCode = error.type(); strMsg = error.text(); LOG_ERROR("insert task failed %s", strMsg.toStdString().c_str()); break; } errorCode = 0; //Create picture parsing task } while (0); return errorCode; }
three point five Update data
UPDATE statement is used to update data. QSqlQuery method is also used
int LocalDb::onUpdateTask(QVariantMap & task, QString & strMsg) { int errorCode = -1; do { QSqlQuery query; query.prepare("update alarmTask set name=:name,type=:type,target=:target,similarity=:similarity,time=:time,createtime=:createtime where id=:id"); query.bindValue(":id", task.value("id")); query.bindValue(":name", task.value("name")); query.bindValue(":type", task.value("type")); query.bindValue(":target", task.value("target")); query.bindValue(":time", task.value("time")); query.bindValue(":similarity", task.value("similarity")); query.bindValue(":createtime", task.value("createtime")); if (!query.exec()) { QSqlError error = query.lastError(); strMsg = error.text(); errorCode = error.type(); LOG_ERROR("UpdateTask failed %s", strMsg.toStdString().c_str()); break; } errorCode = 0; //Create picture parsing task } while (0); return errorCode; }
three point six Query data
When using a query, if there are multiple conditions, such as select * from vehicle where vehicle type in (: vehicle color), if you use query.bindvalue (": vehicle color", task.value ("vehicle color")); When the vehiclecolor value is multiple, for example, 'Red', 'yellow'. If you use query.bindValue(":vehiclecolor", task.value("vehiclecolor")); Binding value, you will find that the statement is invalid when executing. Only valid after organizing colors into strings with, strcondition + = qstring ("vehiclecolor in (% 1) AND").arg(strtemp);
void LocalDb::SlotQueryVehicleByCondition(int pageno, int pagesize, QMap<QString, QMap<QString, QString>> cond) { int errorCode = 0; QString msg; QString sqlcount = "SELECT count(*) from vehicle "; QVariantMap bindvalue; QString sqlserchpage = "SELECT * from vehicle ";//ORDER BY time DESC LIMIT :limit OFFSET :offset"; QString strCondition = ""; QString strtemp = ""; if (cond.size() > 0) { strCondition = "where"; //Vehicle type if (cond.contains("vehicletype")) { strtemp = GetStringCondition(cond["vehicletype"].keys()); strCondition += QString(" vehicletype in (%1) AND").arg(strtemp); } //license plate if (cond.contains("vehiclenum")) { strtemp = cond["vehiclenum"]["vehiclenum"]; strCondition += QString(" vehiclenum in ('%1') AND").arg(strtemp); } //Feature identification if (cond.contains("mark")) { if (cond["mark"].contains("copilot")) { strCondition += QString(" visepilot in ('yes') AND"); } if (cond["mark"].contains("Pendant")) { strCondition += QString(" hangthing in ('yes') AND"); } if (cond["mark"].contains("Sun visor")) { strCondition += QString(" hidesunplate in ('yes') AND"); } if (cond["mark"].contains("Dangerous goods")) { strCondition += QString(" danger in ('yes') AND"); } } //License plate color if (cond.contains("platecolor")) { strtemp = GetStringCondition(cond["platecolor"].keys()); strCondition += QString(" platecolor in (%1) AND").arg(strtemp); } //Body color if (cond.contains("vehiclecolor")) { strtemp = GetStringCondition(cond["vehiclecolor"].keys()); strCondition += QString(" vehiclecolor in (%1) AND").arg(strtemp); } if (cond.contains("time")) { if (cond["time"].contains("starttime")) { strCondition += QString(" time >='%1' AND").arg(cond["time"]["starttime"]); } if (cond["time"].contains("endtime")) { strCondition += QString(" time <='%1' AND").arg(cond["time"]["endtime"]); } } if (strCondition.right(4) == " AND")//Remove the last AND { strCondition = strCondition.left(strCondition.length() - 4); } if (strCondition == "where")//Unconditional { strCondition = ""; } } QVariantMap replyData; do { //Find out the total qualified quantity first QSqlQuery countquery; //Delete the data in the database first sqlcount += strCondition; countquery.prepare(sqlcount); if (!countquery.exec() || !countquery.next()) { QSqlError error = countquery.lastError(); errorCode = error.type(); msg = error.text(); LOG_ERROR("query vehicle count failed,msg:%s", msg.toStdString().c_str()); break; } quint64 totalCount = countquery.record().value(0).toULongLong(); if (0 >= totalCount) { break; } QVariantList dataList; QSqlQuery query; sqlserchpage += strCondition; sqlserchpage += QString(" ORDER BY time DESC LIMIT %1 OFFSET %2").arg(pagesize).arg((pageno)* pagesize); query.prepare(sqlserchpage); if (!query.exec()) { QSqlError error = query.lastError(); errorCode = error.type(); msg = error.text(); break; } while (query.next()) { QSqlRecord record = query.record(); int column = record.count(); QVariantList recorditem; for (int i = 0; i < column; i++) { QVariantMap data; data.insert("N", record.fieldName(i)); data.insert("V", record.value(i)); recorditem.append(data); } QVariantMap item; item.insert("data", recorditem); dataList.append(item); } replyData.insert("totalCount", totalCount); replyData.insert("data", dataList); } while (0); singalQueryVehicle(errorCode, msg, replyData); }
three point seven Delete data
Delete is used to delete data. If the table is empty, there is no where condition. Similarly, if you delete multiple records at one time, you cannot bind the value with bindValue. The timing bound value is a spliced string, and it is useless to add quotation marks. You need to organize a string directly. Add the values of multiple taskids to the string.
int LocalDb::deleteAlarmTask(QString id,QString& strMsg) { if (id=="") { return -1; } int errorCode = 0; QString msg; do { QSqlQuery query; //Delete the data in the database first query.prepare("delete from alarmTask where id =:taskId"); query.bindValue(":taskId",id); if (!query.exec()) { QSqlError error = query.lastError(); errorCode = error.type(); msg = error.text(); LOG_ERROR("delete %s failed,msg:%s", id.toStdString().c_str(), msg.toStdString().c_str()); return -1; } } while (0); return 0; }