参考网上的许多文章学习Qt数据库编程,不一一给出链接。
在项目.pro中添加"QT+=sql",VS插件选择Sql模块。
1.数据库驱动
#include <QApplication>
#include "mainwindow.h"
#include <QDebug>
#include <QSqlDatabase>
int main(int argc, char *argv[])
{
QApplication app(argc, argv);
MainWindow w;
w.show();
qDebug() << "Available drivers: ";
QStringList drivers = QSqlDatabase::drivers();
foreach (QString driver, drivers)
{
qDebug() << driver;
}
QSqlDatabase dbc = QSqlDatabase::addDatabase("QODBC");
qDebug() << "ODBC driver valid? " << dbc.isValid();
return app.exec();
}
如果输出以下内容,表示ODBC驱动可用:
Available drivers:
"QSQLITE"
"QODBC3"
"QODBC"
ODBC driver valid? True
2.连接数据库
QSqlDatabase类实现数据库连接
bool OpenDatabase()
{
QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
db.setDatabaseName(QString("DRIVER={SQL SERVER};"
"SERVER=%1;"
"DATABASE=%2;"
"UID=%3;"
"PWD=%4;"
//如果用主机名,对于express版本数据库,即是VS自带的数据库,要加\SQLEXPRESS后缀,如MYPC\SQLEXPRESS
//如果用ip地址,加上端口号,如192.168.1.1, 1433
).arg("pc")
.arg("db")
.arg("sa")
.arg("sa")
);
if (!db.open())
{
qDebug() << "Error: " << db.lastError().text();
return false;
}
return true;
}
3.执行SQL语句
QSqlQuery类执行SQL语句
void ExecQuery()
{
QSqlQuery query;
QString str = QString("select * from table");
if (query.exec(str))
{
//执行完query.exec()时,query是指向结果集以外的,利用query.next(),query便指向了结果集的第一条记录。
//也可以在query.exec()前加上query.setForwardOnly(true),此后只能使用next()和seek()。
while(query.next())
{
//获取每条记录中属性(即列)的个数
int columnCount = query.record().count();
//获取name属性所在列的编号,列从左向右编号,最左边的编号为0
int nameIndex = query.record().indexOf("name");
//获取id属性的值,并转换为int型
int id = query.value(0).toInt();
//获取name属性的值
QString name = query.value(nameIndex).toString();
qDebug() << " id is : " << id
<< " name is : " << name
<< " columnCount is : " << columnCount;
}
}
}
addDatabase原型: QSqlDatabase * QSqlDatabase::addDatabase ( const QString & type, const QString & connectionName = defaultConnection )
在QSqlDatabase::addDatabase("QODBC")后,query找到defaultConnection ,根据defaultConnection执行SQL语句。
有可能出现QSqlDatabasePrivate::addDatabase: duplicate connection name 'qt_sql_default_connection', old connection removed.错误,就是defaultConnection的原因,可以通过下面的代码使用addDatabase
QSqlDatabase db;
if (QSqlDatabase::contains("qt_sql_default_connection"))
{
db = QSqlDatabase::database("qt_sql_default_connection");
}
else
{
db = QSqlDatabase::addDatabase("QODBC");
}
4.执行其它SQL语句
void InsertQuery()
{
//设置字符编码
QTextCodec::setCodecForTr(QTextCodec::codecForLocale());
//单一插入数据
QSqlQuery query;
query.prepare("insert into course (cno, cname, credit, semester) values (:cno, :cname, :credit, :semester)");
query.bindValue(":cno", "c1005");
query.bindValue(":cname", tr("数学"));
query.bindValue(":credit", 6);
query.bindValue(":semester", 9);
query.exec();
//批量插入数据
query.prepare("insert into course values (?, ?, ?, ?)");
QVariantList cnos;
cnos << "c1" << "c2" << "c3" << "c4";
query.addBindValue(cnos);
QVariantList cnames;
cnames << tr("物理") << tr("化学") << tr("历史") << tr("地理");
query.addBindValue(cnames);
QVariantList credits;
credits << 9 << 9 << 9 << 9;
query.addBindValue(credits);
QVariantList semesters;
semesters << 12 << 12 << 12 << 12;
query.addBindValue(semesters);
if (!query.execBatch())
{
qDebug() << query.lastError();
}
}
void UpdateAndDelete()
{
//设置字符编码
QTextCodec::setCodecForTr(QTextCodec::codecForLocale());
QSqlQuery query;
query.prepare("update course set credit = ? where cname = ?");
query.bindValue(0, 10);
query.bindValue(1, tr("物理"));
query.exec();
query.prepare("delete from course where cname = ?");
query.bindValue(0, tr("化学"));
if (!query.exec())
{
qDebug() << query.lastError();
}
}
5.QSqlQueryModel 只读模式,基于SQL查询
void ExecQueryMode()
{
QSqlQueryModel model;
model.setQuery("SELECT * FROM course");
for (int i = 0; i < model.rowCount(); ++i)
{
QString cno = model.record(i).value("cno").toString();
QString cname = model.record(i).value("cname").toString();
qDebug() << cno << cname;
}
}
6.QSqlTableModel 可进行读写
void ExecTableMode()
{
//设置字符编码
QTextCodec::setCodecForTr(QTextCodec::codecForLocale());
QSqlTableModel model;
model.setTable("course");
model.select();
for (int i = 0; i < model.rowCount(); ++i)
{
QString cno = model.record(i).value("cno").toString();
QString cname = model.record(i).value("cname").toString();
qDebug() << cname << cno;
}
for (int i = 0; i < model.rowCount(); ++i)
{
QSqlRecord record = model.record(i);
int credit = record.value("credit").toInt();
credit -= 10;
record.setValue("credit", credit);
model.setRecord(i, record);
}
model.submitAll();
int row = 3;
model.insertRows(row, 1); //第3行数据后插入一条数据
model.setData(model.index(row, 0), "c1013");
model.setData(model.index(row, 1), tr("数学"));
model.setData(model.index(row, 2), 9);
model.setData(model.index(row, 3), 12);
model.submitAll();
model.removeRows(8, 1); //第8行数据后删除一条数据
model.submitAll();
}
7.QSqlQueryModel 结合QTableView显示数据
void ShowQueryView()
{
QSqlQueryModel *model = new QSqlQueryModel;
model->setQuery("SELECT * FROM course");
//设置标题头部标签信息
model->setHeaderData(0, Qt::Horizontal, QObject::tr("Cno"));
model->setHeaderData(1, Qt::Horizontal, QObject::tr("Cname"));
model->setHeaderData(2, Qt::Horizontal, QObject::tr("Credit"));
model->setHeaderData(3, Qt::Horizontal, QObject::tr("Semester"));
//借助QTableView,把数据信息显示出来,
ui->tableView->setModel(model);
}
8.获取记录总数
void GetRows()
{
QSqlQuery query;
QSqlDatabase db;
int rows;
if (query.exec("select * from course"))
{
while (query.next())
{
if (db.driver()->hasFeature(QSqlDriver::QuerySize))
{
rows = query.size();
}
else
{
query.last();
rows = query.at() + 1;
}
}
}
qDebug() << rows;
}