Qt QSqlTableModel 使用心得

Qt QSqlTableModel 使用心得

连接数据库

执行sql查询，条件显示，排序

获取记录数，列数以及记录内容，字段内容

新增，修改，删除，恢复

其它

1---------------连接数据库（我用的access2003做实验）

在.pro文件添加

QT +=SQL

win32:CONFIG+=console

------------------------MAIN.CPP---------------

#include <QtGui/QApplication>

#include <QSqlDatabase>

#include <QSqlQuery>

#include <QDebug>

#include <QVariant>

#include <QString>

#include <QSqlTableModel>

#include <QTableView>

#include "mainwindow.h"

int main(intargc, char *argv[])

{

QApplication a(argc, argv);

// MainWindow w;

//w.show();

QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");

db.setDatabaseName("DRIVER={Microsoft AccessDriver (*.mdb)};FIL={MS Access};DBQ=barcode.mdb;UID='';PWD=''");

boolok = db.open();

if(ok)

{

qDebug()<<"connect ok!";

}

else

{

qDebug()<<"connect error!";

return false;

}

return a.exec();

}

2-----执行sql查询，条件显示，排序

查询：

model = newQSqlTableModel(this);
model->setTable(“student”);
model->setEditStrategy(QSqlTableModel::OnManualSubmit);
model->select(); //选取整个表的所有行
// model->removeColumn(1); //不显示第二列,如果这时添加记录，则该属性的值添加不上

QTableView *view= new QTableView;

view->setModel(model);

view->show();

//View->setEditTriggers(QAbstractItemView::NoEditTriggers);使其不可编辑

条件：（等价于SQL语句的where）

model->setFilter(QObject::tr(“name= ‘admin’”).arg(name)); //根据姓名进行筛选
model->select(); //显示结果

排序:(等价于ORDERBY)
model->setSort(0,Qt::AscendingOrder); //id属性，即第0列，升序排列
model->select();

model->setSort(0,Qt::DescendingOrder); //id属性，即第0列，降序排
model->select();

3------获取记录数，列数以及记录内容，字段内容

记录数： Model->rowcount

记录值：

值=model.record(num).value(1).toString();

Qrecord record= QSqlRecord record = tablemodel.record(记录行数);

值=Record.value(“字段名或索引”)

字段数：

字段值：

4------新增，修改，删除,提交，撤销

新增：

int rowNum = model->rowCount();//获得表的行数
int id = 10;
model->insertRow(rowNum); //添加一行
model->setData(model->index(rowNum,0),id);
//model->submitAll(); //可以直接提交

或者用record添加

QSqlRecord record = tablemodel.record();

record.setValue("nam","new");

model.insertRecord(1,record);

修改：

首先用条件找到某一条记录

QSqlRecord record = tablemodel.record(num);

record.setValue("name",record.value("name").toString()+"2");

tablemodel.setRecord(num,record);

if(tablemodel.submitAll())

"成功！"

else

"失败！"

或者用下面方法用setData()来修改，代码如下：
model.setData(model.index(1,1),"new");
if(tablemodel.submitAll())
"成功！"
else
"失败！"

删除：
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(); //否则提交，在数据库中删除该行

如果没有table

tablemodel.removeRows(起始行, 要删除的总行数);
model.submitAll();

提交

model->database().transaction();//开始事务操作
if (model->submitAll()) {
model->database().commit();//提交
} else {
model->database().rollback();//回滚
QMessageBox::warning(this,tr(“tableModel”),
tr(“数据库错误: %1″)
.arg(model->lastError().text()));
}

撤销

model->revertAll();

5------其它

he QSqlTableModel class provides an editable data model for a single database table.More...

 #include <QSqlTableModel>

Inherits:QSqlQueryModel.

Inherited by:QSqlRelationalTableModel.

List of all members, including inherited members

Public Types

enum	EditStrategy{ OnFieldChange, OnRowChange, OnManualSubmit }

Public Functions

	QSqlTableModel( QObject *parent= 0, QSqlDatabasedb= QSqlDatabase() )
virtual	~QSqlTableModel()
QSqlDatabase	database() const
EditStrategy	editStrategy() const
int	fieldIndex( const QString &fieldName) const
QString	filter() const
bool	insertRecord( introw, const QSqlRecord &record)
bool	isDirty( const QModelIndex &index) const
QSqlIndex	primaryKey() const
virtual void	revertRow( introw)
virtual bool	select()
virtual void	setEditStrategy( EditStrategystrategy)
virtual void	setFilter( const QString &filter)
bool	setRecord( introw, const QSqlRecord &record)
virtual void	setSort( intcolumn, Qt::SortOrderorder)
virtual void	setTable( const QString &tableName)
QString	tableName() const

Reimplemented Public Functions

virtual void	clear()
virtual QVariant	data( const QModelIndex &index, introle= Qt::DisplayRole ) const
virtual Qt::ItemFlags	flags( const QModelIndex &index) const
virtual QVariant	headerData( intsection, Qt::Orientationorientation, introle= Qt::DisplayRole ) const
virtual bool	insertRows( introw, intcount, const QModelIndex &parent= QModelIndex() )
virtual bool	removeColumns( intcolumn, intcount, const QModelIndex &parent= QModelIndex() )
virtual bool	removeRows( introw, intcount, const QModelIndex &parent= QModelIndex() )
virtual int	rowCount( const QModelIndex &parent= QModelIndex() ) const
virtual bool	setData( const QModelIndex &index, const QVariant &value, introle= Qt::EditRole )
virtual void	sort( intcolumn, Qt::SortOrderorder)

16 public functions inherited fromQSqlQueryModel
2 public functions inherited fromQAbstractTableModel
35 public functions inherited fromQAbstractItemModel
29 public functions inherited fromQObject

Public Slots

virtual void	revert()
void	revertAll()
virtual bool	submit()
bool	submitAll()

2 public slots inherited fromQAbstractItemModel
1 public slot inherited fromQObject

Signals

void	beforeDelete( introw)
void	beforeInsert( QSqlRecord &record)
void	beforeUpdate( introw, QSqlRecord &record)
void	primeInsert( introw, QSqlRecord &record)

18 signals inherited fromQAbstractItemModel
1 signal inherited fromQObject

Protected Functions

virtual bool	deleteRowFromTable( introw)
QModelIndex	indexInQuery( const QModelIndex &item) const
virtual bool	insertRowIntoTable( const QSqlRecord &values)
virtual QString	orderByClause() const
virtual QString	selectStatement() const
void	setPrimaryKey( const QSqlIndex &key)
void	setQuery( const QSqlQuery &query)
virtual bool	updateRowInTable( introw, const QSqlRecord &values)