Edit on GitHub3.3.2. DBMS Types
The type of the DBMS used in the application is defined by the cuba.dbmsType and (optionally) cuba.dbmsVersion application properties. These properties affect various platform mechanisms depending on the database type.
The platform supports the following types of DBMS "out of the box":
| cuba.dbmsType | cuba.dbmsVersion | JDBC driver | |
|---|---|---|---|
HSQLDB |
hsql |
org.hsqldb.jdbc.JDBCDriver |
|
PostgreSQL 8.4+ |
postgres |
org.postgresql.Driver |
|
Microsoft SQL Server 2005 |
mssql |
2005 |
net.sourceforge.jtds.jdbc.Driver |
Microsoft SQL Server 2008 |
mssql |
com.microsoft.sqlserver.jdbc.SQLServerDriver |
|
Microsoft SQL Server 2012+ |
mssql |
2012 |
com.microsoft.sqlserver.jdbc.SQLServerDriver |
Oracle Database 11g+ |
oracle |
oracle.jdbc.OracleDriver |
|
MySQL 5.6+ |
mysql |
com.mysql.jdbc.Driver |
|
MariaDB 5.5+ |
mysql |
org.mariadb.jdbc.Driver |
The table below describes the recommended mapping of data types between entity attributes in Java and table columns in different DBMS. CUBA Studio automatically chooses these types when generates scripts to create and update the database. The operation of all platform mechanisms is guaranteed when you use these types.
| Java | HSQL | PostgreSQL | MS SQL Server | Oracle | MySQL | MariaDB |
|---|---|---|---|---|---|---|
UUID |
varchar(36) |
uuid |
uniqueidentifier |
varchar2(32) |
varchar(32) |
varchar(32) |
Date |
timestamp |
timestamp |
datetime |
timestamp |
datetime(3) |
datetime(3) |
java.sql.Date |
timestamp |
date |
datetime |
date |
date |
date |
java.sql.Time |
timestamp |
time |
datetime |
timestamp |
time(3) |
time(3) |
BigDecimal |
decimal(p, s) |
decimal(p, s) |
decimal(p, s) |
number(p, s) |
decimal(p, s) |
decimal(p, s) |
Double |
double precision |
double precision |
double precision |
float |
double precision |
double precision |
Long |
bigint |
bigint |
bigint |
number(19) |
bigint |
bigint |
Integer |
integer |
integer |
integer |
integer |
integer |
integer |
Boolean |
boolean |
boolean |
tinyint |
char(1) |
boolean |
boolean |
String (limited) |
varchar(n) |
varchar(n) |
varchar(n) |
varchar2(n) |
varchar(n) |
varchar(n) |
String (unlimited) |
longvarchar |
text |
varchar(max) |
clob |
longtext |
longtext |
byte[] |
longvarbinary |
bytea |
image |
blob |
longblob |
longblob |
Usually, all the work to convert the data between the database and the Java code is performed by the ORM layer in conjunction with the appropriate JDBC driver. It means that no manual conversion is required when working with the data using DataManager, EntityManager and JPQL queries; you should simply use Java types listed in the left column of the table.
When using native SQL through EntityManager.createNativeQuery() or through QueryRunner, some types in the Java code will be different from those mentioned above, depending on the DBMS in use. In particular, this applies to attributes of the UUID type – only the PostgreSQL driver returns values of corresponding columns using this type; other servers return String. To abstract application code from the database type, it is recommended to convert parameter types and query results using the DbTypeConverter interface.