How to Print Out Bind Variables in Java Prepared Statements.

Ever been frustrated by the fact that when you use (as you should do!) bind variables within a java prepared statement you cannot see the actual values that get put into those bind variables? I know I have. For example if you had a class that had some JDBC calls that did the following….


connection = DriverManager.getConnection("jdbc:mysql://localhost/message_board", "root", "");

statement = connection.prepareStatement("select * from messages where id = ?");

resultSet = statement.executeQuery();

There is no nice out the box way to find out what the ?’s will be resolved to. I’ve always wanted a method on PreparedStatement that would address this, but alas there still is none. Traditionally I would have done something like the following…

long id = 1L;
String sql = "select * from messages where id = ?" + "; ?1=" + id);

statement = connection.prepareStatement(sql);

Or write your own convoluted method for replacing each ? with the value which is prone to bugs and not being database agnostic.

When using an old jdbc based in-house framework this really wasn’t an issue for me, as we had such a method that was prone to bugs but for the most part it worked ok. Since then the work I have been doing has been much more hibernate based. This is where the issue came for me, you can turn on sql logging either by configuring your favourite logging framework to do so, or by turning on the hibernate.show_sql property, which will give you the following output…

select as id2_, message0_.email_address as email2_2_, message0_.inserted_at as inserted3_2_, message0_.remote_host as remote4_2_, message0_.text as text2_ from messages message0_ where

This sql was generated from the following hql

from Message where id = ?

Now apart from this sql being particularly hideous to look at, the question mark is still there, we have no way of possibly knowing what those bind variables were without printing them out at the time the hql is executed.

Enter p6spy.

I did a bit of googling about and found other people had the same issue. And there is an answer to this problem, and its called p6spy. P6spy is a jdbc driver that is a proxy for your real driver. I’ve only just started to use it, I believe it is capable of many cool things, but the one that I am interested in allows you to print out sql from prepared statements with – yep, you guessed it – the bind variables substituted for the real values.

You can download it from, it must be fairly stable as the last release was in 2003 (I’m so annoyed that I didn’t know about this earlier). There seems to be a little problem with their website as when you click download you get an error while trying to fill in or ignore their survey. Don’t panic, once you’ve ignored the survey go back to the main page and click download again and the files will be there waiting for you to download.

So how do we configure this little beauty? Well the download is, unzip this somewhere safe (and keep in mind that all the files are in the root of the zip file, so you might want to create a directory to unzip it to first). The files we’re interested in are p6spy.jar and Copy the jar file into the lib folder of your project and copy the file into somewhere that will be on the classpath.

If you look inside you will see that it’s really very well documented and tells you all you really need to know on how to configure it. Although one thing to note in there is that they have said that the real driver you configure in your application should be set to com.p6spy.engine.P6SpyDriver which is actually incorrect, it should be com.p6spy.engine.spy.P6SpyDriver, the first one doesn’t exist and you will get a ClassNotFoundException.

So what I did… In my I set the realdriver…

# mysql Connector/J driver

(note by default the open source mysql driver is uncommented which was a gotcha for me at first, so I commented it out, and uncommented the above)

Next I altered my code in the first example to the following…


connection = DriverManager.getConnection("jdbc:mysql://localhost/message_board", "root", "");

statement = connection.prepareStatement("select * from messages where id = ?");

resultSet = statement.executeQuery();

And lo and behold in the root of my eclipse project on the file system a file called spy.log has appeared. and in it the following…

1217374504582|6|0|statement|select * from messages where id = ?|select * from messages where id = 1
1217374504595|-1||resultset|select * from messages where id = 1|email_address =, id = 1, inserted_at = 2008-07-29 00:00:00.0, remote_host = localhost, text = Thank you for showing me the wonders of p6spy

There is lots of other stuff in the log file as well but as you can see its very handy, as it shows you the statement with question marks and with the real values substituted, and also shows you the results set which is passed back 🙂 Beautiful lol.

And the hql of

from Message where id = ?

is output as:

1217374913350|5|2|statement|select as id2_, message0_.email_address as email2_2_, message0_.inserted_at as inserted3_2_, message0_.remote_host as remote4_2_, message0_.text as text2_ from messages message0_ where|select as id2_, message0_.email_address as email2_2_, message0_.inserted_at as inserted3_2_, message0_.remote_host as remote4_2_, message0_.text as text2_ from messages message0_ where
1217374913369|-1||resultset|select as id2_, message0_.email_address as email2_2_, message0_.inserted_at as inserted3_2_, message0_.remote_host as remote4_2_, message0_.text as text2_ from messages message0_ where|email2_2_ =, remote4_2_ = localhost

Still ugly but at least I can now see what the bind variables are going to be 🙂

You can also configure it so it comes out in your normal logging file, the file details how you do that.

Oh how I wish I’d have known this 4 years ago.

Technorati Tags: , , , , ,,,


How to get Logitech QuickCam Ultra Vision working in Ubuntu

I have recently removed Windows from my Dell Inspiron 9400, and replaced it with Ubuntu, which is a flavour of Linux (or GNU/Linux if you’re picky).

I was surprised at how easy the installation was, almost everything worked out of the box, wireless, all the media keys and 95% of the fn keys. (I’m having trouble with fn+f8 and fn+10 not working correctly)

Anyway I am very happy with the Installation, I love the effects you have on the windows, it looks really pretty.

I wanted to get my Logitech QuickCam Ultra Vision working with Ubuntu, so I could use it on Skype. I plugged it in and nothing happened…ho hum… on windows it would have picked it up and installed it, and asked for the driver cd, but anyway, after a bit of googling around I found this thread, and I followed the advise of Andrew Barber on there.

Make sure first you have all the tools for the job:

sudo apt-get install linux-headers-`uname -r` linux-restricted-modules-`uname -r` build-essential subversion

Once you have got all them you will need to use subversion to get the driver from the svn repo..

svn checkout

Then you need to compile and install

cd linux-uvc/linux-uvc/trunk
sudo make install

Plug the camera in and take a look at dmesg. It may [hopefully] give you the device listing for it… eg /dev/video1

Point your application at that device and see if it works.

I checked dmseg to see what had happened…

$ dmesg | grep -i vid
[    0.000000] BIOS-provided physical RAM map:
[    8.445959] Boot video device is 0000:00:02.0
[   26.441798] input: Video Bus as /devices/LNXSYSTM:00/device:00/PNP0A03:00/device:2b/LNXVIDEO:00/input/input7
[   26.489145] ACPI: Video Device [VID] (multi-head: yes  rom: no  post: no)
[   26.497079] input: Video Bus as /devices/LNXSYSTM:00/device:00/PNP0A03:00/LNXVIDEO:01/input/input8
[   26.537089] ACPI: Video Device [VID1] (multi-head: yes  rom: no  post: no)
[   26.537246] input: Video Bus as /devices/LNXSYSTM:00/device:00/PNP0A03:00/LNXVIDEO:02/input/input9
[   26.585000] ACPI: Video Device [VID2] (multi-head: yes  rom: no  post: no)
[ 2902.096243] Linux video capture interface: v2.00
[ 2902.175522] uvcvideo: Found UVC 1.00 device <unnamed> (046d:08c9)
[ 2902.189674] usbcore: registered new interface driver uvcvideo
[ 2902.189683] USB Video Class driver (v0.1.0)

After that it worked in skype, I needed to set the sound in device to be the microphone of the web cam in the options of skype…

Sound In:   USB Device 0x46d:0x8c9 (hw:U0x46d0x8c9,0)
Sound Out:  Default device(default)
Ringing:    Default device(default)

and in the video settings of skype…

Select webcam: UVC Camera (0x46d:0x8c9) (/dev/video0)

I am still currently having a weird issue where it seems that skype takes over the sound of all other applications. If I try and play some music after a skype call then it won’t play until I reboot. Also it seems I’m allowed a max of 1 skype call before the sound in skype doesn’t work, and therefore doesn’t let me make or receive anymore calls. Again it only seems at the moment that a reboot will fix it. For the moment I will live with this as I don’t use skype that often anyway. (If anyone has any advice for this part please leave a comment)

Technorati Tags: , , , ,,

How to Inject Spring Beans into Servlets Revisited.

Back in November I wrote a post about How to Inject Spring Beans into Servlets, since then one of the comments made on the post by angel, mentioned an interface and Servlet that comes with spring that does this the proper spring way. Thank you very much angel, this is what I was looking for before I wrote the last post 🙂

The proper way how this should be achieved is to write a class that implements HttpRequestHandler and define this class in your applicationContext.xml. Then you define a servlet in web.xml with a class of HttpRequestHandlerServlet and make sure the name of the servlet is the same as the bean you defined in applicationContext.xml.

First lets write a class that we want to inject into a servlet…

package name.kayley.httprequesthandlertest;

public interface ServiceToInject {
  public String someMethod();
package name.kayley.httprequesthandlertest;

public class ServiceToInjectImpl implements ServiceToInject {
  public String someMethod() {
      return "someMethod called";

Next we’ll write a class that implements the HttpRequestHandler this is basically our servlet.

package name.kayley.httprequesthandlertest;

public class MyHttpRequestHandler implements HttpRequestHandler {

   private ServiceToInject serviceToInject;

   public void setServiceToInject(ServiceToInject serviceToInject) {
       this.serviceToInject = serviceToInject;

   public void handleRequest(HttpServletRequest request, HttpServletResponse httpServletResponse) throws ServletException, IOException {

       System.out.println("******* HELLO *******");

Next all we need to do is configure our web.xml and applicationContext.xml…

First applicationContext.xml…

<xml version="1.0" encoding="UTF-8">
<beans xmlns=""

    <bean id="serviceToInject" class="name.kayley.httprequesthandlertest.ServiceToInjectImpl"/>

    <bean id="myhttprequesthandler" class="name.kayley.httprequesthandlertest.MyHttpRequestHandler">
        <property name="serviceToInject" ref="serviceToInject"/>


And finally configure your web.xml …

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns=""


Now run your web app and go to http://localhost:8080/MyServlet

You should see in your console log the output of your injected servlet…

******* HELLO *******
someMethod called

Brilliant 🙂 Thanks again angel.

Technorati Tags: , , , ,

How to fold your Hakama

Some of you may or may not be aware that this coming Sunday I will be taking my Black Belt 2nd Dan (or Nidan in Japanese) grading in Ju Jitsu. The style of Ju Jitsu I do is called Go-shin Kempo Ju Jitsu and is based mainly at the Masters of Martial Arts academy in Accrington, Lancashire, UK. The association is a affiliated to the British Ju Jitsu Association Governing Body (BJJAGB).

We will be required to wear Hakamas for part of the grading, mainly to do Kata, then we’ll get changed back into the usual Gi (The name given for the Karate Style Pyjama suit for those of you not in the know). One of the criteria for the 2nd Dan grading is demonstrating that you can fold the Hakama correctly.

While I was searching the web I couldn’t find anywhere that resembled the way our Sensei’s wanted us to fold them.

I found this site which is a similar way but not exactly the same…

And then I found this one too, which at the bottom shows a way very similar to the way we have been shown…

So after practising folding a few times last night I thought I’d share my frustrations and show you this way to fold the Himo(the straps) of the Hakama. I start at this point because there are plenty of examples of how to get to this point on the web, including the two resources I mention above.

Step 1.

Starting with the left side, take the long straps and fold them up and place them in a cross shape across the folded hakama…


Step 2.

Bring the left short strap over the top of the hakama and place it over the two longer folded straps as shown in the photo.


Step 3.

Thread the shorter strap underneath both of the longer straps and upwards as shown below…


Step 4.

Take the left strap and move it back over itself to the left as shown…


Step 5.

Thread the strap under both the short and long straps and diagonally upwards as shown…


Step 6.

Place the strap along the diagonal line of the long strap, if its too long, tuck it under. This side is now finished…


Step 7.

Bring the right short strap over the top of the hakama and place it over the two longer folded straps as shown in the photo.


Step 8.

Again thread the shorter strap underneath both of the longer straps and upwards as shown below…


Step 9.

Take the right strap and move it back over itself to the right as shown…


Step 10.

Thread the strap under both the short and long straps and diagonally upwards as shown…


Step 11.

Place the strap along the diagonal line of the long strap but thread it under the existing hoop from the left side, if its too long, tuck it under.


And that’s that.

Hope this helps someone.

Technorati Tags: , , , ,, , ,,

How to configure Tomcat 5.0.x to use Java Logging

If you are using java logging and a version of tomcat previous to tomcat 5.5, you have to use the deprecated Logger declarations in your server.xml or
context.xml files. The are deprecated in tomcat 5.0 and have actually been removed in tomcat 5.5.

This is used to achieve separate log files for separate virtual hosts/web applications for example in your server xml you may have…

<Host name="" debug="0" appBase="webapps/myapp"<br />
      unpackWARs="true" autoDeploy="true"<br />
      xmlValidation="false" xmlNamespaceAware="false">
      <Logger className="org.apache.catalina.logger.FileLogger"<br />
              directory="logs"  prefix="myapp." suffix=".log"<br />

This will log the myapp.timestamp.log file with all the contents of the webapp “myapp”.

If you are using log4j you can simply omit this and use the (or log4j.xml file) within your webapp to specify what the logging levels are and where
to log to etc.

Java logging lacks this functionality. The java logging configuration file logging.properies is a jvm-wide properties file, and therefore traditionally
you would have to use the Logger feature to get separate log file per webapp.

In tomcat 5.5 apache introduced juli which is an implementation of the java logger that addresses the shortcomings of the file.
i.e. with tomcat 5.5 and above you can have a file within your webapp.

Now if you are tied to tomcat 5.0 for your deploys you can actually add juli logging to tomcat 5.0 quite easily, here’s what you do…

Firstly download the tomcat 5.5 core binary distribution. (from
From this file extract the following two files…

apache-tomcat-5.5.25/bin/tomcat-juli.jar into jakarta-tomcat-5.0.28/bin
apache-tomcat-5.5.25/conf/ into jakarta-tomcat-5.0.28/conf

Next we need to edit our catalina startup script to tell java logging about the juli logging jar and properties file.

if you are using a flavour of linux/unix edit jakarta-tomcat-5.0.28/bin/

and under the cygwin section add the following…

# Set juli LogManager if it is present
if [ -r "$CATALINA_BASE"/conf/ ]; then
 JAVA_OPTS="$JAVA_OPTS "-Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager" "-Djava.util.logging.config.file="$CATALINA_BASE/conf/"

and then after the classpath section add…

if [ -r "$CATALINA_HOME"/bin/tomcat-juli.jar ]; then

if you are using windows edit jakarta-tomcat-5.0.28/bin/catalina.bat

After the setenv.bat section add…

if not exist "" goto noJuliProps
set JAVA_OPTS=%JAVA_OPTS% -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager -Djava.util.logging.config.file=""

and then after the classpath section add…

if not exist "%CATALINA_HOME%bintomcat-juli.jar" goto noJuli

And voila that’s it!

All you need now in your webapp is add your own specific file to the WEB-INF/classes directory of the war, an example of which….

####### --------- --------- #############
handlers = org.apache.juli.FileHandler, java.util.logging.ConsoleHandler

# Handler specific properties.
# Describes specific configuration info for Handlers.



Technorati Tags: , , , ,

Clob Handling Made Easy in Java with Oracle 10g

I found out a pretty neat thing last week about the JDBC Driver for Oracle 10g.

Previously when I’ve had to use a CLOB (Character Large OBject) in Java using Oracle

I’ve had to use the Oracle specific classes, rather than use the java.sql classes.



PreparedStatement stmt = con.prepareStatement(sql);
stmt.setString(1, primaryKey);
ResultSet res = stmt.executeQuery();

oracle.sql.CLOB clob = (oracle.sql.CLOB)res.getClob(1);

Then you had to use methods on the CLOB class that are not available on javax.sql.Clob to stream the bytes to / from the database.
If you tried to use the methods that should have been used on the javax.sql.Clob class you got some sort of error, an Unsupported Driver Operation or the like (Haven’t done this for a while and my memory’s a bit rusty on it)

Since the 10g driver the oracle CLOB and BLOB classes both implement the jdbc interface properly, so you can just use the jdbc classes/interfaces
instead of putting oracle specific classes in your code.

This allows you to use

javax.sql.Clob clob = res.getClob(1);

and for writing using the jdbc methods…

out = clob.setAsciiStream(0);

and for reading …

in = new BufferedInputStream(clob.getAsciiStream());

Recently I’ve had to use a clob again, and I have always wondered why can’t I just go stmt.setClob() and rs.getClob() without having to use a load of byte streaming code.
Well the nice guys n gals at Oracle have done something similar for the 10g driver which I think is fantastic.

No more selecting for update, all you do is use the setString and getString methods on PreparedStatement and ResultSet.

You will need to set some oracle specific settings in the driver properties if you want to put data in that’s bigger than 32k.

I found this information out here

Nice one oracle, its about time 🙂 I wonder if they’ve done something similar for Blob handling!!

Technorati Tags: , , , ,

@Deprecated: How to Inject Spring Beans into Servlets.

This post has been Deprecated, see this post instead

A couple of months ago I was working on an application that used Servlets as entry points to an application, sort of like a web service. This was a new application and I decided to use the Spring framework for dependancy injection and to make using Hibernate easier.

I am fairly new to Spring and never needed to inject any spring beans into servlet before, and I thought there must be a way to do it. However after browsing through a number of websites, blog posts and forum posts, it appeared that there wasn’t a clean spring way to do this.

Solution 1

In the end I read somewhere that you could inject spring beans into the ServletContext, so I took this route.

With this you have to declare this little piece in your applicationContext.xml

<bean class="">
 <property name="attributes">
         <!-- inject the following beans into the servlet
context so the servlets can access them. -->
         <entry key="myBeanFromSpring">
             <ref bean="myBeanFromSpring"/>

As you can see this puts the spring bean myBeanFromSpring into the servlet context. Therefore in your servlet code you can do the following…

protected void doGet(HttpServletRequest reqest, HttpServletResponse response)
                                         throws ServletException, IOException {

   MyBeanFromSpring myBean = MyBeanFromSpring)getServletContext().getAttribute("myBeanFromSpring");

Although this works it still doesn’t feel very spring like.

Solution 2

There is another way to achieve the same thing. You can use WebApplicationContext and get the beans directly from Spring without having to inject anything into the servlet context.

void doGet(HttpServletRequest reqest, HttpServletResponse response)
                                         throws ServletException, IOException {

   WebApplicationContext springContext =
   MyBeanFromSpring myBean =(MyBeanFromSpring)springContext.getBean("myBeanFromSpring");

Although this achieves the same thing and is probably more concise than Solution 1, it still is not achieving what I initially wanted, which was dependancy injection into the servlet.

Solution 3

Although I stayed with Solution 1 for the application it got me thinking. So I set out to write a sub class of HttpServlet that would use the servletContext solution and use reflection to figure out if the servlet had any setters on that spring should call.

My original servlet that had to do all the stuff with getting the servlet context suddenly looks a lot more spring-like…

import name.kayley.springutils.SpringDependencyInjectionServlet;

public class MyServlet extends SpringDependencyInjectionServlet {

   private MyBeanFromSpring myBean;

   public void setMyBeanFromSpring(MyBeanFromSpring myBean) {
       this.myBean = myBean;

   protected void doGet(HttpServletRequest reqest, HttpServletResponse response)
                                           throws ServletException, IOException {


And here is the source of the SpringDependencyInjectionServlet, go easy on it, Its the first version and it passes the unit tests i have written for it, use it at your own risk or as a starting point, but like I said it appears to work ok so far.

I have attempted to keep in with the spring autowiring of giving 2 options, autowire by type and by name, so you can override autowireByType if you want to autowire by name. I think they need some work as I believe that autowire by name should go off the name of the parameter to the setter method and not the setter method name itself… keep coming back for updates.

package name.kayley.springutils;

import java.lang.reflect.Method;
import java.util.Enumeration;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringUtils;

public class SpringDependencyInjectionServlet extends HttpServlet {

   private static final Logger logger =

   protected void service(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
       Enumeration attributeNames = getServletContext().getAttributeNames();

       while (attributeNames.hasMoreElements()) {

           String name = (String) attributeNames.nextElement();
           logger.log(Level.INFO, "attempting to autowire " + name);


       super.service(request, response);

   protected boolean autowireByType() {
       return true;

   private void autowire(String name) {
       if (name != null) {
           Object attribute = getServletContext().getAttribute(name);
           Class c = getClass();
           while (c != null && c != c.getSuperclass()) {
               try {
                   if (autowireByType()) {
                       if (byType(c, attribute)) {
                       else {
                           c = c.getSuperclass();
                   else {
                       if (byName(c, name, attribute)) {
                       else {
                           c = c.getSuperclass();
               catch (NoSuchMethodException e) {
                   c = c.getSuperclass();

   private boolean byName(Class c, String name, Object attribute)
       throws NoSuchMethodException {
       boolean success = false;

       if (attribute != null) {

           Method[] methods = c.getDeclaredMethods();
           for (Method method : methods) {
               if (method.getName().equals(getMethodName(name))) {
                   Class[] paramTypes = method.getParameterTypes();

                   if (paramTypes.length == 1) {
                       success = invokeSpringBeanSetter(method, attribute);
       return success;

   private boolean byType(Class c, Object attribute) {
       boolean success = false;

       if (attribute != null) {
           Method[] methods = c.getDeclaredMethods();

           for (Method method : methods) {
               Class[] paramTypes = method.getParameterTypes();

               Class attributeClass = attribute.getClass();
               if (paramTypes.length == 1 &&
          paramTypes[0].equals(attributeClass)) {
                   boolean succeeded = invokeSpringBeanSetter(method,attribute);
                   if (!success && succeeded) {
                       success = succeeded;
       return success;

   private boolean invokeSpringBeanSetter(Method method, Object attribute) {
       boolean success = false;
       try {
           method.invoke(this, attribute);
           success = true;
       catch (Exception e) {
           // TODO do we care?
       return success;

   private String getMethodName(String contextName) {
       return "set" + StringUtils.capitalize(contextName);

Technorati Tags: , , , ,