Techie Tortoise

A lot of people overlook this important attribute "idMethod" in the "table" tag of the torque schema file. This determines whether you are using the native database generated sequential key.

For instance, in MySQL,
(1) if you want your primary key to be auto-incremented by the DB, you should set the idMethod to be "native";
(2) If you want to define your own primary key, you should set it to "none";
(3) If the primary key mades up of 2 or more primary keys from other tables, you should also set it to "none".

If wrongly set, you will get "funny" errors :
in case (3), if you set the "idMethod" to "native", you will get "Duplicate Key or Foreign Key Constraint Error" when you try to insert to the table.

If for most of your table, the primary key is to be auto-incremented, you can put the defaultIdMethod="native" in the "database" tag. In this case, you only need to put idMethod="none" in the respective tables.

Another lesson learnt.

If the java type of your primary key "id" is of a primitive type int, long, double, etc, the "unsaved-value" attribute must be filled with a value. For instance, if you declare your primary key in your model class as "private int key;", then your unsaved-value should be 0. If you declare your primary key as "private int key = -1;", then your unsaved-value should be -1;

Otherwise, you will get this error when you are trying to save or update the object, "Batch update row count wrong: 0..."

The above is also explained in the FAQ.

JavaWorld has announced that there will be no more updated articles. I am hoping that it is just a temporarily move.

Topic: DB Object Mapping

I just downloaded Hibernate 2.1.2 and was trying out the tutorial in the reference pdf file. As instructed, I copied the following to my webapp lib directory: cglib-2.0-rc2.jar, commons-collections-2.1.jar, commons-dbcp-1.1.jar, commons-lang-1.0.1.jar, commons-logging-1.0.3.jar, commons-pool-1.1.jar, dom4j-1.4.jar, hibernate2.jar, log4j-1.2.8.jar, odmg-3.0.jar. I must admit that I did not read the "Readme.txt" file in the lib directory of the hibernate distribution.

When I start my tomcat, the debug and info level log looks normal and there is nothing in my error log. However, my JSP throws "org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:536)". Seriously this exception message is not at all helpful. I have to insert more log4j statement all over the sample code "HibernateUtil". Finally, I found the following exception message thrown "could not instantiate CacheProvider: net.sf.ehcache.hibernate.Provider".

From a google search on the error message, it leads me back to the Hibernate FAQ which points out that the library "ehcache.jar" is needed from Hibernate 2.1 onwards. This really teach me a lesson to read the FAQ first. However, in the first place, the exception is not really logged properly, which could have left me clueless.

Topic: DB Object Mapping

Found this site where someone makes some comparison on the various Object mapping tools.

Topic: DB Object Mapping

Came across another object-mapping tool called iBATIS. This tool is abit different as it maps JavaBeans to SQL statement instead of the table schemas.

For instance,
<mapped-statement name="getAddress"
result-class="examples.domain.Address">
select
ADR_ID as id,
ADR_DESCRIPTION as description,
ADR_STREET as street,
ADR_CITY as city,
ADR_PROVINCE as province,
ADR_POSTAL_CODE as postalCode
from ADDRESS
where ADR_ID = #value#
</mapped-statement>

Topic: DB Object Mapping
Here's a good introduction article to Hibernate based on version 2.0.3.

I was trying to do some comparison between Torque, Hibernate and OJB. However, later I realise that Torque is out of the comparison as it is something different from the rest. It is more like another persistence layer between the database and the other two. I will write more on this topic when I read up further on Hibernate.

Update: Another good introduction article on version 2.1

Topic: MySQL

Usually for String data, we would use VARCHAR data type. However, in my MySQL, do take note that if we use VARCHAR, the data stored would be case-insensitive. For case-sensitivity, you have to use VARCHAR BINARY.

For instance, if you have the following table schema:

CREATE TABLE MEMBER (
 LOGIN_T VARCHAR(20) PRIMARY KEY,
 PWD_T VARCHAR(20)
);

and the following records:

abc|abc
def|def

In this case both the login ID and the password are NOT case-sensitive, if the user attempts to log in using login ID "ABC" and password "ABC", he will be allowed to proceed.

You may want to change your create table script to the following to make the login ID and password case-sensitive:

CREATE TABLE MEMBER (
 LOGIN_T VARCHAR(20) BINARY PRIMARY KEY,
 PWD_T VARCHAR(20) BINARY
);

Topic: MySQL

Major Benefits (over ISAM and MyISAM):

* transaction-safe
* increase multi-user concurrency and performance
* supports Foreign key constraints
* supports auto-extending tablespace

Foreign Key Constraints:
Some points to take note while creating foreign key constraints

* Both tables must be InnoDB type
* Corresponding columns must have the same datatype. Integer must have the same size and signedness. H/r string length need not be the same.
* Index on the column must be first created before creating the foreign key.

Example:
MBRTP - Member Type
CREATE TABLE MBRTP
(
MBR_TP_C INTEGER (1) NOT NULL AUTO_INCREMENT,
MBR_TP_T VARCHAR (20) NOT NULL,
PRIMARY KEY(MBR_TP_C)
) TYPE=InnoDB;

MBRSTS - Member Status
CREATE TABLE MBRSTS
(
MBR_STS_C INTEGER (1) NOT NULL AUTO_INCREMENT,
MBR_STS_T VARCHAR (20) NOT NULL,
PRIMARY KEY(MBR_STS_C)
) TYPE=InnoDB;

MEMBER - Membership
CREATE TABLE MEMBER
(
LOGIN_T VARCHAR (10) NOT NULL,
PWD_T VARCHAR (40) NOT NULL,
MBR_TP_C INTEGER (1) NOT NULL,
MBR_STS_C INTEGER (1) NOT NULL,
NM_T VARCHAR (100) NOT NULL,
EMAILADDR_T VARCHAR (100) NOT NULL,
CREATE_D TIMESTAMP NOT NULL,
CHG_PWD_F VARCHAR (1) NOT NULL,
PRIMARY KEY(LOGIN_T)
) TYPE=InnoDB;

Foreign Key constraint to MEMBER
ALTER TABLE MEMBER ADD INDEX IDX_MBR_TP (MBR_TP_C);
ALTER TABLE MEMBER ADD INDEX IDX_MBR_STS (MBR_STS_C);
ALTER TABLE MEMBER ADD CONSTRAINT FOREIGN KEY FK_MBR_TP (MBR_TP_C) REFERENCES MBRTP (MBR_TP_C);
ALTER TABLE MEMBER ADD CONSTRAINT FOREIGN KEY FK_MBR_STS (MBR_STS_C) REFERENCES MBRSTS (MBR_STS_C);
Tips

* When you hit an error while creating foreign key reference, use SHOW INNODB STATUS to look at a detailed explanation. You have to use an account with the privilege.
* Use SHOW CREATE TABLE <TABLE-NAME> to see the internally generated foreign key id. You will need this foreign key id when you want to drop the foreign key.
* Use SHOW TABLE STATUS FROM <DB-NAME> LIKE '<TABLE-NAME>' to see the list of foreign key constrains in your table.

Reference

* http://www.mysql.com/doc/en/InnoDB.html
* http://www.innodb.com