Package simpleorm.dataset

Class SQuery<T extends SRecordInstance>

  • public class SQuery<T extends SRecordInstance>
extends java.lang.Object
    The main Query interface that provides structured queries.

    For example:-

    Department d100q = session.findOrCreate(Department.DEPARTMENT, "100"); SQuery&lt;Employee&gt; query = new SQuery(Employee.EMPLOYEE) .eq(Employee.DEPARTMENT, d100q) // and .like(Employee.NAME, "%One%") .descending(Employee.SALARY); List&lt;Employee&gt; emps = session.query(query);

    This API is a little unusual in that there is only one SQuery instance created, and each of the methods update its state and then return this.

    The normal expansion of the query into SQL is performed by SQueryExecute in the SSessionJdbc package. The raw* methods can be used to poke any string into the SQL Where or Order By clauses in the order they appear.

    • Method Detail

      • setLimit

        public SQuery<T> setLimit​(long lim)
        Set limit

      • getLimit

        public long getLimit()

      • getOffset

        public long getOffset()

      • setOffset

        public SQuery<T> setOffset​(long off)
        Set offset

      • rawJoin

        public SQuery<T> rawJoin​(java.lang.String sqlJoin,
                         java.lang.Object... parameters)
        Add a join to the join statement. Spaces before and after statement are added systematically.

      • rawPredicate

        public SQuery<T> rawPredicate​(java.lang.String predicate,
                              java.lang.Object... parameters)
        Add predicate to the where class, eg. "BUDGET > ?". Not normally called directly.

      • rawOrderBy

        public SQuery<T> rawOrderBy​(java.lang.String raw)
        Add a clause to the OrderBy statement, eg. "NAME DESC". Commas are added automatically.

      • rawSql

        public SQuery rawSql​(java.lang.String rawSelectSql,
                     java.lang.Object... parameters)
        Just specifies the entire SQL statement, including the Select.

      • fieldRelopParameter

        public SQuery<T> fieldRelopParameter​(SFieldScalar field,
                                     java.lang.String relop,
                                     java.lang.Object value)
        Generates field relop ? and then subsequently sets the parameter value. Eg.

        fieldRelopParameter(Employee.Name, "=", myName)

      • fieldRelopParameter

        public SQuery<T> fieldRelopParameter​(SFieldScalar field1,
                                     java.lang.String relop,
                                     SFieldScalar field2)
        Generates field1 relop field2

        E.g. fieldRelopParameter(Order.QuantityRequired, "=", Order.QuantityReceived)

        Mainly useful for Joins.

      • fieldQuery

        public SQuery<T> fieldQuery​(SFieldScalar field,
                            java.lang.String clause)
        Generates field clause, ie the clause string is poked literally into the query. Eg.

        fieldQuery(Employee.Name, "= 'Fred'")

        Use fieldRelopParameter instead for parameters determined at run time as blindly concatenating strings is dangerous.

      • join

        public SQuery<T> join​(SFieldReference<?> reference)
        Join to another table, based on the specified reference that is in the query table. This peforms an eager lookup on the referenced tables, so avoiding the N+1 problem.

        Joins only work from the query table to tables directly liked by the reference. It is not currently possible to join a table to itself (eg. Employee.Manager).

        Normally the looked up records are also retrieved, although it is possible to specify selectMode = NONE if the join is only for the WHERE clause.

        Currently implemented as Inner joins, but will be fixed to be left outer joins.

        Note that this is only useful if the number of joined in records is substantial. For example, if there are 1,000 employees in 10 departments, then it is probably faster (and certainly simpler) to retrieve the departments lazily rather than retrieve the fields for the 10 departments 1000 times and throw them away each time.

        Note also that Subselects can be used to reference fields in the where clause without introduing outer join issues.

      • eq

        public SQuery<T> eq​(SFieldMeta field,
                    java.lang.Object value)
        Normally just adds fieldRelopParameter(field, "=", value).

        If field is a reference it recursively expands the foreign keys, and value must be an instance of the same record type.

        value must not be null, you need the special case IS NULL test. (It would be possible to optimize this here, but what about field == field where one of them is null -- that would be inconsistent.)

      • equivalent

        public SQuery<T> equivalent​(SFieldBoolean field,
                            boolean value)
        True if boolean field == writeFieldValue(value). Ie. value is converted from bool to "Y"/"N" etc.

      • ne

        public SQuery<T> ne​(SFieldMeta field,
                    java.lang.Object value)
        Just adds fieldRelopParameter(field, "<>", value).

        Note that there are few methods ne(SfieldMeta, int) etc. This is because 5 relops * 5 data types would require 25 methods! Java 1.5 boxing will (finally) make this unnecessary anyway.

      • isNull

        public SQuery<T> isNull​(SFieldMeta field)
        Just adds fieldQuery(field, "IS NULL")

      • isNotNull

        public SQuery<T> isNotNull​(SFieldMeta field)
        Just adds fieldQuery(field, "IS NULL")

      • gt

        public SQuery<T> gt​(SFieldScalar field,
                    java.lang.Object value)
        Just adds fieldRelopParameter(field, ">", value)

      • lt

        public SQuery<T> lt​(SFieldScalar field,
                    java.lang.Object value)
        Just adds fieldRelopParameter(field, "<", value)

      • le

        public SQuery<T> le​(SFieldScalar field,
                    java.lang.Object value)
        Just adds fieldRelopParameter(field, "<=", value)

      • ge

        public SQuery<T> ge​(SFieldScalar field,
                    java.lang.Object value)
        Just adds fieldRelopParameter(field, ">=", value)

      • in

        public SQuery<T> in​(SFieldScalar field,
                    java.lang.Object... values)
        Use or clause to simulate the in clause

      • like

        public SQuery<T> like​(SFieldScalar field,
                      java.lang.Object value)
        Just adds fieldRelopParameter(field, "like", value)

      • ascending

        public SQuery<T> ascending​(SFieldMeta field)
        rawOrderBy(field.columnName)

      • descending

        public SQuery<T> descending​(SFieldMeta field)
        rawOrderBy(field.columnName) DESC

      • toString

        public java.lang.String toString()
        See SSession.queryToString for a fuller print out.
        Overrides:
        toString in class java.lang.Object

      • getQueryParameters

        public java.util.ArrayList<java.lang.Object> getQueryParameters()

      • getJoins

        public java.util.List<SQuery.Join> getJoins()

      • getRawSql

        public java.lang.String getRawSql()