qt sqlite 提高写入速度的方法 - 使用事务

首先pro引入库

QT       += sql

.h

#ifndef SQLUTIL_H
#define SQLUTIL_H

#include <QtSql>
#include <applicationbase.h>
class SqlUtil : public QObject
{
    Q_OBJECT
signals:
    void sql_fail(QString msg);
public:
    SqlUtil();
    ~SqlUtil();
    void open();
    static SqlUtil* instance();
    void insert(QString dateStr,QString num);
    void transaction();
    void commit();
    int count();
    void clear();
    QList<QMap<int,QString>> selectAll();
private:
    void createDbTable();
private:
    QSqlDatabase db;
    QSqlQuery sql_query;
private:
    QVariantList datestr,values;
};

#endif // SQLUTIL_H

.cpp

#include "sqlutil.h"

SqlUtil::SqlUtil()
{
    open();
}
SqlUtil::~SqlUtil(){
    commit();
    db.close();
}
void SqlUtil::open(){
    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("MyWieghtDB.db3"); // 数据库名与路径, 此时是放在同目录下
    bool state = db.open(); // 连接数据库, 然后就可以使用了.
    if(!state){
        emit sql_fail(tr("数据库打开失败！"));
    }
    sql_query = QSqlQuery("MyWieghtDB.db3");
    // 清除以前的数据
    clear();
    // 创建数据表
    createDbTable();
}
SqlUtil* SqlUtil::instance(){
    static SqlUtil* instance = new SqlUtil();
    return instance;
}
void SqlUtil::insert(QString dateStr,QString num){
　　// 一万个数据提交一次事务，也就是说一万条之前先把数据存储在内存中
    if(datestr.size() == 10000){
        commit();
    }
    if(datestr.size() == 0){
        transaction();
    }
    datestr << dateStr;
    values << num;
}
void SqlUtil::transaction(){
    db.transaction();
}
void SqlUtil::commit(){
    qDebug() << "commit Success" << datestr.size();
    if(datestr.size() > 0){
        sql_query.prepare("insert into wieght (datestr,value) values(?,?)");
        sql_query.addBindValue(datestr);
        sql_query.addBindValue(values);
        if(!sql_query.execBatch()){
            qDebug() << "execBatch fail:" << sql_query.lastError().text();
        }
        if(!db.commit()){
            qDebug() << "commit fail:" << sql_query.lastError().text();
        }
        datestr.clear();
        values.clear();
    }
}
void SqlUtil::createDbTable(){
    bool state = sql_query.exec("CREATE TABLE wieght ("
                       "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                       "datestr VARCHAR(100) NOT NULL, "
                       "value VARCHAR(150) NOT NULL) ");
    if(!state){
        emit sql_fail("数据表创建失败：" + sql_query.lastError().text());
    }
}
int SqlUtil::count(){
    sql_query.exec("SELECT count(*) FROM wieght");
    sql_query.next();
    return sql_query.value(0).toInt();
}
void SqlUtil::clear(){
    commit();
    // DROP TABLE wieght
    if(!sql_query.exec("delete from wieght")){
        qDebug() << "clear fail:" << sql_query.lastError().text();
    }else{
        qDebug() << "clear success";
    }
}
QList<QMap<int,QString>> SqlUtil::selectAll(){
    QList<QMap<int,QString>> resList;
    QString clear_sql = "SELECT * FROM wieght";
    sql_query.prepare(clear_sql);
    if(!sql_query.exec()){
        return resList;
    }
    while(sql_query.next())
    {
        QMap<int,QString> itemMap;
        itemMap[0] = sql_query.value(1).toString();
        itemMap[1] = sql_query.value(2).toString();
        resList.append(itemMap);
    }
    return resList;
}