Monday, April 24, 2006

selectManyShuttle part One

ADF Faces selectManyShuttle Part One: the database

Well, I’m a day or two late with my promised weekend posting for this, but, as “they” say, better late than never. There’s been a question over on the JDeveloper discussion forum that comes up now and again that asks, “How do I create/use the af:selectManyShuttle component?” It comes up often enough that I thought I’d write a post about how I implemented one. I’ve decided to break it up into multiple parts in order to simplify the discussion.

The af:selectManyShuttle is a component that is used to pick multiple items from a list, and it’s rendered as a “shuttle” component that looks like this. It’s a nice, elegant component for doing things like assigning students to courses, for example, which is the example I have chosen to use.

In this first installment, I’ll be going over the database design, and describing a stored procedure that I built to simplify use of the selectManyShuttle. The example I am going to be using is quite simple; the database will contain a student table and a course table. It will also have an “intersection” table called “enrollment” that is used to resolve the many-to-many relationship between students and courses. It’s this table that lends itself quite well to using a selectManyShuttle in the user interface. For a given course, we’ll display a selectManyShuttle to allow the user to assign which students are enrolled in the given course.

Tables and Triggers

The tables I’m going to be using are quite simple. Here’s a simple view of the database layout:



For those who’d like to follow along, here are the DDL statements that you can execute to create the tables:

CREATE TABLE STUDENT(student_id NUMBER PRIMARY KEY,
first_name VARCHAR2(32),
last_name VARCHAR2(32));

CREATE TABLE COURSE(course_id NUMBER PRIMARY KEY,
course_name VARCHAR2(32),
start_date DATE,
end_date DATE);

CREATE TABLE ENROLLMENT(student_id NUMBER NOT NULL, course_id NUMBER NOT NULL);

ALTER TABLE ENROLLMENT ADD CONSTRAINT enrollment_pk PRIMARY KEY (student_id, course_id);

I’ve also created a sequence and some triggers to automatically populate the ID’s:

CREATE SEQUENCE my_seq;

CREATE OR REPLACE TRIGGER student_bir
BEFORE INSERT ON STUDENT
FOR EACH ROW
BEGIN

IF (:NEW.student_id IS NULL) THEN
SELECT my_seq.NEXTVAL
INTO :NEW.student_id
FROM DUAL;
END IF;

END;

/

CREATE OR REPLACE TRIGGER course_bir
BEFORE INSERT ON COURSE
FOR EACH ROW
BEGIN

IF (:NEW.course_id IS NULL) THEN
SELECT my_seq.NEXTVAL
INTO :NEW.course_id
FROM DUAL;
END IF;

END;

/

There is one last piece to the database that I’ll explain here, but first a little more about the shuttle component. When you use an af:selectManyShuttle, the list of selected items is returned to you in an array. It’s this array that we need to use to populate our enrollment table. Now, we could simply iterate over the array in some java code and send a bunch of statements to the database one at a time, but that is very inefficient due to the overhead of a bunch of JDBC calls. What I decided to do was to write a stored procedure that would accept an array of student id’s and a course id and populate the enrollment table all in one fell swoop.

In order to do that, I defined a SQL type that would map to an array of java.lang.Integers:

CREATE OR REPLACE TYPE NUM_ARRAY AS TABLE OF NUMBER;

Then, I coded a simple stored procedure that would populate the enrollments for a course, given the course id and an array of student id’s:

CREATE OR REPLACE PROCEDURE Assign_Students_To_Course(p_course_id IN NUMBER, p_students IN NUM_ARRAY) IS

BEGIN

-- CLEAR out enrollment table and insert enrolled students

DELETE ENROLLMENT
WHERE COURSE_ID = p_course_ID;

INSERT INTO ENROLLMENT(COURSE_ID, STUDENT_ID)
SELECT p_course_id, column_value
FROM TABLE(p_students);

END;
/

Because my enrollment table doesn’t have any additional information other than the primary keys of the students and courses, it was more efficient to just delete the existing records and insert all of the students for the course at once. The only thing in this procedure that may be foreign to some of you is the TABLE operator, which lets us use an array as a table in SQL. The column “column_value” is what Oracle uses to expose the actual data element stored in the array.

Lastly, because I wish to focus on the selectManyShuttle component, and not other elements, I used simple INSERT statements to insert a bunch of data for students and courses. In the next installment, I’ll show how to create the JSF page using the selectManyShuttle component.

10 comments:

Anonymous said...

Hi John,
I am new to ADF Faces and we have planned to convert a legacy application into ADF Faces application with ADF BC.
Since you already has deployment experience on this, can I contact you to ask some question about ADF Faces ?

Thank you for your response
xtanto
xtanto@hotmail.com

Anonymous said...

John,
I guess you could have used a MERGE statement instead of DELETE and INSERT .
Just my $.02

Vitaliy

P.S. When do you think your part(s) 2, (3, etc) are gonna come out? I am axious to see it, since the SRDemo example is not really working...

John Stegeman said...

vitaliy,

You are correct about the merge statement. I actually use MERGE in the real code, but I wanted this example to be as simple as possible.

I hope to have the part 2 out sometime in the next day or so.

John

Anonymous said...

John,
Our group is in the need for the SelectManyShuttle functionality. If we can help in any possible way, please let me know.
Thanks a lot.
Kamran
aminik@cpms.osd.mil

Anonymous said...

John,
Posted a question about your custom login module post a few while ago, thought you havent seen that yet.
Cheers
Mehrdad~

Anonymous said...

bumping an old old blog.

Anonymous said...

Hello John,
When can expext the next parts.

Anonymous said...

Hi John;

Would you please send me an e-mail?
Thank you.

Shaun Shoaee
sshoaee[AT]yahoo[DOT]com

Sildenafil said...

thanks for the explanation on ADF Faces selectManyShuttle I was trying to give it a try, and I had some doubts about it, but with this post I have a good reference for what I want to do now, thank you!

Anonymous said...

hiii blogs istanbul pastalar kurabiyeler.
You are correct about the merge statement. I actually use MERGE in the real code, but I wanted this example to be as simple as possible.
Kurabiye Tarifleri resimli kolay kurabiye hazırlamak