Uaw81XKx

1//
2//  Dao.swift
3//  FMDBDemo
4//
5//  Created by Frank.Chen on 2018/2/3.
6//  Copyright © 2018年 Frank.Chen. All rights reserved.
7//
8
9import UIKit
10import FMDB
11
12class Dao: NSObject {
13    
14    static let shared = Dao()
15    
16    var fileName: String = "DEPARTMENT_DATA.sqlite" // sqlite name
17    var filePath: String = "" // sqlite path
18    var database: FMDatabase! // FMDBConnection
19    
20    private override init() {
21        super.init()
22        
23        // 取得sqlite在documents下的路徑(開啟連線用)
24        self.filePath = NSSearchPathForDirectoriesInDomains(FileManager.SearchPathDirectory.documentDirectory, FileManager.SearchPathDomainMask.userDomainMask, true)[0] + "/" + self.fileName
25        
26        print("filePath: \(self.filePath)")
27    }
28    
29    deinit {
30        print("deinit: \(self)")
31    }
32    
33    /// 生成 .sqlite 檔案並創建表格，只有在 .sqlite 不存在時才會建立
34    func createTable() {
35        let fileManager: FileManager = FileManager.default
36        
37        // 判斷documents是否已存在該檔案
38        if !fileManager.fileExists(atPath: self.filePath) {
39            
40            // 開啟連線
41            if self.openConnection() {
42                let createTableSQL = """
43                    CREATE TABLE DEPARTMENT (
44                    DEPARTMENT_ID integer  NOT NULL  PRIMARY KEY DEFAULT 0,
45                    DEPT_CH_NM Varchar(100),
46                    DEPT_EN_NM Varchar(100))
47                """
48                self.database.executeStatements(createTableSQL)
49                print("file copy to: \(self.filePath)")
50            }
51        } else {
52            print("DID-NOT copy db file, file allready exists at path:\(self.filePath)")
53        }
54    }
55    
56    /// 取得 .sqlite 連線
57    ///
58    /// - Returns: Bool
59    func openConnection() -> Bool {
60        var isOpen: Bool = false
61        
62        self.database = FMDatabase(path: self.filePath)
63        
64        if self.database != nil {
65            if self.database.open() {
66                isOpen = true
67            } else {
68                print("Could not get the connection.")
69            }
70        }
71        
72        return isOpen
73    }
74    
75    /// 新增部門資料
76    ///
77    /// - Parameters:
78    ///   - departmentEnglistName: 部門中文名稱
79    ///   - departmentChineseName: 部門英文名稱
80    func insertData(withDepartmentChineseName departmentChineseName: String, departmentEnglishName: String) {
81        
82        if self.openConnection() {
83            let insertSQL: String = "INSERT INTO DEPARTMENT (DEPARTMENT_ID, DEPT_CH_NM, DEPT_EN_NM) VALUES((SELECT IFNULL(MAX(DEPARTMENT_ID), 0) + 1 FROM DEPARTMENT), ?, ?)"
84            
85            if !self.database.executeUpdate(insertSQL, withArgumentsIn: [departmentChineseName]) {
86                print("Failed to insert initial data into the database.")
87                print(database.lastError(), database.lastErrorMessage())
88            }
89            
90            self.database.close()
91        }
92    }
93    
94    /// 更新部門資料
95    ///
96    /// - Parameters:
97    ///   - departmentId: 部門ID
98    ///   - departmentEnglistName: 部門中文名稱
99    ///   - departmentChineseName: 部門英文名稱
100    func updateData(withDepartmentId departmentId: Int, departmentChineseName: String, departmentEnglistName: String) {
101        if self.openConnection() {
102            let updateSQL: String = "UPDATE DEPARTMENT SET DEPT_CH_NM = ?, DEPT_EN_NM = ? WHERE DEPARTMENT_ID = ?"
103            
104            do {
105                try self.database.executeUpdate(updateSQL, values: [departmentChineseName, departmentEnglistName, departmentId])
106            } catch {
107                print(error.localizedDescription)
108            }
109            
110            self.database.close()
111        }
112    }
113    
114    /// 取得部門的所有資料
115    ///
116    /// - Returns: 部門資料
117    func queryData() -> [Department] {
118        var departmentDatas: [Department] = [Department]()
119        
120        if self.openConnection() {
121            let querySQL: String = "SELECT * FROM DEPARTMENT"
122            
123            do {
124                let dataLists: FMResultSet = try database.executeQuery(querySQL, values: nil)
125                
126                while dataLists.next() {
127                    let department: Department = Department(departmentId: Int(dataLists.int(forColumn: "DEPARTMENT_ID")), departmentChNm: dataLists.string(forColumn: "DEPT_CH_NM")!, departmentEnNm: dataLists.string(forColumn: "DEPT_EN_NM")!)
128                    departmentDatas.append(department)
129                }
130            } catch {
131                print(error.localizedDescription)
132            }
133        }
134        
135        return departmentDatas
136    }
137    
138    /// 刪除部門資料
139    ///
140    /// - Parameter departmentId: 部門ID
141    func deleteData(withDepartmentId departmentId: Int) {
142        if self.openConnection() {
143            let deleteSQL: String = "DELETE FROM DEPARTMENT WHERE DEPARTMENT_ID = ?"
144            
145            do {
146                try self.database.executeUpdate(deleteSQL, values: [departmentId])
147            } catch {
148                print(error.localizedDescription)
149            }
150            
151            self.database.close()
152        }
153    }
154}