Package org.silverpeas.core.persistence.jdbc.sql

Class JdbcSqlQuery

  • public class JdbcSqlQuery
extends Object
    This class permits to build easily a SQL query with parameters. It permits also to execute directly the query.
    Author:
    Yohann Chastagnier

    • Method Detail

      • isSqlDefined

        public static boolean isSqlDefined​(String sqlValue)
        Indicates if the specified value is defined in point of view of SQL.
        Parameters:
        sqlValue - the value to verify.
        Returns:
        true if defined, false otherwise.

      • unique

        public static <E> E unique​(List<E> entities)
        Gets from a entity list the unique entity expected.
        Type Parameters:
        E - the type of the entities.
        Parameters:
        entities - the entity list.
        Returns:
        the unique entity result.
        Throws:
        IllegalArgumentException - if it exists more than one entity in the specified list.

      • create

        public static JdbcSqlQuery create​(String sqlPart,
                                  Object... paramValue)
        Creates a new instance of the JDBC SQL query initialized with the given sql part.
        Parameters:
        sqlPart - the sql part to append.
        paramValue - the value of parameters included into the given sqlPart.
        Returns:
        the instance of the new sql query.

      • create

        public static JdbcSqlQuery create​(String sqlPart,
                                  Collection<?> paramValue)
        Creates a new instance of the JDBC SQL query initialized with the given sql part.
        Parameters:
        sqlPart - the sql part to append.
        paramValue - the value of parameters included into the given sqlPart.
        Returns:

      • select

        public static JdbcSqlQuery select​(String columns)
        Creates a new instance of the JDBC SQL query to select some fields of the items to find according to the specified SQL part.
        Parameters:
        columns - the fields to select in the SQL query.
        Returns:
        the instance of the new sql query.

      • countAll

        public static JdbcSqlQuery countAll()
        Creates a new instance of the SQL query to count all the items that are in the specified table.
        Returns:
        the instance of the new sql query.

      • countAllAs

        public static JdbcSqlQuery countAllAs​(@Nonnull
                                      String counterName)
        Creates a new instance of the SQL query to count all the items under the given name of counter.
        Parameters:
        counterName - the name of the counter containing the counted number of items.
        Returns:
        the instance of the new sql query.

      • createTable

        public static JdbcSqlQuery createTable​(String tableName)
        Creates a new instance of the SQL query to create the specified table.
        Parameters:
        tableName - the table name aimed by the insert.
        Returns:
        the instance of the new sql query.

      • insertInto

        public static JdbcSqlQuery insertInto​(String tableName)
        Creates a new instance of the SQL query to insert one or more items in the specified table.
        Parameters:
        tableName - the table name aimed by the insert.
        Returns:
        the instance of the new sql query.

      • update

        public static JdbcSqlQuery update​(String tableName)
        Creates a new instance of the SQL query to update some items in the specified table.
        Parameters:
        tableName - the table name aimed by the update.
        Returns:
        the instance of the new sql query.

      • deleteFrom

        public static JdbcSqlQuery deleteFrom​(String tableName)
        Creates a new instance of the SQL query to delete some items in the specified table.
        Parameters:
        tableName - the table name aimed by the delete.
        Returns:
        the instance of the new sql query.

      • dropTable

        public static JdbcSqlQuery dropTable​(String tableName)
        Creates a new instance of the SQL query to drop the specified table.
        Parameters:
        tableName - the table name aimed by the drop.
        Returns:
        the instance of the new sql query.

      • getSqlQuery

        public String getSqlQuery()
        Gets the built SQL query.
        Returns:
        the SQL query.

      • getParameters

        public Collection<Object> getParameters()
        Gets the parameters to apply to the SQL query.
        Returns:
        the parameters to apply to the SQL query.

      • configure

        public JdbcSqlQuery configure​(Consumer<JdbcSqlQuery.Configuration> config)
        Calling this method to configure some parameters around execution, result, etc.
        Parameters:
        config - the configuration instance.
        Returns:
        the instance of JdbcSqlQuery that represents the SQL query.

      • addField

        public JdbcSqlQuery addField​(String fieldName,
                             String definition)
        Centralization in order to populate the prepare statement parameters (FOR TABLE CREATION ONLY).
        Parameters:
        fieldName - the name of the field to define.
        definition - the definition of the field.
        Returns:
        the instance of JdbcSqlQuery that represents the SQL query.

      • union

        public JdbcSqlQuery union()
        Centralization in order to populate the prepare statement parameters.
        Returns:
        the instance of JdbcSqlQuery that represents the SQL query.

      • join

        public JdbcSqlQuery join​(String sqlPart)
        Centralization in order to populate the prepare statement parameters.
        Parameters:
        sqlPart - the SQL part that contains the parameter.
        Returns:
        the instance of JdbcSqlQuery that represents the SQL query.

      • outerJoin

        public JdbcSqlQuery outerJoin​(String sqlPart)
        Centralization in order to populate the prepare statement parameters.
        Parameters:
        sqlPart - the SQL part that contains the parameter.
        Returns:
        the instance of JdbcSqlQuery that represents the SQL query.

      • on

        public JdbcSqlQuery on​(String sqlPart,
                       Object... paramValue)
        Centralization in order to populate the prepare statement parameters.
        Parameters:
        sqlPart - the SQL part that contains the parameter.
        paramValue - the value of parameters included into the given sqlPart.
        Returns:
        the instance of JdbcSqlQuery that represents the SQL query.

      • where

        public JdbcSqlQuery where​(String sqlPart,
                          Object... paramValue)
        Centralization in order to populate the prepare statement parameters.
        Parameters:
        sqlPart - the SQL part that contains the parameter.
        paramValue - the value of parameters included into the given sqlPart.
        Returns:
        the instance of JdbcSqlQuery that represents the SQL query.

      • where

        public JdbcSqlQuery where​(String sqlPart,
                          Collection<?> paramValues)
        Centralization in order to populate the prepare statement parameters.
        Parameters:
        sqlPart - the SQL part that contains the parameter.
        paramValues - the value of parameters included into the given sqlPart.
        Returns:
        the instance of JdbcSqlQuery that represents the SQL query.

      • from

        public JdbcSqlQuery from​(String... tableNames)
        The query is about the concerned SQL table.
        Parameters:
        tableNames - the name of the table(s) concerned by the query.
        Returns:
        the instance of JdbcSqlQuery that represents the SQL query.

      • and

        public JdbcSqlQuery and​(String sqlPart,
                        Object... paramValue)
        Centralization in order to populate the prepare statement parameters.
        Parameters:
        sqlPart - the SQL part that contains the parameter.
        paramValue - the value of parameters included into the given sqlPart.
        Returns:
        the instance of JdbcSqlQuery that represents the SQL query.

      • and

        public JdbcSqlQuery and​(String sqlPart,
                        Collection<?> paramValues)
        Centralization in order to populate the prepare statement parameters.
        Parameters:
        sqlPart - the SQL part that contains the parameter.
        paramValues - the value of parameters included into the given sqlPart.
        Returns:
        the instance of JdbcSqlQuery that represents the SQL query.

      • andNotNull

        public JdbcSqlQuery andNotNull​(String parameter)
        The specified parameter, in a conjunction filter, must not be null when requesting the data source.
        Parameters:
        parameter - the parameter that has to be not null.
        Returns:
        the instance of JdbcSqlQuery that represents the SQL query.

      • andNull

        public JdbcSqlQuery andNull​(String parameter)
        The specified parameter, in a conjunction filter, must be null when requesting the data source.
        Parameters:
        parameter - the parameter that has to be not null.
        Returns:
        the instance of JdbcSqlQuery that represents the SQL query.

      • or

        public JdbcSqlQuery or​(String sqlPart,
                       Object... paramValue)
        Centralization in order to populate the prepare statement parameters.
        Parameters:
        sqlPart - the SQL part that contains the parameter.
        paramValue - the value of parameters included into the given sqlPart.
        Returns:
        the instance of JdbcSqlQuery that represents the SQL query.

      • or

        public JdbcSqlQuery or​(String sqlPart,
                       Collection<?> paramValues)
        Centralization in order to populate the prepare statement parameters.
        Parameters:
        sqlPart - the SQL part that contains the parameter.
        paramValues - the value of parameters included into the given sqlPart.
        Returns:
        the instance of JdbcSqlQuery that represents the SQL query.

      • orNotNull

        public JdbcSqlQuery orNotNull​(String parameter)
        The specified parameter, in a disjunction filter, must not be null when requesting the data source.
        Parameters:
        parameter - the parameter that has to be not null.
        Returns:
        the instance of JdbcSqlQuery that represents the SQL query.

      • orNull

        public JdbcSqlQuery orNull​(String parameter)
        The specified parameter, in a conjunction filter, must null when requesting the data source.
        Parameters:
        parameter - the parameter that has to be not null.
        Returns:
        the instance of JdbcSqlQuery that represents the SQL query.

      • orderBy

        public JdbcSqlQuery orderBy​(String... fields)
        Orders the result of the query by the specified columns. If the statement isn't defined, then no ordering will be done.
        Parameters:
        fields - the fields by which the query results should be ordered.
        Returns:
        the instance of JdbcSqlQuery that represents the SQL query.

      • groupBy

        public JdbcSqlQuery groupBy​(String... fields)
        Group the result of the query by the specified columns. If the statement isn't defined, then no group by will be done.
        Parameters:
        fields - the fields by which the query results should be grouped.
        Returns:
        the instance of JdbcSqlQuery that represents the SQL query.

      • limit

        public JdbcSqlQuery limit​(int count)
        Limits the count of result returned by the query. This overrides any previous value of the limit property in the configuration.
        Parameters:
        count - the size of results to return.
        Returns:
        the instance of JdbcSqlQuery that represents the SQL query.

      • offset

        public JdbcSqlQuery offset​(int offset)
        Sets the offset from which each result should be processed by the row processor. The other results returned by the query will be ignored. This overrides any previous value of the offset property in the configuration.
        Parameters:
        offset - the offset from which the row processing has to start.
        Returns:
        the instance of JdbcSqlQuery that represents the SQL query.

      • withPagination

        public JdbcSqlQuery withPagination​(PaginationCriterion pagination)
        Configures the query execution in order to retrieve only items of pagination.
        Be careful to execute a SQL query containing an ORDER BY clause!!!
        Parameters:
        pagination - the pagination criterion to apply.
        Returns:
        the instance of JdbcSqlQuery that represents the SQL query.

      • addSqlPart

        public JdbcSqlQuery addSqlPart​(String sqlPart,
                               Object... paramValue)
        Centralization in order to populate the prepare statement parameters.
        Parameters:
        sqlPart - the SQL part that contains the parameter.
        paramValue - the value of parameters included into the given sqlPart.
        Returns:
        the instance of JdbcSqlQuery that represents the SQL query.

      • in

        public JdbcSqlQuery in​(Collection<?> parameters)
        Centralization in order to build easily a SQL in clause.

        If one element exists into list, an equality is performed instead of a in

        Parameters:
        parameters - the parameters to append to the given SQL query.
        Returns:
        the instance of JdbcSqlQuery that represents the SQL query.

      • in

        public JdbcSqlQuery in​(Object... parameters)
        Centralization in order to build easily a SQL in clause.

        If one element exists into list, an equality is performed instead of a in

        Parameters:
        parameters - the parameters to append to the given SQL query.
        Returns:
        the instance of JdbcSqlQuery that represents the SQL query.

      • notIn

        public JdbcSqlQuery notIn​(Collection<?> parameters)
        Centralization in order to build easily a SQL in clause.

        If one element exists into list, a non equality is performed instead of a not in

        Parameters:
        parameters - the parameters to append to the given SQL query.
        Returns:
        the instance of JdbcSqlQuery that represents the SQL query.

      • notIn

        public JdbcSqlQuery notIn​(Object... parameters)
        Centralization in order to build easily a SQL in clause.

        If one element exists into list, a non equality is performed instead of a not in

        Parameters:
        parameters - the parameters to append to the given SQL query.
        Returns:
        the instance of JdbcSqlQuery that represents the SQL query.

      • withInsertParam

        public JdbcSqlQuery withInsertParam​(String paramName,
                                    Object paramValue)
        Centralization in order to populate the prepare statement parameters for insertion.
        Parameters:
        paramName - the name of the parameter to add into update fields part.
        paramValue - the value of the parameter.
        Returns:
        the instance of JdbcSqlQuery that represents the SQL query.

      • withUpdateParam

        public JdbcSqlQuery withUpdateParam​(String paramName,
                                    Object paramValue)
        Centralization in order to populate the prepare statement parameters for update.
        Parameters:
        paramName - the name of the parameter to add into update fields part.
        paramValue - the value of the parameter.
        Returns:
        the instance of JdbcSqlQuery that represents the SQL query.

      • withSaveParam

        public JdbcSqlQuery withSaveParam​(String paramName,
                                  Object paramValue,
                                  boolean isInsert)
        Centralization in order to populate the prepare statement parameters.
        Parameters:
        paramName - the name of the parameter to add into update fields part.
        paramValue - the value of the parameter.
        isInsert - indicates if the SQL built is an INSERT or an UPDATE one.
        Returns:
        the instance of JdbcSqlQuery that represents the SQL query.

      • executeBySplittingOn

        public static <I,​T> Map<I,​List<T>> executeBySplittingOn​(Collection<I> discriminantData,
                                                                    SplitExecuteProcess<I,​T> process)
                                                             throws SQLException
        Split executor.
        Type Parameters:
        I - the type of list of discriminant data.
        T - the type of the entity into result.
        Parameters:
        discriminantData - a discriminant list of data.
        Returns:
        a mapping between given discriminant identifiers and the corresponding data.
        Throws:
        SQLException - on SQL error.

      • streamBySplittingOn

        public static <I,​T> Stream<T> streamBySplittingOn​(Collection<I> discriminantData,
                                                        SplitListProcess<I,​List<T>> process)
                                                 throws SQLException
        Split executor.
        Type Parameters:
        I - the type of list of discriminant data.
        T - the type of the entity into result.
        Parameters:
        discriminantData - a discriminant list of data.
        Returns:
        a stream between given discriminant identifiers and the corresponding data.
        Throws:
        SQLException - on SQL error.

      • streamBySplittingOn

        public static <I,​T> Stream<T> streamBySplittingOn​(Collection<I> discriminantData,
                                                        SplitListProcess<I,​List<T>> process,
                                                        Function<T,​I> idGetter)
                                                 throws SQLException
        Split executor giving a result sorted exactly like the discriminantData parameter is sorted.
        Type Parameters:
        I - the type of list of discriminant data.
        T - the type of the entity into result.
        Parameters:
        discriminantData - a discriminant list of data.
        idGetter - permits to get the id from T entity in order to sort the result.
        Returns:
        a stream between given discriminant identifiers and the corresponding data.
        Throws:
        SQLException - on SQL error.

      • execute

        public <R> ListSlice<R> execute​(SelectResultRowProcess<R> process)
                         throws SQLException
        Select executor.
        Type Parameters:
        R - the type of the items in the list.
        Parameters:
        process - the process to execute on the ResultSet objects.
        Returns:
        a slice of the list of entities matching the query. The slice is computed from the query configuration JdbcSqlQuery.Configuration.
        Throws:
        SQLException - on SQL error.

      • executeWith

        public <R> ListSlice<R> executeWith​(Connection connection,
                                    SelectResultRowProcess<R> process)
                             throws SQLException
        Select executor.
        Type Parameters:
        R - the type of the items in the list.
        Parameters:
        connection - existing connection.
        process - the process to execute on the ResultSet objects.
        Returns:
        a slice of the list of entities matching the query. The slice is computed from the query configuration JdbcSqlQuery.Configuration.
        Throws:
        SQLException - on SQL error.

      • executeUnique

        public <R> R executeUnique​(SelectResultRowProcess<R> process)
                    throws SQLException
        Select executor.
        Type Parameters:
        R - the type of the entity.
        Parameters:
        process - the process to execute on the ResultSet objects.
        Returns:
        the entity matching the query.
        Throws:
        SQLException - on SQL error.

      • executeUniqueWith

        public <R> R executeUniqueWith​(Connection connection,
                               SelectResultRowProcess<R> process)
                        throws SQLException
        Select executor.
        Type Parameters:
        R - the type of the entity.
        Parameters:
        connection - existing connection.
        process - the process to execute on the ResultSet objects.
        Returns:
        the entity matching the query.
        Throws:
        SQLException - on SQL error.

      • execute

        public long execute()
             throws SQLException
        Modify executor.
        Returns:
        the number of entities that were implied in the modification.
        Throws:
        SQLException - on SQL error.

      • executeWith

        public long executeWith​(Connection connection)
                 throws SQLException
        Modify executor.
        Parameters:
        connection - existing connection.
        Returns:
        the number of entities that were implied in the modification.
        Throws:
        SQLException - on SQL error.