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.