Writing custom SQL queries
Company Blogs December 13, 2011 By Miguel Ángel Pastor Olivar Staff
Sometimes there is need of writing custom SQL queries in order to obtain the data acording to our needs. And, as all of you know, Liferay can run on top of the most popular databases so writing this queries should be written "carefully" to prevent unpleasant surprises in the future.
The following lines summarize some of the most common tips that we must pay attention:
- Using the "&" bitwise operator. Many databases, as Oracle, does not support using the keyword "&" as an operator, using a function instead. If you want to write an "bitwise and" operation in your custom SQL queries you will need to type something like BITAND(X,Y) and Liferay will translate your code according the underlaying database.
- Achieving casts in text columns. Another common difference between databases are casting text columns. You can solve this problem using the CAST_TEXT(text_column) function. So, every time you need to do a cast with text columns use this function and your cast operation will be working on the different databases.
- Integer division. Some databases as MySql have an specific way on doing integer divisions. If you want to the integer result you should write A DIV B in MySql or TRUNC(A, B) in Oracle. If you want to make your query agnostic from the database use the function INTEGER_DIV(A, B).
- Related with the first item, some databases as SyBase does not support using decimals columns in the bitwise operator. Liferay comes to the rescue again and offer you a CAST_LONG(x) function that will translate your sql code acording the current database.
There are some more tips but the previous ones, IMHO, are the most common in the custom sql queries.
One simple advice: be careful when writing your custom SQL queries ;)
PD: I need to create a wiki page will all the technical details. When the page is available i will update this entry with the corresponding link.
PD2: I have a few pending blog entries related with Liferay and Cloud Computing. Hope I can write this posts as soon as possible :)