轻量化 SQLbuilder

本篇介绍一下以前写的一个轻量化的sql 语句 builder

简介

需求

解决fmdb中手写sql的麻烦

• 为什么不用三方,wcdb 等进一步的sqlite 封装
  • 业务需求简单,只需要非常轻量级的数据库操作,wcdb封装过度,且与sqlite冲突
• 为什么没有介绍 FMDB 的使用
  • 本篇只是 sql语句封装,仅此而已,FMDB 可以另开一篇水一下

思路

链式调用,封装增删改查,生成 sql 语句

结果

下面是训练 gpt生成的KKSQLBuilder类,脑子先想好自己想要的结果,让 gpt 自己去训练生成即可

import KKLog

public class KKSQLBuilder {
    private let log = KKLog(label: "sql")
    private var selectClause: String = ""
    private var insertClause: String = ""
    private var updateClause: String = ""
    private var deleteClause: String = ""
    public var parameters: [Any] = []
    private var whereClause: String?
    private var limitClause: String?
    private var orderByClause: String?
    /// 最后执行的sql语句
    public private(set) var sql: String = ""
}

public enum KKSQLOrder: String {
    case asc = "ASC"
    case desc = "DESC"
}

public extension KKSQLBuilder {
    // 设置 SELECT 语句
    func select(columns: String = "*", from table: String) -> KKSQLBuilder {
        selectClause = "SELECT \(columns) FROM \(table)"
        return self
    }
    // 设置 INSERT 语句
    func insert(into table: String, values: [String: Any]) -> KKSQLBuilder {
        // 获取有序的列名
        let keys = values.keys
        let values = keys.map { values[$0] }
        // 根据有序列名获取对应的值
        let placeholders = Array(repeating: "?", count: keys.count).joined(separator: ", ")
        insertClause = "INSERT INTO \(table) (\(keys.joined(separator: ", "))) VALUES (\(placeholders))"
        // 根据有序列名提取对应的值
        for value in values {
            parameters.append(value)
        }
        return self
    }
    // 设置 INSERT 语句
    func insertOrReplace(into table: String, values: [String: Any]) -> KKSQLBuilder {
        // 获取有序的列名
        let keys = values.keys
        let values = keys.map { values[$0] }
        // 根据有序列名获取对应的值
        let placeholders = Array(repeating: "?", count: keys.count).joined(separator: ", ")
        insertClause = "INSERT OR REPLACE INTO \(table) (\(keys.joined(separator: ", "))) VALUES (\(placeholders))"
        // 根据有序列名提取对应的值
        for value in values {
            parameters.append(value)
        }
        return self
    }
    
    // 设置 DELETE 语句
    func delete(from table: String) -> KKSQLBuilder {
        deleteClause = "DELETE FROM \(table)"
        return self
    }

    // 设置 UPDATE 语句
    func update(_ table: String, set values: [String: Any]) -> KKSQLBuilder {
        let columns = values.keys.sorted()
        let setClause = columns.map { "\($0)=?" }.joined(separator: ",")
        updateClause = "UPDATE \(table) SET \(setClause)"

        for column in columns {
            parameters.append(values[column])
        }

        return self
    }
    
    // 添加 AND 条件，使用占位符
    func `where`(_ condition: String, values: [Any]) -> KKSQLBuilder {
        if !condition.isEmpty {
            whereClause = "WHERE \(condition)"
            parameters += values
        }
        return self
    }

    // 添加 LIMIT 子句
    func limit(_ count: Int) -> KKSQLBuilder {
        limitClause = "LIMIT \(count)"
        return self
    }

    // 添加 ORDER BY 子句
    func orderBy(_ v: [(column: String, asc: KKSQLOrder)]) -> KKSQLBuilder {
        let orders = v.map { " \($0) \($1)"}.joined(separator: ",")
        orderByClause = "ORDER BY" + orders
        return self
    }

    // 构建最终 SQL 语句并返回参数
    @discardableResult
    func apply() -> Self {
        var query = ""

        if !selectClause.isEmpty {
            query += selectClause
        } else if !insertClause.isEmpty {
            query += insertClause
        } else if !updateClause.isEmpty {
            query += updateClause
        } else if !deleteClause.isEmpty {
            query += deleteClause
        }

        // 添加 WHERE
        if let whereClause = whereClause {
            query += " \(whereClause)"
        }

        // 添加 ORDER BY 子句
        if let orderBy = orderByClause {
            query += " \(orderBy)"
        }

        // 添加 LIMIT 子句
        if let limit = limitClause {
            query += " \(limit)"
        }
        query += ";"
        
        sql = query
        log.info(query, parameters)
        return self
    }
}


简单使用

func insertIfNotExist(db: FMDatabase, model: ChatMessage) throws -> Bool {
    let sql = KKSQLBuilder()
        .select(from: tableName)
        .where("\(msgId) = ?", values: [model.msgId])
        .apply()

    let old: ChatMessage? = try db.executeQuery(sql.sql, values: sql.parameters).toHandyJSON().first

    if old == nil {
        try insert(db: db, model: model)
    }
    return true
}

func queryMessages(db: FMDatabase, targetSessionId: Int, pageSize: Int, lastTimestamp: Int64?, lastTimestampBefore: Bool) throws -> [ChatMessage] {
    let timestampCondition: String
    let order: KKSQLOrder
    if let lastTimestamp = lastTimestamp {
        timestampCondition = lastTimestampBefore ? " < \(lastTimestamp)" : "> \(lastTimestamp)"
        order = lastTimestampBefore ? .desc : .asc
    } else {
        timestampCondition = "> 0"
        order = .desc
    }
    let sql = KKSQLBuilder()
        .select(from: tableName)
        // 是sessionId, 激活状态的message, timestamp升降序的条件
        .where("""
        \(sessionId) = \(targetSessionId)
        and \(status) = \(ChatSessionStatus.active.rawValue)
        and \(timestamp)\(timestampCondition)
        """, values: [])
        .limit(pageSize)
        .orderBy([(timestamp, order)])
        .apply()
    let list: [ChatMessage] = try db.executeQuery(sql.sql, values: sql.parameters).toHandyJSON()
    return list
}

PS

本篇只介绍 sql 的生成,移动端的 sql 一般都是比较简单的,最多也只需要个两三张表的联查,有需自取即可.