Server side Prepared statements in MYSQL + Java

One thing that we generally forget when using MySQL prepared statements in Java is the setting up of the parameter "useServerPrepStmts". This variable must be set at the JDBC connection level to take advantage of MySQL server side prepared statements. Otherwise, though we follow all the standards of coding in writing client-side prepared statements, MySQL ultimately treats them as a regular statement only.

Below is an example datasource setting in JBOSS




SomeMySQLPool
true
jdbc:mysql://HOSTNAME:3306/SomeMYSQLDb
com.mysql.jdbc.Driver
NON ROOT USER NAME
PASSWD
true
org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter

mySQL

Multiple columns having default TIMESTAMP

Based on the last two statements in the above section, we can get a workaround for this (date_modified, date_modified) combination problem, where both the columns should default to sysdate.

Follow the below example.

mysql> create table multicolsdefaultts (name varchar(100),date_modified timestamp,date_added timestamp);
Query OK, 0 rows affected (0.03 sec)

mysql> show create table multicolsdefaultts;

CREATE TABLE `multicolsdefaultts` (
  `name ` varchar(100) NULL,
  `date_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `date_added` timestamp NOT NULL default '0000-00-00 00:00:00'
)

mysql> insert into multicolsdefaultts values ('sujay', null,null);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from multicolsdefaultts;
+-------+---------------------+---------------------+
| name  | date_modified       | date_added          |
+-------+---------------------+---------------------+
| sujay | 2007-01-09 23:52:31 | 2007-01-09 23:52:31 |
+-------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> update multicolsdefaultts set name='sujay1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from multicolsdefaultts;
+--------+---------------------+---------------------+
| name   | date_modified       | date_added          |
+--------+---------------------+---------------------+
| sujay1 | 2007-01-09 23:52:44 | 2007-01-09 23:52:31 |
+--------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> insert into multicolsdefaultts(name) values ('Andale');
Query OK, 1 row affected (0.00 sec)

mysql> select * from multicolsdefaultts;
+--------+---------------------+---------------------+
| name   | date_modified       | date_added          |
+--------+---------------------+---------------------+
| sujay  | 2007-01-09 23:52:31 | 2007-01-09 23:52:31 |
| Andale | 2007-01-30 17:02:01 | 0000-00-00 00:00:00 |
+--------+---------------------+---------------------+

mysql> insert into multicolsdefaultts(name, date_added) values ('Andale2', null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from multicolsdefaultts;
+---------+---------------------+---------------------+
| name    | date_modified       | date_added          |
+---------+---------------------+---------------------+
| sujay   | 2007-01-09 23:52:31 | 2007-01-09 23:52:31 |
| Andale  | 2007-01-30 17:02:01 | 0000-00-00 00:00:00 |
| Andale2 | 2007-01-30 17:12:01 | 2007-01-30 17:12:01 |
+---------+---------------------+---------------------+

The important things to remember here are

• DATE_MODIFIED field should be declared before DATE_ADDED is declared during the table creation. Else provide CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP for the default value.
• The date columns should be inserted with 'NULL' and should not be left out to be filled with default value. If left out then DATE_ADDED will not have appropriate date but "0000-00-00 00:00:00"

• MySQL gives warnings or errors if you try to insert an illegal date. But by using the ALLOW_INVALID_DATES SQL mode, we can still store illegal dates.

•  CREATE TABLE t (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 

  . This allows to update the column to currenttime during row creation as well as updation.

MySQL's workaround for Oracle's ROWNUM

This is a common question that come across the minds of lot of developers especially in the initial stages of their migration from oracle to MySQL.

MySQL's equivalent to Oracle's rownum is limit.
Limit takes two numeric arguments, the first argument is the offset of first row to return (offset of the first row in the results starts from 0 and not 1) and the second argument takes the maximum number of rows to return from the specified offset.

e.g: where rownum between 10 and 20 <---> limit 10,20

One other common use of rownum in oracle to copy the table structure.
e.g: create table mytable_copy as (select * from mytable where rownum <0);

The equivalent in MySQL is

create table mytable_copy as (select * from mytable limit 0);

For more information, please refer to this link on MySQL website.

http://dev.mysql.com/doc/refman/5.1/en/select.html

Hope this helps. Please contact me in case of any queries.