内容简介:SqlHelp.hSqlHelp.cpp
SqlHelp.h
点击( 此处 )折叠或打开
- #ifndef SQLHELP_H
- #define SQLHELP_H
- #include < QString >
- #include < QSqlDatabase >
- #include < QSqlQuery >
- #include < QVector >
- class SqlHelp
- {
- public :
- SqlHelp ( ) ;
- ~ SqlHelp ( ) ;
- void createConnection ( QString dbFile ) ;
- void closeConnection ( ) ;
- void createDb ( QString dbFile ) ;
- void deleteDb ( ) ;
- void createTable ( QString tableName , QVector < QString > fdNameTypePairs ) ; / / fdNameTypePairs 字段名称,类型对列表
- void deleteTable ( QString tableName ) ;
- void addRecord ( QString tableName , QVector < QString > fdNameValuePairs ) ;
- void delRecord ( QString tableName , QString condition ) ;
- void setRecord ( QString tableName , QVector < QString > fdNameValuePairs , QString condition ) ;
- void showTableUI ( QString tableName ) ;
- QVector < QString > getFirstRecord ( QString tableName , QVector < QString > fdNames , QString condition ) ;
- QVector < QString > getAllTableName ( ) ;
- void execSql ( const QString sqlStr ) ;
- QString getLastSqlString ( ) const ;
- QString getLastErrorText ( ) const ;
- QString vectorToString ( const QVector < QString > & list , QString split ) const ;
- void testCase ( ) ;
- private :
- QSqlDatabase connection ;
- QString lastSqlString ;
- QString lastErrorText ;
- } ;
- #endif / / SQLHELP_H
SqlHelp.cpp
点击( 此处 )折叠或打开
- #include "SqlHelp.h"
- #include < QMessageBox >
- #include "ui/MessageDialog.h"
- #include < QSqlError >
- #include < QSqlTableModel >
- #include < QTableView >
- #include "debug.h"
- / * *
- * @brief SqlHelp : : SqlHelp
- *
- * QT SQLite 辅助库
- * Authour : dikui
- * 测试方式:main . cpp 中添加两行
- SqlHelp h ;
- h . testCase ( ) ;
- 其中MessageDialog为自定义的对话框
- debug . h仅包含了TRACE_MSG定义,用于调试打印输出,实际是封装了qDebug ( ) 而已
- testCase ( ) ; 执行完后会产生一个数据库test . db ,里面包含了10条数据
- * /
- SqlHelp : : SqlHelp ( )
- {
- }
- SqlHelp : : ~ SqlHelp ( )
- {
- closeConnection ( ) ;
- }
- void SqlHelp : : createConnection ( QString dbFile )
- {
- connection = QSqlDatabase : : addDatabase ( "QSQLITE" ) ;
- connection . setDatabaseName ( dbFile ) ;
- if ( ! connection . open ( ) ) {
- MessageDialog msgBox ;
- msgBox . setTitle ( "ERROR" ) ;
- msgBox . setMessage ( "Cannot open database :" + dbFile ) ;
- }
- }
- void SqlHelp : : closeConnection ( )
- {
- if ( connection . isOpen ( ) ) {
- connection . close ( ) ;
- }
- }
- void SqlHelp : : createDb ( QString dbFile )
- {
- Q_ASSERT ( dbFile . isEmpty ( ) = = false ) ;
- connection = QSqlDatabase : : addDatabase ( "QSQLITE" ) ;
- connection . setDatabaseName ( dbFile ) ;
- if ( ! connection . open ( ) ) {
- MessageDialog msgBox ;
- msgBox . setTitle ( "ERROR" ) ;
- msgBox . setMessage ( "Cannot open database :" + dbFile ) ;
- }
- }
- void SqlHelp : : deleteDb ( )
- {
- }
- QVector < QString > SqlHelp : : getAllTableName ( )
- {
- / *
- SQLite数据库中一个特殊的表叫 sqlite_master,sqlite_master的结构。
- CREATE TABLE sqlite_master (
- type TEXT ,
- name TEXT ,
- tbl_name TEXT ,
- rootpage INTEGER ,
- sql TEXT
- ) ;
- 我们可以通过查询这个表来获取数据库所有的表名:
- SELECT name FROM sqlite_master WHERE type = ' table ' ORDER BY name ;
- * /
- QVector < QString > result ;
- QString sqlStr = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name" ;
- QSqlQuery query ( connection ) ;
- if ( ! query . exec ( sqlStr ) ) {
- MessageDialog msgBox ;
- msgBox . setTitle ( "ERROR" ) ;
- msgBox . setYesVisible ( false ) ;
- lastSqlString = sqlStr ;
- lastErrorText = query . lastError ( ) . text ( ) ;
- msgBox . setMessage ( "SQL string :" + lastSqlString + "\n" + "Error text:" + lastErrorText ) ;
- msgBox . exec ( ) ;
- }
- / / 遍历表名
- while ( query . next ( ) ) {
- result . append ( query . value ( 0 ) . toString ( ) ) ;
- }
- return result ;
- }
- / * *
- * @brief SqlHelp : : createTable 创建表
- * @param tableName 表名称
- * @param fdNameTypePairs 字段名,字段值类型对。比如表字段为 id,name,类型分别为int primary key , varchar
- * 那么 fdNameTypePairs 数组的值依次设置为 "id" , "int primary key" , "name" , "varchar"
- * /
- void SqlHelp : : createTable ( QString tableName , QVector < QString > fdNameTypePairs )
- {
- / * *
- QVector < QString > fdDeclareList ;
- fdDeclareList < < "id" < < "int primary key" ;
- fdDeclareList < < "name" < < "varchar" ;
- fdDeclareList < < "color" < < "varchar" ;
- fdDeclareList < < "picture" < < "varchar" ;
- fdDeclareList < < "barcode" < < "int" ;
- fdDeclareList < < "stage" < < "varchar" ;
- h . createTable ( "recipe" , fdDeclareList ) ;
- * /
- / / query1 . exec ( "create table student (id int primary key, name varchar(20))" ) ;
- / / 检查表是否已存在
- QVector < QString > existTables = getAllTableName ( ) ;
- if ( existTables . indexOf ( tableName ) > = 0 ) {
- return ;
- }
- QString createStr = "create table " + tableName ;
- QString nameTypeStr ;
- if ( fdNameTypePairs . count ( ) % 2 ) {
- MessageDialog msgBox ;
- msgBox . setTitle ( "Error" ) ;
- msgBox . setMessage ( "fdNameTypePairs count error" ) ;
- msgBox . exec ( ) ;
- }
- for ( int i = 0 ; i < fdNameTypePairs . count ( ) ; i + = 2 ) {
- nameTypeStr + = fdNameTypePairs . at ( i ) + " " + fdNameTypePairs . at ( i + 1 ) + "," ;
- }
- nameTypeStr = nameTypeStr . mid ( 0 , nameTypeStr . length ( ) - 1 ) ; / / remove last ","
- QString sqlStr = createStr + " (" + nameTypeStr + ")" ;
- execSql ( sqlStr ) ;
- }
- / * *
- * @brief SqlHelp : : deleteTable 删除表
- * @param tableName
- * /
- void SqlHelp : : deleteTable ( QString tableName )
- {
- QString sqlStr = "drop table " + tableName ;
- execSql ( sqlStr ) ;
- }
- / * *
- * @brief SqlHelp : : addRecord 向表中添加一条记录
- * @param tableName 操作的表名
- * @param fdNameValuePairs 字段名,字段值对。比如添加项为 id = 1 name = "zhangshan"
- * 那么fdNameValuePairs数组的值依次设置为 "id" , "1" , "name" , "'zhangshan'" , 注意字符串类型的值要加上 '
- * /
- void SqlHelp : : addRecord ( QString tableName , QVector < QString > fdNameValuePairs )
- {
- / / INSERT INTO table_name ( 列1 , 列2 , . . . ) VALUES ( 值1 , 值2 , . . . . )
- / / QString ( "INSERT INTO %1 ( %2 ) VALUES ( %3 )" ) . arg ( tableName ) . arg ( nameStr ) . arg ( valueStr ) ;
- if ( fdNameValuePairs . count ( ) % 2 ) {
- MessageDialog msgBox ;
- msgBox . setTitle ( "Error" ) ;
- msgBox . setMessage ( "fdNameValuePairs count error" ) ;
- msgBox . exec ( ) ;
- return ;
- }
- QVector < QString > names ;
- QVector < QString > values ;
- for ( int i = 0 ; i < fdNameValuePairs . count ( ) ; i + = 2 ) {
- names . append ( fdNameValuePairs . at ( i ) ) ;
- values . append ( fdNameValuePairs . at ( i + 1 ) ) ;
- }
- QString nameStr ;
- QString valueStr ;
- / / 拼接 name
- for ( int i = 0 ; i < names . count ( ) ; + + i ) {
- nameStr + = names . at ( i ) + "," ;
- }
- nameStr = nameStr . mid ( 0 , nameStr . length ( ) - 1 ) ; / / remove last ","
- / / 拼接 value
- for ( int i = 0 ; i < values . count ( ) ; + + i ) {
- valueStr + = values . at ( i ) + "," ;
- }
- valueStr = valueStr . mid ( 0 , valueStr . length ( ) - 1 ) ; / / remove last ","
- / / sqlStr = actionStr + " (" + nameStr + ") VALUES (" + valueStr + ")" ;
- QString sqlStr = QString ( "INSERT INTO %1 ( %2 ) VALUES ( %3 )" ) . arg ( tableName ) . arg ( nameStr ) . arg ( valueStr ) ;
- execSql ( sqlStr ) ;
- }
- void SqlHelp : : delRecord ( QString tableName , QString condition )
- {
- / / DELETE FROM 表名称 WHERE 列名称 = 值
- QString sqlStr ;
- if ( condition . isEmpty ( ) ) {
- sqlStr = QString ( "DELETE FROM %1" ) . arg ( tableName ) ;
- } else {
- sqlStr = QString ( "DELETE FROM %1 WHERE %2" ) . arg ( tableName ) . arg ( condition ) ;
- }
- execSql ( sqlStr ) ;
- }
- void SqlHelp : : setRecord ( QString tableName , QVector < QString > fdNameValuePairs , QString condition )
- {
- / / 参数说明
- / / condition为空时,应用到所有列
- / / 返回值
- / / 更新某一行中的若干列
- / / UPDATE Person SET Address = ' Zhongshan 23 ' , City = ' Nanjing ' WHERE LastName = ' Wilson '
- / / QString ( "UPDATE %1 SET %2 WHERE %3" ) . arg ( tableName ) . arg ( nameValueStr ) . arg ( condition ) ;
- QString nameValueStr ;
- if ( fdNameValuePairs . count ( ) % 2 ) {
- MessageDialog msgBox ;
- msgBox . setTitle ( "Error" ) ;
- msgBox . setMessage ( "fdNameValuePairs count error" ) ;
- msgBox . exec ( ) ;
- return ;
- }
- QVector < QString > names ;
- QVector < QString > values ;
- for ( int i = 0 ; i < fdNameValuePairs . count ( ) ; i + = 2 ) {
- names . append ( fdNameValuePairs . at ( i ) ) ;
- values . append ( fdNameValuePairs . at ( i + 1 ) ) ;
- }
- / / 拼接 name = value
- for ( int i = 0 ; i < names . count ( ) ; + + i ) {
- nameValueStr + = names . at ( i ) + " = " + values . at ( i ) + "," ;
- }
- nameValueStr = nameValueStr . mid ( 0 , nameValueStr . length ( ) - 1 ) ; / / remove last ","
- QString sqlStr ;
- if ( condition . isEmpty ( ) ) {
- sqlStr = QString ( "UPDATE %1 SET %2 " ) . arg ( tableName ) . arg ( nameValueStr ) ;
- } else {
- sqlStr = QString ( "UPDATE %1 SET %2 WHERE %3" ) . arg ( tableName ) . arg ( nameValueStr ) . arg ( condition ) ;
- }
- execSql ( sqlStr ) ;
- }
- QVector < QString > SqlHelp : : getFirstRecord ( QString tableName , QVector < QString > fdNames , QString condition )
- {
- / / condition为空时,应用到所有列
- / / SELECT LastName , FirstName FROM Persons
- QVector < QString > fdValues ;
- QString nameStr = vectorToString ( fdNames , "," ) ;
- QString sqlStr ;
- if ( condition . isEmpty ( ) ) {
- sqlStr = QString ( "SELECT %1 FROM %2 " ) . arg ( nameStr ) . arg ( tableName ) ;
- } else {
- sqlStr = QString ( "SELECT %1 FROM %2 WHERE %3" ) . arg ( nameStr ) . arg ( tableName ) . arg ( condition ) ;
- }
- QSqlQuery query ( connection ) ;
- if ( ! query . exec ( sqlStr ) ) {
- MessageDialog msgBox ;
- msgBox . setTitle ( "ERROR" ) ;
- msgBox . setYesVisible ( false ) ;
- lastSqlString = sqlStr ;
- lastErrorText = query . lastError ( ) . text ( ) ;
- msgBox . setMessage ( "SQL string :" + lastSqlString + "\n" + "Error text:" + lastErrorText ) ;
- msgBox . exec ( ) ;
- }
- if ( query . first ( ) ) {
- for ( int i = 0 ; i < fdNames . count ( ) ; + + i ) {
- fdValues . append ( query . value ( i ) . toString ( ) ) ;
- }
- return fdValues ;
- }
- return fdValues ;
- }
- void SqlHelp : : showTableUI ( QString tableName )
- {
- QSqlTableModel * model = new QSqlTableModel ( ) ;
- model - > setTable ( tableName ) ;
- model - > select ( ) ;
- / / 设置编辑策略
- model - > setEditStrategy ( QSqlTableModel : : OnManualSubmit ) ;
- QTableView * tableView = new QTableView ( ) ;
- tableView - > setModel ( model ) ;
- MessageDialog msgBox ;
- msgBox . setTitle ( tableName ) ;
- msgBox . setWorkUI ( tableView ) ;
- msgBox . exec ( ) ;
- delete tableView ;
- delete model ;
- }
- void SqlHelp : : execSql ( const QString sqlStr )
- {
- if ( ! connection . isOpen ( ) ) {
- MessageDialog msgBox ;
- msgBox . setTitle ( "ERROR" ) ;
- msgBox . setMessage ( "connection is not open" ) ;
- msgBox . exec ( ) ;
- return ;
- }
- QSqlQuery query ( connection ) ;
- if ( ! query . exec ( sqlStr ) ) {
- MessageDialog msgBox ;
- msgBox . setTitle ( "ERROR" ) ;
- msgBox . setYesVisible ( false ) ;
- lastSqlString = sqlStr ;
- lastErrorText = query . lastError ( ) . text ( ) ;
- msgBox . setMessage ( "SQL string :" + lastSqlString + "\n" + "Error text:" + lastErrorText ) ;
- msgBox . exec ( ) ;
- }
- }
- QString SqlHelp : : getLastSqlString ( ) const
- {
- return lastSqlString ;
- }
- QString SqlHelp : : getLastErrorText ( ) const
- {
- return lastErrorText ;
- }
- QString SqlHelp : : vectorToString ( const QVector < QString > & list , QString split ) const
- {
- QString result ; / / 转化成val1 , val2 , val3
- for ( int i = 0 ; i < list . count ( ) ; + + i ) {
- result + = list . at ( i ) + split ;
- }
- int index = result . lastIndexOf ( split ) ;
- result . remove ( index , split . length ( ) ) ;
- return result ;
- }
- void SqlHelp : : testCase ( )
- {
- QString tableName = "recipe" ;
- createDb ( "test.db" ) ;
- / / h . deleteTable ( "recipe" ) ;
- QVector < QString > fdDeclareList ;
- fdDeclareList < < "id" < < "INTEGER PRIMARY KEY autoincrement" ;
- fdDeclareList < < "name" < < "varchar" ;
- fdDeclareList < < "color" < < "varchar" ;
- fdDeclareList < < "picture" < < "varchar" ;
- fdDeclareList < < "barcode" < < "int" ;
- fdDeclareList < < "stage" < < "varchar" ;
- createTable ( tableName , fdDeclareList ) ;
- for ( int i = 0 ; i < 10 ; + + i ) {
- QVector < QString > fdDeclareList ;
- / / fdDeclareList < < "id" < < QString : : number ( i ) ;
- fdDeclareList < < "name" < < QString ( "'name%1'" ) . arg ( i ) ;
- fdDeclareList < < "color" < < QString ( "'ff000%1'" ) . arg ( i ) ;
- fdDeclareList < < "picture" < < QString ( "'picture%1'" ) . arg ( i ) ;
- fdDeclareList < < "barcode" < < QString ( "20181219000000%1" ) . arg ( i ) ;
- fdDeclareList < < "stage" < < QString ( "'stage%1'" ) . arg ( i ) ;
- addRecord ( "recipe" , fdDeclareList ) ;
- TRACE_MSG ( i ) ;
- }
- / / h . delRecord ( tableName , "1=1" ) ;
- QVector < QString > fdNameValuePair2 ;
- fdNameValuePair2 < < "barcode" < < "1" ;
- setRecord ( tableName , fdNameValuePair2 , "1=1" ) ;
- showTableUI ( tableName ) ;
- QVector < QString > fdNames ;
- fdNames < < "id" < < "name" ;
- TRACE_MSG ( getFirstRecord ( tableName , fdNames , "" ) ) ;
- }
以上所述就是小编给大家介绍的《QT SQLite 辅助库》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!
猜你喜欢:本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
计算机程序设计艺术(第1卷)
[美] 唐纳德·E. 克努特 / 苏运霖 / 国防工业出版社 / 2002-9 / 98.00元
7卷本《计算机程序设计艺术》的第1卷以基本的程序设计概念和技术开始,然后专注于信息结构——计算机内部信息的表示、数据元素之间的结构关系以及如何有效地处理它们,给出了对于模拟、数值方法、符号计算、软件和系统设计的初等应用。书中附有大量习题和答案,标明了难易程序及数学概念的使用。 此新版本增加了几十项简单且重要的算法和技术,并对有关数学预备知识作了大量修改以适应现时研究的趋势。一起来看看 《计算机程序设计艺术(第1卷)》 这本书的介绍吧!