C++ QSqlQuery函数代码示例

SeeStudents::SeeStudents(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::SeeStudents)
{
    this->showMaximized();

    ui->setupUi(this);

    ostringstream q;
    q << "SELECT * FROM Student;";
    QSqlQuery query = QSqlQuery(QString(q.str().c_str()));
    ui->tableWidget->setColumnCount(4);
    int i = 1;

    ui->tableWidget->setHorizontalHeaderItem(0, new QTableWidgetItem("Foto"));
    ui->tableWidget->setHorizontalHeaderItem(1, new QTableWidgetItem("Nombre"));
    ui->tableWidget->setHorizontalHeaderItem(2, new QTableWidgetItem("Localización"));
    ui->tableWidget->setHorizontalHeaderItem(3, new QTableWidgetItem("Seleccionar para modelo"));
    ui->tableWidget->horizontalHeader()->setStretchLastSection(true);

    while(query.next()){
        int id = query.value("id").toInt();
        ui->tableWidget->setRowCount(i);
        string name = query.value("name").toString().toStdString();
        string path = query.value("directory").toString().toStdString();
        QString pathImage = QString(path.c_str()) + "/0.jpg";
        QImage* image = new QImage();
        if(!image->load(pathImage))
            cout << "ERROR LOADING" << endl;
        else{
            QTableWidgetItem *itm = new QTableWidgetItem;
            *image = image->scaledToHeight(50);
            itm->setData(Qt::DecorationRole, QPixmap::fromImage(*image));
            itm->setFlags(itm->flags() ^ Qt::ItemIsEditable);
            ui->tableWidget->setItem(i - 1, 0, itm);
            QTableWidgetItem *itm2 = new QTableWidgetItem;

            itm2->setData(Qt::DisplayRole, name.c_str());
            ui->tableWidget->setItem(i - 1, 1, itm2);
            QTableWidgetItem* itm3 = new QTableWidgetItem;
            itm3->setData(Qt::DisplayRole, path.c_str());
            ui->tableWidget->setItem(i - 1, 2, itm3);
            QCheckBox* check = new QCheckBox();
            ui->tableWidget->setCellWidget(i - 1, 3, check);
            _checks.push_back(check);
            i++;
        }
        _students.push_back(new Alumno(id, path, name));
    }
    ui->tableWidget->resizeColumnsToContents();
    ui->tableWidget->resizeRowsToContents();

}

QSqlQuery DBWorker::prepare(const char *statement)
{
    QSqlQuery query(m_database);
    query.setForwardOnly(true);
    if (!query.prepare(statement)) {
        qWarning() << Q_FUNC_INFO << "failed to prepare query";
        qWarning() << query.lastQuery();
        qWarning() << query.lastError();
        return QSqlQuery();
    }
    return query;
}

void SoftKeyPad::connectDatabase()
{
    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("china.db");
    if(db.open()) {
        qDebug() << "db open successful!";
    } else {
        qDebug() << "db open failure!";
    }

    query = QSqlQuery("china.db");
}

QSqlQuery CommHistoryDatabase::prepare(const char *statement, const QSqlDatabase &database)
{
    QSqlQuery query(database);
    query.setForwardOnly(true);
    if (!query.prepare(statement)) {
        qWarning() << "Failed to prepare query";
        qWarning() << query.lastError();
        qWarning() << statement;
        return QSqlQuery();
    }
    return query;
}

bool NGSD::isOpen() const
{
	static bool is_initialized = false;
	static bool is_open = false;
	if (!is_initialized)
	{
		is_open = QSqlQuery(*db_).exec("SELECT 1");
		is_initialized = true;
	}

	return is_open;
}

bool ibanBicStoragePlugin::setupDatabase(QSqlDatabase connection)
{
  // Get current version
  QSqlQuery query = QSqlQuery(connection);
  query.prepare("SELECT versionMajor FROM kmmPluginInfo WHERE iid = ?");
  query.bindValue(0, iid());
  if (!query.exec()) {
    qWarning("Could not execute query for ibanBicStoragePlugin: %s", qPrintable(query.lastError().text()));
    return false;
  }

  int currentVersion = 0;
  if (query.next())
    currentVersion = query.value(0).toInt();

  // Create database in it's most recent version if version is 0
  // (version 0 means the database was not installed)
  if (currentVersion == 0) {
    // If the database is recreated the table may be still there. So drop it if needed. No error handling needed
    // as this step is not necessary - only the creation is important.
    query.exec("DROP TABLE IF EXISTS kmmIbanBic;");

    if (!query.exec(
          "CREATE TABLE kmmIbanBic ("
          "  id varchar(32) NOT NULL PRIMARY KEY REFERENCES kmmPayeeIdentifier( id ) ON DELETE CASCADE ON UPDATE CASCADE,"
          "  iban varchar(32),"
          "  bic char(11) CHECK(length(bic) = 11 OR bic IS NULL),"
          "  name text"
          " );"
        )) {
      qWarning("Could not create table for ibanBicStoragePlugin: %s", qPrintable(query.lastError().text()));
      return false;
    }

    query.prepare("INSERT INTO kmmPluginInfo (iid, versionMajor, versionMinor, uninstallQuery) VALUES(?, ?, ?, ?)");
    query.bindValue(0, iid());
    query.bindValue(1, 1);
    query.bindValue(2, 0);
    query.bindValue(3, "DROP TABLE kmmIbanBic;");
    if (query.exec())
      return true;
    qWarning("Could not save plugin info for ibanBicStoragePlugin (%s): %s", qPrintable(iid()), qPrintable(query.lastError().text()));
    return false;
  }

  // Check if version is valid with this plugin
  switch (currentVersion) {
    case 1: return true;
  }

  return false;
}

QList<key> dbAccess::getTitleID() {
    QList<key> result;
    if(dbStatus) {
        request = QSqlQuery(db);
        request.exec("select ID, Title from contacts Order by Title ASC");
        while(request.next()) {
            result.append({request.value(0).toInt(),request.value(1).toString()});
        }
        return result;
    } else {
        qDebug() << "Database doesn't open";
    }
}

void SyncDatabase::saveSyncFile(SyncFile * sf, int parent_id)
{
    if (!sf->isInDatabase()) {
        QSqlQuery(*db).exec(QString("INSERT INTO content (id, parent_id, file_name, last_modified) VALUES (%1, %2, '%3', '%4')")
                            .arg(sf->index())
                            .arg(parent_id)
                            .arg(sf->getName())
                            .arg(sf->lastModifiedString()));
    } else if (sf->wasModified() && sf->wasDeleted()) {
        removeRecords(sf->index());
        return;
    } else if (sf->wasModified()) {
        QSqlQuery(*db).exec(QString("UPDATE content SET parent_id = %1, file_name = '%2', last_modified = '%3' WHERE id = %4")
                            .arg(parent_id)
                            .arg(sf->getName())
                            .arg(sf->lastModifiedString())
                            .arg(sf->index()));
    }
    for (int i = 0; i < sf->childCount(); ++i) {
        saveSyncFile(sf->childAt(i), sf->index());
    }
}

TaskRecorder::TaskRecorder() 
{
   this->initDatabase();
   if (!dbconn.open())
   {
        QMessageBox::critical(0, QObject::tr("Database"), QObject::tr("Sorry, I couldn't connect to SQLite database"));
        return;
   }

   query = QSqlQuery(dbconn);
   query.exec("create table if not exists tasks(id integer primary key autoincrement,event varchar, time datetime)");
   qDebug() << "open sqlite connection";
}

void DebtReport::on_type_currentIndexChanged(int index)
{
    if (index == 0) {
        setWindowTitle(QString::fromUtf8("Отчет: задолженность по заказчикам"));

    } else if (index == 1) {
        setWindowTitle(QString::fromUtf8("Отчет: задолженность поставщикам/подрядчикам"));

    }

    ui->total->clear();
    model->setQuery(QSqlQuery());
}

BoardUtils::BoardUtils(QObject *parent) :
    QObject(parent)
{
    m_carmodel = new CarModel();
    //colors taken from gtlevel-0.4.c, line 143
    //transform them to QML color type
    m_colors.insert("DarkGreen", "darkgreen");
    m_colors.insert("purple3","mediumpurple");
    m_colors.insert("orange","orange");
    m_colors.insert("seagreen","seagreen");
    m_colors.insert("blue3","mediumblue");
    m_colors.insert("SteelBlue1","lightsteelblue");
    m_colors.insert("yellow","yellow");
    m_colors.insert("gold3","goldenrod");
    m_colors.insert("IndianRed1","indianred");
    m_colors.insert("SteelBlue4","steelblue");
    m_colors.insert("LightSkyBlue1","lightskyblue");
    m_colors.insert("SlateGray2","lightslategray");
    m_colors.insert("LavenderBlush1","lavenderblush");
    m_colors.insert("LemonChiffon1","lemonchiffon");
    m_colors.insert("MistyRose2","mistyrose");
    m_colors.insert("NavajoWhite4", "navajowhite");
    m_colors.insert("thistle2","thistle");
    m_colors.insert("red","red");

    //now let's load stuff from database
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
   // QString path = adjustPath("data/levels.db");
    QString path = "app/native/assets/levels.db";
    db.setDatabaseName(path);

    if (!db.open()) {
        //abort with error
        qDebug() << "Error opening database : " << db.lastError();
        //db.lastError();
    }
    QStringList tables = db.tables();
    if (!tables.contains("levels", Qt::CaseInsensitive)) {
        //abort with error
        QSqlError("sqlite", "data/levels.db doesn't contain tabe 'levels'");
    }
    m_query = QSqlQuery("", db);
    m_query.exec(QLatin1String("select count(id) from levels;"));
    m_query.next();
    m_numLevels = m_query.value(0).toInt();

    int lastLevel = m_sett.value("lastLevelID", 1).toInt();
    if(lastLevel<1 || lastLevel>m_numLevels) lastLevel=1;

    loadLevel(lastLevel);
}

QSqlQuery MysqlConnection::Query(QString sqlQuery)
{
    if(sqlQuery.isEmpty() || !m_db.isOpen())
        return QSqlQuery();

    QSqlQuery req = QSqlQuery(m_db);

    if(!req.exec(sqlQuery))
    {
        Log::Write(LOG_TYPE_NORMAL, "SQL error with %s", sqlQuery.toLatin1().data());
        Log::Write(LOG_TYPE_NORMAL, "[Error %u] %s", req.lastError().number(), req.lastError().text().toLatin1().data());

        if (req.lastError().number() == 2013 || req.lastError().number() == 2003)
        {
            if (Reconnect())
                Query(sqlQuery);
            else
                Log::Write(LOG_TYPE_NORMAL, "Reconnect failed : MySQL connection lost.");
        }
    }

    return req;
}

bool sqlite3::openSQL3(QString db)
{
    this->DB = new QSqlDatabase(QSqlDatabase::addDatabase("QSQLITE","mysqlite3"));
    this->DB->setDatabaseName(db);

    if (!this->DB->open()){
        qDebug() << "DB error open:" << this->DB->lastError().text();
        delete this->DB;
        QSqlDatabase::removeDatabase("mysqlite3");
        return false;
    }
    this->Q = QSqlQuery(*this->DB);
    return true;
}

void CrmClusterLineEdit::silentSetId(const int pId)
{
  if (DEBUG)
    qDebug("VCLE %s::silentSetId(%d)", qPrintable(objectName()), pId);

  if (pId == -1)
  {
    XLineEdit::clear();
    _model = new QSqlQueryModel(this);
  }
  else
  {
    XSqlQuery idQ;
    idQ.prepare(_query + _idClause + QString(";"));
    idQ.bindValue(":id", pId);
    idQ.exec();
    if (idQ.first())
    {
      if (_completer)
        static_cast<QSqlQueryModel* >(_completer->model())->setQuery(QSqlQuery());

      _id = pId;
      _valid = true;

      _model->setQuery(idQ);

      setText(idQ.value("number").toString());
      if (_hasName)
        _name = (idQ.value("name").toString());
      if (_hasDescription)
        _description = idQ.value("description").toString();
      if (_hasActive)
        setStrikeOut(!idQ.value("active").toBool());
      if (_hasOwner)
        _owner = idQ.value("owner").toString();
      if (_hasAssignto)
        _assignto = idQ.value("assignto").toString();
    }
    else if (idQ.lastError().type() != QSqlError::NoError)
      QMessageBox::critical(this, tr("A System Error Occurred at %1::%2.")
                            .arg(__FILE__)
                            .arg(__LINE__),
                            idQ.lastError().databaseText());
  }

  _parsed = true;
  sHandleNullStr();
  emit parsed();
}

QString QgsDb2GeometryColumns::open( const QString &schemaName, const QString &tableName )
{
  QString queryExtents( "SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, TYPE_NAME, "
                        "SRS_ID, SRS_NAME, MIN_X, MIN_Y, MAX_X, MAX_Y "
                        "FROM DB2GSE.ST_GEOMETRY_COLUMNS" );
  QString queryNoExtents( "SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, TYPE_NAME, "
                          "SRS_ID, SRS_NAME "
                          "FROM DB2GSE.ST_GEOMETRY_COLUMNS" );
  mQuery = QSqlQuery( mDatabase );
  QString nativeError;
  mEnvironment = ENV_LUW;

  if ( !schemaName.isEmpty() && !tableName.isEmpty() )
  {
    QString whereClause = QStringLiteral( " WHERE TABLE_SCHEMA = '%1' AND TABLE_NAME = '%2'" )
                          .arg( schemaName, tableName );
    queryExtents += whereClause;
    queryNoExtents += whereClause;
  }
  QgsDebugMsg( queryExtents );
  // issue the sql query
  if ( !mQuery.exec( queryExtents ) )
  {
    QgsDebugMsg( "ST_Geometry_Columns query failed: " + mDatabase.lastError().text() );
    nativeError = mQuery.lastError().nativeErrorCode();
    QgsDebugMsg( QStringLiteral( "SQLCODE: %1" ).arg( nativeError ) );
    /* The MIN_X, MIN_Y, MAX_X, and MAX_Y columns are not available on z/OS (and LUW 9.5)
       so SQLCODE -206 is returned when specifying non-existent columns. */
    if ( mQuery.lastError().nativeErrorCode() == QStringLiteral( "-206" ) )
    {
      QgsDebugMsg( QStringLiteral( "Try query with no extents" ) );
      mQuery.clear();

      if ( !mQuery.exec( queryNoExtents ) )
      {
        QgsDebugMsg( QStringLiteral( "SQLCODE: %1" ).arg( mQuery.lastError().nativeErrorCode() ) );
      }
      else
      {
        QgsDebugMsg( QStringLiteral( "success; must be z/OS" ) );
        mEnvironment = ENV_ZOS;
        nativeError.clear();
      }
    }
  }
//  QgsDebugMsg( QStringLiteral( "sqlcode: %1" ).arg( sqlcode ) );

  return nativeError;
}