fix for the performance problem of mysql memory tables

In our project, a very high database performance is needed. Thus we create our critical tables as memory engine tables but although our queries are using corresponding indices we still can't achieve the needed performance. After few research in google I see that tables of memory type are having "hash" indices by default. After we drop the "hash" indices and replace them with "b-tree" ones, we had a much better performance in our project. So for those of you with performance problems using memory tables, I recommend trying b-tree indices.

problem while using dbcp with oracle database

We have two different databases in a project; an Oracle and a MySql one. Both of them need connection pooling for a better performance. First, I add dbcp context for mysql to my application context. The settings were quite straightforward. But when I try to use the same settings (except for the connection url and the driver class name of course) for the Oracle data source I got the following exception:

org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (ORA-00923: FROM keyword not found where expected

which can be translated as "there's some FROM statement missing somewhere while setting your connection". My validation query for MySql was just "SELECT 1" which annoys Oracle database. I change it to "SELECT 1 FROM DUAL" and it's fixed.

datetime fun with mysql: when do i lost my time?

In our project, we chose the data type of a column as datetime in MySQL. We built models that are in line with our tables. For the datetime attribute in the database I chose to use the Date object in Java model and the show begun.

When I tried to use my model's Date attribute for setting the corresponding field (with setDate()) on the prepared statement I saw that the object types dont match. What I have in the model is java.util.Date while the prepared statement asks for java.sql.Date. Ok then I'll convert it. I googled a bit and found a solution, or rather thought that I found a solution.

ps.setDate(1, new java.sql.Date(model.getDateField().getTime()) )

First I was converting to a millisecond unix timestamp with getTime() and then converting it to second (by dividing with 1000) and then wrapping the result into a java.sql.Date object.
The result was far from satisfying. The time part of the date (hour, minute and second) were missing in the result. The date in the model was '2010-10-26 14:13:33' and the result in the database was '2010-10-26 00:00:00'.

After that I googled a bit more and inspected few javadocs then came up with not one but two solutions.

SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
ps.setString(1, dateFormat.format( model.getDateField() ));

The first solution creates a SimpleDateFormat object which formats the Date attribute of the model as a String and we use setString() of the prepared statement for setting it.

The second solution is below.

ps.setTimestamp(1, new java.sql.Timestamp(model.getDateField().getTime()));

We first get the Date attribute and then convert it to unix timestamp in milliseconds for wrapping it into a Timestamp object and call the related setter.

I didn't notice major performance difference in one of the solutions I offer which means that you have to test it in your cases.