qt操作带密码的sqlite数据库

c++代码诗人

于 2024-08-13 11:34:42 发布

阅读量24

点赞数 1

分类专栏： QT 文章标签： qt

本文链接：https://blog.csdn.net/lydstory123/article/details/141159912

版权

QT 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

#ifndef SQLITE_DB_H
#define SQLITE_DB_H
#include <string>
using namespace std;
#include <QString>
#include <QObject>
#include <QtSql/QSqlDatabase>
#include <QVariant>
#include <QtSql/QSqlQuery>

extern "C" {
#include <sqlite/sqlite3.h>

}

class Sqlite_DB : public QObject
{
	Q_OBJECT

public:
	Sqlite_DB(QObject *parent = 0);
	~Sqlite_DB();
	void sql_db_open();
	void db_close();
	//建表
	void db_createtable();
	void db_initdata();
	//查询登录用户在数据库中是否存在
	bool db_is_usr_exist(const QString &account, const QString &psd = QString());

	//oprecord
	bool db_insert_oprecord(const QString& OpType, const  QString& OpObject, const QString& OpResult, const QString& OpOperator);

protected:
	//系统temp路径
	std::string System_temp_path;
	//错误文件路径
	std::string log_path = "./log/";
 
private:
	sqlite3* conn;
};

#endif // SQLITE_DB_H

#include "sqlite_db.h"
#include "PublicFunction.h"
#include <QDir>
#include <QVector>
#include <QDebug>
#include <QUuid>
Sqlite_DB::Sqlite_DB(QObject *parent)
	: QObject(parent)
{
	 
	if (!getSystemTempPath(System_temp_path))
	{
		log_path = System_temp_path + "\\FontDetect\\log\\";
		std::string RG_path = System_temp_path + "\\FontDetect\\report\\";
		FilePathCheck(RG_path); //检查文件路径，保证存在
	}
	else
	{
		FilePathCheck(log_path);
	}
 
}

Sqlite_DB::~Sqlite_DB()
{

}
void Sqlite_DB::sql_db_open()
{
	 
	QString path = QDir::currentPath() + "/sufeiSQLite.db";

	 

	int result = sqlite3_open(ws2s(path.toStdWString()).c_str(), &conn);
 	if (result != SQLITE_OK)
	{
		sqlite3_close(conn);
		return;
	}
//	sqlite3_key(conn, "1q2w3e4r", 8);
	sqlite3_key(conn, "1q2w3e4r", 8);
	sqlite3_rekey(conn, "1q2w3e4r", 8);
}
void Sqlite_DB::db_close()
{
	sqlite3_close(conn);
	conn = NULL;
}

void Sqlite_DB::db_createtable()
{
	char **zErrMsg = NULL;
	QString sql = "CREATE TABLE SYS_UserInfo ("
		" id varchar(64) NOT NULL,"
		" UserID varchar(255),"
		" UserName varchar(200),"
		" Password varchar(200),"
		" RealName varchar(200),"
		" RoleID varchar(36),"
		" PRIMARY KEY (id));";
	int nResult = sqlite3_exec(conn, ws2s(sql.toStdWString()).c_str(), NULL, NULL, zErrMsg);
	if (nResult != SQLITE_OK)
	{
#ifndef CODE_DEBUG
		//   qDebug() << "SQL = " << qstring2char(sql) << endl;
#endif

		sqlite3_free(zErrMsg);
	}

	QString sql2 = "CREATE TABLE User_Func ("
		" id varchar(64) NOT NULL,"
		" UserName varchar(200),"
		" MarkName varchar(200),"
		"PRIMARY KEY (id));";
	int nResult2 = sqlite3_exec(conn, ws2s(sql2.toStdWString()).c_str(), NULL, NULL, zErrMsg);
	if (nResult2 != SQLITE_OK)
	{
		sqlite3_free(zErrMsg);
	}

	QString sql3 = "CREATE TABLE OprLog ("
		" id varchar(64) NOT NULL,"
		" OpType varchar(200),"
		" OpObject varchar(200),"
		" OpResult varchar(200),"
		" OpTime DATETIME(36),"
		" OpOperator varchar(200),"

		"PRIMARY KEY (id));";
	int nResult3 = sqlite3_exec(conn, ws2s(sql3.toStdWString()).c_str(), NULL, NULL, zErrMsg);
	if (nResult3 != SQLITE_OK)
	{
		sqlite3_free(zErrMsg);
	}

}

void Sqlite_DB::db_initdata()
{
	char **zErrMsg = NULL;
	QString insertsql = "INSERT INTO SYS_UserInfo(id,UserID, UserName, Password,RealName,RoleID) VALUES ('1','123','lyd','123','liyaodong','1');";
	int nResult = sqlite3_exec(conn, ws2s(insertsql.toStdWString()).c_str(), NULL, NULL, zErrMsg);
	if (nResult != SQLITE_OK)
	{
#ifndef CODE_DEBUG
		qDebug() << "SQL = " << ws2s(insertsql.toStdWString()).c_str() << endl;
#endif

		sqlite3_free(zErrMsg);
	}

	QString insertsql2 = "INSERT INTO SYS_UserInfo(id,UserID, UserName, Password,RealName,RoleID) VALUES ('2','123','sysadmin','123','sysadmin','1');";
	int nResult2 = sqlite3_exec(conn, ws2s(insertsql2.toStdWString()).c_str(), NULL, NULL, zErrMsg);
	if (nResult2 != SQLITE_OK)
	{
#ifndef CODE_DEBUG
		qDebug() << "SQL = " << ws2s(insertsql2.toStdWString()).c_str() << endl;
#endif

		sqlite3_free(zErrMsg);
	}
	//secadmin
	QString insertsql3 = "INSERT INTO SYS_UserInfo(id,UserID, UserName, Password,RealName,RoleID) VALUES ('3','123','secadmin','123','secadmin','2');";
	int nResult3 = sqlite3_exec(conn, ws2s(insertsql3.toStdWString()).c_str(), NULL, NULL, zErrMsg);
	if (nResult3 != SQLITE_OK)
	{
#ifndef CODE_DEBUG
		qDebug() << "SQL = " << ws2s(insertsql3.toStdWString()).c_str() << endl;
#endif

		sqlite3_free(zErrMsg);
	}

	QString insertsql4 = "INSERT INTO SYS_UserInfo(id,UserID, UserName, Password,RealName,RoleID) VALUES ('4','123','audadmin','123','audadmin','3');";
	int nResult4 = sqlite3_exec(conn, ws2s(insertsql4.toStdWString()).c_str(), NULL, NULL, zErrMsg);
	if (nResult4 != SQLITE_OK)
	{
#ifndef CODE_DEBUG
		qDebug() << "SQL = " << ws2s(insertsql4.toStdWString()).c_str() << endl;
#endif

		sqlite3_free(zErrMsg);
	}
	QString insertsql5 = "INSERT INTO SYS_UserInfo(id,UserID, UserName, Password,RealName,RoleID) VALUES ('5','123','print','123','print','4');";
	int nResult5 = sqlite3_exec(conn, ws2s(insertsql5.toStdWString()).c_str(), NULL, NULL, zErrMsg);
	if (nResult5 != SQLITE_OK)
	{
#ifndef CODE_DEBUG
		qDebug() << "SQL = " << ws2s(insertsql5.toStdWString()).c_str() << endl;
#endif

		sqlite3_free(zErrMsg);
	}

	QString insertsql6 = "INSERT INTO SYS_UserInfo(id,UserID, UserName, Password,RealName,RoleID) VALUES ('6','123','detect','123','detect','0');";
	int nResult6 = sqlite3_exec(conn, ws2s(insertsql6.toStdWString()).c_str(), NULL, NULL, zErrMsg);
	if (nResult6 != SQLITE_OK)
	{
#ifndef CODE_DEBUG
		qDebug() << "SQL = " << ws2s(insertsql6.toStdWString()).c_str() << endl;
#endif

		sqlite3_free(zErrMsg);
	}

	//User_Func

	QString insertsql7 = "INSERT INTO User_Func(id,UserName,MarkName) VALUES ('1','sysadmin','OneInspect');";
	int nResult7 = sqlite3_exec(conn, ws2s(insertsql7.toStdWString()).c_str(), NULL, NULL, zErrMsg);
	if (nResult7 != SQLITE_OK)
	{
#ifndef CODE_DEBUG
		qDebug() << "SQL = " << ws2s(insertsql7.toStdWString()).c_str() << endl;
#endif

		sqlite3_free(zErrMsg);
	}

	QString insertsql8 = "INSERT INTO User_Func(id,UserName,MarkName) VALUES ('2','sysadmin','TwoInspect');";
	int nResult8 = sqlite3_exec(conn, ws2s(insertsql8.toStdWString()).c_str(), NULL, NULL, zErrMsg);
	if (nResult8 != SQLITE_OK)
	{
		sqlite3_free(zErrMsg);
	}
	QString insertsql9 = "INSERT INTO User_Func(id,UserName,MarkName) VALUES ('3','sysadmin','ThreeInspect');";
	int nResult9 = sqlite3_exec(conn, ws2s(insertsql9.toStdWString()).c_str(), NULL, NULL, zErrMsg);
	if (nResult9 != SQLITE_OK)
	{


		sqlite3_free(zErrMsg);
	}

	QString insertsql10 = "INSERT INTO User_Func(id,UserName,MarkName) VALUES ('4','secadmin','TwoInspect');";
	int nResult10 = sqlite3_exec(conn, ws2s(insertsql10.toStdWString()).c_str(), NULL, NULL, zErrMsg);
	if (nResult10 != SQLITE_OK)
	{
#ifndef CODE_DEBUG
		qDebug() << "SQL = " << ws2s(insertsql10.toStdWString()).c_str() << endl;
#endif

		sqlite3_free(zErrMsg);
	}

	QString insertsql11 = "INSERT INTO User_Func(id,UserName,MarkName) VALUES ('5','audadmin','OneInspect');";
	int nResult11 = sqlite3_exec(conn, ws2s(insertsql11.toStdWString()).c_str(), NULL, NULL, zErrMsg);
	if (nResult11 != SQLITE_OK)
	{
#ifndef CODE_DEBUG
		qDebug() << "SQL = " << ws2s(insertsql11.toStdWString()).c_str() << endl;
#endif

		sqlite3_free(zErrMsg);
	}


}
bool Sqlite_DB::db_insert_oprecord(const QString& OpType, const  QString& OpObject, const QString& OpResult, const QString& OpOperator)
{
	char **zErrMsg = NULL;
	QString id = QUuid::createUuid().toString();
	QString sql = QString("INSERT INTO OprLog(id,OpType,OpObject,OpResult,OpTime,OpOperator)"
		"VALUES('%1','%2','%3','%4',DATETIME('now','localtime'),'%5')"
		).arg(id).arg(OpType).arg(OpObject).arg(OpResult).arg(OpOperator);
	qDebug() << sql;
	int nResult = sqlite3_exec(conn, ws2s(sql.toStdWString()).c_str(), NULL, NULL, zErrMsg);
	if (nResult != SQLITE_OK)
	{
		return false;
	}
	return true;
}


bool Sqlite_DB::db_is_usr_exist(const QString &account, const QString &psd)
{
	int row = 0, col = 0;
	char **result;
	char *zErrMsg = NULL;

	QString sql;
	if (psd.isEmpty() || account.isEmpty())
	{
		return false;
	}
	else
	{
		sql = QString::fromUtf8("SELECT COUNT(*) FROM SYS_UserInfo WHERE UserName='%1' AND Password='%2'").arg(account).arg(psd);
	}
	int rc = sqlite3_get_table(conn, ws2s(sql.toStdWString()).c_str(), &result, &row, &col, &zErrMsg);
	if (rc != SQLITE_OK)
	{
		/// QMessageBox::warning(this,"警告", "查询用户失败！");
	}

	if (QString::fromUtf8(result[col]).toInt() > 0)
	{
		return true;
	}
	sqlite3_free_table(result);
	return false;
}