QT SQLite 辅助库

栏目: 编程工具 · 发布时间: 7年前

内容简介：SqlHelp.hSqlHelp.cpp

SqlHelp.h

点击( 此处 )折叠或打开

  1. #ifndef SQLHELP_H
  2. #define SQLHELP_H
  4. #include < QString >
  5. #include < QSqlDatabase >
  6. #include < QSqlQuery >
  7. #include < QVector >
  8. class SqlHelp
  9. {
  10. public :
  11.     SqlHelp ( ) ;
  12.      ~ SqlHelp ( ) ;
  13.     void createConnection ( QString dbFile ) ;
  14.     void closeConnection ( ) ;
  15.     void createDb ( QString dbFile ) ;
  16.     void deleteDb ( ) ;
  17.     void createTable ( QString tableName , QVector < QString > fdNameTypePairs ) ; / / fdNameTypePairs 字段名称，类型对列表
  18.     void deleteTable ( QString tableName ) ;
  19.     void addRecord ( QString tableName , QVector < QString > fdNameValuePairs ) ;
  20.     void delRecord ( QString tableName , QString condition ) ;
  21.     void setRecord ( QString tableName , QVector < QString > fdNameValuePairs , QString condition ) ;
  22.     void showTableUI ( QString tableName ) ;
  23.     QVector < QString > getFirstRecord ( QString tableName , QVector < QString > fdNames , QString condition ) ;
  24.     QVector < QString > getAllTableName ( ) ;
  25.     void execSql ( const QString sqlStr ) ;
  26.     QString getLastSqlString ( ) const ;
  27.     QString getLastErrorText ( ) const ;
  28.     QString vectorToString ( const QVector < QString > & list , QString split ) const ;
  29.     void testCase ( ) ;
  31. private :
  32.     QSqlDatabase connection ;
  33.     QString lastSqlString ;
  34.     QString lastErrorText ;
  36. } ;
  38. #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条数据
  20.   * /
  21. SqlHelp : : SqlHelp ( )
  22. {
  24. }
  26. SqlHelp : : ~ SqlHelp ( )
  27. {
  28.     closeConnection ( ) ;
  29. }
  31. void SqlHelp : : createConnection ( QString dbFile )
  32. {
  33.     connection = QSqlDatabase : : addDatabase ( "QSQLITE" ) ;
  34.     connection . setDatabaseName ( dbFile ) ;
  35.      if ( ! connection . open ( ) ) {
  36.         MessageDialog msgBox ;
  37.          msgBox . setTitle ( "ERROR" ) ;
  38.          msgBox . setMessage ( "Cannot open database :" + dbFile ) ;
  39.      }
  40. }
  42. void SqlHelp : : closeConnection ( )
  43. {
  44.      if ( connection . isOpen ( ) ) {
  45.         connection . close ( ) ;
  46.      }
  47. }
  49. void SqlHelp : : createDb ( QString dbFile )
  50. {
  51.     Q_ASSERT ( dbFile . isEmpty ( ) = = false ) ;
  52.     connection = QSqlDatabase : : addDatabase ( "QSQLITE" ) ;
  53.     connection . setDatabaseName ( dbFile ) ;
  54.      if ( ! connection . open ( ) ) {
  55.         MessageDialog msgBox ;
  56.          msgBox . setTitle ( "ERROR" ) ;
  57.          msgBox . setMessage ( "Cannot open database :" + dbFile ) ;
  58.      }
  59. }
  61. void SqlHelp : : deleteDb ( )
  62. {
  64. }
  66. QVector < QString > SqlHelp : : getAllTableName ( )
  67. {
  68.      / *
  69.     SQLite数据库中一个特殊的表叫 sqlite_master，sqlite_master的结构。
  70.     CREATE TABLE sqlite_master (
  71.     type TEXT ,
  72.     name TEXT ,
  73.     tbl_name TEXT ,
  74.     rootpage INTEGER ,
  75.     sql TEXT
  76.      ) ;
  77.     我们可以通过查询这个表来获取数据库所有的表名：
  78.      SELECT name FROM sqlite_master WHERE type = ' table ' ORDER BY name ;
  79.      * /
  80.     QVector < QString > result ;
  81.     QString sqlStr = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name" ;
  82.     QSqlQuery query ( connection ) ;
  83.      if ( ! query . exec ( sqlStr ) ) {
  84.         MessageDialog msgBox ;
  85.          msgBox . setTitle ( "ERROR" ) ;
  86.          msgBox . setYesVisible ( false ) ;
  87.         lastSqlString = sqlStr ;
  88.         lastErrorText = query . lastError ( ) . text ( ) ;
  89.          msgBox . setMessage ( "SQL string :" + lastSqlString + "\n" + "Error text:" + lastErrorText ) ;
  90.          msgBox . exec ( ) ;
  91.      }
  92.      / / 遍历表名
  94.      while ( query . next ( ) ) {
  95.         result . append ( query . value ( 0 ) . toString ( ) ) ;
  96.      }
  97.     return result ;
  99. }
  100. / * *
  101.   * @brief SqlHelp : : createTable 创建表
  102.   * @param tableName 表名称
  103.   * @param fdNameTypePairs 字段名，字段值类型对。比如表字段为 id，name，类型分别为int primary key , varchar
  104.   * 那么 fdNameTypePairs 数组的值依次设置为 "id" , "int primary key" , "name" , "varchar"
  105.   * /
  106. void SqlHelp : : createTable ( QString tableName , QVector < QString > fdNameTypePairs )
  107. {
  109.      / * *
  110.     QVector < QString > fdDeclareList ;
  111.     fdDeclareList < < "id" < < "int primary key" ;
  112.     fdDeclareList < < "name" < < "varchar" ;
  113.     fdDeclareList < < "color" < < "varchar" ;
  114.     fdDeclareList < < "picture" < < "varchar" ;
  115.     fdDeclareList < < "barcode" < < "int" ;
  116.     fdDeclareList < < "stage" < < "varchar" ;
  117.     h . createTable ( "recipe" , fdDeclareList ) ;
  118.      * /
  119.      / / query1 . exec ( "create table student (id int primary key, name varchar(20))" ) ;
  121.      / / 检查表是否已存在
  122.     QVector < QString > existTables = getAllTableName ( ) ;
  123.      if ( existTables . indexOf ( tableName ) > = 0 ) {
  124.         return ;
  125.      }
  127.     QString createStr = "create table " + tableName ;
  128.     QString nameTypeStr ;
  129.      if ( fdNameTypePairs . count ( ) % 2 ) {
  130.         MessageDialog msgBox ;
  131.          msgBox . setTitle ( "Error" ) ;
  132.          msgBox . setMessage ( "fdNameTypePairs count error" ) ;
  133.          msgBox . exec ( ) ;
  134.      }
  135.      for ( int i = 0 ; i < fdNameTypePairs . count ( ) ; i + = 2 ) {
  136.         nameTypeStr + = fdNameTypePairs . at ( i ) + " " + fdNameTypePairs . at ( i + 1 ) + "," ;
  137.      }
  138.     nameTypeStr = nameTypeStr . mid ( 0 , nameTypeStr . length ( ) - 1 ) ; / / remove last ","
  139.     QString sqlStr = createStr + " (" + nameTypeStr + ")" ;
  141.     execSql ( sqlStr ) ;
  142. }
  143. / * *
  144.   * @brief SqlHelp : : deleteTable 删除表
  145.   * @param tableName
  146.   * /
  147. void SqlHelp : : deleteTable ( QString tableName )
  148. {
  149.     QString sqlStr = "drop table " + tableName ;
  151.     execSql ( sqlStr ) ;
  153. }
  154. / * *
  155.   * @brief SqlHelp : : addRecord 向表中添加一条记录
  156.   * @param tableName 操作的表名
  157.   * @param fdNameValuePairs 字段名，字段值对。比如添加项为 id = 1 name = "zhangshan"
  158.   * 那么fdNameValuePairs数组的值依次设置为 "id" , "1" , "name" , "'zhangshan'" , 注意字符串类型的值要加上 '
  159.   * /
  160. void SqlHelp : : addRecord ( QString tableName , QVector < QString > fdNameValuePairs )
  161. {
  162.      / / INSERT INTO table_name ( 列1 , 列2 , . . . ) VALUES ( 值1 , 值2 , . . . . )
  163.      / / QString ( "INSERT INTO %1 ( %2 ) VALUES ( %3 )" ) . arg ( tableName ) . arg ( nameStr ) . arg ( valueStr ) ;
  165.      if ( fdNameValuePairs . count ( ) % 2 ) {
  166.         MessageDialog msgBox ;
  167.          msgBox . setTitle ( "Error" ) ;
  168.          msgBox . setMessage ( "fdNameValuePairs count error" ) ;
  169.          msgBox . exec ( ) ;
  170.         return ;
  171.      }
  172.     QVector < QString > names ;
  173.     QVector < QString > values ;
  174.      for ( int i = 0 ; i < fdNameValuePairs . count ( ) ; i + = 2 ) {
  175.         names . append ( fdNameValuePairs . at ( i ) ) ;
  176.         values . append ( fdNameValuePairs . at ( i + 1 ) ) ;
  177.      }
  179.     QString nameStr ;
  180.     QString valueStr ;
  181.      / / 拼接 name
  182.      for ( int i = 0 ; i < names . count ( ) ; + + i ) {
  183.         nameStr + = names . at ( i ) + "," ;
  184.      }
  185.     nameStr = nameStr . mid ( 0 , nameStr . length ( ) - 1 ) ; / / remove last ","
  186.      / / 拼接 value
  187.      for ( int i = 0 ; i < values . count ( ) ; + + i ) {
  188.         valueStr + = values . at ( i ) + "," ;
  189.      }
  190.     valueStr = valueStr . mid ( 0 , valueStr . length ( ) - 1 ) ; / / remove last ","
  192.      / / sqlStr = actionStr + " (" + nameStr + ") VALUES (" + valueStr + ")" ;
  193.     QString sqlStr = QString ( "INSERT INTO %1 ( %2 ) VALUES ( %3 )" ) . arg ( tableName ) . arg ( nameStr ) . arg ( valueStr ) ;
  195.     execSql ( sqlStr ) ;
  198. }
  201. void SqlHelp : : delRecord ( QString tableName , QString condition )
  202. {
  203.      / / DELETE FROM 表名称 WHERE 列名称 = 值
  204.     QString sqlStr ;
  205.      if ( condition . isEmpty ( ) ) {
  206.         sqlStr = QString ( "DELETE FROM %1" ) . arg ( tableName ) ;
  207.      } else {
  208.         sqlStr = QString ( "DELETE FROM %1 WHERE %2" ) . arg ( tableName ) . arg ( condition ) ;
  209.      }
  210.     execSql ( sqlStr ) ;
  212. }
  214. void SqlHelp : : setRecord ( QString tableName , QVector < QString > fdNameValuePairs , QString condition )
  215. {
  216.      / / 参数说明
  217.      / / condition为空时，应用到所有列
  218.      / / 返回值
  221.      / / 更新某一行中的若干列
  222.      / / UPDATE Person SET Address = ' Zhongshan 23 ' , City = ' Nanjing ' WHERE LastName = ' Wilson '
  223.      / / QString ( "UPDATE %1 SET %2 WHERE %3" ) . arg ( tableName ) . arg ( nameValueStr ) . arg ( condition ) ;
  224.     QString nameValueStr ;
  225.      if ( fdNameValuePairs . count ( ) % 2 ) {
  226.         MessageDialog msgBox ;
  227.          msgBox . setTitle ( "Error" ) ;
  228.          msgBox . setMessage ( "fdNameValuePairs count error" ) ;
  229.          msgBox . exec ( ) ;
  230.         return ;
  231.      }
  232.     QVector < QString > names ;
  233.     QVector < QString > values ;
  234.      for ( int i = 0 ; i < fdNameValuePairs . count ( ) ; i + = 2 ) {
  235.         names . append ( fdNameValuePairs . at ( i ) ) ;
  236.         values . append ( fdNameValuePairs . at ( i + 1 ) ) ;
  237.      }
  238.      / / 拼接 name = value
  239.      for ( int i = 0 ; i < names . count ( ) ; + + i ) {
  240.         nameValueStr + = names . at ( i ) + " = " + values . at ( i ) + "," ;
  241.      }
  242.     nameValueStr = nameValueStr . mid ( 0 , nameValueStr . length ( ) - 1 ) ; / / remove last ","
  244.     QString sqlStr ;
  245.      if ( condition . isEmpty ( ) ) {
  246.         sqlStr = QString ( "UPDATE %1 SET %2 " ) . arg ( tableName ) . arg ( nameValueStr ) ;
  247.      } else {
  248.         sqlStr = QString ( "UPDATE %1 SET %2 WHERE %3" ) . arg ( tableName ) . arg ( nameValueStr ) . arg ( condition ) ;
  249.      }
  250.     execSql ( sqlStr ) ;
  252. }
  254. QVector < QString > SqlHelp : : getFirstRecord ( QString tableName , QVector < QString > fdNames , QString condition )
  255. {
  256.      / / condition为空时，应用到所有列
  257.      / / SELECT LastName , FirstName FROM Persons
  258.     QVector < QString > fdValues ;
  259.     QString nameStr = vectorToString ( fdNames , "," ) ;
  260.     QString sqlStr ;
  261.      if ( condition . isEmpty ( ) ) {
  262.         sqlStr = QString ( "SELECT %1 FROM %2 " ) . arg ( nameStr ) . arg ( tableName ) ;
  263.      } else {
  264.         sqlStr = QString ( "SELECT %1 FROM %2 WHERE %3" ) . arg ( nameStr ) . arg ( tableName ) . arg ( condition ) ;
  265.      }
  266.     QSqlQuery query ( connection ) ;
  268.      if ( ! query . exec ( sqlStr ) ) {
  269.         MessageDialog msgBox ;
  270.          msgBox . setTitle ( "ERROR" ) ;
  271.          msgBox . setYesVisible ( false ) ;
  272.         lastSqlString = sqlStr ;
  273.         lastErrorText = query . lastError ( ) . text ( ) ;
  274.          msgBox . setMessage ( "SQL string :" + lastSqlString + "\n" + "Error text:" + lastErrorText ) ;
  275.          msgBox . exec ( ) ;
  276.      }
  277.      if ( query . first ( ) ) {
  278.          for ( int i = 0 ; i < fdNames . count ( ) ; + + i ) {
  279.             fdValues . append ( query . value ( i ) . toString ( ) ) ;
  280.          }
  281.         return fdValues ;
  282.      }
  283.     return fdValues ;
  284. }
  288. void SqlHelp : : showTableUI ( QString tableName )
  289. {
  290.    QSqlTableModel * model = new QSqlTableModel ( ) ;
  291.    model - > setTable ( tableName ) ;
  292.    model - > select ( ) ;
  293.      / / 设置编辑策略
  294.    model - > setEditStrategy ( QSqlTableModel : : OnManualSubmit ) ;
  295.    QTableView * tableView = new QTableView ( ) ;
  296.    tableView - > setModel ( model ) ;
  297.    MessageDialog msgBox ;
  298.     msgBox . setTitle ( tableName ) ;
  299.     msgBox . setWorkUI ( tableView ) ;
  300.     msgBox . exec ( ) ;
  301.    delete tableView ;
  302.    delete model ;
  304. }
  308. void SqlHelp : : execSql ( const QString sqlStr )
  309. {
  310.      if ( ! connection . isOpen ( ) ) {
  311.         MessageDialog msgBox ;
  312.          msgBox . setTitle ( "ERROR" ) ;
  313.          msgBox . setMessage ( "connection is not open" ) ;
  314.          msgBox . exec ( ) ;
  315.         return ;
  316.      }
  317.     QSqlQuery query ( connection ) ;
  319.      if ( ! query . exec ( sqlStr ) ) {
  320.         MessageDialog msgBox ;
  321.          msgBox . setTitle ( "ERROR" ) ;
  322.          msgBox . setYesVisible ( false ) ;
  323.         lastSqlString = sqlStr ;
  324.         lastErrorText = query . lastError ( ) . text ( ) ;
  325.          msgBox . setMessage ( "SQL string :" + lastSqlString + "\n" + "Error text:" + lastErrorText ) ;
  326.          msgBox . exec ( ) ;
  327.      }
  329. }
  331. QString SqlHelp : : getLastSqlString ( ) const
  332. {
  333.     return lastSqlString ;
  334. }
  336. QString SqlHelp : : getLastErrorText ( ) const
  337. {
  338.     return lastErrorText ;
  339. }
  341. QString SqlHelp : : vectorToString ( const QVector < QString > & list , QString split ) const
  342. {
  344.     QString result ; / / 转化成val1 , val2 , val3
  345.      for ( int i = 0 ; i < list . count ( ) ; + + i ) {
  346.         result + = list . at ( i ) + split ;
  347.      }
  348.      int index = result . lastIndexOf ( split ) ;
  349.     result . remove ( index , split . length ( ) ) ;
  350.     return result ;
  352. }
  354. void SqlHelp : : testCase ( )
  355. {
  356.     QString tableName = "recipe" ;
  357.     createDb ( "test.db" ) ;
  358.      / / h . deleteTable ( "recipe" ) ;
  359.     QVector < QString > fdDeclareList ;
  360.     fdDeclareList < < "id" < < "INTEGER PRIMARY KEY autoincrement" ;
  361.     fdDeclareList < < "name" < < "varchar" ;
  362.     fdDeclareList < < "color" < < "varchar" ;
  363.     fdDeclareList < < "picture" < < "varchar" ;
  364.     fdDeclareList < < "barcode" < < "int" ;
  365.     fdDeclareList < < "stage" < < "varchar" ;
  366.     createTable ( tableName , fdDeclareList ) ;
  368.      for ( int i = 0 ; i < 10 ; + + i ) {
  369.         QVector < QString > fdDeclareList ;
  370.          / / fdDeclareList < < "id" < < QString : : number ( i ) ;
  371.         fdDeclareList < < "name" < < QString ( "'name%1'" ) . arg ( i ) ;
  372.         fdDeclareList < < "color" < < QString ( "'ff000%1'" ) . arg ( i ) ;
  373.         fdDeclareList < < "picture" < < QString ( "'picture%1'" ) . arg ( i ) ;
  374.         fdDeclareList < < "barcode" < < QString ( "20181219000000%1" ) . arg ( i ) ;
  375.         fdDeclareList < < "stage" < < QString ( "'stage%1'" ) . arg ( i ) ;
  376.         addRecord ( "recipe" , fdDeclareList ) ;
  377.         TRACE_MSG ( i ) ;
  378.      }
  379.      / / h . delRecord ( tableName , "1=1" ) ;
  380.     QVector < QString > fdNameValuePair2 ;
  381.     fdNameValuePair2 < < "barcode" < < "1" ;
  383.     setRecord ( tableName , fdNameValuePair2 , "1=1" ) ;
  384.     showTableUI ( tableName ) ;
  386.     QVector < QString > fdNames ;
  387.     fdNames < < "id" < < "name" ;
  388.     TRACE_MSG ( getFirstRecord ( tableName , fdNames , "" ) ) ;
  390. }

以上所述就是小编给大家介绍的《QT SQLite 辅助库》，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持！

查看所有标签

猜你喜欢:

本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。

码农书籍
Pattern Recognition and Machine Learning

Pattern Recognition and Machine Learning

Christopher Bishop / Springer / 2007-10-1 / USD 94.95

The dramatic growth in practical applications for machine learning over the last ten years has been accompanied by many important developments in the underlying algorithms and techniques. For example,......一起来看看 《Pattern Recognition and Machine Learning》 这本书的介绍吧!

码农工具
JSON 在线解析
JSON 在线解析

在线 JSON 格式化工具

随机密码生成器
随机密码生成器

多种字符组合密码

RGB HSV 转换
RGB HSV 转换

RGB HSV 互转工具

  • New
  • 文章
  • 话题
  • 教程
    • 关注 码农网 公众号
    版权所有，保留一切权利！© 2018-2026 码农网 粤ICP备17054400号-3