Package ece.ing3.java.projet.database.sql.queries

Class SQLSelect<M extends Model>

java.lang.Object
ece.ing3.java.projet.database.sql.queries.SQLWhereQuery<SQLSelect>
ece.ing3.java.projet.database.sql.queries.SQLSelect<M>
Type Parameters:
M - Model class
All Implemented Interfaces:
SQLRequest
public class SQLSelect<M extends Model> extends SQLWhereQuery<SQLSelect>
SQL selector helper.

Provides a convenient way to build select SQL queries, reactive-style, for a provided model class.


SQL Select provides multiple ways to create SQL queries.
Table name is directly inferred the from model class and column names are either gathered as defined by the model inputs.


SQLSelect supports conditioning using where clauses.
Nesting where clauses is directly supported as a byproduct of using Where under the hood.
For this, several methods are provided.

Where should be started by calling SQLWhereQuery.where(Where). While SQLWhereQuery.andWhere(Where) and SQLWhereQuery.orWhere(Where) can be used to start, it is strongly discouraged and not supported.

Where clauses can then be chained using either SQLWhereQuery.andWhere(Where) and SQLWhereQuery.orWhere(Where), depending on the desired operator.

Each SQLWhereQuery.where(Where), SQLWhereQuery.andWhere(Where) and SQLWhereQuery.orWhere(Where) have shortcut methods, respectively SQLWhereQuery.where(String, String, Object), SQLWhereQuery.andWhere(String, String, Object) and SQLWhereQuery.orWhere(String, String, Object), to avoid creating new unnecessary Where clauses.


Similarly, SQLSelect supports order by clauses.
Again, several methods are provided.

Order By should be started by calling orderBy(OrderBy). While andOrderBy(OrderBy) can be used to start, it is strongly discouraged and not supported.

Order By clauses can then be chained using andOrderBy(OrderBy).

orderBy(String, Order) and andOrderBy(OrderBy) have shortcut methods, respectively orderBy(String, Order) and andOrderBy(String, Order), to avoid creating new unnecessary OrderBy clauses.


SQLSelect supports Reactive-style programming to create more compact code.
Instead of creating a new object and calling methods on it repeatedly, line by line, calls can be chained directly, eliminating the need to ceate a temprary object and significantly shorting the code. 

 SQLSelect<Model> s = new SQLSelect<>( Model.class );
 s.where( "col1", "=", "val1" );
 s.andWhere( "col2", "<", "56" );
 s.orderBy( "col3", Order.DESC );
 Model m = s.findUnique();

is strictly equivalent to 

 Model m = ( new SQLSelect<Model>( Model.class ) ).where( "col1", "=", "val1" ).andWhere( "col2", "<", "56" ).orderBy( "col3", Order.DESC ).findUnique();

Let us see some example usage.
For this, let us consider the following models: 

 public class ExampleModel extends Model {
     @Id
     @Column( name = 'id' )
     private Long exampleId;

     public ExampleModel() {}

     public Long getExampleId() {
         return exampleId;
     }

     public void setExampleId( Long v ) {
         exampleId = v;
     }
 }
 
 public class InheritedExampleModel extends ExampleModel {
      @Column( name = 'num' )
      private Long number;

     public InheritedExampleModel() {}

     public Long getNumber() {
         return number;
     }

     public void setNumber( Long v ) {
         number = v;
     }
 }

The simplest query is built by just passing a model class.
SQLSelect<ExampleModel> s = new SQLSelect<>( ExampleModel.class );
will generate : SELECT id as exampleId FROM examplemodel;
Column names are automatically retrieved and mapped accordingly.

Multiple classes can be passed to create a NATURAL JOIN request between them. The most complete model should be passed as generic type.
SQLSelect<InheritedExampleModel> s = new SQLSelect<>( new Class[]{ InheritedExampleModel.class, ExampleModel.class } );
will generate : SELECT id as exampleId, num as number FROM inheritedexamplemodel NATURAL JOIN examplemodel;

If NATURAL JOIN is not the desired join clause, custom join clauses and conditions can be passed as parameters.
There should be exactly one less join clauses and conditions than passed model classes.
SQLSelect<InheritedExampleModel> s = new SQLSelect<>( new Class[]{ InheritedExampleModel.class, ExampleModel.class }, new String[]{ 'LEFT OUTER JOIN' }, new String[]{ 'ON inheritedexamplemodel.id = examplemodel.id' } );
will generate : SELECT id as exampleId, num as number FROM inheritedexamplemodel LEFT INNER JOIN examplemodel ON inheritedexamplemodel.id = examplemodel.id;

For each possible combinations, it is possible to pass field names as last parameters or a column name String array to select specific columns.
SQLSelect<ExampleModel> s = new SQLSelect<>( ExampleModel.class, "exampleId" );
will generate : SELECT id as exampleId FROM examplemodel;
SQLSelect<InheritedExampleModel> s = new SQLSelect<>( new Class[]{ InheritedExampleModel.class, ExampleModel.class }, "number" );
will generate : SELECT num as number FROM inheritedexamplemodel NATURAL JOIN examplemodel;

It is also possible to tell the class not to process the passed selected fields and insert them as-is.

Author:
Virgile, Nicolas, Louis-Félix

  • Constructor Summary

    Constructors
    Constructor
    Description
    SQLSelect(Class<? extends Model> modelClass)
    Creates a new helper for a model class
    SQLSelect(Class<? extends Model>[] modelClasses)
    Creates a new helper for multiple model classes, assuming they are joined by NATURAL JOIN.
    SQLSelect(Class<? extends Model>[] modelClasses, boolean rawSelectedFields, String... selectedFields)
    Creates a new helper for multiple model classes, assuming they are joined by NATURAL JOIN, retrieving only the provided columns.
    SQLSelect(Class<? extends Model>[] modelClasses, String... selectedFields)
    Creates a new helper for multiple model classes, assuming they are joined by NATURAL JOIN, retrieving only the provided columns.
    SQLSelect(Class<? extends Model>[] modelClasses, String[] joinClause, String[] joinCondition)
    Creates a new helper for multiple model classes, joining them with the provided join clause and using the provided join condition.
    SQLSelect(Class<? extends Model>[] modelClasses, String[] joinClause, String[] joinCondition, boolean rawSelectedFields, String... selectedFields)
    Creates a new helper for multiple model classes, joining them with the provided join clause and using the provided join condition, retrieving only the provided columns.
    SQLSelect(Class<? extends Model>[] modelClasses, String[] joinClause, String[] joinCondition, String... selectedFields)
    Creates a new helper for multiple model classes, joining them with the provided join clause and using the provided join condition, retrieving only the provided columns.
    SQLSelect(Class<? extends Model> modelClass, boolean rawSelectedFields, String... selectedFields)
    Creates a new helper for a model class, retrieving only the provided columns.
    SQLSelect(Class<? extends Model> modelClass, String... selectedFields)
    Creates a new helper for a model class, retrieving only the provided columns.

  • Method Summary

    Modifier and Type
    Method
    Description
    SQLSelect
    andOrderBy(OrderBy condition)
    Chain another Order By condition using the provided values.
    SQLSelect
    andOrderBy(String column, Order order)
    Chain another Order By condition using the provided values.
    List<M>
    findList()
    Execute the built query and retrieve a list of model instances.
    ResultSet
    findRaw()
    Execute the built query and return a raw ResultSet
    M
    findUnique()
    Execute the built query and retrieve a unique, directly usable model instance.
    Class<? extends Model>
    getModelClass()
     
    boolean
    hasAtLeastOne()
    Execute the built query and return if there is at least one result.
    SQLSelect
    orderBy(OrderBy condition)
    Init a new Order By clause.
    SQLSelect
    orderBy(String column, Order order)
    Init a new Order By clause using the provided values.
    SQLSelect
    setSelectedFields(String... selectedFields)
    Sets new selected columns.
    String
    toString()
    Generate the SQL query for this request helper.

    Methods inherited from class ece.ing3.java.projet.database.sql.queries.SQLWhereQuery

    andWhere, andWhere, andWhere, getParameters, orWhere, orWhere, orWhere, where, where, where

    Methods inherited from class java.lang.Object

    equals, getClass, hashCode, notify, notifyAll, wait, wait, wait

  • Constructor Details

    • SQLSelect

      public SQLSelect(Class<? extends Model> modelClass)
      Creates a new helper for a model class
      Parameters:
      modelClass - Model class

    • SQLSelect

      public SQLSelect(Class<? extends Model> modelClass, String... selectedFields)
      Creates a new helper for a model class, retrieving only the provided columns.
      Parameters:
      modelClass - Model class
      selectedFields - Fields to retrieve

    • SQLSelect

      public SQLSelect(Class<? extends Model> modelClass, boolean rawSelectedFields, String... selectedFields)
      Creates a new helper for a model class, retrieving only the provided columns.
      Parameters:
      modelClass - Model class
      rawSelectedFields - true do not attempt to automatically process selected fields
      selectedFields - Fields to retrieve

    • SQLSelect

      public SQLSelect(Class<? extends Model>[] modelClasses)
      Creates a new helper for multiple model classes, assuming they are joined by NATURAL JOIN.
      Parameters:
      modelClasses - Model classes

    • SQLSelect

      public SQLSelect(Class<? extends Model>[] modelClasses, String... selectedFields)
      Creates a new helper for multiple model classes, assuming they are joined by NATURAL JOIN, retrieving only the provided columns.
      Parameters:
      modelClasses - Model classes
      selectedFields - Fields to retrieve

    • SQLSelect

      public SQLSelect(Class<? extends Model>[] modelClasses, boolean rawSelectedFields, String... selectedFields)
      Creates a new helper for multiple model classes, assuming they are joined by NATURAL JOIN, retrieving only the provided columns.
      Parameters:
      modelClasses - Model classes
      rawSelectedFields - true do not attempt to automatically process selected fields
      selectedFields - Fields to retrieve

    • SQLSelect

      public SQLSelect(Class<? extends Model>[] modelClasses, String[] joinClause, String[] joinCondition)
      Creates a new helper for multiple model classes, joining them with the provided join clause and using the provided join condition.
      Parameters:
      modelClasses - Model classes
      joinClause - Join clause
      joinCondition - Join condition

    • SQLSelect

      public SQLSelect(Class<? extends Model>[] modelClasses, String[] joinClause, String[] joinCondition, String... selectedFields) throws IllegalArgumentException
      Creates a new helper for multiple model classes, joining them with the provided join clause and using the provided join condition, retrieving only the provided columns.
      Parameters:
      modelClasses - Model classes
      joinClause - Join clause
      joinCondition - Join condition
      selectedFields - Fields to retrieve
      Throws:
      IllegalArgumentException - Join clause or condition is malformed

    • SQLSelect

      public SQLSelect(Class<? extends Model>[] modelClasses, String[] joinClause, String[] joinCondition, boolean rawSelectedFields, String... selectedFields) throws IllegalArgumentException
      Creates a new helper for multiple model classes, joining them with the provided join clause and using the provided join condition, retrieving only the provided columns.
      Parameters:
      modelClasses - Model classes
      joinClause - Join clause
      joinCondition - Join condition
      rawSelectedFields - true do not attempt to automatically process selected fields
      selectedFields - Fields to retrieve
      Throws:
      IllegalArgumentException - Join clause or condition is malformed

  • Method Details

    • getModelClass

      public Class<? extends Model> getModelClass()

    • setSelectedFields

      public SQLSelect setSelectedFields(String... selectedFields)
      Sets new selected columns.
      Parameters:
      selectedFields - New selected columns.
      Returns:
      This SQL select helper

    • orderBy

      public SQLSelect orderBy(OrderBy condition)
      Init a new Order By clause.
      Parameters:
      condition - Order By clause to use
      Returns:
      This SQL select helper

    • andOrderBy

      public SQLSelect andOrderBy(OrderBy condition)
      Chain another Order By condition using the provided values.
      Parameters:
      condition - Order By clause to chain
      Returns:
      This SQL select helper

    • orderBy

      public SQLSelect orderBy(String column, Order order)
      Init a new Order By clause using the provided values.
      Parameters:
      column - Column to target
      order - Order to target
      Returns:
      This SQL select helper

    • andOrderBy

      public SQLSelect andOrderBy(String column, Order order)
      Chain another Order By condition using the provided values.
      Parameters:
      column - Column to target
      order - Order to target
      Returns:
      This SQL select helper

    • findUnique

      public M findUnique() throws DatabaseException
      Execute the built query and retrieve a unique, directly usable model instance.
      Returns:
      Model instance
      Throws:
      DatabaseException - Database error

    • findList

      public List<M> findList() throws DatabaseException
      Execute the built query and retrieve a list of model instances.
      Returns:
      Model instances
      Throws:
      DatabaseException - Database error

    • hasAtLeastOne

      public boolean hasAtLeastOne() throws DatabaseException
      Execute the built query and return if there is at least one result.
      Returns:
      true if has at least one result
      Throws:
      DatabaseException - Database error

    • findRaw

      public ResultSet findRaw() throws DatabaseException
      Execute the built query and return a raw ResultSet
      Returns:
      ResultSet
      Throws:
      DatabaseException - Database error

    • toString

      public String toString()
      Description copied from interface: SQLRequest
      Generate the SQL query for this request helper.
      Specified by:
      toString in interface SQLRequest
      Overrides:
      toString in class Object
      Returns:
      Generated SQL query