Saturday, 22 September 2012

Hibernate Annotation One-to-One Mapping Example

While the Hibernate documentation section 2.2.5 is a good place to start for a concrete example for an example of a one-to-one annotation based relationship mapping, the example below describes a couple of scenarios and the annotations that worked in setting up the relationships.

Give below is an example of  a One-to-One  bi-directional relationship between a User object and a Registration object,

In the User object, set up the annotations as follows :

@Entity
@Table(name = "user")
public class User implements Serializable {
........
........
private UserRegistration userRegistration;
.........
  @OneToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
  @JoinColumn(name = "USR_REG_SYS_ID")
   public UserRegistration getUserRegistration() {
        return this.userRegistration;
    }
.........
} // End of User class

The JoinColumn annotation refers to the to the USER_REGN_ID column in the User table that has a Foreign Key reference to the User_Registration table.

In the UserRegistration object, set up the annotation as follows:

@Entity
@Table(name = " userRegistration")
public class UserRegistration implements Serializable {

........
........
private User user;

........
@OneToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER, mappedBy = "userRegistration")
 public  User  getUser() {
        return this.user;
 }

The mappedBy annotation implies that the userRegistration attribute in the User object manages the relationship reference between the objects. In other words, the User table holds the (Foreign) key reference to the relationship.


Wednesday, 19 September 2012

Selecting duplicates in a database table

Selecting duplicates in a database table can be accomplished in various ways and all these approaches involve various trade-offs with respect to efficiency and speed but before we start examining some of these approaches we need a table with duplicates so here is one. The Primary Key in this table is the ID and the Emp_Id is meant to be Unique and will be used for filtering duplicates.


Listing the number of times each record occurs with duplicate rows listed once.

(1) select *, count(*) from employee group by Emp_Id;



Listing the number of times each record occurs with a listing of the first row in the group.

(2) select *, count(*) from employee group by Emp_Id having count(*) > 1;



To list all duplicate rows, the following query would work;


(3) select   a.* from
    employee as a
inner join
    (select Emp_Id
     from employee
     group by Emp_Id
     having count(*) = 2) as b
  on a.Emp_Id = b.Emp_Id;

Now our table has a Tele_No column and some of these numbers are incorrect

Selecting the duplicates using a self-join.

In this case, we need to select all records that have the same Emp_Id but a different Tele_No. We can use a simple self-join with the Tele_No and the column as the distinguishing key to separate our records.
Option 1

(4) select e1.* from employee e1, employee e2 where e1.Emp_Id = e2.Emp_Id and e1.Tele_No != e2.Tele_No order by e1.Id asc;




The exact same result could also be obtained with a sub-query as follows:
Option 2

(5) select e1.* from
employee e1 where e1.Emp_Id IN (
select e2.Emp_Id from employee e2
where e1.Emp_Id = e2.Emp_Id
and e1.Tele_No != e2.Tele_No
order by e1.Id asc;

In Option 1 & 2, we have the condition e1.Tele_No != e2.Tele_No that prevents us from getting a cartesian product of the records. If such a condition is not available then just use the Primary Key column as follows:


(6) select e1.* from
employee e1 where e1.Emp_Id IN (
select e2.Emp_Id from employee e2
where e1.Emp_Id = e2.Emp_Id
and e1.ID <> e2.ID
)
order by e1.Id asc;


If you are wondering whether a join or a sub-query would work best, it would ofcourse depend upon your query optimizer and the size of your tables but generally Joins are the way to go.

So, we have 6 records where the Emp_Id are the same but the Tele_Nos are different. To view these records in a tabular format we could modify (6) as follows.

(7) select e1.ID, e1.Tele_No,e2.ID, e2.Tele_no
from
employee e1, employee e2
         where e1.Emp_Id = e2.Emp_Id
               and e1.Tele_No != e2.Tele_No
               and e1.Id < e2.id
        order by e1.Id asc;



The change in the condition comparing the Primary Keys in the query above e1.Id < e2.id prevents a cartesian product.

Sunday, 16 September 2012

Setting up an Image Gallery in Joomla

Most Joomla based sites end up requiring an image gallery of some sort and while there are a variety of  options available, you are liable to run into trouble with Javascript conflicts between the Javascript version or library of your Image Gallery plug-in with the other Javascript libraries loaded by the other plug-ins or the template. One option is ofcourse to hack the php template and debugging the Javascript conflicts using a browser based debugger which could be a tricky as well as tedious exercise.
The other and more staright forward approach is to use a non-Javascript based image gallery plug-in. One such plug-in is the css-gallery plugin. The plug-in is easy to install and has a simple configurable interface which is well documented. It is also  possible to add captions and link to the gallery images. It took 5 minutes to set up the image gallery for the St John English School website. An example of the css-image gallery in action can be viewed here