Post

WCDB之使用原生SQL语句查询(四)

Posted
By 3 min read

参考：

一些特殊的场景可能wcdb不支持，需要直接使用sql语句进行操作，主要是查询

方案：利用Handle、StatementSelect、Column、ResultColumn执行sql

1、设置sql语句(由于会自动拼接Select关键字上去，不要加SELECT关键字)：Score_Table.score, Score_Table.identifier FROM Score_Table
1、将sql语句转换为Statement语句：StatementSelect().select(Column(named: sqlStr))
2、执行sql语句：let handle = try db.getHandle() try handle.prepare(statementSelect)
3、使用处理结果:while try handle.step() { let val0 = handle.value(atIndex: 0).doubleValue}

简单的单表使用自定义sql语句完整demo如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

// MARK: - 使用原生SQL
    func selectBySql1() {
        let db = Database(at: dbPath)
        do {
            // 1、准备sql语句，由于会自动拼接SELECT,需要将手写的SELECT去掉
            let sql = "SELECT Score_Table.identifier, Score_Table.stuId, Score_Table.score FROM Score_Table".replacingOccurrences(of: "SELECT", with: "")
            let statementSelect = StatementSelect().select(Column(named: sql))
            // 执行查询
            let handle = try db.getHandle()
            try handle.prepare(statementSelect)
            // 获取查询结果
            while try handle.step() {
                let val0 = handle.value(atIndex: 0).intValue
                let val1 = handle.value(atIndex: 1).intValue
                let val2 = handle.value(atIndex: 2).doubleValue
                print("selectBySql1 identifier:\(val0) stuId:\(val1) score:\(val2)")
            }
        } catch {
            print(error)
        }
    }
    func selectBySql2() {
        let db = Database(at: dbPath)
        do {
            // 1、准备sql语句，由于会自动拼接SELECT,需要将手写的SELECT去掉
            // 2、where条件语句也一起拼接在sql语句中
            let sql = "SELECT Score_Table.identifier, Score_Table.stuId, Score_Table.score FROM Score_Table where Score_Table.stuId=345".replacingOccurrences(of: "SELECT", with: "")
            let statementSelect = StatementSelect().select(Column(named: sql))
            // 执行查询
            let handle = try db.getHandle()
            try handle.prepare(statementSelect)
            // 获取查询结果
            while try handle.step() {
                let val0 = handle.value(atIndex: 0).intValue
                let val1 = handle.value(atIndex: 1).intValue
                let val2 = handle.value(atIndex: 2).doubleValue
                print("selectBySql2: identifier:\(val0) stuId:\(val1) score:\(val2)")
            }
        } catch {
            print(error)
        }
    }
    func selectBySql3() {
        let db = Database(at: dbPath)
        do {
            // 1、准备sql语句，由于会自动拼接SELECT,需要将手写的SELECT去掉
            // 2、where条件语句由WCDB的方式设置
            let sql = "SELECT Score_Table.identifier, Score_Table.stuId, Score_Table.score FROM Score_Table".replacingOccurrences(of: "SELECT", with: "")
            let statementSelect = StatementSelect().select(Column(named: sql)).where(ScoreDBModel.Properties.stuId==345)
            // 执行查询
            let handle = try db.getHandle()
            try handle.prepare(statementSelect)
            // 获取查询结果
            while try handle.step() {
                let val0 = handle.value(atIndex: 0).intValue
                let val1 = handle.value(atIndex: 1).intValue
                let val2 = handle.value(atIndex: 2).doubleValue
                print("selectBySql3 identifier:\(val0) stuId:\(val1) score:\(val2)")
            }
        } catch {
            print(error)
        }
    }
db, wcdb
db wcdb
This post is licensed under CC BY 4.0 by the author.