阅读量:3
配置类相关的表,所以我使用sqlite,且QT自带该组件;
1.安装 sqlite-tools-win-x64-3460000、SQLiteExpert5.4.31.575
使用SQLiteExpert建好数据库.db文件,和对应的表后把db文件放在指定目录 ./db/program.db;
2.选择sql组件
3.新增数据库处理类,在使用数据库的地方调用类成员函数即可
DataModel::DataModel() { db = QSqlDatabase::addDatabase("QSQLITE", ""); db.setDatabaseName("./db/program.db"); connectDataBase(); } DataModel::~DataModel() { disconnectDataBase(); } // 打开数据库文件 bool DataModel::connectDataBase() { bool ret = db.open(); if (!ret) { outPutMsg(QtDebugMsg, "DataModel::connectDataBase error = " + db.lastError().text()); } return ret; } // 关闭数据库文件 void DataModel::disconnectDataBase() { db.close(); } QStringList DataModel::queryProgramList() { QStringList programList; // 根据名字查询 QString sql = QString("select sectionBarName,programNo from program where 1=1;"); outPutMsg(QtDebugMsg, "DataModel::queryProgramList sql = " + sql); // 创建一个可以对db执行语句的对象 QSqlQuery query(db); // 执行sql语句 bool ret = query.exec(sql); if (!ret) { outPutMsg(QtDebugMsg, "DataModel::queryProgramNo error = " + query.lastError().text()); return programList; } // 行坐标向下移 while (query.next()) { //获取数据库query所指的那行的数据 QString program; program += (query.value(0).toString() + ";"); // sectionBarName program += QString::number(query.value(1).toInt()); // programNo // outPutMsg(QtDebugMsg, "DataModel::queryProgramInfoBySectionCode programInfo = " + program); programList.append(program); } return programList; } // 根据型材代号查询程序号码 QString DataModel::queryProgramNo(QString sectionBarNo) { // 根据名字查询 QString sql = QString("select programNo from program where sectionBarName=\'%1\';").arg(sectionBarNo); //QString sql = QString("select programNo from program where 1=1"); outPutMsg(QtDebugMsg, "DataModel::queryProgramNo sql = " + sql); // 创建一个可以对db执行语句的对象 QSqlQuery query(db); // 执行sql语句 bool ret = query.exec(sql); if (!ret) { outPutMsg(QtDebugMsg, "DataModel::queryProgramNo error = " + query.lastError().text()); return ""; } QString programNo = ""; // 行坐标向下移 while (query.next()) { //获取数据库query所指的那行的数据 programNo = QString::number(query.value(0).toInt()); outPutMsg(QtDebugMsg, "DataModel::queryProgramNo programNo = " + programNo); } return programNo; } // 根据程序号码查询 QString DataModel::queryProgramByNo(QString programNo) { // 根据名字查询 QString sql = QString("select sectionBarName from program where programNo=\'%1\';").arg(programNo); //QString sql = QString("select programNo from program where 1=1"); outPutMsg(QtDebugMsg, "DataModel::queryProgramByNo sql = " + sql); // 创建一个可以对db执行语句的对象 QSqlQuery query(db); // 执行sql语句 bool ret = query.exec(sql); if (!ret) { outPutMsg(QtDebugMsg, "DataModel::queryProgramByNo error = " + query.lastError().text()); return ""; } QString sectionBarNo = ""; // 行坐标向下移 while (query.next()) { //获取数据库query所指的那行的数据 sectionBarNo = QString::number(query.value(0).toInt()); outPutMsg(QtDebugMsg, "DataModel::queryProgramByNo sectionBarNo = " + sectionBarNo); } return sectionBarNo; } // 根据型材代号查询程序号码 QStringList DataModel::queryProgramInfoBySectionCode(QString sectionBarNo) { QStringList programInfoList; // 根据名字查询 // 使用索引sectionBarName QString sql = QString("select stepNo, cutNo,x, y, f, Rx, Ry from programInfo where programNo = (select programNo from program where sectionBarName=\'%1\');").arg(sectionBarNo); //QString sql = QString("select programNo from program where 1=1"); outPutMsg(QtDebugMsg, "DataModel::queryProgramInfoBySectionCode sql = " + sql); // 创建一个可以对db执行语句的对象 QSqlQuery query(db); // 执行sql语句 bool ret = query.exec(sql); if (!ret) { outPutMsg(QtDebugMsg, "DataModel::queryProgramInfoBySectionCode error = " + query.lastError().text()); return programInfoList; } // 行坐标向下移 while (query.next()) { //获取数据库query所指的那行的数据 QString programInfo; programInfo += (QString::number(query.value(0).toInt())+";"); // stepNo programInfo += (QString::number(query.value(1).toInt())+";"); // cutNo programInfo += (QString::number(query.value(2).toDouble(), 'f', 2) + ";"); // x programInfo += (QString::number(query.value(3).toDouble(), 'f', 2) + ";"); // y programInfo += (QString::number(query.value(4).toDouble(), 'f', 2) + ";"); // f 速度 programInfo += (QString::number(query.value(5).toDouble(), 'f', 2) + ";"); // Rx programInfo += (QString::number(query.value(6).toDouble(), 'f', 2)); // Ry outPutMsg(QtDebugMsg, "DataModel::queryProgramInfoBySectionCode programInfo = " + programInfo); programInfoList.append(programInfo); } return programInfoList; } // 更新程序信息 bool DataModel::updateProInfosByProNo(QString proNo, QStringList proInfoList) { // 开启事务 if (!db.transaction()) { outPutMsg(QtDebugMsg, "DataModel::updateProInfosByProNo db.error = " + db.lastError().text()); return false; } QStringList programInfoList; // 根据名字查询 // 使用索引sectionBarName QString sqlDel = QString("delete from programInfo where programNo=\'%1\';").arg(proNo); outPutMsg(QtDebugMsg, "DataModel::updateProInfosByProNo sqlDel = " + sqlDel); // 创建一个可以对db执行语句的对象 QSqlQuery query(db); // 执行sql语句 bool ret = query.exec(sqlDel); if (!ret) { outPutMsg(QtDebugMsg, "DataModel::updateProInfosByProNo error = " + query.lastError().text()); return false; } try { for (int i = 0; i < proInfoList.size(); i++) { QStringList programInfo = proInfoList.at(i).split(";"); QString sqlOne = "insert into programInfo values ( " + proNo + ","; for (int j = 0; j < programInfo.size(); j++) { if (j > 0) { sqlOne += ","; } sqlOne += programInfo.at(j) ; } sqlOne += ");"; outPutMsg(QtDebugMsg, "DataModel::updateProInfosByProNo sqlOne = " + sqlOne); if (!query.exec(sqlOne)) { outPutMsg(QtDebugMsg, "DataModel::updateProInfosByProNo error = " + query.lastError().text()); return false; } } } catch (...) { outPutMsg(QtDebugMsg, "DataModel::updateProInfosByProNo error = " + query.lastError().text()); return false; } // 开启事务 if (!db.commit()) { outPutMsg(QtDebugMsg, "DataModel::updateProInfosByProNo db.error = " + db.lastError().text()); return false; } return true; }
// 保存程序信息 void ClearCorner::on_edit_saveBtn_clicked() { QStringList proInfoList; for (int i = 0; i < modelProgram.rowCount(); i++) { QString strTmp; for (int j = 0; j < modelProgram.columnCount(); j++) { QStandardItem* item = modelProgram.item(i, j); if (j > 0) { strTmp += ";"; } strTmp += item->text(); } outPutMsg(QtDebugMsg, "ClearCorner::on_edit_saveBtn_clicked strTmp = " + strTmp); proInfoList.append(strTmp); } DataModel dataModel; bool ret = dataModel.updateProInfosByProNo(ui.edit_lineEdit_programNo->text(), proInfoList); if (ret) { QMessageBox::information(nullptr, "提示", "更新程序成功!"); } else { QMessageBox::warning(nullptr, "提示", "更新程序失败!"); } }