Sqlite3 数据库操作 基于Qt

功能:

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

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值