Thursday, September 10, 2009

JPA annotation - @GeneratedValue

Test

JPA annotation “@GeneratedValue” is the way to make insert operation return the auto-incremented ID (Sequence/IDENTITY) of the inserted record.

While working with MySql, you must have used auto-increment property to maintain unique values in the “ID” column of the table. If you are Oracle user, you might have used sequences.(MySql do not have sequences yet.) Sequences are more powerful way of keeping records unique in the table or across multiple tables, and use of sequences is not limited to the tables.

Let’s now see how Object Relational frameworks deal with auto-incremented values or sequences.
Assume, I have a table named "Task" in database. “Task” table has three columns - ‘ID’, ‘Name’ and ‘DueDate’, where ‘ID’ is an auto-increment field. Let’s say “Task” table is mapped to the “Task POJO (Plain Old Java Object)” by some Object-Relation framework such as Hibernate. If I want to insert "Task POJO" into the database, I can simply set the “Name” and “DueDate” using bean methods “setName” and “setDueDate” of the “Task POJO”. It is not required to set the “ID” because “ID” is an auto-incremented field of the table.We want Insert operation to return the value of the auto-incremented ID which is not known until record is actually inserted into the database (because database assigns the auto-incremented value). So, to achieve this, OR frameworks do one simple trick. Once “Task POJO” is inserted into the database, ID property of the “Task POJO” will get set for you. If you are using JPA annotations, you need to set the @GeneratedValue annotation along with the @Id annotation on ID field of the POJO (Entity). Only one @GeneratedValue annotation per POJO or Entity is allowed.

This is how it looks like in code,

@Entity
public class MyEntityimplements Serializable {
@Id
@GeneratedValue
private Long id;
}


More annotations later, stay tuned Wave


Tuesday, January 27, 2009

Storage engine of MySql: MyISAM and InnoDB

Any DBMS software has at least Query parser, Front-end client, and storage engine. Storage engine is the core part of the DBMS system where your tables resides.

With MySQL, we can change the underlying storage engine -
http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html

"MyISAM manages non-transactional tables. It provides high-speed storage and retrieval, as well as fulltext searching capabilities. MyISAM is supported in all MySQL configurations, and is the default storage engine unless you have configured MySQL to use a different one by default."

MyISAM is high-speed and manages non-transactional tables.
But, If you want Transactional support and Primary/Foreign key support you need to use InnoDB.

Also look out Falcon storage engine with Mysql 6.0 which is both high-speed and provides transactional support and other functionality - http://dev.mysql.com/doc/refman/6.0/en/se-falcon.html

InnoDB table example: (ENGINE=InnoDB at the end of create statement)

CREATE TABLE `databasename`.tablename'
(
id int PRIMARY KEY NOT NULL,
name varchar(40),
) ENGINE=InnoDB;

Before running the above script, make sure INNODB support is enabled for your MySQL server
Go to Installation Dir\bin\my.ini
Comment out -- #skip-innodb
Also make "default-storage-engine=INNODB" in the same file so you do not have to type ENGINE=InnoDB in each CREATE statements.