non-random ramble

After playing with regular expressions in notepad+, we were writing some tests around a SQL based query language and wanted to print out the queries nicely.

We came up with the remarkably simply yet effective use of String.replace() to create the following:

     String querystring = getSomeQueryString();
     System.out.println(queryString
                .replaceAll("WHERE", "\\nWHERE")
                .replaceAll("AND", "\\n\\tAND")
                .replaceAll("OR", "\\n\\t\\tOR"));

Well it worked for ours, think might need some adjustment for different OR / AND structures.

Just so i dont forget:

log4j.category.org.hibernate.SQL=DEBUG

log4j.category.org.hibernate.type=TRACE

Found this cool blog about shards…

i.e. scale by putting each users data on a cheap db box – multiple parallel writes possible.

http://mysqldba.blogspot.com/2006/10/unorthodox-approach-to-database-design.html

select *
from (
select *
from (select * from CODETABLE_READCODE)
order by DESCRIPTION
)
where rownum <= 6

I discovered a joyful thing with the websphere JDBC connection:

You want to make a prepared statement that uses the “like” operator.

You write :

select * from SOMETABLE where SOMECOLUMN like ?

and it throws an IndexOutOfBoundsException !!

YOU HAVE TO GET THE CASE CORRECT! so :

select * from SOMETABLE where SOMECOLUMN LIKE ?

or

select * from SOMETABLE where SOMECOLUMN Like ?

Weird eh ?

also if you want to select by a date you have to do :

select * from SOMETABLE where SOMEDATECOLUMN = to_date(?, ‘dd-mm-yyyy’)

putting in whatever format you have.