SQLTemplateクラスを使った処理

問題の部分は受信の部分と、受信したもので未読の部分の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に変換するのが上手く行っていないのが原因だとおもいます。