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=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("""\n""".toRegex(), " ")
val regex = """\b\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(), key = get().trim(), type = get()))
}
}
} 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 =
queryBranches.forEach { queryBranch ->
val param = dict[queryBranch.key]
if (queryBranch.doSetParameter(param, paramIndex, this)) {
paramIndex++
}
}
// 执行查询
executeQuery().apply {
while (next()) {
val result = HashMap<String, Any>()
metaData.apply {
(..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 "",
"s2" to "hello,world",
"userId" to ""
)))
}