2015.9.29-Data access (SQLite)

Sqlite

Mainstream embedded relational database: Lightweight, cross-platform based C language, in iOS need to use the C language database operations using Dynamic Data types, even when the creation of a type defined, in the actual operation can also store other types, It is generally not necessary to close the connection after establishing a connection using the appropriate type (cross-platform) when building the library (can be closed manually)using the SQLite step, first import the LIBSQLITE3 framework into your project

1. Open Library: Sqlite3_open (), specify a database file save path, if the file exists directly open, otherwise create and open, get a sqlite3 type of object

2. Execute SQL statement 3. If there is no return value (increment), Sqlite3_exec () executes 4. If there is a return value (check), SQLITE3_PERPARE_V2 () syntax detection, sqlite3_step () takes out each row of the query result in turn, Sqlite3_column_ type () method to get the corresponding column loops conveniently similar to a cursor--------------------------------------------------------------------------------------------------------- ----------------------------------Basic Notation

////DbManager.h//DataAccess////Created by Kenshin Cui on 14-3-29.//Copyright (c) 2014 Kenshin Cui. All rights reserved.//#import<Foundation/Foundation.h>#import<sqlite3.h>#import "KCSingleton.h"@interfaceKcdbmanager:nsobjectsingleton_interface (Kcdbmanager);#pragmaMark-Properties#pragmaMark database Reference, using it for database operations@property (nonatomic) sqlite3*database;#pragmaMark-Common methods/** * Open Database * * @param dbname database name*/-(void) Opendb: (NSString *) dbname;/** * Execute SQL with no return value * * @param SQL SQL statement*/-(void) ExecuteNonQuery: (NSString *) SQL;/** * Execute SQL with return value * * @param SQL SQL statement * * @return Query Results*/-(Nsarray *) ExecuteQuery: (NSString *) SQL;@end

The main definition is a Sqlite3 database object, an open database Opendb method A method that executes no return value ExecuteNonQuery method A method that executes a return value ExecuteQuery methodSQLite does not support stored procedures

////DBMANAGER.M//DataAccess////Created by Kenshin Cui on 14-3-29.//Copyright (c) 2014 Kenshin Cui. All rights reserved.//#import "KCDbManager.h"#import<sqlite3.h>#import "KCSingleton.h"#import "KCAppConfig.h"#ifndef kdatabasename#defineKdatabasename @ "Mydatabase.db"#endif@interfaceKcdbmanager ()@end@implementationkcdbmanagersingleton_implementation (Kcdbmanager)#pragmaMark overrides initialization Method-(instancetype) init{Kcdbmanager*Manager; if((manager=[Super Init])    {[Manager opendb:kdatabasename]; }    returnManager;}-(void) Opendb: (NSString *) dbname{//get the database save path, usually save the sandbox documents directoryNSString *directory=[Nssearchpathfordirectoriesindomains (NSDocumentDirectory, Nsuserdomainmask, YES) firstobject]; NSLog (@"%@", directory); NSString*filepath=[directory Stringbyappendingpathcomponent:dbname]; //If a database is opened directly, otherwise created and opened (note that FilePath is a string in OBJC and needs to be converted to the C string type)    if(Sqlite_ok ==sqlite3_open (filepath.utf8string, &_database)) {NSLog (@"database open successfully!"); }Else{NSLog (@"Database open failed!"); }}-(void) ExecuteNonQuery: (NSString *) sql{Char*error; //single-step SQL statement for inserting, modifying, deleting    if(Sqlite_ok!=sqlite3_exec (_database, SQL. Utf8string, NULL, null,&error)) {NSLog (@"An error occurred during the execution of the SQL statement! Error message:%s", error); }}-(Nsarray *) ExecuteQuery: (NSString *) sql{Nsmutablearray*rows=[nsmutablearray array];//Data Rows//evaluate grammatical correctnessSQLITE3_STMT *stmt; //Check Syntax correctness    if(SQLITE_OK==SQLITE3_PREPARE_V2 (_database, SQL. Utf8string,-1, &stmt, NULL)) {        //stepping through SQL statementsintColumncount=Sqlite3_column_count (stmt);

            while (Sqlite_row==sqlite3_step (stmt)) {            nsmutabledictionary *dic=[nsmutabledictionary dictionary];            for (int i=0; i<columncount; i++) {                const char *name= sqlite3_column_name (stmt, I);//Get column name                const unsigned char * Value= Sqlite3_column_text (stmt, I);//Gets the value of a column                dic[[nsstring stringwithutf8string:name]]=[nsstring Stringwithutf8string: (const char *) value];            }            [Rows Addobject:dic];        }    }        Release handle    sqlite3_finalize (stmt);        return rows;}

----------------------------------------------------------------------------------------------------------- --------------------------------
Analysis Override initialization Method Init, execute OPENDB at initialization, open database(name strings are defined by # define)The Opendb method first obtains the save address of the database and then opens

// get the database save path, usually save the sandbox documents directory (this step is to take the sandbox directory)    NSString *directory=[Nssearchpathfordirectoriesindomains (NSDocumentDirectory, Nsuserdomainmask, YES) Firstobject];     // incoming database name string gets the database address    NSString *filepath=[directory Stringbyappendingpathcomponent:dbname]; // If a database is opened directly, otherwise created and opened (note that FilePath is a string in OBJC and needs to be converted to the C string type)    Sqlite3_open (filepath.utf8string, &_database)

executenonquery Execute SQL statement directly, no return value(In fact, a SQLITE_OK with a return value indicates whether the statement executed successfully)

Sqlite3_exec (_database, SQL. Utf8string, NULL, Null,&error)

executequery First check the syntax of the SQL statement, there is a return value, one bar of the read dataFirst of allAn array of data that needs to be stored as a whole(equivalent to C # datatable.rows collection)

Nsmutablearray *rows=[nsmutablearray Array]; // Data Rows

And thenrequires a Sqlite3_stmt object check syntax

  // evaluate grammatical correctness    Sqlite3_stmt *stmt;

Then check the syntax

// Check syntax correctness    if (SQLITE_OK==SQLITE3_PREPARE_V2 (_database, SQL. Utf8string,-1, &stmt, NULL))

And thenLoop Execution(similar to a cursor, one line of reads, and each line is checked for syntax)

 while (Sqlite_row==sqlite3_step (stmt))

And thenDefine a nsmutabledicrtionaryTo accept the data for each row,The column name of each column in this row is the key name of DIC, and the value of DIC is the value of this column ., and then add the DIC to the array that holds the data

 while(sqlite_row==Sqlite3_step (stmt)) {Nsmutabledictionary*dic=[Nsmutabledictionary dictionary];  for(intI=0; i<columncount; i++) {                Const Char*name= Sqlite3_column_name (stmt, i);//Get column name                ConstUnsignedChar*value= Sqlite3_column_text (stmt, i);//get the value of a columnDic[[nsstring stringwithutf8string:name]]=[nsstring stringwithutf8string: (Const Char*) value]; } [Rows Addobject:dic];

At lastto release a handle to the check syntax(Do not know what it means) in practice, the controller does not interact directly with the DB, but rather indirectly through a service layer to complete the data transfer, the service layer encapsulates the model layer of business methods, The controller's call to the model method in the service is actually the operation of the DB. Complete understanding of decoupling---------------------------------------------------------------------------------------------------------- ---------------------------------

Practical application to be continued .....

2015.9.29-Data access (SQLite)