QT之数据库批量插入封装——批量插入器

1.head file (.h)

#ifndef QBATCHINSERTADAPTER_H
#define QBATCHINSERTADAPTER_H
/************************************************************/
/* File:    qbatchinsertadapter.h                                    */
/* Author:  Yangze          DateTime:   2020-03-03 22:35    */
/* E-Mail:  709102202@qq.com                                */
/************************************************************/
/*
 * 这是一个数据批量插入库封装，可以实现数据批量插入数据中
*/
/************************************************************/
#include <QString>
#include <QStringList>
#include <QMutex>

class QXdbBase;

class QBatchInsertAdapter
{
public:
    QBatchInsertAdapter(QXdbBase *database);
    ~QBatchInsertAdapter();
    //开始插入字段
    void beginAddField(QString mTable);
    //添加字段
    void addField(QString mField);
    //结束插入字段
    void endAddField();
    //开始插入数值
    void beginAddRow();
    void addValue(QString value);
    void addValue(char value);
    void addValue(int value);
    void addValue(unsigned int value);
    void addValue(long value);
    void addValue(unsigned long value);
    void addValue(float value, int prec = 6);
    void addValue(double value, int prec = 9);
    void addValue(std::string value);
    void addValue(const char * value);
    //结束添加值
    bool endAddRow();
    //将数据插入数据库
    void flush();
    //获取总的行数
    int getSumRow();

    void setAdapterFlushRow(int mAdapterFlushRow = 50000);
private:
    QString mTable;
    QStringList mFieldNames;                  //需要插入的字段名称
    int mFieldAdded;                          //已经添加的字段数
    QString mHeadSql;
    QString mValueSql;
    QXdbBase *mDatabase;
    int mAdapterFlushRow;
    int mRowNum;                              //行数
    int mRowSum;
    QMutex lock;
};

#endif // QBATCHINSERTADAPTER_H

2.source file (.cpp)

#include "qbatchinsertadapter.h"
#include "qxdbbase.h"

QBatchInsertAdapter::QBatchInsertAdapter(QXdbBase *database)
    : mDatabase(database)
    , mAdapterFlushRow(50000)
    , mRowNum(0)
    , mRowSum(0)
    , mFieldAdded(0)
{

}

QBatchInsertAdapter::~QBatchInsertAdapter()
{

}
void QBatchInsertAdapter::beginAddField(QString mTable)
{
    this->mTable = mTable;
    this->mFieldNames.clear();
    this->mHeadSql.clear();
    this->mValueSql.clear();
    this->mRowNum = 0;
    this->mRowSum = 0;
    this->mFieldAdded = 0;
}

void QBatchInsertAdapter::addField(QString mField)
{
    this->mFieldNames.append(mField);
}

void QBatchInsertAdapter::endAddField()
{
    mHeadSql = QString("insert into %0 (%1) values ")
               .arg(this->mTable).arg(this->mFieldNames.join(","));
}

void QBatchInsertAdapter::beginAddRow()
{
    mValueSql += "(";
}

void QBatchInsertAdapter::addValue(QString value)
{
    mValueSql += QString("\'%0\',").arg(value);
    mFieldAdded++;
}

void QBatchInsertAdapter::addValue(char value)
{
    mValueSql += QString("\'%0\',").arg(value);
    mFieldAdded++;
}

void QBatchInsertAdapter::addValue(int value)
{
    mValueSql += QString("\'%0\',").arg(value);
    mFieldAdded++;
}

void QBatchInsertAdapter::addValue(unsigned int value)
{
    mValueSql += QString("\'%0\',").arg(value);
    mFieldAdded++;
}

void QBatchInsertAdapter::addValue(long value)
{
    mValueSql += QString("\'%0\',").arg(value);
    mFieldAdded++;
}

void QBatchInsertAdapter::addValue(unsigned long value)
{
    mValueSql += QString("\'%0\',").arg(value);
    mFieldAdded++;
}

void QBatchInsertAdapter::addValue(float value, int prec)
{
    mValueSql += QString("\'%0\',").arg(QString::number(value, 10, prec));
    mFieldAdded++;
}

void QBatchInsertAdapter::addValue(double value, int prec)
{
    mValueSql += QString("\'%0\',").arg(QString::number(value, 10, prec));
    mFieldAdded++;
}

void QBatchInsertAdapter::addValue(std::string value)
{
    mValueSql += QString("\'%0\',").arg(QString::fromLocal8Bit(value.data()));
    mFieldAdded++;
}

void QBatchInsertAdapter::addValue(const char * value)
{
    mValueSql += QString("\'%0\',").arg(QString::fromLocal8Bit(value));
    mFieldAdded++;
}

bool QBatchInsertAdapter::endAddRow()
{
    if(mFieldAdded != mFieldNames.size())
        return false;
    mValueSql = mValueSql.left(mValueSql.length() - 1);
    mValueSql += "),";
    this->mRowNum++;
    this->mRowSum++;
    mFieldAdded = 0;
    if(this->mRowNum >= this->mAdapterFlushRow)
    {
        flush();
    }
    return true;
}

void QBatchInsertAdapter::flush()
{
    if(mValueSql.isEmpty())
        return;
    mValueSql = mValueSql.left(mValueSql.length() - 1);
    QString mDataSql = mHeadSql + mValueSql;
    this->mRowNum = 0;
    this->mValueSql = "";
    lock.lock();
    if (!mDatabase->insert(mDataSql))
    {
        lock.unlock();
        return;
    }
    lock.unlock();
}

void QBatchInsertAdapter::setAdapterFlushRow(int mAdapterFlushRow)
{
    this->mAdapterFlushRow = mAdapterFlushRow;
}

int QBatchInsertAdapter::getSumRow()
{
    return this->mRowSum;
}

3. demo

void MainWindow::on_BatchInsertAdapterBtn_clicked()
{
    QXdbBase *_mMySqlDatabaseOper = QDatabaseFactory::CreateDatabaseOper(DBType::CMYSQL);
    DatabaseInfo info;
    info.mHostName = "localhost";
    info.mDatabaseName = "wuhan_1500w";
    info.mPort = 3306;
    info.mUserName = "root";
    info.mPassword = "123456";
    _mMySqlDatabaseOper->setDatabaseInfo(info);
    _mMySqlDatabaseOper->setConnectID("BatchInsertAdapter");

    if (_mMySqlDatabaseOper->open())
    {
        //当数据量超过单次批量插入数据量时，可使用事务。
        _mMySqlDatabaseOper->autocomit(false);
        QBatchInsertAdapter mBatchInsertAdapter(_mMySqlDatabaseOper);
        mBatchInsertAdapter.setAdapterFlushRow(500);
        mBatchInsertAdapter.beginAddField("batchInsertadapter_list");
        mBatchInsertAdapter.addField("id");
        mBatchInsertAdapter.addField("num");
        mBatchInsertAdapter.endAddField();
        for(int i = 0 ; i < 50000; i++)
        {
            mBatchInsertAdapter.beginAddRow();
            mBatchInsertAdapter.addValue(i);
            mBatchInsertAdapter.addValue(i);
            if(!mBatchInsertAdapter.endAddRow())
                break;
            qDebug() << mBatchInsertAdapter.getSumRow();
        }
        mBatchInsertAdapter.flush();
        _mMySqlDatabaseOper->commit();
    }
}