QtSqlite数据库Id自动更新 qt数据库增删改查

QT & sqlite3:

先说一下QT自带数据库sqlite3和另外用sqlite3插件的区别，他们的功能是一样的，但是代码就不一样了。QT对数据库具有完善的支持，不需要加任何其他插件就可以直接使用，但是如果你要是加了sqlite3插件，调用数据库就跟直接调用一个驱动一样，直接调用接口函数:open、close、……，换言之QT自带的数据库语言就用不上了。

一、使用Qt自带的数据库sqlite3

Qt自带了很多常用的数据库驱动，使用起来非常方便如下图所示：

使用Qt自带数据库SQLite3的代码实例：

Connect to Sqlite and do insert, delete, update and select

Foundations of Qt Development\Chapter13\sqltest\sqlite\main.cpp
/*
 * Copyright (c) 2006-2007, Johan Thelin
 * 
 * All rights reserved.
 * 
 * Redistribution and use in source and binary forms, with or without modification, 
 * are permitted provided that the following conditions are met:
 * 
 *     * Redistributions of source code must retain the above copyright notice, 
 *       this list of conditions and the following disclaimer.
 *     * Redistributions in binary form must reproduce the above copyright notice,  
 *       this list of conditions and the following disclaimer in the documentation 
 *       and/or other materials provided with the distribution.
 *     * Neither the name of APress nor the names of its contributors 
 *       may be used to endorse or promote products derived from this software 
 *       without specific prior written permission.
 * 
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR
 * CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR
 * PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
 * LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
 * NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 */

#include <QApplication>

#include <QtSql>
#include <QtDebug>

int main( int argc, char **argv )
{
  QApplication app( argc, argv );
   //创建连接
  QSqlDatabase db = QSqlDatabase::addDatabase( "QSQLITE" );//第二个参数可以设置连接名字，这里为default

  db.setDatabaseName( "./testdatabase.db" );// 设置数据库名与路径, 此时是放在上一个目录
  //打开连接
  if( !db.open() )
  {
    qDebug() << db.lastError();
    qFatal( "Failed to connect." );
  }
    
  qDebug( "Connected!" );
  //各种操作
  QSqlQuery qry;
  //创建table
  qry.prepare( "CREATE TABLE IF NOT EXISTS names (id INTEGER UNIQUE PRIMARY KEY, firstname VARCHAR(30), lastname VARCHAR(30))" );
  if( !qry.exec() )
    qDebug() << qry.lastError();
  else
    qDebug() << "Table created!";
    //增
  qry.prepare( "INSERT INTO names (id, firstname, lastname) VALUES (1, 'John', 'Doe')" );
  if( !qry.exec() )
    qDebug() << qry.lastError();
  else
    qDebug( "Inserted!" );

  qry.prepare( "INSERT INTO names (id, firstname, lastname) VALUES (2, 'Jane', 'Doe')" );
  if( !qry.exec() )
    qDebug() << qry.lastError();
  else
    qDebug( "Inserted!" );

  qry.prepare( "INSERT INTO names (id, firstname, lastname) VALUES (3, 'James', 'Doe')" );
  if( !qry.exec() )
    qDebug() << qry.lastError();
  else
    qDebug( "Inserted!" );

  qry.prepare( "INSERT INTO names (id, firstname, lastname) VALUES (4, 'Judy', 'Doe')" );
  if( !qry.exec() )
    qDebug() << qry.lastError();
  else
    qDebug( "Inserted!" );

  qry.prepare( "INSERT INTO names (id, firstname, lastname) VALUES (5, 'Richard', 'Roe')" );
  if( !qry.exec() )
    qDebug() << qry.lastError();
  else
    qDebug( "Inserted!" );

  qry.prepare( "INSERT INTO names (id, firstname, lastname) VALUES (6, 'Jane', 'Roe')" );
  if( !qry.exec() )
    qDebug() << qry.lastError();
  else
    qDebug( "Inserted!" );

  qry.prepare( "INSERT INTO names (id, firstname, lastname) VALUES (7, 'John', 'Noakes')" );
  if( !qry.exec() )
    qDebug() << qry.lastError();
  else
    qDebug( "Inserted!" );

  qry.prepare( "INSERT INTO names (id, firstname, lastname) VALUES (8, 'Donna', 'Doe')" );
  if( !qry.exec() )
    qDebug() << qry.lastError();
  else
    qDebug( "Inserted!" );

  qry.prepare( "INSERT INTO names (id, firstname, lastname) VALUES (9, 'Ralph', 'Roe')" );
  if( !qry.exec() )
    qDebug() << qry.lastError();
  else
    qDebug( "Inserted!" );
//查询
  qry.prepare( "SELECT * FROM names" );
  if( !qry.exec() )
    qDebug() << qry.lastError();
  else
  {
    qDebug( "Selected!" );
    
    QSqlRecord rec = qry.record();
    
    int cols = rec.count();
    
    for( int c=0; c<cols; c++ )
      qDebug() << QString( "Column %1: %2" ).arg( c ).arg( rec.fieldName(c) );
      
    for( int r=0; qry.next(); r++ )
      for( int c=0; c<cols; c++ )
        qDebug() << QString( "Row %1, %2: %3" ).arg( r ).arg( rec.fieldName(c) ).arg( qry.value(c).toString() );
  }
  
  
  qry.prepare( "SELECT firstname, lastname FROM names WHERE lastname = 'Roe'" );
  if( !qry.exec() )
    qDebug() << qry.lastError();
  else
  {
    qDebug( "Selected!" );
    
    QSqlRecord rec = qry.record();
    
    int cols = rec.count();
    
    for( int c=0; c<cols; c++ )
      qDebug() << QString( "Column %1: %2" ).arg( c ).arg( rec.fieldName(c) );
      
    for( int r=0; qry.next(); r++ )
      for( int c=0; c<cols; c++ )
        qDebug() << QString( "Row %1, %2: %3" ).arg( r ).arg( rec.fieldName(c) ).arg( qry.value(c).toString() );
  }


  qry.prepare( "SELECT firstname, lastname FROM names WHERE lastname = 'Roe' ORDER BY firstname" );
  if( !qry.exec() )
    qDebug() << qry.lastError();
  else
  {
    qDebug( "Selected!" );
    
    QSqlRecord rec = qry.record();
    
    int cols = rec.count();
    
    for( int c=0; c<cols; c++ )
      qDebug() << QString( "Column %1: %2" ).arg( c ).arg( rec.fieldName(c) );
      
    for( int r=0; qry.next(); r++ )
      for( int c=0; c<cols; c++ )
        qDebug() << QString( "Row %1, %2: %3" ).arg( r ).arg( rec.fieldName(c) ).arg( qry.value(c).toString() );
  }


  qry.prepare( "SELECT lastname, COUNT(*) as 'members' FROM names GROUP BY lastname ORDER BY lastname" );
  if( !qry.exec() )
    qDebug() << qry.lastError();
  else
  {
    qDebug( "Selected!" );
    
    QSqlRecord rec = qry.record();
    
    int cols = rec.count();
    
    for( int c=0; c<cols; c++ )
      qDebug() << QString( "Column %1: %2" ).arg( c ).arg( rec.fieldName(c) );
      
    for( int r=0; qry.next(); r++ )
      for( int c=0; c<cols; c++ )
        qDebug() << QString( "Row %1, %2: %3" ).arg( r ).arg( rec.fieldName(c) ).arg( qry.value(c).toString() );
  }
  //更新
  qry.prepare( "UPDATE names SET firstname = 'Nisse', lastname = 'Svensson' WHERE id = 7" );
  if( !qry.exec() )
    qDebug() << qry.lastError();
  else
    qDebug( "Updated!" );

  qry.prepare( "UPDATE names SET lastname = 'Johnson' WHERE firstname = 'Jane'" );
  if( !qry.exec() )
    qDebug() << qry.lastError();
  else
    qDebug( "Updated!" );
  //删除
  qry.prepare( "DELETE FROM names WHERE id = 7" );
  if( !qry.exec() )
    qDebug() << qry.lastError();
  else
    qDebug( "Deleted!" );

  qry.prepare( "DELETE FROM names WHERE lastname = 'Johnson'" );
  if( !qry.exec() )
    qDebug() << qry.lastError();
  else
    qDebug( "Deleted!" );


  db.close();

  return 0;
}

二、Qt中使用sqlite3插件

1、安装sqlite3插件

从官方网站http://www.sqlite.org下载完整版本。

2、安装sqlite3

网上可以看到很多修改下载之后的源代码的论坛，我估计那些帖子比较老一点，最新版的代码已经不存在那些bug了，可以直接编译

 *注意复制粘贴库函数的时候有的动态链接库如果单独复制会丢失之间的链接关系，所以需要一块复制

cp -arf libsqlite3.so libsqlite3.so.0 libsqlite3.so.0.8.6 。。。

3、移植sqlite3

在QTE的include文件中建立新文件夹sqlite3,将头文件放到里面；把库文件放到QTE的lib文件中

4、编程代码实例：

（1） QT生成的.pro文件中添加库指令： LIBS += -lsqlite3

（2） 在调用数据库的文件的头文件里添加头文件和变量

#include "sqlite3/sqlite3.h"            
 sqlite3 *db;        //数据库
 char *zErrMsg;      //出错信息
 char  **resultp;    //调用时的保存位置
 int  nrow;          //列数
int  ncolumn;       //行数
 char  *errmsg;      //出错信息

（3）新建或打开数据库      

if( (sqlite3_open("people.db", &db)) != 0 ){
qDebug()<<"sqlite3 open is false";
 }
else {
qDebug()<<"sqlite3 open is OK";
 }

（4） 建立表格

sqlite3_exec(db, "create table person(name varchar(30) PRIMARY KEY, age int);", NULL, NULL, &zErrMsg);

*添加 PRIMARY KEY 是指定主键，每个数据库只能有一个，主键的值不能重复，比方说你设定name为主键，则相同名字的人只能保存第一个，其他的忽略不计。若想避免这种情况，则去掉主键或者设定id号为主键（id号一直加一，不会重复）。

（5）往表格里写入信息

 a.直接添加数据  

sqlite3_exec(db, "insert into person values('张翼', 30)", NULL, NULL, &zErrMsg);
 sqlite3_exec(db, "insert into person values('hongdy', 28)", NULL, NULL, &zErrMsg);

 b.添加数字变量

int  data=10;
 char sql2[100];  //必须写明大小，划分内存，如果只写一个 char *sql2,会出现段错误
 sprintf(sql2,"insert into person values('张翼',%d);",data);
sqlite3_exec(db,sql2,NULL,NULL,&zErrMsg);
*sprintf的作用是字串格式化命令，主要功能是把格式化的数据写入某个字符串中

 c.添加字符串变量

char data[]="张翼";
 char sql2[100];
 sprintf(sql2,"insert into person values('%s',10);",data);
sqlite3_exec(db,sql2,NULL,NULL,&zErrMsg);

 * %s需要用单引号注释             

d.添加text中的变量到数据库中

 这里需要汉字编码的问题，Windows下默认GBK或GB2312编码，Linux下默认UTF-8编码，所以如果没有设置好会出现乱码

d1. 在main.cpp中添加以下指令，支持中文显示 

#include <QTextCodec>
QTextCodec::setCodecForTr(QTextCodec::codecForName("UTF-8"));
QTextCodec::setCodecForCStrings(QTextCodec::codecForName("UTF-8"));
QTextCodec::setCodecForLocale(QTextCodec::codecForName("UTF-8"));

d2. 读取保存

char *abc=ui->lineEdit->text().toUtf8().data();   //QString 转char*
 sprintf(sql2,"insert into person values('%s',%d);",abc,data);
sqlite3_exec(db,sql2,NULL,NULL,&zErrMsg);

*在调试的时候如果用串口超级终端调试的话，在ARM上显示正常，但是在串口是乱码，不要被迷惑

（6）查询、调用数据库

a. 查询全部

sqlite3_get_table(db, "select * from person", &resultp, &nrow, &ncolumn, &errmsg);
*resultp保存数据库信息，nrow返回列数，ncolumn返回列数

b. 查询部分信息

sqlite3_get_table(db, "select * from person where name='zhang'", &resultp, &nrow, &ncolumn, &errmsg);

c. 变量查询查询

char data[]="张翼";
 char sql3[100];
 sprintf(sql3,"select * from person where name='zhang';",data);
 sqlite3_get_table(db, sql3, &resultp, &nrow, &ncolumn, &errmsg);
 *查询时使用变量的方法和添加时一样

(7）关闭数据库

sqlite3_close(db);

三、VS+QT使用SQL驱动

//1.添加SQL库:"Qt project setting"-->"Qt Modules",在SQL library复选框前打勾.或者在vs项目属性--》连接器--》输入里加入Qt5Sqllib 
 //2.添加头文件 
  //#include<QtSql>//这样写会报Cannot open include file: 'QtSql': No such file or directory,奇葩错误，因为QtSql只是一个文件夹
 #include <QtSql/QSqlDatabase>//这样写可以 
  #include <QtSql/QSqlTableModel> 
  #include<QtSql/QSqlError> 
  //3.创建连接  
  qDebug()<< 
  "available driver:" 
  ;   
  QStringList drivers=QSqlDatabase::drivers();   
  foreach(QString driver,drivers)   
  qDebug()<< 
  "/t" 
  <<driver; 
  QSqlDatabase db=QSqlDatabase::addDatabase( 
  "SQLITE" 
  ); 
  qDebug()<< 
  "SQLITE driver?" 
  <<db.isValid();    
  QString dsn=QString::fromLocal8Bit( 
  "DRIVER={SQL SERVER};SERVER=192.168.0.123;DATABASE=test" 
  );      db.setHostName( 
  "192.168.0.123" 
  ); 
 db.setDatabaseName(dsn); 
  db.setUserName( 
  "sa" 
  ); 
 db.setPassword( 
  "111111" 
  ); 
 if 
  (!db.open()) 
 { 
qDebug()<<db.lastError().text(); 
QMessageBox::critical(0,QObject::tr( 
  "Database Error" 
  ),db.lastError().text());  
  return 
  false 
  ; 
} 
 //4.查询数据 
   QSqlQuery query; 
query.exec( 
  "select * from mytable" 
  ); 
 while 
  (query.next()) 
 { 
 ......... 
  }