Plain Old SQL Statements in Java

Lot's of Java developers want to avoid writing SQL or even avoid directly accessing with a relational data store.  I, on the other hand, want to use SQL in my Java programs (which are mostly database utilities) but I want it to be as easy to use SQL in Java as it is in Python, Perl or PHP. 

Recently, I decided to use Java rather than Perl as my language of choice for database utilities.  Perl is quick to write, but tends to be easier to write than to read.  I played with Python for a while, but in the end decided that by using Java I would have an easier time distributing by stuff and it could more easily be re-used inside of my company, which has Java developers but not many Perl people.  So developed a set of JDBC wrappers that would let me use SQL easily within my utilities.   

My Design :

  • Require no configuration files (no XML mappings for instance).  All the data access logic should be right there in the code.  Although I did end up allowing SQL statement to be held in an XML file just to avoid the whole messy string handling involved in really long SQL statements.   
  • Easy processing of result sets, leveraging Java collections (which were not around when JDBC was first speced).
  • Make it easiest to follow best practices.  For instance, make it very easy to use bind variables, re-use cursors, etc.
  • Allow interoperability with the underlying JDBC objects so that I could use these classes without worrying that I would run into a brick wall.
  • Work well with dynamic SQL.
  • Where appropriate, avoid some of the tedium involved with DML and DDL.
  • Cache prepared statements so that you don't have to worry about which prepared statements to keep open and when to close.
  • Be RDBMS neutral. 

This was a bit of a learning experience - had not programmed in Java for quite a while and I tried to follow best Java practice such as creating Junit tests, JavaDoc and ant builds.  If anyone's interested,  here's the latest versions:

I don't really expect anyone else to use this, posting it was I guess one of the ways to enforce a bit of discipline on myself as regards quality. However, it will be embedded in most of my database utilities so I guess it will see some use in our internal benchmarking routines and the like.


MySQL 5.1 events

I finally got around to working with the 5.1 scheduler.  I wanted to have a simple but non-trivial example, and when I saw Brian Akers post on the new processlist table, I thought of a useful little application:  I would submit an event that would summarize the users and their statuses at regular intervals so I could track user trends.

First off,  I needed to enable the scheduler by adding the following line to my configuration file:


Now the scheduler is ready for action.  So I created a table to hold my process list details:

CREATE TABLE processhistory (h_timestamp DATETIME,
                             processcount INTEGER,
                             activecount INTEGER,
                             lockedcount INTEGER)$$

And then the DML to create an event:

CREATE EVENT evt_process_history
    INSERT INTO processhistory (h_timestamp,processcount,
     SELECT NOW() AS h_timestamp,COUNT(*) AS processcount,
            SUM(active) AS activecount  ,
            SUM(locked) AS lockedcount
       FROM (SELECT CASE command WHEN 'Sleep' THEN 0 ELSE 1
                     END AS active ,
                    CASE state WHEN 'Locked' THEN 1 ELSE 0
                     END AS locked
               FROM information_schema.`PROCESSLIST` P) Q;

Every two minutes, the event summarizes the status of the sessions currently connected and stores them to the table.  I could use various tools to analze this data, but for convenience I used Excel with the ODBC driver to create a chart of activity:


Cool! Now I can keep track of active sessions over time, which could be useful. On the test database, there is a little ruby program that locks up a table needed by my java TP simulation, so we see those spikes of lock activity. I'm hoping that MySQL expose the SHOW STATUS command as a table as well, since we can't get at the contents of SHOW STATUS from within the stored program language.


Building ruby with Oracle and MySQL support on windows

If you did the setup neccessary to compile perl with MySQL and Oracle support (), you are well setup to do the same for ruby.  Why this should be so hard I don't know:  python produces very easy to install windows binaries, but if you want anything beyond the basics in perl and ruby you need to try and turn windows into Unix first. Sigh. explains the ruby build procedure.   I'm really just adding instructions for getting the ruby dbi modules for mysql and oracle.

Make sure mingw and msys are first in your path.

Enter the mingw shell:  sh

sh-2.04$ ./configure --prefix=/c/tools/myruby

sh-2.04$ make

sh-2.04$ make test

sh-2.04$ make install

Now, lets do ruby gems:

sh-2.04$ cd /tmp/rubygems
sh: cd: /tmp/rubygems: No such file or directory
sh-2.04$ cd /c/tmp
sh-2.04$ cd rubygems
sh-2.04$ export PATH=/c/tools/myruby/bin:$PATH
sh-2.04$ which ruby.exe
sh-2.04$ ls
sh-2.04$ cd rubygems-0.8.11

sh-2.0.4$ unset RUBYOPT  #If you have cygwin this might be set
sh-2.04$ ruby ./setup.rb
c:\tools\myruby\bin\ruby.exe: no such file to load -- ubygems (LoadError)



Compiling DBD::mysql and DBD::Oracle on windows

Last week my laptop crashed and while installing the new one I decided to update my perl versions. I mainly use the DBD::mysql and DBD::Oracle modules and although I'm confortable building them on Linux/Unix, like most people I use the Activestate binaries on windows.

However it turns out that Oracle licensing changes now prevent Activestate from distributing an Oracle binary, so I was forced to build them from source. It wasn't easy, but now both the Oracle and MySQL modules are working. Here's the procedure in case it helps anyone.

Install Pxperl

Firstly, you probably want to move to the pxperl windows binaries. Pxperl support the familiar CPAN system for updates. Get Pxperl at The installation should be straight forward.

I installed into c:\tools\pxperl

Install MinGW

You'll need a C compiler capable of building native windows binaries. I used the MinGW system. You can't use cygwin, although I believe that Cygwin might be capable of installing MinGW. Anyway, I got the MinGW system from I couldn't use the auto-installer for firewall reasons, so I did a manual download and install.

Firstly, I unpacked the following .gz files into c:\tools\mingw:

  • gcc-java-3.4.2-20040916-1.tar.gz
  • gcc-objc-3.4.2-20040916-1.tar.gz
  • mingw-runtime-3.9.tar.gz w32api-3.5.tar.gz
  • binutils-2.15.91-20040904-1.tar.gz
  • mingw-utils-0.3.tar.gz gcc-core-3.4.2-20040916-1.tar.gz
  • gcc-g++-3.4.2-20040916-1.tar.gz

You probably don't need all of these, and of course the version numbers might be different by the time you read this.

Then I ran the following two executables

  • MSYS-1.0.10.exe
  • msysDTK-1.0.1.exe

...installing both into c:\tools\msys. You must make sure you provide the correct location for MinGW when prompted. Finally, MinGW installs it's own version of perl, so I removed that as well as the make.exe which is inferior.

I added both the bin directories to my path, which now starts something like this:  c:\mysql;c:\tools\msys\1.0\bin; c:\tools\mingw\bin; C:\tools\PXPerl\parrot\bin; C:\tools\PXPerl\bin

Installing DBD::Oracle

Now you can go into cpan (just type CPAN at the command line) and run "Install DBI".  That worked OK for me.

Then I ran "install DBD::Oracle".  That failed.  I can't remember the exact error, but it turns out that a trailing backslash in the include directory for the DBI doesn't work on Windows.  To fix that, run "configure_pxperl" and add an include for that directory in the "Include Directories" section.  For me, the directory was /tools/PXPerl/site/lib/auto/DBI , since I installed pxperl into the tools directory.

Installing DBD::Mysql

For some reason I thought this would be the easy part.  But it actually was really difficult.

In the end, it turns out you need to create your own version of mysqlclient.lib and manually link to that. Check out MySQL Bugs: #8906, for some more details.  Here's the steps that worked for me:

  1. run "install DBD::mysql" from the CPAN prompt
  2. You will get a whole lot of undefined symbol errors which will include the names of the normal mysql client API calls, suffixed with '@4' , '@0' , etc. Make a list of all of these.
  3. Add the missing symbols to the file include/libmysql.def.   
  4. Build your own libmysqlclient library with the following commands (from the directory just above your include directory):
  5. dlltool --input-def include/libmySQL.def --dllname lib/libmySQL.dll --output-lib lib/libmysqlclient2.a -k
  6. Go to the CPAN build area for the DBD-mysql,  for me that was: cd \tools\PXPerl\.cpan\build\DBD-mysql-3.0002
  7.   nmake realclean 
  8. perl Makefile.PL --libs="-L/mysql/lib -lmysqlclient2 -lz -lm -lcrypt -lnsl"
  9. nmake install

And - voila! - you should be OK. The only think you might need to do now is add the top level MySQL directory to your path.  DBD-Mysql wants to find "lib/mysql.dll" so you need to add the directory above that to your path.  I moved all the libraries to c:\mysql\lib and include files to c:\mysql\include, so I added to my path like this:

set PATH=c:\mysql;%PATH%

All done!

Seems to be working OK now for both Oracle and MySQL.  Much more difficult than installing the Activestate binaries but at least now that I'm working from source I can potentially fix bugs although having done it on Linux it's not for the faint hearted (or the incompentent in C++!)

Hopefully pxperl will gain in popularity and as it matures things will work as easily as on Linux.  That would be great.

Page 1 ... 14 15 16 17 18