ios数据库操作

ViewController.h代码

//
//  ViewController.h
//  sqlitedemo
//
//  Created by oo on 12-11-28.
//  Copyright (c) 2012年 oo. All rights reserved.
//

#import <UIKit/UIKit.h>
#import "sqlite3.h"
#import "EGORefreshTableHeaderView.h"
#define DATA_FILE @"aa"
#define TABLE_NAME @"student"
#define FIELDS_NAME_SID @"studentId"
#define FIELDS_NAME_SNAME @"studentName"
#define FIELDS_NAME_SCLASS @"studentClass"
@interface ViewController : UIViewController<UITableViewDataSource, UITableViewDelegate,
UIScrollViewDelegate,EGORefreshTableHeaderDelegate>
{
    NSArray *listData;
    NSArray *listdata1;
    UITextField *studentId;
    UITextField *studentName;
    UITextField *studentClass;
    sqlite3 *db;
    EGORefreshTableHeaderView *_refreshHeaderView;
    BOOL _reloading;//主要是记录是否在刷新中
    UITableView *a;
}
@property (nonatomic,retain) IBOutlet UITableView *a;
@property (nonatomic, retain) IBOutlet UITextField *studentId;
@property (nonatomic, retain) IBOutlet UITextField *studentName;
@property (nonatomic, retain) IBOutlet UITextField *studentClass;
@property (nonatomic, retain) NSArray *listData;
@property (nonatomic, retain) NSArray *listData1;
-(IBAction) save;
-(IBAction) load;
-(IBAction)textFieldDoneEditing:(id)sender;
- (void)reloadTableViewDataSource;
- (void)doneLoadingTableViewData; 
-(NSString *)dataFilePath;
@end

 ViewController.m代码

//
//  ViewController.m
//  sqlitedemo
//
//  Created by oo on 12-11-28.
//  Copyright (c) 2012年 oo. All rights reserved.
//

#import "ViewController.h"
#import "sqlite3.h"
@interface ViewController ()

@end

@implementation ViewController
@synthesize studentId;
@synthesize studentName;
@synthesize studentClass;
@synthesize listData;
@synthesize listData1;
@synthesize a;
-(NSString *)dataFilePath {
    
    //SUserDomainMask表示搜索的范围限制于当前应用的沙盒目录。
    
    NSArray * myPaths = NSSearchPathForDirectoriesInDomains (NSDocumentDirectory,
                                                             NSUserDomainMask, YES);
    NSString * myDocPath = [myPaths objectAtIndex:0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:DATA_FILE];
    return filename;
}
- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section {
	return [listData count];
}

- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath {
	
	static NSString *SimpleCellIdentifier = @"SimpleCellIdentifier";
	UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:SimpleCellIdentifier];
    
	if (cell == nil) {
		cell = [[[UITableViewCell alloc] initWithStyle:UITableViewCellStyleValue1
									   reuseIdentifier:SimpleCellIdentifier] autorelease];
	}
	NSUInteger row = [indexPath row];
	cell.textLabel.text = [listData objectAtIndex:row];
    cell.detailTextLabel.text = [listData1 objectAtIndex:row];
	return cell;
    
}

#pragma mark -- 实现TableView委托方法
- (void)tableView:(UITableView *)tableView
didSelectRowAtIndexPath:(NSIndexPath *)indexPath {
    //	NSUInteger row = [indexPath row];
    //	NSString *rowValue = [listData objectAtIndex:row];
    //	NSString *message = [[NSString alloc] initWithFormat:@"你选择了%@队。", rowValue];
    //
    //	UIAlertView *alert = [[UIAlertView alloc]initWithTitle:@"行选择"
    //                                                   message:message
    //                                                  delegate:self
    //                                         cancelButtonTitle:@"Ok"
    //                                         otherButtonTitles:nil];
    //	[message release];
    //	[alert show];
    //	[alert release];
    //	[tableView deselectRowAtIndexPath:indexPath animated:YES];
}


//--------
-(NSMutableArray*)selectAll
{
    NSMutableArray *list = [[NSMutableArray alloc] initWithObjects:nil];;
	NSString *filename = [self dataFilePath];
	NSLog(@"%@",filename);
	if (sqlite3_open([filename UTF8String], &db) != SQLITE_OK) {
		sqlite3_close(db);
		NSAssert(NO,@"数据库打开失败。");
	} else {
		
		NSString *qsql = [NSString stringWithFormat: @"SELECT %@ FROM %@", FIELDS_NAME_SID, TABLE_NAME];
		NSLog(@"%@",qsql);
		sqlite3_stmt *statement;
		//预处理过程
		if (sqlite3_prepare_v2(db, [qsql UTF8String], -1, &statement, NULL) == SQLITE_OK) {
			//绑定参数开始
			sqlite3_bind_text(statement, 1, [studentId.text UTF8String], -1, NULL);
			
			//执行
			while (sqlite3_step(statement) == SQLITE_ROW) {
				char *field1 = (char *) sqlite3_column_text(statement, 0);
				NSString *field1Str = [[NSString alloc] initWithUTF8String: field1];
				//studentId.text = field1Str;
				//[field1Str release];
                [list addObject:field1Str];
                NSLog(@"%d",list.count);
                
			}
		}
		
		sqlite3_finalize(statement);
		sqlite3_close(db);
		
	}
    return list;
}

-(NSMutableArray*)selectAll1
{
    NSMutableArray *list = [[NSMutableArray alloc] initWithObjects:nil];;
	NSString *filename = [self dataFilePath];
	NSLog(@"%@",filename);
	if (sqlite3_open([filename UTF8String], &db) != SQLITE_OK) {
		sqlite3_close(db);
		NSAssert(NO,@"数据库打开失败。");
	} else {
		
		NSString *qsql = [NSString stringWithFormat: @"SELECT %@ FROM %@", FIELDS_NAME_SNAME, TABLE_NAME];
		NSLog(@"%@",qsql);
		sqlite3_stmt *statement;
		//预处理过程
		if (sqlite3_prepare_v2(db, [qsql UTF8String], -1, &statement, NULL) == SQLITE_OK) {
			//绑定参数开始
			sqlite3_bind_text(statement, 1, [studentId.text UTF8String], -1, NULL);
			
			//执行
			while (sqlite3_step(statement) == SQLITE_ROW) {
				char *field1 = (char *) sqlite3_column_text(statement, 0);
				NSString *field1Str = [[NSString alloc] initWithUTF8String: field1];
				//studentId.text = field1Str;
				//[field1Str release];
                [list addObject:field1Str];
                NSLog(@"%d",list.count);
                
			}
		}
		
		sqlite3_finalize(statement);
		sqlite3_close(db);
		
	}
    return list;
}
//-------
- (void)viewDidLoad
{
    [super viewDidLoad];
   // NSArray *array = [[NSArray alloc] initWithObjects:@"今天下午2:00开会",@"今天晚上12:00世界杯",nil];
    
    NSArray *array = [self selectAll];
    NSArray *array1 = [self selectAll1];
    
    NSLog(@"%d",array.count);
    
    
    
	self.listData = array;
    self.listData1 = array1;
	NSString *filename = [self dataFilePath];
	NSLog(@"%@",filename);
	if (sqlite3_open([filename UTF8String], &db) != SQLITE_OK) {
		sqlite3_close(db);
		NSAssert(NO,@"数据库打开失败。");
	} else {
		char *err;
		NSString *createSQL = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@ (%@ TEXT PRIMARY KEY, %@ TEXT, %@ TEXT);" ,
							   TABLE_NAME,FIELDS_NAME_SID,FIELDS_NAME_SNAME,FIELDS_NAME_SCLASS];
		if (sqlite3_exec(db,[createSQL UTF8String],NULL,NULL,&err) != SQLITE_OK) {
			sqlite3_close(db);
			//NSAssert1(NO, @"建表失败, %@", err);
		}
		sqlite3_close(db);
	}
    
    
    if(_refreshHeaderView == nil)
    {
        EGORefreshTableHeaderView *view = [[EGORefreshTableHeaderView alloc] initWithFrame:CGRectMake(0.0f, 0.0f - self.a.bounds.size.height, self.view.frame.size.width, self.a.bounds.size.height)];
        
        view.delegate = self;
        [self.a addSubview:view];
        _refreshHeaderView = view;
    }
    [_refreshHeaderView refreshLastUpdatedDate];
}
-(IBAction)textFieldDoneEditing:(id)sender
{
    [sender resignFirstResponder];
}

-(IBAction) save {
	NSString *filename = [self dataFilePath];
	NSLog(@"%@",filename);
	if (sqlite3_open([filename UTF8String], &db) != SQLITE_OK) {
		sqlite3_close(db);
		NSAssert(NO,@"数据库打开失败。");
	} else {
		
		NSString *sqlStr = [NSString stringWithFormat: @"INSERT OR REPLACE INTO %@ (%@, %@, %@) VALUES (?,?,?)",
							TABLE_NAME, FIELDS_NAME_SID, FIELDS_NAME_SNAME, FIELDS_NAME_SCLASS];
		
		sqlite3_stmt *statement;
		//预处理过程
		if (sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statement, NULL) == SQLITE_OK) {
			//绑定参数开始
			sqlite3_bind_text(statement, 1, [studentId.text UTF8String], -1, NULL);
			sqlite3_bind_text(statement, 2, [studentName.text UTF8String], -1, NULL);
			sqlite3_bind_text(statement, 3, [studentClass.text UTF8String], -1, NULL);
			
			
			//执行插入
			if (sqlite3_step(statement) != SQLITE_DONE) {
				NSAssert(0, @"插入数据失败。");
			}
		}
		
		sqlite3_finalize(statement);
		sqlite3_close(db);
		
	}
	
	
}

-(IBAction) load {
	NSString *filename = [self dataFilePath];
	NSLog(@"%@",filename);
	if (sqlite3_open([filename UTF8String], &db) != SQLITE_OK) {
		sqlite3_close(db);
		NSAssert(NO,@"数据库打开失败。");
	} else {
		
		NSString *qsql = [NSString stringWithFormat: @"SELECT %@,%@,%@ FROM %@ where %@ = ?", FIELDS_NAME_SID, FIELDS_NAME_SNAME, FIELDS_NAME_SCLASS, TABLE_NAME,FIELDS_NAME_SID];
		NSLog(@"%@",qsql);
		sqlite3_stmt *statement;
		//预处理过程
		if (sqlite3_prepare_v2(db, [qsql UTF8String], -1, &statement, NULL) == SQLITE_OK) {
			//绑定参数开始
			sqlite3_bind_text(statement, 1, [studentId.text UTF8String], -1, NULL);
			
			//执行
			while (sqlite3_step(statement) == SQLITE_ROW) {
				char *field1 = (char *) sqlite3_column_text(statement, 0);
				NSString *field1Str = [[NSString alloc] initWithUTF8String: field1];
				studentId.text = field1Str;
				
				char *field2 = (char *) sqlite3_column_text(statement, 1);
				NSString *field2Str = [[NSString alloc] initWithUTF8String: field2];
				studentName.text = field2Str;
				
				char *field3 = (char *) sqlite3_column_text(statement, 2);
				NSString *field3Str = [[NSString alloc] initWithUTF8String: field3];
				studentClass.text = field3Str;
				
				[field1Str release];
				[field2Str release];
				[field3Str release];
			}
		}
		
		sqlite3_finalize(statement);
		sqlite3_close(db);
		
	}
}
- (void)didReceiveMemoryWarning
{
    [super didReceiveMemoryWarning];
    // Dispose of any resources that can be recreated.
}
//-----
-(void)reloadTableViewDataSource
{
    NSLog(@"==开始加载数据");
    NSArray *array = [self selectAll];
    NSArray *array1 = [self selectAll1];
    
    NSLog(@"%d",array.count);
    
    
    
	self.listData = array;
    self.listData1 = array1;
    
    [self.a reloadData];
    _reloading = YES;

    
}
- (void)doneLoadingTableViewData{
    
    NSLog(@"===加载完数据");
    //  model should call this when its done loading
    _reloading = NO;
    [_refreshHeaderView egoRefreshScrollViewDataSourceDidFinishedLoading:self.a];
    
    
}
#pragma mark –
#pragma mark UIScrollViewDelegate Methods
- (void)scrollViewDidScroll:(UIScrollView *)scrollView{
    
    [_refreshHeaderView egoRefreshScrollViewDidScroll:scrollView];
    
}

- (void)scrollViewDidEndDragging:(UIScrollView *)scrollView willDecelerate:(BOOL)decelerate{
    
    [_refreshHeaderView egoRefreshScrollViewDidEndDragging:scrollView];
    
}

#pragma mark –
#pragma mark EGORefreshTableHeaderDelegate Methods
- (void)egoRefreshTableHeaderDidTriggerRefresh:(EGORefreshTableHeaderView*)view{
    
    [self reloadTableViewDataSource];
    [self performSelector:@selector(doneLoadingTableViewData) withObject:nil afterDelay:3.0];
    
    
    
}
- (BOOL)egoRefreshTableHeaderDataSourceIsLoading:(EGORefreshTableHeaderView*)view{
    
    return _reloading; // should return if data source model is reloading
    
}
- (NSDate*)egoRefreshTableHeaderDataSourceLastUpdated:(EGORefreshTableHeaderView*)view{
    
    return [NSDate date]; // should return date data source was last changed
    
}
@end