Sqlite的相关使用

1. Sqlite简介

SQLite，是一款轻型的数据库，是遵守ACID的关联式数据库管理系统，它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。

Sqlite的数据是存储在单个文件里面，因此你可以看作一个文件就是一个数据库。它使用起来较为方便，无须安装驱动，你的应用程序只须依赖Sqlite动态链接库，就可以进行相应的数据读取操作。它同样支持常用的SQL语法来进行增删改查。它最大的做点就是轻便，无须过多的依赖什么来完成数据操作。

2. Sqlite使用

2.1. 环境配置

要在项目中使用Sqlite，那么先要对其项目环境进行配置。Sqlite开发所需要的文件都存放在一个Common文件夹里面，列表如下图所示：

将其拷贝到项目目录下面，然后在项目属性中进行相关设置，以VS2005为例进行介绍：

1）设置附加目录：

为了方便管理，Sqlite的头文件都在Common文件夹下面，为了方便在代码中调用，直接设置附加目录，这样就可以直接引用文件名，而不用再文件名前再加路径了。

2）设置附加依赖项：

3）将Sqlite文件添加到项目中去

2.2. 基本操作

2.2.1. 数据库的创建

const char* gszFile = "C:\\test.db";//数据库路径

CppSQLite3DB db;

remove(gszFile);                   //清除当前路径的文件

db.open(gszFile);                  //如果数据库存在，则打开数据库，不存在，则创建数据库

2.2.2. 表的创建

在表创建之前，要先进行检测该表是否存在，来避免出现错误：

db.tableExists("emp");      //返回true则为该表已存在， false为不存在

当不存在同名的表，才进行创建

db.execDML("create table emp(empno int, empname char(20));");

创建完成后，同样可利用db.tableExists("emp")来进行判断，创建是否成功。

2.2.3. 基本的SQL语句的执行

一般我们通过SQLite里面的execDML来执行SQL语句：

db.execDML("update emp set numcalls = 10 where dayno = 1;");

在进行增删改等涉及更新数据的操作时，最好是放在事务里面进行操作，一是有效的锁定数据库的使用状态，二是可以进行回滚操作。

如：

db.execDML("begin transaction;");

db.execDML("update emp set numcalls = 10 where dayno = 1;");

db.execDML("commit transaction;");

2.2.4. 数据操作的并发问题

SQLite可以允许多个用户或者线程同时执行查询操作，但最多却只允许一个用户在某一个时段进行数据更新操作。因此，我们在操作数据库的过程中，为了不排除数据库被其他的用户或者线程所占用，我们在使用的过程中，利用try…Catch…来进行捕捉异常，来避免数据库被占用而导致的错误。

try

{

int nRows = db.execDML("update emp set numcalls = 100 where dayno = 1;");

cout << "Main thread: updated " << nRows << " rows" << endl;

}

catch (CppSQLite3Exception& e)

{

cout << "Main thread: " << e.errorCode() << ":" << e.errorMessage() << endl;

}

2.3. 主要类介绍

2.3.1. CppSQLite3Table

CppSQLite3Table是一个SQLite提供的用来专门存放一个完整的表结构，我们也可以通过CppSQLite3Table里面的一些方法和函数去读取这个表里面的内容。

CppSQLite3Table t = db.getTable("select * from emp;");

t.numFields();//返回表里面的总列数

t.fieldName(fld);//返回表里面第fld列的列名

t.numRows();//返回表里面的总行数

t.setRow(i);//i为所在表的第几行

t.fieldIsNull(field);//判断第几列是否为空，field是指该行的第几列数据

t.fieldValue(field);//读取第i行里面的第field列数据

例子：

    CppSQLite3Table t = db.getTable("select * from emp order by 1;");

    for (fld = 0; fld < t.numFields(); fld++)

    {

        cout << t.fieldName(fld) << "|";

    }

    cout << endl;

    for (int row = 0; row < t.numRows(); row++)

    {

        t.setRow(row);

        for (int fld = 0; fld < t.numFields(); fld++)

        {

            if (!t.fieldIsNull(fld))

                cout << t.fieldValue(fld) << "|";

            else

                cout << "NULL" << "|";

        }

        cout << endl;

}

2.3.2. CppSQLite3Query

CppSQLite3Query是SQLite里面用来存放查询的返回结果

CppSQLite3Query q = db.execQuery("select * from emp order by 1;");

q.numFields();//返回这个数据集的总列数

q.fieldName(fld);//返回第fld列的列名

q.fieldDeclType(fld);// 返回第fld列的的数据类型

q.fieldValue(0);//返回某一行的第0列的数据

q.nextRow();//指向数据集当前行的下一行

q.eof();//判断当前行是否为空

例子：

CppSQLite3Query q = db.execQuery("select * from emp order by 1;");

      for (fld = 0; fld < q.numFields(); fld++)

      {

        cout << q.fieldName(fld) << "(" << q.fieldDeclType(fld) << ")|";

      }

      cout << endl;

      while (!q.eof())

      {

        cout << q.fieldValue(0) << "|";

        cout << q.fieldValue(1) << "|" << endl;

        q.nextRow();

}

2.3.3. CppSQLite3Buffer

CppSQLite3Buffer是SQLite里面提供的一个可包含特殊字符的字符串类型。比如在我们以前的程序中的SQL语句中，SQL语句里面涉及到单引号的情况：

CString str;

str.Append(“select * from table where uid = ‘”

str.Append(m_id);

str.Append(“ ’ ”);

有时候显得特别不方便，但是在SQLite里面，通过它提供的这个类型，我们可以这样使用：

CppSQLite3Buffer bufSQL;

bufSQL.format("insert into emp(empno, empname) values (%Q,%Q);", no, name);

db.execDML(bufSQL);

2.3.4. CppSQLite3Exception

CppSQLite3Exception是SQLite里面提供给我们进行捕获异常错误消息的类。可以捕获在我们使用SQLite的过程中出现的异常错误

例如：

try

{

    ……

}

catch (CppSQLite3Exception& e)

{

cout <<e.errorCode() << ":" << e.errorMessage() << endl;

}

2.3.5. CppSQLiteBinary

CppSQLite3Binary可以一些包含空格符、回车符的一些数据转换成相应的二进制数据和编码进行保存和读取，来保证这条数据的完整性。

    可以通过setEncoded()和setBinary()方法来对数据进行转换。

    通过 getEncoded() and getBinary()方法来进行获取数据。

    通过getBinaryLength()来获取数据的长度。

例子：

unsigned char bin[256];

        CppSQLite3Binary blob;

        for (i = 0; i < sizeof bin; i++)

        {

            bin[i] = i;

        }

        blob.setBinary(bin, sizeof bin);

        bufSQL.format("insert into bindata values ('testing', %Q);", blob.getEncoded());

        db.execDML(bufSQL);

        cout << "Stored binary Length: " << sizeof bin << endl;

        q = db.execQuery("select data from bindata where desc = 'testing';");

        if (!q.eof())

        {

            blob.setEncoded((unsigned char*)q.fieldValue("data"));

            cout << "Retrieved binary Length: " << blob.getBinaryLength() << endl;

        }

q.finalize();   

    

2.3.6. CppSQLite3Statement

如果要插入多条SQL语句，但这些SQL语句又仅仅只是插入的数据不一样，格式都是一样的话，那么就可以用这个CppSQLite3Statement来进行保存模版，然后只用往这个模版里面赋予不同的值，然后进行执行就可以。

例如：

db.execDML("begin transaction;");

    CppSQLite3Statement stmt = db.compileStatement("insert into emp values (?, ?);");

     for (i = 0; i < nRowsToCreate; i++)

     {

         char buf[16];

         sprintf(buf, "EmpName%06d", i);

         stmt.bind(1, i);

         stmt.bind(2, buf);

         stmt.execDML();

         stmt.reset();

     }

    db.execDML("commit transaction;");

2.4. 使用注意事项

①　在对SQLite嵌入式数据库使用时，首先要进行判断，判断存放SQLite数据库的文件是否存在，然后再进行操作。同样，对SQLite数据库里面的表进行操作时，也要先进行检测，检测表是否存在。

db.open(gszFile);                  //如果数据库存在，则打开数据库，不存在，则创建数据库

db.tableExists("emp");      //返回true则为该表已存在， false为不存在

②　任何对数据进行更新操作的SQL语句，执行时，一定要放在事务里面进行操作。使用事务，一方面有利于数据回滚，另一方面可对数据库进行独占式操作。SQLite数据库允许多个用户同时进行读操作，但最多却只允许一个用户在某一时刻对数据库进行更新操作和多个用户进行读操作。

db.execDML("begin transaction;");

db.execDML("update emp set numcalls = 10 where dayno = 1;");

db.execDML("commit transaction;");

③　所有数据库的更新操作，一定要用Try…Catch…进行异常捕获处理，获取未知异常。

try

{

int nRows = db.execDML("update emp set numcalls = 100 where dayno = 1;");

cout << "Main thread: updated " << nRows << " rows" << endl;

}

catch (CppSQLite3Exception& e)

{

cout << "Main thread: " << e.errorCode() << ":" << e.errorMessage() << endl;

}

④　在使用SQLite时，对于个表进行读操作的时候，要使用CppSQLite3Table来作为存放数据的容器。对于只对某个表的部分字段进行读操作的时候，要使用CppSQLite3Query来作为存放数据的容器。

⑤　当要拼装一个SQL语句句，严禁使用CString等字符串进行拼装，要使用SQLite自带的CppSQLite3Buffer来进行组装SQL语句。

CppSQLite3Buffer bufSQL;

bufSQL.format("insert into emp(empno, empname) values (%Q,%Q);", no, name);

db.execDML(bufSQL);

⑥　当某一个SQL语句，除了数据的内容不一样以外，所操作的表、字段都一样的使用，这个时候，要使用CppSQLite3Statement来进行保存模板，然后只对模板进行操作，这样可以省去很多SQL语句的编译时间，提高效率。

db.execDML("begin transaction;");

    CppSQLite3Statement stmt = db.compileStatement("insert into emp values (?, ?);");

     for (i = 0; i < nRowsToCreate; i++)

     {

         char buf[16];

         sprintf(buf, "EmpName%06d", i);

         stmt.bind(1, i);

         stmt.bind(2, buf);

         stmt.execDML();

         stmt.reset();

     }

    db.execDML("commit transaction;");