根据需要数据库的内容,封装增删改查的sql函数
说明:数据库存储的内容可以看做为二维数组,使用QMAP方法进行存储;也可以看做是将数据库表的字段封装成类,使用QLIST进行操作
代码实现功能说明
数据库设计表名:user_task
任务节点:
task_node.h
#ifndef TASK_NODE_H #define TASK_NODE_H #include <QString> class task_node { public: task_node(); int task_id; //任务id QString task_type;//任务类型 QString g_time;//任务生成时间点,系统时间 QString s_time;//任务开始时间 QString e_time;//任务结束时间点 QString current_state;//当前状态 QString destination_node1;//目标节点1 QString destination_node2;//目标节点2 }; #endif // TASK_NODE_H
node.cpp
#include "task_node.h" task_node::task_node() { }
数据库连接、封装的增删改查sql
db_manager.h
#ifndef DB_MANAGER_H #define DB_MANAGER_H # pragma execution_character_set("utf-8") #include "QtSql/QSqlDatabase" #include"QMessageBox" #include"QtSql/QSqlError" #include"QSqlQuery" #include<QDebug> #include<QList> #include<db_map_node.h> #include "task_node.h" class db_manager { public: db_manager(); bool db_connect(QString,QString,QString,QString); //////task///// /*任务的链表形式*/ QList<task_node> *task_search; /*查询*/ QList<task_node> task_search_id(QString); /*更新*/ bool task_update(QString,QString,QString,QString); /*增加*/ bool task_insert(QString,QString,QString,QString,QString,QString,QString,QString); /*删除*/ bool task_delete(QString); public: QSqlDatabase db; }; #endif // DB_MANAGER_H
db_manager.cpp
#include "db_manager.h" # pragma execution_character_set("utf-8") db_manager::db_manager() { db = QSqlDatabase::addDatabase("QMYSQL"); } bool db_manager::db_connect(QString host,QString user,QString Password,QString Database) { db.setHostName(host); db.setUserName(user); db.setPassword(Password); db.setDatabaseName(Database); if(!db.open()) { return false; } else { qDebug()<<"数据库连接成功!"; return true; } } /*任务查询 *SELECT 列名称 FROM 表名称; *SELECT*FROM 表名称 WHERE 列名称='某值'; *SELECT*FROM user_task WHERE task_id=''; *若task_id_temp==""时;表示SELECT*FROM user_task 整个表的内容 */ QList<task_node> db_manager:: task_search_id(QString task_id_temp){ QList<task_node> list; QString selectContent; if(task_id_temp==""){ selectContent = QString("select * from user_task"); }else { selectContent = QString("select * from user_task where task_id='%1'").arg(task_id_temp); } task_node task_nodes; QSqlQuery sql_query(db); //查询所有数据 sql_query.exec(selectContent); if(!sql_query.exec()) { qDebug()<<sql_query.lastError(); } else { while(sql_query.next()) { int task_id = sql_query.value(0).toInt(); QString task_type = sql_query.value(1).toString(); QString g_time = sql_query.value(2).toString(); QString s_time = sql_query.value(3).toString(); QString e_time = sql_query.value(4).toString(); QString current_state = sql_query.value(5).toString(); QString destination_node1 = sql_query.value(6).toString(); QString destination_node2 = sql_query.value(7).toString(); task_nodes.task_id=task_id; task_nodes.task_type=task_type; task_nodes.g_time=g_time; task_nodes.s_time=s_time; task_nodes.e_time=e_time; task_nodes.current_state=current_state; task_nodes.destination_node1=destination_node1; task_nodes.destination_node2=destination_node2; // qDebug()<<QString("task_id:%1 task_type:%2 ").arg(task_id).arg(task_type); // qDebug()<<QString("g_time:%3 s_time:%4 e_time:%5").arg(g_time).arg(s_time).arg(e_time); // qDebug()<<QString("current_state:%6 destination_node1:%7 destination_node2:%8").arg(current_state).arg(destination_node1).arg(destination_node2); // qDebug()<<"查询结束"; // qDebug()<<"task_id"<<task_id<<"task_type"<<task_type<< // "g_time"<<g_time<<"s_time"<<s_time<<"e_time"<<e_time<< // "current_state"<<current_state<<"destination_node1"<<destination_node1<<"destination_node2"<<destination_node2; list<<task_nodes; } } // qDebug()<<"qqqqq"<<list.size(); // qDebug()<<"11111"<<list.at(0).task_id; return list; } /*任务更新 UPDATE user_task SET s_time='某值', e_time='某值',current_state WHERE task_id='某值'; */ bool db_manager::task_update(QString task_id_temp,QString s_time_temp,QString e_time_temp,QString current_state_temp){ QString updateContent; if(s_time_temp==""){ updateContent=QString("update user_task set e_time='%1',current_state='%2' where task_id='%3'").arg(e_time_temp).arg(current_state_temp).arg(task_id_temp); }else { updateContent=QString("update user_task set s_time='%1',current_state='%2' where task_id='%3'").arg(s_time_temp).arg(current_state_temp).arg(task_id_temp); } QSqlQuery sql_query(db); //查询所有数据 if(!sql_query.exec(updateContent)){ qDebug()<<"update false"; return false; } qDebug()<<"update true"; return true; } /*任务增加 INSERT INTO user_task VALUES (value1,value2,value3,value4,value5,value6,value7); */ bool db_manager::task_insert(QString task_id_temp,QString task_type_temp,QString g_time_temp,QString s_time_temp,QString e_time_temp,QString current_state_temp,QString destination_node1_temp,QString destination_node2_temp){ QString insertContent; QString str1; QString str2; str1 = QString("insert into user_task values ("); //insertContent=QString("insert into user_task values ("); //insertContent.append("'%1','%2','%3','%4'.'%5','%6','%7','%8')").arg(task_id_temp).arg(task_type_temp).arg(g_time_temp).arg(s_time_temp).arg(e_time_temp).arg(current_state_temp).arg(destination_node1_temp).arg(destination_node2_temp); str2=QString("'%1','%2','%3','%4','%5','%6','%7','%8')").arg(task_id_temp).arg(task_type_temp).arg(g_time_temp).arg(s_time_temp).arg(e_time_temp).arg(current_state_temp).arg(destination_node1_temp).arg(destination_node2_temp); insertContent=str1.append(str2); qDebug()<<insertContent; QSqlQuery sql_query(db); //查询所有数据 if(!sql_query.exec(insertContent)){ qDebug()<<"insert false"; return false; } qDebug()<<"insert true"; return true; } /*任务删除 DELETE FROM user_task WHERE task_id='某值'; */ bool db_manager::task_delete(QString task_id_temp){ QString deleteContent=QString("delete from user_task where task_id='%1'").arg(task_id_temp); QSqlQuery sql_query(db); //查询所有数据 if(!sql_query.exec(deleteContent)){ qDebug()<<"delete false"; return false; } qDebug()<<"delete true"; return true; }
mainwindow.cpp调用
db_manager *a=new db_manager(); /*任务调用数据库的方法示例*/ a->db_connect("127.0.0.1","root","123456","user"); a->task_search_id("1"); a->task_update("1","10:00","","1"); a->task_update("1","","10:50","2"); a->task_insert("6","充电","2021-07-29","","","1","2","3"); a->task_delete("1");