Monthly Archives: June 2016

Working with Collections in Hibernate

Posted on by

While working with collection if you want the details to be stored in a seperate table using @ElementCollection solves the purpose

UserDetails.java

 public class UserDetails {
  @ElementCollection
  private List<Address> arrList = new ArrayList<Address>();

 }

Address.java

@Embeddable
public class Address 
{
  private String DoorNo;
  private String Street;
  private String Location;
  private String Pincode;
  .
  .
  .
 }

createTables.java

public class CreateTables {   
  public static void main(String[] args) 
  {
    UserDetails objUserDetail1 =  new UserDetails();

    Address objAddr = new Address();
    objAddr.setDoorNo("13");
    objAddr.setStreetName("Poes Road");
    objAddr.setLocation("Teynampet");
    objAddr.setPincode("600018");
    
    
    Address objAddr2 = new Address();
    objAddr2.setDoorNo("256");
    objAddr2.setStreetName("Sriman Srinivasan Road");
    objAddr2.setLocation("Alwarpet");
    objAddr2.setPincode("600018");
    
    objUserDetail1.getArrList().add(objAddr);
    objUserDetail1.getArrList().add(objAddr2);
  }
}

Giving Names to Joined Tables

  • New Table will be created under USER_ADDRESS Name
  • joinColumns decides which column should be used for joining two tables

Format for Join Table

  @JoinTable(name="JOIN_TABLE_DESIRED_NAME", 
	     joinColumns= @JoinColumn(name="userId"))

  @JoinTable(name="USER_ADDRESS", 
	     joinColumns= @JoinColumn(name="userId"))
  private Set<Address> addressSet = new HashSet();
  .
  .

Generating Unique Next ID in Process

Posted on by 

FOR cur IN (SELECT  process_Id 
              FROM  tbl_process)
LOOP
  SELECT (MAX(TO_NUMBER(substr(p.old_id, instr(p.old_id, '_', -1) + 1))))
    INTO new_id
    FROM tbl_process p
   WHERE process_Id = cur.process_Id;

  IF new_id IS NULL THEN
    new_id := cur.process_Id || '_1';
  ELSE
    new_id := new_id + 1;
    new_id := cur.process_Id || '_' || new_id;
  END IF;
END LOOP;

NULL or BLANK fields check in Oracle

Posted on by

If you want a query which returns rows in column containing NULL or empty space then the using NULL alone in where clause will not return empty space rows. So the query should be altered as below

Actual Query

 SELECT COUNT (*) 
   FROM TABLE 
  WHERE COL_NAME IS NULL;

Altered Query

  SELECT COUNT (*) 
    FROM TABLE 
   WHERE trim(COL_NAME) IS NULL

(or)

Altered Query

  SELECT COUNT (*) 
    FROM TABLE  
    WHERE COL_NAME IS NULL OR 
          LENGTH(TRIM (COL_NAME)) = 0