配置类相关的表，所以我使用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, "提示", "更新程序失败！");
}
}