HEADERS += \
Widget01.h \
Widget02.h \
Widget03.h \
Widget04.h \
Contact.h \
Widget05.h
SOURCES += \
Widget01.cpp \
main.cpp \
Widget02.cpp \
Widget03.cpp \
Widget04.cpp \
Contact.cpp \
Widget05.cpp
QT += gui widgets sql
CONFIG += C++11
#ifndef CONTACT_H
#define CONTACT_H
#include <QWidget>
#include <QSqlTableModel>
#include <QTableView>
#include <QLineEdit>
#include <QPushButton>
class Contact : public QWidget
{
Q_OBJECT
public:
explicit Contact(QWidget *parent = 0);
QSqlTableModel* _model;
QTableView* _view;
QLineEdit* _filter;
QPushButton* _add;
QPushButton* _del;
QPushButton* _reset;
QPushButton* _submit;
signals:
public slots:
void slotModelDataChanged(QModelIndex,QModelIndex);
void slotFilterChanged(QString filter);
};
#endif // CONTACT_H
CONTACT.cpp
#include "Contact.h"
#include <QVBoxLayout>
#include <QHBoxLayout>
#include <QSqlRecord>
#include <QCompleter>
#include <QDebug>
Contact::Contact(QWidget *parent) :
QWidget(parent)
{
_model = new QSqlTableModel;
_view = new QTableView;
_view->setModel(_model);
_model->setTable("tcontact");
_model->setEditStrategy(QSqlTableModel::OnManualSubmit);
connect(_model, SIGNAL(dataChanged(QModelIndex,QModelIndex)),
this, SLOT(slotModelDataChanged(QModelIndex,QModelIndex)));
_model->select();
// set Layout
QVBoxLayout* vBox = new QVBoxLayout(this);
vBox->addWidget(_view);
QHBoxLayout* hBox = new QHBoxLayout;
vBox->addLayout(hBox);
hBox->addWidget(_filter = new QLineEdit, 1);
hBox->addWidget(_add=new QPushButton("Add"));
hBox->addWidget(_del=new QPushButton("Del"));
hBox->addWidget(_reset=new QPushButton("Reset"));
hBox->addWidget(_submit=new QPushButton("Submit"));
connect(_add, &QPushButton::clicked, [&](){
QSqlRecord record = _model->record();
_model->insertRecord(-1, record);
});
connect(_del, &QPushButton::clicked, [&](){});
connect(_reset, &QPushButton::clicked, [&](){});
connect(_submit, &QPushButton::clicked, [&](){
_model->submitAll();
});
connect(_filter, SIGNAL(textChanged(QString)),
this, SLOT(slotFilterChanged(QString)));
slotModelDataChanged(QModelIndex(), QModelIndex());
}
void Contact::slotFilterChanged(QString filter)
{
if(filter.isEmpty())
{
_model->setFilter("");
_model->select();
return;
}
// username like filter or password like filter .......
QSqlRecord record = _model->record();
QString modelFilter;
for(int i=0; i<record.count(); ++i)
{
if(i!=0)
{
modelFilter += " or ";
}
QString field = record.fieldName(i);
QString subFilter = QString().sprintf("%s like '%%%s%%'", field.toUtf8().data(), filter.toUtf8().data());
// qDebug() << subFilter;
modelFilter += subFilter;
}
qDebug() << modelFilter;
_model->setFilter(modelFilter);
_model->select();
}
void Contact::slotModelDataChanged(QModelIndex,QModelIndex)
{
QStringList strList;
for(int i=0; i<_model->rowCount(); ++i)
{
QSqlRecord record = _model->record(i);
for(int j=0; j<record.count(); ++j)
{
QVariant var = record.value(j);
if(var.isNull()) continue;
strList << var.toString();
}
}
qDebug() << strList;
QCompleter* completer=new QCompleter(strList);
_filter->setCompleter(completer);
}
#ifndef WIDGET01_H
#define WIDGET01_H
#include <QWidget>
class Widget01 : public QWidget
{
Q_OBJECT
public:
explicit Widget01(QWidget *parent = 0);
signals:
public slots:
};
#endif // WIDGET01_H
Widget01.cpp
#include "Widget01.h"
#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQuery>
#include <QSqlResult>
#include <QDebug>
Widget01::Widget01(QWidget *parent) :
QWidget(parent)
{
/* QSQLITE QODBC QPLSQL */
QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
db.setHostName("127.0.0.1");
db.setUserName("root");
db.setPassword("iamxgl");
db.setDatabaseName("d0718");
bool bRet = db.open();
if(bRet == false)
{
qDebug() << "error open database" << db.lastError().text();
exit(0);
}
qDebug() << "open database success";
// db.exec("insert into tuser (username, password, gender) values('xueguoliang', 'xxdfsdf', 1)");
QSqlQuery query = db.exec("select * from d0718");
// int size = query.size();
bool b = query.first();
while(b)
{
// QSqlResult* result = query.result();
// QVector<QVariant> values = result->boundValues();
// qDebug() << values;
b = query.next();
}
}
#ifndef WIDGET02_H
#define WIDGET02_H
#include <QWidget>
class Widget02 : public QWidget
{
Q_OBJECT
public:
explicit Widget02(QWidget *parent = 0);
signals:
public slots:
};
#endif // WIDGET02_H
Widget02.cpp
#include "Widget02.h"
#include <QSqlDatabase>
#include <QSqlTableModel>
#include <QSqlRecord>
#include <QDebug>
#include <QSqlError>
Widget02::Widget02(QWidget *parent) :
QWidget(parent)
{
// QSqlTableModel: 数据表对应的数据结构
QSqlTableModel model;
model.setTable("tuser");
//model.setFilter("username<>'newuser'");
model.select(); // exec query
int ret = model.rowCount();
// read data from database
for(int i=0; i<ret; ++i)
{
QSqlRecord record = model.record(i);
for(int j=0; j<record.count(); j++)
{
qDebug() << record.value(j);
}
}
// update data to database
// model.setData(model.index(0, 1), "xxxxxx");
// model.submitAll();
// insert data to database
QSqlRecord record = model.record();
// record.setValue("id", );
record.setValue("username", "newuser1");
record.setValue("password", "newpassword");
record.setValue("gender", 1);
model.insertRecord(-1, record);
model.submitAll();
}
#ifndef WIDGET03_H
#define WIDGET03_H
#include <QObject>
#include <QSqlTableModel>
#include <QTableView> // show table
class Widget03 : public QWidget
{
Q_OBJECT
public:
explicit Widget03(QWidget *parent = 0);
QSqlTableModel* _model;
QTableView* _view;
signals:
public slots:
void slotSubmitClicked();
void slotDelClicked();
void slotAddClicked();
};
#endif // WIDGET03_H
Widget03.cpp
#include "Widget03.h"
#include <QVBoxLayout>
#include <QHBoxLayout>
#include <QPushButton>
#include <QMessageBox>
#include <QSqlError>
#include <QSqlDatabase>
#include <QSqlRecord>
Widget03::Widget03(QWidget *parent) :
QWidget(parent)
{
_model = new QSqlTableModel;
_model->setTable("tuser");
_model->select();
_model->setEditStrategy(QSqlTableModel::OnManualSubmit);
_model->setHeaderData(0, Qt::Horizontal, "编号");
_model->setHeaderData(1, Qt::Horizontal, "用户名");
_model->setHeaderData(2, Qt::Horizontal, "密码");
_model->setHeaderData(3, Qt::Horizontal, "性别");
_view = new QTableView;
_view->setModel(_model);
// _view->hideColumn(2);
QVBoxLayout* lay = new QVBoxLayout(this);
lay->addWidget(_view);
QHBoxLayout* hBox = new QHBoxLayout;
lay->addLayout(hBox);
hBox->addStretch();
QPushButton* del = new QPushButton("del");
connect(del, SIGNAL(clicked()), this, SLOT(slotDelClicked()));
hBox->addWidget(del);
QPushButton* submit = new QPushButton("submit");
connect(submit, SIGNAL(clicked()), this, SLOT(slotSubmitClicked()));
hBox->addWidget(submit);
QPushButton* add = new QPushButton("add");
connect(add, SIGNAL(clicked()), this, SLOT(slotAddClicked()));
hBox->addWidget(add);
}
void Widget03::slotAddClicked()
{
// start transaction
_model->database().transaction();
QSqlRecord record = _model->record();
_model->insertRecord(-1, record);
}
void Widget03::slotDelClicked()
{
// 通过_view去获取被选中的部分的数据model
QItemSelectionModel * selectModel = _view->selectionModel();
// 通过选中的数据结构，获取这些格子的ModelIndex
QModelIndexList selectList = selectModel->selectedIndexes();
QList<int> delRow;
// 遍历这些格子，获取格子所在行，因为可能存在相同的行，所以要去重
for(int i=0; i<selectList.size(); ++i)
{
QModelIndex index = selectList.at(i);
// _model->removeRow(index.row());
delRow << index.row();
}
while(delRow.size() > 0)
{
int row = delRow.at(0);
delRow.removeAll(row);
_model->removeRow(row);
}
_model->submitAll();
}
void Widget03::slotSubmitClicked()
{
if(!_model->submitAll())
{
QMessageBox::critical(this, "Error", QSqlDatabase().lastError().text());
_model->database().rollback();
}
else
{
_model->database().commit();
}
}
#ifndef WIDGET04_H
#define WIDGET04_H
#include <QObject>
#include <QSqlTableModel>
#include <QTableView> // show table
#include <QItemDelegate>
#include <QComboBox>
class TUserDelegate : public QItemDelegate
{
QWidget *createEditor(QWidget * parent,
const QStyleOptionViewItem &option,
const QModelIndex &index) const
{
if(index.column() == 0)
return NULL;
if(index.column() == 3)
{
QComboBox* combo = new QComboBox(parent);
combo->addItem("男");
combo->addItem("女");
return combo;
}
return QItemDelegate::createEditor(parent, option, index);
}
};
#if 0
class ReadOnlyDelegate : public QItemDelegate
{
QWidget *createEditor(QWidget *,
const QStyleOptionViewItem &,
const QModelIndex &) const
{
return NULL;
}
};
class GenderDelegate : public QItemDelegate
{
public:
QWidget *createEditor(QWidget *parent,
const QStyleOptionViewItem &,
const QModelIndex &) const
{
QComboBox* combo = new QComboBox(parent);
combo->addItem("男");
combo->addItem("女");
return combo;
}
};
#endif
class MyTableModel : public QSqlTableModel
{
public:
QVariant data(const QModelIndex &idx, int role = Qt::DisplayRole) const
{
// if(role == Qt::DisplayRole)
// return QSqlTableModel::data(idx, role);
if(idx.column() != 3)
return QSqlTableModel::data(idx, role);
QVariant var = QSqlTableModel::data(idx, role);
if(var == 0)
{
return "女";
}
return "男";
}
bool setData(const QModelIndex &index, const QVariant &value, int role = Qt::EditRole)
{
if(index.column() != 3)
return QSqlTableModel::setData(index, value, role);
if(value == "男")
return QSqlTableModel::setData(index, 1, role);
return QSqlTableModel::setData(index, 0, role);
}
};
class Widget04 : public QWidget
{
Q_OBJECT
public:
explicit Widget04(QWidget *parent = 0);
MyTableModel* _model;
QTableView* _view;
signals:
public slots:
void slotSubmitClicked();
void slotDelClicked();
void slotAddClicked();
};
#endif // WIDGET04_H
Widget04.cpp
#include "Widget04.h"
#include <QVBoxLayout>
#include <QHBoxLayout>
#include <QPushButton>
#include <QMessageBox>
#include <QSqlError>
#include <QSqlDatabase>
#include <QSqlRecord>
Widget04::Widget04(QWidget *parent) :
QWidget(parent)
{
_model = new MyTableModel;
_model->setTable("tuser");
_model->select();
_model->setEditStrategy(QSqlTableModel::OnManualSubmit);
_model->setHeaderData(0, Qt::Horizontal, "编号");
_model->setHeaderData(1, Qt::Horizontal, "用户名");
_model->setHeaderData(2, Qt::Horizontal, "密码");
_model->setHeaderData(3, Qt::Horizontal, "性别");
_view = new QTableView;
_view->setModel(_model);
// _view->hideColumn(2);
// _view->setItemDelegateForColumn(3, new GenderDelegate);
// _view->setItemDelegateForColumn(0, new ReadOnlyDelegate);
_view->setItemDelegate(new TUserDelegate);
QVBoxLayout* lay = new QVBoxLayout(this);
lay->addWidget(_view);
QHBoxLayout* hBox = new QHBoxLayout;
lay->addLayout(hBox);
hBox->addStretch();
QPushButton* del = new QPushButton("del");
connect(del, SIGNAL(clicked()), this, SLOT(slotDelClicked()));
hBox->addWidget(del);
QPushButton* submit = new QPushButton("submit");
connect(submit, SIGNAL(clicked()), this, SLOT(slotSubmitClicked()));
hBox->addWidget(submit);
QPushButton* add = new QPushButton("add");
connect(add, SIGNAL(clicked()), this, SLOT(slotAddClicked()));
hBox->addWidget(add);
}
void Widget04::slotAddClicked()
{
QSqlRecord record = _model->record();
_model->insertRecord(-1, record);
}
void Widget04::slotDelClicked()
{
// 通过_view去获取被选中的部分的数据model
QItemSelectionModel * selectModel = _view->selectionModel();
// 通过选中的数据结构，获取这些格子的ModelIndex
QModelIndexList selectList = selectModel->selectedIndexes();
QList<int> delRow;
// 遍历这些格子，获取格子所在行，因为可能存在相同的行，所以要去重
for(int i=0; i<selectList.size(); ++i)
{
QModelIndex index = selectList.at(i);
// _model->removeRow(index.row());
delRow << index.row();
}
while(delRow.size() > 0)
{
int row = delRow.at(0);
delRow.removeAll(row);
_model->removeRow(row);
}
_model->submitAll();
}
void Widget04::slotSubmitClicked()
{
if(!_model->submitAll())
{
QMessageBox::critical(this, "Error", QSqlDatabase().lastError().text());
}
}
#ifndef WIDGET05_H
#define WIDGET05_H
#include <QWidget>
#include <QSqlQueryModel>
#include <QTableView>
class Widget05 : public QWidget
{
Q_OBJECT
public:
explicit Widget05(QWidget *parent = 0);
QSqlQueryModel* _model;
QTableView *_view;
signals:
public slots:
};
#endif // WIDGET05_H
Widget05.cpp
#include "Widget05.h"
#include <QSqlQuery>
#include <QVBoxLayout>
Widget05::Widget05(QWidget *parent) :
QWidget(parent)
{
_model = new QSqlQueryModel;
_view = new QTableView(this);
_view->setModel(_model);
_model->setQuery("select * from tuser");
_model->query();
QVBoxLayout* lay = new QVBoxLayout(this);
lay->addWidget(_view);
}
main.cpp
#include <QApplication>
#include "Widget05.h"
#include <QSqlDatabase>
#include <QSqlError>
#include <QDebug>
#include "Contact.h"
int main(int argc, char*argv[])
{
QApplication app(argc, argv);
/* QSQLITE QODBC QPLSQL */
QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
db.setHostName("127.0.0.1");
db.setUserName("root");
db.setPassword("iamxgl");
db.setDatabaseName("d0718");
bool bRet = db.open();
if(bRet == false)
{
qDebug() << "error open database" << db.lastError().text();
exit(0);
}
qDebug() << "open database success";
Widget05 w;
w.show();
return app.exec();
}
使用如下方法进行解决：L::
QSqlDatabase: QMYSQL driver not loaded
QSqlDatabase: available drivers: QSQLITE QODBC QODBC3
Qt5.0开源版本默认是不提供QMySQL驱动的，这篇文档主要讲述Windows环境下如何使用MinGW为Qt5.0安装QMySQL驱动。
(1) 安装Qt5.0开源版本的library和source code：
Qt5.0 Library: http://download.qt-project.org/official_releases/qt/5.0/5.0.2/qt-windows-opensource-5.0.2-mingw47_32-x86-offline.exe
Qt5.0 Source Code: http://download.qt-project.org/official_releases/qt/5.0/5.0.2/single/qt-everywhere-opensource-src-5.0.2.zip
(2) 安装MySQL Installer 5.6.11:
MySQL Installer 5.6.11: http://dev.mysql.com/downloads/installer/
将MySQL安装在C盘自建目录MySQL中，尽量不使用Program files目录(空格)可能引起莫名的路径问题。
(3) 打开Qt5.0命令行界面:
启动方式：开始菜单=>>所有程序==>>Qt5.0.2==>>Qt5.0.2==>>MinGW 4.7==>>Qt 5.0.2 for Desktop (MinGW 4.7)
(4) 运行以下命令：
这是最重要的一步，确保Source Code和MySQL安装在同一个盘符。
cd C:\Qt\Qt5.0.1\Sources\qtbase\src\plugins\sqldrivers\mysql\
该目录下存在几个文件，其中包括mysql.pro，我们所有的编译工作也正是从此文件开始。
qmake "INCLUDEPATH+=c:\\MySQL\\include" "LIBS+=c:\\MySQL\\lib\\libmysql.lib" -o Makefile mysql.pro
此时，会在当前目录下生成几个Makefile文件和两个文件夹类。
此处我们也可将mysql的两路径：
INCLUDEPATH += "C:/MySQL/include"
LIBS += "C:/MySQL/lib/libmysql.lib"
直接加入mysql.pro中，然后运行qmake命令如下：
qmake -o Makefile mysql.pro
最后运行：
mingw32-make
如果一切顺利，此时在C:\Qt\Qt5.0.1\Sources\qtbase\plugins\sqldrivers会生成qsqlmysql.dll和qsqlmysqld.dll文件。
(5) 配置QMySQL驱动：
拷贝qsqlmysql.dll和qsqlmysqld.dll到C:\Qt\Qt5.0.1\5.0.1\mingw47_32\plugins\sqldrivers目录中
从c:\MySQL\lib拷贝libmysql.dll到C:\Qt\Qt5.0.2\5.0.2\mingw47_32\bin，此时大功告成。
(6) 测试QMYSQL驱动：
#include <QApplication>
#include <QtSql>
#include <QtWidgets>
#include <iostream>
bool createConnection()
{
QSqlDatabase db=QSqlDatabase::addDatabase("QMYSQL");
db.setHostName("localhost");
db.setDatabaseName("test");
db.setUserName("root");
db.setPassword("123456");
if(!db.open()) {
QMessageBox::critical(0, QObject::tr("Database Error"), \
db.lastError().text());
return false;
}
return true;
}
int main(int argc, char *argv[])
{
QApplication a(argc, argv);
if(!createConnection())
return 1;
QSqlQuery query("SELECT title, year FROM cd WHERE year >= 1930");
while(query.next()) {
QString title=query.value(0).toString();
int year=query.value(1).toInt()
std::cerr<<qPrintable(title)<<": "<<year<<std::endl;
}
return a.exec();
}
E:\QT\chap13\build-QMysqlTest-Desktop_Qt_5_0_2_MinGW_32bit-Debug\debug\QMysqlTest 启动中...
你好: 1983
周杰伦: 1956
萨顶顶: 1957
(7) 好吧，如果你仍然没有成功，可在此直接下载MySQL用于以下Qt版本的驱动：
Qt5.0版本: http://vdisk.weibo.com/s/aMEiEcvN1JUJH
Qt5.1版本: http://vdisk.weibo.com/s/aMEiEcvN1JUJR
虽然版本不一样，但是编译MySQL驱动的方法都遵循以上步骤，最后祝你好运。
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
