QT连接数据库

test_db.pro

#-------------------------------------------------
#
# Project created by QtCreator 2024-07-05T10:17:43
#
#-------------------------------------------------

QT       += core gui sql #SQL数据库模块

greaterThan(QT_MAJOR_VERSION, 4): QT += widgets

TARGET = test_db
TEMPLATE = app

# The following define makes your compiler emit warnings if you use
# any feature of Qt which as been marked as deprecated (the exact warnings
# depend on your compiler). Please consult the documentation of the
# deprecated API in order to know how to port your code away from it.
DEFINES += QT_DEPRECATED_WARNINGS

# You can also make your code fail to compile if you use deprecated APIs.
# In order to do so, uncomment the following line.
# You can also select to disable deprecated APIs only up to a certain version of Qt.
#DEFINES += QT_DISABLE_DEPRECATED_BEFORE=0x060000    # disables all the APIs deprecated before Qt 6.0.0


SOURCES += main.cpp\
        widget.cpp \
        db.cpp

HEADERS  += widget.h \
        common.h \
        db.h

FORMS    += widget.ui

common.h

#ifndef _COMMON_H_
#define _COMMON_H_
//头文件保护宏，用于防止头文件被多次包含
//当 _COMMON_H_ 宏未定义时，定义它并继续编译；如果已定义，则直接跳过文件内容。

#include <QDebug>
#include <QTime>

#define _TIME_ qPrintable(QTime::currentTime().toString("hh:mm:ss:zzz"))
//返回当前时间的字符串表示，格式为hh:mm:ss:zzz

#define FUNCTION_ENTER qDebug("%s %s %d %s start!",__FILE__,__FUNCTION__,__LINE__,_TIME_);
//这个宏用于在函数开始时输出调试信息，显示当前所在文件名、函数名、行号以及当前时间。

#define FUNCTION_EXIT qDebug("%s %s %d %s end!",__FILE__,__FUNCTION__,__LINE__,_TIME_);
//这个宏用于在函数结束时输出调试信息，同样显示当前所在文件名、函数名、行号以及


#endif //_COMMON_H_

db.h

#ifndef _DB_H_
#define _DB_H_

#include <QSqlDatabase>
#include <QSqlQuery>

class DBManager
{
public:
    enum DBMANAGER_TYPE
    {
        DBMANAGER_OK = 0,
        DBMANAGER_ERR,
    };

public:
    static DBManager * GetInstance();
    static void DestroyInstance();

    int ExecSql(QString strSql);
    int ExecSql(QString strSql, QSqlQuery &query);

private:
    DBManager();
    ~DBManager();

    void InitDb();

private:
    static DBManager *m_pManager;
    QSqlDatabase m_db;
};

#endif //_DB_H_

widget.h

#ifndef WIDGET_H
#define WIDGET_H

#include <QWidget>

namespace Ui {
class Widget;
}

class Widget : public QWidget
{
    Q_OBJECT

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

    void InitWidget();

private slots:
    void on_pushButton_clicked();
    void on_pushButton_2_clicked();

private:
    Ui::Widget *ui;
};

#endif // WIDGET_H

db.cpp

#include "common.h"
#include "db.h"
#include <QSqlError>

DBManager *DBManager::m_pManager = NULL;

int DBManager::ExecSql(QString strSql, QSqlQuery &query) //用于执行带有结果集的 SQL 查询
{
    FUNCTION_ENTER; //用于输出函数进入和退出的调试信息
    if (strSql.length() == 0)
    {
        return DBMANAGER_ERR;
    }
    query = m_db.exec(strSql);  //执行SQL查询
    if (m_db.lastError().isValid())  //如果执行过程中出现错误
    {
        qDebug() << m_db.lastError().text();
        return DBMANAGER_ERR;
    }
    FUNCTION_EXIT;
    return DBMANAGER_OK;
}

int DBManager::ExecSql(QString strSql)  //用于执行不带结果集的 SQL 查询
{
    FUNCTION_ENTER;
    if (strSql.length() == 0)
    {
        return DBMANAGER_ERR;
    }
    m_db.exec(strSql);
    if (m_db.lastError().isValid())
    {
        qDebug() << m_db.lastError().text();
        return DBMANAGER_ERR;
    }
    FUNCTION_EXIT;
    return DBMANAGER_OK;
}

DBManager::DBManager()
{
    FUNCTION_ENTER;
    FUNCTION_EXIT;
}

DBManager::~DBManager()
{
    FUNCTION_ENTER;
    FUNCTION_EXIT;
}

DBManager *DBManager::GetInstance()
{
    FUNCTION_ENTER;
    if (NULL == m_pManager)
    {
        m_pManager = new DBManager();
        m_pManager->InitDb();
    }
    FUNCTION_EXIT;
    return m_pManager;
}

void DBManager::DestroyInstance()
{
    FUNCTION_ENTER;
    if (NULL != m_pManager)
    {
        delete m_pManager;
        m_pManager = NULL;
    }
    FUNCTION_EXIT;
}

void DBManager::InitDb()
{
    FUNCTION_ENTER;
    m_db = QSqlDatabase::addDatabase("QMYSQL");  //使用 MySQL 数据库驱动
    m_db.setHostName("localhost");   //本地数据库
    m_db.setDatabaseName("text");    //数据库名
    m_db.setUserName("root");     //用户名
    m_db.setPassword("123456");     //密码

    if (m_db.open())
    {
        qDebug() << "open ok";
    }
    FUNCTION_EXIT;
}

main.cpp

#include "widget.h"
#include <QApplication>

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);
    Widget w;
    w.InitWidget();
    w.show();

    return a.exec();
}

widget.cpp

#include "widget.h"
#include "ui_widget.h"
#include "common.h"
#include "db.h"
#include <QTableWidgetItem>

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

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

void Widget::InitWidget()  //初始化界面的一个表格
{
    FUNCTION_ENTER;
    ui->tableWidget->setColumnCount(2);  //列为2
    ui->tableWidget->setRowCount(5);  //行为5
    QStringList strList;
    strList << "id" << "name" ;  //表头
    ui->tableWidget->setHorizontalHeaderLabels(strList);
    ui->tableWidget->setAutoScroll(true);
    ui->tableWidget->setEditTriggers(QAbstractItemView::NoEditTriggers);   //启用自动滚动和禁止编辑触发器，以及选择行为。
    ui->tableWidget->setSelectionBehavior( QAbstractItemView::SelectRows);
    FUNCTION_EXIT;
}

void Widget::on_pushButton_clicked()
{
    FUNCTION_ENTER;
    DBManager *pTmp = DBManager::GetInstance();
    if (NULL == pTmp)
    {
        qDebug() << "db error";
    }
    QString strSql = "insert into testtable (id, name) values (‘8’, 'aaaa')";  //SQL插入语句
    int iRet = pTmp->ExecSql(strSql);
    if (iRet != DBManager::DBMANAGER_OK)
    {
        qDebug() << "insert data error";
        return;
    }
    FUNCTION_EXIT;
}

void Widget::on_pushButton_2_clicked()
{
    FUNCTION_ENTER;
    DBManager *pTmp = DBManager::GetInstance();
    if (NULL == pTmp)
    {
        qDebug() << "db error";
    }
    QString strSql = "select * from testtable";  //SQL查询语句
    QSqlQuery query;
    int iRet = pTmp->ExecSql(strSql, query);
    if (iRet != DBManager::DBMANAGER_OK)
    {
        qDebug() << "insert data error";
        return;
    }
    int i = 0;
    while(query.next())     //输出
    {
        int j = 0;
        for (j = 0; j < 2; j++)
        {
            //qDebug() << query.value(j).toString();
            QTableWidgetItem *pItem = new QTableWidgetItem(query.value(j).toString());
            ui->tableWidget->setItem(i, j, pItem);
        }
        i++;
    }
    FUNCTION_EXIT;
}

widget.ui

<?xml version="1.0" encoding="UTF-8"?>
<ui version="4.0">
 <class>Widget</class>
 <widget class="QWidget" name="Widget">
  <property name="geometry">
   <rect>
    <x>0</x>
    <y>0</y>
    <width>671</width>
    <height>541</height>
   </rect>
  </property>
  <property name="windowTitle">
   <string>Widget</string>
  </property>
  <widget class="QPushButton" name="pushButton">
   <property name="geometry">
    <rect>
     <x>410</x>
     <y>80</y>
     <width>93</width>
     <height>28</height>
    </rect>
   </property>
   <property name="text">
    <string>insert</string>
   </property>
  </widget>
  <widget class="QPushButton" name="pushButton_2">
   <property name="geometry">
    <rect>
     <x>410</x>
     <y>140</y>
     <width>93</width>
     <height>28</height>
    </rect>
   </property>
   <property name="text">
    <string>select</string>
   </property>
  </widget>
  <widget class="QTableWidget" name="tableWidget">
   <property name="geometry">
    <rect>
     <x>50</x>
     <y>70</y>
     <width>351</width>
     <height>371</height>
    </rect>
   </property>
  </widget>
 </widget>
 <layoutdefault spacing="6" margin="11"/>
 <resources/>
 <connections/>
</ui>

测试