C++ QSqlQuery::execBatch方法代码示例

void DataBase::insertTweetsToDatabase(QList<tweets> *dataToInsert)
{
    connect();
    QSqlQuery insertTweetsToDatabaseQuery;
    QString queryString = "";
    insertTweetsToDatabaseQuery.prepare("INSERT INTO tweets (tweetID, tweetTime, username, text, userID) "\
                                        " VALUES(:tweetID,:tweetTime,:username,:text,:twitterUserID)" );

    QVariantList tweetID,tweetTime,username,text,twitterUserID;
    for (int i=0; i< dataToInsert->count();i++)
    {
        tweetID.append(dataToInsert->at(i).tweetID);
        tweetTime.append(dataToInsert->at(i).tweetTime);
        username.append(dataToInsert->at(i).username);
        text.append(dataToInsert->at(i).text);
        twitterUserID.append(dataToInsert->at(i).twitterUserID);        
    }

    insertTweetsToDatabaseQuery.addBindValue(tweetID);
    insertTweetsToDatabaseQuery.addBindValue(tweetTime);
    insertTweetsToDatabaseQuery.addBindValue(username);
    insertTweetsToDatabaseQuery.addBindValue(text);
    insertTweetsToDatabaseQuery.addBindValue(twitterUserID);    
    if(!insertTweetsToDatabaseQuery.execBatch())
        qDebug() << insertTweetsToDatabaseQuery.lastError();
    twitterDB.commit();
    disconnect();
}

// 操作model_img表
bool modelImgInsert(int empId, AutoType& photo)
{
    Log log(__LOGARG__,1);
	QSqlQuery query;
	query.exec("INSERT INTO model_img (emp_id, emp_photo) VALUES(?,?)");
	QVariantList emp_id;
	emp_id << empId;
	query.addBindValue(emp_id);
	QVariantList emp_photo;
    QByteArray data(photo.getStr(),photo.getLen());
	emp_photo << data;
	query.addBindValue(emp_photo);
	try
    {
        if (!query.execBatch())
        {
            log << ((query.lastQuery()).toUtf8()).constData() << Log::endl;
            log << (((query.lastError()).text()).toUtf8()).constData() << Log::endl;
            return false;
        }
    }
    catch(...)
    {
        log << "Add New Node error! Unable to add a new Node!" << Log::endl;
    }
    return true;
}

// 操作worktime表
// 插入数据
bool worktimeInsert(int empId, const char* time, const char* photo)
{
    Log log(__LOGARG__,1);
	QSqlQuery query;
	query.exec("INSERT INTO work_time (emp_id, check_time, check_photo) VALUES(?,?,?)");
	QVariantList emp_id;
	emp_id << empId;
	query.addBindValue(emp_id);
	QVariantList check_time;
	check_time << time;
	query.addBindValue(check_time);
	QVariantList check_photo;
	check_photo << photo;
	query.addBindValue(check_photo);
	try
    {
        if (!query.execBatch())
        {
            log << ((query.lastQuery()).toUtf8()).constData() << Log::endl;
            log << (((query.lastError()).text()).toUtf8()).constData() << Log::endl;
            return false;
        }
    }
    catch(...)
    {
        log << "Add New Node error! Unable to add a new Node!" << Log::endl;
    }
    return true;
}

// 修改empId
bool worktimeAltEmpId(int worktimeId, int empId)
{
    Log log(__LOGARG__,1);
	QSqlQuery query;
	query.exec("UPDATE work_time SET emp_id=? WHERE work_time_id=?");
	QVariantList emp_id;
	emp_id << empId;
	query.addBindValue(emp_id);
	QVariantList work_time_id;
	work_time_id << worktimeId;
	query.addBindValue(work_time_id);
	try
    {
        if (!query.execBatch())
        {
            log << ((query.lastQuery()).toUtf8()).constData() << Log::endl;
            log << (((query.lastError()).text()).toUtf8()).constData() << Log::endl;
            return false;
        }
    }
    catch(...)
    {
        log << "Add New Node error! Unable to add a new Node!" << Log::endl;
    }
    return true;
}

void Widget::selects()
{
    QSqlQuery query;
    query.prepare("INSERT INTO Student (name ,age ) VALUES (?,?)");//query.exec();准备输出
    QVariantList names;//链表
    names <<"Tom "<< "Jacks" << "Jane" << "Jerry";
    query.addBindValue(names);//添加绑定链表
    QVariantList ages;
    ages << 20 << 23 << 22 << 25;
    query.addBindValue(ages);//按顺序添加绑定age
    if(!query.execBatch()){//批处理函数值
        QMessageBox::critical(0,QObject::tr("Databases Error"),query.lastError().text());
    }
    query.finish();
    QString janes = "jane";
    QString jane =QString("SELECT  sex FROM Student WHERE name = '%1'").arg(janes);
    query.exec(jane);//执行完这句话,
    QString name,sex;
    while (query.next())
    {
         //name = query.value(0).toString();
          sex = query.value(0).toString();
//        cout<<name.toStdString() <<":"<<age.toStdString()<<endl;
          cout<<sex.toStdString()<<endl;
    }
//    cout<<name.toStdString() <<":"<<age.toStdString()<<endl;
}

int DBimpl::addStudentsToProject(const int& projectID, QList<Student*>* students){
    if (projectID > 0)
    {
        int tempInt;
        QSqlQuery query;
        QVariantList projectIDList;
        QVariantList studentIDList;
        query.prepare(DatabaseQueries::addStudentToProject);

        for (int i = 0; i< students->count();i++)
        {
            tempInt = students->at(i)->getID();
            if (tempInt > 0)
            {
                projectIDList << projectID;
                studentIDList << tempInt ;
            }
            else return 0;
        }

        query.bindValue(":projectID",projectIDList);
        query.bindValue(":studentID",studentIDList);

        if(!query.execBatch())
        {
            qDebug() << query.lastError();
            qDebug() << query.lastQuery();
            return 0;
        }
        else return query.lastInsertId().toInt();
    }
    else return 0;

}

bool CoreData::createDBFile(QString dbname)
{
    if (QFile::exists(dbname)) {
        QFile::remove(dbname);
    }
    db.setDatabaseName(dbname);
    if (!db.open()) {
        QMessageBox::critical(0, QString::fromUtf8("出错了！"), QString::fromUtf8("无法打开数据库文件"));
        return false;
    }

    QSqlQuery query;
    query.exec("drop table people ");
    query.exec("create table people (id int primary key, name varchar(64), gender char(8), join_date date, status tinyint, comment text)");
    qDebug() << db.lastError().type()  << " + " << db.lastError().text();
    query.exec("drop table date");
    query.exec("create table date (id int primary key, date date)");
    query.exec("pragma synchronous=0");
    qDebug() << db.lastError().type()  << " + " << db.lastError().text();
    QDate date = QDate::currentDate();
    date = date.addYears(-1);
    query.prepare("insert into date (id, date) values (?,?)");
    int i = 0;
    QVariantList ids;
    QStringList dateStrings;

    for (i=0; i<3000; ++i) {
        ids<<i;
        date = date.addDays(1);
        dateStrings<<date.toString("yyyy-MM-dd");
    }
    query.addBindValue(ids);
    query.addBindValue(dateStrings);
    if (!query.execBatch())
         qDebug() << query.lastError();

    query.exec("pragma synchronous=2");
    qDebug()<<query.lastError();
    query.exec("create table person_date (id int primary key, person_id integer, date_id integer, foreign key(person_id) references people(id), foreign key(date_id) references date(id))");
    qDebug() << query.lastError();
    personModel = new QSqlTableModel(0, db);
    setPersonModel();
    signInModel = new SignInModel(0);
    setSignInModel(QDate::currentDate(), db);

    query.exec("create table contact (id int primary key, person_id integer, method varchar(64), value varchar(128), foreign key(person_id) references people(id))");
    qDebug() << query.lastError();
    return true;
}

int DBimpl::storeTeamsByProject (const QList<Team*>& teams, const int& projectID){

    if (projectID > 0)
    {
        int tempInt;
        QSqlQuery query;
        QVariantList studentIDList;
        QVariantList teamIDList;


        query.prepare(DatabaseQueries::storeTeamsByProject);

        for (int i = 0; i< teams.count();i++)
        {
            tempInt = createTeam(projectID,teams.at(i)->getResultDisplay());


            if (tempInt > 0)
            {
                for (int j = 0; j < teams.at(i)->getTeamMembers().count();j++)
                {
                    studentIDList << teams.at(i)->getTeamMembers().at(j);
                    teamIDList << tempInt ;
                }


            }
            else return 0;
        }

        query.bindValue(":studentID",studentIDList);
        query.bindValue(":teamID",teamIDList);

        if(!query.execBatch())
        {
            qDebug() << query.lastError();
            qDebug() << query.lastQuery();
            return 0;
        }
        else return query.lastInsertId().toInt();
    }
    else return 0;
}

void MainWindow::on_pushButton_clicked()
{
    QSqlQuery query;
    QString attend = "yes";
    QString person = QString::fromStdString(to_string(m_selectedPerson));
    QString pose[] = {
        person,
        ui->lineEdit->text(),
        ui->lineEdit_2->text(),
        attend
         };

    //从表单中获取数据，执行插入数据语句
    query.prepare("INSERT INTO students VALUES(?,?,?,?)");

    QVariantList Person; //1
    Person << pose[0];
    query.addBindValue(Person);
    QVariantList student_name; //2
    student_name << pose[1];
    query.addBindValue(student_name);
    QVariantList student_id; //3
    student_id << pose[2];
    query.addBindValue(student_id);
    QVariantList attend_class; //4
    attend_class << pose[3];
    query.addBindValue(attend_class);

    if(!query.execBatch()){
        QMessageBox::critical(0,QObject::tr("Error"),query.lastError().text());
    }
    query.finish();//插入错误时报错
    QMessageBox::about(NULL, "提示", "插入成功");

    m_selectedPerson++;
    m_numPersons++;

    ui->lineEdit->setEnabled(false);
    ui->lineEdit_2->setEnabled(false);
    ui->pushButton->hide();
    ui->label_6->setText("请点击菜单");
}

bool ClientDB::addUsers(const QVector<User> *users) {
    QSqlQuery q;
        q.prepare("insert into fx_user(id, name, true_name, department, gmt_create, gmt_modify, status) "
                  "values (?, ?, ?, ?, date(), date(), 1)");

    QVariantList ids, names, true_names, departments;
    QVector<User>::const_iterator i = users->begin();
    for (QVector<User>::const_iterator i = users->begin(); i != users->end(); i++) {
        ids << i->id();
        names << i->name();
        true_names << i->trueName();
        departments << i->department();
    }
    q.addBindValue(ids);
    q.addBindValue(names);
    q.addBindValue(true_names);
    q.addBindValue(departments);
    bool r;
    if (!(r = q.execBatch()))
        qDebug() << "ClientDB addUsers FAIL!" << q.lastError();
    return r;
}

void Client::AddClient() {
    this->setWindowFlags(Qt::WindowStaysOnTopHint);
    QSqlQuery * query = new QSqlQuery(sdb);
    if (!sdb.open()) {
        qDebug()<<"Something wrong with db openning!";
    }
    else {
        query->prepare("INSERT INTO client (surname, name, middlename) VALUES (:surname,:name,:middlename);");
        query->bindValue(":surname",ui->surname->text());
        query->bindValue(":name", ui->name->text());
        query->bindValue(":middlename", ui->middlename->text());
        if (query->exec())
            this->deleteLater();
        else {
            ui->middlename->clear();
            ui->surname->clear();
            ui->name->clear();
        }
    }
    if (!query->execBatch())
        qDebug() << query->lastError();
    this->deleteLater();
}

void postinput::on_pbOK_clicked()
{
    databaseconnection::setdatabase();
    databaseconnection::Open();
    //获取输入数据
    QString number =ui->leID->text().trimmed();
    QString tel  =ui->leTel->text().trimmed();
    PostNumber = number;

    if(number.isEmpty())
    {
        QMessageBox::information(this,tr("提示"),tr("请输入快递单号！"),QMessageBox::Ok);
    }
    if(tel.isEmpty())
    {
        QMessageBox::information(this,tr("提示"),tr("请输入手机号码！"),QMessageBox::Ok);
    }

    //向post、receive、box表插入数据
    if(!number.isEmpty()&&!tel.isEmpty())
    {
        //插入数据到box表，首先需要选择快递箱类型
        //选取大号快递箱
        if(ui->cbBig->isChecked())
        {
            QSqlQuery query;
            //判断所选箱子类的数量是否为0
            QString BStatus = "available";
            QString BType = "Big";
            query.exec("select count(*) from box where BoxStatus = '"+BStatus+"' and Type = '"+BType+"'");
            int Boxnumber = 0;
            QString BoxID = "";
            if(query.next())
            {
                Boxnumber = query.value(0).toInt();
            }
            //选取投放快递箱子的编号
            if(Boxnumber!=0)
            {
                //插入数据到post表
                query.prepare("INSERT INTO post (Number, Tel, UserID) VALUES (?, ?, ?)");
                QVariantList Number;
                Number << number;
                query.addBindValue(Number);
                QVariantList Tel;
                Tel << tel;
                query.addBindValue(Tel);
                QVariantList UserID;
                UserID <<Username;
                query.addBindValue(UserID);
                if (!query.execBatch())
                {
                    QMessageBox::critical(0, QObject::tr("插入数据到post表失败！"),query.lastError().text());
                }
                if(!query.isActive())
                {
                    QMessageBox::warning(this,tr("错误"),tr("插入数据到post表失败！"),QMessageBox::Ok);
                }

                //插入数据到receive表
                RPwd = createpassword::createpwd();
                QString Rstatus2 = "arrived";
                query.prepare("INSERT INTO receive (Number, Password, Tel, ReceiveStatus) VALUES (?, ?, ?, ?)");
                QVariantList RNumber;
                RNumber << number;
                query.addBindValue(RNumber);
                QVariantList Password;
                Password << RPwd;
                query.addBindValue(Password);
                QVariantList RTel;
                RTel << tel;
                query.addBindValue(RTel);
                QVariantList RStatus;
                RStatus << Rstatus2;
                query.addBindValue(RStatus);
                if (!query.execBatch())
                {
                    QMessageBox::critical(0, QObject::tr("插入数据到receive表失败！"),query.lastError().text());
                }
                if(!query.isActive())
                {
                    QMessageBox::warning(this,tr("错误"),tr("插入数据到receive表失败！"),QMessageBox::Ok);
                }
                //插入box表
                query.exec("select BoxID from box where BoxStatus = '"+BStatus+"' and Type = '"+BType+"'");
                if(query.next())
                {
                    BoxID = query.value(0).toString();
                }
                QString status = "using";
                query.prepare("UPDATE box SET BoxStatus = ?,PostID = ? WHERE BoxID = ? ");
                QVariantList BStatus;
                BStatus << status;
                query.addBindValue(BStatus);
                QVariantList BPostID;
                BPostID << number;
                query.addBindValue(BPostID);
                QVariantList BID;
                BID << BoxID;
                query.addBindValue(BID);
//.........这里部分代码省略.........

bool DatabaseManager::insertInvariants(int type, int number, std::vector< std::vector<float> > invariants)
{

    //bool ret = false;

    if (db.isOpen())
    {

        QSqlQuery query;

        // First check, if a bubble has already entered to the table
        /*    bool exists = query.exec(QString("select * from invariant where type = %1 and number = %2").arg(type).arg(number));

        // If query is successfully executed
        if(exists)
        {
            // if there are elements received from the table, then there exists a bubble, we should delete those entries
            if(query.next())
            {
                ret = query.exec(QString("delete from invariant where type = %1 and number = %2").arg(type).arg(number));

                // If deletion is not successfuly executed return false
                if(!ret)

                    return false;

            }
*/
        //else return false;

        // Speed up the multiple-row insertion by using transactions
        //query.exec(QString("BEGIN TRANSACTION"));

        query.prepare(QString("replace into invariant values(?, ?, ?, ?)"));

        QVariantList typee;
        QVariantList numberr;
        QVariantList placeLabel;
        QVariantList val;


        db.transaction();

        // Insert new bubble
        for(uint i = 0; i <invariants.size(); i++)
        {
            for(uint j = 0; j < invariants[i].size();j++){


                val<< invariants[i][j];
                typee<<type;
                numberr<<number;

                if(placeLabels.size() >= number)
                {
                    placeLabel<<placeLabels.at(number-1);
                }

                else
                    placeLabel<<-1;


                //query.exec(QString("replace into invariant values('%1', '%2', '%3')").arg(type).arg(number).arg(val));


            }
        }

        query.addBindValue(typee);
        query.addBindValue(numberr);
        query.addBindValue(placeLabel);
        query.addBindValue(val);

        // query.exec(QString("COMMIT TRANSACTION"));

        if (!query.execBatch())
            qDebug() << query.lastError();
        db.commit();

        return true;

    }



    //  }

    return false;

}

bool DatabaseManager::insertBubble(int type, int number, std::vector<bubblePoint> bubble)
{

    // bool ret = false;

    if (db.isOpen())
    {

        QSqlQuery query;

        //  query.exec("PRAGMA journal_mode = MEMORY");
        //  query.exec("PRAGMA synchronous = OFF");
        query.prepare(QString("replace into bubble values( ?, ?, ?, ?, ?, ?)"));

        //    query.prepare(QString("insert into bubble values( :type, :number, :pan, :tilt, :val) ON DUPLICATE KEY update val = :val)"));

        //    updateQuery.prepare("update bubble set val = :val where type = %type and number = %2 and pan = %3 and tilt = %4");
        QVariantList typee;
        QVariantList numberr;
        QVariantList placeLabel;
        QVariantList pan;
        QVariantList tilt;
        QVariantList val;

        db.transaction();

        // Insert new bubble
        for(uint i = 0; i < bubble.size(); i++)
        {

            bubblePoint pt;

            pt = bubble.at(i);

            typee<<type;
            numberr<<number;
            pan<<pt.panAng;
            tilt<<pt.tiltAng;
            val<<pt.val;

            if(placeLabels.size() >= number)
            {
                placeLabel<<placeLabels.at(number-1);
            }

            else
                placeLabel<<-1;


            /*      query.bindValue(":type",type);
                 query.bindValue(":number",number);
                  query.bindValue(":pan",pt.panAng);
                   query.bindValue(":tilt",pt.tiltAng);
                   query.bindValue(":val",pt.val);*/
            //  query.exec();

            //query.exec(QString("insert into bubble values('%1', '%2', '%3', '%4', %5)").arg(type).arg(number).arg(pt.panAng).arg(pt.tiltAng).arg(pt.val));


        }



        query.addBindValue(typee);
        query.addBindValue(numberr);
        query.addBindValue(placeLabel);
        query.addBindValue(pan);
        query.addBindValue(tilt);
        query.addBindValue(val);


        if (!query.execBatch())
            qDebug() << query.lastError();

        db.commit();

        return true;

    }



    return false;



}

// 操作book_info表
int bookInfoInsert(const char* name, const char* depName, AutoType& photo)
{
    Log log(__LOGARG__,1);
	QSqlQuery query;
	query.exec("INSERT INTO book_info (emp_name, dep_name, hire_time, photo) VALUES(?,?,now(),?)");
	QVariantList emp_name;
	emp_name << name;
	query.addBindValue(emp_name);
	QVariantList dep_name;
	dep_name << depName;
	query.addBindValue(dep_name);
    QByteArray data(photo.getStr(),photo.getLen());
    QVariantList emp_photo;
    emp_photo << data;
	query.addBindValue(emp_photo);
    log << "len1=" << data.size() << Log::endl;
	try
    {
        if (!query.execBatch())
        {
            log << ((query.lastQuery()).toUtf8()).constData() << Log::endl;
            log << (((query.lastError()).text()).toUtf8()).constData() << Log::endl;
            return -1;
        }
    }
    catch(...)
    {
        log << "Add New Node error! Unable to add a new Node!" << Log::endl;
    }
    //query.prepare("select emp_id from book_info where emp_id = (select max(emp_id) from book_info)");
    //query.exec();
    QVariant qvid = query.lastInsertId();
    int empId = qvid.toInt();
    query.prepare("select * from book_info");
    query.exec();
    QSqlRecord rec = query.record();
    int photoCol = rec.indexOf("photo");
    int empIdCol = rec.indexOf("emp_id");
    log << "empId=" << empId << Log::endl;
    int i=0;
    while (query.next()) {
        int id = (query.value(empIdCol)).toInt();
        log << "id=" << id << Log::endl;
        if (id==empId) {
        QByteArray data2 = (query.value(photoCol)).toByteArray();
        log << "len2=" << data2.size() << Log::endl;
        for (int j=0; j<data2.size(); j++)
        {
            char a=data[j];
            char b=data2[j];
            if (a!=b) {
                //log << "不相等" << j << ":" << a <<" , " << b << Log::endl;
            }
        }
        cv::Mat mat = QByteArray2Mat(data2,data2.size());
        std::string s;
        num2str(i, s);
        imwrite("tmp"+s+".jpg",mat);
        mat = AutoType2Mat(photo);
        imwrite("tmp2.jpg",mat);
        i++;
        }
    }
    return empId;
}