com.jladder.db.Sqls Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of jladder Show documentation
Show all versions of jladder Show documentation
with java,a low code SDK,通用低代码开发包
package com.jladder.db;
import com.jladder.data.Record;
import com.jladder.db.bean.FieldInfo;
import com.jladder.db.enums.DbSqlDataType;
import com.jladder.db.jdbc.GroupBy;
import com.jladder.db.jdbc.OrderBy;
import com.jladder.db.jdbc.impl.Dao;
import com.jladder.lang.*;
import com.jladder.lang.func.Tuple2;
import java.util.ArrayList;
import java.util.List;
///
/// 便捷sql处理类，
/// 主要是DML操作和部分DDL
///
public class Sqls
{
///
/// 数据访问对象
///
public IDao Dao= null;
///
/// sql动作
///
public DbSqlDataType Action = DbSqlDataType.Query;
///
/// 表名
///
public String TableName;
///
/// 条件对象
///
public Cnd Condition;
///
/// 排序对象
///
public OrderBy Order;
///
/// 分组对象
///
public GroupBy Group;
///
/// 片选字段
///
public List Columns;
///
/// 基本构造
///
public Sqls()
{
Group=new GroupBy();
Order=new OrderBy();
Columns=new ArrayList();
Condition=new Cnd();
}
///
/// 从文本解码
///
///
///
/// 以YFdao构造
///
/// Dao对象
public Sqls(IDao dao)
{
this.Dao = dao;
Group = new GroupBy();
Order = new OrderBy();
Columns = new ArrayList();
Condition = new Cnd();
}
///
/// 以文本方式构造
///
/// sql文本
public Sqls(String sqltext)
{
sqltext = sqltext.trim();
sqltext = Regex.replace(sqltext,"\\s*\\n\\s*"," ");
//json对象
if (sqltext.startsWith("{")&&sqltext.endsWith("}"))
{
//分别解析 字段 分组 条件 排序等部分
Record sRecord = Json.toObject(sqltext,Record.class);
this.TableName = sRecord.getString("tableName,table", true);
this.AddColumn(sRecord.getString("columns,column", true));
String tempText = sRecord.getString("condition,where", true);
if(!Strings.isBlank(tempText))this.Condition=new Cnd(tempText);
tempText = sRecord.getString("orderby,order", true);
if (!Strings.isBlank(tempText)) this.Order = new OrderBy(tempText);
tempText = sRecord.getString("groupby,group", true);
if (!Strings.isBlank(tempText)) this.Group = new GroupBy(tempText);
}
else
//纯文本sql语句，注意：此代码不能100%应对需要，别太复杂，2015-12-29
{
throw Core.makeThrow("未实现");
// //各种匹配正则
// int length = sqltext.length();
// //select
// var selectMatch = Regex.Match(sqltext, "\\^s*select\\s+", RegexOptions.IgnoreCase);
// //from
// var fromMatch = Regex.Match(sqltext, "\\s*from\\s*", RegexOptions.IgnoreCase);
// //where,以第一个where
// var whereMatch = Regex.Match(sqltext, "\\s*where\\s*", RegexOptions.IgnoreCase);
// //order 以最后一个order
// var orderMatch = Regex.Match(sqltext, "\\s*order\\sby\\s*([\\s\\S]*)$", RegexOptions.IgnoreCase);
// //group 以最后一个分组
// var groupMatch = Regex.Match(sqltext, "\\s*group\\sby\\s*([\\s\\S]*?)(order|$)", RegexOptions.IgnoreCase);
// if (selectMatch.Success && fromMatch.Success)
// {
// this.AddColumn(Strings.SubString(sqltext, selectMatch.Index + selectMatch.Length, fromMatch.Index));
// }
// if (whereMatch.Success)
// {
// this.TableName=Strings.SubString(sqltext, fromMatch.Index + fromMatch.Length, whereMatch.Index);
// int endindex = -1;
// if (orderMatch.Success) endindex = orderMatch.Index;
// if (groupMatch.Success) endindex = groupMatch.Index;
// if (endindex < 1) endindex = length;
// var where = Strings.SubString(sqltext, whereMatch.Index + whereMatch.Length, endindex);
// this.Condition=new Cnd(where);
//
// }
// //如果分组匹配成功
// if (groupMatch.Success)
// {
// if (String.IsNullOrEmpty(this.TableName)&&fromMatch.Success)
// this.TableName = Strings.SubString(sqltext, fromMatch.Index + fromMatch.Length, groupMatch.Index);
// this.Group=new GroupBy(groupMatch.Groups[1].Value);
// }
// //如果排序匹配成功
// if (orderMatch.Success)
// {
// if (String.IsNullOrEmpty(this.TableName) && fromMatch.Success)
// this.TableName = Strings.SubString(sqltext, fromMatch.Index + fromMatch.Length, orderMatch.Index);
//// var order = Strings.SubString(sqltext, orderMatch.Index + orderMatch.Length, length);
// this.Order=new OrderBy(orderMatch.Groups[1].Value);
// }
// if (String.IsNullOrEmpty(this.TableName)&&fromMatch.Success)
// {
// this.TableName = Strings.SubString(sqltext,fromMatch.Index+fromMatch.Length, length);
// }
// if (String.IsNullOrEmpty(this.TableName) && !fromMatch.Success)
// {
// this.TableName = Strings.SubString(sqltext,0, length);
// }
}
}
///
/// 获取条件文本
///
///
/// 获取分组文本
///
///
/// 获取排序文本
///
///
/// 添加字段
///
/// 列文本
public Sqls AddColumn(String columnStr)
{
//空字符或者*则返回
if (Strings.isBlank(columnStr)) return this;
if (Regex.isMatch(columnStr, "^\\s*\\*\\s*"))
{
Columns.clear();
return this;
}
String[] cols = columnStr.split(",");
if(Columns==null)Columns=new ArrayList();
for (String col : cols)
{
String[] fieldArray = Regex.split(col, "(@@)|(\\s+as\\s+)");
String fieldname = fieldArray[0];
String asname = fieldArray[fieldArray.length-1];
Tuple2 firstOrDefault = Collections.first(Columns, x -> Core.is( x.fieldname,fieldname) && Core.is( x.as , asname));
if (!firstOrDefault.item1){
Columns.add(new FieldInfo(fieldname,asname));
}
else{
firstOrDefault.item2.as = asname;
firstOrDefault.item2.fieldname = fieldname;
}
}
return this;
}
/**
* 添加字段
* @param field 字段信息对象
* @return
*/
public Sqls AddColumn(FieldInfo field) {
if (field == null || Strings.isBlank(field.fieldname)) return this;
if(Columns==null)Columns=new ArrayList();
Tuple2 oldfield = Collections.first(Columns, a -> field.fieldname.equals(a.fieldname) && a.as.equals(field.as));
if (!oldfield.item1){
Columns.add(field);
}
else {
throw Core.makeThrow("未实现[0233]");
//Core.CopyData(oldfield, field);
}
return this;
}
/**
* 获取sql组成语句
* @return
*/
public String SqlText(){
String sqltext = "";
switch (Action){
case Query:
sqltext = "select " + getColumns() +" from " +TableName+Condition.getWhere(true, false) + Group.toString()+Order.toString();
break;
}
return sqltext;
}
/**
* 设置数据动作
* @param action 数据动作
* @return
*/
public Sqls SetAction(DbSqlDataType action)
{
Action = action;
return this;
}
public List query(){
IDao dao = (Dao==null?new Dao():Dao);
SqlText sql = new SqlText(SetAction(DbSqlDataType.Query).SqlText());
return dao.query(sql);
}
public List query(IDao dao){
if(dao == null)dao = Dao;
if(dao == null)dao = new Dao();
return dao.query(new SqlText(SetAction(DbSqlDataType.Query).SqlText()));
}
public String getValue(IDao dao){
if(dao == null)dao = Dao;
if(dao == null)dao = new Dao();
return dao.getValue(new SqlText(SetAction(DbSqlDataType.Query).SqlText()),String.class);
}
/**
* 获取列文本
* @return
*/
public String getColumns(){
String columns="";
if(Rs.isBlank(Columns)){
columns="*";
}else{
for (FieldInfo c : Columns) {
columns+=c.fieldname+ (Strings.isBlank(c.as)||c.fieldname.equals(c.as) ? "" : " as "+c.as)+",";
}
columns=Strings.rightLess(columns,1);
}
return columns;
}
}