vs2017+Qt5.9.1环境使用sqlite3cpp

一、准备工作

1. 下载sqlite3cpp，下载连接链接：https://pan.baidu.com/s/17QFYs9pX4wtKVhCHiNYRMA
   提取码：3516
2. vs2017拓展SQlite
   

二、使用方法

1. 将sqlite3pp.h和sqlite3pp.ipp加入工程
2. 创建一个自己的sqlite继承sqlite3pp.h

点击查看代码

#pragma once
#include "sqlite3pp/sqlite3pp.h"
#include <string>	
#include <qmap.h>
using std::string;

class AlSqlite3
{
public:
	AlSqlite3();
	~AlSqlite3();

public:
	// 创建表
	void CreaterTable(string fileName, string tableName);

	// 插入数据
	void InSert2BDFile(string fileName, string tableName, string key, string value);

	// 查询数据
	void GetFromBDFile(string fileName, string tableName, string key, QMap<QString, QString>& map);

public:
	string fileName;
	string tableName_operator;
	string tableName_csv;
	string tableName_test;
	string key;
	string value;
};


#include "AlSqlite3.h"

AlSqlite3::AlSqlite3()
{
	fileName = "data.bd";
	tableName_operator = "operator";
	CreaterTable(fileName, tableName_operator);
	tableName_csv = "csv";
	CreaterTable(fileName, tableName_csv);

	tableName_csv = "csv";
	CreaterTable(fileName, tableName_csv);
}

AlSqlite3::~AlSqlite3()
{
}

void AlSqlite3::CreaterTable(string fileName, string tableName)
{
	sqlite3 *db = NULL;
	const char *sql_tablName = fileName.c_str();
	if (sqlite3_open(sql_tablName, &db) == SQLITE_OK)
	{
		string createtable = "CREATE TABLE "+ tableName +"(key TEXT,value TEXT)";
		const char *sql_createtable = createtable.c_str();
		sqlite3_stmt *stmt = NULL;
		if (sqlite3_prepare_v2(db, sql_createtable, -1, &stmt, NULL) == SQLITE_OK)
		{

			if (sqlite3_step(stmt) == SQLITE_DONE) 
			{

				
				
			}
			sqlite3_reset(stmt);
		}
		sqlite3_finalize(stmt);
	}
	sqlite3_close(db);
}

void AlSqlite3::InSert2BDFile(string fileName, string tableName, string key, string value)
{
	sqlite3 *db = NULL;
	if (sqlite3_open(fileName.c_str(), &db) == SQLITE_OK)
	{

		string insert = "INSERT INTO " + tableName + "(key,value) VALUES(?,?)";
		const char *sql_select = insert.c_str();
		sqlite3_stmt *stmt = NULL;

		if (sqlite3_prepare_v2(db, sql_select, -1, &stmt, NULL) == SQLITE_OK)
		{

			sqlite3_bind_text(stmt, 1, key.c_str(), -1, SQLITE_TRANSIENT);
			sqlite3_bind_text(stmt, 2, value.c_str(), -1, SQLITE_TRANSIENT);

			if (sqlite3_step(stmt) == SQLITE_DONE) 
			{

				//OutputDebugString(L"insert success");
			}
			sqlite3_reset(stmt);
		}
		sqlite3_finalize(stmt);
	}
	sqlite3_close(db);
}

void AlSqlite3::GetFromBDFile(string fileName, string tableName, string key, QMap<QString, QString>& map)
{
	sqlite3 *db = NULL;
	const unsigned char* k;
	const unsigned char* v;
	
	if (sqlite3_open(fileName.c_str(), &db) == SQLITE_OK) {

		string insert = "SELECT * FROM " + tableName + " WHERE KEY LIKE " + "'" + key + "%'";
		const char *sql_select = insert.c_str();
		sqlite3_stmt *stmt = NULL;

		if (sqlite3_prepare_v2(db, sql_select, -1, &stmt, NULL) == SQLITE_OK)
		{
		
			while (sqlite3_step(stmt) == SQLITE_ROW)
			{
				k = sqlite3_column_text(stmt, 0);
				v = sqlite3_column_text(stmt, 1);
				QString qstr_key = QString::fromUtf8(reinterpret_cast<const char *>(k));
				QString qstr_value = QString::fromUtf8(reinterpret_cast<const char *>(v));
				map.insert(qstr_key, qstr_value);
			} 
		
			//sqlite3_reset(stmt);
		}
		sqlite3_finalize(stmt);
	}
	sqlite3_close(db);
}