IOS basic tutorial SQLite database

Time:2020-9-24

The student list management interface realizes the operation of adding, deleting and modifying the database

The most basic database operation is to add, delete, change, query four functions
IOS basic tutorial SQLite database

Modifying the data is divided into several steps in the figure below
IOS basic tutorial SQLite database

Step 1 SQLite3_ Open open database
Step 2 SQLite3_ prepare_ V2 preprocessing SQL statement operations
Step 3 SQLite3_ bind_ Textfunction binding parameters
Step 4 SQLite3_ Step function executes SQL statement
Step 5 SQLite3_ Finalize and SQLite3_ Close releases resources

We create a new sqlmanager management class to encapsulate all the basic database operation methods. The following figure shows all the files of the demo project.
IOS basic tutorial SQLite database

Here is the code section:

Sqlmanager. H file

#import <Foundation/Foundation.h>
#import "sqlite3.h"
#import "StudentModel.h"

@interface SQLManager : NSObject{

    sqlite3 * db;
}

+(SQLManager *)shareManager;

//Inquiry
- (StudentModel *)searchWithIDNum:(StudentModel *)model;

//Insert
-(int)insert:(StudentModel *)model;

//Delete
-(void)remove:(StudentModel *)model;

//Modification
-(void)modify:(StudentModel *)model;

@end

Sqlmanager. M file

#import "SQLManager.h"

@implementation SQLManager

//The purpose of defining a macro is: when we operate a database, it is equivalent to processing a local file. First of all, we need to obtain the file path and splice its name. Therefore, in order to make it convenient for us to call this file later, we should take the file name first
#define kNameFile (@"Student.sqlite")

//Create a single instance
static SQLManager * manager = nil;

+(SQLManager *)shareManager{

    static dispatch_once_t once;
    dispatch_once(&once,^{

        manager = [[self alloc] init];
        [manager createDataBaseTableIfNeeded];
    });
    return manager;
}

//Get the full path of the database
-(NSString *)applicationDocumentsDirectoryFile{

    NSArray * paths =NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString * documentDirectory = [paths firstObject];
    NSString * filePath = [documentDirectory stringByAppendingPathComponent:kNameFile];
    return filePath;
}

//Define a function again. The next operation is to create a database. Before database operation, make sure that the database exists. If it does not exist, it must be created
- (void)createDataBaseTableIfNeeded{

    //Although our database is built into SQLite, it still belongs to a third party. If you want to use its class library during development, you need to configure and add it
    //Namelist configuration file > targets > General > linked frameworks and libraries, click the + sign, search for the book sqlit, and select libsqlite3.tbd. Then you can proceed with the subsequent operations.
    NSString * writetablePath =[self applicationDocumentsDirectoryFile];
    Nslog (@ "the address of the database is% @", writetablepath);

    //Open database

    //The full path of the first parameter database file
    //The second parameter is the database database object
    if (sqlite3_open([writetablePath UTF8String], &db) != SQLITE_OK) {

        //SQLITE_ OK is a constant defined by apple for us. If it is OK, it means that our database has been opened successfully

        //Failure
        //Database shutdown
        sqlite3_close(db);
        Nsasert (no, @ "database open failed! "); // throw error message

    }else{

        //Success
        char * err;
        NSString * createSQL =[NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS StudentName (idNum TEXT PRIMARYKEY, name TEXT);"];
        //Execution function of SQLite
        /*
         First parameter DB object
         Second parameter statement
         The third and fourth parameters callback function and callback function parameters
         The fifth parameter is an error message
         */
        if (sqlite3_exec(db, [createSQL UTF8String], NULL, NULL, &err) !=SQLITE_OK) {

            //Failure
            //Database shutdown
            sqlite3_close(db);
            Nsassert1 (no, @ "failed to create table! %S ", ERR); // throw error message
        }
        sqlite3_close(db);
    }

}

//Query data
/*
 1. Use SQLite3_ prepare_ V2 function preprocessing SQL statement
 2. Use SQLite3_ bind_ Textfunction binding parameters
 3. Use SQLite3_ The step function executes the SQL statement and traverses the result set
 4. Use SQLite3_ column_ Text and other functions extract field data
 */
- (StudentModel *)searchWithIDNum:(StudentModel *)model{

    NSString * path =[self applicationDocumentsDirectoryFile];
    if (sqlite3_open([path UTF8String], &db) != SQLITE_OK) {

        sqlite3_close(db);
        Nsasert (no, @ "failed to open database!");

    }else{

        NSString * qsql [email protected]"SELECT idNum,name FROM StudentName where idNum = ?";
        sqlite3_ Stmt * statement; // statement object

        //First parameter: database object
        //Second parameter: SQL statement
        //Third parameter: length of execution statement - 1 refers to the full length
        //Fourth parameter: statement object
        //The fifth parameter: the part of the statement that is not executed is null

        //Pretreatment
        if(sqlite3_prepare_v2(db, [qsql UTF8String], -1, &statement, NULL) ==SQLITE_OK){


            //Query database by primary key
            NSString * idNum = model.idNum;
            //First parameter statement object
            //The sequence number of the second parameter parameter to start execution
            //The third parameter is the value we want to bind
            //The length of the string bound to the fourth parameter
            //Pointer fifth parameter null

            //Binding
            sqlite3_bind_text(statement, 1, [idNum UTF8String], -1, NULL);

            //Traversal result set
            /*
             There is a return value of SQLite_ The row constant stands for it
             */
            if (sqlite3_step(statement) == SQLITE_ROW) {

                //Extract data
                /*
                 First: statement object
                 Second: the index of the field
                 */
                char * idNum = (char *)sqlite3_column_text(statement, 0);
                //Data transformation
                NSString * idNumStr =[[NSString alloc]initWithUTF8String:idNum];

                char * name =(char *)sqlite3_column_text(statement, 1);
                NSString * nameStr =[[NSString alloc]initWithUTF8String:name];


                StudentModel * model =[[StudentModel alloc] init];
                model.idNum = idNumStr;
                model.name = nameStr;

                //Release
                sqlite3_finalize(statement);
                sqlite3_close(db);
                return model;
            }
        }
        sqlite3_finalize(statement);
        sqlite3_close(db);
    }
    return nil;
}

//Modification
-(int)insert:(StudentModel *)model{

    NSString * path =[self applicationDocumentsDirectoryFile];
    if (sqlite3_open([path UTF8String], &db) != SQLITE_OK) {
        sqlite3_close(db);
        Nsasert (no, @ "database open failed!");
    }else{

        NSString * sql [email protected]"INSERT OR REPLACE INTO StudentName (idNum, name) VALUES (?,?)";
        sqlite3_stmt * statement;
        //Pretreatment过程
        if (sqlite3_prepare_v2(db, [sql UTF8String], -1, &statement, NULL) ==SQLITE_OK) {

            sqlite3_bind_text(statement, 1, [model.idNum UTF8String], -1, NULL);
            sqlite3_bind_text(statement, 2, [model.name UTF8String], -1, NULL);

            if (sqlite3_step(statement) != SQLITE_DONE) {

                Nsasert (no, @ "insert data failed!");

            }
            sqlite3_finalize(statement);
            sqlite3_close(db);
        }

    }
    return 0;
}

//Delete
-(void)remove:(StudentModel *)model{

    /*
     Step 1 SQLite3_ Open open database
     Step 2 SQLite3_ prepare_ V2 preprocessing SQL statement operation
     Step 3 SQLite3_ bind_ Textfunction binding parameters
     Step 4 SQLite3_ Step function executes SQL statement
     Step 5 SQLite3_ Finalize and SQLite3_ Close releases resources
     */
     NSString * path =[self applicationDocumentsDirectoryFile];
    if (sqlite3_open([path UTF8String], &db) != SQLITE_OK) {

        sqlite3_close(db);
        Nsasert (no, @ "database open failed");
    }else{

        NSString * sql [email protected]"DELETE FROM StudentName where idNum = ?";
        sqlite3_stmt * statement;
        //Pretreatment
        if (sqlite3_prepare_v2(db, [sql UTF8String], -1, &statement, NULL) ==SQLITE_OK) {

            sqlite3_bind_text(statement, 1, [model.idNum UTF8String], -1, NULL);
            sqlite3_bind_text(statement, 2, [model.name UTF8String], -1, NULL);
            if (sqlite3_step(statement) != SQLITE_DONE) {

                Nsasert (no, @ "delete data failed!");
            }
            sqlite3_finalize(statement);
            sqlite3_close(db);
        }
    }

}

Studentmodel. H file

#import <Foundation/Foundation.h>

@interface StudentModel : NSObject

@Property (nonatomic, strong) nsstring * idnum; // student number
@Property (nonatomic, strong) nsstring * name; // student name
@end

Homeviewcontroller. M file

#import "HomeViewController.h"
#import "StudentModel.h"
#import "SQLManager.h"

@interface HomeViewController ()

@Property (nonatomic, strong) nsmutablearray * studentarray; // data source model

@end

#define HomeCellIdentifier (@"StudentCell")

@implementation HomeViewController

- (void)viewDidLoad {
    [super viewDidLoad];

    self.studentArray =[[NSMutableArray alloc]init];
    NSLog(@"%@",self.studentArray);
}

- (void)didReceiveMemoryWarning {
    [super didReceiveMemoryWarning];

}

#pragma mark - Table view data source

- (NSInteger)numberOfSectionsInTableView:(UITableView *)tableView {

    return 1;
}

- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section {

    if (self.studentArray.count >0) {

         return self.studentArray.count;
    }else{

        return 1;
    }
}


- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath {
    UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:HomeCellIdentifier forIndexPath:indexPath];

    //Assign cell to wait
    if(self.studentArray.count > 0){

        StudentModel * model =[self.studentArray objectAtIndex:indexPath.row];
        cell.textLabel.text = model.name;
        cell.detailTextLabel.text = model.idNum;
    }
    return cell;
}


- (BOOL)tableView:(UITableView *)tableView canEditRowAtIndexPath:(NSIndexPath *)indexPath {
    //Support for editing
    return YES;
}

-(CGFloat)tableView:(UITableView *)tableView heightForRowAtIndexPath:(NSIndexPath *)indexPath{

    return 50;
}

-(IBAction)addUserDone:(UIStoryboardSegue *)sender{

    StudentModel * model =[[StudentModel alloc]init];
    model.idNum = @"100";
    StudentModel * result = [[SQLManager shareManager] searchWithIDNum:model];
    NSLog(@"%@",result);
    [self.studentArray addObject:result];
    [self.tableView reloadData];
}

Addviewcontroller. H file

#import <UIKit/UIKit.h>

@interface AddViewController : UIViewController
@property (weak, nonatomic) IBOutlet UITextField *idNumTextField;
@property (weak, nonatomic) IBOutlet UITextField *nameTextField;

@end

Addviewcontroller. M file

#import "AddViewController.h"
#import "SQLManager.h"
#import "StudentModel.h"

@interface AddViewController ()

@end

@implementation AddViewController

- (void)viewDidLoad {
    [super viewDidLoad];
    // Do any additional setup after loading the view.
}

- (void)didReceiveMemoryWarning {
    [super didReceiveMemoryWarning];
    // Dispose of any resources that can be recreated.
}

-(void)prepareForSegue:(UIStoryboardSegue *)segue sender:(id)sender{

    if ([segue.identifier isEqualToString:@"AddUser"]) {

        //Write to database
        StudentModel * model =[[StudentModel alloc]init];
        model.idNum = self.idNumTextField.text;
        model.name = self.nameTextField.text;

        [[SQLManager shareManager] insert:model];
    }
}
@end

Finally, I recommend one of mineIOS communication group:642363427 it’s important to have a common circle and get to know people! There are IOS development, full stack development, welcome to settle in, common progress! (the group will provide free learning books, materials and hundreds of interview questions and answer documents collected by the group leader for free

Recommended Today

Wondertrader releases version v0.5.0

Today (July 28, 2020), wondertrader released the latest version of v0.5.0, along with the latest version of wtpy, v0.5.0 Wondertrader has made the following changes in v0.5.0: High frequency strategy engine officially released。 High frequency strategy engine beforeWonderTraderHowever, due to insufficient testing, it has not been officially released for the time being. It only focuses […]