Getting data from the most common queries is trivial using QueryableDatatypes (QDTs)
QueryableDatatypes are a central concept within DBvolution. All of the DBString, DBNumber, etc classes used in the DBRow examples are QueryableDatatypes
Using these types rather than the standard java types allows more natural query design and value manipulation
For instance to query for a particular item in the database use:
exampleMarque.uidMarque.permittedValues(uidValue);
desiredMarque = database.get(exampleMarque);
The "Permitted" and "Excluded" methods of the QDT object encapsulates the common SQL comparisons
marque.uidMarque.permittedValues(1,2,3,4,5);
marque.uidMarque.permittedRangeInclusive(1,5);
marque.uidMarque.permittedRange(0,6);
marque.uidMarque.permittedValues(listOfValues);
marque.name.permittedValueIgnoreCase("toyota");
marque.name.permittedPattern("toy%");
Every "Permitted" method has an equivalent "Excluded" method that functions as the inverse of the operator. So if you want to get everything EXCEPT the items above use:
marque.uidMarque.excludedValues(1,2,3,4,5);
marque.uidMarque.excludedRangeInclusive(1,5);
marque.uidMarque.excludedRange(0,6);
marque.uidMarque.excludedValues(listOfValues);
marque.name.excludedValueIgnoreCase("toyota");
marque.name.excludedPattern("toy%");
There is not an exact match between SQL operators and QDT methods because DBvolution switches to the appropriate SQL operator depending on the values you supply. This simplifies your query to looking for a set of values, a range, or a pattern
DBvolution | SQL |
permittedValues | =, IN |
permittedValuesIgnoreCase | =, IN |
permittedPattern | LIKE |
permittedRange | >, <, BETWEEN |
permittedRangeInclusive | >=, <= |
PermittedRange allows open-ended ranges by using a null. For example if you want to find all Marques with a UID greater than 1 use:
marque.uidMarque.permittedRangeInclusive(1,null);
QDTs are an integral part of the DBvolution system and make query construction trivial. They also support sorting operation, transforms, and creating new entries in the database
All supported datatypes are supported on all supported databases, even when the database type is not natively supported.
QDT | Java Type |
Database Type |
|
---|---|---|---|
DBInteger | Long/Integer | INTEGER | |
Recommendation: use Long, database integers are often too large for Integer | |||
DBNumber | Double | NUMBER | |
DBString | String | NVARCHAR(1000), VARCHAR(1000) |
|
Stores normal amounts of character data. Use DBLargeText, if you need more than 1000 characters. | |||
DBLargeText | String | CLOB | |
Stores massive amounts of character data in the largest character type. May be restricted to one column permitted per table. | |||
DBBoolean | Boolean | BOOLEAN | |
DBBooleanArray | Boolean[] | ARRAY | |
DBLocalDate | LocalDateTime | DATETIME, TIMESTAMP |
|
Stores local dates without time information. It is recommended that you use DBLocalDateTime or DBInstant instead as date without times are not as simple as you might think | |||
DBLocalDateTime | LocalDateTime | DATETIME, TIMESTAMP |
|
DBInstant | Instant | UTCTIMESTAMP, TIMESTAMP WITH TIME ZONE | |
DBDateRepeat | org.joda.time.Period | - | |
stores the results of subtracting 2 dates, useful for defining frequencies of repeating events, see DBDuration | |||
DBDuration | Duration | INTERVAL DAY(18) TO SECOND(9) |
|
DBvolution provides limited support for INTERVAL values as they are porrly supported by the industry and have no standard implementation. | |||
DBIntegerEnum | Enum<Long> | INTEGER | |
DBvolution supports hard-coded values that are stored as integers but represent a java enumeration. | |||
DBJavaObject<O> | O | JAVA_OBJECT | |
Stores and retrieves object of type O. DBvolution supports rapid storage and retrieval of objects of a defined class. For instance a DBJavaObject<HashMap> can easily store and retrieve HashMap objects. | |||
DBLargeBinary | byte[] | BLOB | |
The generic storage type for files and other binary items | |||
DBStringTrimmed | String | VARCHAR | |
DBvolution supports automatic trimming of column values for String columns. | |||
DBStringEnum | Enum<Long> | INTEGER | |
DBvolution supports hard-coded values that are stored as integers but represent a java enumeration. |
QDT | Java Type |
Database Type |
|
---|---|---|---|
DBPasswordHash | String | VARCHAR(1000) | |
A one way encrypted hash of the value. DBvolution supports automatically and securely hashing a password so a value can be safely stored to authenticate logins. No passwords, keys, or plain text are ever stored during processing. DBvolution cannot decrypt the values. | |||
DBEncryptedText | Encrypted | CLOB | |
A large amount of encrypted characters. DBvolution supports automatically encrypting before storing and decrypting after retrieval. No passwords, keys, or plain text are ever stored during processing. DBvolution cannot decrypt the values without the secrets supplied during encryption. | |||
DBStatistics | Various | - | |
DBvolution supports automatically generating simple statistics on a column by adding a DBStatistics field. |
Spatial types are implemented using the Java Topological Suite (JTS) from Vivid Solutions. SRIDs are not currently supported.
QDT | Java Type |
Database Type |
|
---|---|---|---|
DBLine2D | LineString | LINESTRING | |
DBLineSegment2D | LineSegment | LINESTRING | |
DBPoint2D | Point | POINT | |
DBPolygon2D | Polygon | POLYGON |