Qt操作access数据库
封装QtAccessHelper操作类
头文件QtAccessHelper.h
#pragma once#include 源文件QtAccessHelper.cpp
#include "QtAccessHelper.h"
#include "QTableView"
QtAccessHelper::QtAccessHelper(QObject *parent): QObject(parent)
{
}QtAccessHelper::~QtAccessHelper()
{if (m_dbconn.isValid() && m_dbconn.isOpen()){m_dbconn.close();}
}bool QtAccessHelper::ConnectDB(const QString& dbFileName)
{bool bResult = false;//获取是否存在有数据库连接 m_dbconn = QSqlDatabase::database(dbFileName);if (m_dbconn.isValid())//存在连接直接退出{bResult = true;}else{//创建dbFileName数据库连接 m_dbconn = QSqlDatabase::addDatabase("QODBC", dbFileName);QString connStr = QString("DRIVER={Microsoft Access Driver (*.mdb)};FIL={MS Access};DBQ=%1").arg(dbFileName);m_dbconn.setDatabaseName(connStr);bResult = m_dbconn.open();}return bResult;
}QSqlQueryModel* QtAccessHelper::QueryModel(const QString& tabelName)
{QSqlQueryModel* pQueryModel = nullptr;if (m_dbconn.isOpen()){pQueryModel = new QSqlQueryModel;pQueryModel->setQuery(QString("SELECT * FROM %1;").arg(tabelName), m_dbconn); }return pQueryModel;
}QSqlQueryModel* QtAccessHelper::ExecuteScalar(const QString& sql)
{QSqlQueryModel* pQueryModel = nullptr;if (m_dbconn.isOpen()){QSqlQuery* query = new QSqlQuery(m_dbconn);if (query){bool bResult = query->exec(sql);if(bResult){pQueryModel = new QSqlQueryModel;pQueryModel->setQuery(*query);}query->clear();}}return pQueryModel;
}bool QtAccessHelper::ExecuteNonQuery(const QString& sql)
{bool bResult = false;if (m_dbconn.isOpen()){ QSqlQuery* query = new QSqlQuery(m_dbconn); if (query){bResult = query->exec(sql);query->clear();}}return bResult;
}bool QtAccessHelper::ExistsTable(const QString& tableName)
{bool bResult = false;if (m_dbconn.isOpen()){ bResult = m_dbconn.tables().contains(tableName);}return bResult;
}测试代码头文件
#pragma once#include 测试代码源文件
#include "QtPro.h"
#include "qdebug.h"
#include "qabstractbutton.h"
#include "qstylepainter.h"
#include "QMessageBox"QtPro::QtPro(QWidget *parent) : QMainWindow(parent)
{ui.setupUi(this);m_szDirPath = QCoreApplication::applicationDirPath();//.exe所在路径 bool res = m_AccessDbHelper.ConnectDB(QString("%1/Access.mdb").arg(m_szDirPath));if (!res){qDebug() << QString::fromLocal8Bit("数据库连接失败!");}QSqlQueryModel* pQueryModel = m_AccessDbHelper.QueryModel(QString("Config"));if (pQueryModel != nullptr){ui.tableAboutDlg->setRowCount(pQueryModel->rowCount());ui.tableAboutDlg->setColumnCount(pQueryModel->record().count()-1); setTopLeftCellText(ui.tableAboutDlg,"ID");QString szText;QSqlRecord record;for (int row=0; row < pQueryModel->rowCount(); row++){record = pQueryModel->record(row); //行记录szText = record.value(0).toString();ui.tableAboutDlg->setVerticalHeaderItem(row, new QTableWidgetItem(szText));for (int col=1;col <= record.count();col++){if (row == 0){ szText = record.fieldName(col);ui.tableAboutDlg->setHorizontalHeaderItem(col-1, new QTableWidgetItem(szText));}szText = record.value(col).toString();ui.tableAboutDlg->setItem(row, col-1, new QTableWidgetItem(szText));}}} //end if(pQueryModel != nullptr)
}bool QtPro::eventFilter(QObject* watched, QEvent* event)
{if (event->type() == QEvent::Paint){QAbstractButton* btn = qobject_cast<QAbstractButton*>(watched);if (btn){QStyleOptionHeader opt;opt.init(btn);QStyle::State state = QStyle::State_None;if (btn->isEnabled())state |= QStyle::State_Enabled;if (btn->isActiveWindow())state |= QStyle::State_Active;if (btn->isDown())state |= QStyle::State_Sunken;opt.state = state;opt.rect = btn->rect();opt.text = btn->text(); // this line is the only difference to QTableCornerButton opt.position = QStyleOptionHeader::OnlyOneSection;QStylePainter painter(btn);painter.drawControl(QStyle::CE_Header, opt);return true;}}return false;
}void QtPro::setTopLeftCellText(QTableWidget* table, QString text)
{if (table){QAbstractButton* btn = ui.tableAboutDlg->findChild<QAbstractButton*>();if (btn){btn->setText(QString(text));btn->installEventFilter(this); //按钮注册eventFilter事件QStyleOptionHeader opt;opt.text = btn->text();QSize s = (btn->style()->sizeFromContents(QStyle::CT_HeaderSection, &opt, QSize(), btn).expandedTo(QApplication::globalStrut()));if (s.isValid())ui.tableAboutDlg->verticalHeader()->setMinimumWidth(s.width());}}
}void QtPro::on_btnQuery_clicked()
{QString sql = QString("SELECT * FROM Config WHERE ID < 5");QSqlQueryModel* pQueryModel = m_AccessDbHelper.ExecuteScalar(sql);if (pQueryModel == nullptr)return;QTableView* tableView = new QTableView;tableView->setWindowTitle(sql);tableView->setModel(pQueryModel);tableView->resize(800,300);tableView->show();return;//以下是具体值获取QString strQuery; for (int row = 0; row < pQueryModel->rowCount(); row++){QSqlRecord record = pQueryModel->record(row);for (int col = 0; col < record.count(); col++){strQuery += record.value(col).toString() + " ";}strQuery += "\r\n";}QMessageBox::information(this, "Query Data", strQuery);
}void QtPro::on_btnInsert_clicked()
{if (m_AccessDbHelper.ExistsTable("student")){m_AccessDbHelper.ExecuteNonQuery(QString("drop table student"));}m_AccessDbHelper.ExecuteNonQuery(QString("create table student(id int primary key, name varchar(20)) "));m_AccessDbHelper.ExecuteNonQuery(QString("insert into student values(0,'LiMing')"));m_AccessDbHelper.ExecuteNonQuery(QString("insert into student values(1,'LiuTao')"));m_AccessDbHelper.ExecuteNonQuery(QString("insert into student values(2,'WangHong')"));QString sql = QString("select * from student");QSqlQueryModel* pQueryModel = m_AccessDbHelper.ExecuteScalar(sql);if (pQueryModel == nullptr)return;QString strQuery;for (int row = 0; row < pQueryModel->rowCount(); row++){QSqlRecord record = pQueryModel->record(row);for (int col = 0; col < record.count(); col++){strQuery += record.value(col).toString() + " ";}strQuery += "\r\n";}QMessageBox::information(this, "Query Data", strQuery);pQueryModel->clear(); //注意:不进行clear操作 会导致下一次的某些操作失败,比如删除表格/*QTableView* tableView = new QTableView;tableView->setWindowTitle(sql);tableView->setModel(pQueryModel);tableView->resize(500, 200);tableView->show();*/
}void QtPro::on_btnDelete_clicked()
{if (m_AccessDbHelper.ExistsTable("student")){m_AccessDbHelper.ExecuteNonQuery(QString("drop table student"));}m_AccessDbHelper.ExecuteNonQuery(QString("create table student(id int primary key, name varchar(20)) "));m_AccessDbHelper.ExecuteNonQuery(QString("insert into student values(0,'LiMing')"));m_AccessDbHelper.ExecuteNonQuery(QString("insert into student values(1,'LiuTao')"));m_AccessDbHelper.ExecuteNonQuery(QString("insert into student values(2,'WangHong')"));m_AccessDbHelper.ExecuteNonQuery(QString("insert into student values(3,'ADFASFASDF')"));m_AccessDbHelper.ExecuteNonQuery(QString("insert into student values(4,'ASDFDS')"));m_AccessDbHelper.ExecuteNonQuery(QString("insert into student values(5,'SADFDSF')"));m_AccessDbHelper.ExecuteNonQuery(QString("insert into student values(6,'ASDFDASFWE')"));m_AccessDbHelper.ExecuteNonQuery(QString("insert into student values(7,'EFEFEWAF')"));m_AccessDbHelper.ExecuteNonQuery(QString("insert into student values(8,'ASDFASDFDSAF')"));//删除m_AccessDbHelper.ExecuteNonQuery(QString("delete from student where id >5"));QString sql = QString("select * from student");QSqlQueryModel* pQueryModel = m_AccessDbHelper.ExecuteScalar(sql);if (pQueryModel == nullptr)return;QString strQuery;for (int row = 0; row < pQueryModel->rowCount(); row++){QSqlRecord record = pQueryModel->record(row);for (int col = 0; col < record.count(); col++){strQuery += record.value(col).toString() + " ";}strQuery += "\r\n";}QMessageBox::information(this, "Query Data", strQuery);pQueryModel->clear(); //注意:不进行clear操作 会导致下一次的某些操作失败,比如删除表格/*QTableView* tableView = new QTableView;tableView->setWindowTitle(sql);tableView->setModel(pQueryModel);tableView->resize(500, 200);tableView->show();*/}void QtPro::on_btnModify_clicked()
{if (m_AccessDbHelper.ExistsTable("student")){m_AccessDbHelper.ExecuteNonQuery(QString("drop table student"));}m_AccessDbHelper.ExecuteNonQuery(QString("create table student(id int primary key, name varchar(20)) "));m_AccessDbHelper.ExecuteNonQuery(QString("insert into student values(0,'LiMing')"));m_AccessDbHelper.ExecuteNonQuery(QString("insert into student values(1,'LiuTao')"));m_AccessDbHelper.ExecuteNonQuery(QString("insert into student values(2,'WangHong')"));//改m_AccessDbHelper.ExecuteNonQuery(QString("update student set name = 'aaaaaaaaaaa' where id = 0"));m_AccessDbHelper.ExecuteNonQuery(QString("update student set name = 'bbbbbbbbbbb' where id = 1"));m_AccessDbHelper.ExecuteNonQuery(QString("update student set name = 'ccccccccccc' where id = 2"));QString sql = QString("select * from student");QSqlQueryModel* pQueryModel = m_AccessDbHelper.ExecuteScalar(sql);if (pQueryModel == nullptr)return;QString strQuery;for (int row = 0; row < pQueryModel->rowCount(); row++){QSqlRecord record = pQueryModel->record(row);for (int col = 0; col < record.count(); col++){strQuery += record.value(col).toString() + " ";}strQuery += "\r\n";}QMessageBox::information(this, "Query Data", strQuery);pQueryModel->clear(); //注意:不进行clear操作 会导致下一次的某些操作失败,比如删除表格/*QTableView* tableView = new QTableView;tableView->setWindowTitle(sql);tableView->setModel(pQueryModel);tableView->resize(500, 200);tableView->show();*/
}本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！