Introduction to FMDB and addition, deletion, modification, and query

Instructions for use

Add, delete, modify, query, and create:

FMDatabase

# Import <Foundation/Foundation. h>
# Import "FMDatabaseAdditions. h"

@ Class FMDatabase;

/**
* @ Brief manage data links, including links and close connections
* You can establish a persistent connection.
*/
@ Interface SDBManager: NSObject {
NSString * _ name;
}
/// Database operation object. When a database is created, it will exist
@ Property (nonatomic, readonly) FMDatabase * dataBase; // dataBase operation object
/// Singleton Mode
+ (SDBManager *) defaultDBManager;

// Close the database
-(Void) close;

@ End

# Renewal #--------------------------------------------------------------------------------------------------------

# Import "SDBManager. h"
# Import "FMDatabase. h"

# Define kDefaultDBName @ "voice. sqlite"

@ Interface SDBManager ()

@ End

@ Implementation SDBManager

Static SDBManager * _ sharedDBManager;

+ (SDBManager *) defaultDBManager {
If (! _ SharedDBManager ){
_ SharedDBManager = [[SDBManager alloc] init];
}
Return _ sharedDBManager;
}

-(Void) dealloc {
[Self close];
}

-(Id) init {
Self = [super init];
If (self ){
Int state = [self initializeDBWithName: kDefaultDBName];
If (state =-1 ){
NSLog (@ "database initialization failed ");
} Else {
NSLog (@ "database initialization successful ");
}
}
Return self;
}

/**
* @ Brief initialize database operations
* @ Param name: Database name
* @ Return returns the database initialization status. 0 indicates that the database exists, 1 indicates that the database is created successfully, and-1 indicates that the database fails to be created.
*/
-(Int) initializeDBWithName: (NSString *) name {
If (! Name ){
Return-1; // The error code returned when the database creation fails.
}
// Sandbox Docu directory
NSString * docp = [NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex: 0];
_ Name = [docp stringByAppendingString: [NSString stringWithFormat: @ "/% @", name];
NSFileManager * fileManager = [NSFileManager defaultManager];
BOOL exist = [fileManager fileExistsAtPath: _ name];
[Self connect];
If (! Exist ){
Return 0;
} Else {
Return 1; // The Returned Database already exists.

}

}

/// Connect to the database
-(Void) connect {
If (! _ DataBase ){
_ DataBase = [[FMDatabase alloc] initWithPath: _ name];
}
If (! [_ DataBase open]) {
NSLog (@ "cannot open database ");
}
}
/// Close the connection
-(Void) close {
[_ DataBase close];
_ SharedDBManager = nil;
}

@ End

 

# Renewal #--------------------------------------------------------------------------------------------------------

 

 

# Import "SDBManager. h"
# Import "SUser. h"

@ Interface SUserDB: NSObject {
FMDatabase * _ db;
}

/**
* @ Brief create a database
*/
-(Void) createDataBase;
/**
* @ Brief save a user record
*
* @ Param user the user data to be saved
*/
-(Void) saveUser :( SUser *) user;

/**
* @ Brief delete a user data
*
* @ Param uid the id of the user to be deleted
*/
-(Void) deleteUserWithId :( NSString *) uid;

/**
* @ Brief modify user information
*
* @ Param user the user information to be modified
*/
-(Void) mergeWithUser :( SUser *) user;

/**
* @ Brief simulate querying data by page. Take the limit data after the uid is greater than a certain value
*
* @ Param uid
* @ Param limit: Number of limit entries per page
*/
-(NSArray *) findWithUid :( NSString *) uid limit :( int) limit;

@ End

# Renewal #--------------------------------------------------------------------------------------------------------

 

# Import "SUserDB. h"

# Define kUserTableName @ "SUser"

@ Implementation SUserDB

-(Id) init {
Self = [super init];
If (self ){
// ============ First, check whether the database has created a message. If the database has not been created, ========
_ Db = [SDBManager defaultDBManager]. dataBase;

}
Return self;
}

/**
* @ Brief create a database
*/
-(Void) createDataBase {
FMResultSet * set = [_ db executeQuery: [NSString stringWithFormat: @ "select count (*) from sqlite_master where type = 'table' and name = '% @'", kUserTableName];

[Set next];

NSInteger count = [set intForColumnIndex: 0];

BOOL existTable = !! Count;

If (existTable ){
// TODO: whether to update the database
[AppDelegate showStatusWithText: @ "database already exists" duration: 2];
} Else {
// TODO: Insert a new database
NSString * SQL = @ "CREATE TABLE SUser (uid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name VARCHAR (50), description VARCHAR (100 ))";
BOOL res = [_ db executeUpdate: SQL];
If (! Res ){
[AppDelegate showStatusWithText: @ "database creation failed" duration: 2];
} Else {
[AppDelegate showStatusWithText: @ "database created successfully" duration: 2];
}
}
}

/**
* @ Brief save a user record
*
* @ Param user the user data to be saved
*/
-(Void) saveUser :( SUser *) user {
NSMutableString * query = [NSMutableString stringWithFormat: @ "insert into SUser"];
NSMutableString * keys = [NSMutableString stringWithFormat: @ "("];
NSMutableString * values = [NSMutableString stringWithFormat: @ "("];
NSMutableArray * arguments = [NSMutableArray arrayWithCapacity: 5];
If (user. name ){
[Keys appendString: @ "name,"];
[Values appendString :@"?, "];
[Arguments addObject: user. name];
}
If (user. description ){
[Keys appendString: @ "description,"];
[Values appendString :@"?, "];
[Arguments addObject: user. description];
}
[Keys appendString: @ ")"];
[Values appendString: @ ")"];
[Query appendFormat: @ "% @ VALUES % @",
[Keys stringByReplacingOccurrencesOfString: @ ",)" withString: @ ")"],
[Values stringByReplacingOccurrencesOfString: @ ",)" withString: @ ")"];
NSLog (@ "% @", query );
[AppDelegate showStatusWithText: @ "insert a data entry" duration: 2.0];
[_ Db executeUpdate: query withArgumentsInArray: arguments];
}

/**
* @ Brief delete a user data
*
* @ Param uid the id of the user to be deleted
*/
-(Void) deleteUserWithId :( NSString *) uid {
NSString * query = [NSString stringWithFormat: @ "delete from SUser WHERE uid = '% @'", uid];
[AppDelegate showStatusWithText: @ "delete a piece of data" duration: 2.0];
[_ Db executeUpdate: query];
}

/**
* @ Brief modify user information
*
* @ Param user the user information to be modified
*/
-(Void) mergeWithUser :( SUser *) user {
If (! User. uid ){
Return;
}
NSString * query = @ "UPDATE SUser SET ";
NSMutableString * temp = [NSMutableString stringWithCapacity: 20];
// Xxx = xxx;
If (user. name ){
[Temp appendFormat: @ "name = '% @',", user. name];
}
If (user. description ){
[Temp appendFormat: @ "description = '% @',", user. description];
}
[Temp appendString: @ ")"];
Query = [query stringByAppendingFormat: @ "% @ WHERE uid = '% @'", [temp stringByReplacingOccurrencesOfString: @ ",)" withString: @ ""], user. uid];
NSLog (@ "% @", query );

[AppDelegate showStatusWithText: @ "modify a piece of data" duration: 2.0];
[_ Db executeUpdate: query];
}

/**
* @ Brief simulate querying data by page. Take the limit data after the uid is greater than a certain value
*
* @ Param uid
* @ Param limit: Number of limit entries per page
*/
-(NSArray *) findWithUid :( NSString *) uid limit :( int) limit {
NSString * query = @ "SELECT uid, name, description FROM SUser ";
If (! Uid ){
Query = [query stringByAppendingFormat: @ "order by uid DESC limit % d", limit];
} Else {
Query = [query stringByAppendingFormat: @ "WHERE uid >%@ order by uid DESC limit % d", uid, limit];
}

FMResultSet * rs = [_ db executeQuery: query];
NSMutableArray * array = [NSMutableArray arrayWithCapacity: [rs columnCount];
While ([rs next]) {
SUser * user = [SUser new];
User. uid = [rs stringForColumn: @ "uid"];
User. name = [rs stringForColumn: @ "name"];
User. description = [rs stringForColumn: @ "description"];
[Array addObject: user];
}
[Rs close];
Return array;
}

@ End

 

 

Call:

-(Void) initFMDB {
BlogDB * db = [[BlogDB alloc] init] autorelease];
[Db createDataBase];
}

 

BlogDB * blogDB = [[BlogDB alloc] init];
[BlogDB saveBlog: blog];
[BlogDB findWithBlogid: nil limit: 20];
NSArray * arr = [blogDB findWithBlogid: nil limit: 20];

NSLog (@ "data quantity: % d", [arr count]);
[BlogDB release];