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:

      • createSelect

        public static JdbcSqlQuery createSelect​(String sqlPart,
                                        Object... paramValue)
        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:
        sqlPart - the sql part to append.
        paramValue - the value of parameters included into the given sqlPart.
        Returns:
        the instance of the new sql query.

      • createSelect

        public static JdbcSqlQuery createSelect​(String sqlPart,
                                        Collection<?> paramValue)
        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:
        sqlPart - the sql part to append.
        paramValue - the value of parameters included into the given sqlPart.
        Returns:
        the instance of the new sql query.

      • createCountFor

        public static JdbcSqlQuery createCountFor​(String tableName)
        Creates a new instance of the SQL query to count the items that are in the specified table.
        Parameters:
        tableName - the table name aimed by the count.
        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.

      • createInsertFor

        public static JdbcSqlQuery createInsertFor​(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.

      • createUpdateFor

        public static JdbcSqlQuery createUpdateFor​(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.

      • createDeleteFor

        public static JdbcSqlQuery createDeleteFor​(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.

      • createDropFor

        public static JdbcSqlQuery createDropFor​(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 sqlPart)
        Orders the result of the query by the specified statement. If the statement isn't defined, then no ordering will be done.
        Parameters:
        sqlPart - the SQL part that contains the statement over which the result of the query should be ordered.
        Returns:
        the instance of JdbcSqlQuery that represents the SQL query.

      • groupBy

        public JdbcSqlQuery groupBy​(String sqlPart)
        Group the result of the query by the specified columns. If the statement isn't defined, then no group by will be done.
        Parameters:
        sqlPart - the SQL part that contains the statement over which the result of the query 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.

      • addInsertParam

        public JdbcSqlQuery addInsertParam​(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.

      • addUpdateParam

        public JdbcSqlQuery addUpdateParam​(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.

      • addSaveParam

        public JdbcSqlQuery addSaveParam​(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.