Constructing queries is a simply matter of object composition. To make a SELECT query theSelect-class should be instantiated. The source can either be a table, another
Select-object, or any other object derived from AbstractQuery<AbstractTable>). The constructor takes four arguments, the operations, the input, the conditions and the groupings.
A.4.1
Operations
The operations that can be used are the following:
– Column- Simply returns the column as it is.
– Arithmetic - Numeric operations such as multiplication, addition, etc. Operates over two columns.
– Aggregate- Base class for aggregate operations
• Count - Returns the number of elements in the source, or for each group if groupings are present.
• First- Returns the value of the first element in the source or in the group.
• Last - Returns the value of the last element in the column or group.
• Max- Returns the maximum value of the column or group. Only applicable for types implementing theComparable-interface.
• Min- Returns the minimum value of the column or group. Only applicable for types implementing theComparable-interface.
• Sum- Returns the sum of all the elements in the column or for each grouping.
– Functions
• Round- Rounds the column down to a multiple of the given value.
• Sqrt - Returns the square root of the column or groups.
A.4.2
Conditions
Conditions return a bit vector. They are typically used as the WHERE-part of the query but can also be used as an operation. In this case the resulting column is a bit vector column. The common base class for conditions is theCondition-class.
A.4. Query interface 57
– In- Returns true for elements in the column that are contained within the specified vector.
– Within- Range condition. Returns true for elements that are within the specified range.
– Not- Bit-wisenot of a bit vector column.
A.4.3
Grouping
Grouping is done by simply using operations with the group parameter to the select. The result of the grouping operations will define the key of the result.
A.4.4
Examples
Listing A.7: Example query with a simple condition. // S E L E C T id , s q r t ( p r i c e ) F R O M t a b l e W H E R E p r i c e < 5;
S e l e c t sel = new S e l e c t ( new C o l u m n []{ new C o l u m n ( " id " ) , new S q r t ( " p r i c e " )} , table , new C o n d i t i o n []{ new C o m p a r i s o n ( " p r i c e " . C o m p a r i s o n . T y p e . LT , new A t o m ( new I n t V e c t o r ( 5 ) ) ) } , n u l l ); // E x e c u t e t h e q u e r y A b s t r a c t T a b l e r e s u l t = sel . e x e c u t e ();
Listing A.8: Example query with groupings. // C o u n t h o w m a n y e m p l o y e e s l i v e in e a c h c i t y
S e l e c t sel = new S e l e c t (
new C o l u m n []{ new F i r s t ( " c i t y " ) , new C o u n t ( " c i t y " )} , table ,
null ,
new C o l u m n []{ new C o l u m n ( " c i t y " ) } ) ; // g r o u p by c i t y
Listing A.9: Example query rounding times.
// S e l e c t s t h e p r o d u c t s f r o m t a b l e w i t h t i m e c o l u m n r o u n d e d to e v e n m i n u t e s S e l e c t sel = new S e l e c t ( new C o l u m n []{
new C o l u m n ( " p r o d u c t " ) ,
new R o u n d ( " t i m e " , new A t o m ( new T i m e V e c t o r ( " 0 0 : 0 1 : 0 0 . 0 0 0 " ))) , new C o l u m n ( " p r i c e " )} ,
table , null , n u l l );
Listing A.10: Example query with nested operations. // S E L E C T p r i c e * 2 F R O M t a b l e W H E R E n o t ( p r i c e < 2 2 . 5
// a n d p r i c e > 1 0 . 0 ) ;
S e l e c t sel = new S e l e c t ( new C o l u m n []{ new S q r t ( new A r i t h m e t i c ( " p r i c e " , A r i t h m e t i c . MUL , new A t o m ( new I n t V e c t o r ( 2 ) ) ) ) } , table , new C o n d i t i o n []{ new Not ( new W i t h i n ( new C o l u m n ( " p r i c e " ) ,
new A t o m ( new D o u b l e V e c t o r ( 1 0 . 0 ) ) , new A t o m ( new D o u b l e V e c t o r ( 2 2 . 5 ) ) ))