Im having a problem retrieving records from the database using OQLQuery.
I am running an a clustered WebLogic 8.1 environment using 0.9.4.3 version
of castor.
The problem:
Executing the same query from different servers in the cluster results in
different size results being returned. A record is missing on one of the
servers. This problem goes away after the servers are restarted. Also, this
doesnt happen all the time.
I am attaching the relevent part of mapping.xml and the method that does
the querying. I hope this is enough information:
public List findLoanApplications(Query query)
throws DataAccessException {
StringBuffer queryBuffer = new StringBuffer();
List parameters = new ArrayList();
int counter = 0;
if(query.getDecision() != null) {
List codes = retrieveStatusCodes(query.getDecision());
if(codes == null || codes.size() < 1) {
throw new DataAccessException("Unable to retrieve decision
codes " +
"for group " + query.getDecision());
}
if(counter > 0) {
queryBuffer.append(" AND");
} else {
queryBuffer.append("WHERE");
}
Iterator iter = codes.iterator();
queryBuffer.append(" (");
for(int i = 0; iter.hasNext(); i++) {
StatusElement element = (StatusElement)iter.next();
parameters.add(counter, element.getStatus());
if(i > 0) {
queryBuffer.append(" OR");
}
queryBuffer.append(" l.decision.status=$" + (++counter));
}
queryBuffer.append(")");
}
if(query.getCareer() != null) {
if(counter > 0) {
queryBuffer.append(" AND");
} else {
queryBuffer.append("WHERE");
}
parameters.add(counter, query.getCareer());
queryBuffer.append(" l.school.name=$" + (++counter));
}
if(query.getEmployeeId() != null) {
if(counter > 0) {
queryBuffer.append(" AND");
} else {
queryBuffer.append("WHERE");
}
parameters.add(counter, query.getEmployeeId());
queryBuffer.append(" l.student.studentId=$" + (++counter));
}
if(query.getProcessChangeEndDate() != null) {
if(counter > 0) {
queryBuffer.append(" AND");
} else {
queryBuffer.append("WHERE");
}
parameters.add(counter, query.getProcessChangeEndDate());
queryBuffer.append(" l.statusChangeDate<=$" + (++counter));
}
if(query.getLastName() != null) {
if(counter > 0) {
queryBuffer.append(" AND");
} else {
queryBuffer.append("WHERE");
}
parameters.add(counter, query.getLastName().toUpperCase());
queryBuffer.append(" l.student.name.upperLast=$" + (++counter));
}
if(query.getSsn() != null) {
if(counter > 0) {
queryBuffer.append(" AND");
} else {
queryBuffer.append("WHERE");
}
parameters.add(counter, query.getSsn());
queryBuffer.append(" l.student.ssn=$" + (++counter));
}
if(query.getProcessChangeStartDate() != null) {
if(counter > 0) {
queryBuffer.append(" AND");
} else {
queryBuffer.append("WHERE");
}
parameters.add(counter, query.getProcessChangeStartDate());
queryBuffer.append(" l.statusChangeDate>=$" + (++counter));
}
if(query.getDecisionChangeStartDate() != null) {
if(counter > 0) {
queryBuffer.append(" AND");
} else {
queryBuffer.append("WHERE");
}
parameters.add(counter, query.getDecisionChangeStartDate());
queryBuffer.append(" l.decision.lastUpdate>=$" + (++counter));
}
if(query.getDecisionChangeEndDate() != null) {
if(counter > 0) {
queryBuffer.append(" AND");
} else {
queryBuffer.append("WHERE");
}
parameters.add(counter, query.getDecisionChangeEndDate());
queryBuffer.append(" l.decision.lastUpdate<=$" + (++counter));
}
if(query.getStatus() != null) {
List codes = retrieveStatusCodes(query.getStatus());
if(codes == null || codes.size() < 1) {
throw new DataAccessException("Unable to retrieve status codes
" +
"for group " + query.getStatus());
}
if(counter > 0) {
queryBuffer.append(" AND");
} else {
queryBuffer.append("WHERE");
}
Iterator iter = codes.iterator();
queryBuffer.append(" (");
for(int i = 0; iter.hasNext(); i++) {
StatusElement element = (StatusElement)iter.next();
parameters.add(counter, element.getStatus());
if(i > 0) {
queryBuffer.append(" OR");
}
queryBuffer.append(" l.status.type=$" + (++counter));
}
queryBuffer.append(")");
}
Database db = null;
try {
db = getDatabase();
OQLQuery oql = db.getOQLQuery(
"SELECT l " +
"FROM com.loanapplication.LoanApplicationSummary l " +
queryBuffer.toString() + " order by
l.student.name.upperLast, " +
"l.student.name.upperFirst, l.applicationId");
Iterator iter = parameters.iterator();
while(iter.hasNext()) {
oql.bind(iter.next());
}
db.setAutoStore(true);
db.begin();
QueryResults results = oql.execute(Database.ReadOnly);
List list = null;
if(query.getMaxResults() > 0) {
list = populateFromQueryResults(results,
query.getMaxResults());
} else {
list = populateFromQueryResults(results);
}
db.commit();
System.out.println("Query generated: " + list.size() + " results."
);
return list;
} catch(PersistenceException pe) {
pe.printStackTrace();
try {
db.rollback();
} catch(TransactionNotInProgressException tnipe) {
// Ignore and report the initial cause.
}
throw new DataAccessException(pe.getMessage(), pe);
} finally {
try {
releaseDatabase(db);
} catch(PersistenceException pe) {
// Must not be aquired.
}
}
} // end findLoanApplication(Query)
<!-- Mapping for the LoanApplicationSummary -->
<class name="com.loanapplication.LoanApplicationSummary"
identity="applicationId">
<cache-type type="none"/>
<map-to table="LOAN_APPLICATION"/>
<field name="applicationId" type="long" >
<sql name="OID" type="bigint"/>
</field>
<field name="status.type" type="string" >
<sql name="STATUS_CD" type="longvarchar"/>
</field>
<field name="alternateDisbursement" type="string" >
<sql name="ALTRNTE_DSBRSMNT" type="longvarchar"
dirty="ignore"/>
</field>
<field name="complete" type="boolean" >
<sql name="APP_COMPLETED_IND" type="bit" dirty="ignore"/>
</field>
<field name="statusChangeDate" type="date" >
<sql name="STATUS_UPDATE_DATE" type="date"
dirty="ignore"/>
</field>
<field name="year" type="string" >
<sql name="ACADEMIC_YEAR" type="longvarchar" dirty="ignore"/>
</field>
<field name="school.name" type="string" >
<sql name="SCHOOL_CD" type="longvarchar" dirty="ignore"/>
</field>
<field name="startDate" type="date" >
<sql name="APP_START_DATE" type="date" dirty="ignore"/>
</field>
<field name="closeDate" type="date" >
<sql name="DATE_CLOSED" type="date" dirty="ignore"/>
</field>
<field name="internalLiabilities"
type="com.loanapplication.LiabilitySummary"
collection="arraylist">
<sql many-key="P_LOAN_APPLICATION"/>
</field>
<!-- Mapping for the Certification -->
<field name="certification.certifiedDate" type="date">
<sql name="DATE_CERTIFIED" type="date" dirty="ignore"/>
</field>
<field name="certification.certifyingOfficer" type="string">
<sql name="CERTIFIED_BY" type="longvarchar"
dirty="ignore"/>
</field>
<!-- Mapping for the Decision -->
<field name="decision.status" type="string">
<sql name="DECISION" type="longvarchar" dirty="ignore"/>
</field>
<field name="decision.lastUpdate" type="date">
<sql name="DECISION_UPDATE_DT" type="date"
dirty="ignore"/>
</field>
<field name="decision.creditDecision.status" type="string">
<sql name="CREDIT_DECISION" type="longvarchar"
dirty="ignore"/>
</field>
<!-- Mapping for the Student -->
<field name="student.studentId" type="long">
<sql name="STUDENT_ID" type="bigint"
dirty="ignore"/>
</field>
<field name="student.name.first" type="string">
<sql name="STDT_FIRST_NAME" type="longvarchar"
dirty="ignore"/>
</field>
<field name="student.name.upperFirst" type="string">
<sql name="UPPER_FIRST_NAME" type="longvarchar"
dirty="ignore"/>
</field>
<field name="student.name.middle" type="string">
<sql name="STDT_MIDDLE_INITIAL" type="longvarchar"
dirty="ignore"/>
</field>
<field name="student.name.last" type="string">
<sql name="STDT_LAST_NAME" type="longvarchar"
dirty="ignore"/>
</field>
<field name="student.name.title.type" type="string">
<sql name="TITLE" type="longvarchar"
dirty="ignore"/>
</field>
<field name="student.name.upperLast" type="string">
<sql name="UPPER_LAST_NAME" type="longvarchar"
dirty="ignore"/>
</field>
<field name="student.ssn" type="string">
<sql name="STDT_SSN" type="longvarchar"
dirty="ignore"/>
</field>
</class>
<!-- Mapping for LiabilitySummary -->
<class name="com.loanapplication.LiabilitySummary"
identity="id"
depends="com.loanapplication.LoanApplicationSummary">
<cache-type type="none"/>
<map-to table="LIABILITY"/>
<field name="loanApplication"
type="com.loanapplication.LoanApplicationSummary">
<sql name="P_LOAN_APPLICATION" dirty="ignore" />
</field>
<field name="id" type="long">
<sql name="OID" type="bigint"/>
</field>
<field name="type" type="string">
<sql name="TYPE" type="longvarchar" dirty="ignore" />
</field>
<field name="amount" type="integer">
<sql name="AMT" type="integer" dirty="ignore"/>
</field>
</class>
Thanks, Steve C
-----------------------------------------------------------
If you wish to unsubscribe from this mailing, send mail to
[EMAIL PROTECTED] with a subject of:
unsubscribe castor-dev
