Tag Archives: Tip

Query SQL / BOQL with Dates in where clause using Java

Posted on by
Reply

Hi everyone,
One of the things that I struggled in the beginning (a long time ago in a galaxy far away, cof cof) with SQL queries (or BOQL queries if you’re using the XEO framework) was how to make queries using dates in the where clause, something like this:

select * from table where column_date > SOMETHING

Not knowing how to do date comparisons I usually did stuff like this ( evil code ahead, beware ) :

select * from some_table where to_char(column_data,'DD-MON-YYYY') > to_char('23-05-2013','DD-MON-YYYY');

Usually in Java you have java.util.Date instances which I heroically used like the following, in order to be able to create the previous query:

SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");
java.util.Date date = loadDateFromSomewhere();
String sql = "select * from some_table where to_char(column_data,'DD-MON-YYYY') > ('"+sdf.format(date)+"','DD-MON-YYYY')";

Well now I don’t do this kind of thing, eh eh… it’s way easier to do such queries using proper data types. Check in a Java SQL Prepared statement:

Connection conn = getConnection();
PreparedStatement statement = conn.prepareStatement("select * from some_table where column_date > ?");
statement.setDate(1, new java.sql.Date(loadDateFromSomewhere().getTime()));

Notice how you pass a java.sql.Date instance (created using the information from the java.util.Date instance) to the parameter. This has the added benefit that no data conversions are done (meaning extra performance). And if you have an index on a date column that index will be used ( I also learned that when you apply a function to an indexed column, that index won’t be used properly as the function has to be applied to every row in the table – check this )

In BOQL by the way it would be like this:

boObjectList.list(getEboContext(), "select Object where column_data > ?", new Object[]{ new java.sql.Date(date.getTime()) });

Important notes:

  • In SQL/BOQL queries you use java.sql.Date and java.sql.Timestamp instances (you don’t use java.util.Date instances)
  • Use java.sql.Date instances when you want to compare Dates only (ignoring time)
  • Use java.sql.Timestamp instances when you want to compare Dates including time information

And voilá… Dates in SQL/BOQL queries (using Java) without much effort. Hope it was useful,  Happy coding!

 

Avoiding the copy-paste mistake when using Iterators in Java

Posted on by
Reply

If you’re reading this it’s probably because you’ve done something like this (iterating over two lists in same block of code):

//Iterating with no problems

Iterator<String> it = list.iterator();  

while (it.hasNext()){  

     String current = it.next;  

}  

//BUG!!!!!!

Iterator<String> it2 = list2.iterator();  

while (it2.hasNext()){  

     String current = it.next;  //Not using the correct iterator

}

Before anyone says anything, YES, YOU SHOULD USE FOREACH. But there are situations where you want access to the iterator (for instance, to remove an element using the iterator’s remove() method).

This little bug where you increment the wrong iterator variable is a pain in the neck to figure out sometimes and I read this really interesting piece of advice in Joshua Bloch’s Effective Java. Instead of using a while loop, use a for loop like this:

for (Iterator<String> it = list.iterator(); it.hasNext(); ){  

   String current = it.next();  

}

The snippet above allows you to reuse the “it” variable name as many times as you wish because, this time, the variable is declared inside the for loop thus, if you try to use it else where the compiler will generate an error (because the variable is only declared in the scope of the for loop) and you’ll avoid some debugging time 🙂

P.S – One could argue that you should iterate over each list inside separate methods, but the point of this post is to put the variable declaration in a narrower scope than the original idea (the one that creates the error in the first place).

Happy coding!