Connecting to Databases
为了使用
QSqlQuery
orQSqlQueryModel
访问数据库,需要创建并打开一个或多个database connections. Database connections通常由connection name标识,而不是数据库名字。QSqlDatabase
提供了默认的不具名的connection。如果你的应用只需要一个connection,那么创建一个默认的connection是很方便的。
创建connection和打开它是有区别的。创建connection只是创建了一个QSqlDatabase
的实例,并且该实例在被打开之前都是不可用的。
下面的代码片段,展示了如何创建一个默认connection,并打开它
|
|
- Qt支持以下database driver
Driver name | DBMS | |||
---|---|---|---|---|
QDB2 | IBM | DB2 (version 7.1 and above) | ||
QIBASE | Borland InterBase | |||
QMYSQL | MySQL | |||
QOCI | Oracle Call Interface Driver | |||
QODBC | Open Database Connectivity (ODBC) - Microsoft SQL Server and | other | ODBC-compliant databases | |
QPSQL | PostgreSQL (versions 7.3 and above) | |||
QSQLITE2 | SQLite version 2 | |||
QSQLITE | SQLite version 3 | |||
QTDS | Sybase Adaptive ServerNote: obsolete from Qt 4.7 |
我们也可以给
addDatabase()
传递第二个参数来指定connection name。12QSqlDatabase firstDB = QSqlDatabase::addDatabase("QMYSQL", "first");QSqlDatabase secondDB = QSqlDatabase::addDatabase("QMYSQL", "second");open()
失败,返回false。此时,可以使用QSqlDatabase::lastError()
来获得对应的错误信息一个connection一旦建立,我们可以在任何地方使用
QSqlDatabase::database()
来获得一个指向该链接的指针。123QSqlDatabase defaultDB = QSqlDatabase::database();QSqlDatabase firstDB = QSqlDatabase::database("first");QSqlDatabase secondDB = QSqlDatabase::database("second");删除一个connection。首先要用
QSqlDatabase::close()
关闭数据库,然后用静态函数QSqlDatabase::removeDatabase()
删除connection
Executing SQL Statements
QSqlQuery
提供了一系列接口来执行SQL语句,以及浏览查询的结果集合
而QSqlQueryModel
and QSqlTableModel
类提供了更高阶的数据库访问接口
Executing a Query
To execute an SQL statement, simply create a QSqlQuery
object and call QSqlQuery::exec()
like this:
QSqlQuery
的构造函数有一个可选参数,可以为其制定connection。上例中则直接使用默认connection
- 如果哟错误产生
exec()
returns false. The error is then available asQSqlQuery::lastError()
.
Navigating the Result Set浏览结果
在调用exec()
之后,QSqlQuery
的内部指针指向结果集中第一条记录的前一个位置。所以必须调用QSqlQuery::next()
来获得第一个记录,以此类推可以获得全部记录。next()返回false则表示不再有任何记录
QSqlQuery::value()
返回当前记录的一个field(列)的值。Fields从0开始索引。 QSqlQuery::value()
返回一个QVariant
.
Qt支持的类型,可以参考下表,或者Data Types for Qt-supported Database Systems
MySQL Data Types
|MySQL data type |SQL type description |Recommended input (C++ or Qt data type)|
|—-|—-|—-|
|TINYINT| 8 bit signed integer| typedef qint8|
|TINYINT UNSIGNED |8 bit unsigned integer| typedef quint8|
|SMALLINT| 16-bit signed intege|r typedef qint16
|SMALLINT| UNSIGNED 16-bit unsigned integer| typedef quint16
|INT| 32-bit signed integer| typedef qint32
|INT UNSIGNED| 32-bit unsigned integer| typedef quint32
|BIGINT| 64-bit signed integer| typedef qint64
|FLOAT| 32-bit Floating Point| By default mapping to QString
|DOUBLE| 64-bit Floating Point| By default mapping to QString
|CHAR| Character string| Mapped to QString
|VARCHAR| Character string| Mapped to QString
|TINYTEXT| Character string| Mapped to QString
|TEXT| Character string| Mapped to QString
|MEDIUMTEXT| Character string| Mapped to QString
|LONGTEXT| Character string| Mapped to QString
|CLOB| Character large string object |Mapped to QString
|all BLOB types| BLOB |Mapped to QByteArray
|DATE| Date without Time |Mapped to QDate
|DATETIME| Date and Time |Mapped to QDateTime
|TIMESTAMP| Date and Time |Mapped to QDateTime
|TIME| Time |Mapped to QTime
|YEAR| Year (int) |Mapped to QDateTime
|ENUM| Enumeration of Value Set |Mapped to QString
QSQLITE SQLite version 3 Data Types
QSQLITE | SQLite version 3 data type | SQL type description Recommended input (C++ or Qt data type) |
---|---|---|
NULL | NULL value. | NULL |
INTEGER | Signed integer, stored in 8, 16, 24, 32, 48, or 64-bits depending on the magnitude of the value. | typedef qint8/16/32/64 |
REAL | 64-bit floating point value. | By default mapping to QString |
TEXT | Character string (UTF-8, UTF-16BE or UTF-16-LE). | Mapped to QString |
CLOB | Character large string object | Mapped to QString |
BLOB | The value is a BLOB of data, stored exactly as it was input. | Mapped to QByteArray |
- 可以使用
QSqlQuery::next()
,QSqlQuery::previous()
,QSqlQuery::first()
,QSqlQuery::last()
, andQSqlQuery::seek()
来获取不同位置的记录 QSqlQuery::at()
返回当前的row indexQSqlQuery::size()
返回结果集中的所有行数,但是只对支持的结果有效如果你不确定,driver是否支持某个feature,可以使用
QSqlDriver::hasFeature()
来获取是否支持该特性.
|
|
如果你只想使用next()
and seek()
来迭代获取记录,那么在调用exec()之前你可以调用QSqlQuery::setForwardOnly(true)
. 当操作一个大的结果集时,这是一个很简单的优化方式,可以提高query效率。
void QSqlQuery::setForwardOnly(bool forward)
If forward is true, only
next()
andseek()
with positive values, are allowed for navigating the results.如果驱动支持的话,Forward only mode可以节省内存空间,因为结果不需要缓存。他将会提升一些数据库的性能
默认情况下,Forward only mode是关闭的。如果想开启,必须在query被prepared or executed之前调用setForwardOnly()
. Note that
the constructor that takes a query and a database may execute the
query.Setting forward only to false is a suggestion to the database engine, which has the final say on whether a result set is forward only or scrollable. isForwardOnly() will always return the correct status of the result set.
Note: 如果在query执行之后调用
setForwardOnly
会出现不可预料的结果,最坏会直接爆炸
Inserting, Updating, and Deleting Records
1. insert
|
|
如果你想一次插入多条记录,通常这会比分割成一条一条且插入实际值更搞笑。这可以通过占位符来实现。Qt支持两种占位符:named
binding and positional binding. Qt提供的数据库驱动支持两种占位符。
named binding
1234567QSqlQuery query;query.prepare("INSERT INTO employee (id, name, salary) ""VALUES (:id, :name, :salary)");query.bindValue(":id", 1001);query.bindValue(":name", "Thad Beaumont");query.bindValue(":salary", 65000);query.exec();positional binding:
1234567QSqlQuery query;query.prepare("INSERT INTO employee (id, name, salary) ""VALUES (?, ?, ?)");query.addBindValue(1001);query.addBindValue("Thad Beaumont");query.addBindValue(65000);query.exec();
The actual query that ends up being executed by the DBMS is available as QSqlQuery::executedQuery().
当需要插入多条记录时,你只需要调用QSqlQuery::prepare()
一次,然后在exec()
之后,可以调用多次bindValue()
or addBindValue()
When inserting multiple records, you only need to call QSqlQuery::prepare() once. Then you call bindValue() or addBindValue() followed by exec() as many times as necessary.
除了性能的考量,占位符的另一个优势是可以指定转义字符
2. update
|
|
- 也可以使用占位符。
3. delete
|
|
4. Transactions事务
可以通过QSqlDriver::hasFeature(QSqlDriver::Transactions)查询当前的数据库引擎是否支持
使用
QSqlDatabase::transaction()
来初始化一个事务随后就可以在事务的context内部执行SQL命令
然后调用
QSqlDatabase::commit()
orQSqlDatabase::rollback()
必须在创建query之前开始事务
Example:
事务可以确保一个复杂的操作是原子的,比如,查找外键和创建记录;另外也可以在一个复杂操作中间取消它