QT上的Mysql编程

一.单个数据库单个表格

1.建立并打开数据库

"MyBlastRecord.db"为我们要创建的数据库名

2.往数据库里创建表格

recordview为我们创建表格名

recordview(id int primary key, date text, num int, status int, BlastRecord_id int)
括号里是我们表格里内容

3.sql_query.exec("select * from recordview");

访问表格里面的数据

4.sql_query.next()

表格的下一行，直到表格为空

5.数据库关闭。

//建立并打开数据库
    QSqlDatabase database = QSqlDatabase::addDatabase("QSQLITE");
    database.setDatabaseName("MyBlastRecord.db");               //格式  (MyBlastRecord.db)
    if (!database.open())
    {
        qDebug() << "Error: Failed to connect database(MyBlastRecord)." << database.lastError();
    }
    //创建表格
    QSqlQuery sql_query = QSqlQuery(database);
    if(!sql_query.exec("create table recordview(id int primary key, date text, num int, status int, BlastRecord_id int)"))
    {
        qDebug() << "Error: Fail to create table.(updateBlastRecord)"<< sql_query.lastError();
    }
    sql_query.exec("select * from recordview");
    if(sql_query.exec())
    {
        while(sql_query.next())
        {
            Mode_temp=sql_query.value(3).toInt();
            if (sql_query.value(3).toInt() !=1)
            {
                ret++;
            }
        }
    }
    database.close();

 二、单个数据库多个表格

 

#include <QSqlRecord>
#include <QSqlDriver>
#include <QSqlField>

#define CONNECTNAME "mysqlite_con"
#define DATABASENAME "Launcher.db"
#define DATABASEDRIVER "QSQLITE"

DataManage* DataManage::m_pDataManage = Q_NULLPTR;
QMutex  DataManage::m_mutex;

DataManage::DataManage(QObject *parent) : QObject(parent)
{
    if (QSqlDatabase::contains(CONNECTNAME)){
        m_dataBase = QSqlDatabase::database(CONNECTNAME);
    }else{
        m_dataBase = QSqlDatabase::addDatabase(DATABASEDRIVER,CONNECTNAME);
        m_dataBase.setDatabaseName(DATABASENAME);
    }
    if(!m_dataBase.isOpen())
        m_dataBase.open();

    QStringList tableList = m_dataBase.tables();
    QSqlQuery sqlQuery(m_dataBase);

    if(!tableList.contains("systemview")){
        if(!sqlQuery.exec("create table systemview(id int primary key, bdlight int, bklight int, bmtime datetime, longitude varchar(5), "
                          "latitude varchar(5), locattime datetime, style int, keyvoice int, charge int, "
                          "canswitch int, mA1data int, mA2data int, uAdata int, LG00Enable int, orgNum int, "
                          "StatusCheck int, PageStyle int, NeedAuth int, NeedGPS int, AuthNum int, DeviceID1 int, DeviceID2 int, "
                          "DeviceID3 int, DeviceID4 int, DeviceID5 int, SW int, DVStatus int)")){

            qDebug() << "Error: Fail to create table:systemview"<<sqlQuery.lastError();
        }
    }
   if(!tableList.contains("delayview")){

       if(!sqlQuery.exec("create table delayview(id int primary key, code text, time int, area int, hole int, authstatus int)")){
           qDebug() << "Error: Fail to create table:delayview"<<sqlQuery.lastError();
       }
   }
   if(!tableList.contains("recordview")){

       if(!sqlQuery.exec("create table recordview(id int primary key, date text,"
                         " num int, status int, BlastRecord_id int, Longitude text, Latitude text, bscanma int, bscanmv int, "
                         "scaningma int, scaningmv int, bchargema int, bchargemv int, chargingma int, chargingmv int, chargedma int, "
                         "bchargedmv int, bboomma int, bboomemv int, batenp int)")){
           qDebug() << "Error: Fail to create table:recordview"<<sqlQuery.lastError();
       }
   }
   if(!tableList.contains("recordlistview")){

       if(!sqlQuery.exec("create table recordlistview(id int primary key, code text, time int, blastrecord_id int)")){
           qDebug() << "Error: Fail to create table:recordlistview"<<sqlQuery.lastError();
       }
   }

   if(!tableList.contains("authheadview")){

       if(!sqlQuery.exec("create table authheadview(id int primary key, espdata text, "
                         "authid text, longitude varchar(5), latitude varchar(5),"
                         "radius int, starttime text, stoptime text, lgnumber int,"
                         " usefultime int, lgrang_number int, password_number int,downdata text)")){
           qDebug() << "Error: Fail to create table:authheadview"<<sqlQuery.lastError();
       }
   }
   if(!tableList.contains("authpasswordview")){

       if(!sqlQuery.exec("create table authpasswordview(id int primary key, LGPassword text, authfile_id int)")){
           qDebug() << "Error: Fail to create table:authpasswordview"<<sqlQuery.lastError();
       }
   }
   if(!tableList.contains("userInfo")){

       if(!sqlQuery.exec("create table userInfo(userId int primary key, orgId varchar(32), "
                         "realName nvarchar(32),orgName nvarchar(32),loginName varchar(32),userPasswd varchar(32))")){
           qDebug() << "Error: Fail to create table:userInfo"<<sqlQuery.lastError();
       }
   }

}

 

 访问数据库的表格

void DataManage::setDelayPara(LGMessageDef *LGMsg, int lg_num)
{
    int i = 0, j = 0;
    char *code = new char[24];

    QSqlQuery sqlQuery(m_dataBase);

    sqlQuery.exec("select * from delayview");
    if(sqlQuery.exec())
    {
        while(sqlQuery.next())
        {
            j++;
        }
    }
    for (i = 0; i < lg_num; i++)
    {
        if (0 != ((LGMsg+i)->Det_status & mDesign))
        {
            continue;
        }
        DetidToString((LGMsg+i)->Uid, code);
        QString uid = QString(QLatin1String(code));
        int time = (LGMsg+i)->DT;
        int area = (LGMsg+i)->KW>>10;
        int hole = (LGMsg+i)->KW&0x3FF;
        int status = 1;
        if(((LGMsg+i)->Det_status & mAuthorize)==0)  //获取雷管当前状态
        {
            status=0;
        }
        if(false == m_dataBase.tables().contains(uid))
        {
            QString str=QString("INSERT INTO delayview VALUES(%1, \"%2\", %3, %4, %5, %6)").arg(j+1).arg(uid).arg(time).arg(area).arg(hole).arg(status);
            sqlQuery.exec(str);
            (LGMsg+i)->DesignDT=(LGMsg+i)->DT;
            (LGMsg+i)->DesignKW=(LGMsg+i)->DT;
            (LGMsg+i)->Det_status |=mDesign;
            j++;
        }
    }
    sqlQuery.exec();
    delete code;
    //return true;
}