前端之家收集整理的这篇文章主要介绍了
sqlite3 批量插入数据库总结,
前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
#ifndef _DATABASEWRITER_H_
#define _DATABASEWRITER_H_
#include "sqlite3.h"
#include <string>
#include <vector>
#define __is_exists(filename) (_access(filename,0) != -1)
struct userdata_t
{
int type; /// integer,string
int length;
void* value;
};
struct database_init_t
{
std::string _sql_ctb;
std::string _sql_ctbi;
std::string _sql_insrt;
};
// 特殊数据库,所有表相同,对象状态不轻易发生变化
class DatabaseWriter
{
public:
DatabaseWriter(void);
~DatabaseWriter(void);
/**
* returns: 0:succeed.
* 1.a new DatabaseWriter file was request to open.
* -1.unsolved error!
*/
int init(const char* filename,const std::vector<database_init_t>& initinfo);
void uninit(void);
int start(void);
int complete(void);
void prepare(const std::vector<database_init_t>& initinfo);
void finalize(void);
void step(int tabloc);
void fillData(int tabloc,int col,const userdata_t& dat);
private:
std::string filename;
sqlite3* db;
sqlite3_stmt** stmts;
unsigned int stmt_count;
};
#endif
#include <io.h>
#include <direct.h>
#include <stdlib.h>
#include "DatabaseWriter.h"
DatabaseWriter::DatabaseWriter(void) : filename(),db(),stmts(),stmt_count()
{
}
DatabaseWriter::~DatabaseWriter(void)
{
}
int DatabaseWriter::init(const char* filename,const std::vector<database_init_t>& initinfo)
{
if(NULL == this->db)
{
if(sqlITE_OK == sqlite3_open(filename,&this->db))
{
this->stmt_count = initinfo.size();
this->stmts = (sqlite3_stmt**)::malloc(sizeof(sqlite3_stmt**) * this->stmt_count);
::memset(this->stmts,0x0,sizeof(sqlite3_stmt**) * this->stmt_count);
if(!__is_exists(filename))
{
for(std::vector<database_init_t>::const_iterator i = initinfo.begin(); i != initinfo.end(); ++i)
{
// create table
sqlite3_exec(this->db,i->_sql_ctb.c_str(),NULL,NULL);
// create index
sqlite3_exec(this->db,i->_sql_ctbi.c_str(),NULL);
}
}
this->filename = filename;
return 0;
}
else { // unsolved error
return -1;
}
}
return filename == this->filename ? 0 : 1;
}
void DatabaseWriter::uninit(void)
{
if(this->db != NULL)
{
::free(this->stmts);
this->stmts = NULL;
sqlite3_close(this->db);
this->db = NULL;
}
}
int DatabaseWriter::start(void)
{
return sqlite3_exec(this->db,"begin;",NULL);
}
int DatabaseWriter::complete(void)
{
return sqlite3_exec(this->db,"commit;",NULL);
}
void DatabaseWriter::prepare(const std::vector<database_init_t>& initinfo)
{
for(size_t i = 0; i < this->stmt_count; ++i)
{
sqlite3_prepare_v2(this->db,initinfo[i]._sql_insrt.c_str(),-1,&this->stmts[i],NULL);
}
}
void DatabaseWriter::finalize(void)
{
for(size_t i = 0; i < this->stmt_count; ++i)
{
sqlite3_finalize(this->stmts[i]);
this->stmts[i] = 0;
}
}
void DatabaseWriter::step(int loc)
{
sqlite3_step(this->stmts[loc]);
sqlite3_reset(this->stmts[loc]);
}
void DatabaseWriter::fillData(int loc,const userdata_t& dat)
{
if(dat.type == 0)
{
sqlite3_bind_int64(this->stmts[loc],col,*( (__int64*)dat.value ));
}
else {
sqlite3_bind_blob(this->stmts[loc],dat.value,dat.length,NULL);
}
}
#include <iostream>
#include <exception>
#include <nsconv>
#include "DatabaseWriter.h"
#pragma comment(lib,"c:\\sqlite\\sqlite3.lib")
/**
* 一条数据插入时:
* 1.打开数据库:
* (1)数据库文件不存在,则打开,并创建表
* (2)数据库文件不存在,不创建表
* (3)数据库句柄不为空,直接返回
* 2.数据插入做准备:
*
*/
int main(int,char**)
{
DatabaseWriter writer;
database_init_t initinfo;
std::vector<database_init_t> initinfoList;
for(int i = 0; i < 10; ++i)
{
initinfo._sql_ctb = "create table tb_" + std::nsc::to_string<char>(i) + "(account BIGINT)";
initinfo._sql_insrt = "insert into tb_" + std::nsc::to_string<char>(i) + " values(?)";
initinfoList.push_back(initinfo);
}
int stat = writer.init("test.db",initinfoList);
userdata_t dat;
dat.type = 0;
dat.length = 8;
/// 一次批量插入
writer.prepare(initinfoList);
writer.start();
for(int i = 0; i < 20; ++i)
{
dat.value = new __int64(i);
writer.fillData(i % 10,1,dat);
writer.step( i % 10 );
}
stat = writer.complete();
writer.finalize();
writer.uninit();
return 0;
}