03u-02-db-批量操作DB

1.重点内容

• 事务操作
• 批量insert操作。
• 批量delete操作。
• 运行进度条及如何在运行时取消进度条。
• 了解批量操作与Grid的配合使用。

2.运行展示

 图1 批量insert

 图2 批量delete

3.程序示例

程序示例是在psample03u1的基础上增加批量示例等代码，有关内容请参考该示例。

3.1 事务操作

我们首先了解下事务操作，事务操作是为了保证连续多个SQL执行在数据库是一致性操作的，例如，insert A表，insert B表，insert C表是对数据库操作一致性执行，如果在对这三张表操作中有一个操作失败（例如insert B表时主键重复），则对这三张表的都失败，否则都成功。下面给出一个程序片断。

 PsApplication *app = PsApplication::getPsApplication();
    try {
        //取DB模块的Bean(beanCoinfgId=dbBeanConfigSample, beanId=sqliteDBBeanSample)
        dbCtrl = app->getDBCtrl("dbBeanConfigSample", "sqliteDBBeanSample");
        if(dbCtrl==nullptr) {
            throw PeanutError(app->getError());
        }

	 //创建一个数据库事务
        dbCtrl->db().transaction();

   QString sql = "insert A(c1,c2) values(1,2)";
   query = dbCtrl->sqlQuery();  //得到QSqlQuery对象
   //执行SQL
   dbCtrl->queryExec(query, sql, isSuccess, errMsg);
   if(!isSuccess) {
       throw PeanutError(dbCtrl->lastError());
   }
   
   sql = "insert B(c1,c2) values(1,2)";
   query = dbCtrl->sqlQuery();  //得到QSqlQuery对象
   //执行SQL
   dbCtrl->queryExec(query, sql, isSuccess, errMsg);
   if(!isSuccess) {
       throw PeanutError(dbCtrl->lastError());
   }
   
   sql = "insert C(c1,c2) values(1,2)";
   query = dbCtrl->sqlQuery();  //得到QSqlQuery对象
   //执行SQL
   dbCtrl->queryExec(query, sql, isSuccess, errMsg);
   if(!isSuccess) {
       throw PeanutError(dbCtrl->lastError());
   }
 
   //提交数据库
dbCtrl->db().commit();


  } catch(PeanutError err) {
        //事务回滚
        dbCtrl->db().rollback();
        progress.close();
        PlMessageBox::warning(this, tr("警告"), err.what());
    }

3.2 批量操作

• 设计ui文件ps_batchsql_mainwindow.ui

<?xml version="1.0" encoding="UTF-8"?>
<ui version="4.0">
 <class>peanut::PsBatchSqlMainWindow</class>
 <widget class="QMainWindow" name="peanut::PsBatchSqlMainWindow">
  <property name="geometry">
   <rect>
    <x>0</x>
    <y>0</y>
    <width>800</width>
    <height>600</height>
   </rect>
  </property>
  <property name="windowTitle">
   <string>DB模块-批量操作</string>
  </property>
  <widget class="QWidget" name="centralwidget">
   <layout class="QVBoxLayout" name="verticalLayout">
    <item>
     <widget class="QTableView" name="tableView"/>
    </item>
   </layout>
  </widget>
  <widget class="QToolBar" name="toolBar">
   <property name="windowTitle">
    <string>toolBar</string>
   </property>
   <property name="toolButtonStyle">
    <enum>Qt::ToolButtonTextUnderIcon</enum>
   </property>
   <attribute name="toolBarArea">
    <enum>TopToolBarArea</enum>
   </attribute>
   <attribute name="toolBarBreak">
    <bool>false</bool>
   </attribute>
   <addaction name="actBatchAdd"/>
   <addaction name="actBatchDel"/>
   <addaction name="actFind"/>
  </widget>
  <action name="actBatchAdd">
   <property name="text">
    <string>批量增加</string>
   </property>
  </action>
  <action name="actFind">
   <property name="text">
    <string>查询</string>
   </property>
  </action>
  <action name="actBatchDel">
   <property name="text">
    <string>批量删除</string>
   </property>
  </action>
 </widget>
 <resources/>
 <connections/>
</ui>

• 头文件ps_batchsql_mainwindow.h

/***************************************************************************
 * This file is part of the Peanut Library project                         *
 * Copyright (C) 2022 by Wang Ren Qian                                     *
 * author：积木虎 154318869@qq.com                                        *
 *              Peanut Software Studio                                                   *
****************************************************************************/
#ifndef PS_BATCHSQL_MAINWINDOW_H
#define PS_BATCHSQL_MAINWINDOW_H

#include <QMainWindow>
#include <pl_grid.h>

#include <grid/pl_grid_info.h>

namespace peanut {

namespace Ui {
class PsBatchSqlMainWindow;
}

class PsStaffItemInfo
{
public:
    PsStaffItemInfo(){}
    PsStaffItemInfo(const PsStaffItemInfo &other);
    ~PsStaffItemInfo(){}
    PsStaffItemInfo &operator=(const PsStaffItemInfo &other);
    void copy(const PsStaffItemInfo &other);

public:
    QString m_staffNo;
    QString m_staffName;
    qreal   m_borrowCharge;
    QString m_destAddress;
    QString m_beginDate;
    QString m_endDate;
    QString m_isRefund;
};

class PsBatchSqlMainWindow : public QMainWindow
{
    Q_OBJECT

public:
    explicit PsBatchSqlMainWindow(QWidget *parent = nullptr);
    ~PsBatchSqlMainWindow();
protected:
    //初始化
    void init();
    void selectStaffItem(const QString &name="");
    PsStaffItemInfo newStaffItemRecord(PlDBController *dbCtrl, bool &isSuccess, QString &errMsg);
private slots:
    //当前TableView的行发生改变后触发
    void on_currentRowChanged(const QModelIndex &current, const QModelIndex &previous);
    //批量插入
    void on_actBatchAdd_triggered();
    //批量删除
    void on_actBatchDel_triggered();
    //刷新tableView
    void on_actFind_triggered();
private:
    Ui::PsBatchSqlMainWindow *ui;

    PlGridInfo                            m_gridInfo;              //Grid配置
    PlGridSqlQueryController             *m_gridCtrl = nullptr;        //SQl查询只读风格类型的Grid控制器
};

}

#endif // PS_BATCHSQL_MAINWINDOW_H

• .cpp文件ps_batchsql_mainwindow.cpp

/***************************************************************************
 * This file is part of the Peanut Library project                         *
 * Copyright (C) 2022 by Wang Ren Qian                                     *
 * author：积木虎 154318869@qq.com                                        *
 *              Peanut Software Studio                                                   *
****************************************************************************/
#include "ps_application.h"
#include "ps_batchsql_mainwindow.h"
#include "ui_ps_batchsql_mainwindow.h"

#include <pl_form.h>
#include <pl_utils.h>

namespace peanut {

PsBatchSqlMainWindow::PsBatchSqlMainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::PsBatchSqlMainWindow)
{
    ui->setupUi(this);

    PsApplication *app = PsApplication::getPsApplication();

    //设置toolBar上各个action的图标
    ui->actBatchAdd->setIcon(app->getIcon("plus"));
    ui->actBatchDel->setIcon(app->getIcon("remove"));
    ui->actFind->setIcon(app->getIcon("binoculars"));

    //初始化
    init();
}

PsBatchSqlMainWindow::~PsBatchSqlMainWindow()
{
    delete ui;
}

void PsBatchSqlMainWindow::init()
{
    PsApplication *app = PsApplication::getPsApplication();
    try{
         QString sql;
         PlDBController *dbCtrl = app->getDBCtrl("dbBeanConfigSample", "sqliteDBBeanSample");
         if(dbCtrl==nullptr) {
             throw PeanutError(app->getError());
         }

         //********Grid设置 表头 begin*******
         m_gridInfo.insertHearderColumnInfo(PlGridHearderColumnInfo("staff_no","员工号", Qt::Horizontal));
         m_gridInfo.insertHearderColumnInfo(PlGridHearderColumnInfo("staff_name","员工姓名", Qt::Horizontal));
         m_gridInfo.insertHearderColumnInfo(PlGridHearderColumnInfo("borrow_charge","借支额", Qt::Horizontal));
         m_gridInfo.insertHearderColumnInfo(PlGridHearderColumnInfo("dest_address","目的地", Qt::Horizontal));
         m_gridInfo.insertHearderColumnInfo(PlGridHearderColumnInfo("begin_date","借支开始日期", Qt::Horizontal));
         m_gridInfo.insertHearderColumnInfo(PlGridHearderColumnInfo("end_date","借支结束日期", Qt::Horizontal));
         m_gridInfo.insertHearderColumnInfo(PlGridHearderColumnInfo("is_refund","是否借支", Qt::Horizontal));
         //**********************Grid设置 表头 end*******************

         //**********Grid设置 列项 begin**************
         PlGridColumnItemInfo  staffNameInfo;
         staffNameInfo.setColumnName("staff_name");
         staffNameInfo.setTextAlignment(Qt::AlignVCenter|Qt::AlignHCenter);
         m_gridInfo.putColumnItemInfo(staffNameInfo);

         PlGridColumnItemInfo  destAddressInfo;
         destAddressInfo.setColumnName("dest_address");
         destAddressInfo.setTextAlignment(Qt::AlignVCenter|Qt::AlignHCenter);
         m_gridInfo.putColumnItemInfo(destAddressInfo);

         PlGridColumnItemInfo  borrowChargeInfo;
         borrowChargeInfo.setColumnName("borrow_charge");
         borrowChargeInfo.setColumnDataType(PlBaseGridController::Double);
         borrowChargeInfo.setColumnFormat("%.2f");
         borrowChargeInfo.setTextColor(QColor(255,0,0));
         borrowChargeInfo.setTextAlignment(Qt::AlignVCenter|Qt::AlignRight);
         m_gridInfo.putColumnItemInfo(borrowChargeInfo);

         PlGridColumnItemInfo beginDateInfo;
         beginDateInfo.setColumnName("begin_date");
         beginDateInfo.setColumnDataType(PlBaseGridController::Date);
         m_gridInfo.putColumnItemInfo(beginDateInfo);

         PlGridColumnItemInfo endDateInfo;
         endDateInfo.setColumnName("end_date");
         endDateInfo.setColumnDataType(PlBaseGridController::Date);
         m_gridInfo.putColumnItemInfo(endDateInfo);

         PlGridColumnItemInfo refundItemInfo;
         QList<PlItemCodeInfo> refundList;
         PlItemCodeInfo refundItemCodeInfo0;
         refundItemCodeInfo0.setParentItemCode("samp.refund");
         refundItemCodeInfo0.setItemCode("samp.refund.0");
         refundItemCodeInfo0.setItemName("未借支");
         refundItemCodeInfo0.setItemValue("0");
         refundList.append(refundItemCodeInfo0);
         PlItemCodeInfo refundItemCodeInfo1;
         refundItemCodeInfo1.setParentItemCode("samp.refund");
         refundItemCodeInfo1.setItemCode("samp.refund.1");
         refundItemCodeInfo1.setItemName("已借支");
         refundItemCodeInfo1.setItemValue("1");
         refundList.append(refundItemCodeInfo1);
         refundItemInfo.setColumnName("is_refund");
         refundItemInfo.setIsEdit(false);
         refundItemInfo.setColumnDataType(PlBaseGridController::ItemCodeListMap);
         refundItemInfo.setTextAlignment(Qt::AlignVCenter|Qt::AlignHCenter);
         refundItemInfo.setItemCodeInfos(refundList);
         m_gridInfo.putColumnItemInfo(refundItemInfo);

         //**********Grid设置 列项 end********************

         //**********Grid设置 begin*****************
         sql = " select staff_no,staff_name,borrow_charge,dest_address,begin_date,end_date,is_refund from ps_staff_item ";
         m_gridInfo.setSqlQueryText(sql);
         m_gridInfo.setDbController(dbCtrl);
         m_gridInfo.setView(ui->tableView);
         m_gridInfo.setSelectionBehavior(QAbstractItemView::SelectRows);
         m_gridInfo.setSelectionMode(QAbstractItemView::SingleSelection);
         ui->tableView->setAlternatingRowColors(true);
         //**********Grid设置 end*****************

         //创建Grid
         m_gridCtrl = new PlGridSqlQueryController(m_gridInfo,this);
         //初始化Grid
         if(!m_gridCtrl->initialize()) {
              throw PeanutError(m_gridCtrl->getLastError());
         }

         //绑定tableWidget对象的当前行发生改变信号与槽
         connect(ui->tableView->selectionModel(),SIGNAL(currentRowChanged(QModelIndex,QModelIndex)),
                     this,SLOT(on_currentRowChanged(QModelIndex,QModelIndex)));

    }
    catch(PeanutError err) {
        PlMessageBox::warning(this, tr("警告"), err.what());
    }
}

void PsBatchSqlMainWindow::selectStaffItem(const QString &name)
{
    if(m_gridCtrl!=nullptr) {
        //查询表ps_staff_item的sql
        QString sql = " select staff_no,staff_name,borrow_charge,dest_address,begin_date,end_date,is_refund from ps_staff_item ";
        if(!PlStringUtils::isStrEmpty(name)) {
            sql = sql + QString(" where staff_name like '%1'").arg(name);
        }
        //Grid表格使用SQL刷新表格
        if(!m_gridCtrl->select(sql)) {
             throw PeanutError(m_gridCtrl->getLastError());
        }
    }
}

void PsBatchSqlMainWindow::on_currentRowChanged(const QModelIndex &current, const QModelIndex &previous)
{
    Q_UNUSED(previous);
    if (!current.isValid())
    {
        return;
    }
}

void peanut::PsBatchSqlMainWindow::on_actBatchAdd_triggered()
{
    QString sql, errMsg;
    bool isSuccess;
    PlDBController *dbCtrl;
    PlProgressWaitDialogProxy progress(this);
    progress.show();
    progress.setProgressValue(1);
    //取PsApplication对象
    PsApplication *app = PsApplication::getPsApplication();
    try {
        //取DB模块的Bean(beanCoinfgId=dbBeanConfigSample, beanId=sqliteDBBeanSample)
        dbCtrl = app->getDBCtrl("dbBeanConfigSample", "sqliteDBBeanSample");
        if(dbCtrl==nullptr) {
            throw PeanutError(app->getError());
        }
        QSqlQuery query;
        //创建一个事务
        dbCtrl->db().transaction();
        for(int i=0; i<100; i++) {
            PsStaffItemInfo info = newStaffItemRecord(dbCtrl, isSuccess, errMsg);
            if(!isSuccess) {
                throw PeanutError(errMsg);
            }
            //插入表ps_staff_item的sql
            sql = "insert into ps_staff_item(staff_no,staff_name,borrow_charge,dest_address,begin_date,end_date,is_refund) ";
            sql = sql + "values('%1','%2',%3,'%4',%5,%6,'%7')";
            sql = sql.arg(info.m_staffNo)
                    .arg(info.m_staffName)
                    .arg(QString::asprintf("%.2f",info.m_borrowCharge))
                    .arg(info.m_destAddress)
                    .arg(info.m_beginDate)
                    .arg(info.m_endDate)
                    .arg(info.m_isRefund);
            query = dbCtrl->sqlQuery();  //得到QSqlQuery对象
            LOG_INFO(PlBaseApplication::getGlobalLogger(),sql);
            //执行SQL
            dbCtrl->queryExec(query, sql, isSuccess, errMsg);
            if(!isSuccess) {
                throw PeanutError(dbCtrl->lastError());
            }

            //每10条数据提交一次
            if(!(i%10)) {
                //事务提交
               dbCtrl->db().commit();
            }
            progress.setProgressValue(i+1,QString("已插入员工[%1]的记录").arg(info.m_staffNo));
        }
        dbCtrl->db().commit();
        //刷新整个Grid表格
        selectStaffItem();
    } catch(PeanutError err) {
        //事务回滚
        dbCtrl->db().rollback();
        progress.close();
        PlMessageBox::warning(this, tr("警告"), err.what());
    }
}

PsStaffItemInfo PsBatchSqlMainWindow::newStaffItemRecord(PlDBController *dbCtrl, bool &isSuccess, QString &errMsg)
{
    PsStaffItemInfo info;
    try {
        int count;
        //获取当前ps_staff_item表的staff_no的最大值
        QString sql = "select max(staff_no) as staff_no_max from ps_staff_item";
        QSqlQuery query = dbCtrl->sqlQuery(); //得到一个 QSqlQuery对象
        dbCtrl->queryExec(query, sql, isSuccess, errMsg);  //执行sql
        if(!isSuccess) {
            throw PeanutError(dbCtrl->lastError());
        }
        //将query对象指向sql结果集的首行
        query.first();
        //获取结果集的值
        count = dbCtrl->queryIntValue(query, "staff_no_max");

        //置staff_no的值
        QString staffNo = QString("%1").arg(++count);
        //staff_no是一个10位长的数，不足位在前面补0
        staffNo = PlStringUtils::fillCharFormat(staffNo, "0", 10, true);
        //置staff_namer的值
        QString staffName = QString("员工%1").arg(count);  //

        //置borrow_charge的值，取的是0至5000内的随机值
        qreal borrowCharge = 0.0+(qrand() % 5000);

        //置dest_address的值
        QStringList destAddressList;
        destAddressList.append("长沙");
        destAddressList.append("北京");
        destAddressList.append("上海");
        destAddressList.append("广州");
        destAddressList.append("深圳");
        destAddressList.append("南京");
        destAddressList.append("成都");
        destAddressList.append("武汉");
        int destIndex = qrand() % 8;
        QString destAddress = destAddressList.at(destIndex);

        //置begin_date的值
        int month = 1+(qrand() % 12);  //取随机月份数
        int day = 1+(qrand() % 30);  //取随机日期数
        if(month==2) {
            if(day>28) day = 28;
        }
        QString beginDate = QString("'2021-%1-%2'").arg(PlStringUtils::fillIntZero(month)).arg(PlStringUtils::fillIntZero(day));
        month = 1+(qrand() % 12);
        day = 1+(qrand() % 30);
        QString endDate = QString("'2022-%1-%2'").arg(PlStringUtils::fillIntZero(month)).arg(PlStringUtils::fillIntZero(day));

        //置refund值
        int refund = qrand() % 2;
        QString isRefund = QString("%1").arg(refund);

        info.m_staffNo = staffNo;
        info.m_staffName = staffName;
        info.m_borrowCharge = borrowCharge;
        info.m_beginDate = beginDate;
        info.m_endDate = endDate;
        info.m_isRefund = isRefund;

        isSuccess = true;
    } catch(PeanutError err) {
        isSuccess = false;
        errMsg = err.what();
    }
    return info;
}

void peanut::PsBatchSqlMainWindow::on_actBatchDel_triggered()
{
    QSqlQueryModel *model = m_gridCtrl->getQueryModel();
    int row=0;
    QString errMsg;
    bool isSuccess;
    PlDBController *dbCtrl;
    PlProgressWaitDialogProxy progress(this);
    try {
        //取PsApplication对象
        PsApplication *app = PsApplication::getPsApplication();
        //取DB模块的Bean(beanCoinfgId=dbBeanConfigSample, beanId=sqliteDBBeanSample)
        dbCtrl = app->getDBCtrl("dbBeanConfigSample", "sqliteDBBeanSample");
        if(dbCtrl==nullptr) {
            throw PeanutError(app->getError());
        }
        //超过256行处理
        while(model->canFetchMore()) {
            model->fetchMore();
        }        
        progress.setProgressWaitModality(Qt::ApplicationModal);  //设置进度条对整个应用模态
        progress.setHideCancelButton(false);  //设置进度条取消按钮
        progress.setRange(1, model->rowCount());  //设置进度条的刻度
        progress.show();
        //创建一个事务
        dbCtrl->db().transaction();
        for(row=0; row<model->rowCount(); row++) {
            QString staffNo = m_gridCtrl->getItem(row, "staff_no").toString();
            //delete语句操作
            QString sql = "delete from ps_staff_item where staff_no='%1'";
            sql = sql.arg(staffNo);
            QSqlQuery query = dbCtrl->sqlQuery(); //得到一个 QSqlQuery对象
            dbCtrl->queryExec(query, sql, isSuccess, errMsg);  //执行sql
            if(!isSuccess) {
                throw PeanutError(dbCtrl->lastError());
            }
            //每10条数据提交一次
            if(!(row%10)) {
                //事务提交
               dbCtrl->db().commit();
            }
            progress.setProgressValue(row+1, QString("已准备删除员工[%1]的记录").arg(staffNo));
            if(progress.wasCanceled()) {   //如果进度条得到取消响应
                progress.setProgressValue(model->rowCount(), "程序已中断");
                break;
            }
        }
        //事务提交
        dbCtrl->db().commit();
        selectStaffItem("");
        PlMessageBox::information(this, tr("提示信息"), tr("删除成功"));
    } catch (PeanutError err) {
        dbCtrl->db().rollback();
        progress.close();
        PlMessageBox::warning(this, tr("警告"), err.what());
    }
}

void peanut::PsBatchSqlMainWindow::on_actFind_triggered()
{
    try {
        selectStaffItem();
    } catch (PeanutError err) {
        PlMessageBox::warning(this, tr("警告"), err.what());
    }
}


PsStaffItemInfo::PsStaffItemInfo(const PsStaffItemInfo &other)
{
    copy(other);
}

PsStaffItemInfo &PsStaffItemInfo::operator=(const PsStaffItemInfo &other)
{
    if (this == &other)
       return *this;
    copy(other);
    return *this;
}

void PsStaffItemInfo::copy(const PsStaffItemInfo &other)
{
    m_staffNo        =  other.m_staffNo     ;
    m_staffName      =  other.m_staffName   ;
    m_borrowCharge   =  other.m_borrowCharge;
    m_destAddress    =  other.m_destAddress ;
    m_beginDate      =  other.m_beginDate   ;
    m_endDate        =  other.m_endDate     ;
    m_isRefund       =  other.m_isRefund    ;
}

3.3 运行进度条

进度条提示操作人员当前运行到了多少，到了哪一步，还剩下多少未运行完成，还可以在运行过程中中断执行，在本例void on_actBatchDel_triggered()方法中，就有相关示例代码。

 progress.setProgressWaitModality(Qt::ApplicationModal);  //设置进度条对整个应用模态
        progress.setHideCancelButton(false);  //设置进度条取消按钮
        progress.setRange(1, model->rowCount());  //设置进度条的刻度
        progress.show();

...
for(row=0; row<model->rowCount(); row++) {

	progress.setProgressValue(row+1, QString("已准备删除员工[%1]的记录").arg(staffNo));
        if(progress.wasCanceled()) {   //如果进度条得到取消响应
            progress.setProgressValue(model->rowCount(), "程序已中断");
            break;
        }
}

4.代码下载

代码下载链接： 链接: https://pan.baidu.com/s/1G_8HyaPIcIfL4FXYbQBtaA?pwd=6ge9 提取码: 6ge9

Peanutlib项目演示程序下载：peanut_pwdis: pwdis是QT应用系统开发框架（C++），采用分层模块化设计，底层peanutlib按模块封装方便易用的类库及API（xml,db,appconfig,log,grid,json,bean等十几个），应用层提供部门人员权限及报表等，还提供了开发中常用的组件使用。