IPhone Development (9)-using SQLite to manage databases

 

Today, let's take a look at how to use the database in the iPhone. The iPhone uses a database management system named SQLite. It is a lightweight database and an ACID-compliant associated database management system. It is designed to be embedded and has been used in many embedded products, it occupies very low resources. In embedded devices, it may only need several hundred KB of memory. It supports mainstream operating systems such as Windows, Linux, and Unix, and can be combined with many programming languages, such as Tcl, PHP, Java, and ODBC interfaces, similar to Mysql and PostgreSQL, the two world-renowned open-source database management systems, the processing speed is faster than that of them.

 

The procedure is as follows:

 

Create DB files and tables

Add required library files (FMDB for iPhone, libsqlite3.0.dylib)

Using SQLite through FMDB

Create DB files and tables

$ Sqlite3 sample. db

Sqlite> create table test (

...> Id integer primary key,

...> Name VARCHAR (255)

...> );

After the above statements are used to generate database files, It is very convenient to use a graphical SQLite management tool, such as Lita for management.

 

Then add the file (sample. db) to the project.

 

Add required library files (FMDB for iPhone, libsqlite3.0.dylib)

First, add the sqlite operation library ibsqlite3.0.dylib provided by Apple to the project.

 

The location is as follows:

/Developer/Platforms/iPhoneOS. platform/Developer/SDKs/iPhoneOS $ {VER}. sdk/usr/lib/libsqlite3.0.dylib

 

In this way, you can access the database, but in order to operate the database more conveniently, FMDB for iPhone is used here.

 

Svn co http://flycode.googlecode.com/svn/trunk/fmdb (fmdb)

Download the library and add the following files to the project file:

 

FMDatabase. h

FMDatabase. m

FMDatabaseAdditions. h

FMDatabaseAdditions. m

FMResultSet. h

FMResultSet. m

Using SQLite through FMDB

Use SQL to Operate Database code in the library fmdb. most of the m files are listed, but when you connect to the database file, you must note that the reference database path is located in the Document directory during execution. copy the db file.

 

The location is as follows:

/Users/xxxx/Library/Application Support/iPhone Simulator/User/Applications/xxxx/Documents/sample. db

The following code links to a database:

 

BOOL success;

NSError * error;

NSFileManager * fm = [NSFileManager defamanager manager];

NSArray * paths = NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES );

NSString * documentsDirectory = [paths objectAtIndex: 0];

NSString * writableDBPath = [documentsDirectory stringByAppendingPathComponent: @ "sample. db"];

Success = [fm fileExistsAtPath: writableDBPath];

If (! Success ){

NSString * defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent: @ "sample. db"];

Success = [fm copyItemAtPath: defaultDBPath toPath: writableDBPath error: & error];

If (! Success ){

NSLog ([error localizedDescription]);

}

}

 

// Connect to the database

FMDatabase * db = [FMDatabase databaseWithPath: writableDBPath];

If ([db open]) {

[Db setShouldCacheStatements: YES];

 

// INSERT

[Db beginTransaction];

Int I = 0;

While (I ++ <20 ){

[Db executeUpdate: @ "insert into test (name) values (?) ", [NSString stringWithFormat: @" number % d ", I];

If ([db hadError]) {

NSLog (@ "Err % d: % @", [db lastErrorCode], [db lastErrorMessage]);

}

}

[Db commit];

 

// SELECT

FMResultSet * rs = [db executeQuery: @ "SELECT * from test"];

While ([rs next]) {

NSLog (@ "% d % @", [rs intForColumn: @ "id"], [rs stringForColumn: @ "name"]);

}

[Rs close];

[Db close];

} Else {

NSLog (@ "cocould not open db .");

}

Next, let's take a look at how to access the database in the form of DAO. The overall structure of the Code is as follows.

 

 

First, create a database file in the following format:

$ Sqlite3 sample. db

Sqlite> create table TbNote (

...> Id integer primary key,

...> Title VARCHAR (255 ),

...> Body VARCHAR (255)

...> );

Create a DTO (Data Transfer Object)

// TbNote. h

# Import <Foundation/Foundation. h>

 

@ Interface TbNote: NSObject {

Int index;

NSString * title;

NSString * body;

}

 

@ Property (nonatomic, retain) NSString * title;

@ Property (nonatomic, retain) NSString * body;

 

-(Id) initWithIndex :( int) newIndex Title :( NSString *) newTitle Body :( NSString *) newBody;

-(Int) getIndex;

 

@ End

 

// TbNote. m

# Import "TbNote. h"

 

@ Implementation TbNote

@ Synthesize title, body;

 

-(Id) initWithIndex :( int) newIndex Title :( NSString *) newTitle Body :( NSString *) newBody {

If (self = [super init]) {

Index = newIndex;

Self. title = newTitle;

Self. body = newBody;

}

Return self;

}

 

-(Int) getIndex {

Return index;

}

 

-(Void) dealloc {

[Title release];

[Body release];

[Super dealloc];

}

 

@ End

Create DAO (Data Access Objects)

Here, the function call of FMDB is encapsulated as DAO.

 

// BaseDao. h

# Import <Foundation/Foundation. h>

 

@ Class FMDatabase;

 

@ Interface BaseDao: NSObject {

FMDatabase * db;

}

 

@ Property (nonatomic, retain) FMDatabase * db;

 

-(NSString *) setTable :( NSString *) SQL;

 

@ End

 

// BaseDao. m

# Import "SqlSampleAppDelegate. h"

# Import "FMDatabase. h"

# Import "FMDatabaseAdditions. h"

# Import "BaseDao. h"

 

@ Implementation BaseDao

@ Synthesize db;

 

-(Id) init {

If (self = [super init]) {

// The database opened by AppDelegate

SqlSampleAppDelegate * appDelegate = (SqlSampleAppDelegate *) [[UIApplication sharedApplication] delegate];

Db = [[appDelegate db] retain];

}

Return self;

}

// Implement in subclass

-(NSString *) setTable :( NSString *) SQL {

Return NULL;

}

 

-(Void) dealloc {

[Db release];

[Super dealloc];

}

 

@ End

The following is a class for accessing the TbNote table.

// TbNoteDao. h

# Import <Foundation/Foundation. h>

# Import "BaseDao. h"

 

@ Interface TbNoteDao: BaseDao {

}

 

-(NSMutableArray *) select;

-(Void) insertWithTitle :( NSString *) title Body :( NSString *) body;

-(BOOL) updateAt :( int) index Title :( NSString *) title Body :( NSString *) body;

-(BOOL) deleteAt :( int) index;

 

@ End

 

// TbNoteDao. m

# Import "FMDatabase. h"

# Import "FMDatabaseAdditions. h"

# Import "TbNoteDao. h"

# Import "TbNote. h"

 

@ Implementation TbNoteDao

 

-(NSString *) setTable :( NSString *) SQL {

Return [NSString stringWithFormat: SQL, @ "TbNote"];

}

// SELECT

-(NSMutableArray *) select {

NSMutableArray * result = [[NSMutableArray alloc] initWithCapacity: 0] autorelease];

FMResultSet * rs = [db executeQuery: [self setTable: @ "SELECT * FROM % @"];

While ([rs next]) {

TbNote * tr = [TbNote alloc]

InitWithIndex: [rs intForColumn: @ "id"]

Title: [rs stringForColumn: @ "title"]

Body: [rs stringForColumn: @ "body"]

];

[Result addObject: tr];

[Tr release];

}

[Rs close];

Return result;

}

// INSERT

-(Void) insertWithTitle :( NSString *) title Body :( NSString *) body {

[Db executeUpdate: [self setTable: @ "insert into % @ (title, body) VALUES (?,?) "], Title, body];

If ([db hadError]) {

NSLog (@ "Err % d: % @", [db lastErrorCode], [db lastErrorMessage]);

}

}

// UPDATE

-(BOOL) updateAt :( int) index Title :( NSString *) title Body :( NSString *) body {

BOOL success = YES;

[Db executeUpdate: [self setTable: @ "UPDATE % @ SET title = ?, Body =? WHERE id =? "], Title, body, [NSNumber numberWithInt: index];

If ([db hadError]) {

NSLog (@ "Err % d: % @", [db lastErrorCode], [db lastErrorMessage]);

Success = NO;

}

Return success;

}

// DELETE

-(BOOL) deleteAt :( int) index {

BOOL success = YES;

[Db executeUpdate: [self setTable: @ "delete from % @ WHERE id =? "], [NSNumber numberWithInt: index];

If ([db hadError]) {

NSLog (@ "Err % d: % @", [db lastErrorCode], [db lastErrorMessage]);

Success = NO;

}

Return success;

}

 

-(Void) dealloc {

[Super dealloc];

}

 

@ End

To ensure that the program is correct, we add a UITableView. Use initWithNibName to test DAO.

// NoteController. h

# Import <UIKit/UIKit. h>

 

@ Class TbNoteDao;

 

@ Interface NoteController: UIViewController <UITableViewDataSource, UITableViewDelegate> {

UITableView * myTableView;

TbNoteDao * tbNoteDao;

NSMutableArray * record;

}

 

@ Property (nonatomic, retain) UITableView * myTableView;

@ Property (nonatomic, retain) TbNoteDao * tbNoteDao;

@ Property (nonatomic, retain) NSMutableArray * record;

 

@ End

 

// NoteController. m

# Import "NoteController. h"

# Import "TbNoteDao. h"

# Import "TbNote. h"

 

@ Implementation NoteController

@ Synthesize myTableView, tbNoteDao, record;

 

-(Id) initWithNibName :( NSString *) nibNameOrNil bundle :( NSBundle *) nibBundleOrNil {

If (self = [super initWithNibName: nibNameOrNil bundle: nibBundleOrNil]) {

TbNoteDao = [[TbNoteDao alloc] init];

[TbNoteDao insertWithTitle: @ "test title" Body: @ "test body"];

// [TbNoteDao updateAt: 1 Title: @ "update test" Body: @ "update body"];

// [TbNoteDao deleteAt: 1];

Record = [[tbNoteDao select] retain];

}

Return self;

}

 

-(Void) viewDidLoad {

[Super viewDidLoad];

MyTableView = [[UITableView alloc] initWithFrame: [UIScreen mainScreen] applicationFrame];

MyTableView. delegate = self;

MyTableView. dataSource = self;

Self. view = myTableView;

}

 

-(NSInteger) numberOfSectionsInTableView :( UITableView *) tableView {

Return 1;

}

 

-(NSInteger) tableView :( UITableView *) tableView numberOfRowsInSection :( NSInteger) section {

Return [record count];

}

 

-(UITableViewCell *) tableView :( UITableView *) tableView cellForRowAtIndexPath :( NSIndexPath *) indexPath {

Static NSString * CellIdentifier = @ "Cell ";

 

UITableViewCell * cell = [tableView dequeueReusableCellWithIdentifier: CellIdentifier];

If (cell = nil ){

Cell = [[[UITableViewCell alloc] initWithFrame: CGRectZero reuseIdentifier: CellIdentifier] autorelease];

}

TbNote * tr = (TbNote *) [record objectAtIndex: indexPath. row];

Cell. text = [NSString stringWithFormat: @ "% I % @", [tr getIndex], tr. title];

Return cell;

}

 

-(Void) didReceiveMemoryWarning {

[Super didReceiveMemoryWarning];

}

 

-(Void) dealloc {

[Super dealloc];

}

 

@ End

Finally, let's take a look at how to connect to the DB and add ViewController. This does not use Interface Builder.

// SqlSampleAppDelegate. h

# Import <UIKit/UIKit. h>

 

@ Class FMDatabase;

 

@ Interface SqlSampleAppDelegate: NSObject <UIApplicationDelegate> {

UIWindow * window;

FMDatabase * db;

}

 

@ Property (nonatomic, retain) IBOutlet UIWindow * window;

@ Property (nonatomic, retain) FMDatabase * db;

 

-(BOOL) initDatabase;

-(Void) closeDatabase;

 

@ End

 

// SqlSampleAppDelegate. m

# Import "SqlSampleAppDelegate. h"

# Import "FMDatabase. h"

# Import "FMDatabaseAdditions. h"

# Import "NoteController. h"

 

@ Implementation SqlSampleAppDelegate

 

@ Synthesize window;

@ Synthesize db;

 

-(Void) applicationDidFinishLaunching :( UIApplication *) application {

If (! [Self initDatabase]) {

NSLog (@ "Failed to init Database .");

}

NoteController * ctrl = [[NoteController alloc] initWithNibName: nil bundle: nil];

[Window addSubview: ctrl. view];

[Window makeKeyAndVisible];

}

 

-(BOOL) initDatabase {

BOOL success;

NSError * error;

NSFileManager * fm = [NSFileManager defamanager manager];

NSArray * paths = NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES );

NSString * documentsDirectory = [paths objectAtIndex: 0];

NSString * writableDBPath = [documentsDirectory stringByAppendingPathComponent: @ "sample. db"];

 

Success = [fm fileExistsAtPath: writableDBPath];

If (! Success ){

NSString * defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent: @ "sample. db"];

Success = [fm copyItemAtPath: defaultDBPath toPath: writableDBPath error: & error];

If (! Success ){

NSLog ([error localizedDescription]);

}

Success = NO;

}

If (success ){

Db = [[FMDatabase databaseWithPath: writableDBPath] retain];

If ([db open]) {

[Db setShouldCacheStatements: YES];

} Else {

NSLog (@ "Failed to open database .");

Success = NO;

}

}

Return success;

}

 

-(Void) closeDatabase {

[Db close];

}

 

-(Void) dealloc {

[Db release];

[Window release];

[Super dealloc];

}

 

@ End