INNER JOIN Database
This Demo Used To Save Data in Different Table and also Get This Data of Database
1) Open the Build Phases tab, and within Link Binary with Libraries, add the following frameworks:
-libsqlite3.dylib
2) create Sqlite3 Object In your view .h file for using like that ..
#import <sqlite3.h>
3) Create Database :
in Database create Two Table And Take empid as a Common to Get Data
table 1 is Look Like This :
table 2 is Look Like This :
 In "viewcontroller.m" File we create database first and also find the path using "NSSearchpathfordocumentdirectory" method . In "viewdidload" method put This code.THIS IS COPY TO VIEWCONTROLLER.M (VIEWDIDLOAD METHOD) :
NSString *docsDir;
NSArray *dirPaths ;
dirPaths = NSSearchPathForDirectoriesInDomains( NSDocumentDirectory, NSUserDomainMask, YES); // Get the documents directory
docsDir = dirPaths[0];
databasePath = [[NSString alloc]initWithString: [docsDir stringByAppendingPathComponent:@"data.sqlite"]]; // Build the path to the database file
This code is useful for the copy file NSBundle to the NSDocument directory folder. But First Check the file is available in bundle or not ..?
THIS IS COPY TO APPDELEGATE.M :-
NSString *pathsToReources = [[NSBundle mainBundle] resourcePath];
NSString *yourOriginalDatabasePath = [pathsToReources stringByAppendingPathComponent:@"data.sqlite"];
NSArray *pathsToDocuments = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [pathsToDocuments objectAtIndex: 0];
NSString *dbPath = [documentsDirectory stringByAppendingPathComponent:@"data.sqlite"];
if (![[NSFileManager defaultManager] isReadableFileAtPath: dbPath]) {
if ([[NSFileManager defaultManager] copyItemAtPath: yourOriginalDatabasePath toPath: dbPath error: NULL] != YES)
NSAssert2(0, @"Fail to copy database from %@ to %@", yourOriginalDatabasePath, dbPath);
}
4) Make Your Storyboard Like This :
5) Write Coding Which Is given Below :
ViewController (use For Registration View) :
ViewController.h :
#import <UIKit/UIKit.h>
#import <sqlite3.h>
#import "SecViewController.h"
@interface ViewController : UIViewController
@property(strong,nonatomic)IBOutlet UITextField *txtid;
@property(strong,nonatomic)IBOutlet UITextField *txtname;
@property(strong,nonatomic)IBOutlet UITextField *txtno;
@property(strong,nonatomic)IBOutlet UITextField *txtsal;
@property(strong,nonatomic)IBOutlet UITextField *txtdep;
@property (nonatomic) sqlite3 *contactDB;
@property (strong, nonatomic) NSString *databasePath;
@property(strong,nonatomic)SecViewController *secobj;
-(IBAction)dataregister:(id)sender;
-(IBAction)datashow:(id)sender;
@end
ViewController.m :
#import "ViewController.h"
@interface ViewController ()
@end
@implementation ViewController
@synthesize txtdep,txtid,txtname,txtno,txtsal;
@synthesize contactDB,databasePath;
@synthesize secobj;
- (void)viewDidLoad
{
[super viewDidLoad];
NSString *docsDir;
NSArray *dirPaths ;
dirPaths = NSSearchPathForDirectoriesInDomains( NSDocumentDirectory, NSUserDomainMask, YES); // Get the documents directory
docsDir = dirPaths[0];
databasePath = [[NSString alloc]initWithString: [docsDir stringByAppendingPathComponent:@"data.sqlite"]]; // Build the path to the database file
}
-(IBAction)dataregister:(id)sender
{
sqlite3_stmt *statement;
sqlite3_stmt *statement1;
const char *dbpath = [databasePath UTF8String];
if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
{
NSString *insertSQL = [NSString stringWithFormat:
@"INSERT INTO register (empid, empname, empno) VALUES (\"%@\", \"%@\", \"%@\")",self.txtid.text, self.txtname.text, self.txtno.text];
const char *insert_stmt = [insertSQL UTF8String];
sqlite3_prepare_v2(contactDB, insert_stmt,
-1, &statement, NULL);
// coding for second table
NSString *insertSQL1 = [NSString stringWithFormat:
@"INSERT INTO empdata (empid, empsal, empdep) VALUES (\"%@\", \"%@\", \"%@\")",self.txtid.text, self.txtsal.text, self.txtdep.text];
const char *insert_stmt1 = [insertSQL1 UTF8String];
sqlite3_prepare_v2(contactDB, insert_stmt1,
-1, &statement1, NULL);
// coding Complate
if (sqlite3_step(statement) == SQLITE_DONE)
{
if (sqlite3_step(statement1) == SQLITE_DONE)
{
UIAlertView *message = [[UIAlertView alloc] initWithTitle:@"Hello World!" message:@"Data Saved"
delegate:nil
cancelButtonTitle:@"OK"
otherButtonTitles:nil];
[message show];
}
else
{
UIAlertView *message = [[UIAlertView alloc] initWithTitle:@"Hello World!" message:@"Data Not Saved"
delegate:nil
cancelButtonTitle:@"OK"
otherButtonTitles:nil];
[message show];
}
}
else
{
UIAlertView *message = [[UIAlertView alloc] initWithTitle:@"Hello World!" message:@"Data Not Saved"
delegate:nil
cancelButtonTitle:@"OK"
otherButtonTitles:nil];
[message show];
}
sqlite3_finalize(statement);
sqlite3_finalize(statement1);
sqlite3_close(contactDB);
}
}
-(IBAction)datashow:(id)sender
{
UIStoryboard *story = [UIStoryboard storyboardWithName:@"Main" bundle:nil];
secobj = [story instantiateViewControllerWithIdentifier:@"SecViewController"];
[self.navigationController pushViewController:secobj animated:YES];
}
- (BOOL)textFieldShouldReturn:(UITextField *)textField;
{
[textField resignFirstResponder];
return YES;
}
- (void)didReceiveMemoryWarning
{
[super didReceiveMemoryWarning];
// Dispose of any resources that can be recreated.
}
@end
SecViewController (use For Show Data in View) :
SecViewController.h :
#import <UIKit/UIKit.h>
#import <sqlite3.h>
@interface SecViewController : UIViewController
@property(strong,nonatomic)IBOutlet UITextField *txtsearchname;
@property(strong,nonatomic)IBOutlet UILabel *lblname;
@property(strong,nonatomic)IBOutlet UILabel *lblno;
@property(strong,nonatomic)IBOutlet UILabel *lblsal;
@property(strong,nonatomic)IBOutlet UILabel *lbldep;
@property (nonatomic) sqlite3 *contactDB;
@property (strong, nonatomic) NSString *databasePath;
-(IBAction)searchdata:(id)sender;
@end
SecViewController.m :
#import "SecViewController.h"
@interface SecViewController ()
@end
@implementation SecViewController
@synthesize lbldep,lblname,lblno,lblsal;
@synthesize contactDB,databasePath;
- (id)initWithNibName:(NSString *)nibNameOrNil bundle:(NSBundle *)nibBundleOrNil
{
self = [super initWithNibName:nibNameOrNil bundle:nibBundleOrNil];
if (self) {
// Custom initialization
}
return self;
}
- (void)viewDidLoad
{
[super viewDidLoad];
NSString *docsDir;
NSArray *dirPaths ;
dirPaths = NSSearchPathForDirectoriesInDomains( NSDocumentDirectory, NSUserDomainMask, YES); // Get the documents directory
docsDir = dirPaths[0];
databasePath = [[NSString alloc]initWithString: [docsDir stringByAppendingPathComponent:@"data.sqlite"]]; // Build the path to the database file
}
-(IBAction)searchdata:(id)sender
{
const char *dbpath = [databasePath UTF8String];
sqlite3_stmt *statement;
if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
{
NSString *querySQL = [NSString stringWithFormat:
@"SELECT register.empname, register.empno, empdata.empsal, empdata.empdep FROM register INNER JOIN empdata ON register.empid=empdata.empid WHERE register.empid=\"%@\"",self.txtsearchname.text];
const char *query_stmt = [querySQL UTF8String];
if (sqlite3_prepare_v2(contactDB, query_stmt, -1, &statement, NULL) == SQLITE_OK)
{
if (sqlite3_step(statement) == SQLITE_ROW)
{
NSString *addressField = [[NSString alloc] initWithUTF8String: (const char *) sqlite3_column_text( statement, 0)];
lblname.text = addressField;
NSString *nofield = [[NSString alloc] initWithUTF8String: (const char *) sqlite3_column_text( statement, 1)];
lblno.text = nofield;
NSString *salary = [[NSString alloc] initWithUTF8String: (const char *) sqlite3_column_text( statement, 2)];
lblsal.text = salary;
NSString *depart = [[NSString alloc] initWithUTF8String: (const char *) sqlite3_column_text( statement, 3)];
lbldep.text = depart;
UIAlertView *message = [[UIAlertView alloc] initWithTitle:@"Hello World!" message:@"Data Seached"
delegate:nil
cancelButtonTitle:@"OK"
otherButtonTitles:nil];
[message show];
}
else
{
UIAlertView *message = [[UIAlertView alloc] initWithTitle:@"Hello World!" message:@"Data Not Searched"
delegate:nil
cancelButtonTitle:@"OK"
otherButtonTitles:nil];
[message show];
}
sqlite3_finalize(statement);
}
sqlite3_close(contactDB);
}
}
- (BOOL)textFieldShouldReturn:(UITextField *)textField
{
[textField resignFirstResponder];
return YES;
}
- (void)didReceiveMemoryWarning
{
[super didReceiveMemoryWarning];
// Dispose of any resources that can be recreated.
}
@end
|
