Ios - SQLite database - Moment For Technology

concept

1.SQLite is a lightweight relational database, originally intended for embedded systems, with very few resources. In iOS, just add the libsqlite3.0 dependency and introduce the sqlite3.h header. 2.SQLite is a typeless database and can store any type of data. It is perfectly valid for SQLite to specify no type for fields.

Basic knowledge of

Constraints on SQLite fields

Not null — Not null

Foreign key check — check to make sure that all values in a column meet certain conditions default — default autoincrement — Autoincrement variable. The field data can be incremented by 1 if it is an integer

A primary key

1. Each record in a table has a primary key, like an ID number, and conversely each primary key corresponds to a data record. Therefore, the primary key must be unique.

2. The primary key is also an index, so it is fast to find records using the primary key. 3. In the relational type library, the primary key of one table can act as the foreign key of another table. So, the relationship between these two tables is established by this key. 4. The primary key is usually an integer or a string, as long as it is unique. In SQLite, the value of this column can grow automatically if the primary key is of integer type.

SQLite approximate type rules

To maximize compatibility between SQLite and other databases, SQLite supports the idea of “type approximation,” where column type approximation refers to the recommended type for storing data on a column.

If the type string contains “int”, the related type of the field is INTEGER

If the type string contains “char”, “clob”, or “text”, the field has a genetic type of text. For example, VARCHAR if the type string contains “blob”, the field has a genetic type of None. If the type string contains “real”, “floa”, or “doub”, The genetic type of the field is real; otherwise, the genetic type of the field is NUMERIC.

SQLite statement

1. Create a table (create table) 2. Database update command (updata) 4. Database delete command (delete) 5. Database retrieval command (select)

SQLite method

Sqlite3 *db Database handle, similar to file handle
Sqlite3_stmt * STMT This is the ODBC equivalent of the Command object used to hold compiled SQL statements
sqlite3_open()

Open the database. Returns an int. In fact, most methods using the libsqlite3 framework return an int that represents the corresponding result state of the method execution.

sqlite3_exec()

Execute non-query SQL statements. The first parameter is the sqlite3 pointer that successfully performed the open database operation, the second parameter is the SQL statement to execute, and the last parameter is an error message string.

stmt

Is a data location pointer that marks the location of data queried into a database.

sqlite3_prepare_v2()

Prepares a database query with one reference to the database pointer successfully opened, two to the query statement to execute, and three to the address of the sqLITe3_STMT pointer. This method also returns an int value to indicate whether the status was successful.

sqlite3_step()

Moving the STMT pointer will move it line by line. This method returns an int that corresponds to the SQLITE_ROW macro, indicating that there is row data, which can be read through a while loop.

Sqlite3_close () closes the database file
sqlite3_column_XXX()

Sqlite3_column_XXX () has a set of methods for each column in a row, depending on the data type. The first argument to sqlite3_column_XXX() is the STMT pointer and the second argument is the column number.

There are also a series of functions to retrieve data from recordset fields, such as:

-sqlite3_column_text() The value is of the text type. -sqlite3_column_blob() Sets the bloB type. -sqlite3_column_int() sets the int type

The sqlite3_Finalize () method closes the STMT pointer.

Sqlite in iOS

1. Import libsqlite3

Libsqlite3 is a system library that operates on sqLite databases. You need to import libsqlite3 before using it.

Build Phases: Create a Link Binary With Libraries, click the + sign, search for libsqlite3.0, and import it into the project.

2. Create a DB class to perform database operations

Import header file

Create a DB class to perform operations on the database and import the header file in the file: #import <sqlite3.h>

Set the database file path:

static sqlite3 *db = nil; // Create a database pointer? ? ? NSString *databaseFilePath = [NSHomeDirectory() stringByAppendingPathComponent:@"Desktop/mydb.sqlite"];Copy the code

Common methods of encapsulation

1. Open the database

- (void)openSqlite { if(db ! = nil) { return; } / / access to the file path nsstrings * STR = [NSSearchPathForDirectoriesInDomains (NSDocumentDirectory NSUserDomainMask, YES) firstObject]; NSString *strPath = [str stringByAppendingPathComponent:@"my.sqlite"]; NSLog(@"%@",strPath); Int result = sqlite3_open([strPath UTF8String], &db); int result = sqlite3_open([strPath UTF8String], &db); If (result == SQLITE_OK) {NSLog(@" database opened successfully "); } else {NSLog(@" database failed to open "); }}Copy the code

// This method opens the database and returns a pointer to the database + (sqlite3 *)open {if (db! = nil) { return db; / / lazy loading path} / / get the Documents nsstrings * docPath = [NSSearchPathForDirectoriesInDomains (NSDocumentationDirectory, NSUserDomainMask, YES) lastObject]; / / generate database file path in the sandbox nsstrings * sqlPath = [docPath stringByAppendingPathComponent: @ "studentDB. Sqlite"]. // Create a file management object NSFileManager *fileManager = [NSFileManager defaultManager]; // Check whether the database file exists in the sandbox path. If not, copy the database file. If ([fileManager fileExistsAtPath:sqlPath] == NO) {// Obtain the path of the database file in the package NSString *filePath = [[NSBundle mainBundle] pathForResource:@"studentDB" ofType:@"sqlite"]; FileManager copyItemAtPath:filePath toPath:sqlPath error:nil]; // The first parameter is the path to copy the file. The second parameter is the target path to copy the file. } // The first argument is the path to the database (because you need C strings, not NSStrings, // The second argument is a pointer to a pointer sqlite3_open([sqlPath UTF8String], &db); return db; }Copy the code

2. Create a table

NSString *sqlite = [NSString stringWithFormat:@"create table if not exists 'student' "-(void)createTable {//1.  ('number' integer primary key autoincrement not null,'name' text,'sex' text,'age'integer)"]; //2. Execute sqlite statement char *error = NULL; Int result = sqlite3_exec(db, [sqlite UTF8String], nil, nil, &error); // Sqlite3_exec (db, [sqLite UTF8String], nil, nil, &error); If (result == SQLITE_OK) NSLog(@" create table successfully "); Else {NSLog(@" create table failed "); sqlite3_free(error); // Empty the error string after each use, so that the next use}}Copy the code

Description:

Create table if not exists is fixed and is created if the table does not exist. Student () represents a table, student is the name of the table, and the field information is in parentheses. The first word in each field is the field name, the second word is the data type, the primary key is the primary key, and the autoincrement is the autoincrement.Copy the code

3. Add data

-(void)addStudent:(student *)stu { //1. NSString *sqlite = [NSString stringWithFormat:@"insert into student(number,name,age,sex) values ('%ld','%@','%@','%ld')",stu.number,stu.name,stu.sex,stu.age]; //2. Execute sqlite statement char *error = NULL; Int result = sqlite3_exec(db, [sqlite UTF8String], nil, nil, &error); // Sqlite3_exec (db, [sqLite UTF8String], nil, nil, &error); If (result == SQLITE_OK) {NSLog(@" add data successfully "); } else {NSLog(@" failed to add data "); sqlite3_free(error); }}Copy the code

4. Delete data

-(void)delete:(student*)stu { //1. NSString *sqlite = [NSString stringWithFormat:@"delete from student where number = '%ld'",stu.number]; //2. Execute sqlite statement char *error = NULL; Int result = sqlite3_exec(db, [sqlite UTF8String], nil, nil, &error); // Sqlite3_exec (db, [sqLite UTF8String], nil, nil, &error); If (result == SQLITE_OK) {NSLog(@" delete data successfully "); } else {NSLog(@" failed to delete data %s",error); sqlite3_free(error); }}Copy the code

5. Modify data

NSString *sqlite = [NSString stringWithFormat:@"update student set name = '%@',sex = '%@',age = '%ld' where number = '%ld'",stu.name,stu.sex,stu.age,stu.number]; //2. Execute sqlite statement char *error = NULL; Int result = sqlite3_exec(db, [sqlite UTF8String], nil, nil, &error); // Sqlite3_exec (db, [sqLite UTF8String], nil, nil, &error); If (result == SQLITE_OK) {NSLog(@" SQL "); } else {NSLog(@" failed to modify data "); }}Copy the code

6. Query all data

-(NSMutableArray*)selectWithStu { NSMutableArray *array = [[NSMutableArray alloc] init]; NSString *sqlite = [NSString stringWithFormat:@"select * from student"]; Sqlite3_stmt * STMT = NULL; Int result = sqlite3_prepare(db, sqlite.utf8String, -1, &STMT, NULL); If (result == SQLITE_OK) {NSLog(@" query successful "); if (result == SQLITE_OK) {NSLog(@" query successful "); While (sqlite3_step(STMT) == SQLITE_ROW) {student *stu = [[student alloc] init]; Stu. number = sqlite3_column_int(STMT, 0); Stu. name = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(STMT, 1)]; Stu. Sex = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(STMT, 2)]; Stu. age = sqlite3_column_int(STMT, 3); [array addObject:stu]; }} else {NSLog(@" query failed "); } //5. Close the adjoint pointer SQlite3_Finalize (STMT); return array; }Copy the code

7. Close the database

-(void)closeSqlite { int result = sqlite3_close(db); If (result == SQLITE_OK) {NSLog(@" database closed successfully "); } else {NSLog(@" database shutdown failed "); }}Copy the code

+ (void)close { sqlite3_close(db); db = nil; // Null pointer to database}Copy the code

3. Create a student class

Encapsulate methods in student classes, using SQLite

Create a table

NSString * SQL = @"create table if not exists stu (ID INTEGER primary) Key, name text not null, gender text default 'male ')"; Sqlite3 *db = [db open]; Int result = sqlite3_exec(db, sql.utf8String, nil, nil, nil); If (result == SQLITE_OK) {NSLog(@" create table successfully "); } else {NSLog(@" create table failed "); } [DB close]; // Close the database}Copy the code

Gets all students saved in the table

+ (NSArray *)allStudents { sqlite3 *db = [DB open]; Sqlite3_stmt * STMT = nil; // Create a statement object NSMutableArray *mArr = nil; This function creates a statement object that does not execute the SQL statement. The created statement object holds the associated database, and the executed SQL statement. Int result = sqLITe3_prepare_v2 (db, "select * from Students", -1, &stMT, nil); int result = sqLITe3_prepare_v2 (db, "select * from Students", -1, &stMT, nil); If (result == SQLITE_OK) {// Create space for array mArr = [NSMutableArray arrayWithCapacity:0]; Sqlite3_step () is only used for query statements. If sqlite3_step() is used for query statements, the result of sqlite3_step() indicates that there is still data in the result set, and the next result is automatically skipped. If it is already the last one, execute sqlite3_step() again. While (sqlite3_step(STMT) == SQLITE_ROW) {// The first argument is the statement object, the second argument is the subscript of the field, Int ID = sqlite3_column_int(STMT, 0); const unsigned char *cName = sqlite3_column_text(stmt, 1); const unsigned char *cGender = sqlite3_column_text(stmt, 2); NSString *name = [NSString stringWithUTF8String:(const char *)cName]; NSString *gender = [NSString stringWithUTF8String:(const char *)cGender]; Student *student = [Student studentWithID:ID name:name gender:gender]; [mArr addObject:student]; // Add student information to array}} sqlite3_Finalize (STMT); Return mArr; }Copy the code

Find the student with the corresponding ID

+ (Student *)findStudentByID:(int)ID { sqlite3 *db = [DB open]; Sqlite3_stmt * STMT = nil; // create a statement object Student * Student = nil; Int result = sqlite3_prepare_v2(db, "select * from Students where ID =? , -1, &stmt, nil); If (result == SQLITE_OK) {// If (result == SQLITE_OK) {// If (result == SQLITE_OK) {// If (result == SQLITE_OK) {// If (result == SQLITE_OK) { Sqlite3_bind_int (STMT, 1, ID) sqlite3_bind_int(STMT, 1, ID); If (sqlite3_step(STMT) == SQLITE_ROW) {const unsigned char *cName = sqlite3_column_text(STMT, 1); const unsigned char *cGender = sqlite3_column_text(stmt, 2); NSString *name = [NSString stringWithUTF8String:(const char *)cName]; NSString *gender = [NSString stringWithUTF8String:(const char *)cGender]; student = [Student studentWithID:ID name:name gender:gender]; } } sqlite3_finalize(stmt); Return student; }Copy the code

Adds a record to the table

+ (void)insertStudentWithID:(int)ID name:(NSString *)name gender:(NSString *)gender { sqlite3 *db = [DB open]; sqlite3_stmt *stmt = nil; int result = sqlite3_prepare_v2(db, "insert into Students values(? ,? ,?) ", -1, &stmt, nil); If (result == SQLITE_OK) {// bind sqlite3_bind_int(STMT, 1, ID); sqlite3_bind_text(stmt, 2, [name UTF8String], -1, nil); sqlite3_bind_text(stmt, 3, [gender UTF8String], -1, nil); // Insert, unlike query, returns no value sqlite3_step(STMT); } sqlite3_finalize(stmt); // Release statement object}Copy the code

Updates the name and gender of the specified ID

+ (void)updateStudentName:(NSString *)name gender:(NSString *)gender forID:(int)ID { sqlite3 *db = [DB open]; sqlite3_stmt *stmt = nil; int result = sqlite3_prepare_v2(db, "update Student set name = ? , gender = ? where ID = ?" , -1, &stmt, nil); if (result == SQLITE_OK) { sqlite3_bind_text(stmt, 1, [name UTF8String], -1, nil); sqlite3_bind_text(stmt, 2, [gender UTF8String], -1, nil); sqlite3_bind_int(stmt, 3, ID); sqlite3_step(stmt); } sqlite3_finalize(stmt); }Copy the code

Deletes a student based on the specified ID

+ (void)deleteStudentByID:(int)ID { sqlite3 *db = [DB open]; sqlite3_stmt *stmt = nil; int result = sqlite3_prepare_v2(db, "delete from Students where ID = ?" , -1, &stmt, nil); if (result == SQLITE_OK) { sqlite3_bind_int(stmt, 1, ID); sqlite3_step(stmt); } sqlite3_finalize(stmt); }Copy the code

Pay attention to

To write to the database, the string can be char. When the char type is retrieved from the database, garbled characters will appear when the char type has Chinese characters. This is because the database uses ASCII encoding by default. So to retrieve Chinese from the database correctly, you need to use NSString to receive the string retrieved from the database.

Check if a table exists

(BOOL)isExistTable:(NSString *)tablename{
    if ([_dataBase open]) {
        FMResultSet *rs = [_dataBase executeQuery:@"select count(*) as 'count' from sqlite_master where type ='table' and name = ?", tablename];
        while ([rs next]){
            NSInteger count = [rs intForColumn:@"count"];
            if (0 == count){
                return NO;
            }
            else{
                return YES;
            }
        }
        return NO;
    }
    return NO;
}
Copy the code

Other references

Blog.csdn.net/dolacmeng/a… www.jianshu.com/p/0b9b78e70… www.jianshu.com/p/cf76e2e81…

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Ios – SQLite database

concept

Basic knowledge of

SQLite approximate type rules

SQLite statement

SQLite method

Sqlite in iOS

1. Import libsqlite3

2. Create a DB class to perform database operations

3. Create a student class

Pay attention to

Other references

Ios – SQLite database

concept

Basic knowledge of

SQLite approximate type rules

SQLite statement

SQLite method

Sqlite in iOS

1. Import libsqlite3

2. Create a DB class to perform database operations

3. Create a student class

Pay attention to

Other references

Related Posts

IOS [M1 fit?] UIKeyCommand accepts external keyboard input

Cache_t analysis like OC low-level exploration

IOS – Set the rotation direction of the app screen and disable landscape screen