sqlite数据库通过网络通信的TCP框架进行交互的简单示例代码

实现的功能

  1. 创建一个名为"student.db"的数据库，通过网络通信的TCP框架，实现在客户端发出增(insert) 删(delete) 改(update) 查(query) 的请求，增删改查整个的操作是在服务器端实现的，在客户端发出增删改查的请求，在服务器端返回增删改查的结果给客户端，然后在客户端显示。

  2. 要求在以上网络编程，数据库的基础上加上并发的操作，完成单服务器对多个客户端的支持。

此代码只是在本地进行测试的代码，如有不同需求，更改所需的网络端口号和ip地址即可。

代码实现

  1. 服务端代码 server.c

为实现并发的操作，在服务端接收到客户端的连接请求后进行子进程的创建，创建子进程后在子进程中关闭监听套接字，只处理客户端的请求，父进程关闭客户端套接字，继续监听新的客户端连接请求。

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>
#include <unistd.h>
#include <sys/socket.h>
#include <netinet/in.h>
#include <arpa/inet.h>

#define DATABASE "student.db"
#define N 128
#define M 1024
#define PORT 8888
#define MAX_CLIENTS 10

//函数声明
int client_handle(int client_socket, sqlite3 *db);
int do_insert(sqlite3 *db, char *request);
int do_delete(sqlite3 *db, char *request);
int do_update(sqlite3 *db, char *request);
int do_query(sqlite3 *db, char *request, char *response_buffer);
int callback(void *arg, int f_num, char **f_value, char **f_name);
int do_query1(sqlite3 *db, char *request, char *response_buffer);

int main(int argc, const char *argv[])
{
	int server_socket, client_socket;
	struct sockaddr_in server_address, client_address;
	socklen_t address_len = sizeof(client_address);
	sqlite3 *db;
	char *errmsg;

	//创建数据库连接
	if (sqlite3_open(DATABASE, &db) != SQLITE_OK){
		printf("%s\n", sqlite3_errmsg(db));
		return -1;
	}else{
		printf("Open DATABASE success.\n");
	}

	//创建表格
	if(sqlite3_exec(db, "create table if not exists stu(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, sex TEXT, score INTEGER);",

				NULL, NULL, &errmsg) != SQLITE_OK)
	{
		printf("%s\n", errmsg);
	}
	else
	{
		printf("Create or open table success.\n");
	}

	//创建服务器套接字
	if ((server_socket = socket(AF_INET, SOCK_STREAM, 0)) < 0){
		perror("socket creation failed:\n");
		exit(0);
	}

	//设置通信地址结构体
	server_address.sin_family = AF_INET;
	server_address.sin_port = htons(PORT);
	server_address.sin_addr.s_addr = htons(0);

	// 地址快速重用
	int flag = 1;
	if (setsockopt(server_socket, SOL_SOCKET, SO_REUSEADDR, &flag, sizeof(int)) < 0){
		perror("setsockopt failed.\n");
		exit(0);
	}

	//绑定套接字
	if ( (bind(server_socket, (struct sockaddr *)&server_address, sizeof(server_address))) < 0){
		perror("Bind failed: \n");
		exit(0);
	}

	//监听连接请求
	if (listen(server_socket, MAX_CLIENTS) < 0){
		perror("Listen failed: \n");
		exit(0);		
	}

	//接收并处理客户端连接请求
	while (1){
		//接收客户端连接请求
		if ( (client_socket = accept(server_socket, (struct sockaddr *)&client_address, &address_len)) < 0){
			perror("Accept failed: \n");
			exit(0);
		}

		printf("[%s:%d] connection success.\n", inet_ntoa(client_address.sin_addr), ntohs(client_address.sin_port));

		// 创建子进程处理客户端请求
		pid_t pid = fork();
		if (pid < 0){
			perror("Fork failed: \n");
			exit(0);
		}else if (pid == 0){
			int flag = 1;
			//子进程中处理客户端请求
			close(server_socket);
			while (flag){
				if (client_handle(client_socket, db) < 0)
					flag = 0;
			}

			close(client_socket);
			exit(0);
		}else{
			//父进程中继续监听新的连接请求
			close(client_socket);
		}
	}

	//关闭数据库连接
	sqlite3_close(db);

	return 0;
}

//处理客户端请求
int client_handle(int client_socket, sqlite3 *db){
	char request[N] = {};
	char response[M] = {};
	
	//接收请求类型
	int request_type;
	if (read(client_socket, &request_type, sizeof(int)) < 0){
		perror("Read failed: \n");
		exit(0);
	}
	printf("request_type = %d\n", request_type);
	
	//接收客户端请求数据
	if (read(client_socket, request, sizeof(request)) < 0){
		perror("Read failed: \n");
		exit(0);
	}

	//根据请求类型进行处理
	switch(request_type){
		case 1:
			do_insert(db, request);
			strcpy(response, "Insert done.\n");
			break;
		case 2:
			do_delete(db, request);
			strcpy(response, "Delete done.\n");
			break;
		case 3:
			do_update(db, request);
			strcpy(response, "Update done.\n");
			break;
		case 4:
			do_query(db, request, response);
			break;
		case 5:
			do_query1(db, request, response);
			break;
		default:
			printf("Invalid request.\n");
			strcpy(response, "Invalid request.\n");
			break;

	}

	//将操作结果发送给客户端
	if (write(client_socket, response, strlen(response)) < 0){
		perror("Write failed: \n");
		exit(0);
	}

	//检测客户端是否断开连接
	int bytes_read = recv(client_socket, request, sizeof(request), MSG_PEEK);
	if (bytes_read == 0) {
		printf("Client disconnected.\n");
		return -1;
	}
	return 0;
}

// 插入学生信息
int do_insert(sqlite3 *db, char *request) {
    char name[32] = {};
    char sex[32] = {};
    int score;
    char sql[N] = {};
    char *errmsg;

    sscanf(request, "%s %s %d", name, sex, &score);

    sprintf(sql, "insert into stu (name, sex, score) values('%s', '%s', %d);", name, sex, score);

    if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
        printf("%s\n", errmsg);
    }

    return 0;
}

// 删除学生信息
int do_delete(sqlite3 *db, char *request){
	int id;
	char sql[N] = {};
	char *errmsg;

	sscanf(request, "%d", &id);

	sprintf(sql, "delete from stu where id = %d;", id);

	if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK){
		printf("%s\n", errmsg);
	}

	return 0;
}

// 更新学生信息
int do_update(sqlite3 *db, char *request){
	int id;
	int score;
	char sql[N] = {};
	char *errmsg;

	sscanf(request, "%d %d", &id, &score);

	sprintf(sql, "update stu set score = %d where id = %d;", score, id);

	if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK){
		printf("%s\n", errmsg);
	}

	return 0;
}

//查询学生成绩
int do_query(sqlite3 *db, char *request, char *response_buffer){
	char *errmsg;
	char name[32] = {};
	char sql[N] = {};

	sscanf(request, "%s", name);

	sprintf(sql, "select score from stu where name = '%s';", name);

	if (sqlite3_exec(db, sql, callback, response_buffer, &errmsg) != SQLITE_OK){
		printf("%s\n", errmsg);
	}

	return 0;	
}

// 查询回调函数
int callback(void *arg, int f_num, char **f_value, char **f_name){
    int i;
    int score_index = -1; // 成绩字段的索引
	char *response_buffer = (char *)arg;

    // 查找成绩字段的索引
    for (i = 0; i < f_num; i++) {
        if (strcmp(f_name[i], "score") == 0) {
            score_index = i;
            break;
        }
    }

    if (score_index != -1) {
		strcat(response_buffer, f_name[score_index]);
		strcat(response_buffer, " ");
		strcat(response_buffer, f_value[score_index]);
		strcat(response_buffer, "\n");
        printf("%s = %s\n", f_name[score_index], f_value[score_index]);
    }

    return 0;
}

//查询整个表格
int do_query1(sqlite3 *db, char *request, char *response_buffer){
	char *errmsg;
	char **resultp;
	int nrow;
	int ncolumn;
	int flag;

	sscanf(request, "%d", &flag);

	if (flag){
		if (sqlite3_get_table(db, "select * from stu;", &resultp, &nrow, &ncolumn, &errmsg) != SQLITE_OK){
			printf("%s\n", errmsg);
			exit(0);
		}

		int i, j;
		int index = ncolumn;
		const int width = 12;
        memset(response_buffer, 0, M);

        for (j = 0; j < ncolumn; j++) {
			char temp[width];
			snprintf(temp, width, "%-12s", resultp[j]);
            strcat(response_buffer, temp);
        }
        strcat(response_buffer, "\n");

        for (i = 0; i < nrow; i++) {
            for (j = 0; j < ncolumn; j++) {
				char temp[width];
				snprintf(temp, width, "%-12s", resultp[index++]);
                strcat(response_buffer, temp);
            }
            strcat(response_buffer, "\n");
        }
		sqlite3_free_table(resultp);
	}else{
		memset(response_buffer, 0, M);
		strcat(response_buffer, "Don't query table.\n");
	}

	return 0;
}
  1. 客户端代码 client.c
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>
#include <unistd.h>
#include <sys/socket.h>
#include <netinet/in.h>
#include <arpa/inet.h>


#define N 128
#define M 1024
#define PORT 8888

void printInsertPrompt(char *request){
	printf("Enter student information: \n");
	printf("Input name: ");
	fgets(request, N, stdin);
	printf("Input sex: ");
	fgets(request + strlen(request), N - strlen(request), stdin);
	printf("Input score: ");
	fgets(request + strlen(request), N - strlen(request), stdin);
}

void printDeletePrompt(char *request){
	printf("Enter student id to delete: ");
	fgets(request, N, stdin);
}

void printUpdatePrompt(char *request){
	printf("Enter student id to update: ");
	fgets(request, N, stdin);
	printf("Input new score: ");
	fgets(request + strlen(request), N - strlen(request), stdin);
}

void printQueryPrompt(char *request){
	printf("Enter student name to query: ");
	fgets(request, N, stdin);
}

void printQueryTable(char *request){
	printf("Confirm to query table(1-yes 0-no): ");
	fgets(request, N, stdin);
}

int main(int argc, const char *argv[])
{
	int client_socket;
	struct sockaddr_in server_address;
	char request[N] = {};
	char response[M] = {};
	int choice;
	int flag;

	//创建客户端套接字
	if ((client_socket = socket(AF_INET, SOCK_STREAM, 0)) < 0){
		perror("socket creation failed:\n");
		exit(0);
	}

	// 设置服务器地址和端口
	server_address.sin_family = AF_INET;
	server_address.sin_port = htons(PORT);
	//将IPV4地址从点分十进制转换为二进制
	if (inet_aton("127.0.0.1", &server_address.sin_addr) <= 0){
		perror("Invalid address/ Address not supported.\n");
		close(client_socket);
		exit(0);
	}

	//连接服务器
	if (connect(client_socket, (struct sockaddr *)&server_address, sizeof(server_address)) < 0){
		perror("Connection failed.\n");
		close(client_socket);
		exit(0);
	}

	while (1){
		// 提示用户选择请求类型
		printf("*************************************************\n");
		printf("1.insert 2.delete 3.update 4.query 5.query table\n");
		printf("*************************************************\n");
		printf("Input your choice: ");
		scanf("%d", &choice);
		getchar(); //吃掉换行符

		if (write(client_socket, &choice, sizeof(int)) < 0){
			perror("Write failed.\n");
			exit(0);
		}


		switch(choice){
		case 1:
			printInsertPrompt(request);
			flag = 0;
			break;
		case 2:
			printDeletePrompt(request);
			flag = 0;
			break;
		case 3:
			printUpdatePrompt(request);
			flag = 0;
			break;
		case 4:
			printQueryPrompt(request);
			flag = 1;
			break;
		case 5:
			printQueryTable(request);
			flag = 1;
			break;
		default:
			printf("Invalid choice.\n");
			flag = 0;
			break;
		}

		// 向服务端发送请求
		if(write(client_socket, request, strlen(request)) <= 0){
			perror("Write failed.\n");
			exit(0);
		}

		// 接受服务器响应数据
		if (read(client_socket, response, sizeof(response)) < 0){
			perror("Read failed.\n");
			exit(0);
		}
		
		if (flag == 0){
			//显示服务端的响应数据
			printf("Server response: %s\n", response);
		}else if (flag == 1){
			printf("%s", response);
		}
		memset(response, 0, M);
	}

	//关闭套接字
	close(client_socket);

	return 0;
}

代码逻辑

首先，客户端执行后会在终端给出提示，选择要进行的操作进行输入，输入后客户端会将所需的请求类型发送给服务端，服务端接收到来自客户端的请求类型后进行判断，然后转到相应的处理函数中执行，执行完毕后将结果发送给客户端在客户端进行显示。
以下是交互的示例图：

在这里插入图片描述
在这里插入图片描述