iOS sqlite的使用

1、基本的数据库操作

1)加libsqlite3.0.dylib库, #import  "/usr/include/sqlite3.h"

2)打开数据库

int sqlite3_open(
                 const char *filename, /* Database filename (UTF-8) */
                 sqlite3 **ppDb /* OUT: SQLite db handle */
);

3 )对表的操作

int sqlite3_exec(
                 sqlite3*, /* An open database */
                 const char *sql, /* SQL to be evaluated */
                 int (*callback)(void*,int,char**,char**), /*Callbk func*/
                 void *, /* 1st argument to callback*/
                 char **errmsg /* Error msg written here */
);


SQLite有五种数据存储类型:

aINTEGER.整型值

bREAL.浮点类型(An  8-byte IEEE floating-point storage representing a floating-point number.

cTEXT.字符串 A storage area for text. The text can be in any of the following encodings: UTF-8, UTF-16BE, or  UTF-16-LE.

dBLOB.大数据 Used to store data exactly as entered — for example, an image.

eNULL.空值 Used to store  the value  NULL.

4)示例

#import "/usr/include/sqlite3.h"
int main(int argc, char *argv[])
{
    char *sqlStatement;
    sqlite3 *pDb;
    char *errorMsg;
    int returnCode;
    char *databaseName;
    databaseName = "financial.db";
    returnCode = sqlite3_open(databaseName, &pDb);
    if (returnCode!=SQLITE_OK)
    {
         fprintf(stderr, "Error in opening the database. Error: %s", sqlite3_errmsg(pDb));
         sqlite3_close(pDb);
         return -1;
    }
    sqlStatement = "DROP TABLE IF EXISTS stocks";
    returnCode = sqlite3_exec(pDb, sqlStatement, NULL, NULL, &errorMsg);
    if (returnCode != SQLITE_OK)
    {
         fprintf(stderr, "Error in dropping table stocks. Error: %s", errorMsg);
         sqlite3_free(errorMsg);
    }
    sqlStatement = "CREATE TABLE stocks (symbol VARCHAR(5), "
    "purchasePrice FLOAT(10,4), "
    "unitsPurchased INTEGER, "
    "purchase_date VARCHAR(10))";
    returnCode = sqlite3_exec(pDb, sqlStatement, NULL, NULL, &errorMsg);
    if (returnCode != SQLITE_OK)
    {
        fprintf(stderr, "Error in creating the stocks table. Error: %s", errorMsg);
        sqlite3_free(errorMsg);
    }
    insertStockPurchase(pDb, "ALU", 14.23, 100, "03-17-2012");
    insertStockPurchase(pDb, "GOOG", 600.77, 20, "01-09-2012");
    insertStockPurchase(pDb, "NT", 20.23,140, "02-05-2012");
    insertStockPurchase(pDb, "MSFT", 30.23, 5, "01-03-2012");
    sqlite3_close(pDb);
    return 0;
}

void insertStockPurchase(sqlite3 *pDb, const char*symbol, float price, int units, const char* theDate)
{
    char *errorMsg;
    int returnCode;
    char *st;
    st = sqlite3_mprintf("INSERT INTO stocks VALUES"
                         " (’%q’, %f, %d, ’%q’)", symbol, price, units, theDate);
    returnCode = sqlite3_exec(pDb, st, NULL, NULL, &errorMsg);
    if (returnCode != SQLITE_OK)
    {
        fprintf(stderr, "Error in inserting into the stocks table. Error: %s", errorMsg);
        sqlite3_free(errorMsg);
    }
    sqlite3_free(st);
}


2Processing Row Results

利用回调函数来处理每行的数据


3、预申明Prepared Statements

对于不返回数据的sql操作(insert,drop,create),用sqlite3_exec();对于返回数据的操作(select)

1)三个阶段:

准备(Preparation),执行(Execution),结束(Finalization

准备 - sqlite3_prepare_v2(sqlite3  *db,const  char  *zSql ,int  nBytes,sqlite3_stmt  **ppStmt,const  char  **pzTail)

执行 - sqlite3_step(sqlite3_stmt*);

结束 - sqlite3_finalize(sqlite3_stmt  *pStmt);

示例:

#import "/usr/include/sqlite3.h"
int main(int argc, char *argv[])
{
    char *sqlStatement;
    sqlite3 *database;
    int returnCode;
    char *databaseName;
    sqlite3_stmt *statement;
    databaseName = "financial.db";
    returnCode = sqlite3_open(databaseName, &database);
    if (returnCode!=SQLITE_OK)
    {
        fprintf(stderr, "Error in opening the database. Error: %s", sqlite3_errmsg(database));
        sqlite3_close(database);
        return -1;
    }
    
    sqlStatement = sqlite3_mprintf("SELECT S.symbol, S.unitsPurchased, " "S.purchasePrice FROM stocks AS S WHERE " "S.purchasePrice >= %f", 30.0);
    returnCode = sqlite3_prepare_v2(database, sqlStatement, strlen(sqlStatement), &statement, NULL);
    if (returnCode != SQLITE_OK)
    {
        fprintf(stderr, "Error in preparation of query. Error: %s", sqlite3_errmsg(database));
        sqlite3_close(database);
        return -1;
    }
    returnCode = sqlite3_step(statement);
    while (returnCode == SQLITE_ROW)
    {
        char *symbol;
        int units;
        double price;
        symbol = sqlite3_column_text(statement, 0);
        units = sqlite3_column_int(statement, 1);
        price = sqlite3_column_double(statement, 2);
        printf("We bought %d from %s at a price equal to %.4f\n", units, symbol, price);
        returnCode = sqlite3_step(statement);
    }
    sqlite3_finalize(statement);
    sqlite3_free(sqlStatement);
    return 0;
}

4 、用户自定义函数 (User-Defined Functions)

sqlite3_create_function()


5、存储二进制大对象(Storing BLOBs)


6、检索二进制大对象(Retrieving BLOBs



  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值