SQL文生成メソッド(SQLite用)

# はじめに
SQL文生成のメソッド群です。
自分でゴリゴリとSQL文を書く必要がある時に使います。

他プロジェクトでも流用可能かもな、と思ったが故の、ほぼ自分用の処理まとめです。

SQLite以外で試していないのでSQLiteの方言が入っているかもしれません。

MRC版です。

# 注意！
SQLインジェクション対策を入れていないので、ユーザ入力値や外部から与えられた値は渡さないようにしてください！

# SQL文生成メソッド群

## 宣言部
```lang:XXXSQLManager.h

+ (NSString *)selectSQL:(NSString *)tableName columnNames:(NSArray *)columnNames sqlWhereList:(NSArray *)sqlWhereList orderByDics:(NSArray *)orderByDics;
+ (NSString *)insertSQL:(NSString *)tableName columnNames:(NSArray *)columnNames hasReplace:(BOOL)hasReplace;
+ (NSString *)updateSQL:(NSString *)tableName columnNames:(NSArray *)columnNames sqlWhereList:(NSArray *)sqlWhereList;
+ (NSString *)deleteSQL:(NSString *)tableName sqlWhereList:(NSArray *)sqlWhereList;

@end
```

## Select文生成

```lang:XXXSQLManager.m 

+ (NSString *)selectSQL:(NSString *)tableName columnNames:(NSArray *)columnNames sqlWhereList:(NSArray *)sqlWhereList orderByDics:(NSArray *)orderByDics
{
    if (!tableName || [tableName length] == 0) {
        return @"";
    }
    
    if (!columnNames || [columnNames count] == 0) {
        return @"";
    }
    
    // カラム名
    NSMutableString *columnNamesStr = [NSMutableString string];
    for (NSString *columnName in columnNames) {
        [columnNamesStr appendFormat:@"%@, ", columnName];
    }
    [columnNamesStr deleteCharactersInRange:NSMakeRange([columnNamesStr length] - 2, 2)];
    
    // Where句
    NSMutableString *wherePhrase = [NSMutableString string];
    for (XXXSQLWhere *where in sqlWhereList) {
        if ([where.value isEqualToString:SQLWhere_VALUE_EXIST]) {
            [wherePhrase appendFormat:@" %@ (%@ IS NOT NULL AND %@ != '') ", where.connector, where.columnName, where.columnName];
        }
        else if ([where.sign isEqualToString:@"LIKE"]) {
            [wherePhrase appendFormat:@" %@ %@ %@ '%%%@%%' ", where.connector, where.columnName, where.sign, where.value];
        }
        else if ([where.sign isEqualToString:@"IN"]) {
            [wherePhrase appendFormat:@" %@ %@ %@ (%@) ", where.connector, where.columnName, where.sign, where.value];
        }
        else {
            [wherePhrase appendFormat:@" %@ %@ %@ '%@' ", where.connector, where.columnName, where.sign, where.value];
        }
    }
    
    // Order by 句
    NSMutableString *orderByPhrase = [NSMutableString string];
    if (orderByDics) {
        [orderByPhrase appendFormat:@" ORDER BY "];
        
        for (NSDictionary *dic in orderByDics) {
            NSString *key = dic.allKeys[0];
            [orderByPhrase appendFormat:@" CAST(%@ AS INTEGER) %@, ", key, dic[key]];
        }
        
        [orderByPhrase deleteCharactersInRange:NSMakeRange([orderByPhrase length] - 2, 2)];
    }
    
    return [NSString stringWithFormat:@"SELECT %@ FROM %@ WHERE (1 = 1) %@ %@", columnNamesStr, tableName, wherePhrase, orderByPhrase];
}

@end
```

## Insert文生成

```lang:XXXSQLManager.m

+ (NSString *)insertSQL:(NSString *)tableName columnNames:(NSArray *)columnNames hasReplace:(BOOL)hasReplace
{
    if (!tableName || [tableName length] == 0) {
        return @"";
    }
    
    if (!columnNames || [columnNames count] == 0) {
        return @"";
    }
    
    // カラム名
    NSMutableString *columnNamesStr = [NSMutableString string];
    for (NSString *columnName in columnNames) {
        [columnNamesStr appendFormat:@"%@, ", columnName];
    }
    [columnNamesStr deleteCharactersInRange:NSMakeRange([columnNamesStr length] - 2, 2)];
    
    // 「:」 + カラム名
    NSMutableString *valuesNamesStr = [NSMutableString string];
    for (NSString *columnName in columnNames) {
        [valuesNamesStr appendFormat:@":%@, ", columnName];
    }
    [valuesNamesStr deleteCharactersInRange:NSMakeRange([valuesNamesStr length] - 2, 2)];
    
    // Replace句: PK項目が存在しない場合はINSERT、存在する場合はDELETE後、INSERTを行う
    NSString *replace = @"";
    if (hasReplace) {
        replace = @"OR REPLACE";
    }
    
    return [NSString stringWithFormat:@"INSERT %@ INTO %@ (%@) VALUES (%@)", replace, tableName, columnNamesStr, valuesNamesStr];
}
```

## Update文

```lang:XXXSQLWhere.m

+ (NSString *)updateSQL:(NSString *)tableName columnNames:(NSArray *)columnNames sqlWhereList:(NSArray *)sqlWhereList
{
    if (!tableName || [tableName length] == 0) {
        return @"";
    }
    
    if (!columnNames || [columnNames count] == 0) {
        return @"";
    }
    
    NSMutableString *columnNamesStr = [NSMutableString string];
    for (NSString *columnName in columnNames) {
        [columnNamesStr appendFormat:@" %@ = :%@, ", columnName, columnName];
    }
    [columnNamesStr deleteCharactersInRange:NSMakeRange([columnNamesStr length] - 2, 2)];
    
    // Where句
    NSMutableString *wherePhrase = [NSMutableString string];
    for (XXXSQLWhere *where in sqlWhereList) {
        [wherePhrase appendFormat:@" %@ %@ %@ '%@' ", where.connector, where.columnName, where.sign, where.value];
    }
    
    return [NSString stringWithFormat:@"UPDATE %@ SET %@ WHERE (1 = 1) %@", tableName, columnNamesStr, wherePhrase];
}
```

## Delete文

```lang:XXXSQLWhere.m

+ (NSString *)deleteSQL:(NSString *)tableName sqlWhereList:(NSArray *)sqlWhereList
{
    // Where句
    NSMutableString *wherePhrase = [NSMutableString string];
    for (XXXSQLWhere *where in sqlWhereList) {
        [wherePhrase appendFormat:@" %@ %@ %@ '%@' ", where.connector, where.columnName, where.sign, where.value];
    }
    
    return [NSString stringWithFormat:@"DELETE FROM %@ WHERE (1 = 1) %@", tableName, wherePhrase];
}
```

## SQLWhereクラス

```lang:XXXSQLWhere.h
@interface XXXSQLWhere : NSObject

#define SQLWhere_VALUE_EXIST @"NOT_NULL_NOT_BLANK"

- (id)init __attribute__((unavailable("init is not available")));

- (id)initWithParams:(NSString *)connector columnName:(NSString *)columnName sign:(NSString *)sign value:(NSString *)value;

@property (nonatomic, retain) NSString *connector;
@property (nonatomic, retain) NSString *columnName;
@property (nonatomic, retain) NSString *sign;
@property (nonatomic, retain) NSString *value;

@end
```

```lang:XXXSQLWhere.m
#import "XXXSQLWhere.h"

@implementation XXXSQLWhere

- (id)initWithParams:(NSString *)connector columnName:(NSString *)columnName sign:(NSString *)sign value:(NSString *)value
{
    self = [super init];
    
    if (self) {
        self.connector = connector;
        self.columnName = columnName;
        self.sign = sign;
        self.value = value;
    }
    
    return self;
}

- (void)dealloc
{
    self.connector = nil;
    self.columnName = nil;
    self.sign = nil;
    self.value = nil;
    
    [super dealloc];
}

@end
```

# 使い方

```lang:Select
    NSMutableArray *conditions = [NSMutableArray array];
    
    XXXSQLWhere *where = nil;
    
    where = [[XXXSQLWhere alloc] initWithParams:@"AND" columnName:@"DEL_FLG" sign:@"=" value:@"0"];
    [conditions addObject:where];
    [where release];

    NSString *selectSQL = [XXXSQLiteManager selectSQL:@"XXXXX_MST" columnNames:@[@"ID", @"COLUMN1", @"DEL_FLG"] sqlWhereList:conditions orderByDics:@[@{@"ID":@"ASC"}]];

```



```lang:Insert
    NSString *insertSQL = [XXXSQLiteManager insertSQL:@"XXXXX_MST" columnNames:@[@"ID", @"COLUMN1", @"DEL_FLG"] hasReplace:NO];
```

```lang:Update
    NSMutableArray *conditions = [NSMutableArray array];
    
    XXXSQLWhere *where = nil;
    
    where = [[XXXSQLWhere alloc] initWithParams:@"AND" columnName:@"COLUMN1" sign:@"=" value:value1];
    [conditions addObject:where];
    [where release];
    
    NSString *updateSQL = [XXXSQLiteManager updateSQL:@"XXXXX_TBL" columnNames:@[@"ID", @"COLUMN1", @"DEL_FLG"] sqlWhereList:conditions];
```

```lang:Delete

    NSMutableArray *conditions = [NSMutableArray array];
    
    XXXSQLWhere *where = nil;
    
    where = [[XXXSQLWhere alloc] initWithParams:@"AND" columnName:@"XXXXX_DELKB" sign:@"=" value:@"0"];
    [conditions addObject:where];
    [where release];
    
    NSString *deleteSQL = [XXXQLiteManager deleteSQL:@"XXXXX_TBL" sqlWhereList:conditions];
```