Fmdb is used in IOS development to enable programs to connect to SQLite databases _ios

One, simple explanation

1. What is Fmdb

Fmdb is the SQLite database framework for iOS platform

Fmdb encapsulates the C language API of SQLite in OC mode

The advantages of 2.FMDB

Use more object-oriented, save a lot of trouble, redundant C language code

Compare the core data frame with Apple's own, lighter and more flexible

Provides a multi-threaded and secure database operation method to effectively prevent data clutter

3.FMDB GitHub Address

Https://github.com/ccgus/fmdb

Second, Core class

The Fmdb has three main classes

(1) Fmdatabase

A Fmdatabase object represents a separate SQLite database.

Used to execute SQL statements

(2) Fmresultset

Result set after a query is executed using Fmdatabase

(3) Fmdatabasequeue

Used to execute multiple queries or updates in multiple threads, it is thread-safe

Iii. Open the database

To create a Fmdatabase object by specifying the SQLite database file path

Copy Code code as follows:

Fmdatabase *db = [Fmdatabase Databasewithpath:path];

if (![ DB Open]) {

NSLog (@ "Database open failed!) ");

}

There are three cases of file paths

(1) Specific file path

If it does not exist, it is created automatically

(2) empty string @ ""

Creates an empty database in the temp directory

The database file is also deleted when the Fmdatabase connection is closed

(3) Nil

Creates an in-memory staging database that is destroyed when the Fmdatabase connection is closed

Iv. implementation of the update

In Fmdb, all operations except queries are called "updates."

Create, DROP, insert, UPDATE, DELETE, etc.

To perform an update using the Executeupdate: method

Copy Code code as follows:

-(BOOL) Executeupdate: (nsstring*) SQL, ...

-(BOOL) Executeupdatewithformat: (nsstring*) format, ...

-(BOOL) Executeupdate: (nsstring*) SQL Withargumentsinarray: (Nsarray *) arguments

Example

Copy Code code as follows:

[DB executeupdate:@ UPDATE t_student SET age =? WHERE name =?; ", @20, @" Jack "]

V. Execution of inquiries

Query method

Copy Code code as follows:

-(Fmresultset *) ExecuteQuery: (nsstring*) SQL, ...

-(Fmresultset *) Executequerywithformat: (nsstring*) format, ...

-(Fmresultset *) ExecuteQuery: (NSString *) SQL Withargumentsinarray: (Nsarray *) arguments

Example

Copy Code code as follows:

Querying data

Fmresultset *rs = [db executequery:@ "select * from T_student"];

Traverse result set

while ([Rs next]) {

NSString *name = [rs stringforcolumn:@ "name"];

int age = [rs intforcolumn:@ ' age '];

Double score = [Rs doubleforcolumn:@ "score"];

}

Vi. Code Examples

1. Create a new project, import the Libsqlite3 library, and include the primary header file in the project

2. Download the third party framework Fmdb

3. Sample Code

YYVIEWCONTROLLER.M file

Copy Code code as follows:

//
Yyviewcontroller.m
04-fmdb Basic Use
//
Created by Apple on 14-7-27.
Copyright (c) 2014 wendingding. All rights reserved.
//

#import "YYViewController.h"
#import "FMDB.h"

@interface Yyviewcontroller ()
@property (Nonatomic,strong) fmdatabase *db;
@end

@implementation Yyviewcontroller

-(void) viewdidload
{
[Super Viewdidload];
1. Get the path to the database file
NSString *doc=[nssearchpathfordirectoriesindomains (NSDocumentDirectory, Nsuserdomainmask, YES) lastObject];
NSString *filename=[doc stringbyappendingpathcomponent:@ "Student.sqlite"];

2. Access to the database
Fmdatabase *db=[fmdatabase Databasewithpath:filename];

3. Open the Database
if ([db Open]) {
4. Create a table
BOOL result=[db executeupdate:@ "CREATE TABLE IF not EXISTS t_student (ID integer PRIMARY KEY autoincrement, Name text not NULL, age integer NOT NULL);
if (result) {
NSLog (@ "Success of the Table");
}else
{
NSLog (@ "Create a table failure");
}
}
self.db=db;

}

-(void) Touchesbegan: (Nsset *) touches withevent: (Uievent *) event
{
[Self Delete];
[Self insert];
[Self query];
}

Inserting data
-(void) Insert
{
for (int i = 0; i<10; i++) {
NSString *name = [NSString stringwithformat:@ "jack-%d", Arc4random_uniform (100)];
Executeupdate: An indeterminate parameter used to occupy a position
[Self.db executeupdate:@ INSERT into t_student (name, age) VALUES (?,?); ", Name, @ (Arc4random_uniform (40))];
[Self.db executeupdate:@ INSERT into t_student (name, age) VALUES (?,?); "Withargumentsinarray:@[name, @ (Arc4ra Ndom_uniform (40))]];

Executeupdatewithformat: Indeterminate parameters are occupied by%@,%d, etc.
[Self.db executeupdatewithformat:@ INSERT into t_student (name, age) VALUES (%@,%d); ", Name, Arc4random_uniform (40)];
}
}

//Delete data
-(void) Delete
{
   //    [self.db executeupdate:@] Delete from T_ student; "];
    [self.db executeupdate:@ "DROP TABLE IF EXISTS t_student;"];
    [self.db executeupdate:@ "CREATE TABLE IF not EXISTS t_student (ID integer PRIMARY KEY autoincremen T, name text NOT NULL, age integer NOT null;
}

Inquire
-(void) query
{
1. Execute Query statement
Fmresultset *resultset = [self.db executequery:@ "SELECT * from T_student"];

2. Traversal results
while ([ResultSet next]) {
int ID = [ResultSet intforcolumn:@ "id"];
NSString *name = [resultSet stringforcolumn:@ "name"];
int age = [ResultSet intforcolumn:@ ' age '];
NSLog (@ "%d%@%d", ID, name, age);
}
}

@end

Print View results:

Tips:

If the ID is set to gradual and is set to grow automatically, then the data in the table is deleted and the new data is reinserted, and the ID number is not starting at 0, but the previous ID.

Attention:

Do not write the following form, do not add ', directly using%@, it will automatically think that this is a string.

Vii. Fmdb Database queues
1. code example

(1). Fmdb Framework and header files need to be imported first, and because the framework relies on libsqlite libraries, you should also import the library.

(2). The code is as follows:

Copy Code code as follows:

//
Yyviewcontroller.m
05-fmdb database queues
//
Created by Apple on 14-7-28.
Copyright (c) 2014 wendingding. All rights reserved.
//

#import "YYViewController.h"
#import "FMDB.h"

@interface Yyviewcontroller ()
@property (Nonatomic,strong) Fmdatabasequeue *queue;
@end

@implementation Yyviewcontroller

-(void) viewdidload
{
[Super Viewdidload];

1. Get the path to the database file
NSString *doc=[nssearchpathfordirectoriesindomains (NSDocumentDirectory, Nsuserdomainmask, YES) lastObject];
NSString *filename=[doc stringbyappendingpathcomponent:@ "Person.sqlite"];

2. Get the database queue
Fmdatabasequeue *queue=[fmdatabasequeue Databasequeuewithpath:filename];
Fmdatabase *db=[fmdatabase Databasewithpath:filename];

3. Open the Database
[Queue indatabase:^ (Fmdatabase *db) {
BOOL result=[db executeupdate:@ "CREATE TABLE IF not EXISTS t_person (ID integer PRIMARY KEY autoincrement, name text not N ull, age integer Not NULL);
if (result) {
NSLog (@ "Success of the Table");
}else
{
NSLog (@ "Create a table failure");
}
}];
Self.queue=queue;

}

-(void) Touchesbegan: (Nsset *) touches withevent: (Uievent *) event
{
Inserting data
[Self.queue indatabase:^ (Fmdatabase *db) {
[DB executeupdate:@ INSERT into T_person (name, age) VALUES (?,?); ", @" wendingding ", @22];
//    }];

Querying data
[Self.queue indatabase:^ (Fmdatabase *db) {
1. Execute Query statement
Fmresultset *resultset = [db executequery:@ "select * from T_person"];

2. Traversal results
while ([ResultSet next]) {
int ID = [ResultSet intforcolumn:@ "id"];
NSString *name = [resultSet stringforcolumn:@ "name"];
int age = [ResultSet intforcolumn:@ ' age '];
NSLog (@ "%d%@%d", ID, name, age);
}
}];

}

@end

First insert the data, then the query results, print as follows:

(3). Code description

With a queue object, its internal automatically owns a database object, and the operation of the database is thread-safe.

2. The transaction
Transaction, there is a problem if there is no transaction.
Example: Bank examples

John and Dick accounts have 1000 dollars, if Zhang three to transfer to the dick, need to execute two SQL statements, taking into account security, the requirements of the two fish with either the success of all, or all failed to execute.
Transactions: Put multiple statements into the same transaction, either all succeeds or all fail (automatically rollback if there is a problem in the middle). The execution of a transaction is atomic.
Transaction code Processing:
Add multiple statements to a transaction to perform:

Copy Code code as follows:

Inserting data
[Self.queue indatabase:^ (Fmdatabase *db) {
[DB BeginTransaction];
[DB executeupdate:@ INSERT into T_person (name, age) VALUES (?,?); ", @" wendingding ", @22];
[DB executeupdate:@ INSERT into T_person (name, age) VALUES (?,?); ", @" wendingding ", @23];
[DB executeupdate:@ INSERT into T_person (name, age) VALUES (?,?); ", @" wendingding ", @24];
[DB executeupdate:@ INSERT into T_person (name, age) VALUES (?,?); ", @" wendingding ", @25];
[DB commit];
}];

If the problem occurs halfway through, it is automatically rolled back, or you can choose to roll back manually.

Copy Code code as follows:

Inserting data
[Self.queue indatabase:^ (Fmdatabase *db) {
[DB BeginTransaction];
[DB executeupdate:@ INSERT into T_person (name, age) VALUES (?,?); ", @" wendingding ", @22];
[DB executeupdate:@ INSERT into T_person (name, age) VALUES (?,?); ", @" wendingding ", @23];
[DB executeupdate:@ INSERT into T_person (name, age) VALUES (?,?); ", @" wendingding ", @24];
[DB rollback];
[DB executeupdate:@ INSERT into T_person (name, age) VALUES (?,?); ", @" wendingding ", @25];
[DB commit];
}];

The code above. The first three INSERT statements are invalid.

Another way to deal with a transaction:

Copy Code code as follows:

[Self.queue intransaction:^ (Fmdatabase *db, BOOL *rollback) {
[DB executeupdate:@ INSERT into T_person (name, age) VALUES (?,?); ", @" wendingding ", @22];
[DB executeupdate:@ INSERT into T_person (name, age) VALUES (?,?); ", @" wendingding ", @23];
[DB executeupdate:@ INSERT into T_person (name, age) VALUES (?,?); ", @" wendingding ", @24];
}];

Description: Open the transaction, then start the transaction, then execute the code snippet in block, and finally commit the transaction.