java.lang.Object

com.onenetwork.platform.data.sql.SqlParams

public class SqlParams
extends java.lang.Object

Used to pass SQL parameter values when executing a query with SqlService.

SQL queries using SqlParams should use $PARAMNAME$ syntax in their query. For example, for the following query ...

 SELECT NAME, AGE FROM EMPLOYEE WHERE NAME LIKE $NAME$ and AGE < $MAXAGE$

... one would expect SqlParams to be constructed something like ...

 SqlParams params = new SqlParams();
 params.setStringValue("NAME", "John%");
 params.setIntValue("MAXAGE", 40);

The set() methods in SqlParams return the object itself, allowing easy chaining of multiple sets in a single line of code:

 SqlParams params = new SqlParams()
   .setStringValue("FOO", "ABC")
   .setLongValue("BAR", 1L);

SqlParams also supports the binding of collections. For example ...

 SELECT NAME, AGE FROM EMPLOYEE WHERE NAME IN $NAMES$

... would be bound using ...

 SqlParams params = new SqlParams();
 List names = Arrays.asList("John", "David", "Brian");
 params.setCollectionValue("NAMES", names);

At runtime, this would evaluate to the following query, with appropriate bindings:

 SELECT NAME, AGE FROM EMPLOYEE WHERE NAME IN (?, ?, ?)

If the collection passed is empty, the list evaluates to (null), which will not match any column.

Note that you can escape the dollar symbol in a query using \$. For example:

 SELECT '\$' || PRICE FROM ITEM

Nested Class Summary

Nested Classes

Modifier and Type Class Description

static class SqlParams.Binding
Captures a DaoDataType + value.
Field Summary

Fields

Modifier and Type Field Description

protected java.util.Map<java.lang.Object,SqlParams.Binding> bindings

protected java.util.List<SqlParams.Binding> orderedBindings

Constructor Summary

Constructors
Constructor	Description
`SqlParams()`	Constructs a new SqlParams with no bindings
`SqlParams(SqlParams sqlParams)`	Constructs a new SqlParams which copies the bindings from the given object
`SqlParams(java.util.Map<java.lang.String,?> paramValues)`	Constructs a new SqlParams given a Map of name-to-value param pairs.

Method Summary

Modifier and Type	Method	Description
`void`	`addOrderedBinding(SqlParams.Binding binding)`	Deprecated. Not intended for use by clients
`void`	`clearOrderedBindings()`	Deprecated. Not intended for use by clients
`boolean`	`containsKey(java.lang.String paramName)`	Returns whether the given param name is bound.
`static SqlParams`	`fromJSON(org.json.JSONObject json)`	Given a JSON, returns a SqlParams object.
`SqlParams.Binding`	`getBinding(int index)`	Deprecated. Not intended for use by clients
`SqlParams.Binding`	`getBinding(java.lang.String paramName)`	Deprecated. Not intended for use by clients
`java.util.Set<java.lang.String>`	`getBoundParamNames()`	Returns the names of all parameters bound into this SqlParams object
`int`	`getNoOfOrderedParams()`	Deprecated. Not intended for use by clients
`int`	`getNoOfParams()`	Returns the number of bound params.
`java.util.Iterator<SqlParams.Binding>`	`getOrderedBindings()`	Deprecated. Not intended for use by clients
`protected java.lang.Object`	`getValue(int index)`	Deprecated. Not intended for use by clients
`java.lang.Object`	`getValue(java.lang.String paramName)`	Returns the value of the given parameter.
`void`	`putAll(SqlParams sqlParams)`	Takes all the params from the given argument and merges them into this SqlParams object.
`SqlParams`	`setBooleanValue(java.lang.String paramName, boolean value)`	Set a Boolean param
`SqlParams`	`setByteValue(java.lang.String paramName, byte value)`	Set a Byte param
`SqlParams`	`setCollectionValue(java.lang.String paramName, java.util.Collection<?> values)`	Binds multiple params for the given name.
`SqlParams`	`setDoubleValue(java.lang.String paramName, double value)`	Set a Double param
`SqlParams`	`setFloatValue(java.lang.String paramName, float value)`	Set a Float param
`SqlParams`	`setIntValue(java.lang.String paramName, int value)`	Set an Integer param
`SqlParams`	`setLongValue(java.lang.String paramName, long value)`	Set a Long param
`SqlParams`	`setNullBooleanValue(java.lang.String paramName)`	Indicates that the null value should be bound for the given boolean parameter.
`SqlParams`	`setNullByteValue(java.lang.String paramName)`	Indicates that the null value should be bound for the given byte parameter
`SqlParams`	`setNullDoubleValue(java.lang.String paramName)`	Indicates that the null value should be bound for the given double parameter
`SqlParams`	`setNullFloatValue(java.lang.String paramName)`	Indicates that the null value should be bound for the given float parameter
`SqlParams`	`setNullIntValue(java.lang.String paramName)`	Indicates that the null value should be bound for the given int parameter
`SqlParams`	`setNullLongValue(java.lang.String paramName)`	Indicates that the null value should be bound for the given long parameter
`SqlParams`	`setNullShortValue(java.lang.String paramName)`	Indicates that the null value should be bound for the given short parameter
`SqlParams`	`setNullStringValue(java.lang.String paramName)`	Indicates that the null value should be bound for the given string parameter
`SqlParams`	`setNullTimestampValue(java.lang.String paramName)`	Indicates that the null value should be bound for the given timestamp parameter
`SqlParams`	`setNullValue(java.lang.String paramName, int daoDataType)`	Deprecated. Not intended for use by clients
`SqlParams`	`setShortValue(java.lang.String paramName, short value)`	Set a Short param
`SqlParams`	`setStringValue(java.lang.String paramName, java.lang.String value)`	Set a String param
`SqlParams`	`setTimestampUTCValue(java.lang.String paramName, java.sql.Timestamp value)`	Set a Timestamp parameter bound to the UTC timezone, which will be used when it's constructed as a SQL TIMESTAMP value for the database.
`SqlParams`	`setTimestampValue(java.lang.String paramName, java.sql.Timestamp value)`	Set a Timestamp param
`SqlParams`	`setValue(java.lang.String param, java.lang.Object paramVal)`	Use this method if data type of sqlparam value is not known up front.
`org.json.JSONObject`	`toJSON()`	Returns a JSON representation of this SqlParams.
`java.lang.String`	`toString()`

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait

Field Details
- bindings
  
  protected java.util.Map<java.lang.Object,SqlParams.Binding> bindings
- orderedBindings
  
  protected java.util.List<SqlParams.Binding> orderedBindings
Constructor Details
- SqlParams
  
  public SqlParams()
  
  Constructs a new SqlParams with no bindings
- SqlParams
  
  public SqlParams(java.util.Map<java.lang.String,?> paramValues)
  
  Constructs a new SqlParams given a Map of name-to-value param pairs. Type of params is inferred from the java types; see setValue(String, Object).
  
  Parameters:
  
  paramValues - name-to-value param pairs
- SqlParams
  
  public SqlParams(SqlParams sqlParams)
  
  Constructs a new SqlParams which copies the bindings from the given object
  
  Parameters:
  
  sqlParams - bindings should be copied from this object into the new SqlParams
Method Details
- putAll
  
  public void putAll(SqlParams sqlParams)
  
  Takes all the params from the given argument and merges them into this SqlParams object. Any existing bindings with colliding names are replaced.
- setStringValue
  
  public SqlParams setStringValue(java.lang.String paramName, java.lang.String value)
  
  Set a String param
  
  Parameters:
  
  paramName - param name
  
  value - param value
- setLongValue
  
  public SqlParams setLongValue(java.lang.String paramName, long value)
  
  Set a Long param
  
  Parameters:
  
  paramName - param name
  
  value - param value
- setIntValue
  
  public SqlParams setIntValue(java.lang.String paramName, int value)
  
  Set an Integer param
  
  Parameters:
  
  paramName - param name
  
  value - param value
- setByteValue
  
  public SqlParams setByteValue(java.lang.String paramName, byte value)
  
  Set a Byte param
  
  Parameters:
  
  paramName - param name
  
  value - param value
- setShortValue
  
  public SqlParams setShortValue(java.lang.String paramName, short value)
  
  Set a Short param
  
  Parameters:
  
  paramName - param name
  
  value - param value
- setFloatValue
  
  public SqlParams setFloatValue(java.lang.String paramName, float value)
  
  Set a Float param
  
  Parameters:
  
  paramName - param name
  
  value - param value
- setDoubleValue
  
  public SqlParams setDoubleValue(java.lang.String paramName, double value)
  
  Set a Double param
  
  Parameters:
  
  paramName - param name
  
  value - param value
- setTimestampValue
  
  public SqlParams setTimestampValue(java.lang.String paramName, java.sql.Timestamp value)
  
  Set a Timestamp param
  
  Parameters:
  
  paramName - param name
  
  value - param value
- setTimestampUTCValue
  
  public SqlParams setTimestampUTCValue(java.lang.String paramName, java.sql.Timestamp value)
  
  Set a Timestamp parameter bound to the UTC timezone, which will be used when it's constructed as a SQL TIMESTAMP value for the database. By comparison, the setTimestampValue(String, Timestamp) method will use the default server timezone when constructing the SQL TIMESTAMP value for the database.
  
  Parameters:
  
  paramName - param name
  
  value - param value
- setBooleanValue
  
  public SqlParams setBooleanValue(java.lang.String paramName, boolean value)
  
  Set a Boolean param
  
  Parameters:
  
  paramName - param name
  
  value - param value
- setCollectionValue
  
  public SqlParams setCollectionValue(java.lang.String paramName, java.util.Collection<?> values)
  
  Binds multiple params for the given name. At runtime, this will result in binding the parameters within a SQL list, i.e. (?, ?, ?) where the number of bindings matches the length of the collection. If the collection is empty, the list will be generated as (null).
  
  Parameters:
  
  paramName - param name
  
  values - param values
- getNoOfParams
  
  public int getNoOfParams()
  
  Returns the number of bound params.
- getValue
  
  protected java.lang.Object getValue(int index)
  
  Deprecated.
  Not intended for use by clients
- getBinding
  
  public SqlParams.Binding getBinding(int index)
  
  Deprecated.
  Not intended for use by clients
- getBinding
  
  public SqlParams.Binding getBinding(java.lang.String paramName)
  
  Deprecated.
  Not intended for use by clients
- getValue
  
  public java.lang.Object getValue(java.lang.String paramName)
  
  Returns the value of the given parameter.
- containsKey
  
  public boolean containsKey(java.lang.String paramName)
  
  Returns whether the given param name is bound.
- addOrderedBinding
  
  public void addOrderedBinding(SqlParams.Binding binding)
  
  Deprecated.
  Not intended for use by clients
- clearOrderedBindings
  
  public void clearOrderedBindings()
  
  Deprecated.
  Not intended for use by clients
- getNoOfOrderedParams
  
  public int getNoOfOrderedParams()
  
  Deprecated.
  Not intended for use by clients
- getOrderedBindings
  
  public java.util.Iterator<SqlParams.Binding> getOrderedBindings()
  
  Deprecated.
  Not intended for use by clients
- toString
  
  public java.lang.String toString()
  
  Overrides:
  
  toString in class java.lang.Object
- getBoundParamNames
  
  public java.util.Set<java.lang.String> getBoundParamNames()
  
  Returns the names of all parameters bound into this SqlParams object
- setNullBooleanValue
  
  public SqlParams setNullBooleanValue(java.lang.String paramName)
  
  Indicates that the null value should be bound for the given boolean parameter.
- setNullByteValue
  
  public SqlParams setNullByteValue(java.lang.String paramName)
  
  Indicates that the null value should be bound for the given byte parameter
- setNullDoubleValue
  
  public SqlParams setNullDoubleValue(java.lang.String paramName)
  
  Indicates that the null value should be bound for the given double parameter
- setNullFloatValue
  
  public SqlParams setNullFloatValue(java.lang.String paramName)
  
  Indicates that the null value should be bound for the given float parameter
- setNullIntValue
  
  public SqlParams setNullIntValue(java.lang.String paramName)
  
  Indicates that the null value should be bound for the given int parameter
- setNullLongValue
  
  public SqlParams setNullLongValue(java.lang.String paramName)
  
  Indicates that the null value should be bound for the given long parameter
- setNullShortValue
  
  public SqlParams setNullShortValue(java.lang.String paramName)
  
  Indicates that the null value should be bound for the given short parameter
- setNullStringValue
  
  public SqlParams setNullStringValue(java.lang.String paramName)
  
  Indicates that the null value should be bound for the given string parameter
- setNullTimestampValue
  
  public SqlParams setNullTimestampValue(java.lang.String paramName)
  
  Indicates that the null value should be bound for the given timestamp parameter
- setNullValue
  
  public SqlParams setNullValue(java.lang.String paramName, int daoDataType)
  
  Deprecated.
  Not intended for use by clients
- setValue
  
  public SqlParams setValue(java.lang.String param, java.lang.Object paramVal)
  
  Use this method if data type of sqlparam value is not known up front. Supports Boolean, Byte, Calendar, Collection, Date, Double, Float, Integer, Long, Short, String and Timestamp.
  
  Parameters:
  
  param -
  
  paramVal -
- toJSON
  
  public org.json.JSONObject toJSON()
  
  Returns a JSON representation of this SqlParams. Symmetric with #fromJSON().
- fromJSON
  
  public static SqlParams fromJSON(org.json.JSONObject json)
  
  Given a JSON, returns a SqlParams object. Symmetric with toJSON().

Modifier and Type	Field	Description
`protected java.util.Map<java.lang.Object,SqlParams.Binding>`	`bindings`
`protected java.util.List<SqlParams.Binding>`	`orderedBindings`

Class SqlParams

Nested Class Summary

Field Summary

Constructor Summary

Method Summary

Methods inherited from class java.lang.Object

Field Details

Constructor Details

Method Details