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

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

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

例如：对用户的管理界面

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

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

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

#include <QSqlTableModel>
#include "mysqltablemodel.h"
#include <QSqlDatabase>

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 <QGridLayout>
#include <QTableView>
#include <QHeaderView>
#include <QSqlQuery>
#include <QSqlError>
#include <QDebug>
#include <QCoreApplication>
#include <QSqlRecord>
#include <QSqlDatabase>
#include <QInputDialog>
#include <QMessageBox>
#include <QModelIndex>
UserManagement::UserManagement(QWidget *parent) :
    QDialog(parent)
{
    this->setWindowTitle(tr("用户管理界面"));
    if(QSqlDatabase::contains("monitor"))
        db = QSqlDatabase::database("monitor");
    else
        db = 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 user
            deleteUser(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();
}