sqlite的几种访问方法

方法1：直接执行SQL语句

	sqlite3* db = Open(_T("./test.db3"), FALSE);
	if (db != NULL)
	{
		ExecuteSQL(db, _T("CREATE TABLE Template(TID INTEGER, TName TEXT(32), TContent TEXT(1024), PRIMARY KEY(TID ASC));"));

		for (int i = 0; i < TEST_COUNT; i++)
		{
			CString s;
			s.Format(_T("INSERT INTO Template(TName, TContent) VALUES('%d', '%d');"), i, i);
			ExecuteSQL(db, s);
		}

		Close(db);
	}

 

方法2：批处理

	sqlite3* db = Open(_T("./test.db3"), FALSE);
	if (db != NULL)
	{
		ExecuteSQL(db, _T("BEGIN;"));

		ExecuteSQL(db, _T("CREATE TABLE Template(TID INTEGER, TName TEXT(32), TContent TEXT(1024), PRIMARY KEY(TID ASC));"));

		for (int i = 0; i < TEST_COUNT; i++)
		{
			CString s;
			s.Format(_T("INSERT INTO Template(TName, TContent) VALUES('%d', '%d');"), i, i);
			ExecuteSQL(db, s);
		}

		ExecuteSQL(db, _T("COMMIT;"));

		Close(db);
	}

 

方法3：数据绑定

	sqlite3* db = Open(_T("./test.db3"), FALSE);
	if (db != NULL)
	{
		ExecuteSQL(db, _T("CREATE TABLE Template(TID INTEGER, TName TEXT(32), TContent TEXT(1024), PRIMARY KEY(TID ASC));"));

		TCHAR szSQL[128];
		_stprintf(szSQL, _T("INSERT INTO Template(TName, TContent) VALUES(?, ?);"));
		sqlite3_stmt *stmt = NULL;
		const char *pzTail = NULL;
		int utf8Len = 0;
		char *utf8 = (char*)enc_unicode_to_utf8((const unsigned short*)szSQL, _tcslen(szSQL), &utf8Len);
		int nRes = sqlite3_prepare_v2(db, utf8, utf8Len, &stmt, &pzTail);
		delete []utf8;
		if (SQLITE_OK != nRes)
		{
			return;
		}

		for (int i = 0; i < TEST_COUNT; i++)
		{
			sqlite3_reset(stmt);

			sqlite3_bind_int(stmt, 1, i);
			sqlite3_bind_int(stmt, 2, i);

			sqlite3_step(stmt);
		}
		Close(db);
	}

 

方法4：批处理与数据绑定结合

 

	sqlite3* db = Open(_T("./test.db3"), FALSE);
	if (db != NULL)
	{
 		ExecuteSQL(db, _T("BEGIN;"));

		ExecuteSQL(db, _T("CREATE TABLE Template(TID INTEGER, TName TEXT(32), TContent TEXT(1024), PRIMARY KEY(TID ASC));"));

		TCHAR szSQL[128];
		_stprintf(szSQL, _T("INSERT INTO Template(TName, TContent) VALUES(?, ?);"));
		sqlite3_stmt *stmt = NULL;
		const char *pzTail = NULL;
		int utf8Len = 0;
		char *utf8 = (char*)enc_unicode_to_utf8((const unsigned short*)szSQL, _tcslen(szSQL), &utf8Len);
		int nRes = sqlite3_prepare_v2(db.GetObject(), utf8, utf8Len, &stmt, &pzTail);

		for (int i = 0; i < TEST_COUNT; i++)
		{
			sqlite3_reset(stmt);

			sqlite3_bind_int(stmt, 1, i);
			sqlite3_bind_int(stmt, 2, i);

			sqlite3_step(stmt);
		}
		delete []utf8;
		if (SQLITE_OK != nRes)
		{
			return;
		}
		sqlite3_finalize(stmt);

		ExecuteSQL(db, _T("COMMIT;"));


		Close(db);
	}

 

经过测试，给这几种方法的效率排序如下：方法4>方法2>方法3>方法1

测试用的公共代码

sqlite3* Open(LPCTSTR szFile, BOOL bReadOnly /* = FALSE */)
{
	int nStrlen = _tcslen(szFile);
	if (0 == nStrlen)
	{
		return FALSE;
	}

	sqlite3* db = NULL;
	char *utf8 = (char*)enc_unicode_to_utf8((const unsigned short*)szFile, nStrlen);
	int nRes = sqlite3_open_v2(utf8, &db, bReadOnly ? SQLITE_OPEN_READONLY : (SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE), NULL);
	delete []utf8;
	if (nRes != SQLITE_OK)
	{
		return NULL;
	}
	return ;
}

void Close(sqlite3* db)
{
	if (db != NULL)
	{
		sqlite3_close(db);
	}
}

BOOL ExecuteSQL(sqlite3* db, LPCTSTR szSQL)
{
	sqlite3_stmt *stmt = NULL;
	const char *pzTail = NULL;
	int utf8Len = 0;
	char *utf8 = (char*)enc_unicode_to_utf8((const unsigned short*)szSQL, _tcslen(szSQL), &utf8Len);
	int nRes = sqlite3_prepare_v2(db, utf8, utf8Len, &stmt, &pzTail);
	delete []utf8;
	if (SQLITE_OK != nRes)
	{
		return FALSE;
	}

	nRes = sqlite3_step(stmt);
	for (int i = 0; i < 10; i++)
	{
		if (SQLITE_BUSY == nRes)
		{
			Sleep(1000);
			continue;
		}
		else
		{
			break;
		}
	}
	sqlite3_finalize(stmt);

	if (SQLITE_DONE != nRes)
	{
		return FALSE;
	}
	return TRUE;
}