使用Sqlite-API开发程序

转载

mb5fd86cce321a9 2021-09-29 18:57:00

文章标签 SQL sqlite #include 静态函数数据 文章分类 SQL Server 数据库

任务:

使用SQLITE的api进行简单的查询, 结果写入到数据类中.

1. 数据库设计

采用Sql-Exercise的employees数据库,

设计图如下:

使用Sqlite-API开发程序_SQL

包含三个表格, 分别是部门表, 工资级别表和员工表.

2. 建立数据库,

使用sqlite3.exe新建数据库employees.db.

建立employees.sql文件

CREATE TABLE department(
dep_id INTEGER,
dep_name TEXT,
dep_location TEXT);

CREATE TABLE salary_grade(
grade INTEGER,
min_salary INTEGER,
max_salary INTEGER);

CREATE TABLE employees(
emp_id INTEGER,
emp_name TEXT,
job_name TEXT,
manager_id INTEGER,
hire_date TEXT,
salary    FLOAT,
commission FLOAT,
dep_id INTEGER);

INSERT INTO department VALUES (1001, '财务部', '悉尼');
INSERT INTO department VALUES (2001, '审计部', '墨尔本');
INSERT INTO department VALUES (3001, '市场部', '柏斯');
INSERT INTO department VALUES (4001, '产品部', '布瑞斯本');

INSERT INTO salary_grade VALUES (1, 800, 1300);
INSERT INTO salary_grade VALUES (2, 1301, 1500);
INSERT INTO salary_grade VALUES (3, 1501, 2100);
INSERT INTO salary_grade VALUES (4, 2101, 3100);
INSERT INTO salary_grade VALUES (5, 3101, 9999);

/**
* 插入值, 批量, 事务插入,
*/
INSERT INTO employees VALUES (68319,    '科林',    '总经理',NULL,    '1991-11-18',    6000.00,    NULL,    1001);
INSERT INTO employees VALUES (66928,    '布雷泽',    '经理',    68319,    '1991-05-01',    2750.00,    NULL,    3001);
INSERT INTO employees VALUES (67832,    'CLARE',    '经理',    68319,    '1991-06-09',    2550.00,    NULL,    1001);
INSERT INTO employees VALUES (65646,    '吉安娜丝',    '经理',    68319,    '1991-04-02',    2957.00,    NULL,    2001);
INSERT INTO employees VALUES (64989,    'ADELYN',    '销售',    66928,    '1991-02-20',    1700.00,    400.00,    3001);
INSERT INTO employees VALUES (65271,    'WADE',        '销售',    66928,    '1991-02-22',    1350.00,    600.00,    3001);
INSERT INTO employees VALUES (66564,    'MADDEN',    '销售',    66928,    '1991-09-28',    1350.00,    1500.00,    3001);
INSERT INTO employees VALUES (68454,    '塔克尔',    '销售', 66928,    '1991-09-08',    1600.00,    0.00,    3001);
INSERT INTO employees VALUES (68736,    '安德烈斯',    '职员',    67858,    '1997-05-23',    1200.00,    NULL,    2001);
INSERT INTO employees VALUES (69000,    '朱莉',    '职员',    66928,    '1991-12-03',    1050.00,    NULL,    3001);
INSERT INTO employees VALUES (69324,    'MARKER',    '职员',    67832,    '1992-01-23',    1400.00,    NULL,    1001);
INSERT INTO employees VALUES (67858,    'SCARLET',    '分析员',    65646,    '1997-04-19',    3100.00,    NULL,    2001);
INSERT INTO employees VALUES (69062,    '弗兰克',    '分析员',    65646,    '1991-12-03',    3100.00,    NULL,    2001);
INSERT INTO employees VALUES (63679,    '桑德莲娜',    '职员',    69062,    '1990-12-18',    900.00,        NULL,   2001)

执行命令:

>> sqlite3.exe employees.db

>> .read xxxx/empolyees.sql

3.建立数据类DBModel

DBModel.h文件

#pragma once

#include <vector>
#include <string>
#include <codecvt>

// 部门类
class Department
{
    // TODO
};

// 工资级别类
class SalaryGrade
{
　　// TODO
};


class DBModel
{
public:
    DBModel();

// 加载数据文件.入口.
    bool load(const char* dbFileName);
// 实际的查询结果处理程序
    int queryAllDepartment(void*, int, char**, char**);

// 全局函数
    static int allDepCallBack(void*, int, char**, char**);

private:

// 存储所有的数据
    std::vector<std::vector<std::wstring>> m_allData;

// 字符编码转换, utf8的字符串转成宽字符串wstring.
    std::wstring_convert<std::codecvt_utf8<wchar_t>> m_conv;
};

DBModel.cpp

1 #include "DBModel.h"
 2 #include <sqlite3.h>
 3 
 4 
 5 DBModel::DBModel()
 6 {
 7 }
 8 
 9 bool DBModel::load(const char* dbFileName)
10 {
11     sqlite3* db;
12     char* errmsg;
13 
14     int ret = sqlite3_open(dbFileName, &db);
15     if (ret != SQLITE_OK)
16     {
17         // sqlite3_
18         return false;
19     }
20 
21     // 执行程序.
22     const char* allDepQuery = R"(select * from department)";
23 
24     ret = sqlite3_exec(db, allDepQuery, allDepCallBack, reinterpret_cast<void*>(this), &errmsg);
25     if (ret != SQLITE_OK)
26     {
27         sqlite3_free(errmsg);
28         return false;
29     }
30 
31     sqlite3_close(db);
32 
33     return true;
34 }
35 
36 int DBModel::queryAllDepartment(void* userData, int numCol, char** colData, char** colName)
37 {
38     std::vector<std::wstring> dataArray;
39     for (int i = 0; i < numCol; i++)
40     {
41         // 对每一个行进行处理.
42         std::string szData = colData[i];
43         std::wstring szWData = m_conv.from_bytes(szData);
44         dataArray.push_back(szWData);
45     }
46     m_allData.push_back(dataArray);
47 
48     return 0;
49 }
50 
51 int DBModel::allDepCallBack(void* pointer, int numCol, char** colData, char** colName)
52 {
53     return reinterpret_cast<DBModel*>(pointer)->queryAllDepartment(pointer, numCol, colData, colName);
54 }