方法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;
}