Use of "IOS" sqlite3 (for further deletion)

Directory:

First, sqlite3 commonly used functions

Second, integrate Sqlite3 into the project, realize the

Three, Package Dbmanager

Four, Demo

I. Common functions and explanations of sqlite3

(1) Sqlite3_open:

Used to create and open a database file, receive two parameters, the first is the name of the database, and the second is the handle to the database. If the database file does not exist, it is created first, then opened, or it is just opened.

(2) Sqlite3_prepare_v2:

Use the formatted string to obtain the SQL PREPARE statement (prepared statement) and then convert to an execution statement that can be SQLite3 recognized. (This function does not actually execute this SQL statement)

(3) Sqlite3_step:

This function executes the prepared statement created by the previous function call, which executes to the position where the first line of the result is available and calls again Sqlite3_setp (), Will Move on to the second line of the result . It is called once when an insert, UPDATE, delete operation is performed, and can be executed more than once when the fetch data is executed. This function cannot be called before Sqlite3_preprare_v2.

(4) Sqlite3_column_count:

Returns the number of columns in a table

(5 ) sqlite3_column_text : 

Returns the contents of the column in text Format (actually the char* type of C). It receives two parameters, the index of the SQLite statement and the column.

(6)Sqlite3_column_name :

Returns the name of the column, with the same argument as the previous function

(7)sqlite3_changes:

Returns the number of rows affected after the statement is executed

(8)Sqlite3_last_insert_rowid:

Returns the ID of the last inserted row

(9)sqlite3_errmsg:

Returns the SQLite error description

(sqlite3_finalize):

prepared statements created by the SQLITE3_PREPARE_V2 function before the memory is removed

(one)sqlite3_close : 

Closes the database connection, which is called after any database data modification is completed, and it frees its stored system resources.

Second, integrate Sqlite3 into the project, realize the1. First you need to add the SQLite3 library to the project, enter SQLite, select Libsqlite3.dylib from the recommended options, for example.


2. Import sqlite3 header file: #import <sqlite3.h>
3. Define macros for easy use later, and declare a sqlite3 handle

#define DBNAME    @"myDB.sqlite"
#define TABLENAME @"PERSONINFO"
#define NAME      @"name"

@interface ViewController (){
    sqlite3 *db;
}

3. Create and open a database file

NSArray * paths = NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES);
   NSString * documents = [paths objectAtIndex: 0];
   NSString * database_path = [documents stringByAppendingPathComponent: DBNAME];
   if (sqlite3_open ([database_path UTF8String], & db)! = SQLITE_OK) {
        sqlite3_close (db);
        NSLog (@ "Failed to open database");
   }

4. Create a new SQL statement to manipulate the database functions

-(void) execSql: (NSString *) sql
{
     char * err;
     if (sqlite3_exec (db, [sql UTF8String], NULL, NULL, & err)! = SQLITE_OK) {
         sqlite3_close (db);
         NSLog (@ "Database operation data failed!");
     }
}

5. Create a data table with a macro definition in Table Name 2 Personinfo

NSString *sqlCreateTable = @"CREATE TABLE IF NOT EXISTS PERSONINFO(peopleInfoID integer primary key, firstname text, lastname text, age integer);";  
[self execSql:sqlCreateTable];  

6. Insert a record with the name Zhang San, age 22 years

NSString *sql = [NSString stringwithformat:@ "insert into peopleinfo values (null, '%@ ', '%@ ',%d) ', @" Zhang ", @" three ", [];  [Self execsql:sql];  

  

	
   


  

7. Querying All Records

NSString *sqlQuery = @"SELECT * FROM PERSONINFO";  
   sqlite3_stmt * statement;  
     
   if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK) {  
       while (sqlite3_step(statement) == SQLITE_ROW) {  
           char *firstName = (char*)sqlite3_column_text(statement, 1);  
           NSString *firstNameStr = [[NSString alloc]initWithUTF8String:firstName];  
             <pre name="code" class="objc">           char *lastName = (char*)sqlite3_column_text(statement, 2);  
           NSString *lastNameStr = [[NSString alloc]initWithUTF8String:lastName];  

int age = Sqlite3_column_int (statement, 3); NSLog (@ "firstname:%@ lastname:%@ age:%d",Firstnamestr, LASTNAMESTR, age);

}} sqlite3_close (db);

Three, package Dbmanager(1)To add a new Dbmanager class: Select File > New > File ..., select Cocoa Touch class, click Next, press action. (2). h file

#import <Foundation / Foundation.h>

@interface DBManager: NSObject

@property (nonatomic, strong) NSMutableArray * arrColumnNames; // store column names
@property (nonatomic) int affectedRows; // Record the number of rows changed
@property (nonatomic) long long lastInsertedRowID; // Record the id of the last inserted row

-(NSArray *) loadDataFromDB: (NSString *) query; // Query
-(void) executeQuery: (NSString *) query; // Insert, update, delete
-(instancetype) initWithDatabaseFilename: (NSString *) dbFilename; // Init method
-(BOOL) createTableWithSql: (const char *) sql_stmt; // New table

@end

(3). m file

#import "DBManager.h"
#import <sqlite3.h> // Import the header file of sqlite3

@interface DBManager ()

@property (nonatomic, strong) NSString * documentsDirectory;
@property (nonatomic, strong) NSString * databaseFilename;
@property (nonatomic, strong) NSMutableArray * arrResults;

@end

@implementation DBManager

-(instancetype) initWithDatabaseFilename: (NSString *) dbFilename {
    self = [super init];
    if (self) {
        // Get the storage path
        NSArray * paths = NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES);
        self.documentsDirectory = [paths objectAtIndex: 0];
        
        //Database name
        self.databaseFilename = dbFilename;
    }
    return self;
}

#pragma mark Create a table
-(BOOL) createTableWithSql: (const char *) sql_stmt {
    BOOL isSuccess = YES;
    // Check if the database file already exists
    NSString * destinationPath = [self.documentsDirectory stringByAppendingPathComponent: self.databaseFilename];
    NSLog (@ "path:% @", destinationPath);
    if (! [[NSFileManager defaultManager] fileExistsAtPath: destinationPath]) {
        sqlite3 * database = nil;
        const char * dbpath = [destinationPath UTF8String];
        if (sqlite3_open (dbpath, & database) == SQLITE_OK) {
            char * errMsg;
            if (sqlite3_exec (database, sql_stmt, NULL, NULL, & errMsg)
                ! = SQLITE_OK)
            {
                isSuccess = NO;
                NSLog (@ "Failed to create table");
            }
            sqlite3_close (database);
        }else{
            isSuccess = NO;
            NSLog (@ "Failed to open / create table");
        }
    }
    return isSuccess;
}

#pragma mark Execute SQL statement
-(void) runQuery: (const char *) query isQueryExecutable: (BOOL) queryExecutable {
    // Create a sqlite3 object
    sqlite3 * sqlite3Database;
    
    // Set the database path
    NSString * databasePath = [self.documentsDirectory stringByAppendingPathComponent: self.databaseFilename];
    
    // Initialize the array that stores the results
    if (self.arrResults! = nil) {
        [self.arrResults removeAllObjects];
        self.arrResults = nil;
    }
    self.arrResults = [[NSMutableArray alloc] init];
    
    // Initialize the array that stores the column names
    if (self.arrColumnNames! = nil) {
        [self.arrColumnNames removeAllObjects];
        self.arrColumnNames = nil;
    }
    self.arrColumnNames = [[NSMutableArray alloc] init];
    
    
    // Open the database
    BOOL openDatabaseResult = sqlite3_open ([databasePath UTF8String], & sqlite3Database);
    if (openDatabaseResult == SQLITE_OK) {
        // Declare a sqlite3_stmt object to store query results
        sqlite3_stmt * compiledStatement;
        
        // Load all data into memory
        BOOL prepareStatementResult = sqlite3_prepare_v2 (sqlite3Database, query, -1, & compiledStatement, NULL);
        if (prepareStatementResult == SQLITE_OK) {
            // whether it is a query
            if (! queryExecutable) {
                // Used to save each row of data
                NSMutableArray * arrDataRow;
                
                // Add the results to arrDataRow line by line
                while (sqlite3_step (compiledStatement) == SQLITE_ROW) {
                    // Initialize arrDataRow
                    arrDataRow = [[NSMutableArray alloc] init];
                    
                    // Get the number of columns
                    int totalColumns = sqlite3_column_count (compiledStatement);
                    
                    // Read and save each column of data
                    for (int i = 0; i <totalColumns; i ++) {
                        // Convert data to char
                        char * dbDataAsChars = (char *) sqlite3_column_text (compiledStatement, i);
                        
                        // The data is not empty and added to arrDataRow
                        if (dbDataAsChars! = NULL) {
                            // Convert char to string.
                            [arrDataRow addObject: [NSString stringWithUTF8String: dbDataAsChars]];
                        }
                        
                        // Save column names (save only once)
                        if (self.arrColumnNames.count! = totalColumns) {
                            dbDataAsChars = (char *) sqlite3_column_name (compiledStatement, i);
                            [self.arrColumnNames addObject: [NSString stringWithUTF8String: dbDataAsChars]];
                        }
                    }
                    
                    // If not empty, save the data of each row to
                    if (arrDataRow.count> 0) {
                        [self.arrResults addObject: arrDataRow];
                    }
                }
            }
            else {
                // Insert, update, delete, etc.
                if (sqlite3_step (compiledStatement) == SQLITE_DONE) {
                    // how many rows have been changed
                    self.affectedRows = sqlite3_changes (sqlite3Database);
                    
                    // last inserted row id
                    self.lastInsertedRowID = sqlite3_last_insert_rowid (sqlite3Database);
                }
                else {
                    // Insert, update, delete and other errors
                    NSLog (@ "DB Error:% s", sqlite3_errmsg (sqlite3Database));
                }
            }
        }
        else {
            // open error
            NSLog (@ "% s", sqlite3_errmsg (sqlite3Database));
        }
        
        // release memory sqlite3_finalize (compiledStatement);
        
     }
    
     // close the database
     sqlite3_close (sqlite3Database);
}

-(NSArray *) loadDataFromDB: (NSString *) query {
     // execute the query
     [self runQuery: [query UTF8String] isQueryExecutable: NO];
    
     // return the query result
     return (NSArray *) self.arrResults;
}

-(void) executeQuery: (NSString *) query {
     // perform insert, update, delete, etc.
     [self runQuery: [query UTF8String] isQueryExecutable: YES];
}

@end  

Four, the use of three Dbmanager to complete a demo, to achieve additions and deletions to check, the effect, such as, source code: http://download.csdn.net/detail/dolacmeng/8816001

Use of "IOS" sqlite3 (for further deletion)