IOS basics 7: Databases

IOS basics 7: Databases

In today's smartphone world, many of our data is stored on mobile phones, such as memos, address books, etc. Tell you, the search and storage speed of various file formats is not as fast as that of the database. After all, the database is saved in a special format. Whether it is ios or android, the embedded database SQLite is used internally as a solution. Because it is much smaller than other commercial databases.

SQLite is a lightweight database that does not require any settings or any server. This makes it especially suitable for embedded devices with limited memory.

Sqlite creates databases, tables, and inserts and views data.

IOS sqlite database operations. Steps:

First join the sqlite Development Library libsqlite3.dylib,

Create or open a database,

Create a data table,

Insert data,

Query and print data

1. Create a project sqliteDemo and add the library libsqlite3.dylib that uses sqlite.

2. sqlite Method

Sqlite3 * db, database handle, similar to FILE handle

Sqlite3_stmt * stmt, which is equivalent to the Command object of ODBC, used to save compiled SQL statements
Sqlite3_open () to open the database. It is created when no database exists.
Sqlite3_exec (): executes non-query SQL statements.
Sqlite3_step (): After sqlite3_prepare is called, use this function to move in the record set.
Sqlite3_close () to close database files
There are also a series of functions used to obtain data from record set fields, such
Sqlite3_column_text (), which is text-type data.
Sqlite3_column_blob (), fetch blob-type data
Sqlite3_column_int (), which is an int-type data.

3. Obtain the sandbox directory and create or open a database.

Add a member variable to the viewController. h header file and include the header file sqlite3.h

[Cpp]View plaincopy

1. # Import
2. # Import
5. @ Interface ViewController: UIViewController
6. {
7. Sqlite3 * db;
8. }
9. @ End

   In the. m file definition macro, which will be used later

   
   
   1. # Define DBNAME @ "personinfo. sqlite"
   2. # Define NAME @ "name"
   3. # Define AGE @ "age"
   4. # Define ADDRESS @ "address"
   5. # Define TABLENAME @ "PERSONINFO"
      
      
      1. NSArray * paths = NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES );
      2. NSString * documents = [paths objectAtIndex: 0];
      3. NSString * database_path = [documents stringByAppendingPathComponent: DBNAME];
      5. If (sqlite3_open ([database_path UTF8String], & db )! = SQLITE_ OK ){
      6. Sqlite3_close (db );
      7. NSLog (@ "database opening failed ");
      8. }
         

         Sqlite3_open. If the data does not exist, it is created. Run. This shows the database files in the sandbox directory. (For details about how to open the sandbox directory of the simulator, refer to iOS sandbox and file operations (I ))

         
         

         
         

         4. Create a data table

         Create an independent method to execute SQL statements. Pass in SQL statements to execute SQL statements.

         
         
         1. -(Void) execSql :( NSString *) SQL
         2. {
         3. Char * err;
         4. If (sqlite3_exec (db, [SQL UTF8String], NULL, NULL, & err )! = SQLITE_ OK ){
         5. Sqlite3_close (db );
         6. NSLog (@ "database operation data failed! ");
         7. }
         8. } Create a data table PERSONINFO statement
            
            
            1. NSString * sqlCreateTable = @ "create table if not exists personinfo (id integer primary key autoincrement, name TEXT, age INTEGER, address TEXT )";
            2. [Self execSql: sqlCreateTable]; run the program and create a data table. How can I know that a data table has been created? Let's use the Sqlite Manager plug-in tool of Firefox to open the database file. You can install this plug-in Firefox. Open

               
               

               All four fields are in the table.

               5. Insert data:

               <喎?http: www.bkjia.com kf ware vc " target="_blank" class="keylink"> VcD4KCgoKPGJyPgoKCgoKPG9sIHN0YXJ0PQ = "1" class = "dp-cpp">

            3. NSString * sql1 = [NSString stringWithFormat:
            4. @ "Insert into '% @' ('% @', '% @', '% @') VALUES ('% @', '% @', '% @')",
            5. TABLENAME, NAME, AGE, ADDRESS, @ "Zhang San", @ "23", @ "Xicheng district"];
            7. NSString * sql2 = [NSString stringWithFormat:
            8. @ "Insert into '% @' ('% @', '% @', '% @') VALUES ('% @', '% @', '% @')",
            9. TABLENAME, NAME, AGE, ADDRESS, @ "la ", @ "20", @ "Dongcheng District"];
            10. [Self execSql: sql1];
            11. [Self execSql: sql2]; run the program, insert two pieces of data, and use the sqlite tool of Firefox to view

                
                

                6. query the database and print data
                
                
                1. NSString * sqlQuery = @ "SELECT * from personinfo ";
                2. Sqlite3_stmt * statement;
                4. If (sqlite3_prepare_v2 (db, [sqlQuery UTF8String],-1, & statement, nil) = SQLITE_ OK ){
                5. While (sqlite3_step (statement) = SQLITE_ROW ){
                6. Char * name = (char *) sqlite3_column_text (statement, 1 );
                7. NSString * nsNameStr = [[NSString alloc] initwithuf8string: name];
                9. Int age = sqlite3_column_int (statement, 2 );
                11. Char * address = (char *) sqlite3_column_text (statement, 3 );
                12. NSString * nsAddressStr = [[NSString alloc] initwithuf8string: address];
                14. NSLog (@ "name: % @ age: % d address: % @", nsNameStr, age, nsAddressStr );
                15. }
                16. }
                17. Sqlite3_close (db); print the result:
                    [Cpp]View plaincopy
                    1. 13:25:32. 205 sqlitDemo [3587: f803] name: Zhang San age: 23 address: Xicheng District
                    2. 13:25:32. 206 sqlitDemo [3587: f803] name: la age: 20 addh