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