Use of SQlite

In CrossApp, simple data storage, using CAUserDefault. S o how do you store large amounts of irregular data? W e can use the SQL database to store data. SQLite is a very widely used embedded database that is compact, efficient, cross-platform, open source free, and easy to operate.

The SQLite database is written in C and is very easy to use in CrossApp.

CrossApp has added SQlite, and in the CrossApp,"extensions\sqlite3 directory, I'll use it directly.

Introduce a header file

#include "CrossAppExt.h"

Create a database

//数据库指针
sqlite3 *pdb=NULL;
 
//保存数据库的路径
std::string path= CCFileUtils::sharedFileUtils()->getWritablePath()+"save.db";
 
std::string sql;
int result;
 
//打开一个数据，如果该数据库不存在，则创建一个新的数据库文件
result=sqlite3_open(path.c_str(),&pdb);
 
if(result!=SQLITE_OK)
{
    CCLog("open database failed,  number%d",result);
}

SQL statement

Create a sql statement for the database table

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

Create a Talbe

//创建表格
result=sqlite3_exec(pdb,sql.c_str(),NULL,NULL,NULL);
 
if(result!=SQLITE_OK)
    CCLog("create table failed");

Insert

//向表内插入3条数据
sql="insert into student  values(1,'student1','male')";
 
result=sqlite3_exec(pdb,sql.c_str(),NULL,NULL,NULL);
 
if(result!=SQLITE_OK)
   CCLog("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)
    CCLog("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)
    CCLog("insert data failed!");

Inquire

//查询结果
char **re;
 
//行、列
int r,c; 
 
//查询数据
sqlite3_get_table(pdb,"select * from student",&re,&r,&c,NULL);
 
CCLog("row is %d,column is %d",r,c);
 
//将查询出的数据通过log输出
for(int i=1;i<=r;i++)
{
    for(int j=0;j<c;j++)
    {
        CCLog("%s",re[i*c+j]);
    }
}
 
sqlite3_free_table(re);

Delete

sql="delete from student where ID=1";
 
//删除id=1的学生的信息
result=sqlite3_exec(pdb,sql.c_str(), NULL,NULL,NULL);
 
if(result!=SQLITE_OK)
    CCLog("delete data failed!");

Attention
Use sqlite must pay attention to the memory management problem, that is, after opening the database, after the data operation is complete, be sure to close the database, whether the side will cause memory leakage.

sqlite3_close(pdb);

SQlite saves the path

Android:

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

Ios:

Located in the document directory of the program sandbox

../Documents/save.db