[Qt 数据库 -- SQLite]
Qt 本身支持 SQLite 数据库，pro文件只需要加入数据库模块
QT += sql
包含头文件
#include <QSqlDatabase> #include <QSqlError> #include <QSqlQuery> #include <QSqlRecord>
下面以一套流程具体实现一个表的创建和CURD
1. 定义一个容器，用来存储表名和初始化代码
QSqlDatabase m_database; QMap<QString, QString> m_myDBTableMap; // 数据库的表
2. 创建数据表结构体与sql语句
struct Records { // 主键，正常 >= 0 int id = -1; // 类型 snap photo RECORD_TYPE recordType = RECORD_TYPE::SNAP; // 路径 QString path = ""; // 通道类型 CHANNEL_TYPE channelType = CHANNEL_TYPE::ETD_SE; // 主图区 1--左； 2--右 int view = 1; // 分辨率 int resX = 768; int resY = 512; // 驻点时间 double speed = 1; // 高压 int hv = 0; // 放大倍数 int multiple = 100; // HFW 扫描区域 QString hfw = "1mm"; // 工作距离 double workDistance = 0; // time qint64 time = 0; // photo QByteArray photo; // 新记录的标志位，默认是新插入的 bool newFlag = true; // 已收藏的标志， 默认未收藏 bool collected = false; // userid 表关联 int userid; }; Q_DECLARE_METATYPE(Records) const QString CREATE_RECORDS_TABLE = "create table Records (id INTEGER PRIMARY KEY AUTOINCREMENT, " "path TEXT NOT NULL, type INTEGER NOT NULL, channel INTEGER NOT NULL, " "resX INTEGER NOT NULL, resY INTEGER NOT NULL, speed REAL NOT NULL, " "hv INTEGER, multiple INTEGER, hfw TEXT, " "workDistance REAL, newFlag BOOLEAN DEFAULT 0, collected BOOLEAN DEFAULT 0, " "time INTEGER DEFAULT 0, photo BLOB, userid INTEGER NOT NULL);";
3. 创建表
void SqlDB::init() { // 初始化表 m_myDBTableMap.clear(); m_myDBTableMap.insert("Records", CREATE_RECORDS_TABLE); m_myDBTableMap.insert("Users", CREATE_USERS_TABLE); m_myDBTableMap.insert("Version", CREATE_VERSION_TABLE); m_myDBTableMap.insert("FilamentRecords", CREATE_FILAMENT_RECORDS_TABLE); InitMyDBAndTable(); }
4. 初始化表
const QString SQL_PATH = QStandardPaths::writableLocation(QStandardPaths::AppDataLocation) + "SEM/data.db";
void SqlDB::InitMyDBAndTable() { QFile file(SQL_PATH); if(!file.exists()) { // 生成默认的 db 文件 file.open(QIODevice::WriteOnly); file.close(); if(!connectMyDB()) { return; } // 初始化数据表 QMap<QString, QString>::const_iterator map = m_myDBTableMap.constBegin(); while (map != m_myDBTableMap.constEnd()) { QSqlQuery sql_query; sql_query.prepare(map.value()); if(!sql_query.exec()) { QLOG_ERROR(QString("Error: Fail to create %1 table: %2").arg(map.key()).arg(sql_query.lastError().driverText())); } ++map; } // 初始化 version table 的版本号 1.6 insertVersion(PRODUCT_VERSION); } else { if(!connectMyDB()) { return; } // 版本号判断 1.6 < 1.7 则执行数据库迁移 QString oldVer; getVersion(oldVer); if(QString::compare(PRODUCT_VERSION, oldVer) > 0) { // 数据库升级 UpdateMyDBAndTable(); insertVersion(PRODUCT_VERSION); } } }
5. 连接数据库
bool SqlDB::connectMyDB() { if(!m_database.isValid() || !m_database.isOpen()) { m_database = QSqlDatabase::addDatabase("QSQLITE"); m_database.setDatabaseName(SQL_PATH); return m_database.open(); } return true; }
6. 数据库迁移 【字段增量更新】
/** * @brief getFieldsVector 根据sql语句拆解为字段向量 * @param sql * @return */ QVector<QString> getFieldsVector(const QString sql) { int startPos, endPos; startPos = sql.indexOf("(");endPos = sql.indexOf(")"); QString sqlTemp = sql.mid(startPos + 1, endPos - startPos - 1); QStringList arrayList = sqlTemp.split(","); QVector<QString> res; for(int i = 0; i< arrayList.length(); i++) { QString itemStr = arrayList.at(i).trimmed(); QStringList itemArray = itemStr.split(" "); QString key = itemArray[0]; res.append(key); } return res; } void SqlDB::UpdateMyDBAndTable() { QMap<QString, QString> newSqlMap = m_myDBTableMap; // 认为当前的表map是最新的 QMap<QString, QString> oldSqlMap; QSqlQuery sql_query(m_database); if(!sql_query.exec(QString("select * from sqlite_master where type=\"table\";"))) { QLOG_ERROR(QString("Error: Fail to get table: %1").arg(sql_query.lastError().driverText())); return; } else { // 获取当前数据库的表结构 while(sql_query.next()) { QString pName = sql_query.value("tbl_name").toString(); QString pSql = sql_query.value("sql").toString(); oldSqlMap.insert(pName, pSql); } // 找出新增字段的表（相对原有的表） QMap<QString, QString> changeTableMap; // 捕获发生字段更新的表 QMap<QString, QString>::const_iterator newMap = newSqlMap.constBegin(); for(; newMap != m_myDBTableMap.constEnd(); newMap++) { QString tableName = newMap.key(); QMap<QString, QString>::const_iterator oldMap = oldSqlMap.constBegin(); for(; oldMap != oldSqlMap.constEnd(); oldMap++) { QString oldTableName = oldMap.key(); // 表相同时，查询字段是否相同 if(tableName.compare(oldTableName) == 0) { QVector<QString> newTableFields = getFieldsVector(newMap.value()); QVector<QString> oldTableFields = getFieldsVector(oldMap.value()); // 如果发生字段更新 if(newTableFields.size() != oldTableFields.size()) { // 封装查询字段 QString fileds; for(int i = 0; i < oldTableFields.size(); i++) { QString oneField = oldTableFields[i]; if(newTableFields.contains(oneField)) { if(!fileds.isEmpty()) { fileds.append(","); } fileds.append(oneField); } } // sql 命令行 QString strSql; strSql.append( QString("CREATE TEMPORARY TABLE %1_backup(%2);").arg(tableName).arg(fileds) ); strSql.append( QString("INSERT INTO %1_backup SELECT %2 FROM %3;").arg(tableName).arg(fileds).arg(tableName) ); strSql.append( QString("DROP TABLE %1;").arg(tableName) ); strSql.append( newMap.value() ); // 创建新表 strSql.append( QString("INSERT INTO %1(%2) SELECT %3 FROM %4_backup;").arg(tableName).arg(fileds).arg(fileds).arg(tableName) ); strSql.append( QString("DROP TABLE %1_backup;").arg(tableName) ); // 发生表结构变化的map changeTableMap.insert(newMap.key(), strSql); // 移除变更的map newSqlMap.erase(newSqlMap.find(newMap.key())); } // 找到同名的表，则跳出第二层的循环 break; } } } // 找出新增的表 QMap<QString, QString>::const_iterator iter = newSqlMap.constBegin(); QMap<QString, QString> createTableMap; for(; iter != newSqlMap.constEnd(); iter++) { QString tableName = iter.key(); bool bFind = false; QMap<QString, QString>::const_iterator oldIter = oldSqlMap.constBegin(); for(; oldIter != oldSqlMap.constEnd(); oldIter++) { QString oldTableName = oldIter.key(); if(tableName.compare(oldTableName) == 0) { bFind = true; break; } } if(!bFind) { createTableMap.insert(iter.key(), iter.value()); } } // 创建新的表 QMap<QString, QString>::const_iterator it = createTableMap.constBegin(); for(; it != createTableMap.constEnd(); it++) { QStringList sqlList = it.value().split(";"); for(int i = 0; i < sqlList.length(); i++) { QString singleSql = sqlList.at(i).trimmed(); if(!singleSql.isEmpty()) { if(!sql_query.exec(singleSql.append(";"))) { QLOG_ERROR(QString("Error: Fail to create %1 table: %2").arg(it.key()).arg(sql_query.lastError().driverText())); } } } } // 更新表字段 it = changeTableMap.constBegin(); for(; it != changeTableMap.constEnd(); it++) { QStringList sqlList = it.value().split(";"); for (int i = 0; i < sqlList.length(); i++) { QString singleSql = sqlList.at(i).trimmed(); if (!singleSql.isEmpty()) { if (!sql_query.exec(singleSql.append(";"))) { QLOG_ERROR(QString("Error: Fail to update %1 table: %2").arg(it.key()).arg(sql_query.lastError().driverText())); } } } } } }
7. 插入数据【方式一】
const QString INSERT_RECORDS_TABLE = "INSERT INTO Records VALUES(NULL,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);"; bool SqlDB::insertRecord(Records records) { if (!connectMyDB()) { return false; } // 如果已经存在该记录，则直接删除 if(this->exitPath(records.path)) { this->deleteRecordByPath(records.path); } QSqlQuery sql_query(m_database); sql_query.prepare(INSERT_RECORDS_TABLE); sql_query.addBindValue(records.path); sql_query.addBindValue(records.recordType); sql_query.addBindValue(records.channelType); sql_query.addBindValue(records.resX); sql_query.addBindValue(records.resY); sql_query.addBindValue(records.speed); sql_query.addBindValue(records.hv); sql_query.addBindValue(records.multiple); sql_query.addBindValue(records.hfw); sql_query.addBindValue(records.workDistance); sql_query.addBindValue(records.newFlag); sql_query.addBindValue(records.collected); sql_query.addBindValue(records.time); sql_query.addBindValue(records.photo); sql_query.addBindValue(records.userid); if(!sql_query.exec()) { qDebug() << "Error: Fail to insert data." << sql_query.lastError(); QLOG_ERROR(QString("Error: Fail to insert data: %1").arg(sql_query.lastError().driverText())); return false; } return true; }
插入数据【方式二】
const QString INSERT_RECORDS_TABLE = "INSERT INTO Records(path, type, channel, resX, resY, speed, hv, multiple, hfw, workDistance, newFlag, collected, time, photo, userid) VALUES(:path, :type, :channel, :resX, :resY, :speed, :hv, :multiple, :hfw, :workDistance, :newFlag, :collected, :time, :photo, :userid);"; bool SqlDB::insertRecord(Records records) { if (!connectMyDB()) { return false; } // 如果已经存在该记录，则直接删除 if(this->exitPath(records.path)) { this->deleteRecordByPath(records.path); } QSqlQuery sql_query(m_database); sql_query.prepare(INSERT_RECORDS_TABLE); sql_query.bindValue(":path", records.path); sql_query.bindValue(":type", records.recordType); sql_query.bindValue(":channel", records.channelType); sql_query.bindValue(":resX", records.resX); sql_query.bindValue(":resY", records.resY); sql_query.bindValue(":speed", records.speed); sql_query.bindValue(":hv", records.hv); sql_query.bindValue(":multiple", records.multiple); sql_query.bindValue(":hfw", records.hfw); sql_query.bindValue(":workDistance", records.workDistance); sql_query.bindValue(":newFlag", records.newFlag); sql_query.bindValue(":collected", records.collected); sql_query.bindValue(":time", records.time); sql_query.bindValue(":photo", records.photo); sql_query.bindValue(":userid", records.userid); if(!sql_query.exec()) { qDebug() << "Error: Fail to insert data." << sql_query.lastError(); QLOG_ERROR(QString("Error: Fail to insert data: %1").arg(sql_query.lastError().driverText())); return false; } return true; }
8. 删除记录
const QString DELETE_RECORDS_BY_PATH = "DELETE FROM Records WHERE path='%1';"; const QString DELETE_RECORDS_BY_ID = "DELETE FROM Records WHERE id=:id;"; const QString DELETE_RECORDS_TABLE = "DELETE FROM Records;"; bool SqlDB::deleteRecordByPath(QString path) { if (!connectMyDB()) { return false; } QSqlQuery sql_query(m_database); path.replace("'", "''"); QString str = QString(DELETE_RECORDS_BY_PATH).arg(path); sql_query.prepare(str); return sql_query.exec(); } bool SqlDB::deleteRecordById(int _id) { if (!connectMyDB()) { return false; } QSqlQuery sql_query(m_database); sql_query.prepare(DELETE_RECORDS_BY_ID); sql_query.bindValue(":id", _id); return sql_query.exec(); } bool SqlDB::deleteAllRecords() { if (!connectMyDB()) { return false; } QSqlQuery sql_query(m_database); sql_query.prepare(DELETE_RECORDS_TABLE); return sql_query.exec(); }
9. 更改记录
const QString UPDATE_RECORDS_PHOTO = "UPDATE Records SET photo = :photo WHERE id = :id;"; const QString UPDATE_RECORDS_FLAG = "UPDATE Records SET newFlag = :newFlag, collected = :collected WHERE id = :id;"; const QString UPDATE_RECORDS_NOCOLLECT = "UPDATE Records SET collected = false WHERE collected = true;"; bool SqlDB::updateRecordPhoto(Records records) { if (!connectMyDB()) { return false; } QSqlQuery sql_query(m_database); sql_query.prepare(UPDATE_RECORDS_PHOTO); sql_query.bindValue(":photo", records.photo); sql_query.bindValue(":id", records.id); if (!sql_query.exec()) { QLOG_ERROR(QString("Error: Fail to update data: %1").arg(sql_query.lastError().driverText())); return false; } return true; } bool SqlDB::updateRecordFlag(Records records) { if (!connectMyDB()) { return false; } QSqlQuery sql_query(m_database); sql_query.prepare(UPDATE_RECORDS_FLAG); sql_query.bindValue(":newFlag", records.newFlag); sql_query.bindValue(":collected", records.collected); sql_query.bindValue(":id", records.id); if (!sql_query.exec()) { QLOG_ERROR(QString("Error: Fail to update data: %1").arg(sql_query.lastError().driverText())); return false; } return true; }
10. 查找记录
const QString SELECT_RECORDS_INFO = "SELECT * FROM Records WHERE path='%1';"; const QString SELECT_RECORDS_LIMIT = "SELECT * FROM Records ORDER BY time DESC LIMIT %1;"; const QString SELECT_RECORDS_INFO_LIMIT = "SELECT * FROM Records WHERE userid='%1' ORDER BY time DESC LIMIT %2;"; //%1 => userid=:userid AND %2 => collected=true AND %3 => DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(time) AND const QString SELECT_RECORDS_INFO_BY = "SELECT * FROM Records WHERE %1 %2 %3 path LIKE :conditions ORDER BY time DESC LIMIT :limit OFFSET :offset;"; const QString SELECT_RECORDS_COUNT = "SELECT count(*) FROM Records;"; QList<Records> SqlDB::getAllRecordsByCondition(int _id, QString _condition, bool _collected, int _days, int _limit, int _offset) { QList<Records> returnRecords; Records tempRecord; if (!connectMyDB()) { return returnRecords; } QSqlQuery sql_query(m_database); // 筛选当前用户 QString _userid = _id != -1 ? QString("userid='%1' AND").arg(_id) : ""; // 是否筛选收藏 QString _collectedCondition = _collected ? "collected=true AND" : ""; // 筛选最近n天 QString _daysConditon = _days >= 1 ? QString("julianday('now')-julianday(date(time, 'unixepoch')) <= %1 AND").arg(_days) : ""; QString sqlStr = QString(SELECT_RECORDS_INFO_BY).arg(_userid).arg(_collectedCondition).arg(_daysConditon); QString conStr = ""; for(int i = 0; i< _condition.length(); i++) { conStr += QString("%%1%").arg(_condition[i]); } if(conStr.isEmpty()) conStr = "%"; sql_query.prepare(sqlStr); sql_query.bindValue(":conditions", conStr); sql_query.bindValue(":limit", _limit); sql_query.bindValue(":offset", _offset); if(!sql_query.exec()) { qDebug() << "Error: Fail to select all data." << sql_query.lastError(); QLOG_ERROR(QString("Error: Fail to get All data: %1").arg(sql_query.lastError().driverText())); } else { while(sql_query.next()) { tempRecord.id = sql_query.value("id").toInt(); tempRecord.path = sql_query.value("path").toString(); tempRecord.recordType = RECORD_TYPE(sql_query.value("type").toInt()); tempRecord.channelType = CHANNEL_TYPE(sql_query.value("channel").toInt()); tempRecord.resX = sql_query.value("resX").toInt(); tempRecord.resY = sql_query.value("resY").toInt(); tempRecord.speed = sql_query.value("speed").toDouble(); tempRecord.hv = sql_query.value("hv").toInt(); tempRecord.multiple = sql_query.value("multiple").toInt(); tempRecord.hfw = sql_query.value("hfw").toString(); tempRecord.workDistance = sql_query.value("workDistance").toDouble(); tempRecord.newFlag = sql_query.value("newFlag").toBool(); tempRecord.collected = sql_query.value("collected").toBool(); tempRecord.time = sql_query.value("time").toLongLong(); tempRecord.photo = sql_query.value("photo").toByteArray(); tempRecord.userid = sql_query.value("userid").toInt(); returnRecords << tempRecord; } } return returnRecords; } // 统计记录个数 int SqlDB::getRecordsCount() { if (!m_database.open()) { return 0; } int count = 0; QSqlQuery sql_query(m_database); if (!sql_query.exec(SELECT_RECORDS_COUNT)) { qDebug() << "Error: Fail to get count." << sql_query.lastError(); QLOG_ERROR(QString("Error: Fail to get data: %1").arg(sql_query.lastError().driverText())); } else { sql_query.next(); count = sql_query.value(0).toInt(); } return count; }
