View Source

Wstęp

Przydatne klasy:

SQLFinder - odpowiada za wykonywanie zapytań
SQLBuilder - pozwala definiować zapytanie
SQLQueryBuilder - udostępnia statyczne metody do generowania fragmentów zapytań SQL
FilterOperator - typ wyliczeniowy z dostępnymi operatorami warunkowymi
LogicOperator - typ wyliczeniowy z dostępnymi operatorami logicznymi
SimpleSQLFilter - definiuje prosty filtr
GroupSQLFilter - definiuje grupę filtrów
Sorter - definiuje sortowanie
SortDirection - typ wyliczeniowy z dostępnymi kierunkami sortowania
CountedResult<?> - przechowuje dane, wraz z ogólną liczbą wyników.

		SQLFinder finder = FinderFactory.getSQLFinder();//udostępnia metody wyszukujące

Podstawowe możliwości

		//proste zapytanie
		SQLBuilder builder =new SQLBuilder();
        builder.setQuery( "select * from testtable" );
        builder.addScalar( "boolcol", StandardBasicTypes.BOOLEAN );
        builder.addScalar( "textcol", StandardBasicTypes.STRING );
 		builder.addScalar( "datecol", StandardBasicTypes.DATE );
        builder.addScalar( "intcol", StandardBasicTypes.INTEGER );
        builder.addScalar( "doublecol", StandardBasicTypes.DOUBLE );

		List<Map<String,Object>> data=finder.find( builder );//wykonanie zapytania

W powyższym przykładzie w wyniku otrzymamy wartości tylko kolumn, dla których zdefiniowaliśmy scalar'y nawet jeżeli tabela testtable posiada więcej kolumn. Rekord jest reprezentowany jako mapa, w której kluczem jest nazwa kolumn(lub alias jeżeli został zdefiniowany). Podanie scalar'ów jest opcjonalne, lecz zalecane.

Scalar'y definiują do jakiego typu języka Java należy przekonwertować wartości z bazy danych. Jeżeli nie podamy scalar'ów system spróbuje sam wybrać odpowiedni typ. Jednak z uwagi na dużą różnorodność typów bazodanowych(w zależności od producenta SZBD) system nie zawsze jest w stanie dopasować typ bazodanowy do typu Java. W takim wypadku zostaje rzucony wyjątek. Nawet jeżeli system pomyślnie znajdzie odpowiedni typ to nie wiemy jaki konkretnie jest to typ. Np. jeżeli zdefiniujemy zapytanie z użyciem count( * ), lub z kolumną numeryczną i spróbujemy rzutować wartość na Long to możemy dostać wyjątek, ponieważ system dopasuje typ BigInteger, BigDecimal lub jeszcze inny w zależności od silnika bazy danych.

		builder.setQuery( "select * from testtable where textcol=:textcol" );
        //... definicja scalarów
        builder.setParameter( "textcol", "text3" );//ustawienie pojedynczego parametru
		Map<String,Object> parameters=new HashMap<String,Object>();//alternatywnie możemy podać mapę ze wszystkimi parametrami
		parameters.put("textcol","text3");
        builder.setParameters( parameters );//usunie wcześniej podane parametry i wstawi nowe

Przy podawaniu parametrów ważne jest, aby ich typy zgadzały się z typami kolumn. Np. nie możemy podać "true" jako String, gdy kolumna jest typu boolean.

Definiowanie zwracanego typu

Dodawanie do każdego zapytania scalar'ów może być czasem uciążliwe, a odnoszenie się do wartości rekordów poprzez ich nazwę może powodować błędy wynikające np. z literówek. Dlatego innym podejściem jest utworzenie klasy, która będzie przechowywała zwracane z bazy danych informacje. Przejdźmy do przykładu:

		builder.setQuery( "select textcol,boolcol as wlasnanazwa from testtable" );
        List<TestDto> data = finder.find( builder, TestDto.class );//wykonanie zapytania

Aby, przedstawiony kod zadziałał musimy oczywiście utworzyć klasę TestDto(nazwa może być inna ). Pola klasy powinny odpowiadać aliasom kolumn. W tym przypadku textcol i wlasnanazwa. Wielkość znaków nie ma znaczenia, jednak konieczne jest trzymanie się standardu JavaBean(gettery i settery). Dzięki temu otrzymujemy listę obiektów z polami uzupełnionymi przez wartości z bazy danych. Nie musimy się również przejmować typami kolumn, ponieważ zostaną automatycznie rzutowane na typy odpowiednich pól.

Filtrowanie

API umożliwia również dodatkowe funkcje tj. budowanie klauzuli where, order by, oraz ograniczanie zbioru wynikowego(offset i limit). Zacznijmy od filtrowania. Klasa SQLBuilder pozwala zbudować filtry za pomocą klas:

SimpleSQLFilter - prosty filtr, opisuje nazwę kolumny, operator porównania i zawiera wartość filtru.
GroupSQLFilter - pozwala grupować proste filtry, lub inne grupy filtrów. Warunki w grupie filtrów mogą być łączone operatorem logicznym OR, lub AND.

		builder.setQuery( "select textcol,boolcol from testtable " );
        builder.addFilter(new SimpleSQLFilter( "textcol", "text3" ));
        builder.addFilter(new SimpleSQLFilter( "boolcol", true ));
        List<TestDto> data = finder.find( builder, TestDto.class );//wykonanie zapytania

W przykładzie zdefiniowaliśmy dwa proste warunki. Domyślnie operatorem porównania jest znak równości, a operatorem logicznym, który łączy warunki jest AND. Oczywiście możemy zmienić domyślne ustawienia.

		builder.setQuery( "select textcol,boolcol from testtable " );
        builder.addFilter(new SimpleSQLFilter( "textcol", Arrays.asList("text3","text4"),FilterOperator.IN ));
        builder.addFilter(new SimpleSQLFilter( "boolcol", true,FilterOperator.NOTEQ ));// różny od 
		builder.addFilter(new SimpleSQLFilter( "doublecol", 5.0,FilterOperator.GT ));// większy od
        builder.addFilter(new SimpleSQLFilter( "intcol", new Integer[]{1,10},FilterOperator.BETWEEN ));//dla operator BETWEEN należy podać tablicę dwuelementową
        List<TestDto> data = finder.find( builder, TestDto.class );//wykonanie zapytania

Dostępne operatory są opisane w dokumentacji FilterOperator. Przejdźmy do grupowania filtrów:

		builder.setQuery( "select textcol,boolcol from testtable " );
        
        GroupSQLFilter g1 = new GroupSQLFilter(LogicOperator.OR );//główna grupa
        GroupSQLFilter g2 = new GroupSQLFilter(LogicOperator.AND);//podgrupa 1
        GroupSQLFilter g3 = new GroupSQLFilter(LogicOperator.AND);//podgrupa 2
        
		//grupa g1 łączy g2 i g3 operatorem OR
        g1.addFilter( g2 );
        g1.addFilter( g3 );
 
		//warunki pierwszej podgrupy połączone operatorem AND
        g2.addFilter( new SimpleSQLFilter( "intcol", 5, FilterOperator.GT ) );
        g2.addFilter( new SimpleSQLFilter( "intcol", 7, FilterOperator.LT ) );
        
		//warunki drugiej podgrupy połączone operatorem AND
        g3.addFilter( new SimpleSQLFilter( "textcol", Arrays.asList( "text3", "text4" ), FilterOperator.IN ) );
        g3.addFilter( new SimpleSQLFilter( "boolcol", true, FilterOperator.NOTEQ ) );
	
		builder.addFilter( g1 );//dodajemy stworzoną grupę filtów do zapytania
        List<TestDto> data = finder.find( builder, TestDto.class );// wykonanie zapytania

Powyższy kod wygeneruje następujące zapytanie:

select textcol,boolcol from testtable where ( ( intcol > :intcol_0 and intcol < :intcol_1 ) or ( textcol in (:textcol_2) and boolcol != :boolcol_3 ) )

Jak widzimy, możliwe jest zagnieżdżanie grup w innych grupach co pozwala na tworzenie drzewa warunków. Należy pamiętać, że gdy zdefiniujemy filtry, zapytanie, które podaliśmy poprzez setQuery ulegnie zmianie. Doklejona zostanie klauzula WHERE, wraz z warunkami. Dlatego nie możemy w setQuery podać zapytania z już istniejącą klauzulą WHERE. Jeżeli jednak pojawi się taka potrzeba możemy niezależnie zbudować fragment zapytania dotyczący warunków, w następujący sposób:

		GroupSQLFilter g1 = new GroupSQLFilter( LogicOperator.OR );
        //...definicja filtrów(patrz 'Grupowanie filtrów')
 		g2.addFilter( new SimpleSQLFilter( "intcol", 5, FilterOperator.GT ) );
        g2.addFilter( new SimpleSQLFilter( "intcol", 7, FilterOperator.LT ) );

        List<SQLFilter> filters=new ArrayList<SQLFilter>();
        filters.add( g1 );
		//builder.addFilter( g1 );//tym razem nie ustawiamy filtrów!
		builder.setParameters( filters );//ale musimy podać parametry użyte w filtrach!
        String where=SQLQueryBuilder.buildWhere( filters );//fragment zapytania zawierający warunki(bez słowa WHERE)
        builder.setQuery( "select textcol,boolcol from testtable where "+where ); 
        List<TestDto> data = finder.find( builder, TestDto.class );// wykonanie zapytania

Efekt będzie dokładnie taki sam jak w przykładzie poprzednim. Należy zwrócić uwagę na parametry użyte w filtrach. Spójrzmy ponownie na zapytanie SQL wygenerowwane w przykładzie:

select textcol,boolcol from testtable where ( ( intcol > :intcol_0 and intcol < :intcol_1 ) or ( textcol in (:textcol_2) and boolcol != :boolcol_3 ) )

Parametry są poprzedzone są znakiem dwukropka. Widzimy, że nazwy parametrów różnią się od nazw podanych w definicji filtrów. Dzieje się tak dlatego, że podczas budowania zapytania parametry są numerowane. Celem numerowania jest umożliwienie definiowania wielu warunków dla jednej kolumny. Gdybyśmy nie wprowadzili numerowania zapytanie wyglądało by tak:

select textcol,boolcol from testtable where ( ( intcol > :intcol and intcol < :intcol ) or ( textcol in (:textcol) and boolcol != :boolcol ) )

Problem pojawiłby się przy kolumnie intcol, której dotyczą dwa różne warunki. Bez numeracji parametr :intcol przyjmie wartość 5(podany jako pierwszy) w dwóch miejscach co spowoduje, że nie otrzymamy żadnych wyników. Z tego powody zaleca się korzystanie z metody builder.setParameters(List<SQLFilter> filters) do ustawienia wartości filtrów.

Sortowanie

		builder.addSorter( new Sorter( "intcol", SortDirection.ASC ) );
        builder.addSorter( new Sorter( "textcol", SortDirection.DESC ) );
        builder.setQuery( "select textcol,boolcol,intcol from testtable " );
		List<TestDto> data = finder.find( builder, TestDto.class );// wykonanie zapytania
 
		//niezależne budowanie klauzuli ORDER BY
		List<Sorter> sorters=new ArrayList<Sorter>();
        sorters.add( new Sorter( "intcol", SortDirection.ASC ) );
        sorters.add( new Sorter( "textcol", SortDirection.DESC ) );
        String order=SQLQueryBuilder.buildOrder( sorters );
		//wygeneruje: intcol ASC, textcol DESC

Paging

		CountedResult<Map<String,Object>> mapResult = finder.find( builder, 0, 5 );//
        CountedResult<TestDto> classResult = finder.find( builder, TestDto.class, 0, 5 );
        
        Long totalMap=mapResult.getTotal();
        Long total=classResult.getTotal();
        
        List<Map<String,Object>> mapData=mapResult.getData();
        List<TestDto> classData=classResult.getData();

Przykład prezentuje sposób ograniczenia ilości zwracanych wyników. Poza parametrami oznaczającymi odpowiednio przesunięcie w zbiorze wynikowym(offset) i maksymalną ilość wyników(limit), widzimy, że zmienił się typ zwracanej wartości. Klasa CountedResult jest generyczna i służy przechowywaniu danych, wraz z ilością ogólną elementów. W tym przypadku otrzymamy maksymalnie 5 elementów, a wartość total będzie liczbą wszystkich elementów.

Introduction

Useful classes:

SQLFinder - is responsible for executing queries
SQLBuilder - allows you to define a query
SQLQueryBuilder - provides static methods to generate SQL query fragments
FilterOperator - an enumeration type with available conditional operators
LogicOperator - an enumeration type with available logical operators
SimpleSQLFilter - defines a simple filter
GroupSQLFilter - defines a group of filters
Sorter - defines a sort
SortDirection - defines an enumeration type with available sort directions
CountedResult<?> - stores the data, along with the total number of results.

		SQLFinder finder = FinderFactory.getSQLFinder();//provides search methods

Basic capabilities

		//proste zapytanie
		SQLBuilder builder =new SQLBuilder();
        builder.setQuery( "select * from testtable" );
        builder.addScalar( "boolcol", StandardBasicTypes.BOOLEAN );
        builder.addScalar( "textcol", StandardBasicTypes.STRING );
 		builder.addScalar( "datecol", StandardBasicTypes.DATE );
        builder.addScalar( "intcol", StandardBasicTypes.INTEGER );
        builder.addScalar( "doublecol", StandardBasicTypes.DOUBLE );

		List<Map<String,Object>> data=finder.find( builder );//wykonanie zapytania

In the above example, the result will be the values of only the columns for which we have defined scalars even if the testtable has more columns. The record is represented as a map, in which the key is the name of the columns(or alias if defined). Specifying scalars is optional, but recommended.

Scalars define what Java type to convert values from the database to. If you do not specify scalars, the system will try to select the appropriate type on its own. However, due to the wide variety of database types(depending on the SZBD manufacturer), the system is not always able to match the database type to the Java type. In such a case, an exception is raised. Even if the system successfully finds the right type, we do not know what specific type it is. For example, if we define a query using count( * ), or with a numeric column, and try to render the value to Long then we may get an exception because the system will match the type BigInteger, BigDecimal or yet another depending on the database engine.

		builder.setQuery( "select * from testtable where textcol=:textcol" );
        //... scalars definition
        builder.setParameter( "textcol", "text3" );//set a single parameter
		Map<String,Object> parameters=new HashMap<String,Object>();//alternatively, we can provide a map with all parameters
		parameters.put("textcol","text3");
        builder.setParameters( parameters );//remove previously specified parameters and insert new ones

When specifying parameters, it is important that their types match the column types. E.g., we cannot specify "true" as String when the column is of type boolean.

Defining the returned type

Adding scalars to each query can sometimes be troublesome, and referring to record values by their name can cause errors due to typos, for example. Therefore, another approach is to create a class that will store the information returned from the database. Let's turn to an example:

		builder.setQuery( "select textcol,boolcol as wlasnanazwa from testtable" );
        List<TestDto> data = finder.find( builder, TestDto.class );//execution of the queries

So that, the presented code works you need to create a class TestDto (the name can be different). The fields of the class should correspond to the aliases of the columns. In this case, textcol and nazwawlasna. The character size does not matter, but it is necessary to stick to the JavaBean standard( getters and setters). This will give you a list of objects with fields populated by values from the database. You don't have to worry about column types, as they will be automatically cast to the types of the corresponding fields.

Filtering

The API also allows additional functions, i.e. building a where clause, order by, and limiting the resulting set(offset and limit). Let's start with filtering. SQLBuilder allows you to build filters using classes:

SimpleSQLFilter - a simple filter, describes the name of the column, the comparison operator and contains the value of the filter.
GroupSQLFilter - allows you to group simple filters, or other groups of filters. Conditions in a group of filters can be combined with the logical operator OR, or AND.

		builder.setQuery( "select textcol,boolcol from testtable " );
        builder.addFilter(new SimpleSQLFilter( "textcol", "text3" ));
        builder.addFilter(new SimpleSQLFilter( "boolcol", true ));
        List<TestDto> data = finder.find( builder, TestDto.class );//execution of the query

In the example, we defined two simple conditions. By default, the comparison operator is the equal sign, and the boolean operator that connects the conditions is AND. Of course, we can change the defaults.

		builder.setQuery( "select textcol,boolcol from testtable " );
        builder.addFilter(new SimpleSQLFilter( "textcol", Arrays.asList("text3","text4"),FilterOperator.IN ));
        builder.addFilter(new SimpleSQLFilter( "boolcol", true,FilterOperator.NOTEQ ));// different from
		builder.addFilter(new SimpleSQLFilter( "doublecol", 5.0,FilterOperator.GT ));// greater than
        builder.addFilter(new SimpleSQLFilter( "intcol", new Integer[]{1,10},FilterOperator.BETWEEN ));//for BETWEEN operator, provide a two-element array
        List<TestDto> data = finder.find( builder, TestDto.class );//execution of the query

DThe available operators are described in the FilterOperator. Let's move on to grouping filters:

		builder.setQuery( "select textcol,boolcol from testtable " );
        
        GroupSQLFilter g1 = new GroupSQLFilter(LogicOperator.OR );//main group
        GroupSQLFilter g2 = new GroupSQLFilter(LogicOperator.AND);//subgroup 1
        GroupSQLFilter g3 = new GroupSQLFilter(LogicOperator.AND);//subgroup 2
        
		//group g1 combines g2 and g3 with OR operator
        g1.addFilter( g2 );
        g1.addFilter( g3 );
 
		//conditions of the first subgroup connected by the AND operator
        g2.addFilter( new SimpleSQLFilter( "intcol", 5, FilterOperator.GT ) );
        g2.addFilter( new SimpleSQLFilter( "intcol", 7, FilterOperator.LT ) );
        
		//conditions of the second subgroup connected by the AND operator
        g3.addFilter( new SimpleSQLFilter( "textcol", Arrays.asList( "text3", "text4" ), FilterOperator.IN ) );
        g3.addFilter( new SimpleSQLFilter( "boolcol", true, FilterOperator.NOTEQ ) );
	
		builder.addFilter( g1 );//add the created filter group to the query
        List<TestDto> data = finder.find( builder, TestDto.class );// execution of the query

The above code will generate the following query:

select textcol,boolcol from testtable where ( ( intcol > :intcol_0 and intcol < :intcol_1 ) or ( textcol in (:textcol_2) and boolcol != :boolcol_3 ) )

As you can see, it is possible to nest groups in other groups which allows you to create a tree of conditions. Note that when we define filters, the query we specified via setQuery will change.

The WHERE clause will be pasted, along with the conditions. Therefore, you cannot specify a query with an already existing WHERE clause in setQuery. However, if the need arises, we can independently build a query fragment with conditions, as follows:

		GroupSQLFilter g1 = new GroupSQLFilter( LogicOperator.OR );
        //...definicja filtrów(patrz 'Grupowanie filtrów')
 		g2.addFilter( new SimpleSQLFilter( "intcol", 5, FilterOperator.GT ) );
        g2.addFilter( new SimpleSQLFilter( "intcol", 7, FilterOperator.LT ) );

        List<SQLFilter> filters=new ArrayList<SQLFilter>();
        filters.add( g1 );
		//builder.addFilter( g1 );//this time you don't set filters!
		builder.setParameters( filters );//but you must specify the parameters used in the filters!
        String where=SQLQueryBuilder.buildWhere( filters );//query fragment containing conditions(without the word WHERE)
        builder.setQuery( "select textcol,boolcol from testtable where "+where ); 
        List<TestDto> data = finder.find( builder, TestDto.class );// execution of the query

The result will be exactly the same as in the previous example. Note the parameters used in the filters. Let's look again at the SQL query generated in the example:

select textcol,boolcol from testtable where ( ( intcol > :intcol_0 and intcol < :intcol_1 ) or ( textcol in (:textcol_2) and boolcol != :boolcol_3 ) )

Parameters are preceded by a colon sign. Notice that the names of the parameters differ from the names given in the filter definition. This is because the parameters are numbered when building the query. The purpose of numbering is to allow multiple conditions to be defined for a single column. If we had not introduced numbering the query would look like this:

select textcol,boolcol from testtable where ( ( intcol > :intcol and intcol < :intcol ) or ( textcol in (:textcol) and boolcol != :boolcol ) )

The problem would arise with the intcol column, which is affected by two different conditions. Without numbering, the :intcol parameter will take the value of 5(given first) in two places which will result in no results. For this reason, it is recommended to use the builder.setParameters(List<SQLFilter> filters) method to set the filter values.

Sorting

		builder.addSorter( new Sorter( "intcol", SortDirection.ASC ) );
        builder.addSorter( new Sorter( "textcol", SortDirection.DESC ) );
        builder.setQuery( "select textcol,boolcol,intcol from testtable " );
		List<TestDto> data = finder.find( builder, TestDto.class );// execution of the query
 
		//niezależne budowanie klauzuli ORDER BY
		List<Sorter> sorters=new ArrayList<Sorter>();
        sorters.add( new Sorter( "intcol", SortDirection.ASC ) );
        sorters.add( new Sorter( "textcol", SortDirection.DESC ) );
        String order=SQLQueryBuilder.buildOrder( sorters );
		//wygeneruje: intcol ASC, textcol DESC

Paging

		CountedResult<Map<String,Object>> mapResult = finder.find( builder, 0, 5 );//
        CountedResult<TestDto> classResult = finder.find( builder, TestDto.class, 0, 5 );
        
        Long totalMap=mapResult.getTotal();
        Long total=classResult.getTotal();
        
        List<Map<String,Object>> mapData=mapResult.getData();
        List<TestDto> classData=classResult.getData();

The example shows how to limit the number of returned results. In addition to the parameters indicating the offset in the result set(offset) and the maximum number of results(limit), we see that the type of the returned value has changed, accordingly. The CountedResult class is generic and is used to store data, along with the total number of elements. In this case, we will get a maximum of 5 elements, and the value of total will be the number of all elements.