MySQL exception of DATETIME value in JSP

There is a column type DATETIME and default value as “0000-00-00 00:00:00″ in MySQL. When Using JSP with JDBC query and get result via getString, there arise an error:

java.sql.SQLException: Value ’0000-00-00 ‘ can not be represented as java.sql.Timestamp

As far as I see, this is a new feature of JDBC since version 3.1. However I did not encounter (or I ignore) it for a long time.

The value of MySQL is not valid in JAVA and the solution is to add a parameter in connection. Reference is here: Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J

That is, add zeroDateTimeBehavior=convertToNull into JDBC connection. and it works. 🙂

Set MySQL default character set as UTF8 on CentOS

The default character set of MySQL installed in CentOS 5 is latin while a feasible one for CJKs is UTF-8.

mysql> SHOW VARIABLES LIKE '%character%';
+--------------------------+--------+
| Variable_name | Value |
+--------------------------+--------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
+--------------------------+--------+

To enable whole system UTF-8, we need to add the following codes in /etc/my.cnf:
[client]
...
default-character-set = utf8

[mysqld]
...
character-set-server = utf8

mysql> SHOW VARIABLES LIKE '%character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

There are lots of additional settings in my Gentoo box, but it seems we don’t need right now. Maybe it can be use later. enjoyt it. 😉

UPDATE
[120302] The parameter default-character-set = utf8 for server configuration in 5.1.61 (or 5.1) is deprecated. We should use character-set-server = utf8 instead.

Mysql CONCAT fields between char type and number type with JDBC

In the issue, if we concat a char field with Chinese characters and a number filed directly in JDBC’s sql query, the result returned by JDBC may lead Chinese words to be ‘blocked’ (which means the charset of the words is wrong). For some tries, the issue is due to the charset of number seems ISO-8859-1 but the charset of Chinese words are UTF8. And the solution is convert the words via cast function in SQL:

1
SELECT CONCAT( CHAR_FIELD, CAST( NUMBER_FIELD AS CHAR ) ) AS T ...

whereas the dispaly is correct. Enjoy it. 🙂