問題の部分は受信の部分と、受信したもので未読の部分のSQLです。 以下のように記述し直すことで、改善できると考えられます。
【受信したもので未読】 変更前
SELECT t0.CREATE_DATE, t0.END_DATE, t0.NOTE, t0.PARENT_ID, t0.REPORT_NAME, t0.START_DATE, t0.UPDATE_DATE, t0.USER_ID, t0.REPORT_ID FROM eip_t_report t0 WHERE (t0.REPORT_ID IN ……) AND (t0.REPORT_NAME <> '') ORDER BY t0.CREATE_DATE DESC LIMIT 20
変更後
SELECT t0.CREATE_DATE, t0.END_DATE, t0.NOTE, t0.PARENT_ID, t0.REPORT_NAME, t0.START_DATE, t0.UPDATE_DATE, t0.USER_ID, t0.REPORT_ID FROM eip_t_report_map t0, eip_t_report_map t1WHERE (t0.USER_ID = login_user_id) AND ( t0.REPORT_ID =t1.REPORT_ID ) AND ( t1.STATUS = 'U' ) AND (t0.REPORT_NAME <> '') ORDER BY t0.CREATE_DATE DESC LIMIT 20
【受信】 変更前
SELECT t0.CREATE_DATE, t0.END_DATE, t0.NOTE, t0.PARENT_ID, t0.REPORT_NAME, t0.START_DATE, t0.UPDATE_DATE, t0.USER_ID, t0.REPORT_ID FROM eip_t_report t0 WHERE (t0.REPORT_ID IN ……) AND (t0.REPORT_NAME <> '') ORDER BY t0.CREATE_DATE DESC LIMIT 20
変更後
SELECT t0.CREATE_DATE, t0.END_DATE, t0.NOTE, t0.PARENT_ID, t0.REPORT_NAME, t0.START_DATE, t0.UPDATE_DATE, t0.USER_ID, t0.REPORT_ID FROM eip_t_report t0, eip_t_report_map t1 WHERE ( t0.USER_ID = login_user_id) AND( t0.REPORT_ID = t1.REPORT_ID ) AND (t0.REPORT_NAME <> '') ORDER BY t0.CREATE_DATE DESC LIMIT 20
このSQL文はCayenneでは書けないため、SQLTemplateクラスを使って書くことに挑戦しました。 以下がそのコードです。
import com.aimluck.eip.orm.query.SQLTemplate;
import com.aimluck.eip.orm.query.CountQuery;
import com.aimluck.eip.orm.query.CustomSelectQuery;
public ResultList<EipTReport> selectList(RunData rundata, Context context) {
try {
if (ReportUtils.hasResetFlag(rundata, context)) {
ReportUtils.resetFilter(rundata, context, this.getClass().getName());
target_keyword.setValue("");
} else {
target_keyword.setValue(ReportUtils.getTargetKeyword(rundata, context));
}
SQLTemplate<EipTReport> query = getSelectQuery(rundata, context);
CustomSelectQuery delegate = new CustomSelectQuery(EipTReport.class);
CountQuery countQuery = new CountQuery(EipTReport.class);
DataContext dataContext = DataContext.getThreadDataContext();
List<EipTReport> fetchList = query.fetchList();
int totalCount = countQuery.count(dataContext, delegate.isDistinct());
int pageSize = delegate.getFetchLimit();
int num = ((int) (Math.ceil(totalCount / (double) pageSize)));
int page = 1;
if ((num > 0) && (num < page)) {
page = num;
}
ResultList<EipTReport> list =
new ResultList<EipTReport>(fetchList, page, 20, totalCount);
return list;
} catch (Exception ex) {
logger.error("report", ex);
return null;
}
}
private SQLTemplate<EipTReport> getSelectQuery(RunData rundata,
Context context) {
uid = ALEipUtils.getUserId(rundata);
Integer login_user_id =
Integer.valueOf((int) login_user.getUserId().getValue());
String login_id = login_user_id.toString();
StringBuilder select = new StringBuilder();
StringBuilder body = new StringBuilder();
if ((target_keyword != null) && (!target_keyword.getValue().equals(""))) {
ALEipUtils.setTemp(rundata, context, LIST_SEARCH_STR, target_keyword
.getValue());
} else {
ALEipUtils.removeTemp(rundata, context, LIST_SEARCH_STR);
}
if (ALEipUtils.getTemp(rundata, context, "Report_Maximize") == "false") {
// 通常画面
// 受信したもので未読
select.append("SELECT ");
select.append(" t0.create_date, ");
select.append(" t0.end_date, ");
select.append(" t0.note, ");
select.append(" t0.parent_id, ");
select.append(" t0.user_id, ");
select.append(" t0.report_id, ");
select.append(" t0.report_name, ");
select.append(" t0.start_date, ");
select.append(" t0.update_date, ");
body.append(" FROM eip_t_report t0, eip_t_report_map t1 ");
body.append(" WHERE ");
body.append(" t0.user_id = #bind($login_id) AND ");
body.append(" t1.status = 'U' ");
} else if (SUBMENU_CREATED.equals(currentSubMenu)) {
// 送信
select.append("SELECT ");
select.append(" t0.create_date, ");
select.append(" t0.end_date, ");
select.append(" t0.note, ");
select.append(" t0.parent_id, ");
select.append(" t0.user_id, ");
select.append(" t0.report_id, ");
select.append(" t0.report_name, ");
select.append(" t0.start_date, ");
select.append(" t0.update_date, ");
body.append(" FROM eip_t_report t0 ");
body.append(" WHERE ");
body.append(" t0.user_id = #bind($login_id) AND ");
} else if (SUBMENU_REQUESTED.equals(currentSubMenu)) {
// 受信
select.append("SELECT ");
select.append(" t0.create_date, ");
select.append(" t0.end_date, ");
select.append(" t0.note, ");
select.append(" t0.parent_id, ");
select.append(" t0.user_id, ");
select.append(" t0.report_id, ");
select.append(" t0.report_name, ");
select.append(" t0.start_date, ");
select.append(" t0.update_date, ");
body.append(" FROM eip_t_report t0, ");
body.append(" eip_t_report_map t1 ");
body.append(" WHERE ");
body.append(" t0.user_id = #bind($login_id) AND ");
body.append(" t0.report_id = t1.report_id AND ");
} else if (SUBMENU_ALL.equals(currentSubMenu)) {
// 全て
select.append("SELECT ");
select.append(" t0.create_date, ");
select.append(" t0.end_date, ");
select.append(" t0.note, ");
select.append(" t0.parent_id, ");
select.append(" t0.user_id, ");
select.append(" t0.report_id, ");
select.append(" t0.report_name, ");
select.append(" t0.start_date, ");
select.append(" t0.update_date, ");
body.append(" FROM eip_t_report t0 ");
body.append(" WHERE ");
}
// 検索
String search = ALEipUtils.getTemp(rundata, context, LIST_SEARCH_STR);
if (search != null && !search.equals("")) {
current_search = search;
select.append("SELECT ");
select.append(" t0.create_date, ");
select.append(" t0.end_date, ");
select.append(" t0.note, ");
select.append(" t0.parent_id, ");
select.append(" t0.user_id, ");
select.append(" t0.report_id, ");
select.append(" t0.report_name, ");
select.append(" t0.start_date, ");
select.append(" t0.update_date, ");
body.append(" FROM eip_t_report t0 ");
body.append(" WHERE ");
body.append(" t0.report_name LIKE #bind($search) AND ");
body.append(" t0.note LIKE #bind($search) AND ");
}
// replyを除く
body.append(" t0.report_name <> '' ");
StringBuilder last = new StringBuilder();
last.append(" ORDER BY t0.create_date desc ");
last.append(" LIMIT ");
last.append(20);
SQLTemplate<EipTReport> query =
Database.sql(
EipTReport.class,
select.toString() + body.toString() + last.toString()).param(
"user_id",
Integer.valueOf(uid));
return query;
}
しかし上手くいきませんでした。 ResultListに変換するのが上手く行っていないのが原因だとおもいます。


