Code that runs T-SQL:
Code:
public ReportVO[] getReportWhereClientNameLike(
final String cliNm, final Date startDate, final Date endDate) {
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
String queryString =
"SELECT client_t.gcli_nm AS gcliNm "
+ ", pol_t.pol_num AS polNum "
+ ", cc_file_t.ccfile_rcvd_dtm AS ccfileRcvdDtm "
+ ", cc_file_event_t.ccfile_evnt_dsc AS ccfileEvntDsc "
+ ", cc_file_event_status_type_t.ccfile_evnt_stat_dsc AS ccfileEvntStatDsc "
+ ", cc_file_t.ccfile_mbr_cnt AS ccfileMbrCnt "
+ ", cc_file_t.ccfile_10pct_chg_ind AS ccfile10pctChgInd "
+ ", cc_profile_t.ccpfl_is_active_ind AS ccpflIsActiveInd "
+ "FROM client_t "
+ "INNER JOIN pol_t ON "
+ "client_t.gcli_id = pol_t.gcli_id "
+ "INNER JOIN cc_profile_t ON "
+ "pol_t.gcli_id = cc_profile_t.gcli_id "
+ "AND pol_t.pol_id = cc_profile_t.pol_id "
+ "INNER JOIN cc_file_t ON "
+ "cc_profile_t.gcli_id = cc_file_t.gcli_id "
+ "AND cc_profile_t.pol_id = cc_file_t.pol_id "
+ "INNER JOIN cc_file_event_history_t ON "
+ "cc_file_t.gcli_id = cc_file_event_history_t.gcli_id "
+ "AND cc_file_t.pol_id = cc_file_event_history_t.pol_id "
+ "AND cc_file_t.ccfile_seq_num = cc_file_event_history_t.ccfile_seq_num "
+ "INNER JOIN cc_file_event_t ON "
+ "cc_file_event_history_t.ccfile_evnt_cd = cc_file_event_t.ccfile_evnt_cd "
+ "INNER JOIN cc_file_event_status_type_t ON "
+ "cc_file_event_history_t.ccfile_evnt_stat_cd = cc_file_event_status_type_t.ccfile_evnt_stat_cd "
+ "WHERE "
+ "((cc_file_event_history_t.ccfile_evnt_cd = 'CCLOAD' AND (cc_file_event_history_t.ccfile_evnt_stat_cd = 'SUCCESS' OR cc_file_event_history_t.ccfile_evnt_stat_cd = 'WTHERROR')) "
+ "OR (cc_file_event_history_t.ccfile_evnt_cd = 'OASISLD' AND (cc_file_event_history_t.ccfile_evnt_stat_cd = 'SUCCESS' OR cc_file_event_history_t.ccfile_evnt_stat_cd = 'WTHERROR')) "
+ "OR (cc_file_event_history_t.ccfile_evnt_stat_cd = 'FAILURE') "
+ "OR (cc_file_event_history_t.ccfile_evnt_stat_cd = 'SYSPROC')) "
+ "AND cc_file_event_history_t.ccfile_evnt_strt_dtm = ( "
+ "SELECT MAX(ccfile_evnt_strt_dtm) "
+ "FROM cc_file_event_history_t "
+ "WHERE ((cc_file_event_history_t.ccfile_evnt_cd = 'CCLOAD' AND (ccfile_evnt_stat_cd = 'SUCCESS' OR ccfile_evnt_stat_cd = 'WTHERROR')) "
+ "OR (cc_file_event_history_t.ccfile_evnt_cd = 'OASISLD' AND (ccfile_evnt_stat_cd = 'SUCCESS' OR ccfile_evnt_stat_cd = 'WTHERROR')) "
+ "OR (ccfile_evnt_stat_cd = 'FAILURE') "
+ "OR (ccfile_evnt_stat_cd = 'SYSPROC')) "
+ "AND gcli_id = cc_file_t.gcli_id "
+ "AND pol_id = cc_file_t.pol_id "
+ "AND ccfile_seq_num = cc_file_t.ccfile_seq_num) "
+ "AND cc_file_t.ccfile_rcvd_dtm BETWEEN '" + formatter.format(startDate) + "' AND + '" + formatter.format(endDate) + "' "
+ "AND client_t.gcli_nm LIKE '" + cliNm + "'";
SQLQuery sqlQuery =
HibernateExtranetSession.getSessionFactory().getCurrentSession().
createSQLQuery(queryString);
return convertToVOArray(sqlQuery.list());
}
So sqlQuery.list() is a list of Object[] as there is no mapping to a VO involved. In order to map the data to a VO, I have to traverse each object array in the list and map the contents of the array to a field in the VO.
Code that maps object[] list to VO array:Code:
public final ReportVO[] convertToVOArray(
final List hibernateList) {
try {
ReportVO[] ret = new ReportVO[hibernateList.size()];
for (int i = 0; i < hibernateList.size(); i++) {
Object[] objs = (Object[]) hibernateList.get(i);
// String[] arr = ((String) hibernateList.get(i)).split(",");
ReportVO vo = new ReportVO();
vo.setCliNm((String) objs[0]);
vo.setPolNum((String) objs[1].toString());
vo.setCcfileRcvdDtm((Date) objs[2]);
vo.setCcfileEvntDsc((String) objs[3]);
vo.setCcfileEvntStatDsc((String) objs[4]);
vo.setCcfileMbrCnt(((Short) objs[5]).intValue());
vo.setCcfile10pctChgInd((String) objs[6].toString());
vo.setCcpflIsActiveInd((String) objs[7].toString());
ret[i] = vo;
}
//hibernateList.toArray(ret);
return ret;
} catch (ArrayStoreException ase) {
return null;
}
}
Here is what I am mapping the contents of ecah object[] to:
VO code:Code:
public class ReportVO implements Serializable {
/** serialVersionUID. */
private static final long serialVersionUID = 1;
/** cli_nm. */
private String cliNm;
/** pol_num. */
private String polNum;
/** ccfile_rcvd_dtm. */
private Date ccfileRcvdDtm;
/** ccfile_evnt_dsc. */
private String ccfileEvntDsc;
/** ccfile_evnt_stat_dsc. */
private String ccfileEvntStatDsc;
/** ccfile_mbr_cnt. */
private int ccfileMbrCnt;
/** ccfile_10pct_chg_ind. */
private String ccfile10pctChgInd;
/** ccpflIsActiveInd. */
private String ccpflIsActiveInd;
/**
* cliNm getter.
*
* @return cliNm
*/
public String getCliNm() {
return cliNm;
}
/**
* cliNm setter.
*
* @param cliNmIn cliNmIn
*/
public void setCliNm(final String cliNmIn) {
this.cliNm = cliNmIn;
}
/**
* polNum getter.
*
* @return polNum
*/
public String getPolNum() {
return polNum;
}
/**
* polNum setter.
*
* @param polNumIn polNumIn
*/
public void setPolNum(final String polNumIn) {
this.polNum = polNumIn;
}
/**
* ccfileRcvdDtm getter.
*
* @return ccfileRcvdDtm
*/
public Date getCcfileRcvdDtm() {
return ccfileRcvdDtm;
}
/**
* ccfileRcvdDtm setter.
*
* @param ccfileRcvdDtmIn ccfileRcvdDtmIn
*/
public void setCcfileRcvdDtm(final Date ccfileRcvdDtmIn) {
this.ccfileRcvdDtm = ccfileRcvdDtmIn;
}
/**
* ccfileEvntDsc getter.
*
* @return ccfileEvntDsc
*/
public String getCcfileEvntDsc() {
return ccfileEvntDsc;
}
/**
* ccfileEvntDsc setter.
*
* @param ccfileEvntDscIn ccfileEvntDscIn
*/
public void setCcfileEvntDsc(final String ccfileEvntDscIn) {
this.ccfileEvntDsc = ccfileEvntDscIn;
}
/**
* ccfileEvntStatDsc getter.
*
* @return ccfileEvntStatDsc
*/
public String getCcfileEvntStatDsc() {
return ccfileEvntStatDsc;
}
/**
* ccfileEvntStatDsc setter.
*
* @param ccfileEvntStatDscIn ccfileEvntStatDscIn
*/
public void setCcfileEvntStatDsc(final String ccfileEvntStatDscIn) {
this.ccfileEvntStatDsc = ccfileEvntStatDscIn;
}
/**
* ccfileMbrCnt getter.
*
* @return ccfileMbrCnt
*/
public int getCcfileMbrCnt() {
return ccfileMbrCnt;
}
/**
* ccfileMbrCnt setter.
*
* @param ccfileMbrCntIn ccfileMbrCntIn
*/
public void setCcfileMbrCnt(final int ccfileMbrCntIn) {
this.ccfileMbrCnt = ccfileMbrCntIn;
}
/**
* ccfile10pctChgInd getter.
*
* @return ccfile10pctChgInd
*/
public String getCcfile10pctChgInd() {
return ccfile10pctChgInd;
}
/**
* ccfile10pctChgInd setter.
*
* @param ccfile10pctChgIndIn ccfile10pctChgIndIn
*/
public void setCcfile10pctChgInd(final String ccfile10pctChgIndIn) {
this.ccfile10pctChgInd = ccfile10pctChgIndIn;
}
/**
* ccpflIsActiveInd getter.
*
* @return ccpflIsActiveInd
*/
public String getCcpflIsActiveInd() {
return ccpflIsActiveInd;
}
/**
* ccpflIsActiveInd setter.
*
* @param ccpflIsActiveIndIn ccpflIsActiveIndIn
*/
public void setCcpflIsActiveInd(final String ccpflIsActiveIndIn) {
this.ccpflIsActiveInd = ccpflIsActiveIndIn;
}
}
objs[1] is the problematic field, it should be returning a 5 char String but Hibernate is only bringing back the first char of the string and it must be down to the fact that the db field is a char type instead of varchar.
Therefore I need to instruct Hibernate in some way to bring back the full string instead of the first char only.
Login
FAQ
Search
