03u-01-db-SQL操作DB

于 2023-02-27 10:45:41 发布
阅读量244 收藏
点赞数
分类专栏： Qt-peanutlib使用 文章标签： sql qt c++ sqlite
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wangrq_cn/article/details/129237162
版权

目录

1. 重点内容

2. 运行展示

3. 程序示例

3.2 在peanutsample.db中新建两个表

3.2 程序CURD

4. 代码下载

1. 重点内容

  • 查询操作。
  • 增删改操作。
  • 初步了解使用DB模块操作数据库与Grid表格互动。

2. 运行展示

3. 程序示例

 SQL操作DB数据库，我们将以操作本地sqlite为例来说明如何对一个表进行增删改查。sqlite数据库在程序运行工作目录/db/peanutsample.db中，配置文件peanut_appconfig.xml中，配置DB模块BeanConfig。

 <beanConfig id="dbBeanConfigSample" name="DBModule" type="DB" className="peanut::PlDBBeanConfig" chooseBeanId="" >
        <bean id="sqliteDBBeanSample" name="sqliteDBBeanSample" className="peanut::PlDBController"  databaseName="./db/peanutsample.db"  type="QSQLITE" userName="" port="" hostName="" description="" isEncrypt="false"  password=""   connectOptions="" >		 	
	    </bean>
		<bean id="mysqlDBBeanSample" name="mysql-peanutsample"  className="peanut::PlDBController" hostName="127.0.0.1" userName="root" password="123456"  port="3306"  databaseName="peanutsample"  type="QMYSQL" isEncrypt="false" connectOptions="">
        </bean>
 </beanConfig>

3.2 在peanutsample.db中新建两个表

在peanutsample.db中新建两个表，分别是员工费用总表ps_staff_item,员工费用明细表ps_staff_charge_item，建表语句如下：

CREATE TABLE `ps_staff_item` (

  `staff_no` varchar(20) PRIMARY KEY ASC NOT NULL,

  `staff_name` varchar(30) NOT NULL,

  `borrow_charge` decimal(10,2),

  `dest_address` varchar(50),

  `begin_date` datetime,

  `end_date` datetime,

  `is_refund` char(1) NOT NULL

);

CREATE TABLE `ps_staff_charge_item` (

  `item_id` int(11) PRIMARY KEY ASC NOT NULL,

  `charge_id` int(11) NOT NULL,

  `charge_name` varchar(30) NOT NULL,

  `charge` decimal(12,2) NOT NULL,

  `state_date` datetime NOT NULL,

  `staff_no` varchar(20) NOT NULL

);

3.2 程序CURD

  • 设计ui文件ps_dbcurd_mainwindow.ui

 

<?xml version="1.0" encoding="UTF-8"?>
<ui version="4.0">
 <class>peanut::PsDBcurdMainWindow</class>
 <widget class="QMainWindow" name="peanut::PsDBcurdMainWindow">
  <property name="geometry">
   <rect>
    <x>0</x>
    <y>0</y>
    <width>800</width>
    <height>600</height>
   </rect>
  </property>
  <property name="windowTitle">
   <string>DB模块-CRUD示例</string>
  </property>
  <widget class="QWidget" name="centralwidget">
   <layout class="QVBoxLayout" name="verticalLayout">
    <item>
     <widget class="QGroupBox" name="groupBox">
      <property name="title">
       <string/>
      </property>
      <layout class="QHBoxLayout" name="horizontalLayout">
       <item>
        <widget class="QLabel" name="label">
         <property name="text">
          <string>员工姓名：</string>
         </property>
        </widget>
       </item>
       <item>
        <widget class="QLineEdit" name="le_name"/>
       </item>
       <item>
        <spacer name="horizontalSpacer">
         <property name="orientation">
          <enum>Qt::Horizontal</enum>
         </property>
         <property name="sizeHint" stdset="0">
          <size>
           <width>40</width>
           <height>20</height>
          </size>
         </property>
        </spacer>
       </item>
      </layout>
     </widget>
    </item>
    <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="act_add"/>
   <addaction name="act_update"/>
   <addaction name="act_remove"/>
   <addaction name="act_find"/>
  </widget>
  <action name="act_add">
   <property name="text">
    <string>新增</string>
   </property>
   <property name="toolTip">
    <string>新增员工费用一条记录</string>
   </property>
  </action>
  <action name="act_update">
   <property name="text">
    <string>修改</string>
   </property>
   <property name="toolTip">
    <string>修改员工费用一条记录</string>
   </property>
  </action>
  <action name="act_remove">
   <property name="text">
    <string>删除</string>
   </property>
   <property name="toolTip">
    <string>删除记录</string>
   </property>
  </action>
  <action name="act_find">
   <property name="text">
    <string>查询</string>
   </property>
  </action>
 </widget>
 <resources/>
 <connections/>
</ui>
  • 头文件ps_dbcurd_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_DBCURD_MAINWINDOW_H
#define PS_DBCURD_MAINWINDOW_H

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

#include <grid/pl_grid_info.h>

namespace peanut {

namespace Ui {
class PsDBcurdMainWindow;
}

class PsDBcurdMainWindow : public QMainWindow
{
    Q_OBJECT

public:
    explicit PsDBcurdMainWindow(QWidget *parent = nullptr);
    ~PsDBcurdMainWindow();
protected:
    //初始化
    void init();
    //查询对ps_staff_item的记录
    void selectStaffItem(const QString &name="");

private slots:
    //当前TableView的行发生改变后触发
    void on_currentRowChanged(const QModelIndex &current, const QModelIndex &previous);

    //ps_staff_item表增加一条记录
    void on_act_add_triggered();
    //ps_staff_item表修改staff_name
    void on_act_update_triggered();
    //ps_staff_item表删除一条记录
    void on_act_remove_triggered();
    //查询显示Grid
    void on_act_find_triggered();


private:
    Ui::PsDBcurdMainWindow *ui;

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

}

#endif // PS_DBCURD_MAINWINDOW_H

  • .cpp文件ps_dbcurd_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_dbcurd_mainwindow.h"
#include "ui_ps_dbcurd_mainwindow.h"

#include <pl_utils.h>

namespace peanut {

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

    PsApplication *app = PsApplication::getPsApplication();

    //设置toolBar上各个action的图标
    ui->act_add->setIcon(app->getIcon("plus"));
    ui->act_update->setIcon(app->getIcon("edit"));
    ui->act_remove->setIcon(app->getIcon("remove"));
    ui->act_find->setIcon(app->getIcon("binoculars"));

    //初始化
    init();
}

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

void PsDBcurdMainWindow::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 PsDBcurdMainWindow::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 PsDBcurdMainWindow::on_currentRowChanged(const QModelIndex &current, const QModelIndex &previous)
{
    Q_UNUSED(previous);
    if (!current.isValid())
    {
        return;
    }
}

void peanut::PsDBcurdMainWindow::on_act_add_triggered()
{
    QString errMsg;
    bool isSuccess;
    //取PsApplication对象
    PsApplication *app = PsApplication::getPsApplication();
    try {
        //取DB模块的Bean(beanCoinfgId=dbBeanConfigSample, beanId=sqliteDBBeanSample)
        PlDBController *dbCtrl = app->getDBCtrl("dbBeanConfigSample", "sqliteDBBeanSample");
        if(dbCtrl==nullptr) {
            throw PeanutError(app->getError());
        }

        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);

        //插入表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(staffNo)
                .arg(staffName)
                .arg(QString::asprintf("%.2f",borrowCharge))
                .arg(destAddress)
                .arg(beginDate)
                .arg(endDate)
                .arg(isRefund);
        query = dbCtrl->sqlQuery();  //得到QSqlQuery对象
        LOG_INFO(PlBaseApplication::getGlobalLogger(),sql);
        //执行SQL
        dbCtrl->queryExec(query, sql, isSuccess, errMsg);
        if(!isSuccess) {
            throw PeanutError(dbCtrl->lastError());
        }

        //刷新整个Grid表格
        selectStaffItem();

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

void peanut::PsDBcurdMainWindow::on_act_update_triggered()
{
    Q_ASSERT(m_gridCtrl!=nullptr);
    int rowNo = m_gridCtrl->currentRowIndex();
    if(rowNo<0) {
        PlMessageBox::information(this, tr("提示信息"), tr("请选择一条记录"));
        return;
    }
    QString name = ui->le_name->text().trimmed();
    if(PlStringUtils::isStrEmpty(name)) {
        PlMessageBox::information(this, tr("提示信息"), tr("请输入您要修改的员工姓名"));
        return;
    }
    QString staffNo = m_gridCtrl->getItem(rowNo, "staff_no").toString();

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

        //update语句操作
        QString sql = "update ps_staff_item set staff_name='%1' where staff_no='%2'";
        sql = sql.arg(name).arg(staffNo);
        QSqlQuery query = dbCtrl->sqlQuery(); //得到一个 QSqlQuery对象
        dbCtrl->queryExec(query, sql, isSuccess, errMsg);  //执行sql
        if(!isSuccess) {
            throw PeanutError(dbCtrl->lastError());
        }

        //PlGridSqlQueryController使用的模型是QSqlQueryModel，该模型是只读属性，不能写
//        m_gridCtrl->setItem(rowNo, "staff_name", name);

        selectStaffItem(name);

        PlMessageBox::information(this, tr("提示信息"), tr("修改成功"));
    } catch (PeanutError err) {
        PlMessageBox::warning(this, tr("警告"), err.what());
    }
}

void peanut::PsDBcurdMainWindow::on_act_find_triggered()
{
    try {
        QString text = ui->le_name->text().trimmed();
        selectStaffItem(text);
    } catch (PeanutError err) {
        PlMessageBox::warning(this, tr("警告"), err.what());
    }
}

void peanut::PsDBcurdMainWindow::on_act_remove_triggered()
{
    Q_ASSERT(m_gridCtrl!=nullptr);
    int rowNo = m_gridCtrl->currentRowIndex();
    if(rowNo<0) {
        PlMessageBox::information(this, tr("提示信息"), tr("请选择一条记录"));
        return;
    }
    QString staffNo = m_gridCtrl->getItem(rowNo, "staff_no").toString();
    int ret = PlMessageBox::question(this, "消息", tr("您是否确定删除当前[员工号=%1]的记录\n").arg(staffNo),
                                                     QMessageBox::Ok,QMessageBox::Cancel);
    if(QMessageBox::Ok == ret) {
        QString errMsg;
        bool isSuccess;
        //取PsApplication对象
        PsApplication *app = PsApplication::getPsApplication();
        try {
            //取DB模块的Bean(beanCoinfgId=dbBeanConfigSample, beanId=sqliteDBBeanSample)
            PlDBController *dbCtrl = app->getDBCtrl("dbBeanConfigSample", "sqliteDBBeanSample");
            if(dbCtrl==nullptr) {
                throw PeanutError(app->getError());
            }

            //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());
            }

            selectStaffItem("");  //刷新当前tableView

            PlMessageBox::information(this, tr("提示信息"), tr("删除成功"));
        } catch (PeanutError err) {
            PlMessageBox::warning(this, tr("警告"), err.what());
        }
    }
}

}

4. 代码下载

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

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

积木虎
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

积木虎

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值