[Original] Access database using Fmdb

Recently began to write a blog, the things I learned to summarize and summarize.

Review today to Fmdb,fmdb is a third-party access to the database plugin. With it, my mother no longer has to worry about my access to the database.

Okay, so why use Fmdb?

One of the most direct reason is that iOS sqlite3 access to the database code is too cumbersome, difficult to remember, write it is not easy, the reason is very simple, that is, Sqlite3 is a C package to access the database API, it is written in C, you want to use the form of C to access, so, Use up you will feel a little twist, as if it is a foreigner living in Guangdong, although can also communicate with the Cantonese, but still feel the slightest twist.

and Fmdb solved the problem of this twist, it is the use of OC Grammar again encapsulated sqlite3, you go to use Fmdb all the way is OC syntax, and reduce the optimization of a lot of content, let us manipulate the database become more rapid and convenient.

Fmdb:https://github.com/ccgus/fmdb (Friendship links, if not used, their own Baidu, haha)

After you download the Fmdb, there are a few things you need to do:
1. Drag the Fmdb folder into the project

2. Add the Libsqlite3.dylib package to the project library

3. In the need to use FMDB place, import FMDB, here I added a FMDB.h file, in the FMDB.h file import FMDB all the package, so to use all the package, directly import FMDB.h file, example: #import "FMDB.h"

The FMDB.h file includes a list of all the FMDB files as shown in:

The content commonly used in Fmdb is as follows:

1.FMDatabase: A reference to a database that represents a database that can be used to execute SQL statements

2.FMResultSet: A result set that holds the results of a database query statement.

Okay, let's do the first thing. Create a database

creates a path string that is student.sqlite in the Documents directory in the sandbox

NSString* path=[[nssearchpathfordirectoriesindomains(nsdocumentdirectory, Nsuserdomainmask, YES) lastobject] stringbyappendingstring:@ " Student.sqlite "];

Open the database file according to the path and create the database file if the file does not exist

fmdatabase* db=[fmdatabase databasewithpath:p ath];

　　

Second thing, open the database connection, close the database connection

The Open method can open a database connection and returns a bool value indicating whether the open database connection was successful

BOOL Result=[db Open];

...;//Database operations

Shutting down a database connection is a good practice to shut down when you're done, freeing up connections to reduce memory usage.

[DB close];

The third thing, use Fmdb to perform additions and deletions, create tables and other operations

If you want to make a query, you need to use the ExecuteQuery method.

SQL statements other than queries are executed using the Executeupdate method.

For example, create a data table:

Use Executeupdate to execute the statement and return a bool value to indicate the result of the creation of the table, yes to the success of the table, no to the table failure

if ([db Open]) {//Remember to open a database connection to use executeupdate Execute SQL statement

BOOL cresult= [self. MyDB executeupdate:@ "CREATE TABLE IF not EXISTS t_student (Sid integer PRIMARY KEY autoincrement,name text Not null,sex text not nill,age int); " ];

}

[DB close]; Run out of toilets, remember to rush, quack.

　　

after executing the above code, we have a t_student data table in our database, there is no data in the table, we can use executeupdate method to delete and modify the data, if the use of executeupdate is passed a complete SQL statement, can not be passed the parameter, The Executeupdatewithformat I'm using now is the way that parameters can be passed. However, it is important to note that this is no longer necessary to provide the database string "(single quotation marks), because the Fmdb is already encapsulated, if you use it will be a problem.

Add Data:

if ([db Open]) {

for (int i=1;i<100;i++) {

nsstring* name=[nsstring stringwithformat:@ " student -%d", I];

No need to provide ' '(single quote) for string type when using Withformat provided by Fmdb

[self. MyDB executeupdatewithformat:@ "INSERT into t_student values (%@,%@,%d)", name,i%2? @ " male ":@ " female ", I];

}}

[DB close];

Delete data:

if ([db Open]) {

Delete the data that is numbered 1,

In particular, it is important to note that the DELETE statement must be delete from, and the INSERT statement must be insert INTO, because I used to use SQL Server, and I've had a loss on these two things.

[DB executeupdate:@ "Delete from t_student where sid=1"];

}

[DB close];

Update data:

if ([db Open]) {

Update gender information, male change girl

[DB executeupdatewithformat:@ "Update t_student set sex=%@ where sex=%@" @ "male" @ "female"];

}

[DB close];

Finally is the more troublesome query data, it is not like the previous additions and deletions, as long as the execution will have success or failure of the result is good, query it returns the table data result set, so we will use Fmresultset to store the final query results.

Query data:

if ([db Open]) {

nsstring* [Email protected] "SELECT * from T_student";

Executes the SQL statement using the ExecuteQuery method and returns a Fmresultset* object that contains the results of the query statement

fmresultset* rs=[db Executequery:sql];

while ([Rs next])//Use the next method of the result set object to read the next row of data in the result sets, if the next method result is no, it means that the data has been read

{//This grammatical form is more like Java than the sqlite3.

The Xxxforcolumn:column method of the result set, XXX represents the type of return, and column represents the data table column name

int Sid=[rs Intforcolumn:sid];//sid is the meaning of the SID column of T_student, and Intforcolumn represents the return as an integral type

nsstring* Name=[rs Stringforcolumn:name];

nsstring* sex = [Rs stringforcolumn:sex];

int Age=[rs Intforcolumn:age];

I do not encapsulate the entity here, so directly printed, whether to encapsulate the entity, see you greatly need

NSLog (@ "Number:%d, name:%@, Gender:%@, Age:%d", sid,name,sex,age);

}

}

[DB close];

With the specific form of xxxforcolumn, what kind of type can you read and look at:

　　

Fmdb really good, use is also really very convenient, think up Sqlite3 is two lines of tears AH.

Finally, the transaction execution syntax encapsulated in Fmdb is included:

if ([db Open]) {

[DB begintransaction];//Start transaction

@try {

...;//perform related data operations;

}

@catch (nsexception* ex) {

[DB rollback];//rollback operation;

}

[DB commit];//Commit Transaction

}

[DB close];//Close database connection

Well, today's share dear here, hope to be helpful to everyone, if there is any problem, welcome to discuss and study, qq:1750587828

[Original] Access database using Fmdb