Techie Tortoise

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

Topic: JDBC

Here's an interesting and easy-to-understand article that talks about the different transaction types of a connection. It also explains terms like "Dirty Read", "Phantom Reads" and "Non-Repeatable Reads".

Here's an article on ANT's best practices.

I have decided to use this blog to keep track of some of the interesting technical articles and resources that I have read on the web. The articles that I "recommend" are usually quite simple to read and easy to learn as I am not very advanced with programming technologies.

Technologies that I have used in my recent project includes:

• Torque
• Struts
• LOG4J