IOS (Swift) SQLite 사용하기2 - 테이블생성, 데이터 INSERT, UPDATE, SELECT

SQLite 사용하기2 - 테이블생성, 데이터 INSERT, UPDATE, SELECT 

- db.executeStatements()

- db.executeUpdate()

- db.executeQuery()

ViewController.swift)

import UIKit
 
class ViewController: UIViewController {
    //ui
    @IBOutlet weak var txtText: UITextView!
    @IBOutlet weak var btnInsert: UIButton!
    @IBOutlet weak var btnUpdate: UIButton!
    @IBOutlet weak var btnSelect: UIButton!
    
 
    var dbPath : String = ""
    let dbName : String = "test.db"
    
    let sqlCreate : String = "CREATE TABLE IF NOT EXISTS TEST ( "
                                + " ID    INTEGER PRIMARY KEY AUTOINCREMENT , "
                                + " NAME  TEXT , "
                                + " PHONE TEXT "
                            + ")"
    
    //데이터 입력
    let sqlInsert : String = "INSERT INTO TEST (NAME, PHONE) VALUES ('권혁준' , '010-1234-1234')"
    
    //데이터 보기
    let sqlSelect : String = "SELECT ID, NAME, PHONE FROM TEST ORDER BY ID"
    
    //데이터 수정
    let sqlUpdate : String = "UPDATE TEST SET NAME='홍길동', PHONE='010-1234-5678' WHERE ID=?"
    
 
    override func viewDidLoad() {
        super.viewDidLoad()
        
        let dirPath = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true)
        let docPath = dirPath[0]
        
        dbPath = docPath+"/"+dbName
        
        let fileManager = FileManager.default
        if !fileManager.fileExists(atPath: dbPath as String) {
            //DB 객체 생성
            let database : FMDatabase? = FMDatabase(path: dbPath as String)
         
            if let db = database {
                //DB 열기
                db.open()
                //TABLE 생성
                db.executeStatements(sqlCreate)
                //DB 닫기
                db.close()
                
                NSLog("TABLE 생성 성공")
            }else{
                NSLog("DB 객체 생성 실패")
            }
        }
    }
    
    @IBAction func onDBInsert(_ sender: Any) {
        //DB 객체 생성
        let database : FMDatabase? = FMDatabase(path: dbPath as String)
        
        if let db = database {
            //DB 열기
            db.open()
            //INSERT
            db.executeUpdate(sqlInsert, withArgumentsIn: [])
            
            if db.hadError() {
                txtText.text = "DB INSERT 실패 \(db.lastErrorMessage())"
            }else{
                txtText.text = "DB INSERT 성공"
                db.commit()
            }
            
            //DB 닫기
            db.close()
        }else{
            NSLog("DB 객체 생성 실패")
        }
    }
    
    @IBAction func onDBUpdate(_ sender: Any) {
        //DB 객체 생성
        let database : FMDatabase? = FMDatabase(path: dbPath as String)
        
        if let db = database {
            //DB 열기
            db.open()
            //UPDATE
            db.executeUpdate(sqlUpdate, withArgumentsIn: [1])
            
            if db.hadError() {
                txtText.text = "DB UPDATE 실패 \(db.lastErrorMessage())"
            }else{
                txtText.text = "DB UPDATE 성공"
                db.commit()
            }
            
            //DB 닫기
            db.close()
        }else{
            NSLog("DB 객체 생성 실패")
        }
    }
    
    @IBAction func onDBSelect(_ sender: Any) {
        //DB 객체 생성
        let database : FMDatabase? = FMDatabase(path: dbPath as String)
        
        if let db = database {
            //DB 열기
            db.open()
            //SELECT
            let result : FMResultSet? = db.executeQuery(sqlSelect, withArgumentsIn: [])
            if let rs = result {
                var select : String = ""
                while rs.next(){
                    select += "ID : "+rs.string(forColumn: "ID")! + ", NAME : " + rs.string(forColumn: "NAME")! + ", PHONE : " + rs.string(forColumn: "PHONE")! + "\n"
                }
                txtText.text = select
            }else{
                txtText.text = "데이터 없음"
            }
            
            //DB 닫기
            db.close()
        }else{
            NSLog("DB 객체 생성 실패")
        }
    }
    
 
    override func didReceiveMemoryWarning() {
        super.didReceiveMemoryWarning()
    
    }
}
 
 

결과

데이터 저장

데이터 보기

데이터 수정

데이터 보기