iOS learning SQLite create DATABASE, table, insert view data

IOS SQLite database operation. The steps are:

First join the SQLite development Library Libsqlite3.dylib,

Create a new or open database,

Create a data table,

Insert Data,

Querying data and printing

1. Create a new project Sqlitedemo, add a library using SQLite libsqlite3.dylib

2, the method of SQLite

Sqlite3 *db, database handle, similar to file handle files

Sqlite3_stmt *stmt, an ODBC-equivalent command object that holds compiled SQL statements
Sqlite3_open (), open the database when it is created without a database.
Sqlite3_exec (), execute non-query SQL statement
Sqlite3_step (), use this function to move through the recordset after calling Sqlite3_prepare.
Sqlite3_close (), close the database file
There is also a series of functions that are used to fetch data from a recordset field, such as
Sqlite3_column_text (), take the text type of data.
Sqlite3_column_blob (), fetching blob-type data
Sqlite3_column_int (), take int type of data

3. Get the sandbox directory and create or open the database.

The ViewController.h header file adds a member variable and contains a header file Sqlite3.h

[CPP]View Plaincopy

1. #import <UIKit/UIKit.h>
2. #import <sqlite3.h>
5. @interface Viewcontroller:uiviewcontroller
6. {
7. Sqlite3 *db;
8. }
9. @end

Define macros in. m files, and use them later

[CPP]View Plaincopy

1. #define DBNAME @ "Personinfo.sqlite"
2. #define NAME @ "Name"
3. #define Age @ ' age '
4. #define ADDRESS @ "Address"
5. #define TABLENAME @ "PERSONINFO"

[CPP]View Plaincopy

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 open failed");
8. }

Sqlite3_open, if the data does not exist, it is created. Run. This is in the sandbox directory can see the database file (how to open the emulator sandbox directory please refer to: iOS learning iOS sandbox (sandbox) mechanism and file Operations (a))

4. Create a data table

Create a separate method for executing SQL statements, passing in SQL statements, executing SQL statements

[CPP]View Plaincopy

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 statement personinfo a data table

[CPP]View Plaincopy

1. NSString *sqlcreatetable = @"CREATE TABLE IF not EXISTS PERSONINFO (ID INTEGER PRIMARY KEY autoincrement, name TEXT, a  GE INTEGER, address TEXT) ";
2. [Self execsql:sqlcreatetable];

Run the program, and the data table is created. How do you know the data sheet was created? Let's open the database file with the SQLite Manager plugin tool in Firefox. You can install this plugin in Firefox. Open it

All four fields appear in the table.

5. Insert Data:

[CPP]View Plaincopy

2. NSString *SQL1 = [NSString stringWithFormat:
4. @"INSERT into '%@ ' ('%@ ', '%@ ', '%@ ') VALUES ('%@ ', '%@ ', '%@ ')",
6. TABLENAME, NAME, age, ADDRESS, @ "Zhang San", @ "at", @"Xicheng"];
10. NSString *SQL2 = [NSString stringWithFormat:
12. @"INSERT into '%@ ' ('%@ ', '%@ ', '%@ ') VALUES ('%@ ', '%@ ', '%@ ')",
14. TABLENAME, NAME, age, ADDRESS, @ "old six", @"a", @"Dongcheng District"];
16. [Self EXECSQL:SQL1];
18. [Self EXECSQL:SQL2];

Run the program, insert two data, and use Firefox's SQLite tool to view

6. Querying the database and printing the data

[CPP]View Plaincopy

2. NSString *sqlquery = @"SELECT * from PERSONINFO";
4. SQLITE3_STMT * statement;
8. if (SQLITE3_PREPARE_V2 (DB, [SQLQuery utf8string],-1, &statement, nil) = = SQLITE_OK) {
10. While (sqlite3_step (statement) = = Sqlite_row) {
12. char *name = (char*) sqlite3_column_text (statement, 1);
14. NSString *nsnamestr = [[NSString alloc]initwithutf8string:name];
18. int age = Sqlite3_column_int (statement, 2);
22. char *address = (char*) sqlite3_column_text (statement, 3);
24. NSString *nsaddressstr = [[NSString alloc]initwithutf8string:address];
28. NSLog (@"name:%@ age:%d address:%@", Nsnamestr,age, NSADDRESSSTR);
30. }
32. }
34. Sqlite3_close (DB);

Printing results:

[CPP]View Plaincopy

1. 2012-06-29 13:25:32.205 sqlitdemo[3587:f803] Name: Zhang San age:23 address: Xicheng District
2. 2012-06-29 13:25:32.206 sqlitdemo[3587:f803] name: Old six age:20 address: Dongcheng District

Finally, close the database.

Example code: http://download.csdn.net/detail/totogo2010/4400911

iOS learning SQLite create DATABASE, table, insert view data