基于sqlite3实现的简单通讯录程序（控制台方法实例教程



基于sqlite3实现的简单通讯录程序（控制台方法实例教程。1. sqlite3 安装
1.1. 下载sqlite3源码    www.sqlite3.org    下载 sqlite-autoconf-3070701.tar.gz
1.2. 解压    将下载的 sqlite-autoconf-3070701.tar.gz 解压，得到sqlite-autoconf-3070701 文件夹
1.3. 编译源码(参照解压文件夹下的install文件)    $ cd sqlite-autoconf-3070701 #进入文件夹    $ ./configure    $ make    $ sudo make install          #注意一定要root权限
1.4. 查看安装情况    在/usr/local/include 下：sqlite3.h sqlite3ext.h    在/usr/local/bin     下：sqlite3    在/usr/local/lib      下：pkgconfig（文件夹） libsqlite3.a  libsqlite3.la                                           libsqlite3.so  libsqlite3.so.0 libsqlite3.so.0.8.6
2. sqlite3 c编程 之 环境搭建(Codeblocks)
2.1. 新建 c project    打开codeblocks,File–> new –> project–>console application–>c
2.2. 引入库    project–>build options –> linker setting    在linker library 下点击 add， 选择 /usr/local/lib/libsqlite3.so  文件
2.3. 添加.h文件    在程序开头#include <sqlite3.h>
2.4. 至此环境搭建完成。    在程序中，就可以使用sqlite3的c语言接口了    如：sqlite3_open()

sqlite3_close()

 

3. 通讯录程序

一个简单的通讯录程序，主要是为了练手，熟悉Slqite3的使用。

数据库：contact.db，只有一张表info，保存联系人信息（姓名，年龄，关系，手机号）

程序中bug较多，但却涵盖了sqlite3的数据库常用操作：创建数据库、创建表、增、删、改、查等。

 

  1. /************************************************************************
  2. * 名  称：contact_using_sqlite3.c
  3. * 功  能：一个简单的通讯录程序，主要是为了练习sqlite3的使用
  4. * 描  述：使用sqlite3，建立数据库contact,其中有表一张
  5.           info(name varchar(10), age smallint, relation varchar(10), phone varchar(11))
  6.           包括如下功能：
  7.              1. 查看通讯录
  8.              2. 增加联系人
  9.              3. 删除联系人
  10.              4. 修改联系人信息
  11.              5. 查找联系人
  12. * 作  者：JarvisChu
  13. * 时  间：2011-8-22
  14. * 修  订：2011-8-24，完成修改和删除；
  15. ************************************************************************/
  16. #include <stdio.h>
  17. #include <stdlib.h>
  18. #include <string.h>
  19. #include <sqlite3.h>
  21. sqlite3* db = NULL;               //the database
  24. //show the menu on the screen,and return user’s choice
  25. int showMenu(){
  26.     int choice = 0;
  27.     while(1){
  28.         printf(“*****************************************************\n”);
  29.         printf(“                      Welcome                        \n”);
  30.         printf(“1. Display All          2. Add   Contact\n”);
  31.         printf(“3. Delete  Contact      4. Alter Contact\n”);
  32.         printf(“5. Find    Contact\n”);
  33.         printf(“Your choice is:”);
  34.         scanf(“%d”,&choice);
  35.         if(choice == 1 || choice == 2 ||\
  36.            choice == 3 || choice == 4 ||\
  37.            choice == 5){
  38.             return choice;
  39.         }
  40.         else{
  41.             printf(“Wrong! Again!\n”);
  42.         }
  43.     }
  44.     return 0;
  45. }
  47. //show all records in db
  48. void displayAll(){
  49.     int i = 0;
  50.     int j = 0;
  51.     int index = 0;
  52.     int ret = 0;
  53.     int row = 0;
  54.     int column = 0;
  55.     char* sql = NULL;
  56.     char** resultSet = NULL;     //store the query result
  58.     sql = (char*)malloc(sizeof(char)*20);
  59.     strcpy(sql,”select * from info;”);
  61.     ret = sqlite3_get_table(db,sql,&resultSet,&row,&column,0);
  62.     if(ret != SQLITE_OK){
  63.         fprintf(stderr,”select records err\n”);
  64.     }
  66.     printf(“There are %d Contact:\n”,row);
  67.     index = 0;
  68.     for(i=0;i<=row;i++){
  69.         for(j=0;j<column;j++){
  70.             printf(“%-11s”,resultSet[index++]);
  71.         }
  72.         printf(“\n”);
  73.     }
  75.     sqlite3_free_table(resultSet);
  76.     free(sql);
  78. }
  80. //add contact
  81. void addContact(){
  82.     int ret = 0;
  83.     char* name = NULL;
  84.     int age = 0;
  85.     char* relation = NULL;
  86.     char* phone = NULL;
  87.     char* sql = NULL;
  89.     name = (char*)malloc(sizeof(char)*10);
  90.     relation = (char*)malloc(sizeof(char)*10);
  91.     phone = (char*)malloc(sizeof(char)*12);
  92.     sql = (char*)malloc(sizeof(char)*64);
  94.     printf(“input (name age relation phone):”);
  95.     scanf(“%s %d %s %s”,name,&age,relation,phone);
  96.     //printf(“%s， %d， %s，%s\n”,name,age,relation,phone);
  99.     sprintf(sql,”insert into info values(‘%s’,%d,’%s’,'%s’);”,name,age,relation,phone);
  100.     //printf(“%s\n”,sql);
  101.     ret = sqlite3_exec(db,sql,0,0,0);
  102.     if(ret != SQLITE_OK){
  103.         printf(“failed!\n”);
  104.     }
  105.     else{
  106.         printf(“ok!\n”);
  107.     }
  108.     free(name);
  109.     free(relation);
  110.     free(phone);
  111.     free(sql);
  113. }
  115. //find Contact
  116. void findContact(){
  117.     int i,j,index;
  118.     int ret  = 0;
  119.     int row = 0;
  120.     int column = 0;
  121.     char* name = NULL;
  122.     char* sql = NULL;
  123.     char** resultSet = NULL;
  125.     name = (char*)malloc(sizeof(char)*10);
  126.     printf(“Input the name you want to find:”);
  127.     scanf(“%s”,name);
  129.     sql = (char*)malloc(sizeof(char)*64);
  130.     sprintf(sql,”select * from info where name = ‘%s’”,name);
  132.     ret = sqlite3_get_table(db,sql,&resultSet,&row,&column,0);
  133.     if(ret != SQLITE_OK){
  134.         fprintf(stderr,”select err:%s\n”,sqlite3_errmsg(db));
  135.         return;
  136.     }
  137.     index = 0;
  138.     if(row>0){
  139.         for(i=0;i<=row;i++){
  140.             for(j=0;j<column;j++){
  141.                 printf(“%-11s”,resultSet[index++]);
  142.             }
  143.             printf(“\n”);
  144.         }
  145.     }
  146.     else{
  147.         printf(“no such person!\n”);
  148.     }
  149. }
  151. //alertContact()
  152. void alterContact(){
  153.     //first,find the contact info to be altered.
  154.     int i,j,index;
  155.     int ret  = 0;
  156.     int row = 0;
  157.     int column = 0;
  158.     int age = 0;
  159.     char* name = NULL;
  160.     char* relation = NULL;
  161.     char* phone = NULL;
  162.     char* sql = NULL;
  163.     char** resultSet = NULL;
  165.     name = (char*)malloc(sizeof(char)*10);
  166.     printf(“Input the name you want to alter:”);
  167.     scanf(“%s”,name);
  169.     sql = (char*)malloc(sizeof(char)*128);
  170.     sprintf(sql,”select * from info where name = ‘%s’”,name);
  172.     ret = sqlite3_get_table(db,sql,&resultSet,&row,&column,0);
  173.     if(ret != SQLITE_OK){
  174.         fprintf(stderr,”select err:%s\n”,sqlite3_errmsg(db));
  175.         return;
  176.     }
  177.     index = 0;
  178.     if(row>0){
  179.         for(i=0;i<=row;i++){
  180.             for(j=0;j<column;j++){
  181.                 printf(“%-11s”,resultSet[index++]);
  182.             }
  183.             printf(“\n”);
  184.         }
  185.         sqlite3_free_table(resultSet);
  187.         //exist ,then alter
  188.         relation = (char*)malloc(sizeof(char)*10);
  189.         phone = (char*)malloc(sizeof(char)*12);
  191.         printf(“input the new data (age relation phone):”);
  192.         scanf(“%d %s %s”,&age,relation,phone);
  193.         //printf(” %d， %s，%s\n”,name,age,relation,phone);
  196.         sprintf(sql,”update info set age=%d,relation=’%s’,phone=’%s’ where name=’%s’;”,age,relation,phone,name);
  197.         //printf(“%s\n”,sql);
  198.         ret = sqlite3_exec(db,sql,0,0,0);
  199.         if(ret != SQLITE_OK){
  200.             printf(“failed!\n”);
  201.         }
  202.         else{
  203.             printf(“ok!\n”);
  204.         }
  205.         free(relation);
  206.         free(phone);
  207.     }
  208.     else{
  209.         printf(“no such person!\n”);
  210.     }
  211.     free(sql);
  212.     free(name);
  214. }
  216. //delete Contact
  217. void deleteContact(){
  218.     int ret = 0;
  219.     char* name = NULL;
  220.     char* sql = NULL;
  222.     name = (char*)malloc(sizeof(char)*10);
  223.     sql = (char*)malloc(sizeof(char)*64);
  225.     printf(“Input the name of contact you want to delete:”);
  226.     scanf(“%s”,name);
  228.     sprintf(sql,”delete from info where name=’%s’;”,name);
  230.     //to be simple, there will be no warning if the contact does not exist
  231.     ret = sqlite3_exec(db,sql,0,0,0);
  232.     if(ret != SQLITE_OK){
  233.         printf(“delete err:%s”,sqlite3_errmsg(db));
  234.     }
  235.     else{
  236.         printf(“ok!”);
  237.     }
  239.     free(name);
  240.     free(sql);
  242. }
  245. int main()
  246. {
  247.     int ret = 0;
  248.     int choice = 0;
  249.     int ch = 0;
  250.     char* errmsg = NULL;
  251.     char* sql = NULL;
  253.     //open the db if exist or create it
  254.     ret = sqlite3_open(“contact.db”,&db);
  255.     if(ret){
  256.         fprintf(stderr,”Cannot open database:%s\n”,sqlite3_errmsg(db));
  257.         sqlite3_close(db);
  258.         exit(1);
  259.     }
  260.     else{
  261.         printf(“Open database successfully…\n”);
  262.     }
  264.     //create the table info if not exists
  265.     sql = (char*)malloc(sizeof(char)*128);//
  266.     //strcpy(sql,);
  267.     //printf(“Copy sql successfully\n”);
  268.     ret = sqlite3_exec(db,”create table if not exists info(\
  269.                     name varchar(10) primary key, \
  270.                     age smallint, \
  271.                     relation varchar(10), \
  272.                     phone varchar(11));”,0,0,&errmsg);
  273.     if(ret != SQLITE_OK){
  274.         //printf(“Create table error\n”);
  275.         fprintf(stderr,”Create table err:%s\n”,sqlite3_errmsg(db));
  276.     }
  277.     //printf(“Create table successfully\n”);
  279.     //insert some initial records,
  280.     //it will cause a err if not the frist time,but that does not matter
  281.     strcpy(sql,”insert into info values(‘zhu’,22,’本人’,’15109217871′);”);
  283.     ret = sqlite3_exec(db,sql,0,0,&errmsg);
  284.     if(ret != SQLITE_OK){
  285.         fprintf(stderr,”Insert record err:%s\n”,sqlite3_errmsg(db));
  286.     }
  287.     free(sql);
  288.     //printf(“Insert  record successfully\n”);
  290.     //main menu
  291.     while(1){
  292.         choice = showMenu();                        //show the menu and get the user’s choose
  293.         switch(choice){
  294.             case 1:
  295.                 displayAll();                       //show all records in db
  296.                 break;
  297.             case 2:
  298.                 addContact();
  299.                 break;
  300.             case 3:
  301.                 deleteContact();
  302.                 break;
  303.             case 4:
  304.                 alterContact();
  305.                 break;
  306.             case 5:
  307.                 findContact();
  308.                 break;
  309.             default:
  310.                 break;
  311.         }
  313.         //if back to main menu or not
  314.         printf(“\nBack to Menu 1(yes) / 0(no)?”);
  315.         scanf(“%d”,&ch);
  316.         if(ch == 0){
  317.             break;
  318.         }
  319.         //printf(“\33[2J”);
  320.         system(“clear”);
  321.     }
  323.     sqlite3_close(db);
  324.     return 0;
  325. }