logo头像

技术引领生活!

再谈Qt数据库

以前写的Qt数据退出时提示异常的情况,经历时间的摧残,再战数据库

多线程支持

一个函数在不同线程中调用,可通过 QThread::currentThread()来区分
所以可声明一个单例函数

1
static QSqlDatabase dataBase();

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
QSqlDatabase DataBaseManager::dataBase()
{
QString name = QString::number((quint64)QThread::currentThread(), 16);

//如果已经连接并且数据库名没有变化,则返回
if(QSqlDatabase::contains(name)){
QSqlDatabase db = QSqlDatabase::database(name);
if(db.databaseName() == m_dbName){
return db;
}
}

{
closeDataBase(name);

auto db = QSqlDatabase::addDatabase(m_driver, name);

db.setHostName(m_host);
db.setPort(m_port);
db.setUserName(m_userName);
db.setPassword(m_passWord);
db.setDatabaseName(m_dbName);

if (!db.open()) {
//关闭数据库,必须这样..
closeDataBase(name);
qDebug() << "数据库打开失败!" << m_dbName << name;
}
else{
qDebug() << QString("数据库%1打开成功, 连接名: %2").arg(m_dbName).arg(name);
}

emit DataBaseManager::instance()->databaseTableChanged(db.tables());
return db;
}
}

信号发送

声明一个静态单例模式函数,然后调用即可

1
static DataBaseManager* instance();

在需要发射信号的地方

1
emit DataBaseManager::instance()->databaseTableChanged(db.tables());

1
2
3
4
5
DataBaseManager *DataBaseManager::instance()
{
static DataBaseManager qinstance;
return &qinstance;
}

事务支持

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
bool DataBaseManager::startTransaction()
{
//开启事务
if(dataBase().isOpen()
&& dataBase().driver()->hasFeature(QSqlDriver::Transactions)){
//QMutexLocker locker(&m_mutex);
if(!m_isTransactioned){
bool ok = dataBase().transaction();
if(ok){
m_isTransactioned = true;
}
else{
qDebug() << "transaction失败";
}

return ok;
}

}

return true;
}

bool DataBaseManager::commit()
{
//开启事务
if(dataBase().isOpen() && dataBase().driver()->hasFeature(QSqlDriver::Transactions)){
if(m_isTransactioned){
bool ok = dataBase().commit();

if(ok){
m_isTransactioned = false;
}
else{
qDebug() << "commit 失败!";
}
return ok;
}
}

return true;
}

调用

使用QSqlQuery执行sql语句的时,需要在构造函数中传入一个数据库

1
QSqlQuery query(DataBaseManager::dataBase());

执行sql文件

一般情况下,数据库不支持多行模式,特别有些文件还有注释等,所以执行sql文件的核心思想就是解析文本文件,去掉注释变成多次单行模式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
//该函数从开源软件中扒拉出来的
QStringList DataBaseManager::quickSplitQueries(const QString& sql, bool keepEmptyQueries, bool removeComments)
{
QChar c;
bool inString = false;
bool inMultiLineComment = false;
bool inSingleLineComment = false;
QStringList queries;
QString query;
QString trimmed;

for (int i = 0, total = sql.size(); i < total; ++i){
c = sql[i];

// String
if (inString){
query += c;
if (c == '\''){
inString = false;
}
continue;
}

// One-line comment
if (inSingleLineComment){
if (!removeComments)
query += c;

if (c == '\r' && (i + 1) < total && sql[i+1] == '\n'){
if (!removeComments)
query += '\n';

i++;
inSingleLineComment = false;
}
else if (c == '\n' || c == '\r')
inSingleLineComment = false;

continue;
}

// Multi-line comment
if (inMultiLineComment){
if (!removeComments)
query += c;

if (c == '*' && (i + 1) < total && sql[i+1] == '/'){
if (!removeComments)
query += '/';

i++;
inMultiLineComment = false;
}

continue;
}

// Everything rest
if (c == '\''){
query += c;
inString = true;
}
else if (c == '-' && (i + 1) < total && sql[i+1] == '-'){
inSingleLineComment = true;
i++;
if (!removeComments)
query += "--";
}
else if (c == '/' && (i + 1) < total && sql[i+1] == '*'){
inMultiLineComment = true;
i++;
if (!removeComments)
query += "/*";
}
else if (c == ';'){
query += c;
if (keepEmptyQueries || (!(trimmed = query.trimmed()).isEmpty() && trimmed != ";"))
queries << query;

query.clear();
}
else{
query += c;
}
}

if (!query.isNull() && (!(trimmed = query.trimmed()).isEmpty() && trimmed != ";"))
queries << query;

return queries;
}

支付宝打赏 微信打赏

您的支持是我前行的动力!