1 package com.dx.efuwu.core
  2 
  3 import org.apache.commons.lang.StringUtils
  4 import java.sql.PreparedStatement
  5 
  6 /**
  7  * sql 模板处理
  8  * @author sunzq
  9  * 2017/06/02
 10  */
 11 
 12 /**
 13  * 查询的一个条件句
 14  */
 15 class QueryBranch(val content: String, val key: String, val type: String) {
 16 
 17     override fun toString(): String {
 18         return "{content:$content, key:$key, type: $type}"
 19     }
 20 
 21     fun build(input: String?): String {
 22         return if (StringUtils.isNotBlank(input)) content.replace("""(?:#|##|@|@@)\{.*}""".toRegex(), replacement = "?")
 23         else if (type.length > 1) " 1=0 "
 24         else " 1=1 "
 25     }
 26 
 27     fun doSetParameter(input: String?, index: Int, pstmt: PreparedStatement): Boolean {
 28         input?.takeIf {
 29             StringUtils.isNotBlank(it)
 30         }?.run {
 31             when (type) {
 32                 "#", "##" -> pstmt.setString(index, input)
 33                 "@", "@@" -> pstmt.setLong(index, input.toLong())
 34             }
 35             return true
 36         }
 37         return false
 38     }
 39 
 40 }
 41 
 42 
 43 /**
 44  * sql 查询模板处理器，并不提供 sql 是否正确的解析.
 45  * @ 原样填写,不自动填充单引号, 为空则 true @@ 为空则 false
 46  * # 自动包一下引号, 为空则 true  ## 为空则 false
 47  */
 48 class SQLTemplateExecutor(sql: String) {
 49 
 50 
 51     /**
 52      * 查询条件句
 53      */
 54     val queryBranches = ArrayList<QueryBranch>()
 55 
 56     /**
 57      * 切割后的查询 sql
 58      */
 59     val splitQueryStrings = ArrayList<String>()
 60 
 61     init {
 62         val _sql = sql.replace("""\n""".toRegex(), " ")
 63         val regex = """\b\S+(?:\s|=|>|<|in|not|like|IN|NOT|LIKE)+(#|##|@|@@)\{([^}]+)}""".toRegex()
 64         splitQueryStrings.addAll(_sql.split(regex))
 65         regex.findAll(_sql).iterator().forEach { matchResult ->
 66             matchResult.groupValues.apply {
 67                 queryBranches.add(QueryBranch(content = get(0), key = get(2).trim(), type = get(1)))
 68             }
 69         }
 70     }
 71 
 72     fun doQuery(dict: Map<String, String?>): ArrayList<Map<String, Any>> {
 73         val sqlBuilder = StringBuilder()
 74         val queryBranchIterator = queryBranches.iterator()
 75         splitQueryStrings.forEach { queryString ->
 76             sqlBuilder.append(queryString)
 77             if (queryBranchIterator.hasNext()) {
 78                 queryBranchIterator.next().apply {
 79                     sqlBuilder.append(build(dict[key]))
 80                 }
 81             }
 82         }
 83 
 84         val queryResults = ArrayList<Map<String, Any>>()
 85         SQL_SERVER_DATA_SOURCE.connection.apply {
 86             prepareStatement(sqlBuilder.toString()).apply {
 87                 // 设置查询参数
 88                 var paramIndex = 1
 89                 queryBranches.forEach { queryBranch ->
 90                     val param = dict[queryBranch.key]
 91                     if (queryBranch.doSetParameter(param, paramIndex, this)) {
 92                         paramIndex++
 93                     }
 94                 }
 95                 // 执行查询
 96                 executeQuery().apply {
 97                     while (next()) {
 98                         val result = HashMap<String, Any>(8)
 99                         metaData.apply {
100                             (1..columnCount).forEach { i ->
101                                 result[getColumnLabel(i)] =
102                                         when (getColumnTypeName(i)) {
103                                             "int" -> getInt(i)
104                                             else -> getString(i)
105                                         }
106                             }
107                         }
108                         queryResults.add(result)
109                     }
110                     close()
111                 }
112                 close()
113             }
114             close()
115         }
116         return queryResults
117     }
118 }
119 
120 fun main(args: Array<String>) {
121     val sql = """select * from test"""
122     println(SQLTemplateExecutor(sql).doQuery(hashMapOf(
123             "userName1" to "大%",
124             "age" to "18",
125             "s2" to "hello,world",
126             "userId" to "10000"
127     )))
128 }