SQLite数据库使用总结(上)

Sqlite 前端之家
前端之家收集整理的这篇文章主要介绍了SQLite数据库使用总结(上)，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

基于sqlite3类的简单封装，使用方法如下：

@interface CsqliteDB : NSObject
{
sqlite3 *m_pDB;
NSString *m_databasePath;
}

@property (nonatomic,retain) NSString *m_databasePath;
-(CsqliteDB *)initWithFilePathOpen:(NSString*)filePath;

-(CsqliteDB *)initWithFilePath:(NSString*)filePath;

// 打开数据库
-(int)open;

// 执行语句
-(int)execsql:(const char*)sql :(char**)errorMsg;\

-(int)execsqlForResult:(const char*)sql :(sqlite3_stmt **)statement;

-(int)execScalar:(const char*)sql ;

-(BOOL)isTableExists:(const char*) strTableName;

-(BOOL)isDatabaSEOpen;
- (BOOL)goodConnection;

-(BOOL)beginTransaction;

-(BOOL)commitTransaction;

-(BOOL)rollbackTransaction;

-(int) getLastRowID;

-(int) getLastErrorCode;

-(void) close;

- (void)dealloc;
@end


#import "CsqliteDB.h"


@implementation CsqliteDB
@synthesize m_databasePath;
- (id) init
{
self = [super init];
if (self != nil) {
m_databasePath = nil;
m_pDB = nil;
}
return self;
}


/********************************************************************
函数描述 : initWithFilePathOpen
*********************************************************************/
-(CsqliteDB *)initWithFilePathOpen:(NSString*)filePath
{
if (self = [super init]) {

self.m_databasePath = [[NSString alloc] initWithString:filePath];
// m_databasePath = filePath;
if(sqlite3_open([filePath UTF8String],&m_pDB) != sqlITE_OK)
{

printf("Can't open database: %s\n",sqlite3_errmsg(m_pDB));
// sqlite3_close(m_pDB);
return self;
}
}
return self;
}

/********************************************************************
函数描述 : initWithFilePath
*********************************************************************/
-(CsqliteDB *)initWithFilePath:(NSString*)filePath
{

if (self = [super init]) {

//m_databasePath = filePath;
m_databasePath = [[NSString alloc] initWithString:filePath];
}

return self;
}

/********************************************************************

*********************************************************************/
-(int)open
{
int ret = sqlite3_open([m_databasePath UTF8String],&m_pDB) ;
if(ret!= sqlITE_OK)
{

printf("Can't open database: %s\n",sqlite3_errmsg(m_pDB));

}
return ret;

}

/********************************************************************

*********************************************************************/
-(BOOL)isDatabaSEOpen
{
if(m_pDB)
return YES;
else
return NO;
}

-(BOOL)goodConnection {

if (nil == m_pDB) {
return NO;
}

sqlite3_stmt *statement;
char *sqlSelect = sqlite3_mprintf("select name from sqlite_master where type='table'");
[self execsqlForResult:sqlSelect :&statement];
sqlite3_free(sqlSelect);

if (statement) {
sqlite3_finalize(statement);
return YES;
}
return NO;
}

/********************************************************************

*********************************************************************/
-(int)execsql:(const char*)sql :(char**)errorMsg
{

int result = sqlite3_exec(m_pDB,sql,NULL,&(*errorMsg)) ;
if(sqlITE_OK != result)
{
printf("sql error: %s\n",sqlite3_errmsg(m_pDB));
// sqlite3_close(m_pDB);
}

return result;
}

/********************************************************************
*********************************************************************/
-(int)execsqlForResult:(const char*)sql :(sqlite3_stmt **)statement
{
int result = sqlite3_prepare_v2(m_pDB,-1,&(*statement),nil);
if(sqlITE_OK != result)
{
printf("sql error: %s\n",sqlite3_errmsg(m_pDB));
}
/*
sqlite3_stmt *statement;
int result = sqlite3_prepare(database,&statement,nil);
*/
return result;
}

/********************************************************************
execScalar

*********************************************************************/
-(int)execScalar:(const char*)sql
{

int ret = -1;
sqlite3_stmt *statement;
if(sqlITE_OK == sqlite3_prepare_v2(m_pDB,nil))
{
if(sqlite3_step(statement) == sqlITE_ROW)
{
ret = sqlite3_column_int(statement,0);

}
}
sqlite3_finalize(statement);

return ret;
}

/********************************************************************
isTableExists
*********************************************************************/
-(BOOL)isTableExists:(const char*) strTableName
{
char *pszsql = NULL;
char ** pszResult = NULL;
int iRow = 0;
int iCol = 0;

if (NULL == m_pDB)
{
return FALSE;
}

pszsql = sqlite3_mprintf("SELECT name FROM sqlite_master WHERE type='table' and name = '%q'",strTableName);
sqlite3_get_table(m_pDB,pszsql,&pszResult,&iRow,&iCol,NULL);
sqlite3_free(pszsql);
sqlite3_free_table(pszResult);

if (0 < iRow)
{
return YES;
}

return NO;

}

/********************************************************************
getLastRowID

*********************************************************************/
-(int) getLastRowID
{
if (nil == m_pDB)
{
return -1;
}

return (int)sqlite3_last_insert_rowid(m_pDB);
}


/********************************************************************
getLastErrorCode
*********************************************************************/
-(int) getLastErrorCode
{

printf("errmsg = %s\n",sqlite3_errmsg(m_pDB));
int errcode = sqlite3_errcode(m_pDB);
printf("errcode:%d",errcode);
return errcode;
}

/********************************************************************
beginTransaction
*********************************************************************/
-(BOOL)beginTransaction
{
// sqlite3_busy_timeout( m_pDB,500);
int result = [self execsql:"BEGIN exclusive TRANSACTION;" :nil];
if(sqlITE_OK == result)
return YES;
else
return NO;
}

/********************************************************************
commitTransaction
*********************************************************************/
-(BOOL)commitTransaction
{
int result = [self execsql:"commit;" :nil];
if(sqlITE_OK == result)
return YES;
else
return NO;
}

/********************************************************************
: rollbackTransaction
*********************************************************************/
-(BOOL)rollbackTransaction
{
int result = [self execsql:"ROLLBACK;" :nil];
if(sqlITE_OK == result)
return YES;
else
return NO;
}

/********************************************************************
: close
*********************************************************************/
-(void) close
{
if(m_pDB)
{
sqlite3_close(m_pDB);
m_pDB = 0;
}
}

/********************************************************************
: dealloc
*********************************************************************/
- (void)dealloc
{
if(m_pDB)
{
sqlite3_close(m_pDB);
m_pDB = 0;
}
[m_databasePath release];
[super dealloc];
}
@end

1.数据库的初始化

//链接db,若数据库文件不存在，则从安装包中搜索拷贝，若安装包中也不存在，则自行创建db文件并创建表。

#define DATABASE_FILE_NAME procName

#define DATABASE_RESOURCE_TYPE db

CsqliteDB* sqlControl = [[CsqliteDB alloc]initWithFilePath:dbFilePath];

if (![[NSFileManager defaultManager]fileExistsAtPath:dbFilePath]) {
NSString *backDbPath = [[NSBundle mainBundle]pathForResource:DATABASE_FILE_NAME
ofType:DATABASE_RESOURCE_TYPE];
if (nil == backDbPath) {
return NO;
}
else {
BOOL copiedBackupDB = [[NSFileManager defaultManager]copyItemAtPath:backDbPath
toPath:dbFilePath
error:nil];
if (!copiedBackupDB) {
return NO;
}
}

}

if (![[NSFileManager defaultManager]fileExistsAtPath:dbFilePath]) {
[sqlControl_ open];
[self createAllTables];
return YES;
}
[sqlControl_ open];
2.创建表

if ([sqlControl_ goodConnection]) {

if (![sqlControl_ isTableExists:"Tbl_User"]) {
const char* strCreateT = "create table if not exists Tbl_User \
(ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\
USERNAME VARCHAR(255) NOT NULL,\
USEREMAIL varchar(255),\
UserType char(1) NOT NULL,\
NickName varchar(255),\
FirstName varchar(255),\
LastName varchar(255),\
Birthday DATETIME,\
mobilenum1 varchar(32),\
mobilenum2 varchar(32),\
JobType char(1),\
UpdateTime DATETIME)";
// NSLog(@"the sql is %s",strCreateT);
char *msg;
iRet = [sqlControl_ execsql:strCreateT :&msg];
}

}
3.查询

//返回是否有记录

BOOL bRet = NO;

char *sqlSelect = sqlite3_mprintf("select count(*) from T_iBaby_BaseUser where Upper(userName) = Upper('%q')",
[ReplaceNULL2Empty(userName) UTF8String]);
bRet = [sqlControl_ execScalar:sqlSelect]>0?YES:NO;
sqlite3_free(sqlSelect);

return bRet;

//查询数据集示例

#define DelimSpace(str) ((NULL == (str)) ? "" : (str))

UserLoginInfo* lastLoginInfo = [[[UserLoginInfo alloc]init]autorelease];
if ([sqlControl_ goodConnection]) {
sqlite3_stmt *statement;
char *sqlSelect = sqlite3_mprintf("select t1.UserName,t2.Id,t1.UserType,t1.passWord,t1.isRememberPWD,t1.isAutoLogin,t1.iModel from T_iBaby_LoginCfg t1,T_iBaby_User t2 where Upper(t1.UserName) = Upper(t2.UserName) and Upper(t1.UserType) = Upper(t2.UserType)");
//NSLog(@"the sql is %s",sqlSelect);
int result = [sqlControl_ execsqlForResult:sqlSelect :&statement];

sqlite3_free(sqlSelect);

if (sqlITE_OK == result)
{
while (sqlite3_step(statement) == sqlITE_ROW)
{
lastLoginInfo.userName = [NSString stringWithUTF8String: DelimSpace((const char*)sqlite3_column_text(statement,LastUserInfoColumnUserName))];
lastLoginInfo.userId = [NSString stringWithUTF8String: DelimSpace((const char*)sqlite3_column_text(statement,LastUserInfoColumnUserId))];
lastLoginInfo.UserType = sqlite3_column_int(statement,LastUserInfoColumnUserType);
lastLoginInfo.passWord = [NSString stringWithUTF8String:DelimSpace((const char*)sqlite3_column_text(statement,LastUserInfoColumnPassWord))];
lastLoginInfo.isRemember = sqlite3_column_int(statement,LastUserInfoColumnisRemember);
lastLoginInfo.isAutoLogin = sqlite3_column_int(statement,LastUserInfoColumnisAutoLogin);
lastLoginInfo.iModel = sqlite3_column_int(statement,LastUserInfoColumniModel);
}
}
sqlite3_finalize(statement);
}
return lastLoginInfo;

//修改数据

int iRet = -1;
if([sqlControl_ goodConnection])
{
if([sqlControl_ isTableExists:"T_iBaby_Notify"])
{
char* strUpdate = sqlite3_mprintf("update T_iBaby_Notify set ISREAD = 0 where ID = '%d' and ISREAD = 1",notifyId);
iRet = [sqlControl_ execsql:strUpdate :nil];
sqlite3_free(strUpdate);
}
}
return iRet;

//新增数据注意:insert or replace必须带主键或索引，用事务能成倍的提高效率

int iRet = -1; if([sqlControl_ goodConnection]) { if (![sqlControl_ isTableExists:"T_iBaby_Group"]) { return -1; } NSArray *baseInfoTable = groupInfoRst.cellGroupInfoRst.tableBody; [sqlControl_ beginTransaction]; for(id tableRecord in baseInfoTable) { GroupTable* GroupTableRecord = (GroupTable*) tableRecord; if(sqlTypeInsert == GroupTableRecord.sqlControl) { char *sqlInsert = sqlite3_mprintf("insert or replace into T_iBaby_Group(OwnerID,GID,uid,name,content,members,coverimage,createDate) values('%d','%d','%q','%q')",GroupTableRecord.ownerId,GroupTableRecord.groupId,[ReplaceNULL2Empty(GroupTableRecord.name) UTF8String],[ReplaceNULL2Empty(GroupTableRecord.content) UTF8String],GroupTableRecord.members,[ReplaceNULL2Empty(GroupTableRecord.coverImage) UTF8String],[ReplaceNULL2Empty(GroupTableRecord.createDate) UTF8String]); NSLog(@"%s",sqlInsert); [sqlControl_ execsql:sqlInsert :nil]; sqlite3_free(sqlInsert); } else if(sqlTypeUpdate == GroupTableRecord.sqlControl) { char *sqlInsert = sqlite3_mprintf("replace into T_iBaby_Group(ownerid,gid,createDate) values('%q',[GETUSERID UTF8String],[ReplaceNULL2Empty(GroupTableRecord.createDate) UTF8String] ); [sqlControl_ execsql:sqlInsert :nil]; sqlite3_free(sqlInsert); } else if(sqlTypeDelete == GroupTableRecord.sqlControl) { char *sqlDelete = sqlite3_mprintf("delete from T_iBaby_Group where gid = '%d' and ownerid = '%q'",[GETUSERID UTF8String]); [sqlControl_ execsql:sqlDelete :nil]; sqlite3_free(sqlDelete); } } COMMITTRANSACTION(sqlControl_,iRet); } else { iRet = [sqlControl_ getLastErrorCode]; } return iRet;

原文链接：https://www.f2er.com/sqlite/201914.html

猜你在找的Sqlite相关文章

SQLite学习笔记
安装 在Windows上安装SQLite。 访问官网下载下Precompliled Binaries for Windows的两个压...
windows上如何安装Sqlite
一、安装 下载地址：http://www.sqlite.org/download.html 将Precompiled Binaries for Wi...
【Android数据存储】SQLite使用实例(附源码)
实例: 会员信息管理 功能：1.查看数据库 2.清空数据库 3.增加会员 4.删除会员 5.更新会员 ...
SQLite数据库管理
SQLite3常用命令
加密你的SQLite
关于SQLite SQLite是一个轻量的、跨平台的、开源的数据库引擎，它的在读写效率、消耗总量、...
SQLite这么娇小可爱，不多了解点都不行啊
在我眼里，MySQL和Oracle是这样的 而SQLite在是这样的 所以这么萌的数据库，我真的应该多了...
SQLite这么娇小可爱，不多了解点都不行啊
在我眼里，MySQL和Oracle是这样的 而SQLite在是这样的 所以这么萌的数据库，我真的应该多了...
SQLite 这么娇小可爱，不多了解点都不行啊
（点击上方蓝字，快速关注我们) 作者：空之境界 网址：http://supermao.cn/sqlitezhe-yao-...
SQLITE3 使用总结
SQLITE3 使用总结 投稿：hebedich 字体：[ 增加  减小] 类型：转载 时间：2014-08-19 Sql...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问