[swift] IOS SQLite 사용하기

• 별다른 설치 없이도 내부적으로 사용 가능 (외부 라이브러리 X)

Import

import SQLite3

Create DB

func createDB(path:String) -> OpaquePointer? {
        var db : OpaquePointer? = nil
        do {
            let filePath = try FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false).appendingPathComponent(path)
            //db 연결, 이미 존재한다면 다시 생성하지 않고 넘어감
            if sqlite3_open(filePath.path, &db) == SQLITE_OK {
                print("Succesfully create Database path : \\(filePath.path)")
                return db
            }
        }
        catch{
            print("ERROR in CreateDB - \\(error.localizedDescription)")
        }
        print("ERROR in CreateDB - sqlite3_open ")
        return nil
    }

Query 적용

func commitQuery(query:String) {
        
        var statement : OpaquePointer? = nil
        
				// query 적용
        if sqlite3_prepare_v2(self.db, query, -1, &statement, nil) == SQLITE_OK {
            if sqlite3_step(statement) == SQLITE_DONE {
                print("Query SuccessFully \\(String(describing: db))")
            }
            else{
                let errorMessage = String(cString: sqlite3_errmsg(db))
                print("\\n Query fail :  \\(errorMessage)")
            }
        }
        else{
            let errorMessage = String(cString: sqlite3_errmsg(db))
            print("\\n Query Fail ! :\\(errorMessage)")
            
        }
        sqlite3_finalize(statement)
    }

Read Data

func readSULData(query:String) -> [SulVO?] {
                
        var statement : OpaquePointer? = nil
        var sulList:[SulVO?] = []
        
        if sqlite3_prepare_v2(self.db, query, -1, &statement, nil) == SQLITE_OK {
            
            while sqlite3_step(statement) == SQLITE_ROW{
                // 읽은 Data 변수에 담기
                let name =  String(cString: sqlite3_column_text(statement, 0))
                let price = sqlite3_column_int(statement, 1)
                // 반환할 리스트에 추가
                sulList.append(SulVO(name: name, price: Int(price)))
            }
        }
        else {
            let errorMessage = String(cString: sqlite3_errmsg(db))
            print("\\n read Data prepare fail! : \\(errorMessage)")
        }
        sqlite3_finalize(statement)
        // 결과 리스트 반환
        return sulList
    }

 

사용예시

DB, Table 생성

// DB Setting ( DB, table 생성 )
let myDB = DBHelper.shared;  //싱글톤 사용
myDB.createDB(path: "sulSqlite.sqlite")
var cTableQ:String = "create table if not exists sulListTB(name text not null primary key, price INTEGER not null);"
myDB.commitQuery(query: cTableQ)

Insert Data

// Test Data 추가
var sul = SulVO(name: "맥주", price:4000)
var testDataInsert = "insert into sulListTB(name, price) values(\\"" + sul.name + "\\",\\"" + String(sul.price) + "\\");"
myDB.commitQuery(query: testDataInsert)

ReadTable

var readSulQ = "select * from sulListTB;"
let myDB = DBHelper.shared;
// 읽어온 데이터 저장
var sulList:[SulVO] = myDB.readSULData(query: readSulQ) as! [SulVO]