LISTAGG vs XMLAGG

LISTAGG gives “java.sql.SQLException: ORA-01489: result of string concatenation is too long” due to limitation for VARCHAR2.

select
  CONCAT(CONCAT(t2.REF_NUM_TYPE,':'), (listagg(t2.REF_NUM,',') within group (order by t2.REF_NUM asc))) as REF_NUMS_BK
from
(
	select
		max(s.PIPELINE_TX_ID) as PIPELINE_TX_ID,
		rn.ref_num_type,
		rn.ref_num
	from 
		reference_numbers rn, 
		ouk_shipment_activity_cmpny s 
	where 1=1
	and rn.pipeline_tx_id = s.pipeline_tx_id
	and s.PIPELINE_TX_ID = '123132123123'
	group by
		rn.ref_num_type,
		rn.ref_num
	order by
		rn.ref_num_type,
		rn.ref_num
) t2
where 1=1
group by
  t2.ref_num_type

Therefore, XMLELEMENT can be workaround.

select
  CONCAT(CONCAT(t2.REF_NUM_TYPE,':'),(RTRIM(XMLAGG(XMLELEMENT(e,t2.REF_NUM,',').EXTRACT('//text()')).GetClobVal(),','))) AS REF_NUMS
from
(
	select
		max(s.PIPELINE_TX_ID) as PIPELINE_TX_ID,
		rn.ref_num_type,
		rn.ref_num
	from 
		reference_numbers rn, 
		ouk_shipment_activity_cmpny s 
	where 1=1
	and rn.pipeline_tx_id = s.pipeline_tx_id
	and s.PIPELINE_TX_ID = '123132123123'
	group by
		rn.ref_num_type,
		rn.ref_num
	order by
		rn.ref_num_type,
		rn.ref_num
) t2
where 1=1
group by
  t2.ref_num_type

It can be trim as well.

select
 CONCAT(
  CONCAT(t2.REF_NUM_TYPE,':'),
   SUBSTR(
    (RTRIM(XMLAGG(XMLELEMENT(e,t2.REF_NUM,',').EXTRACT('//text()')).GetClobVal(),',')
   ),1,1000
  )
 ) AS REF_NUMS
from
(
	select
		max(s.PIPELINE_TX_ID) as PIPELINE_TX_ID,
		rn.ref_num_type,
		rn.ref_num
	from 
		reference_numbers rn, 
		ouk_shipment_activity_cmpny s 
	where 1=1
	and rn.pipeline_tx_id = s.pipeline_tx_id
	and s.PIPELINE_TX_ID = '30277174594' -- 202501494 202498201 30277174594(too long)
	group by
		rn.ref_num_type,
		rn.ref_num
	order by
		rn.ref_num_type,
		rn.ref_num
) t2
where 1=1
group by
  t2.ref_num_type

Example for XMLAGG with order by

with ref_base as (
    select 
     max(a.PIPELINE_TX_ID) as ppl
    ,a.REF_NUM_TYPE
    ,a.REF_NUM
    from REFERENCE_NUMBERS a where 1=1
    and a.PIPELINE_TX_ID = '30277174594'
    group by a.REF_NUM_TYPE, a.REF_NUM
    order by a.REF_NUM_TYPE, a.REF_NUM
)
select
(
    select RTRIM((XMLAGG(XMLELEMENT(e, b.REF_NUM,',') order by b.REF_NUM).EXTRACT('//text()')).GetClobVal(),',') AS REF_NUMS
    from   ref_base b where  b.REF_NUM_TYPE = 'ED1'
    group  by b.REF_NUM_TYPE
) as REF_NUMS_ED1
from dual

Example for LISTAGG with limited elements

select
  LISTAGG(
   case
      when rownum <= 300 
      then (
        case
           when rownum = 300
           then rn.ref_num || ' MORE....'
           else rn.ref_num
        end
      )
      else null
   end,
   ','
  ) within group (order by rn.ref_num)
  as ref_nums
from reference_numbers rn
where rn.pipeline_tx_id = '123456'

Applying Many to Many

many-to-many-header-img1
This is based on this section:


In controller method, it creates a relation ship between RTRN_TP and RTRN_RSN
1. RTRN_RSN object set to HashSet that part of fileds of RTRN_TP
2. RTRN_TP object is saved
many-to-many1
RTRN_RSN of “006”, “010”, and “011” have been bound to RTRN_TP of “01”
many-to-many-data2

@RequestMapping(value = "/cUrlValAttrbSubTpRsn01Jsp", params = "toUnBoundRsnList", method = RequestMethod.POST)
public ModelAndView toUnBoundRsnList(@ModelAttribute("RtrnTpModel") RtrnTpModel tp, ModelMap model, HttpServletRequest req) {

	String tpSelected = (String) req.getSession().getAttribute(CONST.TP_SELECTED.getVal());
	if (CommonBL.hasValue(tpSelected)) {
		List<String> selectedCheckBox = tp.getSelectedCheckBox();
		if (!CommonBL.isEmpty(selectedCheckBox)) {
			for (String code : selectedCheckBox) {
				RTRN_TP_RSN_CMBN tpRsnObj = this.tpRsnSvc.getTpRsnCmbnByHql(tpSelected, code);
				if (tpRsnObj != null) {
					this.tpRsnSvc.delete(tpRsnObj);
				}
			}
		}
	}
	return getMVSubTpRsn01(model, req);
}

Unlike adding the relationship, to delete (un-bound) the relationship, it’s directly deleting the Join Table (RTRN_TP_RSN_CMBN)

HQL and Native SQL in Hibernate

hiv-mvc-img
In Hibernate, there are number of ways to accessing a data: HQL, native SQL and others.
This section shows the implementation of HQL and native SQL.
These method are all defined in the DAO implementation classes.
hql-vs-sql-header-img1
The syntax of HQL is very much like SQL, but HQL is Hibernate object oriented.

SELECT * 
FROM RTRN_TP_RSN_CMBN  
WHERE rtrn_tp_cd ='01'
AND rtrn_rsn_cd ='001'

The above SQL is implemented as HQL as follow: (Source Code)

public RTRN_TP_RSN_CMBN getTpRsnCmbnByHql(String rtrnTpCd, String rtrnRsnCd) {

	String hqlStr = "from RTRN_TP_RSN_CMBN " 
			+ "where rtrn_tp_cd = :rtrnTpCd "
			+ "and   rtrn_rsn_cd = :rtrnRsnCd ";
	
	Query query = super.getCurrSession().createQuery(hqlStr);
	query.setParameter("rtrnTpCd", rtrnTpCd);
	query.setParameter("rtrnRsnCd", rtrnRsnCd);

	List<RTRN_TP_RSN_CMBN> list = query.list();
	if (list != null && list.size() > 0) {
		return (RTRN_TP_RSN_CMBN) list.get(0);
	}
	return null;
}

Native SQl: The result will be a list of Object Array.

SELECT 
 a1.rtrn_rsn_cd 
,a1.rtrn_rsn_nm 
,a1.sort_num 
,'' as rtrn_tp_cd 
FROM rtrn_rsn a1 
WHERE NOT EXISTS ( 
   SELECT '*' 
   FROM rtrn_rsn a2 
   JOIN rtrn_tp_rsn_cmbn cmb 
   ON a2.rtrn_rsn_cd = cmb.rtrn_rsn_cd 
   WHERE cmb.rtrn_tp_cd  = '02'
   AND a2.rtrn_rsn_cd = a1.rtrn_rsn_cd 
) 
ORDER BY a1.sort_num

The above SQL is implemented as follow: (Source Code)

public List<Object[]> getListRsnsUnBoundTpBySql(String tpCd) {
	
	StringBuilder sb = new StringBuilder();
	sb.append("\n");
	sb.append("SELECT \n");
	sb.append(" a1.rtrn_rsn_cd \n");
	sb.append(",a1.rtrn_rsn_nm \n");
	sb.append(",a1.sort_num \n");
	sb.append(",'' as rtrn_tp_cd \n");
	sb.append("FROM rtrn_rsn a1 \n");
	sb.append("WHERE NOT EXISTS ( \n");
	sb.append("   SELECT '*' \n");
	sb.append("   FROM rtrn_rsn a2 \n");
	sb.append("   JOIN rtrn_tp_rsn_cmbn cmb \n");
	sb.append("   ON a2.rtrn_rsn_cd = cmb.rtrn_rsn_cd \n");
	sb.append("   WHERE cmb.rtrn_tp_cd  = :tpCd \n");
	sb.append("   AND a2.rtrn_rsn_cd = a1.rtrn_rsn_cd \n");
	sb.append(") \n");
	sb.append("ORDER BY a1.sort_num");

	Query query = super.getCurrSession().createSQLQuery(sb.toString());
	query.setParameter("tpCd", tpCd);

	List<Object[]> list = query.list();
	return list;
}

Many to Many

many-to-many-header-img1
Many-to-Many mapping is usually implemented a Join Table, suppose we have Type and Reason table and Reason table for many-to-many mapping. Every Type can have more than one Reasons and every Reasons is a part of more than one Type.
2015-05-17_10h45_40

CREATE TABLE `rtrn_tp` (
	`rtrn_tp_cd` VARCHAR(20) NOT NULL DEFAULT '',
	`rtrn_tp_nm` VARCHAR(100) NULL DEFAULT NULL,
	`sort_num` INT(11) UNSIGNED NOT NULL DEFAULT '0',
	PRIMARY KEY (`rtrn_tp_cd`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
CREATE TABLE `rtrn_rsn` (
	`rtrn_rsn_cd` VARCHAR(20) NOT NULL DEFAULT '',
	`rtrn_rsn_nm` VARCHAR(100) NULL DEFAULT NULL,
	`sort_num` INT(11) UNSIGNED NOT NULL DEFAULT '0',
	PRIMARY KEY (`rtrn_rsn_cd`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
CREATE TABLE `rtrn_tp_rsn_cmbn` (
	`rtrn_tp_cd` VARCHAR(20) NOT NULL DEFAULT '',
	`rtrn_rsn_cd` VARCHAR(20) NOT NULL DEFAULT '',
	PRIMARY KEY (`rtrn_tp_cd`, `rtrn_rsn_cd`),
	INDEX `fk_rtrn_rsn` (`rtrn_rsn_cd`),
	CONSTRAINT `fk_rtrn_rsn` FOREIGN KEY (`rtrn_rsn_cd`) REFERENCES `rtrn_rsn` (`rtrn_rsn_cd`),
	CONSTRAINT `fk_rtrn_tp` FOREIGN KEY (`rtrn_tp_cd`) REFERENCES `rtrn_tp` (`rtrn_tp_cd`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

There is a Join Table, RTRN_TP_RSN_CMBN, only have the relationship between Type (RTRN_TP) and Reason (RTRN_RSN)

package com.ns.spring.model;

import java.util.HashSet;
import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.Table;

@Entity
@Table(name = "RTRN_TP")
public class RTRN_TP {

	@Id
	@Column(name = "rtrn_tp_cd")
	private String rtrnTpCd;

	@Column(name = "rtrn_tp_nm")
	private String rtrnTpNm;

	@Column(name = "sort_num")
	private int sortNum;

	@ManyToMany(targetEntity = RTRN_RSN.class, cascade = { CascadeType.ALL })
	@JoinTable(name = "RTRN_TP_RSN_CMBN", 
				joinColumns = { @JoinColumn(name = "rtrn_tp_cd") }, 
				inverseJoinColumns = { @JoinColumn(name = "rtrn_rsn_cd") })
	private Set<RTRN_RSN> rtrnRsns = new HashSet<RTRN_RSN>();

	public String getRtrnTpCd() {
		return rtrnTpCd;
	}

	public void setRtrnTpCd(String rtrnTpCd) {
		this.rtrnTpCd = rtrnTpCd;
	}

	public String getRtrnTpNm() {
		return rtrnTpNm;
	}

	public void setRtrnTpNm(String rtrnTpNm) {
		this.rtrnTpNm = rtrnTpNm;
	}

	public int getSortNum() {
		return sortNum;
	}

	public void setSortNum(int sortNum) {
		this.sortNum = sortNum;
	}

	public Set<RTRN_RSN> getRtrnRsns() {
		return rtrnRsns;
	}

	public void setRtrnRsns(Set<RTRN_RSN> rtrnRsns) {
		this.rtrnRsns = rtrnRsns;
	}

	public String toString() {
		return "Type Code:" + this.rtrnTpCd + ", Name:" + this.rtrnTpNm;
	}
}
package com.ns.spring.model;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "RTRN_RSN")
public class RTRN_RSN {

	@Id
	@Column(name = "rtrn_rsn_cd")
	private String rtrnRsnCd;
	
	@Column(name = "rtrn_rsn_nm")	
	private String rtrnRsnNm;
	
	@Column(name = "sort_num")	
	private int sortNum;

	public String getRtrnRsnCd() {
		return rtrnRsnCd;
	}

	public void setRtrnRsnCd(String rtrnRsnCd) {
		this.rtrnRsnCd = rtrnRsnCd;
	}

	public String getRtrnRsnNm() {
		return rtrnRsnNm;
	}

	public void setRtrnRsnNm(String rtrnRsnNm) {
		this.rtrnRsnNm = rtrnRsnNm;
	}

	public int getSortNum() {
		return sortNum;
	}

	public void setSortNum(int sortNum) {
		this.sortNum = sortNum;
	}

	public String toString() {
		return "Reason Code:" + this.rtrnRsnCd + ", Name:" + this.rtrnRsnNm;
	}
}
package com.ns.spring.model;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

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

	private static final long serialVersionUID = 4677612728077605784L;

	@Id
	@Column(name = "rtrn_tp_cd")
	private String rtrnTpCd;
	
	@Id
	@Column(name = "rtrn_rsn_cd")
	private String rtrnRsnCd;
	
	public String getRtrnTpCd() {
		return rtrnTpCd;
	}

	public void setRtrnTpCd(String rtrnTpCd) {
		this.rtrnTpCd = rtrnTpCd;
	}

	public String getRtrnRsnCd() {
		return rtrnRsnCd;
	}

	public void setRtrnRsnCd(String rtrnRsnCd) {
		this.rtrnRsnCd = rtrnRsnCd;
	}
	
	public String toString() {
		return "Type" + this.rtrnTpCd + ", Reason:" + this.rtrnRsnCd;
	}
}
@RequestMapping(value = "/cUrlValAttrbSubTpRsn01Jsp", params = "toBoundRsnList", method = RequestMethod.POST)
public ModelAndView toBoundRsnList(@ModelAttribute("RtrnTpModel") RtrnTpModel tp, ModelMap model, HttpServletRequest req) {

	String tpSelected = (String) req.getSession().getAttribute(CONST.TP_SELECTED.getVal());
	if (CommonBL.hasValue(tpSelected)) {
		RTRN_TP tpObj = this.tpSvc.findById(tpSelected);
		if (tpObj != null) {
			List<String> selectedCheckBox = TpRsnBL.getRsnToBound(tp, req);
			if (!CommonBL.isEmpty(selectedCheckBox)) {
				Set<RTRN_RSN> rtrnRsns = new HashSet<RTRN_RSN>();
				for (String code : selectedCheckBox) {
					RTRN_RSN rsnObj = this.rsnSvc.findById(code);
					if (rsnObj != null) {
						rtrnRsns.add(rsnObj);
						tpObj.setRtrnRsns(rtrnRsns);
						this.tpSvc.saveOrUpdate(tpObj);
					}
				}
			}
		}
	}
	return getMVSubTpRsn01(model, req);
}

This is how to use Many to Many in Spring MVC.

One to Many – Composite Primary Key

one-to-many-header-img1
In Hibernate, you need to have special field in order to accomplish a composite primary key to make it as a single key object:
one-to-many-header-comppk1
To handle composite primary key, you need to user an extra class “RmaLinePk”.
2015-06-01_07h41_10

2015-05-17_10h08_28

CREATE TABLE `rma_hdr` (
	`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	`rma_num` VARCHAR(20) NOT NULL DEFAULT '',
	`rma_hdr_sts_cd` VARCHAR(20) NULL DEFAULT NULL,
	`rtrn_tp_cd` VARCHAR(20) NOT NULL DEFAULT '',
	`rtrn_rsn_cd` VARCHAR(20) NULL DEFAULT NULL,
	`sell_to_cust_cd` VARCHAR(20) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=27
;
CREATE TABLE `rma_line` (
	`rma_num` VARCHAR(20) NOT NULL DEFAULT '',
	`rma_line_num` VARCHAR(20) NOT NULL DEFAULT '',
	`rma_line_sts_cd` VARCHAR(20) NOT NULL DEFAULT '',
	`mdse_cd` VARCHAR(20) NOT NULL DEFAULT '',
	`qty` INT(11) UNSIGNED NOT NULL DEFAULT '0',
	`rma_id` INT(11) UNSIGNED NOT NULL,
	PRIMARY KEY (`rma_num`, `rma_line_num`),
	INDEX `rma_id` (`rma_id`),
	CONSTRAINT `rma_line_ibfk_1` FOREIGN KEY (`rma_id`) REFERENCES `rma_hdr` (`id`) ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
package com.ns.spring.model;

import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;

import com.ns.spring.model.ui.RmaHdrModel;

/**
 * Entity bean with JPA annotations Hibernate provides JPA implementation
 */
@Entity
@Table(name = "RMA_HDR")
public class RMA_HDR {

	@Id
	@Column(name = "id")
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private int id;

	@Column(name = "rma_num")
	private String rmaNum;
	
	@Column(name = "rma_hdr_sts_cd")
	private String rmaHdrStsCd;

	@Column(name = "rtrn_tp_cd")
	private String rtrnTpCd;

	@Column(name = "rtrn_rsn_cd")
	private String rtrnRsnCd;

	@Column(name = "sell_to_cust_cd")
	private String sellToCustCd;

	@OneToMany(mappedBy = "rmaHdr", targetEntity = RMA_LINE.class, fetch = FetchType.EAGER, cascade = CascadeType.ALL)
	private List<RMA_LINE> rmaLines;
	
	public RMA_HDR(){}

	public RMA_HDR(RmaHdrModel hdr) {
		this.id = hdr.getId();
		this.rmaNum = hdr.getRmaNum();
		this.rmaHdrStsCd = hdr.getRmaHdrStsCd();
		this.rtrnTpCd = hdr.getRtrnTpCd();
		this.rtrnRsnCd = hdr.getRtrnRsnCd();
		this.sellToCustCd = hdr.getSellToCustCd();
	}

	public int getId() {
		return id;
	}

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

	public String getRmaNum() {
		return rmaNum;
	}

	public void setRmaNum(String rmaNum) {
		this.rmaNum = rmaNum;
	}

	public String getRmaHdrStsCd() {
		return rmaHdrStsCd;
	}

	public void setRmaHdrStsCd(String rmaHdrStsCd) {
		this.rmaHdrStsCd = rmaHdrStsCd;
	}

	public String getRtrnTpCd() {
		return rtrnTpCd;
	}

	public void setRtrnTpCd(String rtrnTpCd) {
		this.rtrnTpCd = rtrnTpCd;
	}

	public String getRtrnRsnCd() {
		return rtrnRsnCd;
	}

	public void setRtrnRsnCd(String rtrnRsnCd) {
		this.rtrnRsnCd = rtrnRsnCd;
	}

	public String getSellToCustCd() {
		return sellToCustCd;
	}

	public void setSellToCustCd(String sellToCustCd) {
		this.sellToCustCd = sellToCustCd;
	}

	public List<RMA_LINE> getRmaLines() {
		return rmaLines;
	}

	public void setRmaLines(List<RMA_LINE> rmaLines) {
		this.rmaLines = rmaLines;
	}
	
	public String toString() {
		return "ID:" + this.id + ", RMA_NUM:" + this.rmaNum + ", Status:" + this.rmaHdrStsCd + ", Type:" + this.rtrnTpCd + ", Reason:" + this.rtrnRsnCd;
	}
}
@Id
private RmaLinePk rmaLinePk;

Above:In “many side”, it uses the primary key field

package com.ns.spring.model;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.ManyToOne;
import javax.persistence.JoinColumn;
import javax.persistence.Id;
import javax.persistence.Table;

/**
 * Entity bean with JPA annotations Hibernate provides JPA implementation
 */
@Entity
@Table(name = "RMA_LINE")
public class RMA_LINE {

	@Id
	private RmaLinePk rmaLinePk;

	@Column(name = "rma_line_sts_cd")
	private String rmaLineStsCd;

	@Column(name = "mdse_cd")
	private String mdseCd;

	@Column(name = "qty")
	private int qty;

	@ManyToOne
	@JoinColumn(name = "rma_id")
	private RMA_HDR rmaHdr;
	
	public RMA_LINE(){}

	public RmaLinePk getRmaLinePk() {
		return rmaLinePk;
	}

	public void setRmaLinePk(RmaLinePk rmaLinePk) {
		this.rmaLinePk = rmaLinePk;
	}

	public String getRmaLineStsCd() {
		return rmaLineStsCd;
	}

	public void setRmaLineStsCd(String rmaLineStsCd) {
		this.rmaLineStsCd = rmaLineStsCd;
	}

	public String getMdseCd() {
		return mdseCd;
	}

	public void setMdseCd(String mdseCd) {
		this.mdseCd = mdseCd;
	}

	public RMA_HDR getRmaHdr() {
		return rmaHdr;
	}

	public int getQty() {
		return qty;
	}

	public void setQty(int qty) {
		this.qty = qty;
	}

	public void setRmaHdr(RMA_HDR rmaHdr) {
		this.rmaHdr = rmaHdr;
	}

	public String toString() {
		return "PK:" + this.rmaLinePk.toString() + ", Line Status:" + this.rmaLineStsCd + ", Mdse:" + this.mdseCd + ", Qty:" + this.qty;
	}
}
private String rma_num;
private String rma_line_num;

The real primary keys which are composite defines in the following class: RmaLinePk.java

package com.ns.spring.model;

import java.io.Serializable;

import javax.persistence.Embeddable;

@Embeddable
public class RmaLinePk implements Serializable {

	private static final long serialVersionUID = -403250971215465050L;

	private String rma_num;

	private String rma_line_num;

	public RmaLinePk() {
	}

	public RmaLinePk(String rma_num, String rma_line_num) {
		this.rma_num = rma_num;
		this.rma_line_num = rma_line_num;
	}

	public String getRma_num() {
		return rma_num;
	}

	public void setRma_num(String rma_num) {
		this.rma_num = rma_num;
	}

	public String getRma_line_num() {
		return rma_line_num;
	}

	public void setRma_line_num(String rma_line_num) {
		this.rma_line_num = rma_line_num;
	}

	public boolean equals(Object obj) {
		if (this == obj) {
			return true;
		}
		if (obj == null) {
			return false;
		}
		if (getClass() != obj.getClass()) {
			return false;
		}
		RmaLinePk other = (RmaLinePk) obj;
		if (rma_num == null) {
			if (other.rma_num != null) {
				return false;
			}
		} else if (!rma_num.equals(other.rma_num)) {
			return false;
		}
		if (rma_line_num == null) {
			if (other.rma_line_num != null) {
				return false;
			}
		} else if (!rma_line_num.equals(other.rma_line_num)) {
			return false;
		}
		return true;
	}

	public int hashCode() {
		final int prime = 31;
		int result = 1;
		result = prime * result + ((rma_num == null) ? 0 : rma_num.hashCode());
		result = prime * result	+ ((rma_line_num == null) ? 0 : rma_line_num.hashCode());
		return result;
	}

	public String toString() {
		return "RmaLinePk:RMA#:" + this.rma_num + ", Line#:" + this.rma_line_num;
	}
}

This is how the data is stored in controller: See also Submitting a collection.

public ModelAndView submitLine(@ModelAttribute("rmaLineListModel") RmaLineListModel rmaLineListModel, Model model, HttpServletRequest req) {

	String rmaNum = rmaLineListModel.getRmaNum();
	List<RmaLineModel> currList = rmaLineListModel.getRmaLineModelList();
	if (!CommonBL.isEmpty(currList)) {
		for (RmaLineModel obj : currList) {
			String rmaLineNum = obj.getRmaLineNum();
			List<RmaLineModel> rmaLinsList = RmaDtlBL.getRmaLineListWithCdTblNm(rmaNum, rmaLineNum);
			if (CommonBL.isEmpty(rmaLinsList)) {
				// New Record
				this.rmaLineSvc.save(RmaDtlBL.getRmaLineObj(obj, rmaNum, rmaLineNum));
			}
		}
	}
	List<RmaLineModel> rmaLineModelList = RmaDtlBL.getRmaLineListWithCdTblNm(rmaNum, null);
	return getMVSubLine01(rmaNum, rmaLineModelList, req);
}

This method passes the object to be inserted/updated

public static RMA_LINE getRmaLineObj(RmaLineModel obj, String rmaNum, String rmaLineNum) {
	RMA_LINE rmaLine = new RMA_LINE();
	rmaLine.setRmaLinePk(new RmaLinePk(rmaNum, rmaLineNum));
	rmaLine.setRmaHdr(RmaBL.getRmaHdrObj(rmaNum));
	rmaLine.setRmaLineStsCd(getStsCd(obj.getRmaLineStsCd()));
	rmaLine.setMdseCd(obj.getMdseCd());
	rmaLine.setQty(obj.getQty());
	return rmaLine;
}

Generic DAO with Custom Method

db-chain1
Instead of having DAO for each entity, calling Generic DAO simplify the program.
Unlike the template classes with empty method that can only use the method specified in Generic DAO classes, this example use additional customized method (Source Code)

2015-05-30_11h15_25

2015-05-30_11h17_10

package com.ns.spring.dao.template;

import java.util.List;

import com.ns.spring.dao.GenHbDao;
import com.ns.spring.model.RMA_LINE;

public interface RmaLineDao extends GenHbDao<RMA_LINE, String> {

	public List<Object[]> getListRmaLineBySql(String rmaNum, String rmaLineNum);
	public RMA_LINE getRmaLineByHql(String rmaNum, String rmaLineNum);
}
package com.ns.spring.dao.template.impl;
package com.ns.spring.dao.template.impl;

import java.util.List;

import org.hibernate.Query;
import org.springframework.stereotype.Repository;

import com.ns.spring.dao.GenHbDaoImpl;
import com.ns.spring.dao.template.RmaLineDao;
import com.ns.spring.model.RMA_LINE;

@Repository
public class RmaLineDaoImpl extends GenHbDaoImpl<RMA_LINE, String> implements RmaLineDao {

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getListRmaLineBySql(String rmaNum, String rmaLineNum) {
		
		StringBuilder sb = new StringBuilder();
		sb.append("\n");
		sb.append("SELECT\n");
		sb.append("  rl.rma_num\n");
		sb.append(" ,rl.rma_line_num\n");
		sb.append(" ,rl.rma_line_sts_cd\n");
		sb.append(" ,sts.rma_hdr_sts_nm\n");
		sb.append(" ,rl.mdse_cd\n");
		sb.append(" ,mdse.mdse_nm\n");
		sb.append(" ,rl.qty\n");
		sb.append(" ,rl.rma_id\n");
		sb.append("FROM RMA_LINE rl\n");
		sb.append("LEFT OUTER JOIN RMA_HDR_STS sts\n");
		sb.append("ON rl.rma_line_sts_cd = sts.rma_hdr_sts_cd\n");
		sb.append("LEFT OUTER JOIN MDSE mdse\n");
		sb.append("ON rl.mdse_cd = mdse.mdse_cd\n");
		sb.append("WHERE rl.rma_num = :rmaNum\n");
		if (rmaLineNum != null) {
			sb.append("AND rl.rma_line_num = :rmaLineNum\n");
		}		
		sb.append("ORDER BY\n");
		sb.append("  rl.rma_num\n");
		sb.append(" ,rl.rma_line_num\n");

		Query query = super.getCurrSession().createSQLQuery(sb.toString());
		query.setParameter("rmaNum", rmaNum);
		if (rmaLineNum != null) {
			query.setParameter("rmaLineNum", rmaLineNum);
		}
		List<Object[]> list = query.list();
		return list;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public RMA_LINE getRmaLineByHql(String rmaNum, String rmaLineNum) {

		String hqlStr = "from RMA_LINE " 
				+ "where rma_num = :rmaNum "
				+ "and   rma_line_num = :rmaLineNum ";
		
		Query query = super.getCurrSession().createQuery(hqlStr);
		query.setParameter("rmaNum", rmaNum);
		query.setParameter("rmaLineNum", rmaLineNum);

		List<RMA_LINE> list = query.list();
		if (list != null && list.size() > 0) {
			return (RMA_LINE) list.get(0);
		}
		return null;
	}
}

DAO interface, and it’s implementation has two additional methods:
1. Native SQL with its reesult
2. HQL with its reesult

package com.ns.spring.service.template;

import java.util.List;

import com.ns.spring.model.RMA_LINE;
import com.ns.spring.service.GenHbService;

public interface RmaLineService extends GenHbService<RMA_LINE, String> {

	public List<Object[]> getListRmaLineBySql(String rmaNum, String rmaLineNum);
	public RMA_LINE getRmaLineByHql(String rmaNum, String rmaLineNum);
}
package com.ns.spring.service.template.impl;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.ns.spring.dao.GenHbDao;
import com.ns.spring.dao.template.RmaLineDao;
import com.ns.spring.model.RMA_LINE;
import com.ns.spring.service.GenHbServiceImpl;
import com.ns.spring.service.template.RmaLineService;

@Service("rmaLineService")
public class RmaLineServiceImpl extends GenHbServiceImpl<RMA_LINE, String> implements RmaLineService {

	private RmaLineDao dao;

	public RmaLineServiceImpl() {
	}

	@Autowired
	public RmaLineServiceImpl(@Qualifier("rmaLineDaoImpl") GenHbDao<RMA_LINE, String> genericDao) {
		super(genericDao);
		this.dao = (RmaLineDao) genericDao;
	}
	
	@Override
	@Transactional
	public List<Object[]> getListRmaLineBySql(String rmaNum, String rmaLineNum) {
		return this.dao.getListRmaLineBySql(rmaNum, rmaLineNum);
	}

	@Override
	@Transactional
	public RMA_LINE getRmaLineByHql(String rmaNum, String rmaLineNum) {
		return this.dao.getRmaLineByHql(rmaNum, rmaLineNum);
	}
}

DAO service interface, and it’s implementation calling super class, and two custom method defined in RmaLineDaoImpl.

Generic DAO without Custom Method

db-chain1
Instead of having DAO for each entity, calling Generic DAO simplify the program. (Source Code)

2015-05-30_09h48_00

2015-05-30_09h53_38

package com.ns.spring.dao.template;

import com.ns.spring.dao.GenHbDao;
import com.ns.spring.model.MDSE;

public interface MdseDao extends GenHbDao<MDSE, String> {

}
package com.ns.spring.dao.template.impl;

import org.springframework.stereotype.Repository;

import com.ns.spring.dao.GenHbDaoImpl;
import com.ns.spring.dao.template.MdseDao;
import com.ns.spring.model.MDSE;

@Repository
public class MdseDaoImpl extends GenHbDaoImpl<MDSE, String> implements MdseDao {

}

Empty DAO interface, and it’s implementation

package com.ns.spring.service.template;

import com.ns.spring.model.MDSE;
import com.ns.spring.service.GenHbService;

public interface MdseService extends GenHbService<MDSE, String> {

}

package com.ns.spring.service.template.impl;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

import com.ns.spring.dao.GenHbDao;
import com.ns.spring.dao.template.MdseDao;
import com.ns.spring.model.MDSE;
import com.ns.spring.service.GenHbServiceImpl;
import com.ns.spring.service.template.MdseService;

@Service("mdseService")
public class MdseServiceImpl extends GenHbServiceImpl<MDSE, String> implements MdseService {

	private MdseDao dao;

	public MdseServiceImpl() {
	}

	@Autowired
	public MdseServiceImpl(@Qualifier("mdseDaoImpl") GenHbDao<MDSE, String> genericDao) {
		super(genericDao);
		this.dao = (MdseDao) genericDao;
	}
}

Empty DAO service interface, and it’s implementation calling super class. It can use the method defined in Generic DAO

Generic DAO

db-chain1
For the CRUD operation in Hibernate, you don’t need to repeat Insert, Update, Delete at all. The Generic DAO can take any entity object to do this.

It can be accessed by any entity object with empty method, or some additional customize method

There are Service, and DAO layer, and your controller access Service class
Controller <-> Service <-> DAO : Source Code
model2

The interface of Generic DAO.

package com.ns.spring.dao;

import java.io.Serializable;
import java.util.List;

import com.ns.spring.exception.NSException;

public interface GenHbDao<E, K extends Serializable> {

	public List<E> findAll();	
	public E findById(K key) throws NSException;
	public void save(E obj);
	public void update(E obj);
	public void saveOrUpdate(E obj);
	public void delete(E obj);

}

The implementation of above interface

package com.ns.spring.dao;

import java.io.Serializable;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.util.List;

import org.hibernate.ObjectNotFoundException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.ns.spring.exception.NSException;

@SuppressWarnings("unchecked")
@Repository
public abstract class GenHbDaoImpl<E, K extends Serializable> implements GenHbDao<E, K> {

	private SessionFactory sessionFactory;
	private Class<E> clazz;

	public GenHbDaoImpl() {
		Type t = getClass().getGenericSuperclass();
		ParameterizedType pt = (ParameterizedType) t;
		this.clazz = (Class<E>) pt.getActualTypeArguments()[0];
	}

	public Class<E> getCurrClazz() {
		return clazz;
	}

	@Autowired
	public void setSessionFactory(SessionFactory sf) {
		this.sessionFactory = sf;
	}

	protected Session getCurrSession() {
		return sessionFactory.getCurrentSession();
	}

	@Override
	public void save(E obj) {
		this.getCurrSession().persist(obj);
	}

	@Override
	public void update(E obj) {
		this.getCurrSession().update(obj);
	}

	@Override
	public void saveOrUpdate(E obj) {
		this.getCurrSession().saveOrUpdate(obj);
	}

	@Override
	public void delete(E obj) {
		this.getCurrSession().delete(obj);
	}

	@Override
	public List<E> findAll() {
		List<E> list = this.getCurrSession().createCriteria(getCurrClazz()).list();
		return list;
	}

	@Override
	public E findById(K key) throws NSException {
		Session session = this.getCurrSession();
		try {
			E p = (E) session.load(getCurrClazz(), key);
			return p;
		} catch (ObjectNotFoundException e) {
			throw new NSException("No result");
		}
	}
}

The interface of Service class (nearly identical of Generic DAO) that actually called by your controller.

package com.ns.spring.service;

import java.io.Serializable;
import java.util.List;

public interface GenHbService<E, K extends Serializable> {

	public List<E> findAll();
	public E findById(K key);
	public void save(E obj);
	public void update(E obj);
	public void saveOrUpdate(E obj);
	public void delete(E obj);

}

And finally the implementation of the above service interface. Notice the @Transactional annotation in each method

package com.ns.spring.service;

import java.io.Serializable;
import java.util.List;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.ns.spring.dao.GenHbDao;
import com.ns.spring.exception.NSException;

@Service
public abstract class GenHbServiceImpl<E, K extends Serializable> implements GenHbService<E, K> {

	private GenHbDao<E, K> genDao;

	public GenHbServiceImpl(GenHbDao<E, K> genDao) {
		this.genDao = genDao;
	}

	public GenHbServiceImpl() {
	}

	@Override
	@Transactional(propagation = Propagation.REQUIRED)
	public void save(E obj) {
		genDao.save(obj);
	}

	@Override
	@Transactional(propagation = Propagation.REQUIRED)
	public void update(E obj) {
		genDao.update(obj);
	}

	@Override
	@Transactional(propagation = Propagation.REQUIRED)
	public void saveOrUpdate(E obj) {
		genDao.saveOrUpdate(obj);
	}

	@Override
	@Transactional(propagation = Propagation.REQUIRED)
	public void delete(E obj) {
		genDao.delete(obj);
	}

	@Override
	@Transactional(propagation = Propagation.REQUIRED)
	public List<E> findAll() {
		return genDao.findAll();
	}

	@Override
	@Transactional(propagation = Propagation.REQUIRED)
	public E findById(K key) {
		try {
			return genDao.findById(key);
		} catch (NSException e) {
			return null;
		}
	}
}