Friday, July 31, 2009

One to Many Relational Mapping in ejb ( usage of jpa)

In this example, i use customer / order scenario. One customer can have many orders.
There is a one to many realtionship between customer and order entity classes.
The two tables are CUSTOMER and ORDER_TABLE.
Here is the schemas of the tables.
create table CUSTOMER (
ID NUMERIC(10) PRIMARY KEY,
NAME VARCHAR(256)
);

create table ORDER_TABLE (

ORDER_ID NUMERIC PRIMARY KEY,
SHIPPING_ADDRESS VARCHAR(500),
CUSTOMER_ID NUMERIC(10),
FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (ID)

);


There is a primary key and foreign key mappings between tables.
Here is the entity classes.
Customer.java
import java.io.Serializable;
import javax.persistence.*;
import static javax.persistence.CascadeType.*;
import java.util.Collection;
import java.util.ArrayList;

@Entity

public class Customer implements Serializable {
private int id;
private String name;
private Collection<Order> orders = new ArrayList<Order>();

@Id
public int getId() {
return id;
}

public void setId(int id) {
this.id = id;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

@OneToMany(cascade = ALL, mappedBy = "customer")
public Collection<Order> getOrders() {
return orders;
}

public void setOrders(Collection<Order> newValue) {
this.orders = newValue;
}

}

Order.java
import javax.persistence.*;
@Entity
@Table(name = "ORDER_TABLE")
public class Order {

private int id;
private String address;
private Customer customer;

@Id
@Column(name = "ORDER_ID")
public int getId() {
return id;
}

public void setId(int id) {
this.id = id;
}

@Column(name = "SHIPPING_ADDRESS")
public String getAddress() {
return address;
}

public void setAddress(String address) {
this.address = address;
}

@ManyToOne()
@JoinColumn(name = "CUSTOMER_ID")
public Customer getCustomer() {
return customer;
}

public void setCustomer(Customer customer) {
this.customer = customer;
}
}


Suppose you are going to insert two orders for a customer, here is the insert(); This is a method that should be in your session bean.
public void insert() {

Customer cust = new Customer();
cust.setId(2);
cust.setName("sujith nishantha");

Order order1 = new Order();
order1.setId(300);
order1.setAddress("123 Main St. Anytown, Sri Lanka");

Order order2 = new Order();
order2.setId(400);
order2.setAddress("567 1st St. Random City, Sri Lanka");

cust.getOrders().add(order1);
order1.setCustomer(cust);

cust.getOrders().add(order2);
order2.setCustomer(cust);
em.persist(cust);
}

This will insert 2 orders for a cutomer.
Here is the select a customer. This method will return a customer object that consists of 2 orders.
public void select() {
Query q =
em.createQuery("select c from Customer c where c.name = :name");
q.setParameter("name", "sujith nishantha");
Collection col = q.getResultList();
System.out.println("size ..... "+col.size());
}

Sunday, July 19, 2009

sql of no of count in a value

select mobile,count(mobile) from table group by mobile

This will return no of counts for a mobile number.

Friday, July 17, 2009

Date comparison in sql

Here is the sql for date comparison.

select * from table where active_until > added_on

Wednesday, July 15, 2009

jsp session

Add this code to the top of the jsp page.

<%@ page import="java.util.*" session="true"%>

Then you can get the session id of the browser and the server.

This id is unique for a browser window.

<% String sessionId=session.getId(); %>

And, you can aloso set your own variable for session. There is a method as "setAttribute".

Use this to set your own attribute for a session.

session.setAttribute("user",username);