iOS 操作sqlite3

最新推荐文章于 2023-11-16 14:33:04 发布
原创最新推荐文章于 2023-11-16 14:33:04 发布 · 476 阅读
0 ·
CC 4.0 BY-SA版权
ＩＯＳ专栏收录该内容
45 篇文章
订阅专栏
本文介绍了一个iOS应用中使用SQLite3进行数据库操作的具体实现，包括数据库的创建、表格的建立、数据的增删改查等核心功能。
摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >
//
//  ViewController.m
//  Sqlite3_demo
//
//  Created by 刘薇薇 on 16/6/12.
//  Copyright (c) 2016年 刘胡来. All rights reserved.
//

#import "ViewController.h"

#define TABLENAME @"testdata.sqlite"
#define NAME @"name"
#define AGE @"age"
#define SEX @"sex"
#define WEIGHT @"weight"
#define ADDRESS @"address"



@interface ViewController ()

@end



@implementation ViewController

//获取数据库文件所在路径
-(NSString *) dataFilePath
{
    
    NSArray *path =  NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    
    NSString *document = [path objectAtIndex:0];
    
    return [document stringByAppendingPathComponent:TABLENAME];
    
}

- (void) createDataTable
{
    //1.先打开数据库
    sqlite3 *database;
    if (sqlite3_open([[self dataFilePath] UTF8String], &database)!=SQLITE_OK)
    {
        sqlite3_close(database);
        NSAssert(0, @"open database faid!");
        
        NSLog(@"\n数据库创建失败！");
    }
    
    //2.创建数据表
    NSString *ceateSQL = @"CREATE TABLE IF NOT EXISTS PERSIONINFO(ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, AGE INTEGER, SEX TEXT, WEIGHT INTEGER, ADDRESS TEXT)";
    
    char *ERROR;
    
    if (sqlite3_exec(database, [ceateSQL UTF8String], NULL, NULL, &ERROR)!=SQLITE_OK){
        sqlite3_close(database);
        NSAssert(0, @"ceate table faild!");
        NSLog(@"\n表创建失败");
    }

}
/**
 *查询语句
 *
 */
- (void) Query:(NSString *) quarysql
{
    //NSMutableArray *tempResultSet = [[NSMutableArray alloc] init];
    
    sqlite3 *database;
    if (sqlite3_open([[self dataFilePath] UTF8String], &database)!=SQLITE_OK)
    {
        sqlite3_close(database);
        NSAssert(0, @"open database faid!");
        
        NSLog(@"\n数据库创建失败！");
    }
    
    
    
    sqlite3_stmt *stmt;
    if (sqlite3_prepare_v2(database, [quarysql UTF8String], -1, &stmt, nil) == SQLITE_OK)
    {
        
        while (sqlite3_step(stmt)==SQLITE_ROW)
        {
            
            char *name = (char *)sqlite3_column_text(stmt, 1);
            NSString *nameString = [[NSString alloc] initWithUTF8String:name];
           
            
            int age = sqlite3_column_int(stmt, 2);
            
            char *sex = (char *)sqlite3_column_text(stmt, 3);
            NSString *sexString = [[NSString alloc] initWithUTF8String:sex];
            
            int weight = sqlite3_column_int(stmt, 4);
           
            char *address = (char *)sqlite3_column_text(stmt, 5);
            NSString *addressString = [[NSString alloc] initWithUTF8String:address];
            
             NSLog(@"\n name:%@ age:%d sex:%@ weight:%d address:%@",nameString,age,sexString,weight,addressString);

        }
        
        sqlite3_finalize(stmt);
    }
    //用完了一定记得关闭，释放内存
    sqlite3_close(database);
}

- (void) Insert:(NSString *) insertSql
{
    sqlite3 *database;
    if (sqlite3_open([[self dataFilePath] UTF8String], &database)!=SQLITE_OK)
    {
        sqlite3_close(database);
        NSAssert(0, @"open database faid!");
        
        NSLog(@"\n数据库创建失败！");
    }
    
    char *errorMsg = NULL;
    
    NSString *insert = [NSString stringWithFormat:@"INSERT OR REPLACE INTO PERSIONINFO('%@','%@','%@','%@','%@')VALUES('%@','%d','%@','%d','%@')",NAME,AGE,SEX,WEIGHT,ADDRESS,@"小杨",23,@"man",65,@"中国北京,haidian,shangdi,xinxiRoad,100014"];
    
        //执行语句
        if (sqlite3_exec(database, [insert UTF8String], NULL, NULL, &errorMsg) != SQLITE_OK)
        {
            sqlite3_close(database);
        }
    sqlite3_close(database);

}

//根据sql语句内容执行数据库相应的操作
- (void) OperationDataBaseBySql:(NSString *) sql
{
    if (sql.length == 0)
    {
        return;
    }
    
    sqlite3 *database;
    if (sqlite3_open([[self dataFilePath] UTF8String], &database)!=SQLITE_OK)
    {
        sqlite3_close(database);
        NSAssert(0, @"open database faid!");
        
        NSLog(@"\n数据库创建失败！");
    }
    
    
    char *errorMsg = NULL;
    
  
    //执行语句
    if (sqlite3_exec(database, [sql UTF8String], NULL, NULL, &errorMsg) != SQLITE_OK)
    {
        sqlite3_close(database);
            
        NSLog(@"\n操作数据库失败--:%s\n",errorMsg);
    }
    
    

    
   
}



- (void)viewDidLoad
{
    [super viewDidLoad];
    
    [self createDataTable];
    
    //插入语句
     NSString *insert = [NSString stringWithFormat:@"INSERT OR REPLACE INTO PERSIONINFO('%@','%@','%@','%@','%@')VALUES('%@','%d','%@','%d','%@')",NAME,AGE,SEX,WEIGHT,ADDRESS,@"liu",23,@"man",65,@"中国北京,haidian,shangdi,xinxiRoad,100014"];
    
    //删除语句
    NSString *TmpStr = @"delete from %@ where name = %@";
    NSString *delete =[NSString stringWithFormat:TmpStr,@"PERSIONINFO",@"liu"];
    
    //更新语句,注意更新语句
    NSString *tmpStr2 =@"update PERSIONINFO set name = '%@' where age = %d";
    
    NSString *update = [NSString stringWithFormat:tmpStr2, @"yu", 23];
    
    [self OperationDataBaseBySql:update];
    
    //[self deleteSql];
    
    
    NSString *quary = @"SELECT * FROM PERSIONINFO";//SELECT ROW,FIELD_DATA FROM FIELDS ORDER BY ROW
    
    [self Query:quary];
    
}


- (void)didReceiveMemoryWarning
{
    [super didReceiveMemoryWarning];
    // Dispose of any resources that can be recreated.
}

@end