package com.smtaiserver.smtaiserver.attach;
|
|
import java.util.ArrayList;
|
import java.util.Iterator;
|
import java.util.List;
|
import org.dom4j.Element;
|
import org.dom4j.Node;
|
import org.dom4j.tree.DefaultText;
|
|
import com.smtaiserver.smtaiserver.attach.SMTAIAttachTableDef.SMTAIAttachTableColumn;
|
import com.smtaiserver.smtaiserver.database.SMTDatabase;
|
import com.smtaiserver.smtaiserver.database.SMTDatabase.DBRecord;
|
import com.smtaiserver.smtaiserver.database.SMTDatabase.DBRecords;
|
import com.smtaiserver.smtaiserver.javaai.ast.ASTDBMap;
|
import com.smtservlet.util.Json;
|
import com.smtservlet.util.SMTJsonWriter;
|
import com.smtservlet.util.SMTStatic;
|
|
|
|
public class SMTAttachTableSqlXml
|
{
|
///////////////////////////////////////////////////////////////////////////////////////
|
private static class SQLXMLExecArg
|
{
|
public StringBuilder _sbSQLText = new StringBuilder();
|
public List<Object> _sqlParams = new ArrayList<>();
|
|
}
|
|
///////////////////////////////////////////////////////////////////////////////////////
|
private static abstract class SQLXMLNode
|
{
|
public abstract void execute(SQLXMLExecArg execArg) throws Exception;
|
}
|
|
|
///////////////////////////////////////////////////////////////////////////////////////
|
private static class SQLXMLNodeSQL extends SQLXMLNode
|
{
|
private List<Object> _listChildren = new ArrayList<>();
|
|
public SQLXMLNodeSQL(Element xmlRoot) throws Exception
|
{
|
for (Iterator<Node> iterInner = xmlRoot.nodeIterator(); iterInner.hasNext();)
|
{
|
Node nodeInner = iterInner.next();
|
if(nodeInner.getNodeType() == Node.TEXT_NODE)
|
{
|
String text = ((DefaultText)nodeInner).getText();
|
int lastPos = _listChildren.size() - 1;
|
if(lastPos >= 0 && _listChildren.get(lastPos) instanceof String)
|
{
|
_listChildren.set(lastPos, (String)_listChildren.get(lastPos) + text);
|
}
|
else
|
{
|
_listChildren.add(text);
|
}
|
}
|
else
|
{
|
_listChildren.add(createSQLXMLNode((Element)nodeInner));
|
}
|
}
|
}
|
|
@Override
|
public void execute(SQLXMLExecArg execArg) throws Exception
|
{
|
for(Object oxmlNode : _listChildren)
|
{
|
if(oxmlNode instanceof String)
|
{
|
execArg._sbSQLText.append(oxmlNode);
|
}
|
else if(oxmlNode instanceof SQLXMLNode)
|
{
|
((SQLXMLNode)oxmlNode).execute(execArg);
|
}
|
}
|
}
|
}
|
|
|
///////////////////////////////////////////////////////////////////////////////////////
|
private String _dsId;
|
private SQLXMLNode _SQLXMLNode;
|
|
private static SQLXMLNode createSQLXMLNode(Element xmlRoot) throws Exception
|
{
|
String name = xmlRoot.getName().toUpperCase();
|
if("SQL".equals(name))
|
return new SQLXMLNodeSQL(xmlRoot);
|
else
|
throw new Exception("unknow SQLXML : " + name);
|
}
|
|
public SMTAttachTableSqlXml(Element rootElement) throws Exception
|
{
|
_dsId = SMTStatic.getXmlAttr(rootElement, "ds_id", null);
|
|
_SQLXMLNode = createSQLXMLNode(rootElement);
|
}
|
|
public void querySQLToJson(ASTDBMap dbMap, Json jsonFilters, Json jsonOrders, SMTAIAttachTableDef attachTableDef, SMTJsonWriter jsonWr) throws Exception
|
{
|
// 生成原始SQL
|
SQLXMLExecArg execArg = new SQLXMLExecArg();
|
_SQLXMLNode.execute(execArg);
|
|
// 生成条件查询
|
StringBuilder sbWHERE = new StringBuilder();
|
if(jsonFilters != null)
|
{
|
for(Json jsonFilter : jsonFilters.asJsonList())
|
{
|
if(sbWHERE.length() > 0)
|
sbWHERE.append(" AND ");
|
|
String column = jsonFilter.getJson("col").asString();
|
String filter = jsonFilter.getJson("filter").asString();
|
List<Json> values = jsonFilter.getJson("values").asJsonList();
|
|
if("like".equals(filter))
|
{
|
sbWHERE.append(column + " LIKE '%" + values.get(0).asString().replace("'", "''") + "%'");
|
}
|
else if("time_range".equals(filter))
|
{
|
sbWHERE.append(
|
column + " BETWEEN '" + values.get(0).asString().replace("'", "''") + "'::timestamp AND '"
|
+ values.get(1).asString().replace("'", "''") + "'::timestamp");
|
}
|
else
|
throw new Exception("can't find filter op : " + filter);
|
}
|
}
|
if(sbWHERE.length() > 0)
|
sbWHERE.insert(0, " WHERE ");
|
|
// 生成排序条件
|
StringBuilder sbORDER = new StringBuilder();
|
if(jsonOrders != null)
|
{
|
for(Json jsonOrder : jsonOrders.asJsonList())
|
{
|
if(sbORDER.length() > 0)
|
sbORDER.append(",");
|
String colName = jsonOrder.getJson("col").asString();
|
String order = jsonOrder.safeGetStr("order", "ASC");
|
|
sbORDER.append(colName + " " + order);
|
}
|
}
|
if(sbORDER.length() > 0)
|
sbORDER.insert(0, " ORDER BY ");
|
|
|
SMTDatabase db = dbMap.getDatabase(_dsId);
|
DBRecords recs = db.querySQL(
|
"SELECT * FROM (" + execArg._sbSQLText.toString() + ") T " + sbWHERE.toString() + " " + sbORDER.toString() + " LIMIT 100",
|
execArg._sqlParams.size() == 0 ? null : execArg._sqlParams.toArray(new Object[] {execArg._sqlParams.size()})
|
);
|
|
jsonWr.beginArray("columns");
|
for(SMTAIAttachTableColumn column : attachTableDef.getColumnList())
|
{
|
jsonWr.addKeyValue(null, column.getTitle());
|
}
|
jsonWr.endArray();
|
|
jsonWr.beginArray("values");
|
for(DBRecord rec : recs.getRecords())
|
{
|
jsonWr.beginArray(null);
|
for(SMTAIAttachTableColumn column : attachTableDef.getColumnList())
|
{
|
jsonWr.addKeyValue(null, rec.getString(column.getName()));
|
}
|
jsonWr.endArray();
|
}
|
jsonWr.endArray();
|
}
|
}
|
