C++ QSqlTableModel::setSort方法代码示例

void MainWindow::setModel(QTableView *tableViewName, QString modelName, QString tableName, QStringList *tableFields, QString filter, int orderNum)
{
    QSqlTableModel *model;
    if (modelName == "modelEdit") {
        modelEdit = new QSqlTableModel;
        model = modelEdit;
    } else if (modelName == "modelChoose") {
        modelChoose = new QSqlTableModel;
        model = modelChoose;
    } else if (modelName == "modelQ") {
        modelQ = new QSqlTableModel;
        model = modelQ;
    } else if (modelName == "modelQE") {
        modelQE = new QSqlTableModel;
        model = modelQE;
    } else if (modelName == "modelNote") {
        modelNote = new QSqlTableModel;
        model = modelNote;
    } else if (modelName== "modelNotes") {
        modelNotes = new QSqlTableModel;
        model = modelNotes;
    } else {
        return;
    }

    model= new QSqlTableModel(this);
    model->setTable(tableName);
    model->setEditStrategy(QSqlTableModel::OnFieldChange);

    model->setSort(orderNum, Qt::DescendingOrder);

    int tableFieldsNum = tableFields->length();
    for (int i = 0; i < tableFieldsNum; i++) {
        model->setHeaderData(i, Qt::Horizontal, tableFields->at(i));
    }

    tableViewName->setModel(model);
    tableViewName->alternatingRowColors();
    tableViewName->horizontalHeader()->setStretchLastSection(true);

    model->setFilter(filter);
    model->select();
    tableViewName->reset();
    qDebug() << modelName << model << model->rowCount();

    if (modelName == "modelEdit") {
        modelEdit = model;
    } else if (modelName == "modelChoose") {
        modelChoose = model;
    } else if (modelName == "modelQ") {
        modelQ = model;
    } else if (modelName == "modelQE") {
        modelQE = model;
    } else if (modelName == "modelNote") {
        modelNote = model;
    } else if (modelName == "modelNotes") {
        modelNotes = model;
    } else return;
}

    void  FenPrincipale::afficherTable()
    {
    //    database = FenPrincipale::newdatabase;
    //     createConnection();

        //QMessageBox::information(0,QObject::tr("debug"),
          //              "debug "+database);

        QSqlTableModel  *model = new QSqlTableModel ();
        model->setTable( "stationStationnee" );
        model->setEditStrategy(QSqlTableModel::OnManualSubmit);
        model->setSort(0,Qt::AscendingOrder);
        model->select();
        QSqlTableModel  *model1 = new QSqlTableModel ();
        model1->setTable("mesures");
        model1->setEditStrategy(QSqlTableModel::OnManualSubmit);
        model1->setSort(0,Qt::AscendingOrder);
        model1->select();


        tabless->setModel( model );
        tabless->show();
    //    tabless->setColumnHidden(0,true);

        tabless->setColumnHidden(5,true);
        tabless->setColumnHidden(6,true);
        tabless->setColumnHidden(7,true);
        tabless->resizeColumnToContents(1);
        tablemesures->setModel( model1 );
       // tablemesures->setColumnHidden(0,true);
        tablemesures->setColumnHidden(19,true);
        tablemesures->resizeColumnsToContents();
         QSqlQuery query;
         QModelIndex index;

    //     query.exec("select id from mesures where classe = 30");
    //     int id=0;
    //     while (query.next()) {
    //            id  = query.value(0).toInt();
    //       }

    //    if (index.row() == id)
    //       {
    //           tablemesures->setBackgroundRole(QPalette::Text);
    //       }tmpAreteList->count()
        tablemesures->show();


    //    QString date = "";
    //    QFileInfo file(database);

    //    QDateTime lastModified = file.lastModified();
    //    date = lastModified.toString();
    //    bool valid  = QFile::copy (database, database+"."+date);


    }

void CollectionTreeWidget::showChildrenOf(QModelIndex index) {
    CollectionTreeWidgetItem *item = (CollectionTreeWidgetItem*)itemFromIndex(index);

    // If the item pressed was an artist, add albums
    if (item->getNodeLevel() == LevelArtist) {
        QString artist = item->text(0).toUtf8();

        // Looks for artist id
        QString artistId = QString::number(item->getId());

        // Looks for artist albums
        QSqlTableModel *albumModel = service->albumModel();
        albumModel->setFilter("id_artist = " + artistId);
        albumModel->select();

        while (albumModel->canFetchMore()) albumModel->fetchMore();
        int total = albumModel->rowCount();

        for (int i = 0; i < total; i++) {
            QString album = albumModel->record(i).value(albumModel->fieldIndex("title")).toString();
            unsigned int id = albumModel->record(i).value(albumModel->fieldIndex("id")).toInt();
            addAlbum(artist, album, id);
        }

        delete albumModel;
    }
    // If the item pressed was an album, add songs
    else if (item->getNodeLevel() == LevelAlbum) {
        QString albumId = QString::number(item->getId());

        QSqlTableModel *musicModel = service->musicModel();
        musicModel->setFilter("id_album = " + albumId);
        musicModel->setSort(musicModel->fieldIndex("track_number"), Qt::AscendingOrder);
        musicModel->select();

        while (musicModel->canFetchMore()) musicModel->fetchMore();
        int total = musicModel->rowCount();

        for (int i = 0; i < total; i++) {
            QString path = musicModel->record(i).value(musicModel->fieldIndex("path")).toString();
            unsigned int id = musicModel->record(i).value(musicModel->fieldIndex("id")).toInt();

            Music *music = new Music(QUrl(path));
            addMusic(music, id);
            delete music;
        }
    }

    expand(index);
}

void QgsBookmarks::addClicked()
{
    QSqlTableModel *model = qobject_cast<QSqlTableModel *>( lstBookmarks->model() );
    Q_ASSERT( model );

    QgsMapCanvas *canvas = QgisApp::instance()->mapCanvas();
    Q_ASSERT( canvas );

    QSqlQuery query( "INSERT INTO tbl_bookmarks(bookmark_id,name,project_name,xmin,ymin,xmax,ymax,projection_srid)"
                     "  VALUES (NULL,:name,:project_name,:xmin,:xmax,:ymin,:ymax,:projection_srid)",
                     model->database() );

    QString projStr( "" );
    if ( QgsProject::instance() )
    {
        if ( !QgsProject::instance()->title().isEmpty() )
        {
            projStr = QgsProject::instance()->title();
        }
        else if ( !QgsProject::instance()->fileName().isEmpty() )
        {
            QFileInfo fi( QgsProject::instance()->fileName() );
            projStr = fi.exists() ? fi.fileName() : "";
        }
    }

    query.bindValue( ":name", tr( "New bookmark" ) );
    query.bindValue( ":project_name", projStr );
    query.bindValue( ":xmin", canvas->extent().xMinimum() );
    query.bindValue( ":ymin", canvas->extent().yMinimum() );
    query.bindValue( ":xmax", canvas->extent().xMaximum() );
    query.bindValue( ":ymax", canvas->extent().yMaximum() );
    query.bindValue( ":projection_srid", QVariant::fromValue( canvas->mapSettings().destinationCrs().srsid() ) );
    if ( query.exec() )
    {
        model->setSort( 0, Qt::AscendingOrder );
        model->select();
        lstBookmarks->scrollToBottom();
        lstBookmarks->setCurrentIndex( model->index( model->rowCount() - 1, 1 ) );
        lstBookmarks->edit( model->index( model->rowCount() - 1, 1 ) );
    }
    else
    {
        QMessageBox::warning( this, tr( "Error" ), tr( "Unable to create the bookmark.\nDriver:%1\nDatabase:%2" )
                              .arg( query.lastError().driverText() )
                              .arg( query.lastError().databaseText() ) );
    }
}

TableView::TableView()
   : QWidget()
{
   if (! this->createConnection()) {
      QString msg = "** Create Database Error";
      throw msg;
   }

   setWindowTitle(tr("Table View Model"));
   setMinimumSize(300, 350);

   //
   QSqlTableModel *model = new QSqlTableModel(this, m_db);

   QStringList tempX = m_db.tables();
   model->setTable(tempX.at(0));

   QSqlError error = model->lastError();

   if ( error.isValid() )  {
      ksMsg( this, "Table View - SQL Error", error.text() );
   }

   model->setEditStrategy(QSqlTableModel::OnManualSubmit);
   model->setSort(2, Qt::AscendingOrder);
   model->select();

   model->setHeaderData(0, Qt::Horizontal, QObject::tr("ID"));
   model->setHeaderData(1, Qt::Horizontal, QObject::tr("First name"));
   model->setHeaderData(2, Qt::Horizontal, QObject::tr("Last name"));

   //
   QTableView *view = new QTableView;
   view->setModel(model);
   view->resizeColumnsToContents();

   QHeaderView *temp = view->horizontalHeader();
   temp->setStretchLastSection(true);

   QHBoxLayout *mainLayout = new QHBoxLayout;
   mainLayout->addWidget(view);
   setLayout(mainLayout);  
}

//.........这里部分代码省略.........
    QSqlQuery query;
    query.exec("UPDATE employee SET salary = 70000 WHERE id = 1003");
//! [37]
    }

    {
    // DELETE1
//! [38]
    QSqlQuery query;
    query.exec("DELETE FROM employee WHERE id = 1007");
//! [38]
    }

    {
    // TRANSACTION
//! [39]
    QSqlDatabase::database().transaction();
    QSqlQuery query;
    query.exec("SELECT id FROM employee WHERE name = 'Torild Halvorsen'");
    if (query.next()) {
        int employeeId = query.value(0).toInt();
        query.exec("INSERT INTO project (id, name, ownerid) "
                   "VALUES (201, 'Manhattan Project', "
                   + QString::number(employeeId) + ')');
    }
    QSqlDatabase::database().commit();
//! [39]
    }

    {
    // SQLQUERYMODEL1
//! [40]
    QSqlQueryModel model;
    model.setQuery("SELECT * FROM employee");

    for (int i = 0; i < model.rowCount(); ++i) {
        int id = model.record(i).value("id").toInt();
        QString name = model.record(i).value("name").toString();
        qDebug() << id << name;
    }
//! [40]
    }

    {
    // SQLTABLEMODEL1
//! [41]
    QSqlTableModel model;
    model.setTable("employee");
    model.setFilter("salary > 50000");
    model.setSort(2, Qt::DescendingOrder);
    model.select();

    for (int i = 0; i < model.rowCount(); ++i) {
        QString name = model.record(i).value("name").toString();
        int salary = model.record(i).value("salary").toInt();
        qDebug() << name << salary;
    }
//! [41]
    }

    {
    // SQLTABLEMODEL2
    QSqlTableModel model;
    model.setTable("employee");

//! [42]
    for (int i = 0; i < model.rowCount(); ++i) {
        QSqlRecord record = model.record(i);
        double salary = record.value("salary").toInt();
        salary *= 1.1;
        record.setValue("salary", salary);
        model.setRecord(i, record);
    }
    model.submitAll();
//! [42]

    // SQLTABLEMODEL3
    int row = 1;
    int column = 2;
//! [43]
    model.setData(model.index(row, column), 75000);
    model.submitAll();
//! [43]

    // SQLTABLEMODEL4
//! [44]
    model.insertRows(row, 1);
    model.setData(model.index(row, 0), 1013);
    model.setData(model.index(row, 1), "Peter Gordon");
    model.setData(model.index(row, 2), 68500);
    model.submitAll();
//! [44]

//! [45]
    model.removeRows(row, 5);
//! [45] //! [46]
    model.submitAll();
//! [46]
    }
}

int IDResource::getSingleIdRes(QString& idType)
{
	QSqlTableModel model;
	model.setTable(idType);
	QString filter1="";
	int minNum,maxNum;

	if (idType==tr("ACLID"))
	{
		filter1=QString("ID>=%1 and ID<=%2").arg(g_minACLID).arg(g_maxACLID);
		minNum=g_minACLID;
		maxNum=g_maxACLID;
	}
	else if (idType==tr("XACLID"))
	{
		filter1=QString("ID>=%1 and ID<=%2").arg(g_minXACLID).arg(g_maxXACLID);
		minNum=g_minXACLID;
		maxNum=g_maxXACLID;
	}
	else if (idType==tr("PLID"))
	{
		filter1=QString("ID>=%1 and ID<=%2").arg(g_minPLID).arg(g_maxPLID);
		minNum=g_minPLID;
		maxNum=g_maxPLID;
	}
	
	model.setFilter(filter1);
	model.setSort( 0, Qt::AscendingOrder);
	model.select();
		
	QSet<int>idSet;
	
	for(int j=0;j<model.rowCount();++j)
	{
		QSqlRecord record=model.record(j);
		idSet<<record.value("id").toInt();
	}
	
	QQueue<int>queueId;
	
	for(int j=minNum;j!=maxNum+1;++j)
	{
		if(!idSet.contains(j))
		{
			queueId.enqueue(j);
		}
	}
	
	idSet.clear();
	
	if(queueId.count()<=0)
	{
		QMessageBox::information(this, tr("information"), tr("无可用ID资源"));
	}

	while(true)
	{
		int resId=queueId.dequeue();
	    if(!idConflict(resId,idType))
			return resId;
	}
	
	return 0;
}

QgsBookmarks::QgsBookmarks( QWidget *parent ) : QDockWidget( parent )
{
    setupUi( this );
    restorePosition();

    QToolButton* btnImpExp = new QToolButton;
    btnImpExp->setAutoRaise( true );
    btnImpExp->setToolTip( tr( "Import/Export Bookmarks" ) );
    btnImpExp->setIcon( QgsApplication::getThemeIcon( "/mActionSharing.svg" ) );
    btnImpExp->setPopupMode( QToolButton::InstantPopup );

    QMenu *share = new QMenu( this );
    QAction *btnExport = share->addAction( tr( "&Export" ) );
    QAction *btnImport = share->addAction( tr( "&Import" ) );
    btnExport->setIcon( QgsApplication::getThemeIcon( "/mActionSharingExport.svg" ) );
    btnImport->setIcon( QgsApplication::getThemeIcon( "/mActionSharingImport.svg" ) );
    connect( btnExport, SIGNAL( triggered() ), this, SLOT( exportToXML() ) );
    connect( btnImport, SIGNAL( triggered() ), this, SLOT( importFromXML() ) );
    btnImpExp->setMenu( share );

    connect( actionAdd, SIGNAL( triggered() ), this, SLOT( addClicked() ) );
    connect( actionDelete, SIGNAL( triggered() ), this, SLOT( deleteClicked() ) );
    connect( actionZoomTo, SIGNAL( triggered() ), this, SLOT( zoomToBookmark() ) );

    mBookmarkToolbar->addWidget( btnImpExp );
    mBookmarkToolbar->addAction( actionHelp );

    // open the database
    QSqlDatabase db = QSqlDatabase::addDatabase( "QSQLITE", "bookmarks" );
    db.setDatabaseName( QgsApplication::qgisUserDbFilePath() );
    if ( !db.open() )
    {
        QMessageBox::warning( this, tr( "Error" ),
                              tr( "Unable to open bookmarks database.\nDatabase: %1\nDriver: %2\nDatabase: %3" )
                              .arg( QgsApplication::qgisUserDbFilePath() )
                              .arg( db.lastError().driverText() )
                              .arg( db.lastError().databaseText() )
                            );
        deleteLater();
        return;
    }

    QSqlTableModel *model = new QSqlTableModel( this, db );
    model->setTable( "tbl_bookmarks" );
    model->setSort( 0, Qt::AscendingOrder );
    model->setEditStrategy( QSqlTableModel::OnFieldChange );
    model->select();

    // set better headers then column names from table
    model->setHeaderData( 0, Qt::Horizontal, tr( "ID" ) );
    model->setHeaderData( 1, Qt::Horizontal, tr( "Name" ) );
    model->setHeaderData( 2, Qt::Horizontal, tr( "Project" ) );
    model->setHeaderData( 3, Qt::Horizontal, tr( "xMin" ) );
    model->setHeaderData( 4, Qt::Horizontal, tr( "yMin" ) );
    model->setHeaderData( 5, Qt::Horizontal, tr( "xMax" ) );
    model->setHeaderData( 6, Qt::Horizontal, tr( "yMax" ) );
    model->setHeaderData( 7, Qt::Horizontal, tr( "SRID" ) );

    lstBookmarks->setModel( model );

    QSettings settings;
    lstBookmarks->header()->restoreState( settings.value( "/Windows/Bookmarks/headerstate" ).toByteArray() );

#ifndef QGISDEBUG
    lstBookmarks->setColumnHidden( 0, true );
#endif
}

QgsBookmarks::QgsBookmarks( QWidget *parent, Qt::WindowFlags fl )
    : QDialog( parent, fl )
{
  setupUi( this );
  restorePosition();

  //
  // Create the zoomto and delete buttons and add them to the
  // toolbar
  //
  QPushButton *btnAdd    = new QPushButton( tr( "&Add" ) );
  QPushButton *btnDelete = new QPushButton( tr( "&Delete" ) );
  QPushButton *btnZoomTo = new QPushButton( tr( "&Zoom to" ) );
  QPushButton *btnImpExp = new QPushButton( tr( "&Share" ) );

  btnZoomTo->setDefault( true );
  buttonBox->addButton( btnAdd, QDialogButtonBox::ActionRole );
  buttonBox->addButton( btnDelete, QDialogButtonBox::ActionRole );
  buttonBox->addButton( btnZoomTo, QDialogButtonBox::ActionRole );
  buttonBox->addButton( btnImpExp, QDialogButtonBox::ActionRole );

  QMenu *share = new QMenu();
  QAction *btnExport = share->addAction( tr( "&Export" ) );
  QAction *btnImport = share->addAction( tr( "&Import" ) );
  connect( btnExport, SIGNAL( triggered() ), this, SLOT( exportToXML() ) );
  connect( btnImport, SIGNAL( triggered() ), this, SLOT( importFromXML() ) );
  btnImpExp->setMenu( share );

  connect( btnAdd, SIGNAL( clicked() ), this, SLOT( addClicked() ) );
  connect( btnDelete, SIGNAL( clicked() ), this, SLOT( deleteClicked() ) );
  connect( btnZoomTo, SIGNAL( clicked() ), this, SLOT( zoomToBookmark() ) );

  // open the database
  QSqlDatabase db = QSqlDatabase::addDatabase( "QSQLITE", "bookmarks" );
  db.setDatabaseName( QgsApplication::qgisUserDbFilePath() );
  if ( !db.open() )
  {
    QMessageBox::warning( this, tr( "Error" ),
                          tr( "Unable to open bookmarks database.\nDatabase: %1\nDriver: %2\nDatabase: %3" )
                          .arg( QgsApplication::qgisUserDbFilePath() )
                          .arg( db.lastError().driverText() )
                          .arg( db.lastError().databaseText() )
                        );
    deleteLater();
    return;
  }

  QSqlTableModel *model = new QSqlTableModel( this, db );
  model->setTable( "tbl_bookmarks" );
  model->setSort( 0, Qt::AscendingOrder );
  model->setEditStrategy( QSqlTableModel::OnFieldChange );
  model->select();

  // set better headers then column names from table
  model->setHeaderData( 0, Qt::Horizontal, tr( "ID" ) );
  model->setHeaderData( 1, Qt::Horizontal, tr( "Name" ) );
  model->setHeaderData( 2, Qt::Horizontal, tr( "Project" ) );
  model->setHeaderData( 3, Qt::Horizontal, tr( "xMin" ) );
  model->setHeaderData( 4, Qt::Horizontal, tr( "yMin" ) );
  model->setHeaderData( 5, Qt::Horizontal, tr( "xMax" ) );
  model->setHeaderData( 6, Qt::Horizontal, tr( "yMax" ) );
  model->setHeaderData( 7, Qt::Horizontal, tr( "SRID" ) );

  lstBookmarks->setModel( model );

  QSettings settings;
  lstBookmarks->header()->restoreState( settings.value( "/Windows/Bookmarks/headerstate" ).toByteArray() );

#ifndef QGISDEBUG
  lstBookmarks->setColumnHidden( 0, true );
#endif
}