Sqlite 操作示例

插入数据:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
string mydb = "/DB/test.db";

void insertTest()
{
  string sql = "create table if not exists  test (id integer primary key autoincrement,name varchar(100))";
  Print("sql = "+sql+"\n");

  Sqlite sqlite;

  Print("--------------------------------\n");
  bool b = sqlite.open(mydb);
  Print("sqlite.open, b: "+b+" ,err:"+GetLastError()+"\n");

  Print("--------------------------------\n");
  sqlite.clear();
  Print("sqlite clear ok\n");

  Print("--------------------------------\n");
  b = sqlite.exec(sql);
  Print("sqlite.create table test, b: "+b+" ,err:"+GetLastError()+"\n");

  Print("--------------------------------\n");
  Print("开始插入数据:");
  for(uint i=0;i<10;i++)
  {
    sql = "insert into test (name) values ('pbj"+i+"')";
    if(sqlite.exec(sql))
        Print("insert i:"+i+" ok\n");
    else
        Print("insert i:"+i+" fail\n");
  }
  sqlite.close();
}

for遍历:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
string mydb = "/DB/test.db";

void forTest()
{
  Print("--------------------------------\n");
  string sql = "select * from test";
  Sqlite sqlite;
  bool b = sqlite.open(mydb);
  Print("sqlite.open, b: "+b+" ,err:"+GetLastError()+"\n");
  SQLITE_RES res = sqlite.query(sql);

  Print("for遍历结果集:\n");
  Print("序号\tname\n");
  for(uint i=0;i<res.rows();i++)
  {
      string str = "";
      for(uint j=0;j<res.cols();j++)
      {
        if(str.length() != 0)
            str.append("\t");
        str.append(res.getString(i,j));
      }
      Print(str+"\n");
  }
  sqlite.close();
}

顺序遍历:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
string mydb = "/DB/test.db";

void moveToNextTest()
{
  Print("--------------------------------\n");
  string sql = "select * from test";
  Sqlite sqlite;
  bool b = sqlite.open(mydb);
  Print("sqlite.open, b: "+b+" ,err:"+GetLastError()+"\n");
  SQLITE_RES res = sqlite.query(sql);

  Print("--------------------------------\n");
  Print("moveToNext顺序遍历:\n");
  Print("序号\tname\n");
  while(res.moveToNext())
  {
    string str = ""+res.getInt("id")+"\t"+res.getString("name");
    Print(str+"\n");
  }
  Print(GetLastError()+"\n");

  Print("--------------------------------\n");
  Print("res.toJson :"+res.toString()+"\n");

  Print("--------------------------------\n");
  sqlite.close();
  Print("sqlite.close\n");
}

倒序遍历:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
string mydb = "/DB/test.db";

void moveToPrevTest()
{
  Print("--------------------------------\n");
  string sql = "select * from test";
  Sqlite sqlite;
  bool b = sqlite.open(mydb);
  Print("sqlite.open, b: "+b+" ,err:"+GetLastError()+"\n");
  SQLITE_RES res = sqlite.query(sql);

  Print("--------------------------------\n");
  Print("moveToPrev倒序遍历:\n");
  Print("序号\tname\n");
  while(res.moveToPrev())
  {
     string str = "";
     for(uint j=0;j<res.cols();j++)
     {
        if(str.length() != 0)
            str.append("\t");
        str.append(res.getString(j));
     }
      Print(str+"\n");
  }
  Print(GetLastError()+"\n");
 
  sqlite.close();
}

结果集从头开始遍历:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
string mydb = "/DB/test.db";

void moveToHeadTest()
{
  Print("--------------------------------\n");
  string sql = "select * from test";
  Sqlite sqlite;
  bool b = sqlite.open(mydb);
  Print("sqlite.open, b: "+b+" ,err:"+GetLastError()+"\n");
  SQLITE_RES res = sqlite.query(sql);

  Print("--------------------------------\n");
  res.moveToHead();
  Print("moveToHead Test:\n");
  Print("序号\tname\n");
    do
    {
        string str = "";
        for(uint j=0;j<res.cols();j++)
        {
            if(str.length() != 0)
                str.append("\t");
            str.append(res.getString(j));
        }
        Print(str+"\n");
    }while(res.moveToNext());


  sqlite.close();
}

结果集从尾开始遍历:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
string mydb = "/DB/test.db";

void moveToTailTest()
{
  Print("--------------------------------\n");
  string sql = "select * from test";
  Sqlite sqlite;
  bool b = sqlite.open(mydb);
  Print("sqlite.open, b: "+b+" ,err:"+GetLastError()+"\n");
  SQLITE_RES res = sqlite.query(sql);

  Print("--------------------------------\n");
  res.moveToTail();
  Print("moveToTail Test:\n");
  Print("序号\tname\n");
    do
    {
        string str = "";
        for(uint j=0;j<res.cols();j++)
        {
            if(str.length() != 0)
                str.append("\t");
            str.append(res.getString(j));
        }
        Print(str+"\n");
    }while(res.moveToPrev());


  sqlite.close();
}

================= Demo下载 =================

Mysql 操作示例

Mysql 操作示例

插入数据: 1234567891011121 […]