sqlite3使用详解（Qt版本）

初始化sqlite3 (创建表)

   QString url = QDir::currentPath() + QString::fromLocal8Bit("/Msg.db");
    bool isExsiting = false;
    QFileInfo file(url);
    if (!file.absoluteDir().exists())
    {
        QDir dir;
        dir.mkpath(file.path());
    }
    if (file.exists())
        isExsiting = true;
    if(SQLITE_OK != sqlite3_open(url.toStdString().c_str(), &m_conn))
    {
        sqlite3_close(m_conn);
    }
    if (!isExsiting)
    {
        char *zErrMsg= 0;

        char *sql= "CREATE TABLE [netmsg](\n [currentuser] [VARCHAR(50)],\n [msgType] int,\n [peername] [VARCHAR(40)],\n [peerNickname] [VARCHAR(40)],\n [msg] TEXT,\n [other_info] TEXT, [group_name]  [VARCHAR(40)],\n [send_timestamp] TEXT, \n [bExtract] int);";
        sqlite3_exec(m_conn, sql, 0, 0, &zErrMsg);
    }

插入数据：

        sprintf_s(sql, 1024, "INSERT INTO [netmsg] VALUES('%s','%d','%s','%s','%s','%s','%s','%s','%d');",
                  m_user_name.c_str(),msg.msgType,msg.peername.c_str(),msg.peerNickname.c_str(),
                  msg.msg.c_str(),msg.other_info.c_str(),msg.group_name.c_str(),msg.send_timestamp.c_str(),flag);
        m_rc = sqlite3_prepare_v2(m_conn, sql, strlen(sql), &m_stmt, 0);
        if(m_rc != SQLITE_OK)
        {
            return;
        }

        sqlite3_step(m_stmt);
        sqlite3_finalize(m_stmt);

 

读取数据：

char    sql[1024];
    sprintf_s(sql, 1024,
              "SELECT * FROM [netmsg] WHERE [peername] == '%s' and [msgType] == '%d';"
              ,peerName.c_str(),PUSH_USER_CHAT_MESSAGE_);

    m_rc = sqlite3_prepare_v2(m_conn, sql, strlen(sql), &m_stmt, 0);
    if(m_rc)
    {
        return;
    }

    while(sqlite3_step(m_stmt) == SQLITE_ROW)
    {
        ComingMsg  msg;

        msg.msgType = sqlite3_column_int(m_stmt, 1);
        msg.peername = (char*)sqlite3_column_text(m_stmt, 2);
        msg.peerNickname = (char*)sqlite3_column_text(m_stmt, 3);
        msg.msg = (char*)sqlite3_column_text(m_stmt, 4);
        msg.other_info = (char*)sqlite3_column_text(m_stmt, 5);
        msg.send_timestamp = (char*)sqlite3_column_text(m_stmt, 7);

        if ( sqlite3_column_int(m_stmt, 8) == 0)
        {
            msg.bExtract = false;
        }
        else
        {
            msg.bExtract = true;
        }
        msgVec.push_back(msg);
    }

    sqlite3_finalize(m_stmt);

 

删除数据：

        sprintf_s(sql, 1024, "DELETE FROM [netmsg] WHERE [peername] == '%s' and [msgType] == '%d';",
                  peerName.c_str(),PUSH_USER_CHAT_MESSAGE_);

        m_rc = sqlite3_prepare_v2(m_conn, sql, strlen(sql), &m_stmt, 0);
        if(m_rc != SQLITE_OK)
        {

            return;
        }

        sqlite3_step(m_stmt);
        sqlite3_finalize(m_stmt);

sqlite3第一次用，简单地把自己的使用过程写一下，以后再用到可以参考。

sqlite3对很多通过的SQL语句都支持，像SELECT,UPDATE,INSERT,DELETE等等都支持地很好，只要懂SQL语句就可以用sqlite3。

By 冬狼无敌  转载请注明出处：http://blog.csdn.net/superzhaifd/article/details/13583507