An explanation of the SQLite data store for IOS

In iOS development often need to store data, for a relatively small amount of data can take the form of file storage, such as the use of plist files, archives, etc., but for a large number of data, you need to use the database, in iOS development database storage can directly access the database through SQL You can also map object relationships through ORM, and you can also choose to use a third-party framework to perform operations on the database. Here, mainly to explain the first way, SQLite.

Sqlite

There are a lot of databases, divided into heavyweight and lightweight, mobile device memory is relatively small, so you need to choose a lightweight database. Using databases in application development on mobile devices, the main trend is sqlite. SQLite supports cross-platform, although it is a lightweight database, but it is very powerful, its function as many large relational databases.

SQLite is based on the C language development of lightweight database, so you need to use C language syntax for database operations, access, not directly using the OC language to access the database. In addition, SQLite uses Dynamic data types, even if one type is defined at creation time, other types can be stored in the actual operation, but it is recommended to use the appropriate type when building the library, especially when the application needs to consider cross-platform scenarios.

SQLite can be used directly in the code, but in the process of development, it is best to install a tool that can open the database directly to verify that the operation of the database is correct and easy to debug the program. You can go to the official website of SQLite to download the command line tool under Mac OSX system, can also use similar to Sqlitemanager, Mesasqlite and other tools.

The use of the database, mainly including the opening and closing of the database, the creation of tables, the data in the database for the increase and deletion and update, and so on, in fact, usually speaking of SQL statements, SQLite SQL syntax is not much different, so here for the contents of the SQL statement not too much to repeat, You can refer to other SQL related content, here, we will explain in detail the use of SQLite in iOS development.

SQLite Steps to use:

1. Import the Libsqlite3.0.dylib framework into the project

2. Get the path to the database, usually get the document folder path in the sandbox, and then splice the database path

3. To create or open a database, it should be noted that the use of the C language when open, so you need to first convert the path to C string, and then open the database through Sqlite3_open (), if the file exists directly open, otherwise created and opened. Depending on the Sqlite3_open () return value, you can use the resulting Sqlite3 type object to perform additional operations on the database if it is successfully opened.

4. Execute a written SQL statement, operate the database, execute the SQL statement there are two, one is no return value statements, such as Create, add, delete, etc., is a return value of the statement, such as query.

(1). For statements that have no return value (such as additions, deletions, modifications, and so on) directly through the Sqlite3_exec () function, you can encapsulate a method for operations other than queries

(2). For statements that have a return value, the SQL statement is evaluated first through SQLITE3_PREPARE_V2 (), and then each row of data in the query result is fetched by Sqlite3_step (), which can be used for each row of data through the corresponding sqlite3_ The Column_ type () method obtains the data for the corresponding column, looping through the loop until the traversal is complete. Of course, the last thing you need to do is release the handle.

5. SQLite operation is a persistent connection, there is no need to manage the database connection during the whole operation, if used, you can choose to close the database manually by Sqlite3_close ()

SQLite using code

Import Frame,

viewcontroller.m//jrsqlite Query 3////Created by jerehedu on 15/6/16.//Copyright (c) 2015 jerehedu.  All rights reserved.//#import "ViewController.h" #import <sqlite3.h> @interface Viewcontroller () {sqlite3 *db;        Declare database object} @end @implementation viewcontroller-(void) viewdidload {[Super viewdidload];    Get the database path//Get the Document folder path in the sandbox nsstring *dbpath = [self getuserdocumentpath];        Stitching gets the database path nsstring *sqlitepath = [DbPath stringbyappendingpathcomponent:@ "Test.sqlite"];    Create or open a database const char *p = [Sqlitepath utf8string];    int res = Sqlite3_open (P, &db);             if (RES==SQLITE_OK) {NSLog (@ "db open"); < a > CREATE table nsstring *sql = @ "CREATE table if not exists temps (t_id integer primary key Autoincrement,t_name V        Archar (20)) ";        if ([self execnoquerywithsql:sql]) {NSLog (@ "table temps is created"); }//< two > Insert data nsstring *insert_sql = @ "insert into temps (T_name) ValUEs (' pear ') ";        if ([self execnoquerywithsql:insert_sql]) {NSLog (@ "table Insert");        }//< three > Delete data nsstring *delete_sql = @ "Delete from temps where t_id=2";        if ([self execnoquerywithsql:delete_sql]) {NSLog (@ "table Delete");        }//< four > Modify data nsstring *update_sql = @ "Update temps set t_name= ' iOS ' where t_id=1 ';        if ([self execnoquerywithsql:update_sql]) {NSLog (@ "table Update");        }//< five > query simple data 1 nsstring *select_sql1 = @ "SELECT * from Temps where t_id=1";        sqlite3_stmt *STMT1 = [self EXECQUERYWITHSQL:SELECT_SQL1]; while (Sqlite3_step (stmt1) = = Sqlite_row) {//Select data by the type of the column, the number of columns starts with 0 int t_id = sqlite3_column_int (stmt1            , 0);            Const unsigned char *t_name = Sqlite3_column_text (STMT1, 1);            NSString *name = [NSString stringwithutf8string: (char*) t_name];     NSLog (@ "%i%@", t_id,name);   }//Release stmt statement sqlite3_finalize (STMT1); < five > Query data 2 parameterized SQL statements find id>2 and the name begins with P//with?        placeholder int seachId2 = 2;        NSString *seach_name = @ "p%"; NSString *seach_sql = @ "SELECT * from temps where t_id>?        And t_name like? "; sqlite3_stmt *STMT6 = [self execquerywithsql:seach_sql andwithparams:@[[nsnumber numberwithint:seachid2],seach_name]                ];            Ready to execute (equivalent to clicking Run Query), execution time is a row of execution while (Sqlite3_step (stmt6) = = Sqlite_row) {//According to the type of the column selected data, the number of columns starting from 0            int t_id = sqlite3_column_int (stmt6, 0);            Const unsigned char *t_name = Sqlite3_column_text (STMT6, 1);            NSString *name = [NSString stringwithutf8string: (char*) t_name]; NSLog (@ "...        >>>>>>...%i%@ ", t_id,name);    } sqlite3_finalize (STMT6); }//Close database Sqlite3_close (db);} #pragma mark-Get sandbox sandbox inside Document folder path-(NSString *) getuserdocumentpath{Nsarray *path  = Nssearchpathfordirectoriesindomains (NSDocumentDirectory, Nsuserdomainmask, YES);    NSString *documentpath = [path firstobject]; return documentpath;}      #pragma mark-Performs a database operation method other than lookup-(BOOL) Execnoquerywithsql: (nsstring*) sql{/* Execute parameter 1:sqlite3 object parameter 2:c form of SQL statement  Parameter 3: callback function parameter 4: parameter parameter of callback function 5: Error message (can char type pointer accepts error message, used for troubleshooting) */if (sqlite3_exec (DB, [SQL Utf8string], NULL,    NULL, NULL) = = SQLITE_OK) {return YES; } return NO;    #pragma mark returns a query result set (simple without parameters)-(SQLITE3_STMT *) Execquerywithsql: (nsstring*) sql{//executes the SQL statement and returns the resulting statement sqlite3_stmt *stmt; /* The SQL statement that prepares the query (equivalent to writing the query statement) parameter 3:sql statement length, usually denoted by 1 (the system will automatically calculate), or the Strlength function can be used to calculate Parameters 4:sql_stmt objects (objects executed) parameter 5:    SQL statement not executed */int pre_res = SQLITE3_PREPARE_V2 (db, [SQL Utf8string],-1, &stmt, NULL);    If ready to succeed if (pre_res = = SQLITE_OK) {return stmt; } return NULL; #pragma mark-Returns the query result set (with parameters)-(SQLITE3_STMT *) Execquerywithsql: (NSString *) SQL Andwithparams: (Nsarray *) params{sqlite3_stmt *stmt;    int pre_res = SQLITE3_PREPARE_V2 (db, [SQL Utf8string],-1, &stmt, NULL);  if (pre_res = = SQLITE_OK) {//parameter, loop binding parameter if (Params!=nil) {for (int i=0; i<params.count; i++)                {id obj = params[i];                    The data type of the binding may be nsstring or nsnumber, or the data is empty, judging if (Obj==nil) {//If the data is nil                Sqlite3_bind_null (stmt, i+1); } else if ([obj respondstoselector: @selector (objctype)]) {//Current bound data type bit n Snumber//nsnumber to judge the packing is int? Longint? Shortint?                    Float?double?                     /* STRSTR (parameter 1, parameter 2) (STRSTR () C function searches for the first occurrence of a string in another string, the function returns the address of the first matching string, and cannot find the return null)                    Determines the index of the character in Parameter 1 that appears in the string char* of parameter 2 [obj Objctype] If obj is an int return string I/* if (Strstr ("ilsils", [obj Objctype])) {/*                         Binding parameters If there is a where parameter 1:sqlite_stmt object (statement result set) parameter 2: Placeholder index starting from 1 Parameter 3: The true parameter of the replacement placeholder */Sqlite3_bind_int (STM                    T, i+1, [obj intvalue]); } else if (Strstr ("FdFD", [obj Objctype])) {Sqlite3_bind_dou                    BLE (stmt, i+1, [obj doublevalue]);                    } else {stmt = nil;                    }} else if ([obj respondstoselector: @selector (utf8string)]) { The current bound data type bit nsstring determines if there is a utf8string method//Replace the placeholder index with bind starting with 1 Sqlite3_bind_text (s                TMT, i+1, [obj utf8string],-1, NULL);                } else {stmt = nil;  }} return stmt;      }} return NULL;} -(void) didreceivememorywarning {[Super didreceivememorywarning];} @end

Inquiries or technical exchanges, please join the official QQ Group: (452379712)

Jerry Education
Source:http://blog.csdn.net/jerehedu/
This article is the copyright of Yantai Jerry Education Technology Co., Ltd. and CSDN Common, welcome reprint, but without the author's consent must retain this paragraph statement, and in the article page obvious location to the original link, otherwise reserves the right to pursue legal responsibility.

An explanation of the SQLite data store for IOS