kotlin 写的一个简单 sql 查询解析器

package com.dx.efuwu.core

import org.apache.commons.lang.StringUtils
import java.sql.PreparedStatement

/**
 * sql 模板处理
 * @author sunzq
 * 2017/06/02
 */

/**
 * 查询的一个条件句
 */
class QueryBranch(val content: String, val key: String, val type: String) {

    override fun toString(): String {
        return "{content:$content, key:$key, type: $type}"
    }

    fun build(input: String?): String {
        return if (StringUtils.isNotBlank(input)) content.replace("""(?:#|##|@|@@){.*}""".toRegex(), replacement = "?")
        else if (type.length > 1) " 1=0 "
        else " 1=1 "
    }

    fun doSetParameter(input: String?, index: Int, pstmt: PreparedStatement): Boolean {
        input?.takeIf {
            StringUtils.isNotBlank(it)
        }?.run {
            when (type) {
                "#", "##" -> pstmt.setString(index, input)
                "@", "@@" -> pstmt.setLong(index, input.toLong())
            }
            return true
        }
        return false
    }

}


/**
 * sql 查询模板处理器，并不提供 sql 是否正确的解析.
 * @ 原样填写,不自动填充单引号, 为空则 true @@ 为空则 false
 * # 自动包一下引号, 为空则 true  ## 为空则 false
 */
class SQLTemplateExecutor(sql: String) {


    /**
     * 查询条件句
     */
    val queryBranches = ArrayList<QueryBranch>()

    /**
     * 切割后的查询 sql
     */
    val splitQueryStrings = ArrayList<String>()

    init {
        val _sql = sql.replace("""
""".toRegex(), " ")
        val regex = """S+(?:s|=|>|<|in|not|like|IN|NOT|LIKE)+(#|##|@|@@){([^}]+)}""".toRegex()
        splitQueryStrings.addAll(_sql.split(regex))
        regex.findAll(_sql).iterator().forEach { matchResult ->
            matchResult.groupValues.apply {
                queryBranches.add(QueryBranch(content = get(0), key = get(2).trim(), type = get(1)))
            }
        }
    }

    fun doQuery(dict: Map<String, String?>): ArrayList<Map<String, Any>> {
        val sqlBuilder = StringBuilder()
        val queryBranchIterator = queryBranches.iterator()
        splitQueryStrings.forEach { queryString ->
            sqlBuilder.append(queryString)
            if (queryBranchIterator.hasNext()) {
                queryBranchIterator.next().apply {
                    sqlBuilder.append(build(dict[key]))
                }
            }
        }

        val queryResults = ArrayList<Map<String, Any>>()
        SQL_SERVER_DATA_SOURCE.connection.apply {
            prepareStatement(sqlBuilder.toString()).apply {
                // 设置查询参数
                var paramIndex = 1
                queryBranches.forEach { queryBranch ->
                    val param = dict[queryBranch.key]
                    if (queryBranch.doSetParameter(param, paramIndex, this)) {
                        paramIndex++
                    }
                }
                // 执行查询
                executeQuery().apply {
                    while (next()) {
                        val result = HashMap<String, Any>(8)
                        metaData.apply {
                            (1..columnCount).forEach { i ->
                                result[getColumnLabel(i)] =
                                        when (getColumnTypeName(i)) {
                                            "int" -> getInt(i)
                                            else -> getString(i)
                                        }
                            }
                        }
                        queryResults.add(result)
                    }
                    close()
                }
                close()
            }
            close()
        }
        return queryResults
    }
}

fun main(args: Array<String>) {
    val sql = """select * from test"""
    println(SQLTemplateExecutor(sql).doQuery(hashMapOf(
            "userName1" to "大%",
            "age" to "18",
            "s2" to "hello,world",
            "userId" to "10000"
    )))
}