Qt æä¾äº QtSql 模åæ¥æä¾å¹³å°ç¬ç«çåºäº SQL çæ°æ®åºæä½ãè¿éæ们æ说çâå¹³å°ç¬ç«âï¼æ¢å
æ¬æä½ç³»ç»å¹³å°ï¼æå
æ¬å个æ°æ®åºå¹³å°ãå¦å¤ï¼æ们强è°äºâåºäº SQLâï¼å 为 NoSQL æ°æ®åºè³ä»æ²¡æä¸ä¸ªéç¨æ¥è¯¢æ¹æ³ï¼æ以ä¸å¯è½æä¾ä¸ç§éç¨ç NoSQL æ°æ®åºçæä½ãQt çæ°æ®åºæä½è¿å¯ä»¥å¾æ¹ä¾¿çä¸ model/view æ¶æè¿è¡æ´åãé常æ¥è¯´ï¼æ们对æ°æ®åºçæä½æ´å¤å°å¨äºå¯¹æ°æ®åºè¡¨çæä½ï¼èè¿æ£æ¯ model/view æ¶æçé¿é¡¹ã
Qt 使ç¨QSqlDatabase表示ä¸ä¸ªæ°æ®åºè¿æ¥ãæ´åºå±ä¸ï¼Qt 使ç¨é©±å¨ï¼driversï¼æ¥ä¸ä¸åçæ°æ®åº API è¿è¡äº¤äºãQt æ¡é¢çæ¬æä¾äºå¦ä¸å ç§é©±å¨ï¼
é©±å¨ æ°æ®åº
QDB2 IBM DB2 (7.1 ææ´æ°çæ¬)
QIBASE Borland InterBase
QMYSQL MySQL
QOCI Oracle Call Interface Driver
QODBC Open Database Connectivity (ODBC) â Microsoft SQL Server åå
¶å®å
¼å®¹ ODBC çæ°æ®åº
QPSQL PostgreSQL (7.3 ææ´æ°çæ¬)
QSQLITE2 SQLite 2
QSQLITE SQLite 3
QSYMSQL é对 Symbian å¹³å°çSQLite 3
QTDS Sybase Adaptive Server (èª Qt 4.7 èµ·åºé¤)
ä¸è¿ï¼ç±äºåå°åè®®çéå¶ï¼Qt å¼æºçæ¬å¹¶æ²¡ææä¾ä¸é¢ææ驱å¨çäºè¿å¶çæ¬ï¼èä»
ä»
以æºä»£ç çå½¢å¼æä¾ãé常ï¼Qt åªé»è®¤æè½½ QSqlite 驱å¨ï¼è¿ä¸ªé©±å¨å®é
è¿å
æ¬ Sqlite æ°æ®åºï¼ä¹å°±æ¯è¯´ï¼å¦æéè¦ä½¿ç¨ Sqlite çè¯ï¼åªéè¦è¯¥é©±å¨å³å¯ï¼ãæ们å¯ä»¥éæ©æè¿äºé©±å¨ä½ä¸º Qt çä¸é¨åè¿è¡ç¼è¯ï¼ä¹å¯ä»¥å½ä½æ件ç¼è¯ã
å¦æä¹ æ¯äºä½¿ç¨ SQL è¯å¥ï¼æ们å¯ä»¥éæ©QSqlQueryç±»ï¼å¦æåªéè¦ä½¿ç¨é«å±æ¬¡çæ°æ®åºæ¥å£ï¼ä¸å
³å¿ SQL è¯æ³ï¼ï¼æ们å¯ä»¥éæ©QSqlTableModelåQSqlRelationalTableModelãæ们åªä»ç»QSqlQueryç±»ç使ç¨ã
å¨ä½¿ç¨æ¶ï¼æ们å¯ä»¥éè¿
QSqlDatabase::drivers();
1
æ¾å°ç³»ç»ä¸ææå¯ç¨çæ°æ®åºé©±å¨çååå表ãæ们åªè½ä½¿ç¨åºç°å¨å表ä¸ç驱å¨ãç±äºé»è®¤æ
åµä¸ï¼QtSql æ¯ä½ä¸º Qt çä¸ä¸ªæ¨¡åæä¾çã为äºä½¿ç¨æå
³æ°æ®åºçç±»ï¼æ们å¿
é¡»æ© .pro æ件ä¸æ·»å è¿ä¹ä¸å¥ï¼
QT += sql
1
è¿è¡¨ç¤ºï¼æ们çç¨åºéè¦ä½¿ç¨ Qt ç coreãgui 以å sql ä¸ä¸ªæ¨¡åã注æï¼å¦æéè¦åæ¶ä½¿ç¨ Qt4 å Qt5 ç¼è¯ç¨åºï¼é常æ们ç .pro æ件æ¯è¿æ ·çï¼
QT += core gui sql
greaterThan(QT_MAJOR_VERSION, 4): QT += widgets
1
2
è¿ä¸¤å¥ä¹å¾æç¡®ï¼Qt éè¦å è½½ coreãgui å sql ä¸ä¸ªæ¨¡åï¼å¦æ主æ¿æ¬å¤§äº 4ï¼ååæ·»å widgets 模åã
ä¸é¢æ¥çä¸ä¸ªç®åçå½æ°ï¼
bool connect(const QString &dbName)
{
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
// db.setHostName("host");
// db.setDatabaseName("dbname");
// db.setUserName("username");
// db.setPassword("password");
db.setDatabaseName(dbName);
if (!db.open()) {
QMessageBox::critical(0, QObject::tr("Database Error"),
db.lastError().text());
return false;
}
return true;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
æ们使ç¨connect()å½æ°å建ä¸ä¸ªæ°æ®åºè¿æ¥ãæ们使ç¨QSqlDatabase::addDatabase()éæå½æ°å®æè¿ä¸è¯·æ±ï¼ä¹å°±æ¯å建äºä¸ä¸ªQSqlDatabaseå®ä¾ã注æï¼æ°æ®åºè¿æ¥ä½¿ç¨èªå·±çååè¿è¡åºåï¼èä¸æ¯æ°æ®åºçååãä¾å¦ï¼æ们å¯ä»¥ä½¿ç¨ä¸é¢çè¯å¥ï¼
QSqlDatabase db=QSqlDatabase::addDatabase("QSQLITE", QString("con%1").arg(dbName));
1
æ¤æ¶ï¼æ们æ¯ä½¿ç¨addDatabase()å½æ°ç第äºä¸ªåæ°æ¥ç»è¿ä¸ªæ°æ®åºè¿æ¥ä¸ä¸ªååãå¨è¿ä¸ªä¾åä¸ï¼ç¨äºåºåè¿ä¸ªæ°æ®åºè¿æ¥çååæ¯QString(âconn%1â).arg(dbName)ï¼èä¸æ¯ âQSQLITEâãè¿ä¸ªåæ°æ¯å¯éçï¼å¦æä¸æå®ï¼ç³»ç»ä¼ç»åºä¸ä¸ªé»è®¤çååQSqlDatabase::defaultConnectionï¼æ¤æ¶ï¼Qt ä¼å建ä¸ä¸ªé»è®¤çè¿æ¥ãå¦æä½ ç»åºçååä¸å·²åå¨çååç¸åï¼æ°çè¿æ¥ä¼æ¿æ¢æå·²æçè¿æ¥ãéè¿è¿ç§è®¾è®¡ï¼æ们å¯ä»¥ä¸ºä¸ä¸ªæ°æ®åºå»ºç«å¤ä¸ªè¿æ¥ã
æ们è¿é使ç¨çæ¯ sqlite æ°æ®åºï¼åªéè¦æå®æ°æ®åºååå³å¯ãå¦ææ¯æ°æ®åºæå¡å¨ï¼æ¯å¦ MySQLï¼æ们è¿éè¦æå®ä¸»æºåã端å£å·ãç¨æ·ååå¯ç ï¼è¿äºè¯å¥ä½¿ç¨æ³¨éè¿è¡äºç®åç说æã
æ¥ä¸æ¥æ们è°ç¨äºQSqlDatabase::open()å½æ°ï¼æå¼è¿ä¸ªæ°æ®åºè¿æ¥ãéè¿æ£æ¥open()å½æ°çè¿åå¼ï¼æ们å¯ä»¥å¤ææ°æ®åºæ¯ä¸æ¯æ£ç¡®æå¼ã
QtSql 模åä¸ç类大å¤å
·ælastError()å½æ°ï¼ç¨äºæ£æ¥ææ°åºç°çé误ãå¦æä½ åç°æ°æ®åºæä½æä»»ä½é®é¢ï¼åºè¯¥ä½¿ç¨è¿ä¸ªå½æ°è¿è¡é误çæ£æ¥ãè¿ä¸ç¹æ们ä¹å¨ä¸é¢ç代ç ä¸è¿è¡äºä½ç°ãå½ç¶ï¼è¿åªæ¯æç®åçå®ç°ï¼ä¸è¬æ¥è¯´ï¼æ´å¥½ç设计æ¯ï¼ä¸è¦å¨æ°æ®åºæä½ä¸æ··æçé¢ä»£ç ï¼å¹¶ä¸å°è¿ä¸ªconnect()å½æ°æ¾å¨ä¸ä¸ªä¸é¨çæ°æ®åºæä½ç±»ä¸ï¼ã
æ¥ä¸æ¥æ们å¯ä»¥å¨main()å½æ°ä¸ä½¿ç¨è¿ä¸ªconnect()å½æ°ï¼
int main(int argc, char *argv[])
{
QApplication a(argc, argv);
if (connect("demo.db")) {
QSqlQuery query;
if (!query.exec("CREATE TABLE student ("
"id INTEGER PRIMARY KEY AUTOINCREMENT,"
"name VARCHAR,"
"age INT)")) {
QMessageBox::critical(0, QObject::tr("Database Error"),
query.lastError().text());
return 1;
}
} else {
return 1;
}
return a.exec();
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
main()å½æ°ä¸ï¼æ们è°ç¨è¿ä¸ªconnect()å½æ°æå¼æ°æ®åºãå¦ææå¼æåï¼æ们éè¿ä¸ä¸ªQSqlQueryå®ä¾æ§è¡äº SQL è¯å¥ï¼å°±æ¯query.exec();ãåæ ·ï¼æ们使ç¨å
¶lastError()å½æ°æ£æ¥äºæ§è¡ç»ææ¯å¦æ£ç¡®ã
注æè¿éçQSqlQueryå®ä¾çå建ãæ们并没ææå®æ¯ä¸ºåªä¸ä¸ªæ°æ®åºè¿æ¥å建æ¥è¯¢å¯¹è±¡ï¼æ¤æ¶ï¼ç³»ç»ä¼ä½¿ç¨é»è®¤çè¿æ¥ï¼ä¹å°±æ¯ä½¿ç¨æ²¡æ第äºä¸ªåæ°çaddDatabase()å½æ°å建çé£ä¸ªè¿æ¥ï¼å
¶å®å°±æ¯åå为QSqlDatabase::defaultConnectionçé»è®¤è¿æ¥ï¼ãå¦æ没æè¿ä¹ä¸ä¸ªè¿æ¥ï¼ç³»ç»å°±ä¼æ¥éãä¹å°±æ¯è¯´ï¼å¦æ没æé»è®¤è¿æ¥ï¼æ们å¨å建QSqlQuery对象æ¶å¿
é¡»æææ¯åªä¸ä¸ªQSqlDatabase对象ï¼ä¹å°±æ¯addDatabase()çè¿åå¼ã
æ们è¿å¯ä»¥éè¿ä½¿ç¨QSqlQuery::isActive()å½æ°æ£æ¥è¯å¥æ§è¡æ£ç¡®ä¸å¦ãå¦æQSqlQuery对象æ¯æ´»å¨çï¼è¯¥å½æ°è¿å trueãæè°âæ´»å¨âï¼å°±æ¯æ该对象æåæ§è¡äºexec()å½æ°ï¼ä½æ¯è¿æ²¡æå®æãå¦æéè¦è®¾ç½®ä¸ºä¸æ´»å¨çï¼å¯ä»¥ä½¿ç¨finish()æè
clear()å½æ°ï¼æè
ç´æ¥éæ¾æè¿ä¸ªQSqlQuery对象ãè¿ééè¦æ³¨æçæ¯ï¼å¦æåå¨ä¸ä¸ªæ´»å¨ç SELECT è¯å¥ï¼æäºæ°æ®åºç³»ç»ä¸è½æåå®æconnect()æè
rollback()å½æ°çè°ç¨ãæ¤æ¶ï¼æ们å¿
é¡»é¦å
å°æ´»å¨ç SELECT è¯å¥è®¾ç½®æä¸æ´»å¨çã
å建è¿æ°æ®åºè¡¨ student ä¹åï¼æ们å¼å§æå
¥æ°æ®ï¼ç¶åå°å
¶ç¬ååºæ¥ï¼
if (connect("demo.db")) {
QSqlQuery query;
query.prepare("INSERT INTO student (name, age) VALUES (?, ?)");
QVariantList names;
names << "Tom" << "Jack" << "Jane" << "Jerry";
query.addBindValue(names);
QVariantList ages;
ages << 20 << 23 << 22 << 25;
query.addBindValue(ages);
if (!query.execBatch()) {
QMessageBox::critical(0, QObject::tr("Database Error"),
query.lastError().text());
}
query.finish();
query.exec("SELECT name, age FROM student");
while (query.next()) {
QString name = query.value(0).toString();
int age = query.value(1).toInt();
qDebug() << name << ": " << age;
}
} else {
return 1;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
ä¾æ§è¿æ¥å°æ们å建ç demo.db æ°æ®åºãæ们éè¦æå
¥å¤æ¡æ°æ®ï¼æ¤æ¶å¯ä»¥ä½¿ç¨QSqlQuery::exec()å½æ°ä¸æ¡ä¸æ¡æå
¥æ°æ®ï¼ä½æ¯è¿éæ们éæ©äºå¦å¤ä¸ç§æ¹æ³ï¼æ¹éæ§è¡ãé¦å
ï¼æ们使ç¨QSqlQuery::prepare()å½æ°å¯¹è¿æ¡ SQL è¯å¥è¿è¡é¢å¤çï¼é®å· ? ç¸å½äºå ä½ç¬¦ï¼é¢ç¤ºç以åæ们å¯ä»¥ä½¿ç¨å®é
æ°æ®æ¿æ¢è¿äºä½ç½®ãç®å说æä¸ä¸ï¼é¢å¤çæ¯æ°æ®åºæä¾çä¸ç§ç¹æ§ï¼å®ä¼å° SQL è¯å¥è¿è¡ç¼è¯ï¼æ§è½åå®å
¨æ§é½è¦ä¼äºæ®éç SQL å¤çãå¨ä¸é¢ç代ç ä¸ï¼æ们使ç¨ä¸ä¸ªå符串å表 names æ¿æ¢æ第ä¸ä¸ªé®å·çä½ç½®ï¼ä¸ä¸ªæ´åå表 ages æ¿æ¢æ第äºä¸ªé®å·çä½ç½®ï¼å©ç¨QSqlQuery::addBindValue()æ们å°å®é
æ°æ®ç»å®å°è¿ä¸ªé¢å¤çç SQL è¯å¥ä¸ãéè¦æ³¨æçæ¯ï¼names å ages è¿ä¸¤ä¸ªå表éé¢çæ°æ®éè¦ä¸ä¸å¯¹åºãç¶åæ们è°ç¨QSqlQuery::execBatch()æ¹éæ§è¡ SQLï¼ä¹åç»æ该对象ãè¿æ ·ï¼æå
¥æä½ä¾¿å®æäºã
å¦å¤è¯´æä¸ç¹ï¼æ们è¿é使ç¨äº ODBC é£æ ¼ç ? å ä½ç¬¦ï¼åæ ·ï¼æ们ä¹å¯ä»¥ä½¿ç¨ Oracle é£æ ¼çå ä½ç¬¦ï¼
query.prepare("INSERT INTO student (name, age) VALUES (:name, :age)");
1
æ¤æ¶ï¼æ们就éè¦ä½¿ç¨
query.bindValue(":name", names);
query.bindValue(":age", ages);
1
2
è¿è¡ç»å®ãOracle é£æ ¼çç»å®æ大ç好å¤æ¯ï¼ç»å®çåååå¼å¾æ¸
æ°ï¼ä¸é¡ºåºæ å
³ãä½æ¯è¿ééè¦æ³¨æï¼bindValue()å½æ°åªè½ç»å®ä¸ä¸ªä½ç½®ãæ¯å¦
query.prepare("INSERT INTO test (name1, name2) VALUES (:name, :name)");
// ...
query.bindValue(":name", name);
1
2
3
åªè½ç»å®ç¬¬ä¸ä¸ª :name å ä½ç¬¦ï¼ä¸è½ç»å®å°ç¬¬äºä¸ªã
æ¥ä¸æ¥æ们ä¾æ§ä½¿ç¨åä¸ä¸ªæ¥è¯¢å¯¹è±¡æ§è¡ä¸ä¸ª SELECT è¯å¥ãå¦æåå¨æ¥è¯¢ç»æï¼QSqlQuery::next()ä¼è¿å trueï¼ç´å°å°è¾¾ç»æææ«ï¼è¿å falseï¼è¯´æéåç»æãæ们å©ç¨è¿ä¸ç¹ï¼ä½¿ç¨ while 循ç¯å³å¯éåæ¥è¯¢ç»æã使ç¨QSqlQuery::value()å½æ°å³å¯æç
§ SELECT è¯å¥çå段顺åºè·åå°å¯¹åºçæ°æ®åºåå¨çæ°æ®ã
对äºæ°æ®åºäºå¡çæä½ï¼æ们å¯ä»¥ä½¿ç¨ QSqlDatabase::transaction() å¼å¯äºå¡ï¼QSqlDatabase::commit() æè
QSqlDatabase::rollback() ç»æäºå¡ã使ç¨QSqlDatabase::database()å½æ°åå¯ä»¥æ ¹æ®ååè·åæéè¦çæ°æ®åºè¿æ¥ã
温馨提示:答案为网友推荐,仅供参考