如何使用 Sqlite iPhone 将两个表数据加入 UITableView 以及如何设置 NSDictionary 对象? [英] How to Join two tables data into UITableView using Sqlite iPhone and how to set NSDictionary object?
本文介绍了如何使用 Sqlite iPhone 将两个表数据加入 UITableView 以及如何设置 NSDictionary 对象?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
如何在这个 Am Struck with the NSDictionary、NSArray 和 NSString 中将我的两个表数据连接到另一个屏幕 如何将对象添加到 NSDictionary?下面是我的 DBClass.m
How to join my two tables data into another screen in this Am Struck with the NSDictionary,NSArray and NSString how to add object into NSDictionary? Below is my DBClass.m
#import "DBClass.h"
@implementation DBClass
+(NSString *)connectdb
{
NSArray *docDir=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *dbFolder=[docDir objectAtIndex:0];
NSFileManager *manager=[NSFileManager defaultManager];
if (![manager fileExistsAtPath:dbFolder])
{
[manager createDirectoryAtPath:dbFolder withIntermediateDirectories:YES attributes:nil error:nil];
}
NSString *dbPath=[dbFolder stringByAppendingPathComponent:@"JoinsDB.sqlite"];
if (![manager fileExistsAtPath:dbPath])
{
[manager copyItemAtPath:[[[NSBundle mainBundle]resourcePath]stringByAppendingPathComponent:@"JoinsDB.sqlite"] toPath:dbPath error:nil];
}
NSLog(@"%@",dbPath);
return dbPath;
}
+(BOOL)createTable
{
NSString *dbpath=[DBClass connectdb];
sqlite3 *dbObj;
if (sqlite3_open([dbpath UTF8String], &dbObj)==SQLITE_OK)
{
sqlite3_stmt *stmt=nil;
const char *sql="create table Company(ID INTEGER,Name VARCHAR,Age INTEGER,Address VARCHAR,Salary INTEGER)";
sqlite3_prepare_v2(dbObj, sql, -1, &stmt, nil);
if (sqlite3_step(stmt)==SQLITE_DONE)
{
NSLog(@"tabel created successfull");
}
else
{
NSLog(@"tabel already created");
}
sqlite3_finalize(stmt);
sqlite3_close(dbObj);
}
return YES;
}
+(int)saveData:(NSMutableArray *)data
{
sqlite3 *dbObj;
sqlite3_stmt *stmt=nil;
NSString *dbPath=[DBClass connectdb];
const char *sql=[[NSString stringWithFormat:@"insert into Company(ID,Name,Age,Address,Salary) values(\"%@\",\"%@\",\"%@\",\"%@\",\"%@\")",[data objectAtIndex:0],[data objectAtIndex:1],[data objectAtIndex:2],[data objectAtIndex:3],[data objectAtIndex:4]]UTF8String];
if (sqlite3_open([dbPath UTF8String], &dbObj)==SQLITE_OK)
{
sqlite3_bind_text(stmt, 1, [[data objectAtIndex:0]UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 2, [[data objectAtIndex:1]UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 3, [[data objectAtIndex:2]UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 4, [[data objectAtIndex:3]UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 5, [[data objectAtIndex:4]UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_prepare_v2(dbObj, sql, -1, &stmt, nil);
if (sqlite3_step(stmt)==SQLITE_DONE)
{
NSLog(@"data insertion stmnt executed properly");
}
else
NSLog(@"data insertion stmnt not executed");
sqlite3_finalize(stmt);
sqlite3_close(dbObj);
}
return sqlite3_last_insert_rowid(dbObj);
}
+(BOOL)createTable2
{
NSString *dbpath=[DBClass connectdb];
sqlite3 *dbObj;
if (sqlite3_open([dbpath UTF8String], &dbObj)==SQLITE_OK)
{
sqlite3_stmt *stmt=nil;
const char *sql="create table Department(ID INTEGER,Dept VARCHAR,Emp_ID INTEGER)";
sqlite3_prepare_v2(dbObj, sql, -1, &stmt, nil);
if (sqlite3_step(stmt)==SQLITE_DONE)
{
NSLog(@"tabel created successfull");
}
else
{
NSLog(@"tabel already created");
}
sqlite3_finalize(stmt);
sqlite3_close(dbObj);
}
return YES;
}
+(int)saveData2:(NSMutableArray *)data
{
sqlite3 *dbObj;
sqlite3_stmt *stmt=nil;
NSString *dbPath=[DBClass connectdb];
const char *sql=[[NSString stringWithFormat:@"insert into Department(ID,Dept,Emp_ID) values(\"%@\",\"%@\",\"%@\")",[data objectAtIndex:0],[data objectAtIndex:1],[data objectAtIndex:2]]UTF8String];
if (sqlite3_open([dbPath UTF8String], &dbObj)==SQLITE_OK)
{
sqlite3_bind_text(stmt, 1, [[data objectAtIndex:0]UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 2, [[data objectAtIndex:1]UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 3, [[data objectAtIndex:2]UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_prepare_v2(dbObj, sql, -1, &stmt, nil);
if (sqlite3_step(stmt)==SQLITE_DONE)
{
NSLog(@"data insertion stmnt executed properly");
}
else
NSLog(@"data insertion stmnt not executed");
sqlite3_finalize(stmt);
sqlite3_close(dbObj);
}
return sqlite3_last_insert_rowid(dbObj);
}
+(NSMutableArray *)getData
{
sqlite3 *dbobj;
NSString *dbpath =[DBClass connectdb];
NSMutableArray *readArray=[[NSMutableArray alloc]init];
if(sqlite3_open([dbpath UTF8String], &dbobj)==SQLITE_OK)
{
sqlite3_stmt *statement=nil;
//**** NSString *string=@"SELECT name FROM emptable";
NSString *string=@"SELECT EMP_ID, Age, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID";
const char *query=[string UTF8String];
if(sqlite3_prepare_v2(dbobj, query, -1, &statement, NULL)==SQLITE_OK)
{
while (sqlite3_step(statement)==SQLITE_ROW)
{
NSMutableDictionary *readDic=[[NSMutableDictionary alloc] init];
[readDic setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)] forKey:@"Emp_ID"];
[readDic setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)] forKey:@"Name"];
[readDic setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)] forKey:@"Dept"];
[readArray addObject:readDic];
NSLog(@"%@",readDic);
}
}
sqlite3_finalize(statement);
}
NSLog(@"%@",readArray);
sqlite3_close(dbobj);
return readArray;
}
@end
如何在 UITableview 中显示，在 Viewdidload 中我该怎么做这是我的 JoinViewController.m
How to show in UITableview,and in Viewdidload what should can I do This my JoinViewController.m
#import "JoinViewController.h"
#import "DBClass.h"
@interface JoinViewController ()
@end
@implementation JoinViewController
@synthesize data,array;
@synthesize Table;
- (id)initWithStyle:(UITableViewStyle)style
{
self = [super initWithStyle:style];
if (self) {
// Custom initialization
}
return self;
}
- (void)viewDidLoad
{
[super viewDidLoad];
array=[[NSMutableArray alloc]init];
data=[[NSMutableArray alloc]init];
array=[DBClass getData];
for( NSDictionary *obj in array)
{
NSLog(@"%@",obj);
[data addObject:[obj objectForKey:@"Emp_ID"]];
[data addObject:[obj objectForKey:@"Name"]];
[data addObject:[obj objectForKey:@"Dept"]];
}
NSLog(@"\n%d",[data count]);
[Table reloadData];
}
- (void)didReceiveMemoryWarning
{
[super didReceiveMemoryWarning];
// Dispose of any resources that can be recreated.
}
#pragma mark - Table view data source
- (NSInteger)numberOfSectionsInTableView:(UITableView *)tableView
{
//#warning Potentially incomplete method implementation.
// Return the number of sections.
return 1;
}
- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section
{
#warning Incomplete method implementation.
// Return the number of rows in the section.
//return 1;
return [data count];
}
- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath
{
static NSString *CellIdentifier = @"Cell";
UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:CellIdentifier];
if (cell == nil) {
cell = [[UITableViewCell alloc] initWithStyle:UITableViewCellStyleDefault reuseIdentifier:CellIdentifier];
}
cell.textLabel.text = [[data objectAtIndex:indexPath.row]objectForKey:@"Emp_ID"];
cell.textLabel.text = [[data objectAtIndex:indexPath.row] objectForKey:@"Name"];
cell.textLabel.text = [[data objectAtIndex:indexPath.row] objectForKey:@"Dept"];
return cell;
}
#pragma mark - Table view delegate
- (void)tableView:(UITableView *)tableView didSelectRowAtIndexPath:(NSIndexPath *)indexPath
{
// Navigation logic may go here. Create and push another view controller.
/*
<#DetailViewController#> *detailViewController = [[<#DetailViewController#> alloc] initWithNibName:@"<#Nib name#>" bundle:nil];
// ...
// Pass the selected object to the new view controller.
[self.navigationController pushViewController:detailViewController animated:YES];
*/
}
- (BOOL)shouldAutorotateToInterfaceOrientation:(UIInterfaceOrientation)interfaceOrientation
{
// Return YES for supported orientations
return (interfaceOrientation == UIInterfaceOrientationPortrait);
}
-(BOOL)textFieldShouldReturn:(UITextField *)textField{
[textField resignFirstResponder];
return YES;
}
@end
推荐答案
在您的 getdata 方法中，您的 sql 要求提供 EMP_ID、Age、DEPT，但您将数据存储为 Emp_ID、Name、Dept.一个表示年龄，另一个说名字.此外，您正在复制列号，从第 2 列中获取一个值，即 DEPT，并将其存储在 Emp_ID 中，并且您从第 1 列中获取两次值，即年龄，并存储在 Name 和 Dept 中，第 0 列应该是 Emp_ID，1 是 Age(您可能希望将其更改为 Name)，2 是 DEPT.
In your getdata method, your sql is asking for EMP_ID, Age, DEPT, but you're storing the data as Emp_ID, Name, Dept. One says age, the other says name. Also, you're duplicating the column numbers, getting a value from column 2, which would be DEPT, and you're storing it in Emp_ID, and you're getting the value from column 1 twice, which would be age, and storing it in Name and Dept. Column 0 should be Emp_ID, 1 is Age (which you probably want to change to Name), 2 is DEPT.
您还可以通过以下方式简化 SQL 语句:
You can also simplify your SQL statement a bit with:
SELECT EMP_ID, Age, DEPT FROM COMPANY, DEPARTMENT where COMPANY.ID = DEPARTMENT.EMP_ID;
这篇关于如何使用 Sqlite iPhone 将两个表数据加入 UITableView 以及如何设置 NSDictionary 对象?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
