ADF CRUD Operation

ADF CRUD OPerations – master detail table

login to database as sys


CREATE USER jamesxedb IDENTIFIED BY welcome1 ;

GRANT CREATE session, CREATE table, CREATE view,CREATE procedure, CREATE synonym,
CREATE Trigger, Create Sequence TO jamesxedb ;

GRANT UNLIMITED TABLESPACE TO jamesxedb ;

Logout and Login as jamesxedb/welcome1

CREATE SEQUENCE "JAMESXEDB"."STUDENTIDSEQ" MINVALUE 1 MAXVALUE 1000000 
INCREMENT BY 1 START WITH 115 CACHE 20 NOORDER NOCYCLE ;
——————————————————–
— DDL for Table COURSES
——————————————————–

CREATE TABLE "JAMESXEDB"."COURSES"
( "COURSEID" NUMBER,
"COURSENAME" VARCHAR2(40 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
——————————————————–
— DDL for Table PROGRAMS
——————————————————–

CREATE TABLE "JAMESXEDB"."PROGRAMS"
( "PROGID" NUMBER,
"PROGRAMNAME" VARCHAR2(40 BYTE),
"COURSENAME" VARCHAR2(40 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
——————————————————–
— DDL for Table STUDENTS
——————————————————–

CREATE TABLE "JAMESXEDB"."STUDENTS"
( "STUDENTID" NUMBER,
"STUDENTNAME" VARCHAR2(20 BYTE),
"STUDENTCLASS" VARCHAR2(20 BYTE),
"CERTIFICATESATTACHED" VARCHAR2(20 BYTE),
"SUBJECTTAKEN" VARCHAR2(20 BYTE),
"CLASSTEACHERID" NUMBER,
"PERCENTAGE" NUMBER,
"RESCATEGORY" VARCHAR2(20 BYTE),
"BANKACNUMBER" VARCHAR2(20 BYTE),
"BANKPIN" NUMBER,
"FEESAMOUNT" NUMBER,
"STATUS" VARCHAR2(20 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
——————————————————–
— DDL for Table TEACHERS
——————————————————–

CREATE TABLE "JAMESXEDB"."TEACHERS"
( "TEACHERID" NUMBER,
"TEACHERNAME" VARCHAR2(20 BYTE),
"DESIGNATION" VARCHAR2(20 BYTE),
"EXPERIANCE" VARCHAR2(20 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
REM INSERTING into JAMESXEDB.COURSES
Insert into JAMESXEDB.COURSES (COURSEID,COURSENAME) values (1,’Engineering’);
Insert into JAMESXEDB.COURSES (COURSEID,COURSENAME) values (2,’Communication’);
Insert into JAMESXEDB.COURSES (COURSEID,COURSENAME) values (3,’Medical Science’);
REM INSERTING into JAMESXEDB.PROGRAMS
Insert into JAMESXEDB.PROGRAMS (PROGID,PROGRAMNAME,COURSENAME) values (1,’Mechanical Engineering’,’Engineering’);
Insert into JAMESXEDB.PROGRAMS (PROGID,PROGRAMNAME,COURSENAME) values (2,’Civil Engineering’,’Engineering’);
Insert into JAMESXEDB.PROGRAMS (PROGID,PROGRAMNAME,COURSENAME) values (3,’Computer Science’,’Engineering’);
Insert into JAMESXEDB.PROGRAMS (PROGID,PROGRAMNAME,COURSENAME) values (4,’Mass Communication’,’Communication’);
Insert into JAMESXEDB.PROGRAMS (PROGID,PROGRAMNAME,COURSENAME) values (5,’Journalism’,’Communication’);
Insert into JAMESXEDB.PROGRAMS (PROGID,PROGRAMNAME,COURSENAME) values (6,’Gynaecology’,’Medical Science’);
Insert into JAMESXEDB.PROGRAMS (PROGID,PROGRAMNAME,COURSENAME) values (7,’Physiotherapy’,’Medical Science’);
REM INSERTING into JAMESXEDB.STUDENTS
Insert into JAMESXEDB.STUDENTS (STUDENTID,STUDENTNAME,STUDENTCLASS,CERTIFICATESATTACHED,SUBJECTTAKEN,CLASSTEACHERID,PERCENTAGE,RESCATEGORY,BANKACNUMBER,BANKPIN,FEESAMOUNT,STATUS) values (18,’Sachin Tendulkar’,’1st PUC’,’yes’,’Commerce’,5,null,null,null,null,null,null);
Insert into JAMESXEDB.STUDENTS (STUDENTID,STUDENTNAME,STUDENTCLASS,CERTIFICATESATTACHED,SUBJECTTAKEN,CLASSTEACHERID,PERCENTAGE,RESCATEGORY,BANKACNUMBER,BANKPIN,FEESAMOUNT,STATUS) values (16,’Mike Jackson’,’2nd PUC’,’yes’,’PCMB’,1,null,null,null,null,null,null);
Insert into JAMESXEDB.STUDENTS (STUDENTID,STUDENTNAME,STUDENTCLASS,CERTIFICATESATTACHED,SUBJECTTAKEN,CLASSTEACHERID,PERCENTAGE,RESCATEGORY,BANKACNUMBER,BANKPIN,FEESAMOUNT,STATUS) values (55,’Anna’,’2nd PUC’,’Yes’,’Commerce’,1,null,null,null,null,null,null);
Insert into JAMESXEDB.STUDENTS (STUDENTID,STUDENTNAME,STUDENTCLASS,CERTIFICATESATTACHED,SUBJECTTAKEN,CLASSTEACHERID,PERCENTAGE,RESCATEGORY,BANKACNUMBER,BANKPIN,FEESAMOUNT,STATUS) values (35,’Border Student’,’2nd PUC’,’No’,’PCMB’,4,null,null,null,null,null,null);
Insert into JAMESXEDB.STUDENTS (STUDENTID,STUDENTNAME,STUDENTCLASS,CERTIFICATESATTACHED,SUBJECTTAKEN,CLASSTEACHERID,PERCENTAGE,RESCATEGORY,BANKACNUMBER,BANKPIN,FEESAMOUNT,STATUS) values (1,’James Smith’,’2nd PUC’,’yes’,’PCMB’,1,null,null,null,null,null,null);
Insert into JAMESXEDB.STUDENTS (STUDENTID,STUDENTNAME,STUDENTCLASS,CERTIFICATESATTACHED,SUBJECTTAKEN,CLASSTEACHERID,PERCENTAGE,RESCATEGORY,BANKACNUMBER,BANKPIN,FEESAMOUNT,STATUS) values (3,’Mike Anderson’,’2nd PUC’,’yes’,’PCMB’,3,null,null,null,null,null,null);
Insert into JAMESXEDB.STUDENTS (STUDENTID,STUDENTNAME,STUDENTCLASS,CERTIFICATESATTACHED,SUBJECTTAKEN,CLASSTEACHERID,PERCENTAGE,RESCATEGORY,BANKACNUMBER,BANKPIN,FEESAMOUNT,STATUS) values (4,’Zina Charles’,’2nd PUC’,’yes’,’PCMB’,1,null,null,null,null,null,null);
Insert into JAMESXEDB.STUDENTS (STUDENTID,STUDENTNAME,STUDENTCLASS,CERTIFICATESATTACHED,SUBJECTTAKEN,CLASSTEACHERID,PERCENTAGE,RESCATEGORY,BANKACNUMBER,BANKPIN,FEESAMOUNT,STATUS) values (5,’Andrew Gomez’,’2nd PUC’,’yes’,’PCMB’,1,null,null,null,null,null,null);
Insert into JAMESXEDB.STUDENTS (STUDENTID,STUDENTNAME,STUDENTCLASS,CERTIFICATESATTACHED,SUBJECTTAKEN,CLASSTEACHERID,PERCENTAGE,RESCATEGORY,BANKACNUMBER,BANKPIN,FEESAMOUNT,STATUS) values (6,’Harry Potter’,’2nd PUC’,’yes’,’PCMB’,1,null,null,null,null,null,null);
Insert into JAMESXEDB.STUDENTS (STUDENTID,STUDENTNAME,STUDENTCLASS,CERTIFICATESATTACHED,SUBJECTTAKEN,CLASSTEACHERID,PERCENTAGE,RESCATEGORY,BANKACNUMBER,BANKPIN,FEESAMOUNT,STATUS) values (7,’Bill Smith’,’2nd PUC’,’yes’,’PCMB’,2,null,null,null,null,null,null);
Insert into JAMESXEDB.STUDENTS (STUDENTID,STUDENTNAME,STUDENTCLASS,CERTIFICATESATTACHED,SUBJECTTAKEN,CLASSTEACHERID,PERCENTAGE,RESCATEGORY,BANKACNUMBER,BANKPIN,FEESAMOUNT,STATUS) values (8,’Hillary’,’2nd PUC’,’yes’,’PCMB’,1,null,null,null,null,null,null);
Insert into JAMESXEDB.STUDENTS (STUDENTID,STUDENTNAME,STUDENTCLASS,CERTIFICATESATTACHED,SUBJECTTAKEN,CLASSTEACHERID,PERCENTAGE,RESCATEGORY,BANKACNUMBER,BANKPIN,FEESAMOUNT,STATUS) values (9,’Tim Robinson’,’2nd PUC’,’yes’,’PCMB’,3,null,null,null,null,null,null);
Insert into JAMESXEDB.STUDENTS (STUDENTID,STUDENTNAME,STUDENTCLASS,CERTIFICATESATTACHED,SUBJECTTAKEN,CLASSTEACHERID,PERCENTAGE,RESCATEGORY,BANKACNUMBER,BANKPIN,FEESAMOUNT,STATUS) values (10,’Brad Pitt’,’1st PUC’,’no’,’PCMB’,2,null,null,null,null,null,null);
Insert into JAMESXEDB.STUDENTS (STUDENTID,STUDENTNAME,STUDENTCLASS,CERTIFICATESATTACHED,SUBJECTTAKEN,CLASSTEACHERID,PERCENTAGE,RESCATEGORY,BANKACNUMBER,BANKPIN,FEESAMOUNT,STATUS) values (36,’Mahesh Bondade’,’2nd PUC’,’Yes’,’PCME’,5,null,null,null,null,null,null);
Insert into JAMESXEDB.STUDENTS (STUDENTID,STUDENTNAME,STUDENTCLASS,CERTIFICATESATTACHED,SUBJECTTAKEN,CLASSTEACHERID,PERCENTAGE,RESCATEGORY,BANKACNUMBER,BANKPIN,FEESAMOUNT,STATUS) values (25,’Jordon Updated’,’1st PUC’,’yes’,’PCMB’,1,null,null,null,null,null,null);
Insert into JAMESXEDB.STUDENTS (STUDENTID,STUDENTNAME,STUDENTCLASS,CERTIFICATESATTACHED,SUBJECTTAKEN,CLASSTEACHERID,PERCENTAGE,RESCATEGORY,BANKACNUMBER,BANKPIN,FEESAMOUNT,STATUS) values (75,’Mark Antony’,’2nd PUC’,’Yes’,’PCMB’,3,null,null,null,null,null,null);
Insert into JAMESXEDB.STUDENTS (STUDENTID,STUDENTNAME,STUDENTCLASS,CERTIFICATESATTACHED,SUBJECTTAKEN,CLASSTEACHERID,PERCENTAGE,RESCATEGORY,BANKACNUMBER,BANKPIN,FEESAMOUNT,STATUS) values (80,’Mini Mathur’,’2nd PUC’,’No’,’PCMB’,2,null,’80’,’GEN’,null,null,null);
Insert into JAMESXEDB.STUDENTS (STUDENTID,STUDENTNAME,STUDENTCLASS,CERTIFICATESATTACHED,SUBJECTTAKEN,CLASSTEACHERID,PERCENTAGE,RESCATEGORY,BANKACNUMBER,BANKPIN,FEESAMOUNT,STATUS) values (84,’Mike’,’Mass Communication’,’Yes’,’Communication’,2,null,’80’,’GEN’,null,null,null);
Insert into JAMESXEDB.STUDENTS (STUDENTID,STUDENTNAME,STUDENTCLASS,CERTIFICATESATTACHED,SUBJECTTAKEN,CLASSTEACHERID,PERCENTAGE,RESCATEGORY,BANKACNUMBER,BANKPIN,FEESAMOUNT,STATUS) values (96,’Mike Jackson’,’Physiotherapy’,’Yes’,’Medical Science’,2,null,’80’,’GEN’,null,null,null);
REM INSERTING into JAMESXEDB.TEACHERS
Insert into JAMESXEDB.TEACHERS (TEACHERID,TEACHERNAME,DESIGNATION,EXPERIANCE) values (2,’Joel Garner’,’HOD Chemistry’,’14’);
Insert into JAMESXEDB.TEACHERS (TEACHERID,TEACHERNAME,DESIGNATION,EXPERIANCE) values (3,’Jeff Thompson’,’HOD Maths’,’11’);
Insert into JAMESXEDB.TEACHERS (TEACHERID,TEACHERNAME,DESIGNATION,EXPERIANCE) values (4,’Allan Border’,’HOD Biology’,’10’);
Insert into JAMESXEDB.TEACHERS (TEACHERID,TEACHERNAME,DESIGNATION,EXPERIANCE) values (5,’Ajit Wadekar’,’Reader Physics’,’08’);
——————————————————–
— DDL for Index COURSES_PK
——————————————————–

CREATE UNIQUE INDEX "JAMESXEDB"."COURSES_PK" ON "JAMESXEDB"."COURSES" ("COURSEID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
——————————————————–
— DDL for Index PROGRAMS_PK
——————————————————–

CREATE UNIQUE INDEX "JAMESXEDB"."PROGRAMS_PK" ON "JAMESXEDB"."PROGRAMS" ("PROGID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
——————————————————–
— Constraints for Table PROGRAMS
——————————————————–

ALTER TABLE "JAMESXEDB"."PROGRAMS" ADD CONSTRAINT "PROGRAMS_PK" PRIMARY KEY ("PROGID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE;
ALTER TABLE "JAMESXEDB"."PROGRAMS" MODIFY ("PROGID" NOT NULL ENABLE);
——————————————————–
— Constraints for Table COURSES
——————————————————–

ALTER TABLE "JAMESXEDB"."COURSES" ADD CONSTRAINT "COURSES_PK" PRIMARY KEY ("COURSEID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE;
ALTER TABLE "JAMESXEDB"."COURSES" MODIFY ("COURSEID" NOT NULL ENABLE);
——————————————————–
— DDL for Trigger ASSIGN_STUDENTID
——————————————————–

CREATE OR REPLACE TRIGGER "JAMESXEDB"."ASSIGN_STUDENTID" BEFORE INSERT ON STUDENTS
FOR EACH ROW
BEGIN
IF :NEW.STUDENTID IS NULL OR :NEW.STUDENTID < 0 THEN
SELECT STUDENTIDSEQ.NEXTVAL
INTO :NEW.STUDENTID
FROM DUAL;
END IF;
END;
/
ALTER TRIGGER "JAMESXEDB"."ASSIGN_STUDENTID" ENABLE;
commit 

View link adf bc model

Create View Link

Link Teacher Id from Both the Master and Detail Table (In our case Teacher is Master table – student is detail table)

test the App Module

 

SAVE record backing bean code

 
public void saveStudent(ActionEvent actionEvent)
{
	// Add event code here...
	System.out.println("studentName "+studentName);
	System.out.println("studentClass "+studentClass);
	System.out.println("certificates "+certificates);
	System.out.println("subject "+subject);
	if(certificates)
	{
	this.setStrCertificates("Yes");
	}
	else 
	{
		this.setStrCertificates("No");
	}
	// Getting Values from the Iterator
	BindingContainer bindings =BindingContext.getCurrent().getCurrentBindingsEntry();
	JUCtrlListBinding listBinding =(JUCtrlListBinding)bindings.get("Teachers1");
	System.out.println(listBinding.getAttributeCount());
	System.out.println("Get Teacher Name "+listBinding.getAttribute(0));
	this.setTeacherId( (String)listBinding.getAttribute(1).toString() );
	System.out.println("teacherId "+teacherId);
	System.out.println("strCertificates "+strCertificates);
	// To Insert -----------------------
	AppModuleImpl am = getAm();
	System.out.println("Am Before Insert "+am);
	ViewObject myVO = am.findViewObject("Students1");
	Row rowForInsert = myVO.createRow();
	rowForInsert.setAttribute(1, studentName);
	rowForInsert.setAttribute(2, studentClass);
	rowForInsert.setAttribute(3, strCertificates);
	rowForInsert.setAttribute(4, subject);
	rowForInsert.setAttribute(5, teacherId);
	myVO.insertRow(rowForInsert);
	am.getDBTransaction().commit();
	System.out.println("Am After Insert ");
	// To Insert -----------------------
}
private AppModuleImpl getAm()
{
	FacesContext fc = FacesContext.getCurrentInstance();
	Application app = fc.getApplication();
	ExpressionFactory elFactory = app.getExpressionFactory();
	ELContext elContext = fc.getELContext();
	ValueExpression valueExp = elFactory.createValueExpression(elContext, "#{data.AppModuleDataControl.dataProvider}", Object.class);
	return (AppModuleImpl)valueExp.getValue(elContext);
}

update and delete record backing bean code

public void updateStudent(ActionEvent actionEvent) 
{
	// Add event code here...
	System.out.println("Before Update");
	AppModuleImpl am = getAm();
	am.getDBTransaction().commit();
	System.out.println("After Update");
}
public void deleteStudent(ActionEvent actionEvent) 
{
	// Add event code here...
	System.out.println("Before Delete");
	ADFUtil.invokeEL("#{bindings.Delete.execute}");
	ADFUtil.invokeEL("#{bindings.Commit.execute}");
	System.out.println("After Delete");
}

RUN the jspx file

View Master Detail Data

Add New Student

Edit or Delete Student Data

 

Download and author

Download source code here About Author