sqlite3 wrapper----

一、前言：

    今天试了下如何用C++类实现接口封装，感觉蛮好 。用于封装的类主要有两个，SQLiteStatement类和SQLiteWrapper类，是一个老外写的。我看了下源码，主要是对C接口进行了封装，好处自然不用说，可以重用。很佩服老外的技巧，在这里就引用下他们的代码供大家分享下他们的思想。

源代码链接： http://www.adp-gmbh.ch/sqlite/wrapper.html

二、类源码：

1.头文件：SQLiteWrapper.h

http://sqlitewrapper.kompex-online.com/index.php?content=examples

Kompex SQLite Wrapper for C++ - Examples

Here you can see some examples.
It shows a part of the functionality of the wrapper and how you can use it.
You can find more examples in the example application (contained in download package). 


1. open a database and create a statement instance for sql queries/statements

1. // open database
2. Kompex::SQLiteDatabase *pDatabase = new Kompex::SQLiteDatabase("test.db", SQLITE_OPEN_READWRITE, 0);
3. // create statement instance for sql queries/statements
4. Kompex::SQLiteStatement *pStmt = new Kompex::SQLiteStatement(pDatabase);

2. create a new table and fill it with data

1. // create table and insert some data
2. pStmt->SqlStatement("CREATE TABLE user                        \
3.                     (                                         \
4.                     userID INTEGER NOT NULL PRIMARY KEY,      \
5.                     lastName VARCHAR(50) NOT NULL,            \
6.                     firstName VARCHAR(50), age INTEGER        \
7.                     )");
8.                     
9. pStmt->SqlStatement("INSERT INTO user (userID, lastName, firstName, age) \
10.                      VALUES (1, 'Lehmann', 'Jamie', 20)");
11. pStmt->SqlStatement("INSERT INTO user (userID, lastName, firstName, age) \
12.                      VALUES (2, 'Burgdorf', 'Peter', 55)");
13. pStmt->SqlStatement("INSERT INTO user (userID, lastName, firstName, age) \
14.                      VALUES (3, 'Lehmann', 'Fernando', 18)");
15. pStmt->SqlStatement("INSERT INTO user (userID, lastName, firstName, age) \
16.                      VALUES (4, 'Lehmann', 'Carlene', 17)");

3. use some aggregate functions

1. pStmt->SqlAggregateFuncResult("SELECT COUNT(*) FROM user WHERE lastName = 'Lehmann';");
2. pStmt->SqlAggregateFuncResult("SELECT COUNT(weight) FROM user;");
3. pStmt->SqlAggregateFuncResult("SELECT MAX(age) FROM user;");
4. pStmt->SqlAggregateFuncResult("SELECT MIN(age) FROM user;");
5. pStmt->SqlAggregateFuncResult("SELECT AVG(age) FROM user;");
6. pStmt->SqlAggregateFuncResult("SELECT SUM(age) FROM user;");
7. pStmt->SqlAggregateFuncResult("SELECT TOTAL(age) FROM user;");

4. execute and process a sql query

1. pStmt->Sql("SELECT * FROM user WHERE firstName = 'Jamie';");
2. // process all results
3. while(pStmt->FetchRow())
4. {
5.     std::cout << "first name: " << pStmt->GetColumnInt(0) << std::endl;
6.     std::cout << "last name: " << pStmt->GetColumnString(1) << std::endl;
7.     std::cout << "first name: " << pStmt->GetColumnString(2) << std::endl;
8. }
9. // do not forget to clean-up
10. pStmt->FreeQuery();

5. execute a simple transaction

1. // if an error occurs, a rollback is automatically performed
2. pStmt->BeginTransaction();
3. pStmt->Transaction("INSERT INTO user(userID, firstName, age) VALUES (10, 'Makoto', 23)");
4. pStmt->Transaction("INSERT INTO user(userID, firstName, age) VALUES (11, 'Yura', 20)";
5. pStmt->Transaction("INSERT INTO user(userID, firstName, age) VALUES (12, 'Peter', 63)");
6. pStmt->CommitTransaction();

6. here you can see the exception handling

1. try
2. {
3.     // table structure: userID INTEGER NOT NULL PRIMARY KEY, lastName VARCHAR(50)
4.     pStmt->SqlStatement("INSERT INTO user(userID, lastName) VALUES(1, 'Lehmann')");
5.     // the following line will throw an exception because the primary key is not unique
6.     pStmt->SqlStatement("INSERT INTO user(userID, lastName) VALUES(1, 'Bergmann')");
7. }
8. catch(Kompex::SQLiteException &exception)
9. {
10.     std::cerr << "Exception Occured" << std::endl;
11.     exception.Show();
12. }

7. work with a memory database

1. Kompex::SQLiteDatabase *pDatabase = new Kompex::SQLiteDatabase("scores.db", SQLITE_OPEN_READWRITE, 0);
2. // move database to memory, so that we are work on the memory database hence
3. pDatabase->MoveDatabaseToMemory();
4.  
5. Kompex::SQLiteStatement *pStmt = new Kompex::SQLiteStatement(pDatabase);
6. // insert some data sets into the memory database
7. pStmt->SqlStatement("INSERT INTO score(id, lastScore, avgScore) VALUES(1, 429, 341)");
8. pStmt->SqlStatement("INSERT INTO score(id, lastScore, avgScore) VALUES(2, 37, 44)");
9. pStmt->SqlStatement("INSERT INTO score(id, lastScore, avgScore) VALUES(3, 310, 280)");
10.  
11. // save the memory database to a file
12. // if you don't do it, all database changes will be lost after closing the memory database
13. pDatabase->SaveDatabaseFromMemoryToFile("newScores.db");

8. get some result values via column name (more flexible and a little bit slower than the common way)

1. pStmt->Sql("SELECT * FROM user WHERE lastName = 'Lehmann';");
2.  
3. // process all results
4. while(pStmt->FetchRow())
5. {
6.     std::cout << "firstName: " << pStmt->GetColumnString("firstName") << std::endl;
7.     std::cout << "age: " << pStmt->GetColumnInt("age") << std::endl;
8. }
9.  
10. // do not forget to clean-up
11. pStmt->FreeQuery();    

9. two possibilities to update data in the database

1. // the first way
2. pStmt->SqlStatement("UPDATE user SET weight=51.5, age=18 WHERE firstName='Carlene'");
3.         
4. // the second way - with a prepared statement
5. pStmt->Sql("UPDATE user SET lastName=@lastName, age=@age WHERE userID=@userID");
6.             
7. // bind an integer to the prepared statement
8. pStmt->BindString(1, "Urushihara");        // bind lastName
9. pStmt->BindInt(2, 56);                    // bind age
10. pStmt->BindInt(3, 2);                    // bind userID
11.  
12. // execute it and clean-up
13. pStmt->ExecuteAndFree();