Chinaunix首页 | 论坛 | 博客
  • 博客访问: 164073
  • 博文数量: 90
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 845
  • 用 户 组: 普通用户
  • 注册时间: 2013-06-29 09:58
个人简介

兴趣是坚持一件事永不衰竭的动力

文章分类

全部博文(90)

文章存档

2019年(12)

2018年(9)

2016年(23)

2015年(43)

2013年(3)

我的朋友

分类: C/C++

2018-12-29 13:59:10

SqlHelp.h

点击(此处)折叠或打开

  1. #ifndef SQLHELP_H
  2. #define SQLHELP_H

  3. #include <QString>
  4. #include <QSqlDatabase>
  5. #include <QSqlQuery>
  6. #include <QVector>
  7. class SqlHelp
  8. {
  9. public:
  10.     SqlHelp();
  11.     ~SqlHelp();
  12.     void createConnection(QString dbFile);
  13.     void closeConnection();
  14.     void createDb(QString dbFile);
  15.     void deleteDb();
  16.     void createTable(QString tableName,QVector<QString> fdNameTypePairs);//fdNameTypePairs 字段名称,类型对列表
  17.     void deleteTable(QString tableName);
  18.     void addRecord(QString tableName,QVector<QString> fdNameValuePairs);
  19.     void delRecord(QString tableName,QString condition);
  20.     void setRecord(QString tableName,QVector<QString> fdNameValuePairs,QString condition);
  21.     void showTableUI(QString tableName);
  22.     QVector<QString> getFirstRecord(QString tableName,QVector<QString> fdNames,QString condition);
  23.     QVector<QString> getAllTableName();
  24.     void execSql(const QString sqlStr);
  25.     QString getLastSqlString() const;
  26.     QString getLastErrorText() const;
  27.     QString vectorToString(const QVector<QString> &list, QString split) const;
  28.     void testCase();

  29. private:
  30.     QSqlDatabase connection;
  31.     QString lastSqlString;
  32.     QString lastErrorText;

  33. };

  34. #endif // SQLHELP_H

SqlHelp.cpp

点击(此处)折叠或打开

  1. #include "SqlHelp.h"
  2. #include <QMessageBox>
  3. #include "ui/MessageDialog.h"
  4. #include <QSqlError>
  5. #include <QSqlTableModel>
  6. #include <QTableView>
  7. #include "debug.h"
  8. /**
  9.  * @brief SqlHelp::SqlHelp
  10.  *
  11.  * QT SQLite 辅助库
  12.  * Authour:dikui
  13.  * 测试方式:main.cpp 中添加两行
  14.     SqlHelp h;
  15.     h.testCase();
  16.     其中MessageDialog为自定义的对话框
  17.     debug.h仅包含了TRACE_MSG定义,用于调试打印输出,实际是封装了qDebug()而已
  18.     testCase();执行完后会产生一个数据库test.db ,里面包含了10条数据

  19.  */
  20. SqlHelp::SqlHelp()
  21. {

  22. }

  23. SqlHelp::~SqlHelp()
  24. {
  25.     closeConnection();
  26. }

  27. void SqlHelp::createConnection(QString dbFile)
  28. {
  29.     connection = QSqlDatabase::addDatabase("QSQLITE");
  30.     connection.setDatabaseName(dbFile);
  31.     if (!connection.open()) {
  32.         MessageDialog msgBox;
  33.         msgBox.setTitle("ERROR");
  34.         msgBox.setMessage("Cannot open database :"+dbFile );
  35.     }
  36. }

  37. void SqlHelp::closeConnection()
  38. {
  39.     if(connection.isOpen()){
  40.         connection.close();
  41.     }
  42. }

  43. void SqlHelp::createDb(QString dbFile)
  44. {
  45.     Q_ASSERT(dbFile.isEmpty() == false);
  46.     connection = QSqlDatabase::addDatabase("QSQLITE");
  47.     connection.setDatabaseName(dbFile);
  48.     if (!connection.open()) {
  49.         MessageDialog msgBox;
  50.         msgBox.setTitle("ERROR");
  51.         msgBox.setMessage("Cannot open database :"+ dbFile);
  52.     }
  53. }

  54. void SqlHelp::deleteDb()
  55. {

  56. }

  57. QVector<QString> SqlHelp::getAllTableName()
  58. {
  59.     /*
  60.     SQLite数据库中一个特殊的表叫 sqlite_master,sqlite_master的结构。
  61.     CREATE TABLE sqlite_master (
  62.     type TEXT,
  63.     name TEXT,
  64.     tbl_name TEXT,
  65.     rootpage INTEGER,
  66.     sql TEXT
  67.     );
  68.     我们可以通过查询这个表来获取数据库所有的表名:
  69.     SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;
  70.     */
  71.     QVector<QString> result;
  72.     QString sqlStr = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name";
  73.     QSqlQuery query(connection);
  74.     if(!query.exec(sqlStr)){
  75.         MessageDialog msgBox;
  76.         msgBox.setTitle("ERROR");
  77.         msgBox.setYesVisible(false);
  78.         lastSqlString = sqlStr;
  79.         lastErrorText = query.lastError().text();
  80.         msgBox.setMessage("SQL string :" + lastSqlString +"\n"+ "Error text:"+lastErrorText);
  81.         msgBox.exec();
  82.     }
  83.     //遍历表名

  84.     while ( query.next() ) {
  85.         result.append(query.value(0).toString());
  86.     }
  87.     return result;

  88. }
  89. /**
  90.  * @brief SqlHelp::createTable 创建表
  91.  * @param tableName 表名称
  92.  * @param fdNameTypePairs 字段名,字段值类型对。比如表字段为 id,name,类型分别为int primary key,varchar
  93.  * 那么 fdNameTypePairs 数组的值依次设置为"id","int primary key","name","varchar"
  94.  */
  95. void SqlHelp::createTable(QString tableName, QVector<QString> fdNameTypePairs)
  96. {

  97.     /**
  98.     QVector<QString> fdDeclareList;
  99.     fdDeclareList<<"id"<<"int primary key";
  100.     fdDeclareList<<"name"<<"varchar";
  101.     fdDeclareList<<"color"<<"varchar";
  102.     fdDeclareList<<"picture"<<"varchar";
  103.     fdDeclareList<<"barcode"<<"int";
  104.     fdDeclareList<<"stage"<<"varchar";
  105.     h.createTable("recipe",fdDeclareList);
  106.     */
  107.     //query1.exec("create table student (id int primary key, name varchar(20))");

  108.     //检查表是否已存在
  109.     QVector<QString> existTables = getAllTableName();
  110.     if(existTables.indexOf(tableName) >= 0){
  111.         return;
  112.     }

  113.     QString createStr = "create table " + tableName;
  114.     QString nameTypeStr ;
  115.     if( fdNameTypePairs.count() % 2 ){
  116.         MessageDialog msgBox;
  117.         msgBox.setTitle("Error");
  118.         msgBox.setMessage("fdNameTypePairs count error" );
  119.         msgBox.exec();
  120.     }
  121.     for (int i = 0; i < fdNameTypePairs.count(); i+=2) {
  122.         nameTypeStr += fdNameTypePairs.at(i) + " " + fdNameTypePairs.at(i+1) + ",";
  123.     }
  124.     nameTypeStr = nameTypeStr.mid(0,nameTypeStr.length() -1);//remove last ","
  125.     QString sqlStr = createStr + " (" + nameTypeStr +")";

  126.     execSql(sqlStr);
  127. }
  128. /**
  129.  * @brief SqlHelp::deleteTable 删除表
  130.  * @param tableName
  131.  */
  132. void SqlHelp::deleteTable(QString tableName)
  133. {
  134.     QString sqlStr = "drop table " + tableName;

  135.     execSql( sqlStr) ;

  136. }
  137. /**
  138.  * @brief SqlHelp::addRecord 向表中添加一条记录
  139.  * @param tableName 操作的表名
  140.  * @param fdNameValuePairs 字段名,字段值对。比如添加项为 id=1 name="zhangshan"
  141.  * 那么fdNameValuePairs数组的值依次设置为"id","1","name","'zhangshan'",注意字符串类型的值要加上'
  142.  */
  143. void SqlHelp::addRecord(QString tableName, QVector<QString> fdNameValuePairs)
  144. {
  145.     //INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
  146.     //QString("INSERT INTO %1 ( %2 ) VALUES ( %3 )").arg(tableName).arg(nameStr).arg(valueStr);

  147.     if( fdNameValuePairs.count() % 2 ){
  148.         MessageDialog msgBox;
  149.         msgBox.setTitle("Error");
  150.         msgBox.setMessage("fdNameValuePairs count error" );
  151.         msgBox.exec();
  152.         return;
  153.     }
  154.     QVector<QString> names;
  155.     QVector<QString> values;
  156.     for (int i = 0; i < fdNameValuePairs.count(); i+=2) {
  157.         names.append(fdNameValuePairs.at(i));
  158.         values.append(fdNameValuePairs.at(i+1));
  159.     }

  160.     QString nameStr;
  161.     QString valueStr;
  162.     //拼接 name
  163.     for (int i = 0; i < names.count(); ++i) {
  164.         nameStr += names.at(i) + ",";
  165.     }
  166.     nameStr = nameStr.mid(0,nameStr.length()-1);//remove last ","
  167.     //拼接 value
  168.     for (int i = 0; i < values.count(); ++i) {
  169.         valueStr += values.at(i) + ",";
  170.     }
  171.     valueStr = valueStr.mid(0,valueStr.length()-1);//remove last ","

  172.     //sqlStr = actionStr + " (" + nameStr +") VALUES (" + valueStr +")";
  173.     QString sqlStr = QString("INSERT INTO %1 ( %2 ) VALUES ( %3 )").arg(tableName).arg(nameStr).arg(valueStr);

  174.     execSql( sqlStr) ;


  175. }


  176. void SqlHelp::delRecord(QString tableName,QString condition)
  177. {
  178.     //DELETE FROM 表名称 WHERE 列名称 =
  179.     QString sqlStr;
  180.     if(condition.isEmpty()){
  181.         sqlStr = QString("DELETE FROM %1").arg(tableName);
  182.     }else{
  183.         sqlStr = QString("DELETE FROM %1 WHERE %2").arg(tableName).arg(condition);
  184.     }
  185.     execSql(sqlStr);

  186. }

  187. void SqlHelp::setRecord(QString tableName, QVector<QString> fdNameValuePairs, QString condition)
  188. {
  189.     //参数说明
  190.     //condition为空时,应用到所有列
  191.     //返回值


  192.     //更新某一行中的若干列
  193.     //UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing' WHERE LastName = 'Wilson'
  194.     //QString("UPDATE %1 SET %2 WHERE %3").arg(tableName).arg(nameValueStr).arg(condition);
  195.     QString nameValueStr;
  196.     if( fdNameValuePairs.count() % 2 ){
  197.         MessageDialog msgBox;
  198.         msgBox.setTitle("Error");
  199.         msgBox.setMessage("fdNameValuePairs count error" );
  200.         msgBox.exec();
  201.         return;
  202.     }
  203.     QVector<QString> names;
  204.     QVector<QString> values;
  205.     for (int i = 0; i < fdNameValuePairs.count(); i+=2) {
  206.         names.append(fdNameValuePairs.at(i));
  207.         values.append(fdNameValuePairs.at(i+1));
  208.     }
  209.     //拼接 name = value
  210.     for (int i = 0; i < names.count(); ++i) {
  211.         nameValueStr += names.at(i) + " = " + values.at(i) + ",";
  212.     }
  213.     nameValueStr = nameValueStr.mid(0,nameValueStr.length()-1);//remove last ","

  214.     QString sqlStr ;
  215.     if(condition.isEmpty()){
  216.         sqlStr = QString("UPDATE %1 SET %2 ").arg(tableName).arg(nameValueStr);
  217.     }else{
  218.         sqlStr = QString("UPDATE %1 SET %2 WHERE %3").arg(tableName).arg(nameValueStr).arg(condition);
  219.     }
  220.     execSql( sqlStr) ;

  221. }

  222. QVector<QString> SqlHelp::getFirstRecord(QString tableName, QVector<QString> fdNames,QString condition)
  223. {
  224.     //condition为空时,应用到所有列
  225.     //SELECT LastName,FirstName FROM Persons
  226.     QVector<QString> fdValues;
  227.     QString nameStr = vectorToString(fdNames, ",");
  228.     QString sqlStr;
  229.     if(condition.isEmpty()){
  230.         sqlStr = QString("SELECT %1 FROM %2 ").arg(nameStr).arg(tableName);
  231.     }else{
  232.         sqlStr = QString("SELECT %1 FROM %2 WHERE %3").arg(nameStr).arg(tableName).arg(condition);
  233.     }
  234.     QSqlQuery query(connection);

  235.     if(!query.exec(sqlStr)){
  236.         MessageDialog msgBox;
  237.         msgBox.setTitle("ERROR");
  238.         msgBox.setYesVisible(false);
  239.         lastSqlString = sqlStr;
  240.         lastErrorText = query.lastError().text();
  241.         msgBox.setMessage("SQL string :" + lastSqlString +"\n"+ "Error text:"+lastErrorText);
  242.         msgBox.exec();
  243.     }
  244.     if( query.first() ){
  245.         for (int i = 0; i < fdNames.count(); ++i) {
  246.             fdValues.append(query.value(i).toString());
  247.         }
  248.         return fdValues;
  249.     }
  250.     return fdValues;
  251. }



  252. void SqlHelp::showTableUI(QString tableName)
  253. {
  254.    QSqlTableModel *model = new QSqlTableModel();
  255.    model->setTable(tableName);
  256.    model->select();
  257.     // 设置编辑策略
  258.    model->setEditStrategy(QSqlTableModel::OnManualSubmit);
  259.    QTableView *tableView = new QTableView();
  260.    tableView->setModel(model);
  261.    MessageDialog msgBox;
  262.    msgBox.setTitle(tableName);
  263.    msgBox.setWorkUI(tableView);
  264.    msgBox.exec();
  265.    delete tableView;
  266.    delete model;

  267. }



  268. void SqlHelp::execSql(const QString sqlStr)
  269. {
  270.     if(!connection.isOpen()){
  271.         MessageDialog msgBox;
  272.         msgBox.setTitle("ERROR");
  273.         msgBox.setMessage("connection is not open");
  274.         msgBox.exec();
  275.         return;
  276.     }
  277.     QSqlQuery query(connection);

  278.     if(!query.exec(sqlStr)){
  279.         MessageDialog msgBox;
  280.         msgBox.setTitle("ERROR");
  281.         msgBox.setYesVisible(false);
  282.         lastSqlString = sqlStr;
  283.         lastErrorText = query.lastError().text();
  284.         msgBox.setMessage("SQL string :" + lastSqlString +"\n"+ "Error text:"+lastErrorText);
  285.         msgBox.exec();
  286.     }

  287. }

  288. QString SqlHelp::getLastSqlString() const
  289. {
  290.     return lastSqlString;
  291. }

  292. QString SqlHelp::getLastErrorText() const
  293. {
  294.     return lastErrorText;
  295. }

  296. QString SqlHelp::vectorToString(const QVector<QString> &list, QString split) const
  297. {

  298.     QString result;//转化成val1,val2,val3
  299.     for (int i = 0; i < list.count(); ++i) {
  300.         result += list.at(i) + split;
  301.     }
  302.     int index = result.lastIndexOf(split);
  303.     result.remove(index,split.length());
  304.     return result;

  305. }

  306. void SqlHelp::testCase()
  307. {
  308.     QString tableName= "recipe";
  309.     createDb( "test.db");
  310.     //h.deleteTable("recipe");
  311.     QVector<QString> fdDeclareList;
  312.     fdDeclareList<<"id"<<"INTEGER PRIMARY KEY autoincrement";
  313.     fdDeclareList<<"name"<<"varchar";
  314.     fdDeclareList<<"color"<<"varchar";
  315.     fdDeclareList<<"picture"<<"varchar";
  316.     fdDeclareList<<"barcode"<<"int";
  317.     fdDeclareList<<"stage"<<"varchar";
  318.     createTable(tableName,fdDeclareList);

  319.     for (int i = 0; i < 10; ++i) {
  320.         QVector<QString> fdDeclareList;
  321.         //fdDeclareList<<"id"<<QString::number(i);
  322.         fdDeclareList<<"name"<<QString("'name%1'").arg(i);
  323.         fdDeclareList<<"color"<<QString("'ff000%1'").arg(i);
  324.         fdDeclareList<<"picture"<<QString("'picture%1'").arg(i);
  325.         fdDeclareList<<"barcode"<<QString("20181219000000%1").arg(i);
  326.         fdDeclareList<<"stage"<<QString("'stage%1'").arg(i);
  327.         addRecord("recipe",fdDeclareList);
  328.         TRACE_MSG(i);
  329.     }
  330.     //h.delRecord(tableName,"1=1");
  331.     QVector<QString> fdNameValuePair2 ;
  332.     fdNameValuePair2<<"barcode"<<"1";

  333.     setRecord(tableName,fdNameValuePair2,"1=1");
  334.     showTableUI(tableName);

  335.     QVector<QString> fdNames ;
  336.     fdNames<<"id"<<"name";
  337.     TRACE_MSG( getFirstRecord(tableName,fdNames,""));

  338. }


阅读(16015) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册