QT SQLite 辅助库

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

内容简介：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 辅助库》，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持！

查看所有标签

猜你喜欢:

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

码农书籍
启示录

启示录

[美] Marty Cagan / 七印部落 / 华中科技大学出版社 / 2011-5 / 36.00元

为什么市场上那么多软件产品无人问津，成功的产品凤毛麟角？怎样发掘有价值的产品？拿什么说服开发团队接受你的产品设计？如何将敏捷方法融入产品开发？过去二十多年，Marty Cagan作为高级产品经理人为多家一流企业工作过，包括惠普、网景、美国在线、eBay。他亲历了个人电脑 、互联网、 电子商务的起落沉浮。《启示录：打造用户喜爱的产品》从人员、流程、产品三个角度介绍了现代软件（互联网）产品管理的实践经......一起来看看 《启示录》 这本书的介绍吧!

码农工具
SHA 加密
SHA 加密

SHA 加密工具

XML、JSON 在线转换
XML、JSON 在线转换

在线XML、JSON转换工具

HSV CMYK 转换工具
HSV CMYK 转换工具

HSV CMYK互换工具

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