CRUD Operations

CRUD Operations

There are four SQL operations that are so common that a separate page is warranted. These operations have been dubbed CRUD, which stands for Create, Read, Update, and Delete. Their corresponding SQL keyword equivalents are Insert, Select, Update, and Delete respectively.

The following examples demonstrate how to build these common SQL statements using the builder classes in this repository:

Create (aka Insert)

Say you want to build the following insert statement:

INSERT INTO Customer (phone, name, dateCreated) VALUES ('555-555-5555', 'Ziminji', '2011-10-23 08:55:40');

This can be done like so:

ZIMSqlInsertStatement *sql = [[ZIMSqlInsertStatement alloc] init];
[sql into: @"Customer"];
[sql column: @"name" value: @"Ziminji"];
[sql column: @"phone" value: @"555-555-5555"];
[sql column: @"dateCreated" value: [NSDate date]];
NSString *statement = [sql statement];
NSLog(@"%@", statement);

To execute the statement, pass the generated SQL statement to the connection class:

NSNumber *result = [ZIMDbConnection dataSource: @"live" execute: statement];

Read (aka Select)

SELECT name FROM Customer WHERE name = 'Ziminji' AND phone = '555-555-5555' GROUP BY name ORDER BY name ASC LIMIT 1;

ZIMSqlSelectStatement *sql = [[ZIMSqlSelectStatement alloc] init];
[sql column: @"name"];
[sql from: @"Customer"];
[sql where: @"name" operator: ZIMSqlOperatorEqualTo value: @"Ziminji"];
[sql where: @"phone" operator: ZIMSqlOperatorEqualTo value: @"555-555-5555" connector: ZIMSqlConnectorAnd];
[sql orderBy: @"name"];
[sql groupBy: @"name"];
[sql limit: 1];
NSString *statement = [sql statement];
NSLog(@"%@", statement);

NSArray *records = [ZIMDbConnection dataSource: @"live" query: [sql statement]];

Update

UPDATE Customer SET phone = '555-555-5555' WHERE name = 'Ziminji' LIMIT 1;

ZIMSqlUpdateStatement *sql = [[ZIMSqlUpdateStatement alloc] init];
[sql table: @"Customer"];
[sql column: @"phone" value: @"555-555-5555"];
[sql where: @"name" operator: ZIMSqlOperatorEqualTo value: @"Ziminji"];
[sql limit: 1];
NSString *statement = [sql statement];
NSLog(@"%@", statement);

NSNumber *result = [ZIMDbConnection dataSource: @"live" execute: statement];

Delete

DELETE FROM Customer WHERE name = 'Ziminji';

ZIMSqlDeleteStatement *sql = [[ZIMSqlDeleteStatement alloc] init];
[sql table: @"Customer"];
[sql where: @"name" operator: ZIMSqlOperatorEqualTo value: @"Ziminji"];
NSString *statement = [sql statement];
NSLog(@"%@", statement);

NSNumber *result = [ZIMDbConnection dataSource: @"live" execute: statement];