Qt 示例23 QSqlite数据库编程

通过QSqlite数据库实现简单的数据库客户端功能。包括创建表，右键删除表；数据表记录的增、删、查等。后附完整实现代码。

头文件：

#ifndef MAINWINDOW_H
#define MAINWINDOW_H


#include <QMainWindow>


class QSqlDatabase;
class QListWidgetItem;


namespace Ui {
class MainWindow;
}


class MainWindow : public QMainWindow
{
    Q_OBJECT


public:
    explicit MainWindow(QWidget *parent = 0);
    ~MainWindow();


private:
    QSqlDatabase getDataBase();
    void updateTableList();


protected slots:
    void createTable();
    void deleteTable();
    void insertRecord();
    void deleteRecord();
    void updateTableRecords(QListWidgetItem*);
    void queryRecord();
    void on_listWidget_customContextMenuRequested(const QPoint &pos);




private:
    Ui::MainWindow *ui;
};


#endif // MAINWINDOW_H

源文件：

#include "mainwindow.h"
#include "ui_mainwindow.h"


#include <QSqlDatabase>
#include <QSqlQuery>
#include <QMessageBox>
#include <QDebug>
#include <QMenu>


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


    ui->tableWidget->setRowCount(1);
    ui->tableWidget->setColumnCount(2);
    ui->tableWidget->setHorizontalHeaderLabels(QStringList()<<"Account"<<"User Name");
    ui->tableWidget->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);


    ui->tableWidget_query_result->setRowCount(1);
    ui->tableWidget_query_result->setColumnCount(2);
    ui->tableWidget_query_result->setHorizontalHeaderLabels(QStringList()<<"Account"<<"User Name");
    ui->tableWidget_query_result->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);


    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setHostName("127.0.0.1");
    db.setUserName("admin");
    db.setPassword("123456");
    db.setDatabaseName("Platform");


    if(!db.open())
        QMessageBox::critical(nullptr,"Error","Database error!");


    updateTableList();


    connect(ui->pushButton_createTable,SIGNAL(clicked(bool)),this,SLOT(createTable()));
    connect(ui->listWidget,SIGNAL(itemPressed(QListWidgetItem*)),this,SLOT(updateTableRecords(QListWidgetItem*)));


    connect(ui->pushButton_Insert,SIGNAL(clicked(bool)),this,SLOT(insertRecord()));
    connect(ui->pushButton_deleteRecords,SIGNAL(clicked(bool)),this,SLOT(deleteRecord()));
    connect(ui->pushButton_Query,SIGNAL(clicked(bool)),this,SLOT(queryRecord()));
}


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


QSqlDatabase MainWindow::getDataBase()
{
    QSqlDatabase db = QSqlDatabase::database();


    return db;
}


void MainWindow::updateTableList()
{
    QSqlDatabase db = getDataBase();


    QStringList tables = db.tables();


    QSqlQuery query;


    if(!tables.contains("users"))
    {
        QString sql = "create table users(account varchar(50) primary key,"
                      " username varchar(100), password varchar(100));";
        if(!query.exec(sql))
            QMessageBox::warning(nullptr,"Warnning","Create table failed!");
    }
    ui->listWidget->clear();
    ui->listWidget->addItems(tables);
}


void MainWindow::createTable()
{
    QString table_name = ui->lineEdit_tableName->text();
    QString sql = QString("create table '%1'(account varchar(50) primary key,"
                          " username varchar(100), password varchar(100));")
            .arg(table_name);
    QSqlQuery query;
    if(!query.exec(sql))
        QMessageBox::warning(nullptr,"Warnning","Create table failed!");


    updateTableList();
}


void MainWindow::insertRecord()
{
    QList<QListWidgetItem*> selectItems = ui->listWidget->selectedItems();
    if(selectItems.size()==0)
        return;


    QString tableName = selectItems.at(0)->text();


    QString account = ui->lineEdit_account->text();
    QString userName = ui->lineEdit_Username->text();
    QString password = ui->lineEdit_password->text();


    QSqlQuery query;
    QString sql = QString("insert into '%0'(account,username,password) values('%1','%2','%3')")
            .arg(tableName)
            .arg(account)
            .arg(userName)
            .arg(password);
    if(!query.exec(sql))
    {
        QMessageBox::warning(nullptr,"Warnning",QString("Insert table '%0' error!").arg(tableName));
        return;
    }


    int rowCount = ui->tableWidget->rowCount();
    ui->tableWidget->setRowCount(rowCount+1);
    QTableWidgetItem *accountItem = new QTableWidgetItem(account);
    accountItem->setTextAlignment(Qt::AlignCenter);
    ui->tableWidget->setItem(rowCount,0,accountItem);
    QTableWidgetItem *userNameItem = new QTableWidgetItem(userName);
    userNameItem->setTextAlignment(Qt::AlignCenter);
    ui->tableWidget->setItem(rowCount,1,userNameItem);


}


void MainWindow::deleteRecord()
{
    QList<QListWidgetItem*> selectItems = ui->listWidget->selectedItems();
    if(selectItems.size()==0)
        return;


    QString tableName = selectItems.at(0)->text();


    QList<QTableWidgetItem*> items = ui->tableWidget->selectedItems();


    QSqlQuery query;


    for(int i=0; i<items.size(); i++)
    {
        if(i % 2 != 0)
            continue;


        QString account = items.at(i)->text();


        QString sql = QString("delete from '%0' where account='%1'")
                .arg(tableName)
                .arg(account);
        if(!query.exec(sql))
        {
            QMessageBox::warning(nullptr,"Warnning",QString("delete from table '%0' error!").arg(tableName));
            return;
        }


        ui->tableWidget->removeRow(items.at(i)->row());
    }
}


void MainWindow::updateTableRecords(QListWidgetItem* item)
{
    QSqlQuery query;
    QString tableName = item->text();


    QString sql = QString("select account,username from '%0' order by account asc").arg(tableName);
    if(!query.exec(sql))
    {
        QMessageBox::warning(nullptr,"Warnning",QString("Query table '%0' error!").arg(tableName));
        return;
    }


    ui->tableWidget->clear();
    ui->tableWidget->setRowCount(1);
    int tableRow = 0;
    while (query.next()) {
        QString account = query.value(0).toString();
        QString userName = query.value(1).toString();


        ui->tableWidget->setRowCount(tableRow+1);
        ui->tableWidget->setColumnCount(2);
        QTableWidgetItem *accountItem = new QTableWidgetItem(account);
        accountItem->setTextAlignment(Qt::AlignCenter);
        ui->tableWidget->setItem(tableRow,0,accountItem);
        QTableWidgetItem *userNameItem = new QTableWidgetItem(userName);
        userNameItem->setTextAlignment(Qt::AlignCenter);
        ui->tableWidget->setItem(tableRow,1,userNameItem);
        tableRow++;
    }
}


void MainWindow::queryRecord()
{
    QList<QListWidgetItem*> selectItems = ui->listWidget->selectedItems();
    if(selectItems.size()==0)
        return;


    QString tableName = selectItems.at(0)->text();


    QString account = ui->lineEdit_account->text();
    QString userName = ui->lineEdit_Username->text();
    if(account.isEmpty()&&userName.isEmpty())
    {
        QMessageBox::warning(nullptr,"Warnning","Please input query condition!");
        return;
    }


    QSqlQuery query;


    QString sql = "";


    if(!account.isEmpty())
    {
        sql = QString("select * from '%0' where account='%1'")
                .arg(tableName)
                .arg(account);
    }
    else
    {
        sql = QString("select * from '%0' where username='%1'")
                .arg(tableName)
                .arg(userName);
    }


    if(!query.exec(sql))
    {
        QMessageBox::warning(nullptr,"Warnning",QString("Query table '%0' error!").arg(tableName));
        return;
    }


    ui->tableWidget_query_result->clear();
    int tableRow=0;
    while (query.next()) {
        QString account_query = query.value(0).toString();
        QString userName_query = query.value(1).toString();


        QTableWidgetItem *accountItem = new QTableWidgetItem(account_query);
        accountItem->setTextAlignment(Qt::AlignCenter);
        ui->tableWidget_query_result->setItem(tableRow,0, accountItem);
        QTableWidgetItem *userNameItem = new QTableWidgetItem(userName_query);
        userNameItem->setTextAlignment(Qt::AlignCenter);
        ui->tableWidget_query_result->setItem(tableRow,1,userNameItem);
    }
}


void MainWindow::on_listWidget_customContextMenuRequested(const QPoint &pos)
{
    Q_UNUSED(pos);
    QMenu *menu = new QMenu(this);


    QAction *deleteTable = new QAction(QString::fromLocal8Bit("Delete"),this);
    menu->addAction(deleteTable);


    connect(deleteTable,SIGNAL(triggered()),this,SLOT(deleteTable()));
    menu->exec(QCursor::pos());
}


void MainWindow::deleteTable()
{
    QList<QListWidgetItem*> selectItems = ui->listWidget->selectedItems();
    if(selectItems.size()==0)
        return;


    QSqlQuery query;
    for(int i=0; i<selectItems.size(); i++)
    {
        QString tableName = selectItems.at(i)->text();
        QString sql = QString("DROP TABLE IF EXISTS '%0'").arg(tableName);
        if(!query.exec(sql))
        {
            QMessageBox::warning(nullptr,"Warnning","Drop table error!");
            return;
        }
        ui->listWidget->removeItemWidget(selectItems.at(i));
    }
    updateTableList();
}

后续公众号会发布系列教程，更多内容请关注公众号：程序猿学习日记