Showing posts with label MySQL. Show all posts
Showing posts with label MySQL. Show all posts

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.

Monday, January 28, 2008

Difference between N:1 and 1:N association from O/R mapping framwork and database point of view?

Well, you will say it's same and you are right! Still, I want to write something .

Database: 1:N = Inverse (N:1) ?
In database, we can map "One to Many" and "Many to one" relationship between tables by creating Foreign key (FK) column for table on the "Many" side of the relationship.

Say for example, we want to map "One to Many" (1:N) relationship between Customer and Order. "One customer can have multiple Orders. But one Order can have only one Customer associated"

Which table is on the "Many" side of the relationship? - Order table. So we can create Foreign Key(FK) in Order table pointing to Primary key(PK) in Customer table.

You have created both 1:N (From Customer to Order) and N:1 ( From Order to Customer) mapping in your database!!!

Query - "Give me all the order names for the given customer name"?
- Select order_name from Order,Customer where customer_name="jay";

The above query is nothing but just a INNER JOIN. Is there any better way to write same query?--(Have you heard of Lazy Loading?)

OR Mappers : 1:N = Inverse (N:1) ?

Order class: (JPA Annotations used)
@ManyToOne
@JoinColumn(name="customer_id") //customer_id is a FK field in Order table.
public Customer getCustomer();
public void setCustomer(Customer customer);

You have mapped "Many to One" relationship, but not "One to Many" yet.

you have two options to get the data using Order.getCustomer() :: Lazy Loading(On demand) or JOIN(Eager Loading/Pre-Loading/Pre-Fetching)

Customer class: (JPA Annotations used)
@OneToMany(mappedBy = "customer_id") //customer_id is a FK field in Order table.
List getOrders();
void setOrders(List orders);

Now you have mapped "One to Many" relationship. You are ready to traverse in both way now from Customer -> Order and Order -> Customer in your code.

Under the cover, OR mapppers use Primary/Foreign key mechanism to map 1:N and N:1 relationship..but please do not change your annotation blindly in your POJO "@ManyToOne" != "@OneToMany"

Little bit more hammering:>>>>>> If you want to map many-to-many relationship in between two database tables, you will need one Associate table or JOIN table. This JOIN table will have two foreign keys pointing to the primary key of the two entity tables. So you will have Many-to-One mapping from JOIN table to each entity table.

Sunday, January 27, 2008

Convert MyISAM to INNODB: mysqldump

"C:\>mysqldump -h localhost -u username -p databasename > filename.sql"
Enter password: ****

This will dump your mysql database with structure of all the tables and their content.

If you want to convert your tables from MyISAM to INNODB, you can do "find MyISAM and replace with INNODB" in this filename.sql before importing into database again.

Make sure you have INNODB support enabled for your MySQL server 5.0
- Open file MySQL Server 5.0\bin\my.ini and comment out "skip-innodb"
- AND Don't forget to restart your server after the change.

To import your dump file into database:
"C:\> mysql -h localhost -u username -p databasename < filename.sql " Enter password: **** I am not sure if this is a reliable way for the production databases. -:)
More Info on mysqldump: http://www.devshed.com/c/a/MySQL/Backing-up-and-restoring-your-MySQL-Database

mysql> show create table 'tablename'

mysql> show create table 'tablename' -- is good query (atleast for me) to know about table and which storage engine/charset it uses (InnoDB or MyISAM).

mysql> show create table task
-> ;
+-------+--------------------------------------------------
-----------------------------------------------------------
-----------------------------------------------------------
-----------------------------------------------------------
-------------------------+
| Table | Create Table



|
+-------+--------------------------------------------------
-----------------------------------------------------------
-----------------------------------------------------------
-----------------------------------------------------------
-------------------------+
| task | CREATE TABLE `task` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(40) default NULL,
`description` varchar(100) default NULL,
`tasktype` int(10) default NULL,
`startdatetime` datetime default NULL,
`enddatetime` datetime default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------
-----------------------------------------------------------
-----------------------------------------------------------
-----------------------------------------------------------
-------------------------+
1 row in set (0.00 sec)





I have started using SQuirreL SQL client which is an universal client supporting many database systems like db2, oracle, mysql, sybase, and more..
Give it a try: http://squirrel-sql.sourceforge.net/