Beispiel #1
0
  private void appendOrderByBuffer(StringBuffer sqlBuf, Class objClass) throws Exception {
    if (ClassTool.isNullObj(this.orderByBuf)) {
      this.orderByBuf = new StringBuffer();
    }

    this.orderByBuf.trimToSize();
    if (this.orderByBuf.length() > 0 || this.getPageDto() != null) {

      if (this.orderByBuf.length() > 0) {
        sqlBuf.append(" order by ");
        sqlBuf.append(this.orderByBuf);
      }

      if (this.getPageDto() != null) {
        DBTool.parseSortAndFilter(this.getPageDto());

        if (!StringTool.isEmpty(this.getPageDto().getSort())) {
          if (this.orderByBuf.length() == 0) {
            sqlBuf.append(" order by ");
          } else if (this.orderByBuf.length() > 0) {
            sqlBuf.append(", ");
          }

          String sort =
              ClassTool.getDBColumnNameByObjFieldName(objClass, this.getPageDto().getSort());
          if (StringTool.isEmpty(sort)) {
            sort = StringTool.translateToDBColumn(this.getPageDto().getSort());
          }
          sqlBuf.append(sort);
          sqlBuf.append(" ");
          sqlBuf.append(this.getPageDto().getDir());
        }
      }
    }
  }
Beispiel #2
0
  private void insertDto(Object dto, String idFieldName) throws Exception {
    Connection conn = null;
    Exception ex = null;
    String sql = "";
    try {
      conn = DBManager.getInstance().getDataSource().getConnection();
      /*
      Statement stmt = conn.createStatement();
      sql = DBTool.getInsertSqlFromObjectClass(dto);
      stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
      ResultSet rs = stmt.getGeneratedKeys();
      */
      sql = DBTool.getInsertSqlFromObjectClass(dto);
      PreparedStatement pstmt = (PreparedStatement) conn.prepareStatement(sql, new String[] {"ID"});
      pstmt.executeUpdate();
      ResultSet rs = pstmt.getGeneratedKeys();

      if (rs != null) {
        if (rs.next()) {
          int id = rs.getInt(1);
          ClassTool.invokeSetMethod(dto, idFieldName, String.valueOf(id));
          rs.close();
        }
      }
    } catch (Exception e) {
      LogTool.logError(e, this.getClass().getName());
      LogTool.logText(sql, this.getClass().getName());
      ex = e;
    } finally {
      DBManager.getInstance().closeDBConn(conn);
      if (ex != null) throw ex;
    }
  }
Beispiel #3
0
  private void appendLimitBuffer(StringBuffer sqlBuf) {
    if (this.getPageDto() != null) {
      if (!ClassTool.isNullObj(sqlBuf)) {
        /*
        sqlBuf = new StringBuffer();
        sqlBuf.insert(0, " select * from (select rownum r, t.* from ( ");
        sqlBuf.append(" ) t where rownum < ");
        sqlBuf.append(Integer.parseInt(this.getPageDto().getStart()) + Integer.parseInt(this.getPageDto().getLimit()));
        sqlBuf.append(" ) where r > =");
        sqlBuf.append(this.getPageDto().getStart());
        */
        String pageSql =
            DBTool.getCurrPageSql(
                sqlBuf.toString(),
                Integer.parseInt(this.getPageDto().getStart()),
                Integer.parseInt(this.getPageDto().getLimit()));

        sqlBuf.delete(0, sqlBuf.length());

        sqlBuf.append(pageSql);
      }

      /*
      sqlBuf.append(" limit ");
      sqlBuf.append(this.getPageDto().getStart());
      sqlBuf.append(", ");
      sqlBuf.append(this.getPageDto().getLimit());
      */
    }
  }
Beispiel #4
0
  private void appendWhereBuffer(StringBuffer sqlBuf) throws Exception {
    if (ClassTool.isNullObj(this.whereBuf)) {
      this.whereBuf = new StringBuffer();
    }

    this.whereBuf.trimToSize();
    if (this.whereBuf.length() > 0 || this.getPageDto() != null) {
      if (this.getPageDto() != null) {
        DBTool.parseSortAndFilter(this.getPageDto());

        if (!StringTool.isEmpty(this.getPageDto().getFilter())) {
          this.getPageDto()
              .setFilterOperator(
                  StringTool.isEmpty(
                      this.getPageDto().getFilterOperator(), GeneralConstants.EQUAL_MARK));

          if (this.whereBuf.length() > 0) {
            whereBuf.append(" and ");
          }

          whereBuf.append(this.getPageDto().getFilter());

          whereBuf.append(" ");

          if ("like".equalsIgnoreCase(this.getPageDto().getFilterOperator())) {
            if (StringTool.isEmpty(this.getPageDto().getFilterValue().trim())) {
              this.getPageDto().setFilterOperator(GeneralConstants.EQUAL_MARK);
            }
          }

          whereBuf.append(this.getPageDto().getFilterOperator());

          if ("like".equalsIgnoreCase(this.getPageDto().getFilterOperator())
              || "not like".equalsIgnoreCase(this.getPageDto().getFilterOperator())) {
            whereBuf.append(" '%");
            whereBuf.append(this.getPageDto().getFilterValue().trim());
            whereBuf.append("%' and ");
            whereBuf.append(this.getPageDto().getFilter());
            whereBuf.append(" != ' ' ");
          } else {
            whereBuf.append(" '");
            whereBuf.append(this.getPageDto().getFilterValue().trim());
            whereBuf.append("'");

            if (StringTool.isEmpty(this.getPageDto().getFilterValue().trim())) {
              if (GeneralConstants.EQUAL_MARK.equals(this.getPageDto().getFilterOperator())) {
                whereBuf.append(" or ");
                whereBuf.append(this.getPageDto().getFilter());
                whereBuf.append(" IS NULL ");
              } else if (GeneralConstants.NOT_EQUAL_MARK.equals(
                  this.getPageDto().getFilterOperator())) {
                whereBuf.append(" and ");
                whereBuf.append(this.getPageDto().getFilter());
                whereBuf.append(" IS NOT NULL ");
              }
            }
          }
        }
      }

      if (this.whereBuf.length() > 0) {
        sqlBuf.append(" where ");
        sqlBuf.append(this.whereBuf);
      }
    }
  }
Beispiel #5
0
  private List<Object> selectDtoList(Class objClass, String originalSql, DataSource ds)
      throws Exception {
    List<Object> ret = new ArrayList<Object>();
    Connection conn = null;
    Exception ex = null;
    StringBuffer sqlBuf = new StringBuffer();

    try {
      sqlBuf.append(originalSql);

      this.appendWhereBuffer(sqlBuf);
      this.appendOrderByBuffer(sqlBuf, objClass);
      this.appendLimitBuffer(sqlBuf);

      String sql = sqlBuf.toString();

      LogTool.debugText("sql = " + sql, this.getClass().getName());

      conn = ds.getConnection();
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery(sql);
      if (rs != null) {
        while (rs.next()) {
          if (ClassTool.isNullObj(objClass) || "list".equalsIgnoreCase(objClass.getName())) {
            ret.add(DBTool.getResultSetColumnInList(rs));
          } else {
            ret.add(objClass.cast(ClassTool.extractValueFromResultSet(objClass, rs)));
          }
        }
      }
    } catch (Exception e) {
      LogTool.logError(e, this.getClass().getName());
      LogTool.logText(sqlBuf.toString(), this.getClass().getName());
      ex = e;
    } finally {
      try {
        if (ex != null) {
          throw ex;
        } else {
          if (!ClassTool.isListEmpty(ret)) {
            if (ret.get(0) != null) {
              String selectCountSql = "";
              if (!StringTool.isEmpty(originalSql)) {
                selectCountSql = DBTool.getSelectCountSqlFromSelectSql(originalSql);
              } else {
                selectCountSql = DBTool.getSelectCountSqlFromObjectClass(objClass);
              }
              if (!StringTool.isEmpty(selectCountSql)) {
                long totalRecordsCountInThisSearch = this.selectDtoListCount(selectCountSql, conn);
                if (ClassTool.isNullObj(objClass) || "list".equalsIgnoreCase(objClass.getName())) {
                  if (ret.get(0) instanceof List) {
                    List tmpList = (List) ret.get(0);
                    if (!ClassTool.isListEmpty(tmpList)) {
                      BasePageDTO.class
                          .cast(tmpList.get(0))
                          .setTotalRecordsCountInThisSearch(totalRecordsCountInThisSearch);
                    }
                  }
                } else {
                  BasePageDTO.class
                      .cast(ret.get(0))
                      .setTotalRecordsCountInThisSearch(totalRecordsCountInThisSearch);
                }
              }
            }
          }
        }
      } catch (Exception e) {
        LogTool.logError(e, this.getClass().getName());
        ex = e;
      } finally {
        DBManager.getInstance().closeDBConn(conn);

        if (ex != null) {
          throw ex;
        }
        return ret;
      }
    }
  }