The efficiency of application development management and effectiveness of this application (response time) relies heavily on building a data model "effective".
In this post we will focus on a choice "effective" simple types to a Java application based on Hibernate and JPA.
This article completes a series of other posts on this same JPA Blog:
- Should I use EJB 3.0 and Java Persistence API : This is a reflection on the desirability of use EJB 3.0 and JPA Tutorial
- mapping by annotation JPA : a tutorial introduction to JPA annotations
- Realization of a Web application and JPA Ajax: a tutorial which uses JPA in a web application Ajax
If the word "effective" has been repeatedly quoted, is that this word can take the following contradictory aspects point of view:
- the effectiveness of a data model for the DBA (Database Administrator) will be based on storage space and fast query execution,
- for a developer that will rely largely on the ease that he has to write queries that match the functional need for the user
- it will be the response time of the application
My concern in this post is to provide some balance, a compromise between these aspirations keeping in view, the pragmatic need to which we must all face, develop an application at the best price for a demanding customer.
Initially, we will outline a number of simple rules to quickly select the data types of its properties. In a second step we will explain with examples the various reasons for these choices. This second part should allow a better understanding of the behavior of Hibernate and limits the portability of various solutions.
Constraints for the effectiveness of a data model
We focus in this post only the aspects of the typing of properties and simple columns.
To effectively select a data type must:
- understand the nuances between different types
- be able for a given real world (the value we want) to distinguish the different nuances associated with different types
For example, a double and BigDecimal both represent a decimal number. While the double has no limit to its precision: the same variable can contain a specific number or 10 to 2 decimal places, this is not the case of a BigDecimal, this object has an attribute that indicates precision the number of digits after the decimal point. To change its precision it is necessary to use a method.
For a float, 1.23 and 1.230 are identical, this is not the case for a BigDecimal as attribute accuracy is not the same.
Similarly, if we now know that a euro value will be lower than say at 1 000 000 €, we can hardly know the limits which must be taken into account in a decade especially if we take into account a change in currency.
therefore want for each amount set different limits depending on the information is not necessarily relevant: it increases the difficulty of the choice and we do not always possible to predict changes (increases the risk Error wanting to be precise).
For these choices remain relevant and effective over the lifetime of the application (often several years) requires that different developers handling such data efficiently handle the know. So we often interest:
- be limited to a restricted set of types: a little too precise in some cases cause side-effects negative example in the size of strings: in the end, you never know what is the size of a field and suddenly the likelihood of a developer in a hurry to make mistakes in the control is greater.
- use well-known types and controlled by the whole team: if a type is not well controlled, its use can be catastrophic because one of the first reflex is often to revert to a known type, which has the dual effect of adding extra work and the risks of developing side effects during conversion. Edge effects are obviously very people they wanted to avoid the choice of this type.
My experience gained through the audit of projects shows that often the best is the enemy of good. Optimization too far in forcing out the trails often more perverse effects for a project that more reasonable value.
Finally, typing an "information" is not intrinsic to its content. Its use is also important. Not take such a number composed entirely of numbers, this does not mean it will be stored as an integer base. In many cases it is best to store it as a string. This will depend on the controls that you want to do research that is desired to produce ...
other hand, if information must be stored in the database, it must also be manipulated in Java and displayed on a screen. For developing effective, it must pass from one representation to another and the controls are quick and effective. Hibernate and JPA facilitate this work, still do not he put too many roadblocks in forcing him to use incompatible types.
The rules adopted are:
- limiting the number of types of known types
- some portability across databases
- typing coherent base for display via the memory representation in Java
typing data
We have five major types of data we subdivide into types Nearest Uses:- numeric types
- Monetary amounts: these values in currencies such as prices, sales, amount, commissions ...
- measures: it is a decimal value that corresponds to the measurement of an object that it was a remote, weighing ...
- rates and percentages: it is decimal low (below 100) and sometimes requiring a bit more precision (some rates have 4 digits after the decimal point)
- Quantities: This is for integer values to number of units of a product.
- alphanumeric values
- Strings: strings conventional store information in clear (unencrypted)
- codes: it is a short information (less than ten characters) which is coded.
- Comments: This is a text composed of several words.
- values listed
- Values True-False: this corresponds to the check boxes and dropdown lists
- enumerations: This is information that the selection is a drop down list. is stored in a code base and is displayed with a label a parameter table that provides correspondence.
- dates and timestamp
- The traditional dates precise to the day
- hours: one hour in the day regardless of the date
- tags schedules (TimeStamp) that keep track of the exact time of a event. Those dates are accurate to the millisecond
- identifiers
- the file number sequences
Monetary Amount Type
Monetary amounts have a peculiarity: the number of digits after the decimal point is a constant (Often 2). The rounding problems must always be solved with this constraint: it is not possible to temporarily increase the number of digits after the decimal point.
other hand, the BigDecimal is absolutely accurate in comparing decimal number which is imperative for monetary amounts.
For more details on the value of using BigDecimal you can read my post: Tutorial BigDecimal
The storage hibernate a BigDecimal is a Number of 19 digits with 2 decimal places . We will use this format for all amounts.
The number of digits (19) may seem excessive when account in Euro, Yen, but it is reduced even more for other currencies.
He seems wiser to stay on the default size.
Sample definition of a value:
@ Column (name = "JLTS_AMOUNT", precision = 19, scale = 2) private BigDecimal
amountDefault;
The BigDecimal is an object, it can be null as the corresponding columns in the database. There will be no problem to adapt if the value can be null.
| Database | Type Base | Note |
| Oracle NUMBER (19.2) | The number is truncated if greater accuracy is | |
| MySql | decimal (19.2 ) | An exception is thrown if the accuracy is greater |
| Hsqldb | numeric | Storage knows neither accuracy nor control is stored exactly what we received |
| Sql Server | numeric (19,2) | The number is truncated if the precision is larger, the number is completed for accurate |
Measurement Type A measure similar to a monetary amount, with one difference: the precision of digits after the decimal point is not of great importance (do not forget that the precision of a physical measurement is rarely above 1%).
It may be wise to store a measure in a double or Double, which facilitates programming.
Sample definition of a value:
@ Column (name = "JLTS_MESURE" nullable = false) private double
measure;
| Database | Type Base | Note |
| Oracle | double precision | |
| MySql | double precision | |
| Hsqldb | double | |
| Sql Server | double precision |
Standard Rates and Percentage
percentage rates and are usually associated with monetary data type. Often these multiplications to achieve a monetary amount to obtain a new sum. Typically, a percentage rate can be stored with 2 or 4 digits after the decimal point and does not exceed the 3 digits before the decimal point. To simplify modeling, we use the following definition:
@ Column (name = "JLTS_TAUX" precision = 7, scale = 4, nullable = false)
; private BigDecimal rate;
nulllable Note the attribute set to false by default: it is likely that the default rate is set to 0 and not left to Null. Null makes delicate operations performed while with the default value 0, the result would be functionally correct in many cases.
| Database | Type Base | Note |
| Oracle NUMBER (7.4) | The number is truncated if greater accuracy is | |
| MySql | decimal (7.4) | An exception is thrown if the accuracy is greater |
| Hsqldb | numeric | |
| Sql Server | numeric (7.4) | The number is truncated if the accuracy is greater, the number is completed for accurate |
Type Quantity entire
These integer values as the number of items purchased. We opt for a Long type if the value can be null otherwise we will opt for a long. Warning! In this case, it should be noted nullable attribute to false. It is likely that in many cases, the absence of quantity or the value 0: In this case, the length will be more appropriate.
@ Column (name = "JLTS_QUANTITY", nullable = false) private long
longNumeric;
or
@ Column (name = "JLTS_QUANTITYNULL") private Long
longObject;
| Database Base Type | Note | |
| Oracle NUMBER (19.0) | The number is truncated if greater accuracy is | |
| MySql | bigint | |
| Hsqldb | bigint | |
| Sql Server | numeric (19,0) | The number is truncated if greater accuracy is |
type String
This is all classical strings that correspond to information relatively structured as a name, first name, street name ... These chains are of reasonable size: less than 250 characters. Beyond that, it will probably be a data type of comment.
Storing a string is very simple: a String.
@ Column (name = "JLTS_NAME", length = 40)
private String name;
| Database | Type Base | Note |
| Oracle varchar2 ( 40 char) | Where 40 is the length. Oracle does not distinguish between strings empty string null. It only stores a null string. The char attribute specifies the length after taking into account the UTF-8. | |
| MySql | varchar (40) | MySql is the difference between an empty string and a null string. |
| Hsqldb | varchar (40) | It sends no exception when string too large |
| Sql Server | varchar (40) |
Type Code
Some information is stored as a code:
- postal code code coin
- code
- a state code of a bank
- Siren
- Siret
As a general rule codes are fixed length and relatively short (less than 10 characters). They can be alphanumeric or numeric.
They can also match the type "dropdown list" described below. The distinction is of no interest because the result is the same. It uses a String.
@ Column (name = "JLTS_SHORTCODE", nullable = true, length = 2) private String
ShortCode;
| Database | Type Base | Note |
| Oracle varchar2 (2 char) | ||
| MySql | varchar (2) | |
| Hsqldb | varchar (2) | |
| Sql Server | varchar (2) |
Type Comment
There are high chances that any information that exceeds one hundred Character is a field of type comment. Even if it is comfortable to limit this field to a String and a storage base limited to one or two thousand characters, this approach is highly likely not to withstand future needs of users. It is therefore preferable to use a representation as a CLOB.
@ Lob @ Column (name = "JLTS_COMMENT)
private String comment;
| Database | Base Type Note | |
| Oracle CLOB | ||
| MySql | longtext | |
| Hsqldb | longvarchar | |
| Sql Server | text | ; |
Type Boolean True-False
This is information that is usually displayed as a checkbox (CheckBox). The value can be True or False.
All bases but also the best known and most used (Oracle) does not have Boolean type. The information will be stored as a character (Y, N, for example) or number (0.1).
standard JPA annotation is here.
booleanType;
| Database | Type Base | Note |
| Oracle | number (1.0) | 0 for false and 1 for true |
| MySql | bit | direct correspondence with true and false |
| Hsqldb | bit | |
| Sql Server | tinyint | 0 for false and 1 for true |
There is a Hibernate annotation (not JPA), which allows indicate the storage base:
@ Column (name =" JLTS_YESNO)
public boolean yesno;
@ Type (type = "true_false") @ Column
(name = "JLTS_TF") public boolean
trueFalse;
| Database Base Type | Note | |
| Oracle | char (1 char) | Storage form of a letter Y and N for yes_no |
| MySql | CHAR (1) | true_false T and F, |
| Hsqldb | ||
| Sql Server | char (1) |
Type dropdown list and list
This is the cases generally represented by a dropdown list (ComboBox HTML SELECT or component). There are a series of labels that correspond to a code.
The storage base is a code.
Generally the code belongs to another column (the notion of Foreign Key).
The storage will be in the form of string classical
@ Column (name = "JLTS_COMBOBOX", nullable = true, length = 2)
private String selection;
I do not recommend the use of many- to-one for this type of relationship: this relationship adds to the display when there is information that corresponds to a parameter table. The many-to-one should be reserved for connections between real objects.
| Database | Type Base | Note |
| Oracle varchar2 (2 char) | ||
| MySql | varchar (2) | |
| Hsqldb | varchar (2) | |
| Sql Server | varchar (2) |
Type Date specifies the date
The case dates is very particular the type Date is a precision much greater than the need for storing dates is limited to an accuracy to within a day.
JPA Temporal annotation defines which limits the accuracy to date (DATE), time (TIME), where extreme precision and complete (TIMESTAMP)
Accuracy is important in the dates: it is desirable that a search on records with a Date column that corresponds to today's date 22/09/2009 returns all records via a tie.
If precision is too large (at second) it will not work.
@ Column (name = "JLTS_DATE", nullable = true) private Date
DayDate;
| Database | Type Base | Note |
| Oracle | date | Storage is in a precision dd / MM / yyyy. Equality at work day in Oracle applications (using TO_DATE) |
| MySql | date | Equality at work day in MySql queries (using STR_TO_DATE) |
| Hsqldb | date | |
| Sql Server datetime | Storage is in a precision dd / MM / yyyy. Equality at work day in the SQL Server (using Cast) |
Type Time
This is a time of day, independent of the day. This type is rarely used. His lack of portability makes me prefer the TimeStamp.
@ Temporal (TemporalType.TIME)
@ Column (name = "JLTS_TIME", nullable = true)
private Date time;
| Database | Type Base | Note |
| Oracle | date | This format does not work in Oracle |
| MySql | time | |
| Hsqldb | time | |
| Sql Server datetime |
TIMESTAMP
This is often a specific date to the millisecond allowing keep track an event. This column is often in history tables or audit that keep track of changes made by users.
This type of trace is primarily necessary audit and traceability of information.
His second accuracy can not find just the records where the timestamp value is equal to a date.
@ Column (name = "JLTS_TIMESTAMP", nullable = true) private Date
timeStampAudit;
| Database | Base TypeNote | |
| Oracle timestamp | ||
| MySql | datetime | The accuracy of this format is lower than Oracle |
| Hsqldb | timestamp | |
| Sql Server datetime | The precision of this format is lower than Oracle |
Sequence Type id The id is the simplest key automatic digital . Oracle has the notion sequence for that. In other cases, it must pass through another table that stores the counter.
For portability, it is desirable to use the automatic type following: GenerationType.AUTO.
GenerationType.SEQUENCE works well with Oracle but not with MySQL.
GenerationType.IDENTITY: works well with MySQL but not Oracle.
@ Id @
GeneratedValue (strategy = GenerationType.AUTO)
@ Column (name = "LONG_ID", nullable = false) private long id
= 0;
This corresponds to a sequence
| Database Base Type | Note | |
| Oracle | number (19.0) | There is created using a sequence Oracle hibernate_sequence. |
| MySql | bigint | |
| Hsqldb | bigint generated by default as identity (START WITH 1) | |
| Sql Server | numeric (19,0) identity |
Type File Number
There is information in a digital appearance (They are composed only of numbers) are usually handled as a string of characters as a number.
For example, there is no arithmetic operation on a file number or social security number: add a number, multiply, divide, or compare it not make sense.
It is more common to concatenations of strings: sex code, the code department for a Social Security number.
For a file number, you can find it practical to search by typing only part of the number using a Like.
For these fields, it is desirable to use a String.
@ Column (name = "JLTS_NUMERO", nullable = true, length = 10) private String
NumeroDossier;
| Database | Type Base | Note |
| Oracle | varchar2 (10 char) | |
| MySql | varchar (10) | |
| Hsqldb | varchar (10) | |
| Sql Server | varchar (10) |
Nullity of a column
A column can be null which is different from 0 for a number and an empty string "" to a String. Oracle is a notable exception for string: Oracle inserts a null into a column when passed an empty string. This point is still valid with Oracle 10G and the driver ojdc14.jar. The Java types
accept they are a type of objects (BigDecimal, Double, Long ...) Null which is not the case for native types (double, long, integer ...). The String nullable.
From technical point of view, it is not advisable to use a native type if the column does not have the NOT NULL attribute: it might cause problems or inconsistencies if it gets a null value.
From a functional view, the null must be used to distinguish a non-input of others: We use the null if it is relevant to distinguish 0 from the absence seizure.
For example, it is not desirable that the note of a child is by default 0. It is important to be able to distinguish the lack of scoring (the null) of a zero mark (0).
In the case of a discount, it's probably the opposite, we want the default value is 0. This allows for simple calculations. Value may remain at 0 without much impact. In general, changing this value requires special duties (for example, be manager).
For strings should be avoided to distinguish the case of null and empty strings in order to maintain portability to Oracle. This is not necessarily a major problem. The status of an object (which can be detected by the difference between a field null and void) and is also probably best delivered by a specific column STATUS / CONDITION (this is the type code).
For numbers, the choice is more difficult: the absence
- null facilitates the calculation: the calculation is not beson test the existence of the null
- 0 can often be taken as a default
- there are cases (as a footnote) or 0 can be considered a default, in this case the null is indispensable.
should not lose because the creator of the business object does not necessarily have every right to enter all values. it may be a data entry operator who lacks the qualifications to perform certain sensitive values. In this type of process, it may be important that the person who owns the rights to complete the record can distinguish a quick glance the information that has yet to grasp. In this case, the null can be useful even if in other cases, the value 0 will be very relevant.
In our previous example, the rebate will remain at 0.
If it takes a simple rule: I will give the following rule: The null is useful if the default value of the field can not be 0, in other cases it is preferable not to use Null.
Conclusion The design phase of the data model can be greatly simplified by limiting the definition of types to the list given above :
| Type | Notation JPA | Oracle | Mysql | HsqlDb | Sql Server |
| Montant monétaires | @Column(name="JLTS_AMOUNT", nullable=true, precision=19, scale=2) private BigDecimal amount; | NUMBER(19,2) | decimal(19,2) | numeric | numeric(19,2) |
| Mesure | @ Column (name = "JLTS_MESURE", nullable = false) private double measure; | double precision double precision |
| double | double precision |
|
| @ Column (name = "JLTS_TAUX", nullable = true, precision = 7, scale = 4) private BigDecimal rate; | NUMBER (7.4) | decimal (7.4) |
| numeric numeric (7, 4) |
| Quantity | @ Column (name = "JLTS_LONG", nullable = false) private long longNumeric; | NUMBER (19.0) | bigint bigint |
| numeric (19,0) |
| Strings | @ Column (name = "JLTS_STRING", length = 40) private String textString; | VARCHAR2 (40 CHAR) | varchar (40) | varchar (40) | varchar(40) |
| Code | @Column(name="JLTS_SHORTCODE", nullable=true, length=2) private String shortCode; | VARCHAR2(2 CHAR) | varchar(2) | varchar(2) | varchar(2) |
| Commentaire | @Lob @Column(name="JLTS_COMMENT") private String commentaire; | CLOB | longtext | LongVarChar | text |
| Vrai-Fausse et Booléen | @ Column (name = "JLTS_BOOLEAN) private boolean BooleanType; | NUMBER (1,0) | bit | bit | tinyint |
| list déroulante | @ Column (name = "JLTS_SHORTCODE", nullable = true, length = 2) private String short code; | VARCHAR2 (2 CHAR) | varchar (2) | varchar (2) | varchar (2) |
| Date | @ Temporal (TemporalType.DATE) @ Column (name = "JLTS_DATE", nullable = true) private Date DayDate; | DATE | date | date | datetime |
| Time | @ Temporal (TemporalType.TIME) @ Column (name = "JLTS_TIME", nullable = true) private Date time; | DATE | time | time | datetime |
| Tags schedules (TimeStamp) | @ Temporal (TemporalType.TIMESTAMP) @ Column (name = "JLTS_TIMESTAMP", nullable = true) private Date timeStampAudit; | TIMESTAMP datetime |
| timestamp datetime | |
| File Number | @ Column (name = "JLTS_NUMERO", nullable = true, length = 10 ) private String number; | VARCHAR2 (10 CHAR) | varchar (10) | varchar (10) | varchar (10) |
| Sequence | @ Id @ GeneratedValue (strategy = GenerationType.AUTO) @ Column (name = "LONG_ID", nullable = false) private long id = 0; | NUMBER (19.0) | bigint bigint |
| numeric (19,0) identity |
In the case of a phase re-engineering an existing data model, it will probably be possible to rely on the same types may be changing their representation JPA in order to adapt to the existing database.
For example, if the base amounts are NUMBER (11.2), type the amount can be either
@ Column (name = "JLTS_MONTANT" precision = 11, scale = 2 , nullable = false) private BigDecimal amount
;
In practice, it should not appear more than two versions of the same type in an existing model.
I hope this tutorial will simplify the design of your next data models. A future post will provide a test project to better understand the different behaviors.
