asked on

EJB Entity Bean - from a database View did not create a primary key class but needs one

I hope someone can help..

I have a view which is grabbing information from many tables it does a union to retrieve all the units with multiple instructors associated to it and all the units without. Find below the view:

CREATE OR REPLACE VIEW QV_ADMIN_UNIT_LIST ( UNIT_KEY,
COURSE_NAME, TERM_YEAR, CD_SESSION_DSC_TX, COURSE_PREFIX,
COURSE_SUFFIX, CD_LOCATION_DESC, IS_DISPLAYED, FK_COURSE_TYPE_KEY,
FK_TERM_KEY, FK_DEPT_KEY, FK_CD_LOCATION_KEY, SORT_ORDER,
FK_SESSION_KEY, LOCATION_SORT_ORDER, TERM_END_DT, COURSE_TYPE_DESC,
FK_COURSE_KEY, FK_STATUS_KEY, FK_SECTION_KEY, WEIGHT_DESC,
SECTION_DESC, INSTRUCTOR_KEY, INSTRUCTOR_LAST_NAME, INSTRUCTOR_FIRST_NAME
) AS SELECT u.unit_key, c.course_name, t.term_year, s.cd_session_dsc_tx, c.course_prefix, c.course_suffix, l.cd_location_desc, u.is_displayed, c.fk_course_type_key, u.fk_term_key, u.fk_dept_key, u.fk_cd_location_key, ct.sort_order, t.fk_cd_session_key,l.sort_order, t.term_end_dt, ct.course_type_dsc_tx, u.fk_course_key, u.fk_cd_unit_status_key, u.fk_cd_section_key, w.cd_weight_dsc_tx, cs.cd_section_code, i.instructor_key, qp.last_name, qp.first_name
FROM CRSE_UNIT u, CRSE_COURSE c, CRSE_TERM t, CD_SESSION s, CD_LOCATION l, CD_COURSE_TYPE ct, CD_WEIGHT w, CD_SECTION cs, CRSE_UNIT_INSTRUCTOR_TEACHES uit,INSTR_INSTRUCTOR i, qperson qp
WHERE c.course_key = u.fk_course_key AND
       l.cd_location_key = u.fk_cd_location_key AND
       t.term_key = u.fk_term_key AND
       t.fk_cd_session_key = s.cd_session_key AND
       ct.course_type_key = c.fk_course_type_key AND
       c.fk_cd_weight_key = w.cd_weight_key AND
       u.fk_cd_section_key = cs.cd_section_key AND
       uit.fk_instructor_key = i.instructor_key AND
       i.ce_number = qp.userid AND
       u.unit_key = uit.fk_unit_key(+)
UNION ALL
SELECT u.unit_key, c.course_name, t.term_year, s.cd_session_dsc_tx, c.course_prefix, c.course_suffix, l.cd_location_desc, u.is_displayed, c.fk_course_type_key, u.fk_term_key, u.fk_dept_key, u.fk_cd_location_key, ct.sort_order, t.fk_cd_session_key,l.sort_order, t.term_end_dt, ct.course_type_dsc_tx, u.fk_course_key, u.fk_cd_unit_status_key, u.fk_cd_section_key, w.cd_weight_dsc_tx, cs.cd_section_code, TO_NUMBER(NULL),TO_CHAR(NULL), TO_CHAR(NULL)
FROM CRSE_UNIT u, CRSE_COURSE c, CRSE_TERM t, CD_SESSION s, CD_LOCATION l, CD_COURSE_TYPE ct, CD_WEIGHT w, CD_SECTION cs, CRSE_UNIT_INSTRUCTOR_TEACHES uit
WHERE c.course_key = u.fk_course_key AND
       l.cd_location_key = u.fk_cd_location_key AND
       t.term_key = u.fk_term_key AND
       t.fk_cd_session_key = s.cd_session_key AND
       ct.course_type_key = c.fk_course_type_key AND
       c.fk_cd_weight_key = w.cd_weight_key AND
       u.fk_cd_section_key = cs.cd_section_key AND
       u.unit_key = uit.fk_unit_key(+) AND
       uit.fk_instructor_key IS NULL
ORDER BY u.unit_key

My problem is that when I built the Entity bean from the view it did not create a Primary key class which it will actually need one since the unit_key is no longer unique since you can have multiple
instructors for a unit..

here is the classes it built:

package ca.queensu.conted.impl;
import javax.ejb.EntityBean;
import javax.ejb.EntityContext;
import java.sql.Timestamp;

public abstract class Qv_admin_unit_listBean implements EntityBean
{
private EntityContext context;

public Long ejbCreate()
{
return null;
}

public void ejbPostCreate()
{
}

public void ejbActivate()
{
}

public void ejbLoad()
{
}

public void ejbPassivate()
{
}

public void ejbRemove()
{
}

public void ejbStore()
{
}

public void setEntityContext(EntityContext ctx)
{
this.context = ctx;
}

public void unsetEntityContext()
{
this.context = null;
}

public abstract Long getUnit_key();

public abstract void setUnit_key(Long newUnit_key);

public abstract String getCourse_name();

public abstract void setCourse_name(String newCourse_name);

public abstract String getTerm_year();

public abstract void setTerm_year(String newTerm_year);

public abstract String getCd_session_dsc_tx();

public abstract void setCd_session_dsc_tx(String newCd_session_dsc_tx);

public abstract String getCourse_prefix();

public abstract void setCourse_prefix(String newCourse_prefix);

public abstract String getCourse_suffix();

public abstract void setCourse_suffix(String newCourse_suffix);

public abstract String getCd_location_desc();

public abstract void setCd_location_desc(String newCd_location_desc);

public abstract String getIs_displayed();

public abstract void setIs_displayed(String newIs_displayed);

public abstract Long getFk_course_type_key();

public abstract void setFk_course_type_key(Long newFk_course_type_key);

public abstract Long getFk_term_key();

public abstract void setFk_term_key(Long newFk_term_key);

public abstract Long getFk_dept_key();

public abstract void setFk_dept_key(Long newFk_dept_key);

public abstract Long getFk_cd_location_key();

public abstract void setFk_cd_location_key(Long newFk_cd_location_key);

public abstract Long getSort_order();

public abstract void setSort_order(Long newSort_order);

public abstract Long getFk_session_key();

public abstract void setFk_session_key(Long newFk_session_key);

public abstract Long getLocation_sort_order();

public abstract void setLocation_sort_order(Long newLocation_sort_order);

public abstract Timestamp getTerm_end_dt();

public abstract void setTerm_end_dt(Timestamp newTerm_end_dt);

public abstract String getCourse_type_desc();

public abstract void setCourse_type_desc(String newCourse_type_desc);

public abstract Long getFk_course_key();

public abstract void setFk_course_key(Long newFk_course_key);

public abstract Long getFk_status_key();

public abstract void setFk_status_key(Long newFk_status_key);

public abstract Long getFk_section_key();

public abstract void setFk_section_key(Long newFk_section_key);

public abstract String getWeight_desc();

public abstract void setWeight_desc(String newWeight_desc);

public abstract String getSection_desc();

public abstract void setSection_desc(String newSection_desc);

public abstract Long getInstructor_key();

public abstract void setInstructor_key(Long newInstructor_key);

public abstract String getInstructor_last_name();

public abstract void setInstructor_last_name(String newInstructor_last_name);

public abstract String getInstructor_first_name();

public abstract void setInstructor_first_name(String newInstructor_first_name);
}

package ca.queensu.conted;
import javax.ejb.EJBLocalObject;
import java.sql.Timestamp;

public interface Qv_admin_unit_listLocal extends EJBLocalObject
{
Long getUnit_key();

void setUnit_key(Long newUnit_key);

String getCourse_name();

void setCourse_name(String newCourse_name);

String getTerm_year();

void setTerm_year(String newTerm_year);

String getCd_session_dsc_tx();

void setCd_session_dsc_tx(String newCd_session_dsc_tx);

String getCourse_prefix();

void setCourse_prefix(String newCourse_prefix);

String getCourse_suffix();

void setCourse_suffix(String newCourse_suffix);

String getCd_location_desc();

void setCd_location_desc(String newCd_location_desc);

String getIs_displayed();

void setIs_displayed(String newIs_displayed);

Long getFk_course_type_key();

void setFk_course_type_key(Long newFk_course_type_key);

Long getFk_term_key();

void setFk_term_key(Long newFk_term_key);

Long getFk_dept_key();

void setFk_dept_key(Long newFk_dept_key);

Long getFk_cd_location_key();

void setFk_cd_location_key(Long newFk_cd_location_key);

Long getSort_order();

void setSort_order(Long newSort_order);

Long getFk_session_key();

void setFk_session_key(Long newFk_session_key);

Long getLocation_sort_order();

void setLocation_sort_order(Long newLocation_sort_order);

Timestamp getTerm_end_dt();

void setTerm_end_dt(Timestamp newTerm_end_dt);

String getCourse_type_desc();

void setCourse_type_desc(String newCourse_type_desc);

Long getFk_course_key();

void setFk_course_key(Long newFk_course_key);

Long getFk_status_key();

void setFk_status_key(Long newFk_status_key);

Long getFk_section_key();

void setFk_section_key(Long newFk_section_key);

String getWeight_desc();

void setWeight_desc(String newWeight_desc);

String getSection_desc();

void setSection_desc(String newSection_desc);

Long getInstructor_key();

void setInstructor_key(Long newInstructor_key);

String getInstructor_last_name();

void setInstructor_last_name(String newInstructor_last_name);

String getInstructor_first_name();

void setInstructor_first_name(String newInstructor_first_name);
}

package ca.queensu.conted;
import javax.ejb.EJBLocalHome;
import javax.ejb.CreateException;
import javax.ejb.FinderException;
import java.util.Collection;

public interface Qv_admin_unit_listLocalHome extends EJBLocalHome
{
Qv_admin_unit_listLocal create() throws CreateException;

Qv_admin_unit_listLocal findByPrimaryKey(Long primaryKey) throws FinderException;

Collection findAll() throws FinderException;

Collection findAllUnitDynamically(Long courseKey, Long termKey, Long courseTypeKey, Long sectionKey, Long statusKey, Long locationKey, Long instructorKey, Long deptKey) throws FinderException;
}

It made the unit_key the primary key.. which means when I loop through the unitLocal it always return the first one.. The only difference between the records is that one will have instructors and others won't so will have a null key for the instructor_key so I can't make that the primary key..

Is there a way to create a primary key class based on the unit_key & the instructor_key which are both LONG datatype.. I attempted this but it kept failing on me..

Thanks in advance

Tommy Braas

An entity bean models a row in a table. That being said, what you need to do is to generate your EJBs from simple field mappings into the various tables, and reflect any dependencies on other tables by containment. I.e. In your Unit EJB you will have a reference to a List of Instructor. You will have to write that yourself I'm afraid. Your Unit EJB will have to communicate with the Instructor Home to get the Instructor EJB instances needed for the List, and you will have to write that particular finder as well.

Caro0101

ASKER

I have created this Primary class.. although when I run the code I get this error "Unhandled Exception: javax.ejb.NoSuchObjectLocalException: ca.queensu.conted.Qv_admin_unit_listPK@2ac5"

Anybody have an idea as to what I am missing for this to work?

package ca.queensu.conted;
import java.io.Serializable;

public class Qv_admin_unit_listPK implements Serializable
{
public Long instructor_key;
public Long unit_key;

public Qv_admin_unit_listPK()
{
}

public Qv_admin_unit_listPK(Long unit_key, Long instructor_Key)
{
this.unit_key = unit_key;
this.instructor_key = instructor_key;
}

public boolean equals(Object other)
{
if (other instanceof Qv_admin_unit_listPK)
{
final Qv_admin_unit_listPK otherQv_admin_unit_listPK = (Qv_admin_unit_listPK)other;

// The following assignment statement is auto-maintained and may be overwritten!
boolean areEqual = (otherQv_admin_unit_listPK.unit_key.equals(unit_key) && otherQv_admin_unit_listPK.instructor_key.equals(instructor_key));

return areEqual;
}

return false;
}

public int hashCode()
{
// Add custom hashCode() impl here
return super.hashCode();
}
}

kiwi_alien

An entity CAN model a row in a table, but normally an entity bean will model a view (or entity that spans multiple related tables in a DB). That said your actual problem doesn't seem to me to warrant using an Entity Bean. Or at least an Entity Bean like this. A far better way is just using a Session Bean (stateless). Nothing wrong with Session Beans talking to the DB to get the job done in a situation like this. Otherwise you're not REALLY using the Entity Bean the way is was designed (there IS no primary key for this type of retrieval).

If you do continue down this path the way to create a primary key class based on the unit_key & the instructor_key which are both LONG datatype is pretty much what you have there - you just need to make sure it's glued into the config in the right place.

The Unhandled Exception: javax.ejb.NoSuchObjectLocalException means the container is trying to use the object you have a reference to and can't actually find it (for whatever reason). It could be because it was never created or it could be because you're using a stale handle.

Just more info is what I need.. What actual steps are you going through? What does your DD look like? Whats your App Server? etc, etc

Caro0101

ASKER

I solved my problem.. I had to use a view.

Here is the solution.. had to change the Long to a string
public boolean equals(Object other)
{
if (other instanceof Qv_admin_unit_listPK)
{
final Qv_admin_unit_listPK otherQv_admin_unit_listPK = (Qv_admin_unit_listPK)other;

// The following assignment statement is auto-maintained and may be overwritten!
boolean areEqual = (otherQv_admin_unit_listPK.unit_key.toString().equals(unit_key.toString()) && otherQv_admin_unit_listPK.instructor_key.toString().equals(instructor_key.toString()));

return areEqual;
}

return false;
}

public int hashCode()
{
// Add custom hashCode() impl here
return unit_key.toString().concat(instructor_key.toString()).hashCode();
}

I also had to change the view for the instructor_key when it was NULL to actually make it 0 instead
so where the TO_NUMBER(NULL) change it to TO_NUMBER(0).

THanks for everyone help.. I know it is not the best solution but due to the database design I had to use a View.

kiwi_alien

Caro101 - thats fine - there's nothing wrong with using an Entity Bean to encapsulate a view of a database - sometimes we can map just rows but often a view is required. Its not necessarily good practice to have many fine-grained Entity Beans anyway.

ASKER CERTIFIED SOLUTION

GhostMod

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial