qt实现sqlite3的级联删除

#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_OBJECT

public:
    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/8939977

2.还存在的问题使用自带的自增作为主键可能会出现越界的问题，不过integer最大为9223372036854775807如果数据量不是很大的话可以不必考虑

3.在vs2010+qt 5.40 + win7下编译通过

4.参考http://bbs.csdn.net/topics/391065975

版权声明：本文为博主原创文章，未经博主允许不得转载。

qt实现sqlite3的级联删除

标签：qt   sqlite3   级联删除