QT SQLite 辅助库

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

内容简介:SqlHelp.hSqlHelp.cpp

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. }

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

查看所有标签

猜你喜欢:

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

计算机程序设计艺术(第1卷)

计算机程序设计艺术(第1卷)

[美] 唐纳德·E. 克努特 / 苏运霖 / 国防工业出版社 / 2002-9 / 98.00元

7卷本《计算机程序设计艺术》的第1卷以基本的程序设计概念和技术开始,然后专注于信息结构——计算机内部信息的表示、数据元素之间的结构关系以及如何有效地处理它们,给出了对于模拟、数值方法、符号计算、软件和系统设计的初等应用。书中附有大量习题和答案,标明了难易程序及数学概念的使用。 此新版本增加了几十项简单且重要的算法和技术,并对有关数学预备知识作了大量修改以适应现时研究的趋势。一起来看看 《计算机程序设计艺术(第1卷)》 这本书的介绍吧!

URL 编码/解码
URL 编码/解码

URL 编码/解码

UNIX 时间戳转换
UNIX 时间戳转换

UNIX 时间戳转换

HEX CMYK 转换工具
HEX CMYK 转换工具

HEX CMYK 互转工具