网络编程9.10

使用数据库完成工人管理系统:

ubuntu@ubuntu:DB$ 
ubuntu@ubuntu:DB$ cat 2.c
#include <myhead.h>
#include <sqlite3.h>
#include <string.h>

typedef struct
{
    int id;
    char name[20];
    double salary;
} Worker;

int do_insert(sqlite3 *ppDb)
{
    Worker work;
    printf("请输入工号：");
    scanf("%d", &work.id);
    getchar(); // 清除缓冲区
    printf("请输入姓名：");
    fgets(work.name, sizeof(work.name), stdin);
    work.name[strcspn(work.name, "\n")] = 0; // 移除换行符
    printf("请输入薪水：");
    scanf("%lf", &work.salary);
    
    char *errmsg = NULL;
    char sqlite[200];
    snprintf(sqlite, sizeof(sqlite), "INSERT INTO Work VALUES(%d, '%s', %lf);",
             work.id, work.name, work.salary);
    if(sqlite3_exec(ppDb, sqlite, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("错误码：%d 错误信息：%s, 出错行是：%d\n",
               sqlite3_errcode(ppDb), sqlite3_errmsg(ppDb), __LINE__);
        printf("错误信息：%s\n", errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    printf("添加工人信息成功\n");
    return 0;
}

int do_delete(sqlite3 *ppDb)
{
    char name[20];
    printf("请输入要删除工人的姓名：");
    fgets(name, sizeof(name), stdin);
    name[strcspn(name, "\n")] = 0; // 移除换行符

    char *errmsg = NULL;
    char sqlite[200];
    snprintf(sqlite, sizeof(sqlite), "DELETE FROM Work WHERE name='%s';", name);
    if(sqlite3_exec(ppDb, sqlite, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("错误码：%d 错误信息：%s, 出错行是：%d\n",
               sqlite3_errcode(ppDb), sqlite3_errmsg(ppDb), __LINE__);
        printf("错误信息：%s\n", errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    printf("删除工人信息成功\n");
    return 0;
}

int do_update(sqlite3 *ppDb)
{
    char name[20];
    double new_salary;
    printf("请输入要修改工人的姓名：");
    fgets(name, sizeof(name), stdin);
    name[strcspn(name, "\n")] = 0; // 移除换行符
    printf("请输入新的薪水：");
    scanf("%lf", &new_salary);
    getchar(); // 清除缓冲区

    char *errmsg = NULL;
    char sqlite[200];
    snprintf(sqlite, sizeof(sqlite), "UPDATE Work SET salary=%lf WHERE name='%s';", new_salary, name);
    if(sqlite3_exec(ppDb, sqlite, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("错误码：%d 错误信息：%s, 出错行是：%d\n",
               sqlite3_errcode(ppDb), sqlite3_errmsg(ppDb), __LINE__);
        printf("错误信息：%s\n", errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    printf("修改工人信息成功\n");
    return 0;
}

int callback(void *arg, int n, char **msgtext, char **msgtable)
{
    int i;
    for(i = 0; i < n; i++)
    {
        printf("%s\t", msgtable[i]);
    }
    putchar(10);
    for(i = 0; i < n; i++)
    {
        printf("%s\t", msgtext[i] ? msgtext[i] : "NULL");
    }
    putchar(10);
    return 0;
}

int do_seek(sqlite3 *ppDb)
{
    char key[20];
    printf("请输入你要查找的工人姓名：");
    fgets(key, sizeof(key), stdin);
    key[strcspn(key, "\n")] = 0; // 移除换行符

    char *errmsg = NULL;
    char sqlite[200];
    snprintf(sqlite, sizeof(sqlite), "SELECT * FROM Work WHERE name='%s';", key);

    if(sqlite3_exec(ppDb, sqlite, callback, NULL, &errmsg) != SQLITE_OK)
    {
        printf("错误码：%d 错误信息：%s, 出错行是：%d\n",
               sqlite3_errcode(ppDb), sqlite3_errmsg(ppDb), __LINE__);
        printf("错误信息：%s\n", errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    return 0;
}

int show_all(void *arg, int n, char **msgtext, char **msgtable)
{
    static int row = 0;
    int i;
    if (row == 0) {
        for(i = 0; i < n; i++)
        {
            printf("%s\t", msgtable[i]);
        }
        printf("\n");
    }

    for(i = 0; i < n; i++)
    {
        printf("%s\t", msgtext[i] ? msgtext[i] : "NULL");
    }
    printf("\n");
    row++;
    return 0;
}

int do_showall(sqlite3 *ppDb)
{
    char *errmsg = NULL;
    const char *sqlite = "SELECT * FROM Work;";
    if(sqlite3_exec(ppDb, sqlite, show_all, NULL, &errmsg) != SQLITE_OK)
    {
        printf("错误码：%d 错误信息：%s, 出错行是：%d\n",
               sqlite3_errcode(ppDb), sqlite3_errmsg(ppDb), __LINE__);
        printf("错误信息：%s\n", errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    return 0;
}

void menu(sqlite3 *ppDb)
{
    int n;
    printf("\n\t\t\t*********肖申克的救赎*********\n");
    printf("\t\t\t1、插入一个工人信息\n");
    printf("\t\t\t2、删除一个工人信息\n");
    printf("\t\t\t3、修改一个工人信息\n");
    printf("\t\t\t4、查找一个工人信息\n");
    printf("\t\t\t5、输出所有工人信息\n");
    printf("\t\t\t0、退出\n");
    printf("请输入你的选择：");
    scanf("%d", &n);
    getchar(); // 清除缓冲区
    switch(n)
    {
        case 1:
            do_insert(ppDb);
            break;
        case 2:
            do_delete(ppDb);
            break;
        case 3:
            do_update(ppDb);
            break;
        case 4:
            do_seek(ppDb);
            break;
        case 5:
            do_showall(ppDb);
            break;
        case 0:
            exit(0);
        default:
            printf("输入有误，请重新输入\n");
    }
}

int main(int argc, const char *argv[])
{
    sqlite3 *ppDb;
    if(sqlite3_open("./hhh.db", &ppDb) != SQLITE_OK)
    {
        printf("错误码：%d 错误信息：%s, 出错行是：%d\n",
               sqlite3_errcode(ppDb), sqlite3_errmsg(ppDb), __LINE__);
        return -1;
    }
    printf("数据库打开成功\n");

    char *errmsg = NULL;
    const char *sqlite = "CREATE TABLE IF NOT EXISTS Work(id INT, name TEXT, salary REAL);";
    if(sqlite3_exec(ppDb, sqlite, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("错误码：%d 错误信息：%s, 出错行是：%d\n",
               sqlite3_errcode(ppDb), sqlite3_errmsg(ppDb), __LINE__);
        printf("错误信息：%s\n", errmsg);
        sqlite3_free(errmsg);
        sqlite3_close(ppDb);
        return -1;
    }

    while(1)
    {
        menu(ppDb);
    }

    sqlite3_close(ppDb);
    printf("数据库关闭成功\n");
    return 0;
}