Chinaunix首页 | 论坛 | 博客

草木飞飞gsw.blog.chinaunix.net

首页　| 　博文目录　| 　关于我

zxenv

  • 博客访问： 283530
  • 博文数量： 59
  • 博客积分： 235
  • 博客等级： 二等列兵
  • 技术积分： 409
  • 用 户 组： 普通用户
  • 注册时间： 2012-01-12 15:11
文章分类

全部博文（59）

文章存档

2013年（4）

2012年（55）

我的朋友
最近访客
推荐博文
相关博文
android 的sqlite 简单操作实例

分类： SQLite/嵌入式数据库

2013-01-09 21:02:55

 
 
在android 系统中，Sqlite数据库可以用来存储数据，在c层和android层都分别有接口来对数据库文件进行增删改查的操作，如果灵活运用会解决一些问题。
    例如 可在android层利用contentprovider对数据库进行增删改查，在c层也对此数据库进行增删改查，通常c层操作在contentprovider未运行起来之前操作，数据库文件保存在单独的一个flash分区内，系统recovery时不擦除此分区，数据库文件就可以保存下来，如此，可以提高用户的体验效果。
本文主要记录这一思想，并且附上c操作的简单代码例子。

点击(此处)折叠或打开

  1. #include <stdio.h>
  2. #include <string.h>
  3. #include "sqlite3.h"
  4. #include <stdlib.h>

  6. #define CHARLTH 128
  7. #define SQLTH 256
  8. #define DB_PATH "/data/Mydb/"
  9. #define DB_NAME "My_db"
  10. #define ESC 27
  11. #define DBCREATE "create table My(id integer primary key autoincrement,MyName text,MyValues text,DefaultValues text ,BeDelete int,BeDefault int);"

  13. int main(){

  15.     char ch;
  16.     void toast();
  17.     int query( const char *key,char *val);
  18.     int update( const char *name,char *value);
  19.     int add(const char *MyName,const char *val,const int flgdlte,const int flagrevert);
  20.     int delete( const char *key);
  21.     toast();


  24.     while((ch=getchar()) != ESC){

  26.         int i=-2;
  27.         char MyName[CHARLTH];
  28.         memset(MyName,0,sizeof(MyName));
  29.         char val[CHARLTH];
  30.         memset(val,0,sizeof(val));
  31.         int flgdle;
  32.         int flagrevert;

  34.         switch(ch){
  35.             case 'a':
  36.                 printf("input a to addvalue ,scanf MyName! \n");
  37.                 scanf("%s",MyName);
  38.                 printf("input MyName:%s,then scanf MyValue!\n",MyName);
  39.                 scanf("%s",val);
  40.                 printf("input MyValue:%s,then scanf deleteflag 1 can delete,0 can't delete.\n",val);
  41.                 scanf("%d",&flgdle);
  42.                 printf("input DeleteFlag is: %d,scanf revertOnboot 1 can revert, 0 not revert. \n",flgdle);
  43.                 if( (1 !=flgdle) &&(0 !=flgdle)){
  44.                     printf("No valid input , Exit!\n");
  45.                     exit(0);
  46.                 }
  47.                 scanf("%d",&flagrevert);
  48.                 printf("input revertOnbootFlag:%d\n",flagrevert);
  49.                 if((1 !=flagrevert) && (0 !=flagrevert)){
  50.                     printf("No valid input , Exit!\n");
  51.                     exit(0);
  52.                 }
  53.                 i = add((char*)MyName,(char*)val,flgdle,flagrevert);
  54.                 if(0 == i){
  55.                     printf("add success!\n");
  56.                 }else {
  57.                     printf("add error\n");
  58.                 }

  60.                 break;

  62.             case 'd':
  63.                 printf("input d to delete ,scanf MyName. \n");
  64.                 scanf("%s",MyName);
  65.                 printf("the input MyName:%s\n",MyName);
  66.                 i = delete((char*)MyName);
  67.                 if(0 == i){
  68.                     printf("delete success!\n");
  69.                 }else{
  70.                     printf("delete error!\n");
  71.                 }
  72.                 break;

  74.             case 'm':
  75.                 printf("input m to modify MyValue ,scanf MyName.\n");
  76.                 scanf("%s",MyName);
  77.                 printf("the input MyName:%s scanf New value\n",MyName);
  78.                 scanf("%s",val);
  79.                 printf("the input MyValue:%s\n",val);
  80.                 i = update((char*)MyName,(char*)val);
  81.                 if(0 == i){
  82.                     printf("modify success!\n");
  83.                 }else {
  84.                     printf("modify error\n");
  85.                 }

  87.                 break;

  89.             case 'q':
  90.                 printf("input q to querry MyValue,scanf MyName.\n");
  91.                 scanf("%s",MyName);

  93.                 //            printf("main::: scanf::: %s\n",MyName); // test scanf right

  95.                 i = query((char*)MyName,val);
  96.                 if(0 == i){
  97.                     printf("%s:::%s\n",MyName,val);
  98.                 }else {
  99.                     printf("error\n");
  100.                 }
  101.                 break;

  103.             case 'h':
  104.                 printf("input h to get help\n");
  105.                 toast();
  106.                 break;

  108.             default:
  109.                 //printf("No valid input!\n");
  110.                 break;
  111.         }


  114.     }
  115.            return 0;
  116. }


  119. void toast(){
  120.     printf("****************************************************\n\n");
  121.     printf("Use MyClient to add,delete,modify,query value!\n\n");
  122.     printf("****************************************************\n\n");
  123.     printf("input \n a to add value \n d to delete value \n m to modify value \n q to query value \n ESC to queit \n h to get help\n");
  124.     printf("****************************************************\n\n");

  126. }



  130. int add(const char *MyName,const char *val,const int flgdlte,const int flagrevert){

  132.     int rt = 0;
  133.     sqlite3 *db = NULL ;
  134.     char *zErrMsg = 0;
  135.     char *values = NULL;
  136.     char *db_begin= "BEGIN TRANSACTION;";
  137.     char *db_end = "COMMIT TRANSACTION;";

  139.     int i = 0;
  140.     int nRow = 0;
  141.     int nColumn = 0;
  142.     char** dbResult = NULL;
  143.     int rc = 0;
  144.     char sql[SQLTH];
  145.     char My_name[CHARLTH];
  146.     char My_values[CHARLTH];
  147.     int flag_dlte;
  148.     int flag_revert;
  149.     char db_path[SQLTH];


  152.     memset(My_name,0,sizeof(My_name));
  153.     memset(My_values,0,sizeof(My_values));
  154.     strcpy(My_name,MyName);
  155.     strcpy(My_values,val);

  157.     flag_dlte = flgdlte;
  158.     flag_revert = flagrevert;



  162.     memset(sql,0,sizeof(sql));
  163.     sprintf(db_path,"%s%s",DB_PATH,DB_NAME);
  164.     //rc = sqlite3_open_v2(db_path,&db,SQLITE_OPEN_READWRITE,NULL);
  165.     rc = sqlite3_open(db_path,&db);
  166.     if( rc ) {
  167.         printf("open error ,can't open database\n");
  168.         fprintf(stderr,"Can't open databases : %s\n",sqlite3_errmsg(db));
  169.         rt = -1;
  170.         sqlite3_close(db);
  171.         return rt;
  172.     }

  174.     rc = sqlite3_exec(db,db_begin,NULL,NULL,NULL);
  175.     sprintf(sql,"select *from My where MyName = '%s';",My_name);
  176.     rc = sqlite3_exec(db,DBCREATE,NULL,NULL,NULL);
  177.         if(SQLITE_OK != rc){
  178.            // sqlite3_close(db);
  179.            // sqlite3_free(errmsg);
  180.            printf("create db table error \n");
  181.             rt = -1;
  182.     }
  183.     rc = sqlite3_get_table(db,sql,&dbResult,&nRow,&nColumn,&zErrMsg);
  184.     if (0 == nRow){
  185.         rt = -1;
  186.         //sqlite3_free_table(dbResult);
  187.         //sqlite3_close(db);
  188.         //return rt;
  189.         memset(sql,0,sizeof(sql));
  190.         sprintf(sql,"insert into My(MyName,MyValues,DefaultValues,BeDelete,BeDefault)values \
  191.                 ('%s','%s','%s','%d','%d');",My_name,My_values,My_values,flag_dlte,flag_revert);
  192.         rc = sqlite3_exec(db,sql,NULL,NULL,NULL);
  193.         if(0 != rc){
  194.             printf("My add value Error!\n");
  195.             rt = -1;
  196.         }else{
  197.             rt = 0;
  198.         }
  199.     }else{
  200.         // not 0 == nRow the Myname has been stored in My.

  202.     }
  203.     sqlite3_free_table(dbResult);
  204.     rc = sqlite3_exec(db,db_end,NULL,NULL,NULL);
  205.     sqlite3_close(db);

  207.     return rt;

  209. }


  212. int update( const char *key,char *val){
  213.     int rt = 0;
  214.     sqlite3 *db = NULL ;
  215.     char *zErrMsg = 0;
  216.     char *values = NULL;
  217.     char *db_begin= "BEGIN TRANSACTION;";
  218.     char *db_end = "COMMIT TRANSACTION;";

  220.     int i = 0;
  221.     int nRow = 0;
  222.     int nColumn = 0;
  223.     char** dbResult = NULL;
  224.     int rc = 0;
  225.     char sql[SQLTH];
  226.     char My_values[CHARLTH];
  227.     char db_path[SQLTH];
  228.     char MyName[CHARLTH];

  230.     memset(sql,0,sizeof(sql));
  231.     memset(MyName,0,sizeof(MyName));
  232.     memset(My_values,0,sizeof(My_values));
  233.     memset(db_path,0,sizeof(db_path));

  235.     if(NULL==key || NULL==val){
  236.         rt = -1;
  237.         return rt;
  238.     }

  240.     sprintf(db_path,"%s%s",DB_PATH,DB_NAME);
  241.     //    printf("My db_path::::::::%s\n",db_path);

  243.     rc = sqlite3_open_v2(db_path,&db,SQLITE_OPEN_READWRITE,NULL);

  245.     if( rc ) {
  246.         fprintf(stderr,"Can't open databases : %s\n",sqlite3_errmsg(db));
  247.         rt = -1;
  248.         sqlite3_close(db);
  249.         return rt;
  250.     }

  252.     rc = sqlite3_exec(db,db_begin,NULL,NULL,NULL);

  254.     strcpy(MyName,key);
  255.     strcpy(My_values,val);
  256.     //    printf("query::::MyName:::%s\n",MyName); // test go to query is right or not

  258.     sprintf(sql,"select *from My where MyName = '%s';",MyName);
  259.     rc = sqlite3_get_table(db,sql,&dbResult,&nRow,&nColumn,&zErrMsg);
  260.     if (0 == nRow){
  261.         rt = -1;
  262.         sqlite3_free_table(dbResult);
  263.         sqlite3_close(db);
  264.         return rt;
  265.     }

  267.     sprintf(sql,"update My set MyValues= '%s' where MyName = '%s';",My_values,MyName);

  269.     //printf("update sql:%s\n",sql);
  270.     rc = sqlite3_exec(db,sql,NULL,NULL,NULL);
  271.        //printf("update rc sql %d\n",rc);
  272.     if(0 != rc){
  273.         rt = -1;
  274.         sqlite3_close(db);
  275.         return rt;

  277.     }

  279.     rc = sqlite3_exec(db,db_end,NULL,NULL,NULL);
  280.     sqlite3_close(db);
  281.     if(0 != rc){
  282.         rt = -1;
  283.     }

  285.     return rt;


  288. }



  292. int delete( const char *key){
  293.     int rt = 0;
  294.     sqlite3 *db = NULL ;
  295.     char *zErrMsg = 0;
  296.     //    char *values = NULL;
  297.     char *db_begin= "BEGIN TRANSACTION;";
  298.     char *db_end = "COMMIT TRANSACTION;";


  301.     int nRow = 0;
  302.     int nColumn = 0;

  304.     int i = 0;
  305.     char** dbResult = NULL;
  306.     int rc = 0;
  307.     char sql[SQLTH];
  308.     char db_path[SQLTH];
  309.     char MyName[CHARLTH];

  311.     memset(sql,0,sizeof(sql));
  312.     memset(MyName,0,sizeof(MyName));
  313.     memset(db_path,0,sizeof(db_path));

  315.     if(NULL==key ){
  316.         rt = -1;
  317.         return rt;
  318.     }


  321.     sprintf(db_path,"%s%s",DB_PATH,DB_NAME);
  322.     //    printf("My db_path::::::::%s\n",db_path);

  324.     rc = sqlite3_open_v2(db_path,&db,SQLITE_OPEN_READWRITE,NULL);

  326.     if( rc ) {
  327.         fprintf(stderr,"Can't open databases : %s\n",sqlite3_errmsg(db));
  328.         rt = -1;
  329.         sqlite3_close(db);
  330.         return rt;
  331.     }

  333.     rc = sqlite3_exec(db,db_begin,NULL,NULL,NULL);

  335.     strcpy(MyName,key);

  337.     //printf("query::::MyName:::%s\n",MyName); // test go to query is right or not

  339.     sprintf(sql,"select *from My where MyName = '%s';",MyName);
  340.     rc = sqlite3_get_table(db,sql,&dbResult,&nRow,&nColumn,&zErrMsg);

  342.     if (0 == nRow){
  343.         rt = -1;
  344.         sqlite3_free_table(dbResult);
  345.         sqlite3_close(db);
  346.         return rt;
  347.     }

  349.     char    *values = *(&dbResult[nColumn]+4);

  351.     printf(" values delete flag = %s\n",values);
  352.     //printf(" *values delete flag =%c\n",*values);

  354.     int deleteflag = atoi(values);
  355.     //printf(" delete flag values atoi %d\n",deleteflag);
  356.     // deleteflag = atoi((const char *)(*values));
  357.     // printf("delete flag *values atoi %d\n",deleteflag);

  359.     if(1 == deleteflag){
  360.          //printf("come to delete \n.");
  361.         memset(sql,0,sizeof(sql));
  362.         sprintf(sql,"delete from My where MyName = '%s';",MyName);
  363.         //    printf(" sql =%s\n",sql);
  364.         rc = sqlite3_exec(db,sql,NULL,NULL,&zErrMsg);

  366.         if (0 != rc){
  367.             rt = -1;
  368.         }else{
  369.             rt = 0;
  370.         }

  372.     }else{
  373.         rt = -1;
  374.     }
  375.     sqlite3_free_table(dbResult);
  376.     rc = sqlite3_exec(db,db_end,NULL,NULL,NULL);
  377.     sqlite3_close(db);

  379.     return rt;

  381. }


  384. int query( const char *key,char *val){
  385.     int rt = 0;
  386.     sqlite3 *db = NULL ;
  387.     char *zErrMsg = 0;
  388.     char *values = NULL;
  389.     char *db_begin= "BEGIN TRANSACTION;";
  390.     char *db_end = "COMMIT TRANSACTION;";

  392.     int i = 0;
  393.     int nRow = 0;
  394.     int nColumn = 0;
  395.     char** dbResult = NULL;
  396.     int rc = 0;
  397.     char sql[SQLTH];
  398.     char My_values[CHARLTH];
  399.     char db_path[SQLTH];
  400.     char MyName[CHARLTH];

  402.     memset(sql,0,sizeof(sql));
  403.     memset(MyName,0,sizeof(MyName));
  404.     memset(My_values,0,sizeof(My_values));
  405.     memset(db_path,0,sizeof(db_path));

  407.     if(NULL==key || NULL==val){
  408.         rt = -1;
  409.         return rt;
  410.     }

  412.     sprintf(db_path,"%s%s",DB_PATH,DB_NAME);
  413.     //printf("My db_path::::::::%s\n",db_path);

  415.     rc = sqlite3_open_v2(db_path,&db,SQLITE_OPEN_READONLY,NULL);

  417.     if( rc ) {
  418.         fprintf(stderr,"Can't open databases : %s\n",sqlite3_errmsg(db));
  419.         rt = -1;
  420.         sqlite3_close(db);
  421.         return rt;
  422.     }

  424.     rc = sqlite3_exec(db,db_begin,NULL,NULL,NULL);

  426.     strcpy(MyName,key);

  428.     //    printf("query::::MyName:::%s\n",MyName); // test go to query is right or not

  430.     sprintf(sql,"select *from My where MyName = '%s';",MyName);
  431.     rc = sqlite3_get_table(db,sql,&dbResult,&nRow,&nColumn,&zErrMsg);

  433.     if (0 == nRow){
  434.         rt = -1;
  435.         sqlite3_free_table(dbResult);
  436.         sqlite3_close(db);
  437.         return rt;
  438.     }

  440.     values = *(&dbResult[nColumn]+2);

  442.     while( *values){
  443.         My_values[i] = *values;
  444.         i++;
  445.         values++;
  446.     }
  447.     My_values[i] = '\0';

  449.     //    printf("dbquery::My_values :%s\n",My_values); //test

  451.     memset(val,0,sizeof(val));
  452.     strcpy(val,My_values);

  454.     //    printf("db_query::val::%s\n",val);

  456.     sqlite3_free_table(dbResult);
  457.     rc = sqlite3_exec(db,db_end,NULL,NULL,NULL);
  458.     sqlite3_close(db);
  459.     rt = 0;

  461.     return rt;


  464. }

   
阅读(6528) | 评论(0) | 转发(0) |
0

上一篇：10步让你成为更优秀的程序员

下一篇：Ubuntu 10.10 下安装 apache记录

给主人留下些什么吧！~~
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6