IOS SQLite Basics

1, Import Libsqlite3.0.dylib Library

In file:#import"Sqlite3.h"

2, create a database

#define KDOCDIR [Nssearchpathfordirectoriesindomains (NSDocumentDirectory, Nsuserdomainmask, YES) ObjectAtIndex: 0]

#define DbPath [Kdocdir stringbyappendingpathcomponent:@"test.db"]

Sqlite3 *db;

if (sqlite3_open([dbPathutf8string], &db)! =Sqlite_ok) {

Sqlite3_close (DB);

Nsassert (0,@ " database open failed. ");

return;

}

3, create a table table1, three different types of field IDs are shaped, name is a string, image is a binary

Char *sqlstr ="CREATE TABLE table1 (id integer, name text, image blob)";

sqlite3_stmt *statement;

if (sqlite3_prepare_v2(db, Sqlstr,-1, &statement,nil)! =Sqlite_ok) {

NSLog(@ "error:failed to prepare statement:create table1");

return;

}

int success = sqlite3_step(statement);

sqlite3_finalize (statement);

if (Success! = Sqlite_done) {

NSLog(@ "error:failed to dehydrate:create TABLEtable1");

return;

}

4, insert the data, note the number of question marks to match the parameters

int idvalue=1;

nsstring *namevalue=@ "value";

UIImage *image=[UIImageimagenamed:@ "Test.png"];

sqlstr = "INSERT into table1 VALUES (?,?,?)" ;

int success = sqlite3_prepare_v2(db, Sqlstr,-1, &statement,NULL);

if (Success! = SQLITE_OK) {

NSLog(@ "error:failed to insert INTO Table1");

}

Sqlite3_bind_int (statement, 1, [idvalueintegervalue]); The second parameter starts with 1, which differs from the data that is queried later

sql Ite3_bind_text (statement, 2 , [namevalue utf8string ",-1 , " Span style= "COLOR: #78492a" >sqlite_transient );

Sqlite3_bind_blob (Statement, 3, [imagebytes], (int) [image Length],sqlite_transient);

Success = Sqlite3_step(statement);

sqlite3_finalize (statement);

if (Success = = Sqlite_error) {

NSLog(@ "error:failed to insert into the database with message.") );

return;

}

5. Querying data

sqlstr = "SELECT * from table1";

if (sqlite3_prepare_v2(db, Sqlstr,-1, &statement,NULL)! =Sqlite_ok) {

NSLog(@ "error:failed to prepare statement with Message:gettable1.") );

return;

}

// query result set one line to traverse all records, here the number corresponds to the column value.

whi Le (sqlite3_step (statement) ==sqlite_row ) {

int tempint=sqlite3_column_int(statement, 0); Here to start from 0, note the difference between the ordinal number when inserting

Char *temp= (char *)sqlite3_column_text(statement, 1);

nsstring *tempstr=temp? [nsstring stringwithutf8string: temp]:@ "";

int length=sqlite3_column_bytes(statement,2); //Gets the length of the binary data

Byte* bytes= (byte*)Sqlite3_column_blob(statement,2);

nsdata *data=[nsdatadatawithbytes: byteslength: length];

}

6. Close the database

Sqlite3_close(db);

IOS SQLite Basics