(+) SQLite integration and usage

Overview

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.

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

Get ready

First create a cocos2d-x v3.x HelloWorld project, we will use this project as SQLite integration and usage of the actual project.

Open the terminal and create a new project using the following command:

In accordance with the above operation, we have created a new cocos2d-x v3.x HelloWorld project.

Ios/mac

Ios/mac's system library comes with the SQLite library, we just need to add the Libsqlite3.0.dylib library.

Android

The Android system does not have its own SQLite library, we need to add it manually.

1. Download the SQLite package

Http://www.sqlite.org/download.html: After downloading, import the sqlite3.c and sqlite3.h two files in the project.

2. Import to Project

3. Modify Android.mk

Using SQLite

Open the HelloWorldScene.cpp file and we'll add the use example of SQLite inside

Introducing 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);
    }

1. Database pointers
2. Specify the path to the database
3. Open a database, and if the database does not exist, create a database file

SQL statements

sql="create table student(ID integer primary key autoincrement,name text,sex text)";//1

1. To create a table's SQL statement

CREATE table

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

1. Create a table

Inserting data

sql="insert into student  values(1,‘student1‘,‘male‘)";
    result=sqlite3_exec(pdb,sql.c_str(),NULL,NULL,NULL);
    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!");

1. Inserting 3 data into a table

Inquire

char ** re; // Query results
     int r, c; // row, column
     sqlite3_get_table (pdb, "select * from student", & re, & r, & c, NULL); // 1
     log ("row is% d, column is% d", r, c);

     for (int i = 1; i <= r; i ++) // 2
     {
         for (int j = 0; j <c; j ++)
         {
             log ("% s", re [i * c + j]);
         }
     }
     sqlite3_free_table (re);

1. Querying data in a table
2. Log output of the query results

Query 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 has the same result as the data we inserted earlier.

Delete

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

1. Delete Id=1 's students

Use the query statement above to query the data after deleting id=1 students

Query 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 id=1 data in the table has been deleted.

Attention:

Use SQLite must pay attention to the memory management problem, that is, after opening the database, the data operation is completed, be sure to close the database, the side will cause a memory leak.

sqlite3_close(pdb);

Where the data files are stored

• Android:

/data/data/com.youCompany.Helloworld/files/save.db

• Ios:

Under the document directory in the program sandbox

../Documents/save.db

(+) SQLite integration and usage