qt實現sqlite3的串聯刪除，qt實現sqlite3

qt實現sqlite3的串聯刪除，qt實現sqlite3
1.需求有兩張表，條碼錶和產品詳情表，條碼錶中的rfid為產品表中的rfid外鍵，要求刪除產品表中的相關條目時能實現條碼錶的串聯刪除2.解決使用qt中的sqlite3內建的串聯刪除解決這個需求3.代碼mymain.cpp

#include "mysql.h"#include <QtWidgets/QApplication>#include <QSqlDatabase>#include <QSqlError>#include <QSqlQuery>#include <QtCore/QDir>#include <QMessageBox>bool createdb();int main(int argc, char *argv[]){    QApplication a(argc, argv);    createdb();    mysql w;    w.show();    return a.exec();}bool createdb(){    bool bret = false;    //這裡建立一個db目錄存放資料庫檔案    QString strdbpath("");    strdbpath = QCoreApplication::applicationDirPath();    strdbpath += "/db";    QDir dir("");    dir.mkpath(strdbpath);    strdbpath += "/mysqlite.db";    QSqlDatabase dbset = QSqlDatabase::addDatabase("QSQLITE", "file");    dbset.setDatabaseName(strdbpath);    if (!dbset.open()) {        //LOG_ALL_ERROR(QStringLiteral("failed open mysqlite.db"));        return bret;    }     QSqlQuery setquery(QSqlDatabase::database("file", true));     if(!setquery.exec("PRAGMA foreign_keys = ON;"))     {         QSqlError sqlerror = setquery.lastError();         QString texterr = sqlerror.text();         QMessageBox::information(nullptr, "errormsg", texterr);         return false;     }    //商品詳情表    bret = setquery.exec("create table tb_goods(rfid varchar(33) primary key, name varchar(200) not null)");    if (!bret)    {        QSqlError sqlerror = setquery.lastError();        QString texterr = sqlerror.text();        if (texterr.contains("already exists", Qt::CaseInsensitive))            bret = true;        else        {            //LOG_ALL_ERROR(QStringLiteral("failed create table tb_goods."));            return bret;        }    }    //這裡條碼對照表和skuid對照表因為可能牽涉到多對多的情況 故沒有作為主鍵    //條碼對照表    bret = setquery.exec("create table tb_barcode(id INTEGER PRIMARY KEY AUTOINCREMENT, "                         "barcode varchar(100) not null, rfid varchar(33) not null, "                         "FOREIGN KEY(rfid) REFERENCES tb_goods(rfid)  ON DELETE cascade)");    if (!bret)    {        QSqlError sqlerror = setquery.lastError();        QString texterr = sqlerror.text();        if (texterr.contains("already exists", Qt::CaseInsensitive))            bret = true;        else        {            return bret;        }    }    return bret;}

mysql.h

#ifndef MYSQL_H#define MYSQL_H#include <QtWidgets/QMainWindow>#include <QSqlQueryModel>#include "ui_mysql.h"class mysql : public QMainWindow{    Q_OBJECTpublic:    mysql(QWidget *parent = 0);    ~mysql();private slots:    void on_insertButton_clicked();    void on_deleteButton_clicked();    void on_updateButton_clicked();private:    void reflushModel();    void setAttibutes(QTableView* pView, int nHeaderHeight = 45, int nColumnHeight = 45);private:    Ui::mysqlClass ui;    QSqlQueryModel m_goodsmodel;    QSqlQueryModel m_barcodemodel;};#endif // MYSQL_H

mysql.cpp

#include "mysql.h"#include <QSqlError>#include <QSqlQuery>#include <QScrollBar>mysql::mysql(QWidget *parent)    : QMainWindow(parent){    ui.setupUi(this);    m_goodsmodel.setQuery("select * from tb_goods", QSqlDatabase::database("file", true));    m_goodsmodel.setHeaderData(0, Qt::Horizontal, QStringLiteral("id"));    m_goodsmodel.setHeaderData(1, Qt::Horizontal, QStringLiteral("名稱"));    m_barcodemodel.setQuery("select * from tb_barcode", QSqlDatabase::database("file", true));    m_barcodemodel.setHeaderData(0, Qt::Horizontal, QStringLiteral("id"));    m_barcodemodel.setHeaderData(1, Qt::Horizontal, QStringLiteral("掃描碼"));    m_barcodemodel.setHeaderData(2, Qt::Horizontal, QStringLiteral("rfid"));    ui.goodView->setModel(&m_goodsmodel);    ui.skuidView->setModel(&m_barcodemodel);    setAttibutes(ui.goodView);    setAttibutes(ui.skuidView);}mysql::~mysql(){}void mysql::on_insertButton_clicked(){    QSqlQuery setquery(QSqlDatabase::database("file", true));    setquery.exec("insert into tb_goods values('123456789', 'aaa')");    setquery.exec("insert into tb_goods values('23456789', 'aaeffdda')");    setquery.exec("insert into tb_barcode(barcode,rfid) values('adddaeee', '123456789')");    reflushModel();}//這裡應該問題不是很大void mysql::on_deleteButton_clicked(){    QSqlQuery setquery(QSqlDatabase::database("file", true));    setquery.exec("delete from tb_goods where rfid='123456789'");    reflushModel();}void mysql::on_updateButton_clicked(){    QSqlQuery setquery(QSqlDatabase::database("file", true));    setquery.exec("update tb_goods set name='redddd' where rfid='123456789'");    reflushModel();}void mysql::reflushModel(){    m_goodsmodel.setQuery("select * from tb_goods", QSqlDatabase::database("file", true));    m_barcodemodel.setQuery("select * from tb_barcode", QSqlDatabase::database("file", true));}void mysql::setAttibutes(QTableView* pView, int nHeaderHeight, int nColumnHeight){    if ( pView == NULL )    {        QString strTemp = QStringLiteral("pView== NULL！設定表格屬性失敗！");        return;    }    pView->horizontalHeader()->setFixedHeight(nHeaderHeight);               //設定表頭的高度    pView->horizontalHeader()->setSectionsClickable(false);                 //設定表頭不可點擊（預設點擊後進行排序）    pView->horizontalHeader()->setStretchLastSection(true);                 //最後一列佔滿剩餘空白    pView->setSelectionBehavior(QAbstractItemView::SelectRows);             //設定選擇行為時每次選擇一行        pView->setEditTriggers(QAbstractItemView::NoEditTriggers);              //使表視圖唯讀        pView->verticalHeader()->setDefaultSectionSize(nColumnHeight);          //設定行高    pView->setAlternatingRowColors(true);                                   //可以交替顏色顯示    pView->setShowGrid(false);                                              //設定不顯示格子線    pView->setCornerButtonEnabled(false);                                   //左上方的按鈕不可用，此按鈕功能，一點擊，全選    pView->horizontalScrollBar()->setStyleSheet(        "QScrollBar:horizontal{height:10px;background:transparent;background-color:rgb(248, 248, 248);margin:0px,0px,0px,0px;padding-left:10px;padding-right:10px;}"        "QScrollBar::handle:horizontal{height:10px;background:lightgray;border-radius:5px;/*min-height:20;*/}"        "QScrollBar::handle:horizontal:hover{height:10px;background:gray;border-radius:5px;/*min-height:20;*/}"        "QScrollBar::add-line:horizontal{/*height:10px;width:10px;*/border-image:url(:/button/images/button/right.png);/*subcontrol-position:right;*/}"        "QScrollBar::sub-line:horizontal{/*height:10px;width:10px;*/border-image:url(:/button/images/button/left.png);/*subcontrol-position:left;*/}"        "QScrollBar::add-line:horizontal:hover{/*height:10px;width:10px;*/border-image:url(:/button/images/button/right_mouseDown.png);/*subcontrol-position:right;*/}"        "QScrollBar::sub-line:horizontal:hover{/*height:10px;width:10px;*/border-image:url(:/button/images/button/left_mouseDown.png);/*subcontrol-position:left;*/}"        "QScrollBar::add-page:horizontal,QScrollBar::sub-page:horizontal{background:transparent;border-radius:5px;}"        );    pView->verticalScrollBar()->setStyleSheet(        "QScrollBar:vertical{width:10px;background:transparent;background-color:rgb(248, 248, 248);margin:0px,0px,0px,0px;padding-top:10px;padding-bottom:10px;}"        "QScrollBar::handle:vertical{width:10px;background:lightgray ;border-radius:5px;min-height:20;}"        "QScrollBar::handle:vertical:hover{width:10px;background:gray;border-radius:5px;min-height:20;}"        "QScrollBar::add-line:vertical{height:10px;width:10px;border-image:url(:/button/images/button/down.png);subcontrol-position:bottom;}"        "QScrollBar::sub-line:vertical{height:10px;width:10px;border-image:url(:/button/images/button/up.png);subcontrol-position:top;}"        "QScrollBar::add-line:vertical:hover{height:10px;width:10px;border-image:url(:/button/images/button/down_mouseDown.png);subcontrol-position:bottom;}"        "QScrollBar::sub-line:vertical:hover{height:10px;width:10px;border-image:url(:/button/images/button/up_mouseDown.png);subcontrol-position:top;}"        "QScrollBar::add-page:vertical,QScrollBar::sub-page:vertical{background:transparent;border-radius:5px;}"        );}

4.備忘1.完整的代碼下載http://download.csdn.net/detail/zhang_ruiqiang/89399772.還存在的問題使用內建的自增作為主鍵可能會出現越界的問題，不過integer最大為9223372036854775807如果資料量不是很大的話可以不必考慮3.在vs2010+qt 5.40 + win7下編譯通過4.參考http://bbs.csdn.net/topics/391065975

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。