Skip to end of metadata
Go to start of metadata

Introduction

Useful classes:


Download of useful objects
		SQLFinder finder = FinderFactory.getSQLFinder();//provides search methods

Basic capabilities

Simple SELECT query
		//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.

Why use scalars

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.

Specifying parameters for the query
		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:

 

Definiowanie zwracanej klasy
		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.
Simple filters
		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.

Comparison operators
		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:

Filter grouping
		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:

Independent building of a query fragment
		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

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

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.

  • No labels