SQLite CRUD (Cmd + C)

복사&붙여넣기 후 프로젝트에 맞게 수정해서 사용하기!

 

[ Import ]

import SQLite3

 

[ SQLite : Create (viewDidLoad) ]

// SQLite 생성하기
func createSQLite(){
	let fileURL = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false).appendingPathComponent("<#SchemeName#>.sqlite")
        
	if sqlite3_open(fileURL.path, &db) != SQLITE_OK{
    	// SQLite가 열리지 않으면?
    	print("error opening database")
	}
	if sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS <#SQLiteTableName#>(id INTEGER PRIMARY KEY AUTOINCREMENT, <#Column01#> TEXT, <#Column02#> TEXT)", nil, nil, nil) != SQLITE_OK{
    	let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("error creating table : \(errmsg)")
    }
        
    // SELECT
    selectValues()
} // createSQLite

 

[ SELECT ]

func selectValues() {
    // Init Array
    <#Array<ClassType>#>.removeAll()
        
    // Query
    let queryString = "SELECT * FROM <#databaseTableName#>"
        
    // Statement
    var stmt: OpaquePointer?
        
    // Prepare
    if sqlite3_prepare(db, queryString, -1, &stmt, nil) != SQLITE_OK{
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("error preparing select : \(errmsg)")
        return
    }
        
        // 한줄씩 가져오기
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            // Column Type : Integer
            let <#column0#> = sqlite3_column_int(stmt, 0)
            // Column Type : Text
            let <#column1#> = String(cString: sqlite3_column_text(stmt, 1))
           
            // if need to check
            // print(<#code#>)
           
            // describing: unwrapping Optional
            <#ArrayName#>.append(<#ClassType#>(id: Int(<#column0#>),
                                       name: String(describing: <#column1#>)))
        }
        // 값이 들어왔으면 Table View를 새로고침
        self.<#tableViewName#>.reloadData()
    }

 

[ INSERT ]

func insertValues() {

	// Statement
    var stmt: OpaquePointer?
    
    // 한글 깨짐 방지
    let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
        
    // INSERT 할 DATA 값
    let <#Column1#> = <#TextField1#>.text?.trimmingCharacters(in: .whitespacesAndNewlines)
    let <#Column2#> = <#TextField2#>.text?.trimmingCharacters(in: .whitespacesAndNewlines)
    
    // Query
    let queryString = "INSERT INTO <#TableName#>(<#Column01#>, <#Column02#>) VALUES (?,?)"
        
    // Prepare
    if sqlite3_prepare(db, queryString, -1, &stmt, nil) != SQLITE_OK{
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("error preparing insert : \(errmsg)")
        return
    }
        
    // 1번째 VALUES(?) 처리
    if sqlite3_bind_text(stmt, 1, <#Column1#>, -1, SQLITE_TRANSIENT) != SQLITE_OK{
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("error binding <#Column1#> : \(errmsg)")
        return
    }
    // 2번째 VALUES(?) 처리
    if sqlite3_bind_text(stmt, 2, <#Column2#>, -1, SQLITE_TRANSIENT) != SQLITE_OK{
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("error binding <#Column2#> : \(errmsg)")
        return
    }
    // 실행시키기
    if sqlite3_step(stmt) != SQLITE_DONE{
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("failure inserting <#TableName#> : \(errmsg)")
        return
    }
        
    // Result Alert    
    let resultAlert = UIAlertController(title: "결과", message: "입력 되었습니다.", preferredStyle: .alert)
    let okAction = UIAlertAction(title: "네, 알겠습니다.", style: .default, handler: {_ in
        self.navigationController?.popViewController(animated: true)
    })   
    resultAlert.addAction(okAction)
    present(resultAlert, animated: true, completion: nil)
    
} // insertValues

 

[ UPDATE ]

func updateValues() {

	//Statement
    var stmt: OpaquePointer?
    
    // 한글 깨짐 방지
    let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
        
    // Query    
    let queryString = "UPDATE <#SQLiteTableName#> SET <#Column1#> = ?, <#Column2#> = ? WHERE <#KeyValue#> = ?"
        
    // != SQLITE_OK 가 아니면 {  } 실행
    if sqlite3_prepare(db, queryString, -1, &stmt, nil) != SQLITE_OK{
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("error preparing update : \(errmsg)")
        return    
    }
        
    // 1번째 VALUES(?) 처리
    if sqlite3_bind_text(stmt, 1, <#TextField1#>, -1, SQLITE_TRANSIENT) != SQLITE_OK{
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("error binding <#Column1#> : \(errmsg)")
        return
    }
    // 2번째 VALUES(?) 처리
    if sqlite3_bind_text(stmt, 2, <#TextField1#>, -1, SQLITE_TRANSIENT) != SQLITE_OK{
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("error binding <#Column2#> : \(errmsg)")
        return
    }
    
    // WHERE '?' 처리
    if sqlite3_bind_text(stmt, 4, <#KeyValue>, -1, SQLITE_TRANSIENT) != SQLITE_OK{
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("error binding <#Column3#> : \(errmsg)")
        return
    }
        
    // 실행시키기
    if sqlite3_step(stmt) != SQLITE_DONE{
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("failure updating <#SQLiteTableName#> : \(errmsg)")
        return
    }

    print("Info saved successfully")
    }

 

 

[ DELETE ]

func deleteValues(){
	// Statement
    var stmt: OpaquePointer?
    
    // 한글 깨짐 방지
    let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
        
    let queryString = "DELETE FROM <#SQLiteTableName#> WHERE <#KEYColumn#> = ?"
        
    // Prepare
    if sqlite3_prepare(db, queryString, -1, &stmt, nil) != SQLITE_OK{
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("error preparing update : \(errmsg)")
        return    // return 할께 없는게 이게 있으면? 그냥 함수를 빠져나가는 것이다!
    }
        
    // Update 할 대상을 찾는 식별값
    if sqlite3_bind_text(stmt, 1, <#KeyValueInTextField#>, -1, SQLITE_TRANSIENT) != SQLITE_OK{
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("error binding id : \(errmsg)")
        return
    }
        
    // 실행시키기
    if sqlite3_step(stmt) != SQLITE_DONE{
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("failure inserting <#SQLiteTableName#> : \(errmsg)")
        return
        }

    print("Info delete successfully")
        
} // deleteValues