编程知识 cdmana.com

QT database (SQLite3) operation under Windows

Windows Next Qt The database of (sqlite3) Environment configuration

 Insert picture description here

  • sqlite3.h ( Provide the interface ) Put it under the project folder
  • sqlite3.dll (exe Dependent environment ) Put it in debug Folder , and exe Put together
  • sqlite3.lib ( Encapsulated source code ) Can be placed under the project folder lib Folder

The following for Qt Project folder example :
 Insert picture description here
Above 3 After the files are placed , And then in .pro Add... To the document :LIBS += $$PWD/lib/sqlite3.lib, perform qmake, You can use sqlite3 Database .

LIBS += $$PWD/lib/sqlite3.lib

The main function tests whether the configuration is successful

#include <QApplication>
#include <QDebug>
#include "sqlite3.h"
 
 
int main(int argc, char *argv[])
{
    QApplication a(argc, argv);
 
    qDebug()<<"sqlite3";
 
    qDebug()<<"———— Database environment configuration test ————";
 
    qDebug()<<"sqlite3 Version number :"<<sqlite3_libversion();
 
    return a.exec();
}

 Insert picture description here

QT combination sqlite3 Implementation of database singleton

1、 Why does the database use singleton mode ?

  1. A singleton retains only one object , It can reduce the overhead of system resources .
  2. Increase creation speed , Get the existing object every time , Therefore, improve the creation speed , And share objects globally .
  3. Singleton has only one object instance in the system , Therefore, any use of this object is the same object , In this way, we can avoid the repeated opening of the database caused by multiple instantiated objects .

2、 The singleton pattern ( Slacker type ) Implementation steps

  1. Privatize the structure ( Ensure that you can only instantiate objects yourself ).

  2. Define a private static class object pointer ( Initialize to NULL).

  3. Define a common static access class object pointer provider .

    Only one object can be instantiated , For example, window manager . In an application , A class has and has only one instance , And provide a global access point to access it . Memory is allocated only when static methods are called for the first time . If the whole program does not call the static method , Memory will not be allocated , So is also called “ lazy ” Pattern .

Sqlite3 relevant API

1、sqlite3_open() Open or create a database

The function prototype :

int sqlite3_open(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);

Parameter one : Database name (xxx.db)

Parameter two :sqlite * Database pointer of type

Return value : The return value is 1 Then the database opening or creation fails , Otherwise, success .

2、sqlite3_get_table() Used to query and get the result set

The function prototype :

int sqlite3_get_table(
  sqlite3 *db,          /* An open database */
  const char *zSql,     /* SQL to be evaluated */
  char ***pazResult,    /* Results of the query */
  int *pnRow,           /* Number of result rows written here */
  int *pnColumn,        /* Number of result columns written here */
  char **pzErrmsg       /* Error msg written here */
);

Parameter one :sqlite3 Database pointer

Parameter two :SQL sentence

Parameter 3 : Result set

Parameter 4 : Number of rows of query results

Parameter 5 : The number of columns in the query result

Parameter 6 : Error reporting

Return value : The return value is 0 Execute the statement successfully , Otherwise failure .

3、sqlite3_exec() perform SQL sentence

The function prototype :

int sqlite3_exec(
  sqlite3*,                                  /* An open database */
  const char *sql,                           /* SQL to be evaluated */
  int (*callback)(void*,int,char**,char**),  /* Callback function */
  void *,                                    /* 1st argument to callback */
  char **errmsg                              /* Error msg written here */
);

Parameter one :sqlite3 Database pointer

Parameter two :SQL sentence

Parameter 3 : Callback function , It can be used NULL

Parameter 4 : It can be used 0 The ginseng

Parameter 5 : Error reporting

Return value : The return value is 0 Execute the statement successfully , Otherwise failure .

4、sqlite3_close() Close the database

The function prototype

int sqlite3_close(sqlite3 *);

Test code

The header file

#ifndef DATABASE_H
#define DATABASE_H
 
#include "sqlite3.h"
 
class dataBase
{
public:
    static dataBase *getDataBase(char *dataBaseName);// Method for statically obtaining class object pointer 
 
    static void createUserTable(char *tableName);// Build table 
 
    static int getData(const char *sql,char **&result,int &row,int &col);
 
    sqlite3 *getAppDataBase();
 
private:
    dataBase();
    dataBase(char *dataBaseName);
    ~dataBase();
 
    static dataBase *PdataBase;// Class object pointer 
 
    sqlite3 *appDataBase;// Database pointer 
};
 
#endif // DATABASE_H

Source file

#include "database.h"
#include <QDebug>
#include <QString>
 
dataBase *dataBase::PdataBase = nullptr;
 
dataBase *dataBase::getDataBase(char *dataBaseName)
{
    if(dataBase::PdataBase == nullptr)
    {
        dataBase::PdataBase = new dataBase(dataBaseName);
    }
    return dataBase::PdataBase;
}
 
// Create a user table 
void dataBase::createUserTable(char *tableName)
{
    char *zErrMsg = 0;
    QString createTablesql=QString("CREATE TABLE if not exists %1(\
                                   userID integer  primary key autoincrement,\
                                   userName varchar(255),\
                                   userPasswd varchar(255),\
                                   registerTime varchar(255)\
                                   );").arg(tableName);
    int  rc=sqlite3_exec(PdataBase->appDataBase, createTablesql.toStdString().c_str(), nullptr, 0, &zErrMsg);
    if( rc != SQLITE_OK )
    {
        qDebug()<<zErrMsg;
        qDebug()<<createTablesql;
    }
    else
    {
        qDebug()<<tableName<<"table created successfully!";
    }
}
 
 
// according to sql Statement to get database data 
int dataBase::getData(const char *sql, char **&result, int &row, int &col)
{
    char *errmsg = 0;
    int ret = sqlite3_get_table(PdataBase->appDataBase,sql,&result,&row,&col,&errmsg);
    if(ret != SQLITE_OK)
    {
       qDebug()<<sqlite3_errmsg(PdataBase->appDataBase);
       qDebug()<<sqlite3_errcode(PdataBase->appDataBase);
    }
    return 0;
}
 
// Get database pointer 
sqlite3 *dataBase::getAppDataBase()
{
    return this->appDataBase;
}
 
// structure 
dataBase::dataBase()
{
 
}
 
 
// Overload construction 
dataBase::dataBase(char *dataBaseName)
{
    int ret = sqlite3_open(dataBaseName,&appDataBase);// The database does not exist and is automatically created 
    if(ret)
    {
        qDebug()<<"Can't open DataBase";
        qDebug()<<sqlite3_errmsg(appDataBase);
        exit(0);
    }
    else
    {
        qDebug()<<"Open"<<dataBaseName<<"successfully!";
    }
}
 
// Destructor , Used to close the database 
dataBase::~dataBase()
{
    sqlite3_close(appDataBase);
}

The main function

#include <QApplication>
#include <QDebug>
#include "database.h"
 
int main(int argc, char *argv[])
{
    QApplication a(argc, argv);
 
    dataBase *data1 = dataBase::getDataBase("app.db");
    dataBase *data2 = dataBase::getDataBase("app.db");
 
    if(data1 == data2)// A single test 
    {
        qDebug()<<" This is the same object ";
    }
 
    dataBase::createUserTable("user");// Create a user table 
 
    return a.exec();
}

The printout results are as follows , A... Will be generated under the project directory app.db database , And created a user table .
 Insert picture description here

版权声明
本文为[C don't laugh]所创,转载请带上原文链接,感谢
https://cdmana.com/2022/134/202205141410503543.html

Scroll to Top