nbcp.myoql.db.sql.base.BaseAliasSqlSect.kt Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of ktmyoql Show documentation
Show all versions of ktmyoql Show documentation
kotlin orm -- mysql,mongo , just like ktorm
The newest version!
package nbcp.myoql.db.sql.base
import nbcp.base.comm.JsonMap
import nbcp.base.extend.*
import nbcp.base.utils.CodeUtil
import nbcp.base.utils.StringUtil
import nbcp.myoql.db.db
import nbcp.myoql.db.sql.component.SqlQueryClip
import nbcp.myoql.db.sql.component.WhereData
import nbcp.myoql.db.sql.enums.DbType
import nbcp.myoql.db.sql.extend.jsonContains
import nbcp.myoql.db.sql.extend.jsonEquals
import nbcp.myoql.db.sql.extend.jsonLength
import nbcp.myoql.db.sql.extend.proc_value
import java.io.Serializable
abstract class BaseAliasSqlSect : Serializable {
var aliaValue: String = ""
abstract fun toSingleSqlData(): SqlParameterData;
open fun getAliasName(): String {
return this.aliaValue
}
private fun sqlMatch(op: String, value: Serializable): WhereData {
var valueValue = proc_value(value);
if (this is SqlColumnName) {
return WhereData(
"${this.fullName} ${op} :${this.paramVarKeyName}",
JsonMap(this.paramVarKeyName to valueValue)
)
}
var opKey = CodeUtil.getCode();
var sqlData = this.toSingleSqlData();
return WhereData(
"${sqlData.expression} ${op} :${opKey}",
JsonMap(opKey to valueValue)
)
}
/**
* like 操作
* @param value: 可以包含合法的 %,_
*/
infix fun like(value: String): WhereData = this.sqlMatch("like", value)
/**
* like "%查询内容%"
*/
infix fun likeAll(value: String): WhereData = this.sqlMatch("like", ("%" + value + "%"))
/**
* 相等操作, 也可以比较 Json 数组的相等。
*/
infix fun sqlEquals(value: Serializable): WhereData {
if (this is SqlColumnName) {
if (value is SqlColumnName) {
return WhereData("${this.fullName} = ${value.fullName}")
}
//仅支持 数组相等。
if (this.dbType == DbType.JSON) {
val json_equals = this.jsonEquals(value.ToJson())
return WhereData(
"${json_equals.expression} = 1",
json_equals.values
)
}
}
return this.sqlMatch("=", value);
}
/**
* 相等操作, 也可以比较 Json 数组内容的相等。
*/
infix fun sqlArrayContentEquals(value: Serializable): WhereData {
if (this is SqlColumnName) {
if (value is SqlColumnName) {
return WhereData("${this.fullName} = ${value.fullName}")
}
//仅支持 数组相等。
if (this.dbType == DbType.JSON) {
val v_type = value::class.java
val json_length = this.jsonLength()
if (v_type.isArray) {
var ary = value as Array;
val json_contains = this.jsonContains(db.sql.json_array(ary.toList()))
return WhereData(
"${json_length.expression} = ${ary.size} and ${json_contains.expression} = 1",
json_length.values + json_contains.values
)
} else if (v_type.IsCollectionType) {
var ary = value as Collection;
val json_contains = this.jsonContains(db.sql.json_array(ary))
return WhereData(
"${json_length.expression} = ${ary.size} and ${json_contains.expression} = 1",
json_length.values + json_contains.values
)
} else {
val json_contains = this.jsonContains(db.sql.json_array(listOf(value)))
return WhereData(
"${json_length.expression} = 1 or ${json_contains.expression} = 1",
json_length.values + json_contains.values
)
}
}
}
return this.sqlMatch("=", value);
}
infix fun sqlRegexp(value: String): WhereData {
return this.sqlMatch("regexp", value);
}
/*
* 不等操作
*/
infix fun sqlNotEquals(value: Serializable): WhereData {
if (this is SqlColumnName) {
if (value is SqlColumnName) {
return WhereData("${this.fullName} != ${value.fullName}")
}
//仅支持 数组相等。
if (this.dbType == DbType.JSON) {
val json_equals = this.jsonEquals(value.ToJson())
return WhereData(
"${json_equals.expression} = 0",
json_equals.values
)
}
}
return this.sqlMatch("!=", value);
}
infix fun sqlJsonArrayContains(value: Serializable): WhereData {
if (value is SqlColumnName) {
return WhereData("${this.jsonContains(value)} = 1")
}
if (this is SqlColumnName) {
//仅支持 数组相等。
if (this.dbType == DbType.JSON) {
val v_type = value::class.java
if (v_type.isArray) {
val ary = value as Array;
val json_contains = this.jsonContains(db.sql.json_array(ary.toList()))
return WhereData("${json_contains.expression} = 1", json_contains.values)
} else if (v_type.IsCollectionType) {
val ary = value as Collection;
val json_contains = this.jsonContains(db.sql.json_array(ary))
return WhereData("${json_contains.expression} = 1", json_contains.values)
} else {
val json_contains = this.jsonContains(db.sql.json_array(listOf(value)))
return WhereData("${json_contains.expression} = 1", json_contains.values)
}
}
}
throw java.lang.RuntimeException("json_contains要求列必须是JSON类型！")
}
infix fun sqlJsonArrayNotContains(value: Serializable): WhereData {
if (value is SqlColumnName) {
return WhereData("${this.jsonContains(value)} = 0")
}
if (this is SqlColumnName) {
//仅支持 数组相等。
if (this.dbType == DbType.JSON) {
val v_type = value::class.java
if (v_type.isArray) {
val ary = value as Array;
val json_contains = this.jsonContains(db.sql.json_array(ary.toList()))
return WhereData("${json_contains.expression} = 0")
} else if (v_type.IsCollectionType) {
val ary = value as Collection;
val json_contains = this.jsonContains(db.sql.json_array(ary))
return WhereData("${json_contains.expression} = 0")
} else {
val json_contains = this.jsonContains(db.sql.json_array(listOf(value)))
return WhereData("${json_contains.expression} = 0")
}
}
}
throw java.lang.RuntimeException("json_contains要求列必须是JSON类型！")
}
infix fun sqlJsonObjectContains(value: Serializable): WhereData {
if (value is SqlColumnName) {
return WhereData("${this.jsonContains(value)} = 1")
}
if (this is SqlColumnName) {
//仅支持 数组相等。
if (this.dbType == DbType.JSON) {
val json_contains = this.jsonContains(value.ToJson())
return WhereData("${json_contains.expression} = 1", json_contains.values)
}
}
throw java.lang.RuntimeException("json_contains要求列必须是JSON类型！")
}
infix fun sqlJsonObjectNotContains(value: Serializable): WhereData {
if (value is SqlColumnName) {
return WhereData("${this.jsonContains(value)} = 0")
}
if (this is SqlColumnName) {
//仅支持 数组相等。
if (this.dbType == DbType.JSON) {
val json_contains = this.jsonContains(value.ToJson())
return WhereData("${json_contains.expression} = 0")
}
}
throw java.lang.RuntimeException("json_contains要求列必须是JSON类型！")
}
/**
* 大于等于操作
*/
infix fun sqlGreaterThanEquals(value: Serializable): WhereData {
if (this is SqlColumnName) {
if (value is SqlColumnName) {
return WhereData("${this.fullName} >= ${value.fullName}")
}
}
return this.sqlMatch(">=", value)
}
/**
* 大于操作，不包含等于
*/
infix fun sqlGreaterThan(value: Serializable): WhereData {
if (this is SqlColumnName) {
if (value is SqlColumnName) {
return WhereData("${this.fullName} > ${value.fullName}")
}
}
return this.sqlMatch(">", value);
}
/**
* 小于等于操作。
*/
infix fun sqlLessThanEquals(value: Serializable): WhereData {
if (this is SqlColumnName) {
if (value is SqlColumnName) {
return WhereData("${this.fullName} <= ${value.fullName}")
}
}
return this.sqlMatch("<=", value)
}
/**
* 小于操作，不包含等于
*/
infix fun sqlLessThan(value: Serializable): WhereData {
if (this is SqlColumnName) {
if (value is SqlColumnName) {
return WhereData("${this.fullName} < ${value.fullName}")
}
}
return this.sqlMatch("<", value)
}
private fun sqlUntil(min: Any, max: Any): WhereData {
var minValue = proc_value(min);
var maxValue = proc_value(max);
if (this is SqlColumnName) {
return WhereData(
"${this.fullName} >= :${this.paramVarKeyName}_min and ${this.fullName} < :${this.paramVarKeyName}_max",
JsonMap(
"${this.paramVarKeyName}_min" to minValue,
"${this.paramVarKeyName}_max" to maxValue
)
)
}
var sqlData = this.toSingleSqlData()
var sqlParameterKey = CodeUtil.getCode();
sqlData.values += JsonMap(
"${sqlParameterKey}_min" to minValue,
"${sqlParameterKey}_max" to maxValue
)
return WhereData(
"${sqlData.expression} >= :${sqlParameterKey}_min and ${sqlData.expression} < :${sqlParameterKey}_max",
sqlData.values
)
}
/**
* 开闭区间，表示大于等于 并且 小于
*/
fun sqlUntil(min: T, max: T): WhereData {
if (this is SqlColumnName) {
if (min is SqlColumnName && max is SqlColumnName) {
return WhereData("${this.fullName} >= ${min.fullName} and ${this.fullName} < ${max.fullName}")
}
}
return this.sqlUntil(min, max);
}
/**
* in (values)操作
*/
infix inline fun sqlIn(values: Array): WhereData {
return sqlOpInOrNotIn("in", T::class.java, values)
}
fun sqlOpInOrNotIn(inOrNotIn: String, type: Class, values: Array): WhereData {
if (this is SqlColumnName) {
if (type == SqlColumnName::class.java) {
return WhereData(
"${this.fullName} ${inOrNotIn} (${
values.map { (it as SqlColumnName).fullName }.joinToString(",")
.AsString("null")
} )"
)
}
var needWrap = this.dbType.needTextWrap()
var value = values
.map {
var v = proc_value(it)
if (needWrap) {
return@map "'" + v + "'"
}
return@map v;
}
.joinToString(",")
.AsString("null")
return WhereData("${this.fullName} ${inOrNotIn} ( ${value} )");
}
var sqlData = this.toSingleSqlData();
if (type == SqlColumnName::class.java) {
return WhereData(
"${sqlData.expression} ${inOrNotIn} (${
values.map { (it as SqlColumnName).fullName }
.joinToString(",")
.AsString("null")
} )",
sqlData.values
)
}
var value = values
.map {
var v = proc_value(it)
var v_type = v.javaClass;
if (v_type.IsNumberType) {
return@map v
}
return@map "'" + v + "'"
}
.joinToString(",")
.AsString("null")
return WhereData("${sqlData.expression} in ( ${value} )", sqlData.values);
}
/**
* in 子查询
*/
infix fun sqlIn(select: SqlQueryClip<*, *>): WhereData {
var sqlData = this.toSingleSqlData();
var subSelect = select.toSql()
var ret = WhereData("${sqlData.expression} in ( ${subSelect.expression} )", sqlData.values)
ret.values += subSelect.values
return ret;
}
/**
* not in (values) 操作
*/
infix inline fun sqlNotIn(values: Array): WhereData {
return sqlOpInOrNotIn("not in", T::class.java, values)
}
/**
* not in 子查询
*/
infix fun sqlNotIn(select: SqlQueryClip<*, *>): WhereData {
var sqlData = this.toSingleSqlData();
var subSelect = select.toSql()
var ret =
WhereData("${sqlData.expression} not in ( ${subSelect.expression} )", sqlData.values)
ret.values += subSelect.values
return ret;
}
/**
* 生成 (col is null or col = 0/'' )
*/
fun isNullOrEmpty(): WhereData {
if (this is SqlColumnName) {
var emptyValue = "";
if (this.dbType.isNumberic()) {
emptyValue = " or ${this.fullName} = 0"
} else if (this.dbType != DbType.OTHER) {
emptyValue = " or ${this.fullName} = ''";
}
return WhereData("(${this.fullName} is null ${emptyValue})")
}
/**
* 当在代码段使用该函数时，可能会因为数据类型（数字0 和空字符串比较）导致全表扫描！
* 这里，仅与 空字符串相比。
* 数据库设计上，尽量避免 空值，建议使用 0，空串做默认值！
*/
var sqlData = this.toSingleSqlData();
return WhereData(
"(${sqlData.expression} is null or ${sqlData.expression} = '')",
sqlData.values
)
}
/**
* 返回第一个非Null的值，使用 coalesce 替代 ifNull
* @param elseValues 可以是 BaseAliasSqlSect ，也可以是 简单的值类型
*/
fun ifNull(vararg elseValues: Serializable): SqlParameterData {
var ret = this.toSingleSqlData();
ret.expression = "coalesce(${ret.expression}"
elseValues.forEach {
if (it is BaseAliasSqlSect) {
ret.expression += ","
ret += it.toSingleSqlData()
return@forEach
}
var type = it.javaClass
if (type.IsNumberType) {
ret.expression += ",${it}"
} else {
ret.expression += ",'${it}'"
}
}
ret.expression += ")"
return ret;
}
private fun op_ext_sql(baseAliasSqlData: BaseAliasSqlSect, op: String): SqlParameterData {
if (baseAliasSqlData is SqlParameterData) {
var clone = baseAliasSqlData.CloneObject()
clone.expression = "${op}(${clone.expression})"
return clone;
} else if (baseAliasSqlData is SqlColumnName) {
var clone = baseAliasSqlData.toSingleSqlData()
clone.expression = "${op}(${clone.expression})"
return clone;
}
throw RuntimeException("不识别的类型:${baseAliasSqlData::class.java.name}")
}
fun sum(): SqlParameterData {
return op_ext_sql(this, "sum");
}
fun count(): SqlParameterData {
return op_ext_sql(this, "count");
}
fun min(): SqlParameterData {
return op_ext_sql(this, "min");
}
fun max(): SqlParameterData {
return op_ext_sql(this, "max");
}
fun avg(): SqlParameterData {
return op_ext_sql(this, "avg");
}
/**
* 字符个数
*/
fun character_length(): SqlParameterData {
return op_ext_sql(this, "character_length");
}
/**
* 判断该列的内容是否在另一个列内容集合中。 find_in_set('a','a,b,c') = 1
*/
fun findInSet(arrayColumn: BaseAliasSqlSect): SqlParameterData {
var sqlData = this.toSingleSqlData();
var arrayData = arrayColumn.toSingleSqlData()
var ret =
SqlParameterData("find_in_set ( ${sqlData.expression} , ${arrayData.expression} ) ", sqlData.values)
ret.values += arrayData.values
return ret;
}
fun whereInSet(arrayColumn: BaseAliasSqlSect): WhereData {
var sqlData = this.toSingleSqlData();
var arrayData = arrayColumn.toSingleSqlData()
var ret =
WhereData("find_in_set ( ${sqlData.expression} , ${arrayData.expression} ) > 0", sqlData.values)
ret.values += arrayData.values
return ret;
}
/**
* @param separator 会移除非法的 单引号
*/
fun group_concat(distinct: Boolean = true, order: SqlOrderBy? = null, separator: String = ","): SqlParameterData {
var sqlData = this.toSingleSqlData();
var orderData = order?.toSingleSqlData()
var ret =
SqlParameterData(
"GROUP_CONCAT(${if (distinct) "distinct " else ""} name ${
if (orderData != null && orderData.expression.HasValue) {
" ORDER BY ${orderData.expression}"
} else ""
} SEPARATOR '${separator.replace("'", "")}') ", sqlData.values
)
if (orderData != null) {
ret.values += orderData.values;
}
return ret;
}
/**
* 连接多个列,该列是第一个连接列。
*/
fun concat_ws(separator: String = ",", vararg columns: BaseAliasSqlSect): SqlParameterData {
var sqlData = this.toSingleSqlData();
var others = columns.map { it.toSingleSqlData() };
var ret =
SqlParameterData(
"concat_ws('${separator.replace("'", "")}', ${
others.map { it.expression }.joinToString(",")
}) ", sqlData.values
)
others.forEach {
ret.values += it.values;
}
return ret;
}
/**
* 返回几个列中值的最大值，不是聚合函数max!
*/
fun greatest(vararg columns: BaseAliasSqlSect): SqlParameterData {
var sqlData = this.toSingleSqlData();
var others = columns.map { it.toSingleSqlData() };
var ret =
SqlParameterData(
"greatest(${
others.map { it.expression }.joinToString(",")
}) ", sqlData.values
)
others.forEach {
ret.values += it.values;
}
return ret;
}
fun least(vararg columns: BaseAliasSqlSect): SqlParameterData {
var sqlData = this.toSingleSqlData();
var others = columns.map { it.toSingleSqlData() };
var ret =
SqlParameterData(
"least(${
others.map { it.expression }.joinToString(",")
}) ", sqlData.values
)
others.forEach {
ret.values += it.values;
}
return ret;
}
/**
*
*/
fun subString(startIndex: Int, length: Int): SqlParameterData {
var sqlData = this.toSingleSqlData();
var ret = SqlParameterData(
"subString (${sqlData.expression} from ${startIndex} for ${length})", sqlData.values
)
return ret;
}
fun replaceString(oldString: String, column: BaseAliasSqlSect): SqlParameterData {
var sqlData = this.toSingleSqlData();
var columnData = column.toSingleSqlData();
var ret = SqlParameterData(
"replace (${sqlData.expression} , '${oldString.replace("'", "''")}', ${columnData.expression})",
sqlData.values
)
ret.values += columnData.values;
return ret;
}
}