- //connection.h
- #ifndef CONNECTION_H
- #define CONNECTION_H
- #include <QMessageBox>
- #include <QSqlDatabase>
- #include <QSqlQuery>
- static bool createConnection()
- {
- QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
- // db.setDatabaseName(":memory:");
- db.setDatabaseName("/test1.db");
- if (!db.open()) {
- QMessageBox::critical(0, qApp->tr("Cannot open database"),
- qApp->tr("Unable to establish a database connection."
- ), QMessageBox::Cancel);
- return false;
- }
- QSqlQuery query;
- query.exec("create table student (id int primary key, name varchar(20))");
- //创建表student,如果student表已经存在则无动作
- query.exec("insert into student values(0, 'first')");
- query.exec("insert into student values(1, 'second')");
- query.exec("insert into student values(2, 'third')");
- query.exec("insert into student values(3, 'fourth')");
- query.exec("insert into student values(4, 'fifth')");
- return true;
- }
- #endif // CONNECTION_H
- //main.cpp
- #include <QtGui/QApplication>
- #include "mainwindow.h"
- #include"connection.h"
- #include<QDebug>
- int main(int argc, char *argv[])
- {
- QApplication a(argc, argv);
- if (!createConnection())
- return 1;
- MainWindow w;
- w.show();
- return a.exec();
- }
- //mainwindow.cpp
- void MainWindow::on_pushButton_clicked()
- {
- QSqlQuery query;
- query.exec("select * from student");
- QString str;
- while(query.next())
- {
- qDebug() << query.value(0).toInt() << query.value(1).toString();
- str=str+"\n"+query.value(1).toString();
- ui->textEdit->setText(str);
- }
- }
- void MainWindow::on_pushButton_2_clicked()
- {
- QSqlQuery query;
- query.exec("select * from student");
- qDebug() << "exec next() :";
- if(query.next())
- //开始就先执行一次next()函数,那么query指向结果集的第一条记录
- {
- int rowNum = query.at();
- //获取query所指向的记录在结果集中的编号
- int columnNum = query.record().count();
- //获取每条记录中属性(即列)的个数
- int fieldNo = query.record().indexOf("name");
- //获取"name"属性所在列的编号,列从左向右编号,最左边的编号为0
- int id = query.value(0).toInt();
- //获取id属性的值,并转换为int型
- QString name = query.value(fieldNo).toString();
- //获取name属性的值
- qDebug() << "rowNum is : " << rowNum //将结果输出
- << " id is : " << id
- << " name is : " << name
- << " columnNum is : " << columnNum;
- }
- qDebug() << "exec seek(2) :";
- if(query.seek(2))
- //定位到结果集中编号为2的记录,即第三条记录,因为第一条记录的编号为0
- {
- qDebug() << "rowNum is : " << query.at()
- << " id is : " << query.value(0).toInt()
- << " name is : " << query.value(1).toString();
- }
- qDebug() << "exec last() :";
- if(query.last())
- //定位到结果集中最后一条记录
- {
- qDebug() << "rowNum is : " << query.at()
- << " id is : " << query.value(0).toInt()
- << " name is : " << query.value(1).toString();
- }
- }
当数据库已open,可进行如下操作
创建表
- query.exec("create table student (id int primary key, name varchar(20))");
- //创建表student,如果student表已经存在则无动作
- //有两个字段,id(兼任主键)和name
添加记录
- //直接添加
- query.exec("insert into student values(1, 'second')");
- //在表中增加一条主键即id是1的记录,name字段是"second"
- //如果id=1的记录已经存在,则无动作
- //使用变量添加
- query.prepare("insert into student values (?, ?)");
- query.bindValue(0, i);
- query.bindValue(1, "sixth");
- query.exec();
- //或
- query.prepare("insert into student (id, name) values (:id, :name)");
- query.bindValue(0, i);
- query.bindValue(1, "sixth");
- query.exec();
- //或
- query.prepare("insert into student (id, name) values (:id, :name)");
- query.addBindValue(i);
- query.addBindValue("sixth");
- query.exec();
删除记录
- query.exec("delete from student where id = 1");
查询记录
- //直接查询
- query.exec("select name from student where id = 1");
- query.exec("select name from student ");
- query.exec("select * from student ");
- //使用变量
- query.prepare("select name from student where id = ?");
- int id = ui->spinBox->value(); //从界面获取id的值
- query.addBindValue(id); //将id值进行绑定
- query.exec();
更新记录
- //使用变量
- QSqlQuery query;
- query.prepare("update student set name = ? where id = ?");
- query.addBindValue(name);
- query.addBindValue(studentId);
- query.exec();
http://www.yafeilinux.com/?p=82
************************************************************************************************************************************************************************************
二十七、Qt数据库(七)QSqlRelationalTableModel
- //mainwindow.cpp
- #include "mainwindow.h"
- #include "ui_mainwindow.h"
- #include<QtGui>
- #include <QSqlQuery>
- #include <QtDebug>
- #include<QSqlRecord>
- #include <QModelIndex>
- #include<QSqlQueryModel>
- #include "mysqlquerymodel.h"
- #include <QTableView>
- #include <QSqlRelationalDelegate>
- #include<QSqlError>
- MainWindow::MainWindow(QWidget *parent) :
- QMainWindow(parent),
- ui(new Ui::MainWindow)
- {
- ui->setupUi(this);
- /*
- model = new QSqlTableModel(this);
- model->setTable("student");
- model->setEditStrategy(QSqlTableModel::OnManualSubmit);
- model->select(); //选取整个表的所有行
- // model->removeColumn(0); //不显示name属性列,如果这时添加记录,则该属性的值添加不上
- ui->tableView->setModel(model);
- //ui->tableView->setEditTriggers(QAbstractItemView::NoEditTriggers); //使其不可编
- */
- model = new QSqlRelationalTableModel(this);
- model->setEditStrategy(QSqlTableModel::OnFieldChange); //属性变化时写入数据库
- model->setTable("student");
- model->setRelation(2,QSqlRelation("course","id","name"));
- //将student表的第三个属性设为course表的id属性的外键,并将其显示为course表的name属性的值
- model->setHeaderData(0, Qt::Horizontal, QObject::tr("ID"));
- model->setHeaderData(1, Qt::Horizontal, QObject::tr("Name"));
- model->setHeaderData(2, Qt::Horizontal, QObject::tr("Course"));
- model->select();
- ui->tableView->setModel(model);
- ui->tableView->setItemDelegate(new QSqlRelationalDelegate(ui->tableView));
- }
- MainWindow::~MainWindow()
- {
- delete ui;
- }
- void MainWindow::on_pushButton_clicked()
- {
- QSqlQuery query;
- query.exec("select * from student");
- QString str;
- while(query.next())
- {
- qDebug() << query.value(0).toInt() << query.value(1).toString();
- str=str+"\n"+query.value(1).toString();
- // ui->lineEdit->setText(str);
- //ui->textEdit->setText(str);
- }
- }
- void MainWindow::on_pushButton_2_clicked()
- {
- QSqlQuery query;
- query.exec("select * from student");
- qDebug() << "exec next() :";
- if(query.next())
- //开始就先执行一次next()函数,那么query指向结果集的第一条记录
- {
- int rowNum = query.at();
- //获取query所指向的记录在结果集中的编号
- int columnNum = query.record().count();
- //获取每条记录中属性(即列)的个数
- int fieldNo = query.record().indexOf("name");
- //获取"name"属性所在列的编号,列从左向右编号,最左边的编号为0
- int id = query.value(0).toInt();
- //获取id属性的值,并转换为int型
- QString name = query.value(fieldNo).toString();
- //获取name属性的值
- qDebug() << "rowNum is : " << rowNum //将结果输出
- << " id is : " << id
- << " name is : " << name
- << " columnNum is : " << columnNum;
- }
- qDebug() << "exec seek(2) :";
- if(query.seek(2))
- //定位到结果集中编号为2的记录,即第三条记录,因为第一条记录的编号为0
- {
- qDebug() << "rowNum is : " << query.at()
- << " id is : " << query.value(0).toInt()
- << " name is : " << query.value(1).toString();
- }
- qDebug() << "exec last() :";
- if(query.last())
- //定位到结果集中最后一条记录
- {
- qDebug() << "rowNum is : " << query.at()
- << " id is : " << query.value(0).toInt()
- << " name is : " << query.value(1).toString();
- }
- }
- void MainWindow::on_pushButton_3_clicked()
- {
- QSqlQuery query;
- int i=210;
- while(i--){
- query.prepare("insert into student values (?, ?)");
- query.bindValue(0, i);
- query.bindValue(1, "sixth");
- query.exec();
- }
- //下面输出最后一条记录
- query.exec("select * from student");
- query.last();
- int id = query.value(0).toInt();
- QString name = query.value(1).toString();
- qDebug() << id << name;
- }
- void MainWindow::on_pushButton_4_clicked()
- { // QSqlQuery query;
- // query.exec("insert into student values(69, '768')");
- QSqlQuery query;
- query.prepare("select name from student where id = ?");
- int id = ui->spinBox->value(); //从界面获取id的值
- query.addBindValue(id); //将id值进行绑定
- query.exec();
- query.next(); //指向第一条记录
- qDebug() << query.value(0).toString();
- }
- void MainWindow::on_pushButton_5_clicked()
- {
- QSqlQueryModel *model = new QSqlQueryModel;
- model->setQuery("select * from student");
- model->setHeaderData(0, Qt::Horizontal, tr("id"));
- model->setHeaderData(1, Qt::Horizontal, tr("name song"));
- QTableView *view = new QTableView;
- view->setWindowTitle("ggg"); //修改窗口标题
- view->setModel(model);
- view->show();
- MySqlQueryModel *myModel = new MySqlQueryModel; //创建自己模型的对象
- myModel->setQuery("select * from student");
- myModel->setHeaderData(0, Qt::Horizontal, tr("id"));
- myModel->setHeaderData(1, Qt::Horizontal, tr("name"));
- QTableView *view1 = new QTableView;
- view1->setWindowTitle("mySqlQueryModel"); //修改窗口标题
- view1->setModel(myModel);
- view1->show();
- int column = model->columnCount(); //获得列数
- int row = model->rowCount(); // 获得行数
- QSqlRecord record = model->record(1); //获得一条记录
- QModelIndex index = model->index(1,1); //获得一条记录的一个属性的值
- qDebug() << "column num is:" << column << endl
- << "row num is:" << row << endl
- <<"the second record is:" << record << endl
- << "the data of index(1,1) is:"<< index.data();
- }
- void MainWindow::on_pushButton_6_clicked()
- {
- }
- void MainWindow::on_pushButton_7_clicked()
- {
- model->database().transaction(); //开始事务操作
- if (model->submitAll()) {
- model->database().commit(); //提交
- } else {
- model->database().rollback(); //回滚
- QMessageBox::warning(this, tr("tableModel"),
- tr("数据库错误: %1")
- .arg(model->lastError().text()));
- }
- }
- void MainWindow::on_pushButton_8_clicked()
- {
- model->revertAll();
- }
- void MainWindow::on_pushButton_9_clicked()
- {
- QString str=ui->lineEdit->text();
- model->setFilter(tr("name='%1'").arg(str));
- model->select();
- }
- void MainWindow::on_pushButton_10_clicked()
- { model->setTable("student");
- model->select();
- }
- void MainWindow::on_pushButton_11_clicked()
- {
- model->setSort(0,Qt::AscendingOrder); //id属性,即第0列,升序排列
- model->select();
- }
- void MainWindow::on_pushButton_12_clicked()
- {
- model->setSort(0,Qt::DescendingOrder); //id属性,即第0列,升序排列
- model->select();
- }
- void MainWindow::on_pushButton_14_clicked()
- {
- int curRow = ui->tableView->currentIndex().row();
- //获取选中的行
- model->removeRow(curRow);
- //删除该行
- int ok = QMessageBox::warning(this,tr("删除当前行!"),tr("你确定"
- "删除当前行吗?"),
- QMessageBox::Yes,QMessageBox::No);
- if(ok == QMessageBox::No)
- {
- model->revertAll(); //如果不删除,则撤销
- }
- else model->submitAll(); //否则提交,在数据库中删除该行
- }
- void MainWindow::on_pushButton_13_clicked()
- {
- int rowNum = model->rowCount(); //获得表的行数
- //int id = 10;
- model->insertRow(rowNum); //添加一行
- model->setData(model->index(rowNum,0),rowNum+1);
- model->setData(model->index(rowNum,1),"rowNum");
- //model->submitAll(); //可以直接提交
- }
最后的
model->setData(model->index(rowNum,0),rowNum+1);
此函数是给某条记录的某个字段赋值
参数1 model->index(rowNum,0)指定视图表格中的第rowNum行,从1开始,不是数据库的表第rowNum行,的字段0。
参数2 是实际要赋的值,上面的那个字段值指定为rowNum+1
比如,点击2次添加记录按钮
另外在add记录时,注意构造函数中手动提交和字段变化时自动提交的区别
model -> setEditStrategy ( QSqlTableModel :: OnManualSubmit );
model->setEditStrategy(QSqlTableModel::OnFieldChange);