Qt操作数据库(Sqlite)

QtSql模块提供了与平台以及数据库种类无关的访问SQL数据库接口
将数据库当做数据源,利用模型/视图类进行显示/操作

  1. 对于习惯SQL语法的用户也可以使用QSqlQuery类执行任意的SQL语句

  2. 对于不熟悉SQL语句的也有QSqlTableModelQSqlRelationalTableModel提供了合适的抽象类

     下面用实例程序进行演示
    应用程序包括中心空间是一个QTabWidget
    (此例子用C++ GUI Qt4 编程第二版本的第13章)
    

在这里插入图片描述
data1标签演示了同时连接两个数据库,用QSqlQuery类用SQL语句对数据库1操作,用QSqlTableModel对数据库2操作,结果用字符串的形式显示在QTextEdit里在这里插入图片描述
data2标签用QSqlTableModel将数据显示在QTableView中,可以在QTableView中对数据库进行操作在这里插入图片描述
data3标签给出了对于有外键的数据库采用QSqlRelationalTableModel类而不用QSqlTableModel在这里插入图片描述
继承了QDialog对话框展示用mapper将数据库的一部分内容映射到QWidget中,以及显示外键内容如何实现.
代码:
mainwindow.h

QT_BEGIN_NAMESPACE
namespace Ui { class MainWindow; }
QT_END_NAMESPACE

class MainWindow : public QMainWindow
{
    Q_OBJECT

public:
    MainWindow(QWidget *parent = nullptr);
    ~MainWindow();

private slots:
    void on_pushButton_clicked();
    void on_list_clicked();
    void on_add_clicked();
    void on_clear_clicked();
    void on_pushButton_list2_clicked();
    void on_pushButton_add2_clicked();
    void on_pushButton_detel_clicked();
    void on_change_clicked();
    void updateEmployeeView();
    void on_addDept_clicked();
    void on_delDept_clicked();

    void on_editEmpl_clicked();

private:
    Ui::MainWindow *ui;
    QSqlDatabase db ;
    QSqlDatabase db2;
    QSqlDatabase db3;
    bool createConnect();
    bool createConnect2();
    bool createConnect3();
    void creatDepartmenPanel();
    void createEmployeePanel();
    void loadingViewData2();
    void loadingViewData3();
};

mainwindow.cpp

MainWindow::MainWindow(QWidget *parent)
    : QMainWindow(parent)
    , ui(new Ui::MainWindow)
{
    ui->setupUi(this);
    createConnect();
    createConnect2();
    createConnect3();
    loadingViewData2();
    loadingViewData3();
//    EmployeeForm *test = new EmployeeForm(1, this);
//    test->show();
}

MainWindow::~MainWindow()
{
    db.close();
    db2.close();
    db3.close();
    delete ui;
}

bool MainWindow::createConnect()
{
    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("E:/Qt_Project/Database/QDatabase_pro/database/data.db");
    if(!db.open()){
        QApplication::beep();
        QMessageBox::critical(nullptr, QObject::tr("Database Error"),
                              db.lastError().text());
        return false;
    }
    return true;
}
bool MainWindow::createConnect2()
{
    db2 = QSqlDatabase::addDatabase("QSQLITE", "OTHER");
    db2.setDatabaseName("E:/Qt_Project/Database/QDatabase_pro/database/data2.db");
    if(!db2.open()){
        QApplication::beep();
        QMessageBox::critical(nullptr, QObject::tr("Database2 Error"),
                              db2.lastError().text());
        return false;
    }
    return true;

}
bool MainWindow::createConnect3()
{
    db3 = QSqlDatabase::addDatabase("QSQLITE", "OTHER2");
    db3.setDatabaseName("E:/Qt_Project/Database/QDatabase_pro/database/data3.db");
    if(!db3.open()){
        QApplication::beep();
        QMessageBox::critical(nullptr, QObject::tr("Database3 Error"),
                              db2.lastError().text());
        return false;
    }
    return true;
}
void MainWindow::on_pushButton_clicked()
{
    createConnect();
}

void MainWindow::on_list_clicked()
{
    ui->textEdit->clear();
    QSqlQuery query;
    query.exec("select * from stu_info");
    if(!query.isActive()){
        QMessageBox::warning(this, QObject::tr("Database Error"), query.lastError().text());
    }
    while(query.next()){
        ui->textEdit->append(query.value(0).toString()+"\t"+query.value(1).toString()+"\t"+query.value(2).toString());
    }
}

void MainWindow::on_add_clicked()
{
    QSqlQuery query;
    query.prepare("insert into stu_info( name, age, address ) values( :name, :age, :address )");
    query.bindValue(":name", ui->lineEdit->text());
    query.bindValue(":age", ui->lineEdit_2->text());
    query.bindValue(":address", ui->lineEdit_3->text());
    query.exec();
    if(!query.isActive()){
        QMessageBox::warning(this, QObject::tr("Database Error"), query.lastError().text());
    }
}

void MainWindow::on_clear_clicked()
{
    ui->textEdit->clear();
}

void MainWindow::on_pushButton_list2_clicked()
{
    ui->textEdit->clear();
    QSqlTableModel *model = new QSqlTableModel(this, db2);
    model->setTable("chengji");
//    model->setFilter("yuwen>70");
    model->select();//结果集放在QSqlRecord
    for(int i=0 ; i<model->rowCount(); i++){
        QSqlRecord record = model->record(i);
//        for(int j=0; j<model->columnCount(); j++){
//             ui->textEdit->append(record.value(j).toString());
//        }
        ui->textEdit->append(record.value(record.indexOf("name")).toString() + "\t" +
                             record.value(record.indexOf("yuwen")).toString() + "\t" +
                             record.value(record.indexOf("shuxue")).toString() + "\t" +
                             record.value(record.indexOf("yingyu")).toString() );
    }
}

void MainWindow::on_pushButton_add2_clicked()
{
    QSqlTableModel *model = new QSqlTableModel(this, db2);
    model->setTable("chengji");
    int row = model->rowCount();
    model->insertRows(row, 1);
    model->setData(model->index(model->rowCount()-1, 0), ui->lineEdit_4->text());
    model->setData(model->index(model->rowCount()-1, 1), ui->lineEdit_6->text());
    model->setData(model->index(model->rowCount()-1, 2), ui->lineEdit_5->text());
    model->setData(model->index(model->rowCount()-1, 3), ui->lineEdit_7->text());
    bool ok = model->submitAll();
    if(!ok){
        QMessageBox::warning(this, QObject::tr("Database Error"), model->lastError().text());
    }
}

void MainWindow::on_pushButton_detel_clicked()
{
    QSqlTableModel *model = new QSqlTableModel(this, db2);
    model->setTable("chengji");
    model->setFilter(QString("name = '%1'").arg(ui->lineEdit_4->text()));
    model->select();
    if(model->rowCount() > 0){
        qDebug()<<model->rowCount();
        model->removeRow(0);
        model->submitAll();
    }
}

void MainWindow::on_change_clicked()
{
    QSqlTableModel *model = new QSqlTableModel(this, db2);
    model->setTable("chengji");
    model->setFilter(QString("name = '%1'").arg(ui->lineEdit_4->text()));
    model->select();
    if(model->rowCount() == 1){
        QSqlRecord record = model->record(0);
        record.setValue(1, ui->lineEdit_6->text());
        record.setValue("shuxue", ui->lineEdit_5->text());
        record.setValue(3,ui->lineEdit_7->text());
        model->setRecord(0, record);
        model->submitAll();
    }
}
void MainWindow::loadingViewData2()
{
    QSqlTableModel *model = new QSqlTableModel(this, db);
    model->setTable("stu_info");
    model->setHeaderData(0,Qt::Horizontal,"name");
    model->setHeaderData(1,Qt::Horizontal,"age");
    model->setHeaderData(2,Qt::Horizontal,"address");
    model->select();
    ui->tableView->setModel(model);
    ui->tableView->setSelectionMode(QAbstractItemView::SingleSelection);
    ui->tableView->setSelectionBehavior(QAbstractItemView::SelectRows);
    ui->tableView->resizeColumnsToContents();
//    ui->tableView->setEditTriggers(QAbstractItemView::NoEditTriggers);
    ui->tableView->horizontalHeader()->setStretchLastSection(true);
}
void MainWindow::loadingViewData3()//有外键的数据库显示再view中
{
    QSqlRelationalTableModel *departmentModel = new QSqlRelationalTableModel(this, db3);
    departmentModel->setTable("department");
    departmentModel->setRelation(2, QSqlRelation("location", "id", "name"));
    departmentModel->setHeaderData(1, Qt::Horizontal, "Dept.");
    departmentModel->setHeaderData(2, Qt::Horizontal, "Location");
    departmentModel->select();

    ui->DepartementsView->setModel(departmentModel);
    ui->DepartementsView->setItemDelegate(new QSqlRelationalDelegate(this));
    ui->DepartementsView->setSelectionMode(QAbstractItemView::SingleSelection);
    ui->DepartementsView->setSelectionBehavior(QAbstractItemView::SelectRows);
    ui->DepartementsView->setColumnHidden(0, true);
    ui->DepartementsView->resizeColumnsToContents();
    ui->DepartementsView->horizontalHeader()->setStretchLastSection(true);

    connect(ui->DepartementsView->selectionModel(), SIGNAL(currentRowChanged(const QModelIndex &,const QModelIndex &)), this, SLOT(updateEmployeeView()));

    QSqlRelationalTableModel *employeeModel = new QSqlRelationalTableModel(this, db3);
    employeeModel->setTable("employee");
    employeeModel->setRelation(2, QSqlRelation("department", "id", "name"));
    employeeModel->setHeaderData(1, Qt::Horizontal,"Name");
    employeeModel->setHeaderData(2, Qt::Horizontal,"Email");
    employeeModel->setHeaderData(3, Qt::Horizontal, "date");

    ui->EmployeesView->setModel(employeeModel);
    ui->EmployeesView->setSelectionMode(QAbstractItemView::SingleSelection);
    ui->EmployeesView->setSelectionBehavior(QAbstractItemView::SelectRows);
    ui->EmployeesView->setEditTriggers(QAbstractItemView::NoEditTriggers);
    ui->EmployeesView->horizontalHeader()->setStretchLastSection(true);
    ui->EmployeesView->setColumnHidden(0, true);
    ui->EmployeesView->setColumnHidden(2, true);
//    ui->EmployeesView->setColumnHidden(4, true);
}
void MainWindow::updateEmployeeView()
{
    QModelIndex index = ui->DepartementsView->currentIndex();
    QSqlRelationalTableModel *departementsModel = static_cast<QSqlRelationalTableModel *>(ui->DepartementsView->model());
    QSqlRelationalTableModel *employeeModel = static_cast<QSqlRelationalTableModel *>(ui->EmployeesView->model());
    if(index.isValid()){
        QSqlRecord record = departementsModel->record(index.row());
        int id = record.value("id").toInt();
        employeeModel->setFilter(QString("departmentid = %1").arg(id));
    }else{
        employeeModel->setFilter("departmentid = -1");
    }
    employeeModel->select();
    ui->EmployeesView->horizontalHeader()->setVisible(employeeModel->rowCount() > 0);
}

void MainWindow::on_addDept_clicked()
{
    QSqlRelationalTableModel *departementsModel = static_cast<QSqlRelationalTableModel *>(ui->DepartementsView->model());
    int row = departementsModel->rowCount();
    departementsModel->insertRow(row);
    QModelIndex index = departementsModel->index(row, 1);
    ui->DepartementsView->setCurrentIndex(index);
    ui->DepartementsView->edit(index);
}

void MainWindow::on_delDept_clicked()
{
    QSqlRelationalTableModel *departementsModel = static_cast<QSqlRelationalTableModel *>(ui->DepartementsView->model());
    QSqlRelationalTableModel *employeeModel = static_cast<QSqlRelationalTableModel *>(ui->EmployeesView->model());
    if(departementsModel == nullptr || employeeModel == nullptr){
        qDebug()<<"changed failed!  "<<__FUNCTION__<<__LINE__;
        return;
    }
    QModelIndex index = ui->DepartementsView->currentIndex();
    if(!index.isValid()){
        return;
    }

    db3.transaction();
    QSqlRecord record = departementsModel->record(index.row());
    int id = record.value(0).toInt();
    qDebug()<<id<<__FUNCTION__;
    employeeModel->setFilter(QString("departmentid = %1").arg(id));
    employeeModel->select();
    qDebug()<<employeeModel->rowCount() ;
    if(employeeModel->rowCount() > 0){
        int r = QMessageBox::warning(this, "Delete Dpartment",
                                     QString("Delete %1 and all its employees?").arg(departementsModel->record(0).value(1).toString()),
                                     QMessageBox::Yes | QMessageBox::No);
        if(r == QMessageBox::No){
            db3.rollback();
            return;
        }
        for(int i=0 ; i<employeeModel->rowCount(); i++)
            employeeModel->removeRow(i);
        employeeModel->submit();
    }
    departementsModel->removeRow(index.row());
    departementsModel->submitAll();
    db3.commit();

    updateEmployeeView();
    ui->DepartementsView->setFocus();
}

void MainWindow::on_editEmpl_clicked()
{
    int employeeID = -1;
    QSqlRelationalTableModel *departementsModel = static_cast<QSqlRelationalTableModel *>(ui->DepartementsView->model());
    QModelIndex index = ui->DepartementsView->currentIndex();
    if(index.isValid()){
        //索引有效 提取当前选中的员工ID  用于创建修改员工信息的对话框
        employeeID = departementsModel->record(index.row()).value("id").toInt();
        qDebug()<<employeeID;
        EmployeeForm Form(employeeID, this);
        Form.exec();
    }
    //创建编辑员工信息的对话框
    updateEmployeeView();
}

employeeform.h

namespace Ui {
class EmployeeForm;
}

class EmployeeForm : public QDialog
{
    Q_OBJECT

public:
    explicit EmployeeForm(int id, QWidget *parent = nullptr);
    ~EmployeeForm();

private slots:
    void on_First_clicked();
    void on_previous_clicked();
    void on_next_clicked();
    void on_Last_clicked();
    void addEmployee();
    void deleteEmployee();
    void chooseDepatment(int index);

private:
    QPushButton *addButton;
    QPushButton *deleteButton;
    QDataWidgetMapper *mapper;
    QSqlRelationalTableModel *tableModel;
    void showDatabase(int id);
    Ui::EmployeeForm *ui;
};

employeeform.cpp

EmployeeForm::EmployeeForm(int id, QWidget *parent) :
    QDialog(parent),
    ui(new Ui::EmployeeForm)
{
    ui->setupUi(this);
    addButton = new QPushButton("Add");
    deleteButton = new QPushButton("delete");
    ui->buttonBox->addButton(addButton, QDialogButtonBox::ActionRole);
    ui->buttonBox->addButton(deleteButton, QDialogButtonBox::ActionRole);
    ui->dateEdit->setCalendarPopup(true);
    ui->dateEdit->setDate(QDate::currentDate());
    showDatabase(id);
    connect(addButton, SIGNAL(clicked()), this, SLOT(addEmployee()));
    connect(deleteButton, SIGNAL(clicked()), this, SLOT(deleteEmployee()));
    connect(ui->comboBox, SIGNAL(currentIndexChanged(int)), this, SLOT(chooseDepatment(int)));


}

EmployeeForm::~EmployeeForm()
{
    delete ui;
}
void EmployeeForm::showDatabase(int id)//将数据库的一部分记录显示在窗口的小部件上
{
    QSqlDatabase db = QSqlDatabase::database("OTHER2");
    if(!db.isValid()){
        qDebug()<<"db unValide!"<<__FUNCTION__;
        this->close();
    }

    tableModel = new QSqlRelationalTableModel(this, db);
    tableModel->setTable("employee");//选定显示的表格
    tableModel->setRelation(2, QSqlRelation("department", "id", "name"));//设置数据库第二列外键关系
//    tableModel->setSort(1, Qt::AscendingOrder);//对表格第一列进行排序
    tableModel->setFilter(QString("departmentid = %1").arg(id));
    tableModel->select();
    qDebug()<<tableModel->rowCount();

    QSqlTableModel *relationModel = tableModel->relationModel(2);//获取第二列的外键模型
    ui->comboBox->setModel(relationModel);//把comboBox当作relationModel的视图
    ui->comboBox->setModelColumn(relationModel->fieldIndex("name"));//指定模型的列,comboBox只能显示一列数据,默认是第一列

    mapper = new QDataWidgetMapper(this);//实现数据模型的一部分映射到窗体部件中
    mapper->setSubmitPolicy(QDataWidgetMapper::AutoSubmit);//设置修改内容后自动提交
    mapper->setModel(tableModel);//指定要映射的模型
    mapper->setItemDelegate(new QSqlRelationalDelegate(this));//对于含有外键的模型需要指定一个关系委托
    mapper->addMapping(ui->EditName, 1);
    mapper->addMapping(ui->comboBox, 2);
    mapper->addMapping(ui->EditEmail, 3);
    mapper->addMapping(ui->dateEdit, 4);

    if(id != -1 && tableModel->rowCount() != 0){
        mapper->setCurrentIndex(0);
//        for(int row=0 ; row<tableModel->rowCount(); row++){
//            QSqlRecord record = tableModel->record(row);
//            qDebug()<<record.value(0).toInt();
//            if(record.value(0).toInt() == id){
//                mapper->setCurrentIndex(row);
//                break;
//            }
        }
//    }else{
//        mapper->toFirst();
//    }
}

void EmployeeForm::on_First_clicked()
{
    mapper->toFirst();
}
void EmployeeForm::on_previous_clicked()
{
    mapper->toPrevious();
}
void EmployeeForm::on_next_clicked()
{
    mapper->toNext();
}
void EmployeeForm::on_Last_clicked()
{
    mapper->toLast();
}
void EmployeeForm::addEmployee()
{
    int row = mapper->currentIndex();
    mapper->submit();
    tableModel->insertRow(row);
    mapper->setCurrentIndex(row);

    ui->EditName->clear();
    ui->EditEmail->clear();
    ui->dateEdit->setDate(QDate::currentDate());
    ui->EditName->setFocus();
}
void EmployeeForm::deleteEmployee()
{
    int row = mapper->currentIndex();
    tableModel->removeRow(row);
    mapper->submit();
    mapper->setCurrentIndex(qMin(row,tableModel->rowCount() - 1));
}
void EmployeeForm::chooseDepatment(int index)
{
    tableModel->setFilter(QString("departmentid = %1").arg(index+1));
    tableModel->select();
    qDebug()<<tableModel->rowCount()<<"sss";
    mapper->setCurrentIndex(0);
}
  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值