SQLite C ++ operation class

Document directory

• Usage

To facilitate the use of SQLite, a C ++ class of SQLite is encapsulated and supports both ANSI and UNICODE encoding. The Code is as follows:

Header file (SQLite. h)

/*************************************** * **************************** Filename: SQLite. hcreated: 2012-11-05author: firehoodpurpose: SQLite database operation class ************************************* * ******************************/# pragma once # include <windows. h> # include ".. \ SQLite \ sqlite3.h "# pragma comment (lib," SQLite. lib ") typedef BOOL (WINAPI * QueryCallback) (void * para, int n_column, char ** column_value, char ** co Lumn_name); typedef enum _ SQLITE_DATATYPE {response = SQLITE_INTEGER, response = SQLITE_FLOAT, response = SQLITE_TEXT, response = SQLITE_BLOB, response = SQLITE_NULL,} SQLITE_DATATYPE; class SQLite; class response {public: SQLiteDataReader (sqlite3_stmt * pStmt );~ SQLiteDataReader (); public: // Read a row of Data BOOL Read (); // Close Reader, call void Close () after reading; // The total number of columns int ColumnCount (void ); // obtain the name of a column (maid); // obtain the data type of a column (SQLITE_DATATYPE GetDataType (int nCol); // obtain the value of a column (string) LPCTSTR GetStringValue (int nCol); // get the value of a column (integer) int GetIntValue (int nCol); // obtain the value of a column (long integer) long GetInt64Value (int nCol); // obtain the value of a column (floating point) double GetFloatValue (int nCol); // obtain the value of a column (binary data) const BYTE * Get BlobValue (int nCol, int & nLen); private: sqlite3_stmt * m_pStmt;}; class SQLiteCommand {public: SQLiteCommand (SQLite * pSqlite); SQLiteCommand (SQLite * pSqlite, lpctlpstr SQL ); ~ SQLiteCommand (); public: // set the command BOOL SetCommandText (LPCTSTR lpSql); // bind the parameter (index is the serial number of the parameter to be bound, starting from 1) BOOL BindParam (int index, LPCTSTR szValue); BOOL BindParam (int index, const int nValue); BOOL BindParam (int index, const double dValue); BOOL BindParam (int index, const unsigned char * blobValue, int nLen); // execute the command BOOL Excute (); // Clear the command (call this interface to Clear the command when it is no longer in use) void Clear (); private: SQLite * m_pSqlite; sqlite3_stmt * m_pStmt; }; Class SQLite {public: SQLite (void );~ SQLite (void); public: // Open the database BOOL Open (LPCTSTR lpDbFlie); // Close the database void Close (); // execute non-query operations (update or delete) BOOL ExcuteNonQuery (LPCTSTR lpSql); BOOL ExcuteNonQuery (SQLiteCommand * pCmd); // query SQLiteDataReader ExcuteQuery (LPCTSTR lpSql); // query (callback method) BOOL ExcuteQuery (lptstr lpSql, queryCallback pCallBack); // start the transaction BOOL BeginTransaction (); // submit the transaction BOOL CommitTransaction (); // roll back the transaction BOOL RollbackTransaction (); // obtain the previous error message: Maid (); public: friend class SQLiteCommand; private: sqlite3 * m_db ;};

Source file (SQLite. cpp)

/*************************************** * **************************** Filename: SQLite. cppcreated: 2012-11-05author: firehoodpurpose: SQLite database operation class ************************************* * ******************************/# include "SQLite. H "const char * wchartoutf8 (const wchar_t * pwstr) {If (pwstr = NULL) {return NULL;} int Len = widechartomultibyte (cp_utf8, 0, pwstr,-1, null, 0, null, null); If (LEN <= 0) {retu Rn NULL;} Char * pstr = new char [Len]; widechartomultibyte (cp_utf8, 0, pwstr,-1, pstr, Len, null, null); Return pstr ;} const wchar_t * utf8towchar (const char * pstr) {If (pstr = NULL) {return NULL;} int Len = multibytetowidechar (cp_utf8, 0, pstr,-1, null, 0); If (LEN <= 0) {return NULL;} wchar_t * pwstr = new wchar_t [Len]; multibytetowidechar (cp_utf8, 0, pstr,-1, pwstr, len); Return pwstr;} SQLite: SQLite (void ): M_db (null) {} SQLite ::~ SQLite (void) {close ();} bool SQLite: open (lpctstr lpdbflie) {If (lpdbflie = NULL) {return false ;} # ifdef Unicode if (sqlite3_open16 (lpdbflie, & m_db )! = Sqlite_ OK) # else if (sqlite3_open (lpdbflie, & m_db )! = Sqlite_ OK) # endif {return false;} return true;} void SQLite: Close () {If (m_db) {sqlite3_close (m_db); m_db = NULL ;}} bool SQLite: excutenonquery (lpctstr lpsql) {If (lpsql = NULL) {return false;} sqlite3_stmt * stmt; # ifdef Unicode if (sqlite3_prepare16_v2 (m_db, lpsql,-1, & stmt, null )! = Sqlite_ OK) # else if (sqlite3_prepare_v2 (m_db, lpsql,-1, & stmt, null )! = Sqlite_ OK) # endif {return false;} sqlite3_step (stmt); Return (sqlite3_finalize (stmt) = sqlite_ OK )? True: false;} bool SQLite: excutenonquery (sqlitecommand * pcmd) {If (pcmd = NULL) {return false;} return pcmd-> excute ();} // query (callback method) bool SQLite: excutequery (lpctstr lpsql, querycallback pcallback) {If (lpsql = NULL | pcallback = NULL) {return false ;} char * errmsg = NULL; # ifdef Unicode const char * szsql = wchartoutf8 (lpsql); If (sqlite3_exec (m_db, szsql, pcallback, null, & errmsg )! = Sqlite_ OK) {Delete [] szsql; return false;} Delete [] szsql; # elseif (sqlite3_exec (m_db, lpsql, pcallback, null, & errmsg )! = Sqlite_ OK) {return false ;}# endifreturn true;} // query sqlitedatareader SQLite: excutequery (lpctstr lpsql) {If (lpsql = NULL) {return false ;} sqlite3_stmt * stmt; # ifdef Unicode if (sqlite3_prepare16_v2 (m_db, lpsql,-1, & stmt, null )! = Sqlite_ OK) # elseif (sqlite3_prepare_v2 (m_db, lpsql,-1, & stmt, null )! = Sqlite_ OK) # endif {return false;} return sqlitedatareader (stmt);} // start the transaction bool SQLite: begintransaction () {char * errmsg = NULL; if (sqlite3_exec (m_db, "begin transaction;", null, null, & errmsg )! = Sqlite_ OK) {return false;} return true;} // submit the transaction bool SQLite: committransaction () {char * errmsg = NULL; If (sqlite3_exec (m_db, "Commit transaction;", null, null, & errmsg )! = Sqlite_ OK) {return false;} return true;} // roll back the transaction bool SQLite: rollbacktransaction () {char * errmsg = NULL; If (sqlite3_exec (m_db, "rollback transaction;", null, null, & errmsg )! = Sqlite_ OK) {return false;} return true;} // gets the previous error message: Maid: getlasterrormsg () {# ifdef Unicode return (lpctstr) sqlite3_errmsg16 (m_db ); # else return sqlite3_errmsg (m_db); # endif} sqlitedatareader: sqlitedatareader (sqlite3_stmt * pstmt): m_pstmt (pstmt) {} sqlitedatareader ::~ Sqlitedatareader () {close () ;}// read a row of Data bool sqlitedatareader: Read () {If (m_pstmt = NULL) {return false ;} if (sqlite3_step (m_pstmt )! = Sqlite_row) {return false;} return true;} // close reader. After reading, call void sqlitedatareader: Close () {If (m_pstmt) {sqlite3_finalize (m_pstmt ); m_pstmt = NULL ;}// total number of columns int sqlitedatareader: columncount (void) {return sqlite3_column_count (m_pstmt);} // obtain the name of a column :: getname (INT ncol) {# ifdef Unicode return (lpctstr) sqlite3_column_name16 (m_pstmt, ncol); # elsereturn (lpctstr) sqlite3_column_name (m_pstm T, ncol); # endif} // obtain the data type of a column sqlite_datatype sqlitedatareader: getdatatype (INT ncol) {return (sqlite_datatype) sqlite3_column_type (m_pstmt, ncol );} // obtain the value (string) of a column: Maid: getstringvalue (INT ncol) {# ifdef Unicode return (lpctstr) sqlite3_column_text16 (m_pstmt, ncol); # else return (lpctstr) sqlite3_column_text (m_pstmt, ncol); # endif} // get the value of a column (integer) int sqlitedatareader: getintvalue (INT ncol) {re Turn sqlite3_column_int (m_pstmt, ncol);} // obtain the value of a column (long integer) Long sqlitedatareader: getint64value (INT ncol) {return (long) sqlite3_column_int64 (m_pstmt, ncol);} // obtain the value of a column (floating point) Double sqlitedatareader: getfloatvalue (INT ncol) {return sqlite3_column_double (m_pstmt, ncol );} // obtain the value of a column (binary data) const byte * sqlitedatareader: getblobvalue (INT ncol, Int & nlen) {nlen = sqlite3_column_bytes (m_pstmt, ncol); Return (const byte *) Merge (m_pstmt, ncol);} sqlitecommand: sqlitecommand (SQLite * psqlite): m_psqlite (psqlite), m_pstmt (null) {} sqlitecommand: sqlitecommand (SQLite * psqlite, lptstr lpsql): m_psqlite (psqlite), m_pstmt (null) {setcommandtext (lpsql);} sqlitecommand ::~ Sqlitecommand () {} bool sqlitecommand: setcommandtext (lpctstr lpsql) {# ifdef Unicode if (sqlite3_prepare16_v2 (m_psqlite-> m_db, lpsql,-1, & m_pstmt, null )! = Sqlite_ OK) # elseif (sqlite3_prepare_v2 (m_psqlite-> m_db, lpsql,-1, & m_pstmt, null )! = Sqlite_ OK) # endif {return false;} return true;} bool sqlitecommand: bindparam (INT index, lpctstr szvalue) {# ifdef Unicode if (sqlite3_bind_text16 (m_pstmt, index, szvalue, -1, sqlite_transient )! = Sqlite_ OK) # else if (sqlite3_bind_text (m_pstmt, index, szvalue,-1, sqlite_transient )! = Sqlite_ OK) # endif {return false;} return true;} bool sqlitecommand: bindparam (INT index, const int nvalue) {If (sqlite3_bind_int (m_pstmt, index, nvalue )! = Sqlite_ OK) {return false;} return true;} bool sqlitecommand: bindparam (INT index, const double dvalue) {If (sqlite3_bind_double (m_pstmt, index, dvalue )! = Sqlite_ OK) {return false;} return true;} bool sqlitecommand: bindparam (INT index, const unsigned char * blobbuf, int nlen) {If (sqlite3_bind_blob (m_pstmt, index, index, blobbuf, nlen, null )! = Sqlite_ OK) {return false;} return true;} bool sqlitecommand: excute () {sqlite3_step (m_pstmt); Return (sqlite3_reset (m_pstmt) = sqlite_ OK )? True: false;} void sqlitecommand: clear () {If (m_pstmt) {sqlite3_finalize (m_pstmt );}}

The basic code for using SQLite to operate a database is as follows:

Void SqliteOperate () {TCHAR * szDbPath = _ T ("Book. db ");: DeleteFile (szDbPath); SQLite sqlite; // open or create a database //******************************** * ********************** if (! Sqlite. open (szDbPath) {_ tprintf (_ T ("% s \ n"), sqlite. getLastErrorMsg (); return ;} //************************************** * **************/create a database table //***************** * *********************************** TCHAR SQL [512] = {0 }; _ stprintf (SQL, _ T ("% s"), _ T ("CREATE TABLE [Book] (") _ T ("[id] INTEGER NOT NULL PRIMARY KEY, ") _ T (" [name] NVARCHAR (20), ") _ T (" [author] NVARCHAR (20), ") _ T (" [catagory_id] INTEGER REFEREN CES [Category] ([id]), ") _ T (" [abstruct] NVARCHAR (100), ") _ T (" [path] NVARCHAR (50 ),") _ T ("[image] BLOB);") _ T ("create index [Book_id] ON [Book] ([id]);"); if (! Sqlite. excuteNonQuery (SQL) {printf ("Create database table failed... \ n ");} //************************************** * ************** // insert data [common mode] DWORD dwBeginTick = GetTickCount (); //************************************** * ************** // when multiple records are inserted at a time, improve the efficiency of sqlite by using transactions. beginTransaction (); // insert data in batches for (int I = 0; I <1000; I ++) {memset (SQL, 0, sizeof (SQL )); _ stprintf (SQL, _ T ("insert into Book (name, author, catagory _ Id) values ('Red Sorghum % d', 'mo Yan ', 1) "), I); if (! Sqlite. excuteNonQuery (SQL) {_ tprintf (_ T ("% s \ n"), sqlite. getLastErrorMsg (); break ;}// submit the transaction sqlite. commitTransaction (); printf ("Insert Data Take % dMS... \ n ", GetTickCount ()-dwBeginTick ); //************************************** * ************** // insert data. [by binding parameters, when submitting batch data, it is more efficient (about 45% more) than the preceding normal mode, and binary data can be inserted.] dwBeginTick = GetTickCount (); //************************************** * ************** // when multiple records are inserted at a time, use Improve the efficiency of sqlite. beginTransaction (); memset (SQL, 0, sizeof (SQL); _ stprintf (SQL, _ T ("insert into Book (name, author, catagory_id, image) values (?, 'Han Han ',?,?) "); SQLiteCommand cmd (& sqlite, SQL); // insert data in batches for (int I = 0; I <1000; I ++) {TCHAR strValue [16] = {0}; _ stprintf (strValue, _ T (" % d"), I); // bind the first parameter (name field value) cmd. bindParam (1, strValue); // bind the second parameter (value of the catagory_id field) cmd. bindParam (); BYTE imageBuf [] = {0xff, 0xff, 0xff, 0xff}; // bind the third parameter (image field value, binary data) cmd. bindParam (3, imageBuf, sizeof (imageBuf); if (! Sqlite. excuteNonQuery (& cmd) {_ tprintf (_ T ("% s \ n"), sqlite. getLastErrorMsg (); break ;}// clear cmdcmd. clear (); // submit the transaction sqlite. commitTransaction (); printf ("Insert Data Take % dMS... \ n ", GetTickCount ()-dwBeginTick ); //************************************** * ************** // query dwBeginTick = GetTickCount (); //************************************** * *************** memset (SQL, 0, sizeof (SQL); _ stprintf (SQL, _ T ("% s "), _ T ("select * from Book where name = 'his country 100'"); SQLiteDataReader Reader = sqlite. excuteQuery (SQL); int index = 0; int len = 0; while (Reader. read ()) {_ tprintf (_ T ("***************** [Entry % d Record ]********** * *** \ n "), + + index); _ tprintf (_ T ("field name: % s field value: % d \ n"), Reader. getName (0), Reader. getIntValue (0); _ tprintf (_ T ("field name: % s field value: % s \ n"), Reader. getName (1), Reader. getStringValue (1); _ tprintf (_ T ("field name: % s field value: % s \ n"), Reader. getName (2), Reader. getStringValue (2); _ tprintf (_ T ("field name: % s field value: % d \ n"), Reader. getName (3), Reader. getIntValue (3); _ tprintf (_ T ("field name: % s field value: % s \ n"), Reader. getName (4), Reader. getStringValue (4); // read the Image Binary File const BYTE * ImageBuf = Reader. getBlobValue (6, len ); _ tprintf (_ T ("********************************* * ********* \ n "));} reader. close (); printf ("Query Take % dMS... \ n ", GetTickCount ()-dwBeginTick ); //************************************** * *************** // shut down the database sqlite. close ();}

Running result

Insert Data Take 645MS...
Insert Data Take 229MS...
* ************* [1st records ]***************
Field name: id field value: 1346
Field name: name field value: his country 345
Field name: author field value: Han
Field name: catagory_id field value: 20
Field name: abstruct field value: (null)
**************************************** ***
Query Take 63MS...