Qt访问数据库(ACCESS)基本操作

应用程序经常需要存储大量数据和表，因此对数据的基本操作很有必要；

这里只涉及基本操作，不涉及高级功能；

例如：对用户的管理界面

可以实现对用户新增、删除和修改功能

 mysqltablemodel可以不用，直接使用QSqlTableModel类

showDialog函数用于接收信号，显示本界面窗口

#include 
#include "mysqltablemodel.h"
#include class UserManagement : public QDialog
{Q_OBJECT //使用信号与槽需要的宏
public:explicit UserManagement(QWidget *parent = nullptr);  //explicit 防止歧义~UserManagement();bool editPWD(QString Name, QString pwd);
public slots:void showDialog();
signals:private slots:void addUserSlot();void deleteUserSlot();void editUserSlot();private:QPushButton *editBtn, *addBtn, *delBtn;QPushButton *exitBtn;        //退出按钮QSqlDatabase db;QGridLayout *gridLayout;QTableView * tableview;MySqlTableModel * model;const QString database = "Database2.mdb";bool userExist(QString user);bool insertUser(QString name, QString pwd, bool admin);//插入新用户bool deleteUser(QString name);void modelUpdate();bool editUser(QString oldName, QString newName, QString pwd, bool admin);};#endif // USERMANAGEMENT_H

#include"usermanagement.h"
#include 
#include 
#include 
#include 
#include 
#include 
#include 
#include 
#include 
#include 
#include 
#include 
UserManagement::UserManagement(QWidget *parent) :QDialog(parent)
{this->setWindowTitle(tr("用户管理界面"));if(QSqlDatabase::contains("monitor"))db = QSqlDatabase::database("monitor");elsedb = QSqlDatabase::addDatabase("QODBC","acdc_monitor");db.setDatabaseName(QString("DRIVER={Microsoft Access Driver (*.mdb)};FIL={MS Access};DBQ=%1;").arg( QCoreApplication::applicationDirPath() + "/"+database));if(!db.isOpen())  db.open();gridLayout = new QGridLayout;tableview = new QTableView;model = new MySqlTableModel(nullptr,db);modelUpdate();gridLayout->addWidget(tableview,0,0,4,4);addBtn = new QPushButton(this);addBtn->setText(tr("新增"));gridLayout->addWidget(addBtn,4,0,1,1);delBtn = new QPushButton(this);delBtn->setText(tr("删除"));gridLayout->addWidget(delBtn,4,1,1,1);editBtn = new QPushButton(this);editBtn->setText(tr("修改"));gridLayout->addWidget(editBtn,4,2,1,1);exitBtn = new QPushButton(this);exitBtn->setText(tr("退出"));gridLayout->addWidget(exitBtn,5,3,1,1);this->setLayout(gridLayout);connect(addBtn,SIGNAL(clicked()),this,SLOT(addUserSlot()));connect(delBtn,SIGNAL(clicked()),this,SLOT(deleteUserSlot()));connect(editBtn,SIGNAL(clicked()),this,SLOT(editUserSlot()));connect(exitBtn,SIGNAL(clicked()),this,SLOT(hide()));
}
UserManagement::~UserManagement()
{db.close();
}
void UserManagement::addUserSlot()
{QString userName, pwd;bool ok;userName = QInputDialog::getText(this, tr("输入用户名"),tr("用户名:"), QLineEdit::Normal,"", &ok);//查看用户名是否存在if (ok && !userName.isEmpty()){if(userExist(userName.trimmed())){QMessageBox::warning(this,tr("警告"),tr("用户名已存在！"));return;}}else {return;}pwd = QInputDialog::getText(this, tr("输入密码"),tr("密码:"), QLineEdit::Normal,"", &ok);if(ok && !pwd.isEmpty()){//管理员权限QStringList items;items << tr("否") << tr("是");QString item = QInputDialog::getItem(this, tr("管理员权限"),tr("管理员"), items, 0, false, &ok);if (ok && !item.isEmpty()){//新增用户 并更新insertUser(userName,pwd,item==tr("是"));qDebug() << "add user " << userName.trimmed() << " "<< pwd << endl;modelUpdate();}}
}
bool UserManagement::userExist(QString user)
{if(!db.isOpen()){db.open();}bool res = true;QSqlQuery query = QSqlQuery(db);query.clear();query.prepare("SELECT * FROM [users] WHERE userName=:usr;");//query.bindValue(0,user);query.exec();if(query.seek(0)){qDebug() << query.value(0).toInt() <<  query.value(1).toString() << query.value(2).toString() << query.value(3).toBool();res = true;}else {res = false;}query.clear();return res;
}
bool UserManagement::insertUser(QString name, QString pwd, bool admin)
{if(!db.isOpen()){db.open();}QSqlQuery query = QSqlQuery(db);query.clear();query.prepare("insert into users (userName,passWord,admin) values (:name,:pwd,:admin)");//query.bindValue(0,name);query.bindValue(1,pwd);query.bindValue(2,admin);bool ok = query.exec();query.clear();return ok;
}
bool UserManagement::deleteUser(QString name)
{if(!db.isOpen()){db.open();}QSqlQuery query = QSqlQuery(db);query.clear();query.prepare("delete from users WHERE userName=:usr;");//query.bindValue(0,name);bool ok = query.exec();query.clear();return ok;
}
void UserManagement::modelUpdate()
{if(!db.isOpen()){db.open();}model->setTable("users");model->setEditStrategy(QSqlTableModel::OnManualSubmit);model->select(); //选取整个表的所有行model->removeColumn(0); //不显示第二列,如果这时添加记录，则该属性的值添加不上model->setHeaderData(0, Qt::Horizontal, QObject::tr("用户名"));model->setHeaderData(1, Qt::Horizontal, QObject::tr("密码"));model->setHeaderData(2, Qt::Horizontal, QObject::tr("权限:1=管理员"));tableview->setModel(model);tableview->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);tableview->setEditTriggers(QAbstractItemView::NoEditTriggers);
}
void UserManagement::deleteUserSlot()
{QModelIndex i = tableview->currentIndex();if(i.isValid()){int row = i.row();QStringList ls;for(int j = 0; j < 3; j++)ls.push_back(model->data(model->index(row,j),Qt::DisplayRole).toString());if(ls[0]=="") return;if(QMessageBox::warning(this,tr("警告"),tr("确认删除用户")+ls[0]+"?",QMessageBox::Ok,QMessageBox::Cancel) == QMessageBox::Ok){//delete userdeleteUser(ls[0]);modelUpdate();}}
}
void UserManagement::editUserSlot()
{QModelIndex i = tableview->currentIndex();if(i.isValid()){int row = i.row();QStringList ls;for(int j = 0; j < 3; j++)ls.push_back(model->data(model->index(row,j),Qt::DisplayRole).toString());if(QMessageBox::warning(this,tr("警告"),tr("确认修改用户")+ls[0]+"?",QMessageBox::Ok,QMessageBox::Cancel) == QMessageBox::Ok){QString oldName = ls[0], pwd = ls[1], newName;if(oldName=="" || pwd == "") return;bool ok;newName = QInputDialog::getText(this, tr("输入用户名"),tr("用户名:"), QLineEdit::Normal,oldName, &ok);//查看用户名是否存在if (ok && !newName.trimmed().isEmpty()){if(newName!=oldName && userExist(newName.trimmed())){QMessageBox::warning(this,tr("警告"),tr("用户名已存在！"));return;}}else{return;}pwd = QInputDialog::getText(this, tr("输入密码"),tr("密码:"), QLineEdit::Normal,pwd, &ok);if(ok && !pwd.isEmpty()){//管理员权限QStringList items;items << tr("否") << tr("是");QString item = QInputDialog::getItem(this, tr("管理员权限"),tr("管理员"), items, ls[2].toInt(), false, &ok);if (ok && !item.isEmpty()){editUser(oldName,newName.trimmed(),pwd,item==tr("是"));qDebug() << "edit user " << newName.trimmed() << " "<< pwd << endl;modelUpdate();}}}}
}
bool UserManagement::editUser(QString oldName, QString newName, QString pwd, bool admin)
{if(!db.isOpen()){db.open();}QSqlQuery query = QSqlQuery(db);query.clear();query.prepare("update users set userName=:name, passWord=:pwd, admin=:admin where userName=:oldname;");//query.bindValue(0,newName);query.bindValue(1,pwd);query.bindValue(2,admin);query.bindValue(3,oldName);bool ok = query.exec();query.clear();return ok;
}
bool UserManagement::editPWD(QString Name, QString pwd)
{if(!db.isOpen()){db.open();}QSqlQuery query = QSqlQuery(db);query.clear();query.prepare("update users set passWord=:pwd where userName=:Name;");//query.bindValue(0,pwd);query.bindValue(1,Name);bool ok = query.exec();query.clear();return ok;
}
void UserManagement::showDialog()
{modelUpdate();this->show();
}

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！