Qt中常用的数据库操作

简述：

常用的数据库操作主要有查询、插入、删除等

QSqlDatabase创建连接数据库实例，一个QSqlDatabase的实例代表一个数据库的连接。

Qt 提供了对不同数据库的驱动支持：

Driver Type	Description
QDB2	IBM DB2
QIBASE	Borland InterBase Driver
QMYSQL	MySQL Driver
QOCI	Oracle Call Interface Driver
QODBC	ODBC Driver (includes Microsoft SQL Server)
QPSQL	PostgreSQL Driver
QSQLITE	SQLite version 3 or above
QSQLITE2	SQLite version 2
QTDS	Sybase Adaptive Server

 

1、创建连接数据库

 

/**
 * @brief initDb
 * 创建Db
 * @return
 */
bool initDb()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "project");  
    db.setHostName("localhost");  //数据库主机名   
    db.setDatabaseName("myDb");   //数据库名   
    db.setUserName("hebbe");      //数据库用户名   
    db.setPassword("123456");      //数据库密码   
    //db.open();          //打开数据库连接  调用 open() 方法打开数据库物理连接。在打开连接之前，连接不可用
    //db.close();         //释放数据库连接
    // create connection
    if (!db.open())
    {
        qDebug() << "Sqlite open fail" << endl;
        return false;
    }
    return true;
}

 

2、创建表

 

/**
 * @brief createTable
 * 创建表
 * @return
 */
bool createTable()
{
    QSqlQuery query(db);
    QString cmd,cmd2;
    quint16 num;
    QString info, msg, info_other, msg_other;
    QStringList list;
    cmd = QString("CREATE TABLE IF NOT EXISTS tb_Error ("
                  "ErrorNo INTEGER  NOT NULL PRIMARY KEY,"
                  "ErrorDetaInfo VARCHAR(255) NULL,"
                  "ErrorDetaInfo_En VARCHAR(255) NULL,"
                  "ErrorMesages VARCHAR(255) NULL,"
                  "ErrorMesages_En VARCHAR(255) NULL);"
                  );
    if(!query.exec(cmd))
    {
        qDebug()<<"init tb_error errot"<<query.lastError();
    }

    if(query.exec("select count(1) from tb_Error"))
    {
        if(query.next())
        {
            if(query.value(0).toInt() == 0)
            {
                // 读取错误信息插入数据库
                QFile file("./error.ini");
                if(!file.open(QIODevice::ReadOnly))
                {

                }
                QTextStream stream(&file);
                QString line;
                do
                {
                    list.clear();
                    line = stream.readLine();
                    list = line.split("|");
                    if(list.count() == 5)
                    {
                        num = list.at(0).toUInt();
                        info = list.at(1);
                        msg = list.at(2);
                        info_other = list.at(3);
                        msg_other = list.at(4);

                        qDebug()<<"num = "<<num<<"info = "<<info<<"msg = "<<msg;
                        cmd2 = QString("INSERT INTO tb_Error(ErrorNo,ErrorDetaInfo,ErrorMesages,ErrorDetaInfo_En,ErrorMesages_En)VALUES(%1, \"%2\", \"%3\", \"%4\", \"%5\")")
                                .arg(num).arg(info).arg(msg).arg(info_other).arg(msg_other);
                        if(!query.exec(cmd2))
                        {
                            qDebug()<<"num = "<<num<<"info = "<<info<<"msg = "<<msg;
                            qDebug()<<"initError = "<<query.lastError();
//                            return false;
                        }
                    }
//                    qDebug()<<"inser to database msg"<<line;
                }
                while(!line.isNull());
                file.close();
            }
        }
    }
    return true;
}

 

3、获取列

/**
 * @brief getColumn
 * 获取列
 * @return
 */
int getColumn(const QString &tableName，QString item, QString itemStr)
{
      QSqlQuery query(db);
      QString sql;
      sql = QString("select * from %1 WHERE %2 = '%3' ").arg(tableName).arg(item).arg(itemStr);
      query.prepare(sql);
      if(query.exec(sql))
      {
          QSqlRecord sqlRecord = query.record();
          return sqlRecord.count();
      }
      else
      {
          qDebug()<<"get data failure:"<<query.lastError()<<"\n"<<sql;
      }
      return 0;
}

 

4、获取行

 

/**
 * @brief getRow
 * 获取列
 * @return
 */
int getRow(const QString &tableName，QString item, QString itemStr)
{
      QSqlQuery query(db);
      QString sql;
      sql = QString("select COUNT() from %1 ").arg(tableName);
      query.prepare(sql);
      if(query.exec(sql))
      {
          return query.value(0).toInt() ;
      }
      else
      {
          qDebug()<<"get data failure:"<<query.lastError()<<"\n"<<sql;
      }
      return 0;
}

 

5、获取最大值

 

/**
 * @brief getMaxDateTime
 * 获取列
 * @return
 */
QDateTime getMaxDateTime(const QString &tableName)
{
      QSqlQuery query(db);
      QString sql;
      sql = QString("select MAX(DateTime) from %1 ").arg(tableName);
      query.prepare(sql);
      if(query.exec(sql))
      {
          return query.value(0).toDateTime();
      }
      else
      {
          qDebug()<<"get data failure:"<<query.lastError()<<"\n"<<sql;
          return QDateTime::currentDateTime(); 
      }
}

 

6、获取最小值

 

/**
 * @brief getMinDateTime
 * 获取列
 * @return
 */
QDateTime getMinDateTime(const QString &tableName)
{
      QSqlQuery query(db);
      QString sql;
      sql = QString("select MIN(DateTime) from %1 ").arg(tableName);
      query.prepare(sql);
      if(query.exec(sql))
      {
          return query.value(0).toDateTime();
      }
      else
      {
          qDebug()<<"get data failure:"<<query.lastError()<<"\n"<<sql;
          return QDateTime::currentDateTime(); 
      }
}

 

7、查询所有记录

 

/**
 * @brief selectAllData
 * 获取所有数据
 * @return
 */
void selectAllData(const QString &tableName)
{
    QSqlQuery query = QSqlQuery(db);   
    QString sql;
    sql = QString("select * from %1 ").arg(tableName);
    query.prepare(sql);
    query.exec();
     while (query.next()) //依次取出查询结果的每一条记录，直至结束
     {
         qDebug()<<query.value(0).toInt();
         qDebug()<<query.value(1).toString();   
     }  
}

 

8、获取数据并分页显示到QTableView

 

int total;              //总记录数
int pageSize;           //每页显示条数
int totalPage;          //总页数
int startPage;          //起始页
QSqlQueryModel  *sqlQueryModel;
#define PAGE_SIZE       50

void init()
{
    sqlQueryModel = new QSqlQueryModel(this);
    QSqlQuery query(db);
    ui->tableView->setModel(sqlQueryModel);
    pageSize = PAGE_SIZE;
    query.exec(QString("SELECT COUNT() FROM tb_Diagnose"));
    while(query.next())
    {
        total = query.value(0).toInt();
    }
    if(total <= PAGE_SIZE)
        totalPage = 1;
    else
        totalPage = (total%pageSize == 0)? (total/pageSize):(total/pageSize+1);
    if(startPage > totalPage)
    {
        startPage = totalPage;
    }
}

/**
 * @brief selectAndShowData
 * 从数据库获取数据，并在QTableView显示
 *
 * @return
 */
void selectAndShowData()
{
    QSqlQuery query(db);
    QString cmd;    
    if(startPage < totalPage) {
        cmd = QString("SELECT * FROM ( SELECT * FROM table limit %3, %4 ) a  ORDER BY DateTime DESC ")
                .arg(total-(startPage*pageSize)).arg(pageSize);
    } else if(startPage == totalPage) {
        cmd = QString("SELECT * FROM ( SELECT * FROM table limit 0, %3 ) a ORDER BY DateTime DESC ")
                .arg(total-(startPage-1)*pageSize);
    }
    if(!query.exec(cmd))
    {
        qDebug()<<query.lastError();
    }
    sqlQueryModel->setQuery(query);
    while(sqlQueryModel->canFetchMore())
        sqlQueryModel->fetchMore();

    ui->labelTitle->setText(tr("Log"));
    ui->labelFooter->setText(tr("Page: %1/%2").arg(startPage).arg(totalPage)); 
    sqlQueryModel->setHeaderData(0, Qt::Horizontal, tr("No."));
    sqlQueryModel->setHeaderData(1, Qt::Horizontal, tr("ID"));
    sqlQueryModel->setHeaderData(2, Qt::Horizontal, tr("Name"));
    sqlQueryModel->setHeaderData(3, Qt::Horizontal, tr("Date/time"));
    sqlQueryModel->setHeaderData(4, Qt::Horizontal, tr("Details"));
    ui->tableView->setColumnWidth(0, 55);
    ui->tableView->setColumnWidth(1, 150);
    ui->tableView->setColumnWidth(2, 100);
    ui->tableView->setColumnWidth(3, 255);
    ui->tableView->setColumnWidth(4, ui->tableView->width()-55-100-150-255-ui->tableView->verticalScrollBar()->width());

}

 

9、插入数据

 

/**
 * @brief insertData
 * 插入数据
 * @return
 */
bool insertData(QSqlQuery &query, quint64 rcNum, QString operatorId, QString describeStr)
{
    QString cmd;
    cmd = QString("INSERT INTO table(RcNum, DateTime, OperatorID, Operation) VALUES(%1, datetime('now'), '%2', \"%3\") ")
            .arg(rcNum)
            .arg(operatorId)
            .arg(describeStr);
    if(!query.exec(cmd))
    {
        qDebug()<<"INSERT table Error:"<<query.lastError()<<endl<<cmd;
        return false;
    }
    else
        return true;
}

 

10、更新数据

/**
 * @brief updateData
 * 更新数据
 * @return
 */
bool updateData(QSqlQuery &query, quint64 rcNum, QString describeStr)
{
    QString cmd;
    cmd = QString("update table set Operation = '%1' where RcNum = %2")
            .arg(describeStr)
            .arg(rcNum);
    if(!query.exec(cmd))
    {
        qDebug()<<"update table Error:"<<query.lastError()<<endl<<cmd;
        return false;
    }
    else
        return true;
}

 

11、删除

 

/**
 * @brief deleteData
 * 删除数据
 * @return
 */
bool deleteData(QSqlQuery &query, quint64 rcNum)
{
    QString cmd;
    cmd = QString("delete from table where  where RcNum = %1")
            .arg(rcNum);
    if(!query.exec(cmd))
    {
        qDebug()<<"delete table Error:"<<query.lastError()<<endl<<cmd;
        return false;
    }
    else
        return true;
}


/**
 * @brief deleteAllData
 * 删除所有数据
 * @return
 */
bool deleteAllData(QSqlQuery &query)
{
    QString cmd;
    cmd = QString("delete from table");
    if(!query.exec(cmd))
    {
        qDebug()<<"delete table Error:"<<query.lastError()<<endl<<cmd;
        return false;
    }
    else
        return true;
}