SQLITE3数据库读写助手（2）
前言#
- 本文将贴出基于Qt5.14读取任意sqlite数据库文件内容的源码
- 文件有3个
| 序号 | 名称 | 概述 |
|---|---|---|
| 1 | SqliteExt.h | sqlite数据库读写头文件 |
| 2 | SqliteExt.cc | sqlite数据库读写源文件 |
| 3 | CommonDef.h | 常用定义 |
- 后面有空再优化代码吧， 现在起码功能有了
- 重构的可读写任意sqlite的数据库工具就是基于该模块实现的
- SqliteExt类还有其他的功能， 见函数， 下一节再细说
CommonDef.h#
#ifndef COMMOM_DEF_H_
#define COMMOM_DEF_H_
#include <QObject>
#include <unordered_map>
namespace oct_sqlite
{
/// ----------------------------------------------------------------------------------------
/// @brief: 保存当前支持的打开的文件
/// <sqlite3后缀，备用>
/// ----------------------------------------------------------------------------------------
using mapSqliteSuffix = std::unordered_map<QString, uchar>;
}
#endif /// COMMOM_DEF_H_
SqliteExt.h#
#ifndef COMMON_SQL_H_
#define COMMON_SQL_H_
#include <QObject>
#include <QMap>
#include <QSqlDatabase>
#include <QTableView>
#include <QSqlTableModel>
#include <QSqlQuery>
#include <map>
#include "CommonDef.h"
class QSqlTableModel;
class QTableView;
namespace oct_sqlite
{
/// ----------------------------------------------------------------------------------------
/// @brief: 读取类型
/// ----------------------------------------------------------------------------------------
enum enTableType
{
/// 数据库中的表
TT_TABLES = 1,
/// 数据库中的系统表
TT_SYSTEM_TABLES = 2,
/// 数据库视图
TT_VIEWS = 4,
/// 数据库中的全部表格
TT_ALL_TABLES = 0xFF,
};
enum enEditStrategy
{
ES_FILE_CHANGE = 0,
ES_ROW_CHANGE = 1,
ES_MANUAL_SUBMIT = 2,
};
/// ----------------------------------------------------------------------------------------
/// @brief: 数据库中的列类型
/// ----------------------------------------------------------------------------------------
enum enDBColumnType
{
///未知
DBCT_NONE = 0,
/// 整形
DBCT_INT = 1,
/// TEXT
DBCT_TEXT = 2,
/// DOUBLE
DBCT_DOUBLE = 3,
};
/// ----------------------------------------------------------------------------------------
/// @brief: 初始化参数
/// ----------------------------------------------------------------------------------------
struct stSQLInit_
{
/// 数据库文件
QString str_sqlite_file_;
/// 读取文件类型
enTableType en_tt_;
/// 编辑策略
enEditStrategy en_es_;
};
using stSQLInit = stSQLInit_;
/// ----------------------------------------------------------------------------------------
/// @brief: 操作model
/// ----------------------------------------------------------------------------------------
struct stTableModelView_
{
/// model
QSqlTableModel* pmodel_ = nullptr;
/// view
QTableView* pview_ = nullptr;
void set_model_to_view_()
{
if (pview_)
{
pview_->setModel(pmodel_);
}
}
void del_()
{
if (pmodel_)
{
delete pmodel_;
pmodel_ = nullptr;
}
if (pview_)
{
delete pview_;
pview_ = nullptr;
}
}
};
using stTableModelView = stTableModelView_;
/// ----------------------------------------------------------------------------------------
/// @brief: 保存tableName
/// ----------------------------------------------------------------------------------------
/// <表名，model_view>
using mapTable = QMap<QString, stTableModelView>;
class SqliteExt;
/// 对应的数据转换函数
using type_transfer_func = void* (SqliteExt::*)(void* pdata, const QVariant& var_data);
/// ----------------------------------------------------------------------------------------
/// @brief: 对应的数据类型处理函数结构体
/// ----------------------------------------------------------------------------------------
struct stTypeTransferFunc
{
/// 对应的类型处理函数
type_transfer_func func_ = nullptr;
/// 当前是哪个数据类型
enDBColumnType enType_ = DBCT_NONE;
};
/// <列号，对应的数据类型>
struct stTableColumn
{
/// 列名
QString str_name_ = "";
/// 列数据类型
QString str_type_ = "";
};
using mapTableColumn = QMap<uint, stTableColumn>;
/// ----------------------------------------------------------------------------------------
/// @brief: 通用读取sqlite
/// ----------------------------------------------------------------------------------------
class SqliteExt ///: public QObject
{
public:
explicit SqliteExt(QObject *parent = nullptr);
virtual ~SqliteExt();
/// -------------------------------------------------------------------------------
/// @brief: 初始化
/// @param: const stSQLInit & st_init -
/// 0 - 成功
/// 1 - 失败， st_init的第一个参数错误， 文件不存在
/// 2 - 失败， st_init的第二个参数错误， 不在给定的值中
/// 3 - 失败， st_init的第三个参数错误， 不在给定的值中
/// 5 - 失败， 无法打开参数中的数据库文件, 检查是否为sqlite3类型的数据库格式
/// 6 - 失败， 数据库中没有表格，空的数据库
/// 7 - 失败， 数据库解析失败，没有表格
/// -------------------------------------------------------------------------------
int initialized_(const stSQLInit& st_init) noexcept;
/// -------------------------------------------------------------------------------
/// @brief: 执行sqlite语句
/// @param: const QString & str_sql - sql语句
/// @param: const QString & str_table_name - 对数据库的那张表执行
/// @ret: int
///
/// -------------------------------------------------------------------------------
int carry_on_sql_(const QString& str_sql, const QString& str_table_name) noexcept;
/// -------------------------------------------------------------------------------
/// @brief: 获取sql语句执行结果
/// @param: const QString & str_sql -
/// @param: const QString & str_table_name -
/// @ret: QT_NAMESPACE::QSqlQuery*
///
/// -------------------------------------------------------------------------------
QSqlQuery* sql_record_(const QString& str_sql, const QString& str_table_name) noexcept;
/// -------------------------------------------------------------------------------
/// @brief: 释放
/// @ret: void
///
/// -------------------------------------------------------------------------------
void uninitialized_() noexcept;
/// -------------------------------------------------------------------------------
/// @brief: 获取当数据库中的表
/// @ret: oct_sql::mapTable&
///
/// -------------------------------------------------------------------------------
const mapTable& map_table_();
/// -------------------------------------------------------------------------------
/// @brief: 读取当前数据库中名为str_table_name的参数，
/// @param: const QString str_table_name - 读取哪张表
/// @param: stTableModelView * pout_tmv - 读取结果存放地址， 内部不会赋值， 外部赋值
/// @ret: int
/// 0 - 成功
/// 1 - 失败，参数[pout_tmv]为nullptr
/// 2 - 失败， 数据库中没有名为【str_table_name】的表格
/// -------------------------------------------------------------------------------
int table_(const QString str_table_name, stTableModelView* pout_tmv);
/// -------------------------------------------------------------------------------
/// @brief: 读取str_table_name中的数据到pdata
/// @param: const QString & str_table_name - 哪张表
/// @param: void * pdata - 数据存放地址
/// @ret: int
/// 0 - 成功
/// 1 - 失败， 参数中的[pdata]为空，
/// 2 - 失败， 参数中【str_table_name】的表没有在当前的数据库中
/// 3 - 失败， 无法操作当前表的model
/// 5 - 失败， 查询数据库失败
/// 6 - 失败， 参数[str_table_name]的表格为空表
/// 7 - 失败， 无法获取表格的行数
/// -------------------------------------------------------------------------------
int table_data_(const QString& str_table_name, void* pdata) noexcept;
/// -------------------------------------------------------------------------------
/// @brief: 初始化参数指定为： ES_MANUAL_SUBMIT ， 调用该函数
/// @param: const QString & str_table_name - 哪张表需要提交
/// @ret: int
/// 0 - 成功
/// 1 - 失败， 没有找到
/// 2 - 失败，没有对应的model
/// 3 - 失败， 提交失败
/// -------------------------------------------------------------------------------
int submit_(const QString& str_table_name) noexcept;
/// -------------------------------------------------------------------------------
/// @brief: 读取【str_table_name】中的数据，将结果存放到[pout_map]中
/// @param: const QString str_table_name - 哪张表
/// @param: std::map<TKey,TValue> * pout_map - 存放表数据的map, 外部分配空间， 函数内仅仅赋值，
/// @param: const int ui_key_column_index - map的TKey所在列， 从0开始数
/// @ret: int
/// 0 - 成功
/// -------------------------------------------------------------------------------
template<class TKey, class TValue>
int table_data_map_(const QString str_table_name, std::map<TKey, TValue>* pout_map, const int ui_key_column_index);
/// -------------------------------------------------------------------------------
/// @brief: 将数据写入数据库，注意： pdata中的行数应该与数据库中表的数据相同，否则，将返回非0
/// @param: const QString str_table_name - 哪张表
/// @param: void * pdata - 带写入数据的起始地址
/// @param: const uint key_index - 主键在哪一列,从0 开始数,
/// @ret: int
/// 0 - 成功
/// 1 - 失败， 参数pdata为空
/// 2 - 失败， 无法获取对应表
/// -------------------------------------------------------------------------------
int update_table_(const QString str_table_name, void* pdata, const uint key_index);
/// -------------------------------------------------------------------------------
/// @brief: 将pmap_data中的数据写入[str_table_name]中, 注意： pmap_data中的行数应该与数据库中表的数据相同，否则，将返回6
/// @param: const QString str_table_name - 对应书库中表的名字
/// @param: const std::map<TKey, TValue> * pmap_value - 待写入数据内容
/// @param: const uint key_column_index - str_table_name的主键在哪一列， 从0 开始数
/// @ret: int
///
/// -------------------------------------------------------------------------------
template<typename TKey, typename TValue>
int update_table_map_(const QString str_table_name, const std::map<TKey, TValue>* pmap_data, const uint key_column_index);
/// -------------------------------------------------------------------------------
/// @brief: 检查文件后缀是否为可支持类型
/// @param: const QString & str_suffix - 待检查的文件后缀
/// @ret: bool
/// true - 支持，
/// false - 不支持
/// -------------------------------------------------------------------------------
bool file_suffix_is_right_(const QString& str_suffix);
/// -------------------------------------------------------------------------------
/// @brief: 将支持的文件以字符串的形式返回
/// @ret: QT_NAMESPACE::QString
///
/// -------------------------------------------------------------------------------
QString suffix_() noexcept;
private:
/// -------------------------------------------------------------------------------
/// @brief: 初始化
/// @param: const stSQLInit & st_init -
/// @ret: int
/// 0 - 成功
/// 1 - 失败， st_init的第一个参数错误， 文件不存在
/// 2 - 失败， st_init的第二个参数错误， 不在给定的值中
/// 3 - 失败， st_init的第三个参数错误， 不在给定的值中
///
/// -------------------------------------------------------------------------------
int pre_initialized_(const stSQLInit& st_init) noexcept;
/// -------------------------------------------------------------------------------
/// @brief: 初始化数据库驱动
/// @ret: void
///
/// -------------------------------------------------------------------------------
void initialize_data_base_();
/// -------------------------------------------------------------------------------
/// @brief: 解析数据库默认数据类型
/// @ret: void
///
/// -------------------------------------------------------------------------------
void init_analysis_db_data_type_();
/// -------------------------------------------------------------------------------
/// @brief: 构造函数中调用
/// @ret: void
///
/// -------------------------------------------------------------------------------
void init_other_();
/// -------------------------------------------------------------------------------
/// @brief: 将单元格中的数据放入对应的处理函数
/// @param: void * pdata -
/// @param: const QVariant & var_data -
/// @ret: void
///
/// -------------------------------------------------------------------------------
void* analysis_cell_value_int_(void* pdata, const QVariant& var_data);
void* analysis_cell_value_str_(void* pdata, const QVariant& var_data);
void* analysis_cell_value_double_(void* pdata, const QVariant& var_data);
QSqlTableModel* table_model_(const QString& str_table_name);
/// -------------------------------------------------------------------------------
/// @brief: 获取列名
/// @param: const QString & str_table_name - 哪张表
/// @ret: QT_NAMESPACE::QStringList
///
/// -------------------------------------------------------------------------------
mapTableColumn table_column_type_(QSqlQueryModel* pModel, const QString& str_table_name);
/// -------------------------------------------------------------------------------
/// @brief: 读取表的行数
/// @param: QSqlQueryModel * pModel -
/// @param: const QString & str_table_name -
/// @ret: int
///
/// -------------------------------------------------------------------------------
int table_row_count_(QSqlQueryModel* pModel, const QString& str_table_name);
/// -------------------------------------------------------------------------------
/// @brief: 根据参数， 得到对应sql语句对应的部分
/// @param: const QString & str_key_type - 当前数据是那种类型： TEXT 还是INTEGER
/// @param: void * pdata - 对应的数据
/// @param: QString * pout_value - 解析结果
/// @ret: void *
/// 数据区的下一个首地址
/// -------------------------------------------------------------------------------
void *column_sql_str_(const QString& str_key_type, void* pdata, QString* pout_value);
private:
/// 是否执行了释放资源
bool b_has_uninitialized_ = false;
/// 数据库连链接名称
QString str_data_base_connect_name_;
/// 数据库操作对象
QSqlDatabase sql_data_base_;
/// 保存对应数据库中的表
mapTable map_db_table_;
using mapStrDataType = std::map<QString, stTypeTransferFunc>;/// QMap<QString, stTypeTransferFunc>;
/// <解析数据类型, 解析数据类型>
mapStrDataType map_analysis_db_type_;
/// 保存当前支持的sqlite3的数据库类型
mapSqliteSuffix map_sqlite_suffix_;
};
/// --------------------------------------------------------------------------------
/// @brief: oct_sqlite::SqliteExt::table_data_map_
/// --------------------------------------------------------------------------------
template<class TKey, class TValue>
int oct_sqlite::SqliteExt::table_data_map_(const QString str_table_name, std::map<TKey, TValue>* pout_map, const int ui_key_column_index)
{
/// 1. 参数第二个无效，
if (nullptr == pout_map)
{
return 1;
}
/// 2. 无法找到参数中的key，
QSqlTableModel* pmodel = table_model_(str_table_name);
if (nullptr == pmodel)
{
return 2;
}
mapTableColumn map_column_type = table_column_type_(pmodel, str_table_name);
/// ---------------------------------------------------------------------------------------
/// 3. 主键也有效， 直接读取数据
/// ---------------------------------------------------------------------------------------
/// 得到当前表列数
const int column_total_count = map_column_type.count();
/// 检查参数中的主键索引
if ((0 > ui_key_column_index) || (ui_key_column_index > column_total_count))
{
return 3;
}
/// ---------------------------------------------------------------------------------------
/// 将数据读入pdata中: 逐行读取， 逐行的同时，读取逐列
int row_total_count = table_row_count_(pmodel, str_table_name);
QSqlQuery sql_query = pmodel->query();
sql_query.first();
/// 读取行
for (int row_index = 0; row_index < row_total_count; ++row_index)
{
/// ---------------------------------------------------------------------------------------
/// 这里开始插入map
TValue map_item_value;
TKey* pkey = nullptr;
TKey map_item_key = 0;
void* pdata = (void*)&map_item_value;
/// 读取列
for (int column_index = 0; column_index < column_total_count; ++column_index)
{
/// 检查当前行是否为key
if (column_index == ui_key_column_index)
{
pkey = (TKey*)pdata;
}
QString str_cur_column_type = map_column_type.value(column_index).str_type_;
/// 当前类型为空，则跳过当前行
if (true == str_cur_column_type.isEmpty())
{
break;
}
/// 如果当前的数据类型不在给定的解析列表中，则pass
auto find_current_type_is_exist = map_analysis_db_type_.find(str_cur_column_type);
/// 不在给定的类型中， 则pass
if (find_current_type_is_exist == map_analysis_db_type_.end())
{
break;
}
/// 根据对应的数据类型做转换
if (find_current_type_is_exist->second.func_)
{
pdata = (this->*find_current_type_is_exist->second.func_)(pdata, sql_query.value(column_index));
}
else
{
;/// 数据类型处理函数无效
}
}
if (nullptr != pkey)
{
map_item_key = *pkey;
/// 一行读取结束， 将其插入map
pout_map->emplace(map_item_key, map_item_value);
}
/// 读取下一行, 无法读取，则pass
if (false == sql_query.next())
{
break;
}
}
return 0;
}
/// --------------------------------------------------------------------------------
/// @brief: oct_sqlite::SqliteExt::update_table_map_
/// --------------------------------------------------------------------------------
template<typename TKey, typename TValue>
int oct_sqlite::SqliteExt::update_table_map_(const QString str_table_name, const std::map<TKey, TValue>* pmap_data, const uint key_column_index)
{
/// 1. 检查 参数 pmap_data
if (nullptr == pmap_data)
{
return 1;
}
/// 2, 获取对应表格的model
QSqlQueryModel* pmodel = table_model_(str_table_name);
if (nullptr == pmodel)
{
return 2;
}
/// 3. 将数据写入数据库, 逐个单元格写入
/// UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
/// UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'
/// UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing'
/// WHERE LastName = 'Wilson'
/// 5. 获取表的列
mapTableColumn map_table_column_name = table_column_type_(pmodel, str_table_name);
/// 6. 检查列
const uint table_column_total_count = map_table_column_name.count();
if ((0 == table_column_total_count) || (key_column_index > table_column_total_count))
{
return 3;
}
/// 7. 获取表的行数， 用于确定要写多少个单元格
int row_total_count = table_row_count_(pmodel, str_table_name);
/// 如果表的行为0
if (0 >= row_total_count)
{
return 5;
}
/// 8. 如果参数中map的个数与当前数据库中行的个数不等，则返回
if (row_total_count != pmap_data->size())
{
return 6;
}
/// 9. 将数据写入数据库
//std::map<TKey, TValue>::iterator find_value_it = pmap_data->begin();
auto find_value_it = pmap_data->begin();
/// 一共有这么多行的数据需要写入
for (; find_value_it != pmap_data->end(); ++find_value_it)
{
QString str_update_sql = "UPDATE " + str_table_name + " SET ";
/// 主键列名
QString str_key_name = map_table_column_name.value(key_column_index).str_name_;
QString str_key_type = map_table_column_name.value(key_column_index).str_type_;
QString str_where_value;/// = where_value(str_key_name, pkey_value);
/// 是否读取下一行
bool is_read_next_line = false;
/// 统计set 列数
int set_index = 0;
/// 列数据的起始地址
void* pdata = (void*)&find_value_it->second;
/// 每一行有这么多列的数据需要写入
for (int column_index = 0; column_index < table_column_total_count; ++column_index)
{
QString str_value;
/// 不能更新主键列
if (key_column_index == column_index)
{
pdata = column_sql_str_(str_key_type, pdata, &str_where_value);
continue;
}
/// 第 X列的名称: id， name, value
QString str_column_type = map_table_column_name.value(column_index).str_type_;
QString str_column_name = map_table_column_name.value(column_index).str_name_;
/// 拼接sql: set column=value
pdata = column_sql_str_(str_column_type, pdata, &str_value);
/// 无法解析
if (true == str_value.isEmpty())
{
is_read_next_line = true;
break;
}
/// 如果这是第二次执行， 则需要添加 逗号
if (0 < set_index)
{
str_update_sql += ", " + str_column_name + "=" + str_value;
++set_index;
}
else
{
str_update_sql += str_column_name + "=" + str_value;
++set_index;
}
} /// end 总列数
/// 如果不是读取下一行
if (false == is_read_next_line)
{
/// 构建完整的sql语句
str_update_sql += " WHERE " + str_key_name + "=" + str_where_value + ";";
qDebug() << "sql_str=" << str_update_sql;
/// 执行sql语句
QSqlQuery sql_query = pmodel->query();
sql_query.first();
bool prepare_ret = sql_query.prepare(str_update_sql);
/// 语法错误
if (false == prepare_ret)
{
;
}
else
{
bool exec_ret = sql_query.exec();
qDebug() << "exec_ret=" << exec_ret;
}
}/// 读取下一行
/// 上面的一行已经写入完成， 下面开始写入下一行数据
} /// 有这么多行数据
return 0;
}
}
#endif /// COMMON_SQL_H_
SqliteExt.cc#
#include <QFile>
#include <QHeaderView>
#include <QSqlRecord>
#include <QSqlError>
#include <QDebug>
#include "SqliteExt.h"
namespace oct_sqlite
{
SqliteExt::SqliteExt(QObject *parent)
//: QObject(parent)
{
str_data_base_connect_name_ = QString("");
initialize_data_base_();
init_analysis_db_data_type_();
init_other_();
}
SqliteExt::~SqliteExt()
{
uninitialized_();
}
/// --------------------------------------------------------------------------------
/// @brief: CommonSql::initialized_
/// --------------------------------------------------------------------------------
int SqliteExt::initialized_(const stSQLInit& st_init) noexcept
{
{
///1. 参数检查
int ret = pre_initialized_(st_init);
if (0 != ret)
{
return ret;
}
}
/// 2. 避免多次初始化
uninitialized_();
/// 3. 尝试打开数据库文件
sql_data_base_.setDatabaseName(st_init.str_sqlite_file_);
/// 打开失败
if (false == sql_data_base_.open())
{
QSqlError error = sql_data_base_.lastError();
return 5;
}
{
/// 5. 打开成功，读取数据库中数据
/// 读取表名
QStringList list_table_name = sql_data_base_.tables(static_cast<QSql::TableType>(st_init.en_tt_));
/// 6. 如果没有表
if (0 == list_table_name.count())
{
return 6;
}
/// 7. 存在， 创建对应的sqlmodel和tableview，
for (auto str_table_name : list_table_name)
{
stTableModelView st_tmv;
/// 8, 创建tableview
st_tmv.pview_ = new(std::nothrow) QTableView;
st_tmv.pmodel_ = new(std::nothrow) QSqlTableModel(st_tmv.pview_, sql_data_base_);
QSqlTableModel* pmodel = st_tmv.pmodel_;
/// 9. 设置model的参数
if (pmodel)
{
pmodel->setTable(str_table_name);
/// 设置编辑策略
pmodel->setEditStrategy(static_cast<QSqlTableModel::EditStrategy>(st_init.en_es_));
/// 查询山上
pmodel->select();
/// 解除256行的限制
while (pmodel->canFetchMore())
{
pmodel->fetchMore();
}
}
st_tmv.set_model_to_view_();
/// 10. 设置view的参数
QTableView* pview = st_tmv.pview_;
if (pview)
{
pview->verticalHeader()->setVisible(false);
pview->horizontalHeader()->setSectionResizeMode(QHeaderView::ResizeToContents);
pview->horizontalHeader()->setStretchLastSection(true);
pview->setSelectionBehavior(QAbstractItemView::SelectItems);
}
/// 加入map
map_db_table_.insert(str_table_name, st_tmv);
}
}
/// 11. 读取结束， 检查map中的个数
if (0 == map_db_table_.count())
{
return 7;
}
/// 12, 保存当前操作的数据库名称
str_data_base_connect_name_ = st_init.str_sqlite_file_;
return 0;
}
/// --------------------------------------------------------------------------------
/// @brief: 执行sqlite语句
/// --------------------------------------------------------------------------------
int SqliteExt::carry_on_sql_(const QString& str_sql, const QString& str_table_name) noexcept
{
/// 1. 如果数据库没有打开，则返回
if (false == sql_data_base_.isOpen())
{
return 1;
}
/// 2. 参数为空
if (true == str_sql.isEmpty())
{
return 2;
}
/// 3. 执行sql
auto find_sql_model_it = map_db_table_.find(str_table_name);
/// 表名不存在
if (find_sql_model_it == map_db_table_.end())
{
return 3;
}
QSqlQueryModel* pmodel = find_sql_model_it->pmodel_;
if (nullptr == pmodel)
{
/// 指针无效
return 5;
}
bool prepare_ret = pmodel->query().prepare(str_sql);
/// 语法错误
if (false == prepare_ret)
{
return 6;
}
///
bool exec_ret = pmodel->query().exec();
/// 返回执行结果
return (true == exec_ret ? 0 : 7);
}
/// --------------------------------------------------------------------------------
/// @brief: CommonSql::sql_record_
/// --------------------------------------------------------------------------------
QSqlQuery* SqliteExt::sql_record_(const QString& str_sql, const QString& str_table_name) noexcept
{
/// 1. 如果数据库没有打开，则返回
if (false == sql_data_base_.isOpen())
{
return nullptr;
}
/// 2. 参数为空
if (true == str_sql.isEmpty())
{
return nullptr;
}
/// 3. 执行sql
auto find_sql_model_it = map_db_table_.find(str_table_name);
/// 表名不存在
if (find_sql_model_it == map_db_table_.end())
{
return nullptr;
}
QSqlQueryModel* pmodel = find_sql_model_it->pmodel_;
if (nullptr == pmodel)
{
/// 指针无效
return nullptr;
}
bool prepare_ret = pmodel->query().prepare(str_sql);
/// 语法错误
if (false == prepare_ret)
{
return nullptr;
}
///
bool exec_ret = pmodel->query().exec();
/// 返回执行结果
return (true == exec_ret ? &pmodel->query() : nullptr);
}
/// --------------------------------------------------------------------------------
/// @brief: CommonSql::uninitialized_
/// --------------------------------------------------------------------------------
void SqliteExt::uninitialized_() noexcept
{
try
{
for (auto item: map_db_table_)
{
item.del_();
}
/// 重置map
if (0 < map_db_table_.count())
{
mapTable map_tmp;
map_db_table_.swap(map_tmp);
}
/// 关闭数据库
if (true == sql_data_base_.isOpen())
{
sql_data_base_.close();
}
}
catch (...)
{
;
}
}
/// --------------------------------------------------------------------------------
/// @brief: CommonSql::map_table_
/// --------------------------------------------------------------------------------
const oct_sqlite::mapTable& SqliteExt::map_table_()
{
return map_db_table_;
}
/// --------------------------------------------------------------------------------
/// @brief: SqliteAssistant::table_model_
/// --------------------------------------------------------------------------------
QSqlTableModel* SqliteExt::table_model_(const QString& str_table_name)
{
/// 1. 找到对应的表
auto find_model_it = map_db_table_.find(str_table_name);
if (find_model_it == map_db_table_.end())
{
return nullptr;
}
/// 3. 获取数据库表的字段类型
return find_model_it->pmodel_;;
}
/// --------------------------------------------------------------------------------
/// @brief: SqliteAssistant::table_column_name_
/// --------------------------------------------------------------------------------
mapTableColumn SqliteExt::table_column_type_(QSqlQueryModel* pModel, const QString& str_table_name)
{
mapTableColumn ret_map;
if (nullptr == pModel)
{
return ret_map;
}
QString str_sql_column_type = QString("PRAGMA table_info( " + str_table_name + " ) ");
QSqlQuery sqlquery = pModel->query();
bool ret = sqlquery.prepare(str_sql_column_type);
if (false == ret)
{
return ret_map;
}
if (true == sqlquery.exec())
{
/// 7. 将列的数据类型放入list
uint index = 0;
while (sqlquery.next())
{
stTableColumn map_insert_value;
map_insert_value.str_name_ = sqlquery.value(1).toString();
map_insert_value.str_type_ = sqlquery.value(2).toString();
ret_map.insert(index, map_insert_value);
++index;
}
}
sqlquery.first();
return ret_map;
}
/// --------------------------------------------------------------------------------
/// @brief: SqliteAssistant::table_row_count_
/// --------------------------------------------------------------------------------
int SqliteExt::table_row_count_(QSqlQueryModel* pmodel, const QString& str_table_name)
{
if (nullptr == pmodel)
{
return 0;
}
/// 继续读取行数
QSqlQuery sql_query = pmodel->query();
QString str_sql_query = QString("select * from %1").arg(str_table_name);
sql_query.prepare(str_sql_query);
QSqlRecord table_record = sql_query.record();
if (false == sql_query.exec())
{
return 7;
}
/// 8. 读取行数
int row_total_count = 0;
if (true == sql_query.last())
{
row_total_count = sql_query.at() + 1;
sql_query.first();
}
return row_total_count;
}
/// --------------------------------------------------------------------------------
/// @brief: 读取str_table_name中的数据到pdata中
/// --------------------------------------------------------------------------------
int SqliteExt::table_data_(const QString& str_table_name, void* pdata) noexcept
{
/// 0. 指针无效
if (nullptr == pdata)
{
return 1;
}
/// 1. 得到数据库模型
QSqlQueryModel* pmodel = table_model_(str_table_name);
if (nullptr == pmodel)
{
return 2;
}
/// ---------------------------------------------------------------------------------------
/// 5. 准备查询数据库字段的sql语句
/// 6. 读取表的列数
mapTableColumn map_column_type = table_column_type_(pmodel, str_table_name);
/// 得到当前表列数
const int column_total_count = map_column_type.count();
/// ---------------------------------------------------------------------------------------
/// 将数据读入pdata中: 逐行读取， 逐行的同时，读取逐列
int row_total_count = table_row_count_(pmodel, str_table_name);
QSqlQuery sql_query = pmodel->query();
sql_query.first();
/// 读取行
for (int row_index = 0; row_index < row_total_count; ++row_index)
{
/// 读取列
for (int column_index = 0; column_index < column_total_count; ++column_index)
{
QString str_cur_column_type = map_column_type.value(column_index).str_type_;
/// 当前类型为空，则跳过当前行
if (true == str_cur_column_type.isEmpty())
{
break;
}
/// 如果当前的数据类型不在给定的解析列表中，则pass
auto find_current_type_is_exist = map_analysis_db_type_.find(str_cur_column_type);
/// 不在给定的类型中， 则pass
if (find_current_type_is_exist == map_analysis_db_type_.end())
{
break;
}
/// 根据对应的数据类型做转换
if (find_current_type_is_exist->second.func_)
{
pdata = (this->*find_current_type_is_exist->second.func_)(pdata, sql_query.value(column_index));
}
else
{
;/// 数据类型处理函数无效
}
}
/// 读取下一行, 无法读取，则pass
if (false == sql_query.next())
{
break;
}
}
return 0;
}
/// --------------------------------------------------------------------------------
/// @brief: CommonSql::pre_initialized_
/// --------------------------------------------------------------------------------
int SqliteExt::pre_initialized_(const stSQLInit& st_init) noexcept
{
/// 1. 数据库文件不存在
{
QFile file_db(st_init.str_sqlite_file_);
if (false == file_db.exists())
{
return 1;
}
/// 2. 检查参数
const uint en_tt = st_init.en_tt_;
if ( (TT_TABLES != en_tt) && (TT_VIEWS != en_tt) && (TT_SYSTEM_TABLES != en_tt) && (TT_ALL_TABLES != en_tt) )
{
return 2;
}
/// 检查第三个参数
if ((ES_FILE_CHANGE != st_init.en_es_) && (ES_ROW_CHANGE != st_init.en_es_) && (ES_MANUAL_SUBMIT != st_init.en_es_))
{
return 3;
}
}
return 0;
}
/// --------------------------------------------------------------------------------
/// @brief: CommonSql::initialize_data_base_
/// --------------------------------------------------------------------------------
void SqliteExt::initialize_data_base_()
{
bool is_contain = QSqlDatabase::contains(str_data_base_connect_name_);
if (true == is_contain)
{
sql_data_base_ = QSqlDatabase::database(str_data_base_connect_name_);
}
else
{
sql_data_base_ = QSqlDatabase::addDatabase("QSQLITE", str_data_base_connect_name_);
str_data_base_connect_name_ = "QSQLITE";
}
}
/// --------------------------------------------------------------------------------
/// @brief: 解析数据库默认数据类型
/// --------------------------------------------------------------------------------
void SqliteExt::init_analysis_db_data_type_()
{
auto insert_map = [&](const QString str_type, type_transfer_func func, enDBColumnType en_dbct)
{
stTypeTransferFunc map_item_value;
map_item_value.func_ = func;
map_item_value.enType_ = en_dbct;
map_analysis_db_type_.emplace(str_type, map_item_value);
};
/// 1. 文本
insert_map("TEXT", &SqliteExt::analysis_cell_value_str_, DBCT_TEXT);
/// 2. 整数
insert_map("INTEGER", &SqliteExt::analysis_cell_value_int_, DBCT_INT);
/// 3. double
insert_map("DOUBLE", &SqliteExt::analysis_cell_value_double_, DBCT_DOUBLE);
/// 5. char
////insert_map("CHAR", &SqliteAssistant::analysis_cell_value_char_, );
}
/// --------------------------------------------------------------------------------
/// @brief: CommonSql::analysis_cell_value_int_
/// --------------------------------------------------------------------------------
void* SqliteExt::analysis_cell_value_int_(void* pdata, const QVariant& var_data)
{
if (nullptr == pdata)
{
return pdata;
}
int *pvalue = (int*)(pdata);
*pvalue = var_data.toInt();
++pvalue;
pdata = (void*)pvalue;
return pdata;
}
/// --------------------------------------------------------------------------------
/// @brief: CommonSql::analysis_cell_value_str_
/// --------------------------------------------------------------------------------
void* SqliteExt::analysis_cell_value_str_(void* pdata, const QVariant& var_data)
{
if (nullptr == pdata)
{
return pdata;
}
QString *pvalue = (QString*)(pdata);
*pvalue = var_data.toString();;
++pvalue;
pdata = (void*)pvalue;
return pdata;
}
/// --------------------------------------------------------------------------------
/// @brief: CommonSql::analysis_cell_value_double_
/// --------------------------------------------------------------------------------
void* SqliteExt::analysis_cell_value_double_(void* pdata, const QVariant& var_data)
{
if (nullptr == pdata)
{
return pdata;
}
double *pvalue = (double*)(pdata);
*pvalue = var_data.toDouble();
++pvalue;
pdata = (void*)pvalue;
return pdata;
}
/// --------------------------------------------------------------------------------
/// @brief: CommonSql::读取当前数据库中名为str_table_name的参数
/// --------------------------------------------------------------------------------
int SqliteExt::table_(const QString str_table_name, stTableModelView* pout_tmv)
{
/// 1. 参数为空无法存放读取结果
if (nullptr == pout_tmv)
{
return 1;
}
/// 2. 找参数中的表格
auto find_table_it = map_db_table_.find(str_table_name);
/// 3. 没找到； 返回2
if (find_table_it == map_db_table_.end())
{
return 2;
}
/// 5 找到了，返回结果
*pout_tmv = find_table_it.value();
return 0;
}
/// -------------------------------------------------------------------------------
/// @brief: 检查参数中的key是否在给定的listColumnName中
/// @param: const QStringList & listColumnName -
/// @ret: bool
/// -1 -不存在列
/// X > 0 ， key是第几列
/// -------------------------------------------------------------------------------
/// --------------------------------------------------------------------------------
/// @brief: oct_sqlite::SqliteAssistant::is_right_key_
/// --------------------------------------------------------------------------------
template<class TKey>
int SqliteExt::is_right_key_(const mapTableColumn& listColumnName)
{
int ret = 0;
return ret;
}
/// --------------------------------------------------------------------------------
/// @brief: 将数据写入数据库
/// --------------------------------------------------------------------------------
int SqliteExt::update_table_(const QString str_table_name, void* pdata, const uint key_column_index)
{
/// 1. 检查 参数 pdata
if (nullptr == pdata)
{
return 1;
}
/// 2, 获取对应表格的model
QSqlQueryModel* pmodel = table_model_(str_table_name);
if (nullptr == pmodel)
{
return 2;
}
/// 3. 将数据写入数据库, 逐个单元格写入
/// UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
/// UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'
/// UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing'
/// WHERE LastName = 'Wilson'
/// 5. 获取表的列
mapTableColumn map_table_column_name = table_column_type_(pmodel, str_table_name);
/// 6. 检查列
const uint table_column_total_count = map_table_column_name.count();
if ((0 == table_column_total_count) || (key_column_index > table_column_total_count))
{
return 3;
}
/// 7. 获取表的行数， 用于确定要写多少个单元格
int row_total_count = table_row_count_(pmodel, str_table_name);
/// 如果表的行为0
if (0 >= row_total_count)
{
return 5;
}
/// ---------------------------------------------------------------------------------------
/// 有这么多行
for (int row_index = 0; row_index < row_total_count; ++row_index)
{
QString str_update_sql = "UPDATE " + str_table_name + " SET ";
/// 主键列名
QString str_key_name = map_table_column_name.value(key_column_index).str_name_;
QString str_key_type = map_table_column_name.value(key_column_index).str_type_;
QString str_where_value;/// = where_value(str_key_name, pkey_value);
/// 是否读取下一行
bool is_read_next_line = false;
/// 统计set 列数
int set_index = 0;
/// 有这么多列
for (int column_index = 0; column_index < table_column_total_count; ++column_index)
{
QString str_value;
/// 不能更新主键列
if (key_column_index == column_index)
{
pdata = column_sql_str_(str_key_type, pdata, &str_where_value);
continue;
}
/// 第 X列的名称: id， name, value
QString str_column_type = map_table_column_name.value(column_index).str_type_;
QString str_column_name = map_table_column_name.value(column_index).str_name_;
/// 拼接sql: set column=value
pdata = column_sql_str_(str_column_type, pdata, &str_value);
/// 无法解析
if (true == str_value.isEmpty())
{
is_read_next_line = true;
break;
}
/// 如果这是第二次执行， 则需要添加 逗号
if (0 < set_index)
{
str_update_sql += ", " + str_column_name + "=" + str_value;
++set_index;
}
else
{
str_update_sql += str_column_name + "=" + str_value;
++set_index;
}
}
/// 如果不是读取下一行
if (false == is_read_next_line)
{
/// 构建完整的sql语句
str_update_sql += " WHERE " + str_key_name + "=" + str_where_value + ";";
/// 执行sql语句
QSqlQuery sql_query = pmodel->query();
sql_query.first();
bool prepare_ret = sql_query.prepare(str_update_sql);
/// 语法错误
if (false == prepare_ret)
{
;
}
else
{
bool exec_ret = sql_query.exec();
//qDebug() << "exec_ret=" << exec_ret;
}
}
/// 继续下一行
}
return 0;
}
/// --------------------------------------------------------------------------------
/// @brief: SqliteExt::column_sql_str_
/// --------------------------------------------------------------------------------
void * SqliteExt::column_sql_str_(const QString& str_key_type, void* pdata, QString* pout_value)
{
void *ret_value = nullptr;
/// 如果是text
if ("TEXT" == str_key_type)
{
QString *pstr = (QString*)pdata;
*pout_value = "'" + *pstr + "'";
++pstr;
ret_value = (void*)pstr;
}
/// 如果是INT
else if ("INTEGER" == str_key_type)
{
int* pint = (int*)pdata;
*pout_value = QString::number(*pint);
++pint;
ret_value = (void*)pint;
}
/// 如果是double
else if ("DOUBLE" == str_key_type)
{
double* pdb = (double*)pdata;
*pout_value = QString::number(*pdb);
++pdb;
ret_value = (void*)pdb;
}
else
{
;
}
return ret_value;
}
/// --------------------------------------------------------------------------------
/// @brief: SqliteExt::init_other_
/// --------------------------------------------------------------------------------
void SqliteExt::init_other_()
{
/// 参数1: sqlite的后缀, 比如: .slite
auto insert_map = [=](const QString& str_suffix)
{
map_sqlite_suffix_.emplace(str_suffix, 0);
};
insert_map(".db");
insert_map(".sdb");
insert_map(".sqlite");
insert_map(".db3");
insert_map(".s3db");
insert_map(".sqlite3");
insert_map(".sl3");
}
/// --------------------------------------------------------------------------------
/// @brief: 检查文件后缀是否为可支持类型
/// --------------------------------------------------------------------------------
bool SqliteExt::file_suffix_is_right_(const QString& str_suffix)
{
auto find_suffix_it = map_sqlite_suffix_.find(str_suffix);
if (find_suffix_it == map_sqlite_suffix_.end())
{
return false;
}
return true;
}
/// --------------------------------------------------------------------------------
/// @brief: SqliteExt::submit_
/// --------------------------------------------------------------------------------
int SqliteExt::submit_(const QString& str_table_name) noexcept
{
auto find_table_it = map_db_table_.find(str_table_name);
/// 1. 没有找到
if (find_table_it == map_db_table_.end())
{
return 1;
}
/// 2. 找到了，检查提交结果
auto pmodel = find_table_it.value().pmodel_;
if (pmodel)
{
if (false == pmodel->submitAll())
{
return 3;
}
}
else
{
return 2;
}
return 0;
}
/// --------------------------------------------------------------------------------
/// @brief: 将支持的文件以字符串的形式返回
/// --------------------------------------------------------------------------------
QString SqliteExt::suffix_() noexcept
{
QString str_ret;
for (auto find_it = map_sqlite_suffix_.begin(); find_it != map_sqlite_suffix_.end(); ++ find_it)
{
str_ret += find_it->first + " ";
}
return str_ret;
}
}
作者： mohist
出处：https://www.cnblogs.com/pandamohist/p/16042335.html
版权：本站使用「CC BY 4.0」创作共享协议，未经作者同意，请勿转载；若经同意转载，请在文章明显位置注明作者和出处。
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 阿里最新开源QwQ-32B，效果媲美deepseek-r1满血版，部署成本又又又降低了！
· AI编程工具终极对决：字节Trae VS Cursor，谁才是开发者新宠？
· 开源Multi-agent AI智能体框架aevatar.ai，欢迎大家贡献代码
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
2019-03-23 二叉搜索树判定方法（c++实现）
2019-03-23 Mac下好用的“visio”之 OmniGraffle Pro
2019-03-23 二叉树的深度优先遍历