Database usage tutorial in iPhone Development

IPhoneUnder developmentDatabaseUsage is what we will introduce in this article,IPhoneSqlite is used.DatabaseI used the firefox plug-in Sqlite Manager to manage sqlite. This plug-in is very useful and allows you to easily create and manage sqlite in a view. If you don't talk nonsense, go to the topic.

To use sqlite, first introduce the libsqlite3.0.dylib file in Frameworks. I skipped the steps and then createdDatabase.DatabaseAdd to the Resources directory (remember to check the Copy items into... option). Now the preparation is complete. Write the code below.

For ease of use and future maintenance, I have created a class here to encapsulate the database-related code. Create an NSObject class named GADatabase here and add the following code to the implementation file:

 
 

  
  
#import <sqlite3.h> 
  
  
 
  
  
id getColValue(sqlite3_stmt *stmt,int iCol) {  
  
  
    int type = sqlite3_column_type(stmt, iCol);  
  
  
    switch (type) {  
  
  
        case SQLITE_INTEGER:  
  
  
            return [NSNumber numberWithInt:sqlite3_column_int(stmt, iCol)];  
  
  
            break;  
  
  
        case SQLITE_FLOAT:  
  
  
            return [NSNumber numberWithDouble:sqlite3_column_double(stmt, iCol)];  
  
  
            break;  
  
  
        case SQLITE_TEXT:  
  
  
            return [NSString stringWithUTF8String:sqlite3_column_text(stmt, iCol)];  
  
  
            break;  
  
  
        case SQLITE_BLOB:  
  
  
            return [NSData dataWithBytes:sqlite3_column_blob(stmt, iCol) length:sqlite3_column_bytes(stmt, iCol)];  
  
  
            break;  
  
  
        case SQLITE_NULL:  
  
  
            return @"";  
  
  
            break;  
  
  
        default:  
  
  
            return @"NONE";  
  
  
            break;  
  
  
    }  
  
  
} 
 
 

Here, I use the C-style definition and the definition is out of @ implementation for ease of use. With this function, it will be very convenient for data extraction in the future, the data type can be increased or decreased according to the actual situation. OK. Now Add the following code between @ implementation and @ end to get the sqlite address in the iphone:

 
 

  
  
+ (NSString *)pathForDatabase {  
  
  
    NSArray *arrayOfPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);  
  
  
    NSString *path = [arrayOfPaths objectAtIndex:0];  
  
  
    path = [path stringByAppendingPathComponent:@"yourDatabaseName.sqlite"];  
  
  
    NSLog(path);  
  
  
    NSFileManager *fileManager = [NSFileManager defaultManager];  
  
  
    if(![fileManager fileExistsAtPath:path]){  
  
  
        NSString *databaseSource = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"yourDatabaseName.sqlite"];  
  
  
        if(![fileManager copyItemAtPath:databaseSource toPath:path error:nil]){  
  
  
            return nil;  
  
  
        }  
  
  
    }  
  
  
    return path;  
  
  
} 
 
 

Now you can write SQL statements. Here are some examples:

Query a field:

 
 

  
  
- (NSString *)select:(NSString *)Parameter {  
  
  
    sqlite3 *database;  
  
  
    sqlite3_stmt *stm;  
  
  
    NSString *result = [NSString string];  
  
  
    NSString *sql = [NSString stringWithFormat:@"SELECT columnName FROM table WHERE columnName='%@'", Parameter];  
  
  
     
  
  
    if(sqlite3_open([[GADatabase pathForDatabase] UTF8String], &database) == SQLITE_OK) {  
  
  
        if(sqlite3_prepare_v2(database, [sql UTF8String], -1, &stm, NULL) == SQLITE_OK) {  
  
  
            if(sqlite3_step(stm) == SQLITE_ROW) {  
  
  
                result = getColValue(stm, 0);  
  
  
            }  
  
  
        }  
  
  
        sqlite3_finalize(stm);  
  
  
    }  
  
  
    sqlite3_close(database);  
  
  
    return result;  
  
  
} 
 
 

To query multiple fields, you can use an array and a custom class to store them. For example:

 
 

  
  
- (NSMutableArray *)selectUsers {  
  
  
    sqlite3 *database;  
  
  
    sqlite3_stmt *stm;  
  
  
    NSMutableArray *result = [[NSMutableArray alloc] init];  
  
  
    NSString *sql = @"SELECT * FROM users";  
  
  
     
  
  
    if(sqlite3_open([[GADatabase pathForDatabase] UTF8String], &database) == SQLITE_OK) {  
  
  
        if(sqlite3_prepare_v2(database, [sql UTF8String], -1, &stm, NULL) == SQLITE_OK) {  
  
  
            while(sqlite3_step(stm) == SQLITE_ROW) {  
  
  
                GAData *userObj = [[GAData alloc] init];  
  
  
                userObj.rId = getColValue(stm, 0);  
  
  
                userObj.userName = getColValue(stm, 1);  
  
  
                userObj.passWord = getColValue(stm, 2);  
  
  
                [result addObject:userObj];  
  
  
                [userObj release];  
  
  
            }  
  
  
        }  
  
  
        sqlite3_finalize(stm);  
  
  
    }  
  
  
    sqlite3_close(database);  
  
  
    return result;     
  
  
} 
 
 

The GAData class is defined as follows:

 
 

  
  
#import <Foundation/Foundation.h> 
  
  
@interface GAData : NSObject {  
  
  
    NSNumber *rId;  
  
  
    NSString *userName;  
  
  
    NSString *passWord;  
  
  
}  
  
  
@property(nonatomic, retain)NSNumber *rId;  
  
  
@property(nonatomic, retain)NSString *userName;  
  
  
@property(nonatomic, retain)NSString *passWord;  
  
  
@end  
  
  
#import "GAData.h"  
  
  
@implementation GAData  
  
  
@synthesize rId;  
  
  
@synthesize userName;  
  
  
@synthesize passWord;  
  
  
@end 
 
 

Add data to the database:

 
 

  
  
- (void)addUser:(GAData *)addUserObj {  
  
  
    sqlite3 *database;  
  
  
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO users (userName, passWord) VALUES('%@','%@')",  
  
  
                     addUserObj.userName, addUserObj.passWord];  
  
  
     
  
  
    int status = sqlite3_open([[GADatabase pathForDatabase] UTF8String], &database);  
  
  
    if(status != SQLITE_OK) {  
  
  
        return;  
  
  
    }  
  
  
    status = sqlite3_exec(database, [sql UTF8String], 0, 0, NULL);  
  
  
    if(status != SQLITE_OK) {  
  
  
        return;  
  
  
    }  
  
  
    sqlite3_close(database);  
  
  
} 
 
 

Deletion and modification are similar to addition. They are nothing more than SQL statements, so they are no longer used as an example. Let's talk about the time function in sqlite, so far, I have only used functions about the number of days of computing, so I will not introduce them. You can search for them online and see the following SQL statement:

 
 

  
  
SELECT columnName FROM table WHERE (julianday(date(columnName))-julianday(date('now')))>10 
 
 

This function returns the number of days, starting from January 1, November 24, 4714 BC, Greenwich Mean Time. The date () function returns a date in YYYY-MM-DD format. Therefore, the above statement is not difficult to understand.

AboutDatabaseI have written so much about it for the time being. If there are some bad or wrong places, you are welcome to point it out. Let's learn it together. Finally, I would like to remind you that if you modifyDatabaseContent, You need to delete the previously compiled program, re-compile, or find the path when the program is running, deleteDatabaseFile, and then re-compile and run, only in this way, in your programDatabaseWill be updated, remember!

Summary:IPhoneUnder developmentDatabaseI hope this article will help you!