Cocos data article [3.4] (6)--sqlite3 database basic usage

Nagging

In cocos2d-x, simple data storage, you can use Userdefault. So how do you store large, irregular data? We can use the SQLite database to store data. SQLite is a very extensive embedded database , it has a small, efficient, cross-platform, open source free and easy to operate features. so a lot of it is used in mobile phones, PDAs, MP3 players, and set-top box devices.

SQLite database is written in C language , so it is handy to use SQLite in Cocos2d-x.

This article introduces the basic usage of SQLite3 database: Adding and deleting .

PS: In addition, for SQLite visual management tools, I use sqlitestudio, this is self-understanding it.

Reference

http://cn.cocos2d-x.org/tutorial/show?id=1351 ("Official documents" SQLite integration and usage)

Http://blog.chinaunix.net/uid-8447633-id-3321394.html (SQLite3 use summary)

"SQLite3"

1. Preparatory work

The sqlite3 in the external folder of the Cocos engine seems to have only two. h files and no sqlite3.c files. So I compile the time has been compiled do not pass. Later to the SQLite official website under the source files, only the successful compilation.

(1) First, to download the source file for Sqlite3.

:http://www.sqlite.org/download.html

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/59/CA/wKioL1TkqoDjgyFaAABIim5S4ik833.jpg "title=" 1.png " alt= "Wkiol1tkqodjgyfaaabiim5s4ik833.jpg"/>

(2) Copy the Sqlite3 three source files into the project's classes file and import them into the project.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/59/CA/wKioL1TkrAvTwlsqAACr7MIYIUA012.jpg "title=" 2.png " alt= "Wkiol1tkravtwlsqaacr7miyiua012.jpg"/>

If it is an Android platform, you also need to modify the android.mk file.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/59/CE/wKiom1Tkq27ik1X9AAC5PIxCEvI796.jpg "title=" 3.png " alt= "Wkiom1tkq27ik1x9aac5pixcevi796.jpg"/>

2, SQLite3 's basic API

Here only to introduce the SQLite3 most basic API, can help you to complete the database of additions and deletions to check the function.

Note: The encoding format used by the SQLite3 database is: UTF-8 . If garbled, it will be converted to UTF-8 format.

2.1. Key data structure: sqlite3*

The most commonly used in SQLite is:sqlite3* type.

From the start of the database, SQLite will have to prepare the memory for this type, until the database is closed, the entire process needs to use this type. When the database opens, this type of variable represents the database you want to manipulate. Details are described below.

2.2. Open database: Sqlite3_open

int Sqlite3_open (const char *filename, Sqlite3 **ppdb)

This function is used to open the database.

(1)filename : The database file name (for example: "/users/apple/documents/data.db"). The file name does not need to exist, and SQLite automatically establishes it if it does not exist. If it exists, try to open it as a database file.

(2)ppdb : The key data structure mentioned earlier. What's the bottom detail of this structure, you don't close it.

(3) return value : If sqlite_ok , the database is opened successfully.

2.3. Close the database: Sqlite3_close

int Sqlite3_close (SQLITE3 *)

This function is used to close the database. If you no longer use the database, you must shut down the database and free up memory resources.

(1)sqlite3* : The key data structure mentioned earlier.

(2) return value : If sqlite_ok , the database is closed successfully.

2.4. Execute SQL statement: Sqlite3_exec

int sqlite3_exec (sqlite3*, const char *sql, int (*callback) (void*,int,char**,char**), void*, Char **errmsg)

This function is used to execute the SQL statement, to the database: adding and deleting changes.

(1)sqlite3* : The key data structure mentioned earlier.

(2)SQL : SQL statement (add and revise).

(3)(*callback) : When exec is executed, this callback function is called every time a record is found (specifically described in 2.5 ).

(4)void* : Pass data, can be any value. is passed to the callback function callback, which is passed in as its first argument.

(5)errmsg : Error message.

Note: The following three parameters, if not required, can be filled with NULL.

2.5. Sqlite3_exec callback function Callback

Int (*sqlite3_callback) (void* para, int col_num, char** col_value, char** col_name)

This function acts as the third parameter of the sqlite3_exec. When exec executes, it is called once per query to a record.

(1)para : That is, exec's fourth parameter, the data passed.

(2)Col_num : How many fields a record has (that is, the number of columns in this record).

(3)Col_value : The data that is queried (that is, the value of each field). It is a one-dimensional array (do not think it is a 2-dimensional array), each element is a string char * value, is a field content.

(4)col_name : field name (i.e. column name) for each field, corresponding to Col_value.

2.6, do not use callback query database: sqlite3_get_table

Sqlite3_get_table (sqlite3 *db, const char *sql, char ***result, int *row, int *col, char **errmsg)

This function is used to query the database.

(1)db : The key data structure mentioned earlier.

(2)SQL : SQL query statement.

(3)result : query result. It's still a one-dimensional array (don't think of it as a two-dimensional array, let alone a three-dimensional array). Its memory layout is: The first row is the field name, followed by the value of each field. That is: 0~col-1 is the field name, Col~2*col-1 is the first row of records, 2*COL~3*COL-1 is the second row of records, ... And so on

(4) Row: number of rows.

(5) Col: Number of columns.

(6) ErrMsg: Error message.

Note: After the result of this function is accessed, you need to use Sqlite3_free_table (Retult) to release the resource.

3. How to use

3.1. Introduction of header File

Add source code sqlite3.h header files downloaded from the SQLite website.

#include "sqlite3.h"//

3.2. Create a database

Create the database, and if the file does not exist, SQLite will be created automatically. If the file exists, try to open it as a database file.

Database path std::string path = "/soft/cocos2d-x-3.4/projects/demo34/resources/data.db"; std::string SQL;         SQL statement int ret; Execution result, SQLITE_OK indicates successful execution//create SQLite database: sqlite3*sqlite3* pdb = nullptr;//Open SQLite database: Sqlite3_openret = Sqlite3_open ( Path.c_str (), &pdb);//When Sqllite database open fails if (ret! = SQLITE_OK) {//Get Sqltite Database open error Information const char* ERRMSG = SQLITE3_ERRM SG (PDB); Cclog ("SQLite Open Error:%s", errmsg); Sqlite3_close (PDB); return false;} //

3.3. Create a table

Create a table: The CREATE TABLEtable name (ID, name, sex)

Table name: Student. There are three fields: ID, name, and sex (where ID is the primary key increment).

PS: Create a table without the need for a callback function. The next three parameters of the sqlite3_exec are filled with NULL.

SQL statement Creating table: Create Tablesql = "CREATE TABLE student (ID integer primary key autoincrement, name text, sex text)";//Execute sq L Statement: Sqlite3_execret = sqlite3_exec (pdb, SQL.C_STR (), nullptr, nullptr, nullptr), if (ret! = SQLITE_OK) {cclog ("Create Table failed ");} //

3.4. Insert data (Increase)

Insert three data:insert INTO table name value (1, "name", "Sex")

    ps: Insert data, no callback function is required. The next three parameters of the sqlite3_exec are filled with NULL.

  (1 ,  ' student1 '  ,  ' male ') sql =  "insert into student  VALUES (1,  ' student1 ',  ' Male '); Ret = sqlite3_exec (Pdb, sql.c_str (),  nullptr,  NULLPTR, NULLPTR); if (RET != SQLITE_OK)  {cclog ("insert data failed!");}   (2 ,  ' student3 '  ,  ' female ') sql =  "insert into student  VALUES (2,  ' student2 ',  ' female '); Ret = sqlite3_exec (Pdb, sql.c_str (),  nullptr,  NULLPTR, NULLPTR); if (RET != SQLITE_OK)  {cclog ("insert data failed!");}   (3 ,  ' student3 '  ,  ' male ') sql =  "insert into student  VALUES (3,  ' student3 ',  ' Male '); Ret = sqlite3_exec (Pdb, sql.c_str (),  nullptr,  NULLPTR, NULLPTR); if (RET != SQLITE_OK)  {cclog ("insert data failed!");} //

3.5, query data, use Sqlite3_exec (check)

Use Sqlite3_exec to query:select * FROM table name

Query Statement sql = "SELECT * from student"; ret = sqlite3_exec (PDB, SQL.C_STR (), &callback, (void*) "Para", nullptr);//callback function int callback (void* para, int col_num, char** col_value, char** col_name) {Cclog ("%s: Total%d fields", (char *) para, col_num); for (int i = 0; i < Col_num; i++) {Cclog ("%s =%s", Col_name[i], col_value[i]);} Cclog ("------------------------------"); Delimiter return 0;} //

Console Output results:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/59/CA/wKioL1Tk5gLhJCfoAAB40LWF3vk323.jpg "title=" 5.png " alt= "Wkiol1tk5glhjcfoaab40lwf3vk323.jpg"/>

3.6. Deleting data (delete)

Delete First record:delete from table name where condition

Delete the first record sql = "Delete from student where ID = 1"; ret = sqlite3_exec (PDB, SQL.C_STR (), nullptr, nullptr, nullptr); if (re T! = SQLITE_OK) {cclog ("delete data failed!");} //

3.7, modify the data (change)

Modify data:Update table name Set name = "Hello" Where Condition

Modify the Name field of the third record sql = "UPDATE student set name = ' Hello ' where ID = 3"; ret = sqlite3_exec (PDB, SQL.C_STR (), nullptr, Nullptr, nullptr); if (ret! = SQLITE_OK) {cclog ("Update data failed!");} //

3.8, query data, use Sqlite3_get_table (check)

Use sqlite3_get_table to query:select * FROM table name

When the query is finished, the query results are saved directly instead of using the callback function.

char** table;     Query result int r, C; Number of rows, number of columns sql = "SELECT * from Student"; Sqlite3_get_table (PDB, SQL.C_STR (), &table, &r, &c, nullptr); Cclog ("Number of rows is%d, number of columns is%d", R, c);//Line No. 0 (0 ~ c-1), field name//1th line (c ~ 2*c-1), first record//... for (int i = 0; I <= r; i++) {for (int j = 0; J < C; j + +) {Cclog ("%s", Table[i * C + j]);} Cclog ("------------------------------");} Remember whether to query table sqlite3_free_table (table);//

Console Output results:

You can see that the data for id = 1 has been deleted, and the data for id = 3 is changed to "Hello".

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/59/CE/wKiom1Tk5R-DsWEYAABBhYWVxyk425.jpg "title=" 6.png " alt= "Wkiom1tk5r-dsweyaabbhywvxyk425.jpg"/>

3.9. Close the database

Finally, remember to close the database, otherwise it will cause a memory leak.

Sqlite3_close (PDB);//

"Full code Download":http://down.51cto.com/data/1983839

This article is from the "Summer Wind" blog, please be sure to keep this source http://shahdza.blog.51cto.com/2410787/1614851

Cocos data article [3.4] (6)--sqlite3 database basic usage