|
JavaXT
|
|
ServiceRequest Class
package javaxt.express;
import java.io.StringReader;
import java.util.*;
import javaxt.http.servlet.HttpServletRequest;
import javaxt.http.servlet.ServletException;
import javaxt.json.*;
import static javaxt.utils.Console.console;
import javaxt.express.utils.StringUtils;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.Function;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.statement.select.*;
//******************************************************************************
//** ServiceRequest
//******************************************************************************
/**
* Used to encapsulate an HttpServletRequest and simplify the
* parsing/extracting of parameters from the raw HTTP request.
*
******************************************************************************/
public class ServiceRequest {
private HttpServletRequest request;
private String service;
private String method = "";
private String[] path;
private java.security.Principal user;
private javaxt.utils.URL url;
private byte[] payload;
private JSONObject json;
private HashMap<String, List<String>> parameters; //<- Don't use this directly! Use the static getParameter() and setParameter() methods
private Field[] fields;
private Filter filter;
private Sort sort;
private Long limit;
private Long offset;
private Long id;
private boolean readOnly = false;
private boolean parseJson = false;
private static String[] approvedFunctions = new String[]{
"min", "max", "count", "avg", "sum"
};
//**************************************************************************
//** Constructor
//**************************************************************************
public ServiceRequest(HttpServletRequest request){
this(null, request);
}
//**************************************************************************
//** Constructor
//**************************************************************************
public ServiceRequest(String service, HttpServletRequest request){
this.service = service;
this.request = request;
this.url = new javaxt.utils.URL(request.getURL());
this.parameters = url.getParameters();
//Parse path, excluding servlet and service path
setPath(request.getPathInfo());
//Get offset and limit
updateOffsetLimit();
}
//**************************************************************************
//** getService
//**************************************************************************
/** Returns the service name from the http request. Service requests follow
* the convention: "http://localhost/servlet/service/path". For example
* "http://localhost/myapp/admin/user". In this example, the servlet path
* is "myapp" and the service name is "admin". Note that the servlet path
* is optional and may include multiple "directories". This method makes it
* easier to find the service name from the url.
*/
public String getService(){
return service;
}
//**************************************************************************
//** getMethod
//**************************************************************************
/** Returns a method name for the HTTP request. Examples:
* <ul>
* <li>GET "http://localhost/user" returns "getUser"</li>
* <li>DELETE "http://localhost/user" returns "deleteUser"</li>
* <li>POST or PUT "http://localhost/user" returns "saveUser"</li>
* </ul>
*
* If the request is read-only, "POST", "PUT", and "DELETE" requests will
* be re-mapped to "GET".
*
* <p>
* If the URL contains a "servlet" path or a "service" path, will return
* the first object in the path after the servlet and/or service. Consider
* this example: "http://localhost/myapp/admin/user" In this example, the
* servlet path is "myapp" and the service path is "admin" and and so the
* method name is derived from "user".
* </p>
*
* Note that this method is used by the WebService class to map service
* requests to REST service endpoints and execute CRUD operations.
*/
public String getMethod(){
return method;
}
//**************************************************************************
//** setReadOnly
//**************************************************************************
/** Used to disable insert, update, and delete operations. "POST", "PUT",
* and "DELETE" requests are remapped to "GET". See getMethod() for more
* information on how requests are mapped.
*/
public void setReadOnly(boolean readOnly){
if (readOnly==this.readOnly) return;
this.readOnly = readOnly;
setPath(request.getPathInfo());
}
//**************************************************************************
//** isReadOnly
//**************************************************************************
/** Returns true if insert, update, and delete operations have been disabled.
* See setReadOnly() for more information. Default is false.
*/
public boolean isReadOnly(){
return readOnly;
}
//**************************************************************************
//** getPath
//**************************************************************************
/** Returns a part of the url path at a given index AFTER the service
* name. For example, index 0 for "http://localhost/servlet/service/a/b/c"
* would yield "a".
*/
public javaxt.utils.Value getPath(int i){
if (path==null || i>=path.length) return new javaxt.utils.Value(null);
else return new javaxt.utils.Value(path[i]);
}
//**************************************************************************
//** getPath
//**************************************************************************
/** Returns a part of the url path AFTER the service name. For example,
* "http://localhost/servlet/service/a/b/c" would yield "/a/b/c".
*/
public String getPath(){
if (path==null) return null;
StringBuilder str = new StringBuilder();
for (String s : path){
str.append("/");
str.append(s);
}
return str.toString();
}
//**************************************************************************
//** setPath
//**************************************************************************
/** Used to update the path of the current URL. This method can be used to
* coerce a request to route to different web methods (see getMethod).
* @param path URL path, excluding servlet and service path. For example,
* if a URL follows the follows a pattern like
* "http://localhost/servlet/service/a/b/c" the path is "/a/b/c".
*/
public void setPath(String path){
if (path!=null){
path = path.substring(1);
boolean addPath = service==null;
ArrayList<String> arr = new ArrayList<>();
for (String str : path.split("/")){
if (addPath) arr.add(str);
if (str.equalsIgnoreCase(service)){
addPath = true;
}
}
this.path = arr.toArray(new String[arr.size()]);
}
//Generate a method name using the request method and first "directory"
//in the path. Example: "GET /config/users" would yield the "getUsers"
//from the "config" service.
String name = getPath(0).toString();
if (name!=null){
name = name.substring(0, 1).toUpperCase() + name.substring(1);
if (readOnly){
this.method = "get" + name;
}
else{
String method = request.getMethod();
if (method.equals("GET")){
this.method = "get" + name;
}
else if (method.equals("PUT") || method.equals("POST")){
this.method = "save" + name;
}
else if (method.equals("DELETE")){
this.method = "delete" + name;
}
}
}
//Get ID
id = getPath(1).toLong();
if (id==null) id = getParameter("id").toLong();
}
//**************************************************************************
//** getID
//**************************************************************************
/** Returns the ID associated with the request. Assuming the service request
* follows the convention "http://localhost/servlet/service/object", the ID
* for the "http://localhost/photos/config/user/54" is 54. If an ID is not
* found in the path or is invalid, then the id parameter in the query
* string is returned. Example: "http://localhost/photos/config/user?id=54"
*/
public Long getID(){
return id;
}
//**************************************************************************
//** getURL
//**************************************************************************
/** Returns the original url used to make the request.
*/
public javaxt.utils.URL getURL(){
return url;
}
//**************************************************************************
//** getParameter
//**************************************************************************
/** Returns the value associated with a parameter in the request. Performs a
* case insensitive search for the keyword in the query string. In addition,
* will search the JSON payload of the request if parseJson is set to true.
* If the value is an empty string or "null" then a null value is returned.
* @param key Query string parameter name. Performs a case insensitive
* search for the keyword.
*/
public javaxt.utils.Value getParameter(String key){
String val = null;
if (key!=null){
List<String> parameters = getParameter(key, this.parameters);
if (parameters!=null){
val = parameters.get(0).trim();
if (val.length()>0){
if (val.equalsIgnoreCase("null")) val = null;
}
else{
val = null;
}
}
if (val==null && parseJson){ //&& !getRequest().getMethod().equals("GET")
JSONObject json = getJson();
if (json!=null && json.has(key)){
return new javaxt.utils.Value(json.get(key).toObject());
}
}
}
return new javaxt.utils.Value(val);
}
//**************************************************************************
//** hasParameter
//**************************************************************************
/** Returns true if the request contains a given parameter in the request.
* Performs a case insensitive search for the keyword in the query string.
* In addition, will search the JSON payload of the request if parseJson is
* set to true.
*/
public boolean hasParameter(String key){
if (key!=null){
List<String> parameters = getParameter(key, this.parameters);
if (parameters!=null) return true;
if (parseJson){ //&& !getRequest().getMethod().equals("GET")
JSONObject json = getJson();
if (json!=null && json.has(key)){
return true;
}
}
}
return false;
}
//**************************************************************************
//** setParameter
//**************************************************************************
/** Used to update a parameter extracted from the original request. Performs
* a case insensitive search for the keyword in the query string.
*/
public void setParameter(String key, String val){
if (key!=null){
if (val==null){
removeParameter(key, this.parameters);
return;
}
//Get parameters
List<String> parameters = getParameter(key, this.parameters);
//Special case for classes that override the hasParameter and
//getParameter methods.
if (parameters==null && hasParameter(key)){
parameters = new ArrayList<>();
parameters.add(getParameter(key).toString());
setParameter(key, parameters, this.parameters);
}
//Add or update value
if (parameters==null){
if (val!=null){
parameters = new ArrayList<>();
parameters.add(val);
setParameter(key, parameters, this.parameters);
}
}
else{
if (val!=null) parameters.set(0, val);
}
//Update offset and limit as needed
String k = key.toLowerCase();
if (k.equals("offset") || k.equals("limit") || k.equals("page")){
updateOffsetLimit();
}
}
}
//**************************************************************************
//** getParameterNames
//**************************************************************************
/** Returns a list of all the parameter keywords found in this request.
*/
public String[] getParameterNames(){
LinkedHashSet<String> keys = new LinkedHashSet<>();
Iterator<String> it = this.parameters.keySet().iterator();
while (it.hasNext()) keys.add(it.next());
if (parseJson){
JSONObject json = getJson();
if (json!=null){
for (String key : json.keySet()){
keys.add(key);
}
}
}
return keys.toArray(new String[keys.size()]);
}
private static List<String> getParameter(String key, HashMap<String, List<String>> parameters){
return javaxt.utils.URL.getParameter(key, parameters);
};
private static void setParameter(String key, List<String> values, HashMap<String, List<String>> parameters){
javaxt.utils.URL.setParameter(key, values, parameters);
}
private static void removeParameter(String key, HashMap<String, List<String>> parameters){
javaxt.utils.URL.removeParameter(key, parameters);
}
//**************************************************************************
//** updateOffsetLimit
//**************************************************************************
private void updateOffsetLimit(){
offset = getParameter("offset").toLong();
limit = getParameter("limit").toLong();
Long page = getParameter("page").toLong();
if (offset==null && page!=null){
if (limit==null) limit = 25L;
offset = (page*limit)-limit;
}
}
//**************************************************************************
//** getOffset
//**************************************************************************
/** Returns the value of the "offset" parameter in the request as a number.
* This parameter is used by the WebService class to paginate through list
* requests.
*/
public Long getOffset(){
return offset;
}
//**************************************************************************
//** getLimit
//**************************************************************************
/** Returns the value of the "limit" parameter in the request as a number.
* This parameter is used by the WebService class to paginate through list
* requests.
*/
public Long getLimit(){
return limit;
}
//**************************************************************************
//** getRequest
//**************************************************************************
/** Returns the original, unmodified HTTP request used to instantiate this
* class.
*/
public HttpServletRequest getRequest(){
return request;
}
//**************************************************************************
//** setPayload
//**************************************************************************
/** Used to update the raw bytes representing the payload of the request
*/
public void setPayload(byte[] payload){
this.payload = payload;
json = null;
}
//**************************************************************************
//** getPayload
//**************************************************************************
/** Returns the raw bytes from the payload of the request
*/
public byte[] getPayload(){
if (payload==null){
try{
payload = request.getBody();
}
catch(Exception e){}
}
return payload;
}
//**************************************************************************
//** getJson
//**************************************************************************
/** Returns the payload of the request as a JSON object
*/
public JSONObject getJson(){
if (json==null){
byte[] b = getPayload();
if (b!=null && b.length>0){
try{
json = new JSONObject(new String(getPayload(), "UTF-8"));
}
catch(Exception e){}
}
}
return json;
}
//**************************************************************************
//** parseJson
//**************************************************************************
/** Calling this method will expand parameter searches into the payload of
* the request. See getParameter() for more info.
*/
public void parseJson(){
parseJson = true;
if (id==null) id = getParameter("id").toLong();
updateOffsetLimit();
}
//**************************************************************************
//** getUser
//**************************************************************************
/** Returns the user associated with the request
*/
public java.security.Principal getUser(){
if (user==null){
user = request.getUserPrincipal();
}
return user;
}
//**************************************************************************
//** getCredentials
//**************************************************************************
/** Returns the credentials associated with an HTTP request. The credentials
* will vary based on the security authentication scheme used to
* authenticate clients (e.g. "BASIC", "DIGEST", "NTLM", etc). In the case
* of "BASIC" authentication, the credentials typically include a username
* and password. In the case of "NTLM" authentication, the credentials may
* only contain a username. What is actually returned is up to the
* javaxt.http.servlet.Authenticator used to authenticate the request.
*/
public String[] getCredentials(){
return request.getCredentials();
}
//**************************************************************************
//** authenticate
//**************************************************************************
/** Used to authenticate a client request. Authentication is performed by a
* javaxt.http.servlet.Authenticator. If no Authenticator is defined or if
* the Authenticator fails to authenticate the client, this method throws a
* ServletException.
*/
public void authenticate() throws ServletException {
request.authenticate();
}
//**************************************************************************
//** isCacheable
//**************************************************************************
/** Returns true if a given eTag matches the "if-none-match" request header.
* Additional checks are performed against other cache related headers
* (e.g. "cache-control" and "if-modified-since"). If true is returned,
* the corresponding HTTP response can be set to a 304 "Not Modified".
* @param eTag A custom string that acts as a unique identifier (including
* version) for an HTTP response. ETags are frequently used when sending
* static data such as files. In the context of dynamic web services, the
* same concept can be applied for static, or semi-static responses (e.g.
* static keywords, images in a database, etc).
* @param date A date associated with the HTTP response. The date should be
* in GMT and in "EEE, dd MMM yyyy HH:mm:ss zzz" format (e.g.
* "Sat, 23 Oct 2010 13:04:28 GMT").
*/
public boolean isCacheable(String eTag, String date){
String matchTag = request.getHeader("if-none-match");
String cacheControl = request.getHeader("cache-control");
if (matchTag==null) matchTag = "";
if (cacheControl==null) cacheControl = "";
if (cacheControl.equalsIgnoreCase("no-cache")==false){
if (eTag.equalsIgnoreCase(matchTag)){
return true;
}
else{
//Internet Explorer 6 uses "if-modified-since" instead of "if-none-match"
matchTag = request.getHeader("if-modified-since");
if (matchTag!=null){
for (String tag: matchTag.split(";")){
if (tag.trim().equalsIgnoreCase(date)){
return true;
}
}
}
}
}
return false;
}
//**************************************************************************
//** getFields
//**************************************************************************
/** Returns an array of ServiceRequest.Fields by parsing the "fields"
* parameter in the HTTP request (e.g. "?fields=id,firstName,lastName").
*/
public Field[] getFields(){
if (fields!=null) return fields;
String fields = getParameter("fields").toString();
//If fields are empty, simply return an ampty array
if (fields==null || fields.length()==0){
this.fields = new Field[0];
return this.fields;
}
//Parse the fields
this.fields = getFields(fields);
return this.fields;
}
//**************************************************************************
//** getFields
//**************************************************************************
/** Used to parse a given String into an array of Fields.
* @param fields A comma delimited list of fields (e.g. "id,firstName,lastName")
*/
public Field[] getFields(String fields){
ArrayList<Field> arr = new ArrayList<>();
try{
//Parse fields parameter using JSQLParser
CCJSqlParserManager parserManager = new CCJSqlParserManager();
Select select = (Select) parserManager.parse(new StringReader("SELECT " + fields + " FROM T"));
PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
//Iterate through the fields and update the array
Iterator<SelectItem> it = plainSelect.getSelectItems().iterator();
while (it.hasNext()){
SelectExpressionItem si = (SelectExpressionItem) it.next();
Expression expression = si.getExpression();
String alias = si.getAlias()==null ? null : si.getAlias().getName();
//Check if the expression contains a function
String functionName = null;
try{
Function f = (Function) expression;
functionName = f.getName().toLowerCase();
}
catch(Exception e){
try{
SubSelect ss = (SubSelect) expression;
functionName = "SELECT";
}
catch(Exception ex){
}
}
String column = expression.toString();
boolean isFunction = functionName!=null;
if (!isFunction) column = StringUtils.camelCaseToUnderScore(column);
Field field = new Field(column);
field.setAlias(alias);
field.isFunction(isFunction);
field.setFunctionName(functionName);
arr.add(field);
}
}
catch(Throwable e){
//JSQLParser doesn't like one of the fields or JSqlParser is missing
//from the class path. If so, fallback to the JavaXT SQL parser.
arr.clear();
javaxt.sql.Parser sqlParser = new javaxt.sql.Parser("SELECT " + fields + " FROM T");
for (javaxt.sql.Parser.SelectStatement stmt : sqlParser.getSelectStatements()){
String column = stmt.getField();
boolean isFunction = stmt.isFunction();
if (!isFunction) column = StringUtils.camelCaseToUnderScore(column);
Field field = new Field(column);
field.setAlias(stmt.getAlias());
field.isFunction(isFunction);
arr.add(field);
}
}
return arr.toArray(new Field[arr.size()]);
}
//**************************************************************************
//** getFilter
//**************************************************************************
/** Returns a ServiceRequest.Filter by parsing the query string associated
* this request. Examples:
* <ul>
* <li>http://localhost?id=1 (id = 1)</li>
* <li>http://localhost?id>=1 (id > 1)</li>
* <li>http://localhost?id!=1 (id <> 1)</li>
* <li>http://localhost?id=1,2,3 (id in (1,2,3))</li>
* <li>http://localhost?id!=1,2,3 (id not in (1,2,3))</li>
* <li>http://localhost?active=true (active = true)</li>
* <li>http://localhost?name='Bob' (name = 'Bob')</li>
* <li>http://localhost?name='Bo%' (name like 'Bo%')</li>
* <li>http://localhost?name!='Bo%' (name not like 'Bo%')</li>
* </ul>
* Note that the operators can be transposed without altering the filter
* (e.g. ">=" is the same as "=>").
*
* <p/>
* Alternatively, a Filter can be generated by parsing a "filter" parameter
* containing JSON object. In fact, if a "filter" parameter is provided, it
* will be used instead of the query string.
*/
public Filter getFilter(){
if (filter!=null) return filter;
//Parse querystring
LinkedHashMap<String, javaxt.utils.Value> params = new LinkedHashMap<>();
if (hasParameter("filter")){
String str = getParameter("filter").toString();
if (str.startsWith("{") && str.endsWith("}")){
JSONObject json = new JSONObject(str);
for (String key : json.keySet()){
params.put(key, json.get(key));
}
}
else{
LinkedHashMap<String, List<String>> map = javaxt.utils.URL.parseQueryString(str);
Iterator<String> it = map.keySet().iterator();
while (it.hasNext()){
String key = it.next();
List<String> vals = map.get(key);
if (!vals.isEmpty()) params.put(key, new javaxt.utils.Value(vals.get(0)));
}
}
}
else{
for (String key : getParameterNames()){
params.put(key, getParameter(key));
}
}
//Create filter
filter = new Filter();
Iterator<String> it = params.keySet().iterator();
while (it.hasNext()){
String key = it.next();
//Skip reserved keywords
key = key.trim();
String k = key.toLowerCase();
if (k.equals("fields") || k.equals("where") || k.equals("orderby") ||
k.equals("limit") || k.equals("offset") || k.equals("page") ||
k.equals("count") || k.equals("_") || k.isEmpty()){
continue;
}
//Parse val
String val = params.get(key).toString();
if (val!=null){
//Check if val is a JSON array
if (val.startsWith("[") && val.endsWith("]")){
try{
//TODO: generate comma delimited list?
}
catch(Exception e){}
}
}
//Get characters before and after the "=" sign
String a = key.length()==1 ? key : key.substring(key.length()-1);
String b = val==null ? "" : val.substring(0, 1);
//Get op and update key and val as needed
String op;
if (val==null) val = "null";
if (val.contains(",") && !(val.startsWith("'") || val.startsWith("!'"))){
op = "IN";
if (b.equals("!")){
val = val.substring(1).trim();
op = "NOT IN";
}
if (a.equals("!")){
key = key.substring(0, key.length()-1);
op = "NOT IN";
}
StringBuilder str = new StringBuilder("(");
int x = 0;
String nullVal = null;
for (String s : val.split(",")){
if (s.equalsIgnoreCase("NULL") || s.equalsIgnoreCase("!NULL")){
nullVal = s;
}
else{
if (x>0) str.append(",");
str.append(s);
x++;
}
}
str.append(")");
if (nullVal!=null){
if (op.equals("IN")){
str.append(" OR ");
str.append(StringUtils.camelCaseToUnderScore(key));
str.append(" IS");
if (nullVal.startsWith("!")) str.append(" NOT");
str.append(" NULL");
}
else{ //not tested...
str.append(" AND ");
str.append(StringUtils.camelCaseToUnderScore(key));
str.append(" IS");
if (nullVal.startsWith("!")) str.append(" NOT");
str.append(" NULL");
}
}
val = str.toString();
}
else {
if (a.equals("!") || b.equals("!")){
op = "<>";
if (b.equals("!")){
val = val.substring(1).trim();
}
if (a.equals("!")){
key = key.substring(0, key.length()-1);
}
}
else if (a.equals(">") || b.equals(">")){
op = ">=";
if (b.equals(">")){
val = val.substring(1).trim();
}
if (a.equals(">")){
key = key.substring(0, key.length()-1);
}
}
else if (a.equals("<") || b.equals("<")){
op = "<=";
if (b.equals("<")){
val = val.substring(1).trim();
}
if (a.equals("<")){
key = key.substring(0, key.length()-1);
}
}
else{
op = "=";
}
}
//Special case for nulls
if (val.equalsIgnoreCase("null")){
if (op.equals("=")) op = "IS";
if (op.equals("<>")) op = "IS NOT";
}
//Special case for like
if (val.contains("%") && val.startsWith("'") && val.endsWith("'")){
if (op.equals("=")) op = "LIKE";
if (op.equals("<>")) op = "NOT LIKE";
}
if (op.equals("=")){
if (val.toLowerCase().startsWith("startswith(") && val.endsWith(")")){
op = "LIKE";
val = "'" + val.substring(11, val.length()-1).replace("'", "''") + "%'";
}
else if (val.toLowerCase().startsWith("endswith(") && val.endsWith(")")){
op = "LIKE";
val = "'%" + val.substring(9, val.length()-1).replace("'", "''") + "'";
}
else if (val.toLowerCase().startsWith("contains(") && val.endsWith(")")){
op = "LIKE";
val = "'%" + val.substring(9, val.length()-1).replace("'", "''") + "%'";
}
}
key = StringUtils.camelCaseToUnderScore(key);
filter.set(key, op, val);
}
return filter;
}
//**************************************************************************
//** getWhere
//**************************************************************************
/** Returns the value for the "where" parameter in the HTTP request.
*/
public String getWhere(){
return getParameter("where").toString();
}
//**************************************************************************
//** getSort
//**************************************************************************
/** Returns an order by statement found in the request (e.g. "orderby" query
* string). The order by statement may be given as a comma delimited list
* of fields with optional sort direction for each field. Alternatively,
* the order by statement can be specified as a JSON array with a "property"
* and "direction" for each entry in the array. The order by statement is
* encapsulated as an instance of the Sort class.
*/
public Sort getSort(){
if (sort!=null) return sort;
LinkedHashMap<String, String> fields = new LinkedHashMap<>();
String orderBy = getParameter("orderby").toString();
if (orderBy!=null){
if (orderBy.startsWith("[") && orderBy.endsWith("]")){
//Example: &sort=[{"property":"dob","direction":"ASC"}]
JSONArray arr = new JSONArray(orderBy);
for (int i=0; i<arr.length(); i++){
JSONObject json = arr.get(i).toJSONObject();
String key = json.get("property").toString();
String dir = json.get("direction").toString();
if (key!=null){
if (dir==null) dir = "ASC";
fields.put(key, dir);
}
}
}
else{
for (String field : orderBy.split(",")){
field = field.trim();
if (field.length()>0){
String a, b;
String f = field.toUpperCase();
if (f.endsWith(" ASC") || f.endsWith(" DESC")){
int x = field.lastIndexOf(" ");
a = field.substring(0, x).trim();
b = field.substring(x).trim();
}
else{
a = field;
b = "ASC";
}
a = StringUtils.camelCaseToUnderScore(a).toUpperCase();
fields.put(a, b);
}
}
}
}
sort = new Sort(fields);
return sort;
}
//**************************************************************************
//** Sort Class
//**************************************************************************
/** Used to encapsulate an "order by" statement
*/
public class Sort {
LinkedHashMap<String, String> fields;
public Sort(LinkedHashMap<String, String> fields){
this.fields = fields;
}
public LinkedHashMap<String, String> getFields(){
return fields;
}
public java.util.Set<String> getKeySet(){
return fields.keySet();
}
public String get(String key){
return fields.get(key);
}
public boolean isEmpty(){
return fields.isEmpty();
}
}
//**************************************************************************
//** Field Class
//**************************************************************************
/** Used to encapsulate an entry in a "select" statement
*/
public class Field {
private String col;
private String table;
private String alias;
private boolean isFunction;
private String functionName;
public Field(String field){
col = field;
isFunction = false;
}
public String getColumn(){
return col;
}
public void setAlias(String alias){
this.alias = alias;
}
public boolean isFunction(){
return isFunction;
}
public void isFunction(boolean isFunction){
this.isFunction = isFunction;
}
public void setFunctionName(String functionName){
this.functionName = functionName;
}
public String getFunctionName(){
return functionName;
}
public String toString(){
String str = col;
if (table!=null) str = table + "." + str;
if (alias!=null) str += " as " + alias;
return str;
}
public boolean equals(Object obj){
if (obj instanceof String){
String str = (String) obj;
if (str.equalsIgnoreCase(col)) return true;
if (str.equalsIgnoreCase(alias)) return true;
if (str.equalsIgnoreCase(this.toString())) return true;
}
else if (obj instanceof Field){
Field field = (Field) obj;
return field.equals(this.toString());
}
return false;
}
}
//**************************************************************************
//** Filter Class
//**************************************************************************
/** A Filter consists of one or more Filter.Items. Each Filter.Item consists
* of a field/column (e.g. "id"), an operator (e.g. "="), and a value
* constraint (e.g. "1"). Together, the Items can be joined to generate a
* "where" clause for a SQL query.
*/
public class Filter {
private LinkedHashMap<String, Item> items = new LinkedHashMap<>();
public class Item {
private String col;
private String op;
private javaxt.utils.Value val;
private Item(String col, String op, javaxt.utils.Value val){
this.col = col;
this.op = op;
this.val = val;
}
public String getField(){
return col;
}
public void setField(String col){
this.col = col;
}
public String getOperation(){
return op;
}
public void setOperation(String op){
this.op = op;
}
public javaxt.utils.Value getValue(){
return val;
}
public void setValue(javaxt.utils.Value val){
if (val==null) val = new javaxt.utils.Value(null);
this.val = val;
}
public String toString(){
return StringUtils.camelCaseToUnderScore(col) + " " + op + " " + val;
}
public JSONObject toJson(){
JSONObject json = new JSONObject();
json.set("col", col);
json.set("op", op);
json.set("val", val);
return json;
}
}
protected Filter(){}
public void set(String col, Object val){
set(col, "=", val);
}
public void set(String col, String op, Object val){
String key = col.toLowerCase();
javaxt.utils.Value v = (val instanceof javaxt.utils.Value) ?
(javaxt.utils.Value) val : new javaxt.utils.Value(val);
if (v.isNull()){
items.remove(key);
}
else{
items.put(key, new Item(col, op, v));
}
}
public javaxt.utils.Value get(String col){
Item item = items.get(col.toLowerCase());
if (item!=null){
return item.val;
}
return new javaxt.utils.Value(null);
}
public boolean isEmpty(){
return items.isEmpty();
}
public Item[] getItems(){
ArrayList<Item> arr = new ArrayList<>();
Iterator<String> it = items.keySet().iterator();
while (it.hasNext()){
String key = it.next();
Item item = items.get(key);
arr.add(item);
}
return arr.toArray(new Item[arr.size()]);
}
public JSONArray toJson(){
JSONArray arr = new JSONArray();
for (Item item : getItems()) arr.add(item.toJson());
return arr;
}
}
//**************************************************************************
//** getSelectStatement
//**************************************************************************
/** Returns a SQL select statement for the current request. Compiles the
* select statement using an array of Fields returned by the getFields()
* method. If no fields are found in the request, a "select *" statement is
* returned. Note that fields that are not functions are prepended with a
* table name.
* @param tableName If given, fields that are not functions are prepended
* with a table name.
*/
public String getSelectStatement(String tableName){
StringBuilder sql = new StringBuilder("select ");
Field[] fields = getFields();
if (fields==null || fields.length==0) sql.append("*");
else{
for (int i=0; i<fields.length; i++){
if (i>0) sql.append(", ");
Field field = fields[i];
String fieldName = field.toString();
if (field.isFunction()){
sql.append(fieldName);
}
else{
fieldName = StringUtils.camelCaseToUnderScore(fieldName);
if (tableName!=null && !tableName.isEmpty()){
sql.append(tableName + ".");
}
sql.append(fieldName);
}
}
}
return sql.toString();
}
//**************************************************************************
//** getOrderByStatement
//**************************************************************************
/** Returns a SQL order by statement for the current request. Compiles the
* order by statement using Sort class returned by the getSort() method.
* Returns an empty string if a sort was not defined. Otherwise, the order
* by statement returned, starting with a white space " " for convenience.
*/
public String getOrderByStatement(){
Sort sort = getSort();
if (!sort.isEmpty()){
StringBuilder sql = new StringBuilder();
sql.append(" order by ");
java.util.Iterator<String> it = sort.getKeySet().iterator();
while (it.hasNext()){
String colName = it.next();
String direction = sort.get(colName);
sql.append(colName);
sql.append(" ");
sql.append(direction);
if (it.hasNext()) sql.append(", ");
}
return sql.toString();
}
return "";
}
//**************************************************************************
//** getOffsetLimitStatement
//**************************************************************************
/** Returns a SQL offset and limit statement for the current request. These
* statements are used for pagination. Different database vendors use
* different keywords to specify offset and limit. The given Driver is used
* to determine which keywords to use. Returns an empty string if limit and
* is offset are not defined. Otherwise, the limit and/or offset statement
* is returned, starting with a white space " " for convenience.
* @param driver An instance of a javaxt.sql.Driver class.
*/
public String getOffsetLimitStatement(javaxt.sql.Driver driver){
StringBuilder sql = new StringBuilder();
//Get offset
Object offset = getOffset();
if (offset!=null){
StringBuilder str = new StringBuilder();
str.append(" offset ");
str.append(offset);
if (driver.equals("Oracle")){
str.append(" rows"); //OFFSET 20 ROWS
}
offset = str.toString();
}
else{
offset = "";
}
//Get limit
Object limit = getLimit();
if (limit!=null){
StringBuilder str = new StringBuilder();
if (driver.equals("Oracle")){
str.append(" fetch next ");
str.append(limit);
str.append(" only");
}
else { //PostgreSQL and H2
str.append(" limit ");
str.append(limit);
}
limit = str.toString();
}
else{
limit = "";
}
//Append offset and limit
if (driver.equals("H2")){
sql.append(limit);
sql.append(offset);
}
else{
sql.append(offset);
sql.append(limit);
}
return sql.toString();
}
}
|
|