功能:
Sqlite3 数据库接口使用操作。
下载:(0积分)
sqlite3资源文件下载地址:https://download.csdn.net/download/minggeqiuzhi2/89018731
代码示例:
#include "mysqltool_lite.h"
#include "memdb_define.h"
#include <QDebug>
#include "ToolBase.h"
#include <QUuid>
#include <QFileInfo>
#pragma comment(lib, "sqlite3.lib")
/*********************************************************************/
mysqltool_lite::mysqltool_lite()
{
}
mysqltool_lite::~mysqltool_lite()
{
closeDb();
}
void mysqltool_lite::initlize(char *sqlname)
{
m_bcreateadmin = false;
memset(m_szsqlname, 0, sizeof(m_szsqlname));
strcpy_s(m_szsqlname, sqlname);
openDb();
}
void mysqltool_lite::closeDb()
{
/*step 3: 关闭数据库连接对象*/
sqlite3_close(_pdb);
}
bool mysqltool_lite::openDb()
{
QFileInfo f(QString(m_szsqlname));
if ( !QFileInfo::exists(QString(m_szsqlname)))
{
m_bcreateadmin = true;
}
/*step 1: 打开数据库连接对象*/
int r = sqlite3_open(m_szsqlname, &_pdb);
if (r != SQLITE_OK)
{
printf("Error : open sql=>%s\n", sqlite3_errmsg(_pdb));
return false;
}
printf("success to connect sqlite3 database.\n");
create_table();
return true;
}
void mysqltool_lite::create_table()
{
/*
create table if not exists vadmin (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_name VARCHAR (50) DEFAULT (1),
nick_name VARCHAR (50),
password VARCHAR (50),
salt VARCHAR (50),
group_id INT (1),
add_time INT (20),
update_time INT (20)
);
*/
create_table_user();
}
void mysqltool_lite::create_table_user()
{
QString strsql = QString("create table if not exists ") + Sql_Table_Name_admin + QString(" ( id INTEGER primary key AUTOINCREMENT, ") + \
Sql_Field_Admin_U_No + QString(" varchar(30), ") + \
Sql_Field_Admin_Name + QString(" varchar(100), ") + \
Sql_Field_Admin_Nick + QString(" varchar(100), ") + \
Sql_Field_Admin_Pwd + QString(" varchar(30), ") + \
Sql_Field_Admin_Group+QString("_right") + QString(" int, ") + \
Sql_Field_Admin_Enable + QString(" int, ") + \
Sql_Field_Time_Add + QString(" int, ") + \
Sql_Field_Time_Update + QString(" int)");
/*step 2: sql语句对象。*/
sqlite3_stmt *pStmt;
int r = sqlite3_prepare_v2(
_pdb, //数据库连接对象
strsql.toUtf8().data(), //指向原始sql语句字符串
strlen(strsql.toUtf8().data()), //
&pStmt,
NULL
);
if (r != SQLITE_OK)
{
printf("%s\n", sqlite3_errmsg(_pdb));
perror("sqlite3_prepare_v2 error:");
return ;
}
else
{
/*2.2 执行SQL语句*/
r = sqlite3_step(pStmt);
//销毁一个SQL语句对象
sqlite3_finalize(pStmt);
// qDebug() << "success : Table created!";
if (m_bcreateadmin)
{
// 创建一个默认账户admin,666666
_stu_user user;
user.enable = 1;
user.group = 1;
user.name.append("admin");
QString strpwd("123456");
QByteArray byen;
byen.append(strpwd);
byen.append(QString::number(user.time_add));
QByteArray byout = ToolBase::enValue(byen);
QString strpwd_en;
strpwd_en.append(byout);
QUuid uuid = QUuid::createUuid();
user.u_no.append(uuid.toString());
user.pwd.append(strpwd_en);
std::shared_ptr<_stu_user> p = std::make_shared<_stu_user>();
*p = user;
do_user(ST_Add, p);
}
}
}
int mysqltool_lite::GetAllData(const QString & name_table, QList<QMap<QString, QString>> & lsttable)
{
QString select_all_sql = QString("select * from ") + name_table + QString(" order by ") + Sql_Field_ID + QString(" desc");
/*step 2: sql语句对象。*/
sqlite3_stmt *pStmt;
int r = sqlite3_prepare_v2(
_pdb, //数据库连接对象
select_all_sql.toUtf8().data(), //指向原始sql语句字符串
strlen(select_all_sql.toUtf8().data()), //
&pStmt,
NULL
);
if (r != SQLITE_OK)
{
printf("%s\n", sqlite3_errmsg(_pdb));
perror("sqlite3_prepare_v2 error:");
return -1;
}
QMap<int, QString> mapfileldname;
QMap<int, QString> mapfileldvalue;
int first = 1;
while (1)
{
int i;
/*2.2 执行SQL语句*/
r = sqlite3_step(pStmt);
if (r == SQLITE_DONE)
{
break;//执行完成
}
else if (r == SQLITE_ROW) //获取到一行的结果
{
int cNum = 0;//结果集中有多少列
cNum = sqlite3_column_count(pStmt);
if (first)
{
for (i = 0; i < cNum; i++)
{
//返回结果集中第i列的名字
const char *p = sqlite3_column_name(pStmt, i);
mapfileldname[i] = QString::fromLocal8Bit(p);
}
first = 0;
}
int iType; //第i列的数据类型
for (i = 0; i < cNum; i++)
{
mapfileldvalue[i] = QString::number(0);
//获取结果集中第i列的数据类型
iType = sqlite3_column_type(pStmt, i);
if (iType == SQLITE_INTEGER)
{
int iValue;
//获取结果集中第i列的数据值
iValue = sqlite3_column_int(pStmt, i);
mapfileldvalue[i] = QString::number(iValue);
}
else if (iType == SQLITE_FLOAT)
{
double iValue;
//获取结果集中第i列的数据值
iValue = sqlite3_column_double(pStmt, i);
mapfileldvalue[i] = QString::number(iValue, 'g');
}
else if (iType == SQLITE_TEXT)
{
const unsigned char * iValue;
//获取结果集中第i列的数据值
iValue = sqlite3_column_text(pStmt, i);
mapfileldvalue[i] = QString::fromUtf8((char*)iValue);
}
}
// 记录一条
QMap<QString, QString> mapvalue;
if (mapfileldname.size() == mapfileldvalue.size())
{
for (int index = 0; index < mapfileldname.size(); ++index)
{
mapvalue.insert(mapfileldname[index], mapfileldvalue[index]);
}
if (mapvalue.size())
lsttable.push_back(mapvalue);
if (lsttable.count() >= 2001)
break;
}
mapvalue.clear();
mapfileldvalue.clear();
}
}
//销毁一个SQL语句对象
sqlite3_finalize(pStmt);
return 0;
}
int mysqltool_lite::InsertData(const QString & name_table, const QMap<QString, QString> & mapFieldValue, const QMap<QString, _en_datatype> & mapFieldType)
{
if (mapFieldType.count() != mapFieldValue.count())
{
QByteArray byname;
byname.append(name_table);
byname.append(" , mapFieldType.count()");
byname.append(mapFieldType.count());
byname.append(" , mapFieldValue.count()");
byname.append(mapFieldValue.count());
gLogSet(LOG_DEBUG, byname);
}
Q_ASSERT(mapFieldType.count() == mapFieldValue.count());
// insert into 表名(字段名1,字段名2)values(值a1,值b1)
QString insert_sql = QString("insert into ") + name_table + QString("(");
foreach(QString field,mapFieldType.keys()) {
insert_sql.append(field);
insert_sql.append(",");
}
insert_sql.chop(1);
insert_sql.append(") values (");
foreach(QString field,mapFieldType.keys()) {
if (Type_string == mapFieldType[field])
{
insert_sql.append(QString("\'%1\'").arg(mapFieldValue[field]));
insert_sql.append(",");
}
else if (Type_int == mapFieldType[field])
{
insert_sql.append(QString("%1").arg(mapFieldValue[field].toInt()));
insert_sql.append(",");
}
}
insert_sql.chop(1);
insert_sql.append(")");
// qDebug() << "22";
// qDebug() << insert_sql;
/*step 2: sql语句对象。*/
sqlite3_stmt *pStmt;
int r = sqlite3_prepare_v2(
_pdb, //数据库连接对象
insert_sql.toUtf8().data(), //指向原始sql语句字符串
strlen(insert_sql.toUtf8().data()), //
&pStmt,
NULL
);
if (r != SQLITE_OK)
{
printf("%s\n", sqlite3_errmsg(_pdb));
perror("sqlite3_prepare_v2 error:");
return -1;
}
/*2.2 执行SQL语句*/
r = sqlite3_step(pStmt);
//销毁一个SQL语句对象
sqlite3_finalize(pStmt);
// qDebug() << " success : sql insert success :: ";
return 0;
}
int mysqltool_lite::UpdateData(const QString & name_table, const QString & where_field, const QString & where_value, _en_datatype where_type, QMap<QString, QString> & mapFieldValue, const QMap<QString, _en_datatype> & mapFieldType)
{
Q_ASSERT(mapFieldType.count() == mapFieldValue.count());
// UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing' WHERE LastName = 'Wilson'
QString insert_sql = QString("UPDATE ") + name_table + QString(" SET ");
foreach(QString field,mapFieldType.keys()) {
insert_sql.append(field);
insert_sql.append(" = ");
if (Type_string == mapFieldType[field])
{
insert_sql.append(QString("\'%1\'").arg(mapFieldValue[field]));
insert_sql.append(",");
}
else if (Type_int == mapFieldType[field])
{
insert_sql.append(QString("%1").arg(mapFieldValue[field].toInt()));
insert_sql.append(",");
}
}
insert_sql.chop(1);
if (where_field.count() && where_value.count())
{
insert_sql.append(" WHERE ");
if (Type_string == where_type)
{
insert_sql.append(where_field);
insert_sql.append("=");
insert_sql.append(QString("\'%1\'").arg(where_value));
}
else if (Type_int == where_type)
{
insert_sql.append(where_field);
insert_sql.append("=");
insert_sql.append(QString("%1").arg(where_value.toInt()));
}
}
/*step 2: sql语句对象。*/
sqlite3_stmt *pStmt;
int r = sqlite3_prepare_v2(
_pdb, //数据库连接对象
insert_sql.toUtf8().data(), //指向原始sql语句字符串
strlen(insert_sql.toUtf8().data()), //
&pStmt,
NULL
);
if (r != SQLITE_OK)
{
printf("%s\n", sqlite3_errmsg(_pdb));
perror("sqlite3_prepare_v2 error:");
return -1;
}
/*2.2 执行SQL语句*/
r = sqlite3_step(pStmt);
//销毁一个SQL语句对象
sqlite3_finalize(pStmt);
// qDebug() << " UpdateData : sql insert success :: ";
// qDebug() << insert_sql;
return 0;
}
int mysqltool_lite::DeleteData(const QString & name_table, const QString & where_field, const QString & where_value, _en_datatype where_type)
{
Q_ASSERT(where_field.count() && where_value.count());
QString insert_sql = QString("DELETE FROM ") + name_table + QString(" WHERE ");
if (Type_string == where_type)
{
insert_sql.append(where_field);
insert_sql.append("=");
insert_sql.append(QString("\'%1\'").arg(where_value));
}
else if (Type_int == where_type)
{
insert_sql.append(where_field);
insert_sql.append("=");
insert_sql.append(QString("%1").arg(where_value.toInt()));
}
/*step 2: sql语句对象。*/
sqlite3_stmt *pStmt;
int r = sqlite3_prepare_v2(
_pdb, //数据库连接对象
insert_sql.toUtf8().data(), //指向原始sql语句字符串
strlen(insert_sql.toUtf8().data()), //
&pStmt,
NULL
);
if (r != SQLITE_OK)
{
printf("%s\n", sqlite3_errmsg(_pdb));
perror("sqlite3_prepare_v2 error:");
return -1;
}
/*2.2 执行SQL语句*/
r = sqlite3_step(pStmt);
//销毁一个SQL语句对象
sqlite3_finalize(pStmt);
// qDebug() << " success sql DeleteData :: ";
// qDebug() << insert_sql;
return 0;
}
int mysqltool_lite::DeleteDataAll(const QString & name_table)
{
QString _sql = QString("DELETE FROM ") + name_table ;
/*step 2: sql语句对象。*/
sqlite3_stmt *pStmt;
int r = sqlite3_prepare_v2(
_pdb, //数据库连接对象
_sql.toUtf8().data(), //指向原始sql语句字符串
strlen(_sql.toUtf8().data()), //
&pStmt,
NULL
);
if (r != SQLITE_OK)
{
printf("%s\n", sqlite3_errmsg(_pdb));
perror("sqlite3_prepare_v2 error:");
return -1;
}
/*2.2 执行SQL语句*/
r = sqlite3_step(pStmt);
//销毁一个SQL语句对象
sqlite3_finalize(pStmt);
// qDebug() << " success sql DeleteData :: ";
// qDebug() << _sql;
return 0;
}
void mysqltool_lite::recv_cmd(stu_dodbcmd & cmd)
{
QString strlog = "run recv_cmd =>" + QString(";cmd.type_table=%1").arg(cmd.type_table) + QString(";cmd.type_method=%1").arg(cmd.type_method);
gLogSetS(LOG_DEBUG, strlog);
if (cmd.type_table == SF_User)
{
std::shared_ptr<_stu_user> p = std::dynamic_pointer_cast<_stu_user>(cmd.table);
do_user(cmd.type_method, p);
}
if (cmd.type_method == ST_Del)
cmd.table->bfree = true;
}
int mysqltool_lite::do_user(_en_SqlMethod type, std::shared_ptr<_stu_user> pitem)
{
QString name_table(Sql_Table_Name_admin);
QMap<QString, QString> mapFieldValue;
QMap<QString, _en_datatype> mapFieldType;
mapFieldValue[Sql_Field_Admin_U_No] = pitem->u_no;
mapFieldValue[Sql_Field_Admin_Name] = pitem->name;
mapFieldValue[Sql_Field_Admin_Nick] = pitem->nick;
mapFieldValue[Sql_Field_Admin_Pwd] = pitem->pwd;
mapFieldValue[Sql_Field_Time_Add] = QString::number(pitem->time_add);
mapFieldValue[Sql_Field_Time_Update] = QString::number(pitem->time_update);
mapFieldValue[Sql_Field_Admin_Group + QString("_right")] = QString::number(pitem->group);
mapFieldType[Sql_Field_Admin_U_No] = Type_string;
mapFieldType[Sql_Field_Admin_Name] = Type_string;
mapFieldType[Sql_Field_Admin_Nick] = Type_string;
mapFieldType[Sql_Field_Admin_Pwd] = Type_string;
mapFieldType[Sql_Field_Time_Add] = Type_int;
mapFieldType[Sql_Field_Time_Update] = Type_int;
mapFieldType[Sql_Field_Admin_Group + QString("_right")] = Type_int;
// 加密密码
QString strpwd(pitem->pwd);
//QByteArray byen;
//byen.append(strpwd);
//byen.append(QString::number(pitem->time_add));
//QByteArray byout = ToolBase::enValue(byen);
//QString strpwd_en;
//strpwd_en.append(byout);
//mapFieldValue[Sql_Field_Admin_Pwd] = strpwd_en;
if (type == ST_Add)
{
InsertData(name_table, mapFieldValue, mapFieldType);
}else if (type == ST_Update)
{
UpdateData(name_table, Sql_Field_Admin_U_No, pitem->u_no, Type_string, mapFieldValue, mapFieldType);
}
else if (type == ST_Del)
{
DeleteData(name_table, Sql_Field_Admin_U_No, pitem->u_no, Type_string);
}
return 0;
}
说明:
以上为示例代码,调用sqlite的API进行创建、查、读写、删除操作。未考虑性能。
问题指正请联系修改:
个人邮箱: 1018840656@qq.com