SQLite Integration and usage

this article reprinted to http://cn.cocos2d-x.org/article/index?type=cocos2d-x&url=/doc/cocos-docs-master/manual/framework/ Native/v3/sqlite/zh.mdOverview

Overview
In Cocos2d-x, for simple data storage, UserDefault can be used. So how do you store large amounts of irregular data? We can use SQLite database to store data. SQLite is a very widely used embedded database, which is small, efficient, cross-platform, open source free and easy to operate.

SQLite database is written in C language, so using SQLite in Cocos2d-x is also handy.

ready
First create a helloworld project of Cocos2d-x v3.x. We will use this project as a practical project for SQLite integration and usage.

Open a terminal and use the following command to create a new project:

cocos new HelloWorld -p com.your_company.HelloWorld -l cpp

Following the above operation, we created a new Cocos2d-x v3.x HelloWorld project.

iOS / Mac
The iOS / Mac system library comes with the sqlite library, we just need to add the libsqlite3.0.dylib library.




Android
The Android system does not come with the sqlite library, we need to add it manually.

1. Download the sqlite package

Download address: http://www.sqlite.org/download.html After downloading, import two files of sqlite3.c and sqlite3.h into the project.

2. Import into the project




3. Modify Android.mk





Use SQLite
Open HelloWorldScene.cpp file, we add SQLite usage examples in it

Introduce header files

#include "sqlite3.h"


Create a SQLite database
sqlite3 * pdb = NULL; // 1
std :: string path = FileUtils :: getInstance ()-> getWritablePath () + "save.db"; // 2
 
std :: string sql;
int result;
result = sqlite3_open (path.c_str (), & pdb); // 3
if (result! = SQLITE_OK)
{
    log ("open database failed, number% d", result);
}


Database pointer
Specify the database path
Open a database, if the database does not exist, create a database file
SQL statement
sql = "create table student (ID integer primary key autoincrement, name text, sex text)"; // 1

1. Create a SQL statement for the table

Create Table
result = sqlite3_exec (pdb, sql.c_str (), NULL, NULL, NULL); // 2
if (result! = SQLITE_OK)
    log ("create table failed");

2. Create a table

Insert data
sql = "insert into student values (1, 'student1', 'male')";
result = sqlite3_exec (pdb, sql.c_str (), NULL, NULL, NULL); // 3
  if (result! = SQLITE_OK)
    log ("insert data failed!");
 
sql = "insert into student values (2, 'student2', 'female')";
result = sqlite3_exec (pdb, sql.c_str (), NULL, NULL, NULL);
if (result! = SQLITE_OK)
    log ("insert data failed!");
 
sql = "insert into student values (3, 'student3', 'male')";
result = sqlite3_exec (pdb, sql.c_str (), NULL, NULL, NULL);
if (result! = SQLITE_OK)
    log ("insert data failed!");

3. Insert 3 pieces of data into the table

Inquire
char ** re; // Query results
int r, c; // row, column
sqlite3_get_table (pdb, "select * from student", & re, & r, & c, NULL); // 4
log ("row is% d, column is% d", r, c);
 
for (int i = 1; i <= r; i ++) // 5
{
    for (int j = 0; j <c; j ++)
    {
        log ("% s", re [i * c + j]);
    }
}
sqlite3_free_table (re);

4. Query the data in the table

5. Output log of query results

search result:

cocos2d: row is 3, column is 3
cocos2d: 1
cocos2d: student1
cocos2d: male
cocos2d: 2
cocos2d: student2
cocos2d: female
cocos2d: 3
cocos2d: student3
cocos2d: male

We can see that the query results are the same as the data we inserted earlier.

delete
sql = "delete from student where ID = 1";
result = sqlite3_exec (pdb, sql.c_str (), NULL, NULL, NULL); // 6
if (result! = SQLITE_OK)
    log ("delete data failed!");

6. Delete student with ID = 1

Use the above query to query the data after deleting the student with ID = 1

search result:

cocos2d: row is 2, column is 3
cocos2d: 2
cocos2d: student2
cocos2d: female
cocos2d: 3
cocos2d: student3
cocos2d: male

We can see that the data with ID = 1 in the table has been deleted

note:
The memory management problem that must be paid attention to when using sqlite is that after opening the database, after the data operation is completed, the database must be closed, otherwise it will cause a memory leak.

sqlite3_close (pdb);

Where the data files are stored
Android:
/data/data/com.youCompany.Helloworld/files/save.db

iOS:
Located in the document directory of the program sandbox

../Documents/save.db