使用Sqlite-API开发程序

任务:

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

 

1. 数据库设计

采用Sql-Exercise的employees数据库,

设计图如下:

 

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

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

#include "DBModel.h"
#include <sqlite3.h>


DBModel::DBModel()
{
}

bool DBModel::load(const char* dbFileName)
{
    sqlite3* db;
    char* errmsg;

    int ret = sqlite3_open(dbFileName, &db);
    if (ret != SQLITE_OK)
    {
        // sqlite3_
        return false;
    }

    // 执行程序.
    const char* allDepQuery = R"(select * from department)";

    ret = sqlite3_exec(db, allDepQuery, allDepCallBack, reinterpret_cast<void*>(this), &errmsg);
    if (ret != SQLITE_OK)
    {
        sqlite3_free(errmsg);
        return false;
    }

    sqlite3_close(db);

    return true;
}

int DBModel::queryAllDepartment(void* userData, int numCol, char** colData, char** colName)
{
    std::vector<std::wstring> dataArray;
    for (int i = 0; i < numCol; i++)
    {
        // 对每一个行进行处理.
        std::string szData = colData[i];
        std::wstring szWData = m_conv.from_bytes(szData);
        dataArray.push_back(szWData);
    }
    m_allData.push_back(dataArray);

    return 0;
}

int DBModel::allDepCallBack(void* pointer, int numCol, char** colData, char** colName)
{
    return reinterpret_cast<DBModel*>(pointer)->queryAllDepartment(pointer, numCol, colData, colName);
}

 第24行,执行sql查询, 参数3为静态函数, 参数4为用户自定义数据指针.

对于C编程,直接编写全局函数,参数4为nullptr.

对于面向对象, 由于类的静态函数无法访问类的成员函数(非静态函数),

这里参数4传入this指针, 这样通过类的静态函数就能通过this指针访问对象的成员函数.

 

 

 　　　　　　　　-------------------- 勿在浮沙筑高台