sqlite3 增删改查 之demo

mysql.h:

#ifndef MYSQL_H
#define MYSQL_H

#include <string>
#include <sqlite3.h>

using namespace std;

class MYSQL
{
public:
    MYSQL();
    void initDatabase();//string filePathupdateData
    void deinitDatabase();
    bool insertData(int id, string name, int data);
    void deleteData(string name);
    void updateData(int id, string name);
    void queryData(string name);

    void readData();

private:
    sqlite3* m_pdb;
};

#endif // MYSQL_H

mysql.cpp

#include "mysql.h"
#include <string.h>
#include <stdio.h>

#define SQLITE_DATABASE     "./DatabaseTBox.db"
#define TABLE_NAME          "TEST_TABLE"

int loadinfo(void* para, int n_column, char** column_value, char** column_name)
{
    for(int i=0; i<n_column; i++)
    {
        printf("column_value: %s\n", column_value[i]);
        printf("column_name : %s\n\n", column_name[i]);
    }

    return 0;
}

MYSQL::MYSQL(){}

void MYSQL::initDatabase()//string filePath
{
    int ret;
    char *pErrMsg;
    char *sql = NULL;
    sqlite3_stmt *stmt;

    if((ret = sqlite3_open(SQLITE_DATABASE, &m_pdb)) != SQLITE_OK)
    {
        fprintf(stderr,"open database error: %s\n", sqlite3_errmsg(m_pdb));
        sqlite3_close(m_pdb);
    }

    //create table
    sql = sqlite3_mprintf("create table %s(id INTEGER primary key, mark INTEGER, name varchar, data INTEGER)", TABLE_NAME);


    if ((ret = sqlite3_exec(m_pdb, sql, NULL, NULL, &pErrMsg)) != SQLITE_OK)
    {
        fprintf(stderr, "create table error: %s\n", pErrMsg);
    }

    sqlite3_free(sql);

    insertData(11, "111", 77);//增
    insertData(22, "222", 88);
    insertData(33, "333", 99);

    deleteData("111");//删
    updateData(22, "***");//改
    queryData("***");//查

     deinitDatabase();
     system("sync");
}

void MYSQL::deinitDatabase()
{
    sqlite3_close(m_pdb);
}

bool MYSQL::insertData(int id, string name, int data)
{
    int ret;
    char* sql;
    char* perror;
    sql = sqlite3_mprintf("insert into %s values(NULL,'%d', '%s', '%d')",
                          TABLE_NAME,
                          id,
                          name.c_str(),
                          data);
    if(ret = sqlite3_exec(m_pdb, sql, NULL, NULL, &perror) != SQLITE_OK)
    {
        printf("insert error: %d\n", ret);
    }

    sqlite3_free(sql);

}

void MYSQL::deleteData(string name)
{
    int ret;
    char* sql;
    char* perror;

    sql = sqlite3_mprintf("delete from %s where name='%s'", TABLE_NAME, name.c_str());
    if((ret = sqlite3_exec(m_pdb, sql, NULL, NULL, &perror)) != SQLITE_OK)
    {
        printf("parse is error: %d\n", ret);
    }

    sqlite3_free(sql);
}

void MYSQL::updateData(int id, string name)
{
    int ret;
    char* sql;
    char* perror;
    sql = sqlite3_mprintf("update %s set name='%s' where mark=%d",
                                    TABLE_NAME,
                                    name.c_str(),
                                    id);
    if(ret = sqlite3_exec(m_pdb, sql, NULL, NULL, &perror) != SQLITE_OK)
    {
        printf("update error: %d\n", ret);
    }

    sqlite3_free(sql);
}

void MYSQL::queryData(string name)
{
    int ret;
    char* sql;
    char* pErrMsg;
    sqlite3_stmt* stmt;

    sql = sqlite3_mprintf("select * from %s where name='%s'", TABLE_NAME, name.c_str());

    if((ret = sqlite3_prepare(m_pdb, sql, strlen(sql), &stmt, 0)) != SQLITE_OK)//****111****
    {
        printf("parse is error: %d\n", ret);
    }

    ret = sqlite3_exec(m_pdb, sql, loadinfo, NULL, &pErrMsg);//****222****

    sqlite3_finalize(stmt);//****333****

    sqlite3_free(sql);

}