基于sqlite3的利用数据库实现简单通讯录

最新推荐文章于 2022-03-25 20:41:39 发布

Echo_Anna

最新推荐文章于 2022-03-25 20:41:39 发布

阅读量2.8k

点赞数 1

分类专栏：数据库 Linux 文章标签：数据库通讯录 sqlite3 源码过程

Linux 同时被 2 个专栏收录

42 篇文章

订阅专栏

数据库

5 篇文章

订阅专栏

出处：http://blog.youkuaiyun.com/jarvischu/article/details/6714190

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的数据库常用操作：创建数据库、创建表、增、删、改、查等。

[cpp] view plain copy
/************************************************************************
* 名称：contact_using_sqlite3.c
* 功能：一个简单的通讯录程序，主要是为了练习sqlite3的使用
* 描述：使用sqlite3，建立数据库contact,其中有表一张
info(name varchar(10), age smallint, relation varchar(10), phone varchar(11))
包括如下功能：
1. 查看通讯录
2. 增加联系人
3. 删除联系人
4. 修改联系人信息
5. 查找联系人
* 作者：JarvisChu
* 时间：2011-8-22
* 修订：2011-8-24，完成修改和删除；
************************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>

sqlite3* db = NULL; //the database


//show the menu on the screen,and return user's choice
int showMenu(){
int choice = 0;
while(1){
printf("*****************************************************\n");
printf(" Welcome \n");
printf("1. Display All 2. Add Contact\n");
printf("3. Delete Contact 4. Alter Contact\n");
printf("5. Find Contact\n");
printf("Your choice is:");
scanf("%d",&choice);
if(choice == 1 || choice == 2 ||\
choice == 3 || choice == 4 ||\
choice == 5){
return choice;
}
else{
printf("Wrong! Again!\n");
}
}
return 0;
}

//show all records in db
void displayAll(){
int i = 0;
int j = 0;
int index = 0;
int ret = 0;
int row = 0;
int column = 0;
char* sql = NULL;
char** resultSet = NULL; //store the query result

sql = (char*)malloc(sizeof(char)*20);
strcpy(sql,"select * from info;");

ret = sqlite3_get_table(db,sql,&resultSet,&row,&column,0);
if(ret != SQLITE_OK){
fprintf(stderr,"select records err\n");
}

printf("There are %d Contact:\n",row);
index = 0;
for(i=0;i<=row;i++){
for(j=0;j<column;j++){
printf("%-11s",resultSet[index++]);
}
printf("\n");
}

sqlite3_free_table(resultSet);
free(sql);

}

//add contact
void addContact(){
int ret = 0;
char* name = NULL;
int age = 0;
char* relation = NULL;
char* phone = NULL;
char* sql = NULL;

name = (char*)malloc(sizeof(char)*10);
relation = (char*)malloc(sizeof(char)*10);
phone = (char*)malloc(sizeof(char)*12);
sql = (char*)malloc(sizeof(char)*64);

printf("input (name age relation phone):");
scanf("%s %d %s %s",name,&age,relation,phone);
//printf("%s， %d， %s，%s\n",name,age,relation,phone);


sprintf(sql,"insert into info values('%s',%d,'%s','%s');",name,age,relation,phone);
//printf("%s\n",sql);
ret = sqlite3_exec(db,sql,0,0,0);
if(ret != SQLITE_OK){
printf("failed!\n");
}
else{
printf("ok!\n");
}
free(name);
free(relation);
free(phone);
free(sql);

}

//find Contact
void findContact(){
int i,j,index;
int ret = 0;
int row = 0;
int column = 0;
char* name = NULL;
char* sql = NULL;
char** resultSet = NULL;

name = (char*)malloc(sizeof(char)*10);
printf("Input the name you want to find:");
scanf("%s",name);

sql = (char*)malloc(sizeof(char)*64);
sprintf(sql,"select * from info where name = '%s'",name);

ret = sqlite3_get_table(db,sql,&resultSet,&row,&column,0);
if(ret != SQLITE_OK){
fprintf(stderr,"select err:%s\n",sqlite3_errmsg(db));
return;
}
index = 0;
if(row>0){
for(i=0;i<=row;i++){
for(j=0;j<column;j++){
printf("%-11s",resultSet[index++]);
}
printf("\n");
}
}
else{
printf("no such person!\n");
}
}

//alertContact()
void alterContact(){
//first,find the contact info to be altered.
int i,j,index;
int ret = 0;
int row = 0;
int column = 0;
int age = 0;
char* name = NULL;
char* relation = NULL;
char* phone = NULL;
char* sql = NULL;
char** resultSet = NULL;

name = (char*)malloc(sizeof(char)*10);
printf("Input the name you want to alter:");
scanf("%s",name);

sql = (char*)malloc(sizeof(char)*128);
sprintf(sql,"select * from info where name = '%s'",name);

ret = sqlite3_get_table(db,sql,&resultSet,&row,&column,0);
if(ret != SQLITE_OK){
fprintf(stderr,"select err:%s\n",sqlite3_errmsg(db));
return;
}
index = 0;
if(row>0){
for(i=0;i<=row;i++){
for(j=0;j<column;j++){
printf("%-11s",resultSet[index++]);
}
printf("\n");
}
sqlite3_free_table(resultSet);

//exist ,then alter
relation = (char*)malloc(sizeof(char)*10);
phone = (char*)malloc(sizeof(char)*12);

printf("input the new data (age relation phone):");
scanf("%d %s %s",&age,relation,phone);
//printf(" %d， %s，%s\n",name,age,relation,phone);


sprintf(sql,"update info set age=%d,relation='%s',phone='%s' where name='%s';",age,relation,phone,name);
//printf("%s\n",sql);
ret = sqlite3_exec(db,sql,0,0,0);
if(ret != SQLITE_OK){
printf("failed!\n");
}
else{
printf("ok!\n");
}
free(relation);
free(phone);
}
else{
printf("no such person!\n");
}
free(sql);
free(name);

}

//delete Contact
void deleteContact(){
int ret = 0;
char* name = NULL;
char* sql = NULL;

name = (char*)malloc(sizeof(char)*10);
sql = (char*)malloc(sizeof(char)*64);

printf("Input the name of contact you want to delete:");
scanf("%s",name);

sprintf(sql,"delete from info where name='%s';",name);

//to be simple, there will be no warning if the contact does not exist
ret = sqlite3_exec(db,sql,0,0,0);
if(ret != SQLITE_OK){
printf("delete err:%s",sqlite3_errmsg(db));
}
else{
printf("ok!");
}

free(name);
free(sql);

}


int main()
{
int ret = 0;
int choice = 0;
int ch = 0;
char* errmsg = NULL;
char* sql = NULL;

//open the db if exist or create it
ret = sqlite3_open("contact.db",&db);
if(ret){
fprintf(stderr,"Cannot open database:%s\n",sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
else{
printf("Open database successfully...\n");
}

//create the table info if not exists
sql = (char*)malloc(sizeof(char)*128);//
//strcpy(sql,);
//printf("Copy sql successfully\n");
ret = sqlite3_exec(db,"create table if not exists info(\
name varchar(10) primary key, \
age smallint, \
relation varchar(10), \
phone varchar(11));",0,0,&errmsg);
if(ret != SQLITE_OK){
//printf("Create table error\n");
fprintf(stderr,"Create table err:%s\n",sqlite3_errmsg(db));
}
//printf("Create table successfully\n");

//insert some initial records,
//it will cause a err if not the frist time,but that does not matter
strcpy(sql,"insert into info values('zhu',22,'本人','15109217871');");

ret = sqlite3_exec(db,sql,0,0,&errmsg);
if(ret != SQLITE_OK){
fprintf(stderr,"Insert record err:%s\n",sqlite3_errmsg(db));
}
free(sql);
//printf("Insert record successfully\n");

//main menu
while(1){
choice = showMenu(); //show the menu and get the user's choose
switch(choice){
case 1:
displayAll(); //show all records in db
break;
case 2:
addContact();
break;
case 3:
deleteContact();
break;
case 4:
alterContact();
break;
case 5:
findContact();
break;
default:
break;
}

//if back to main menu or not
printf("\nBack to Menu 1(yes) / 0(no)?");
scanf("%d",&ch);
if(ch == 0){
break;
}
//printf("\33[2J");
system("clear");
}

sqlite3_close(db);
return 0;
}