Exemple #1
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;
    }
  }
Exemple #2
0
  private long selectDtoListCount(String originalSql, Connection conn) throws Exception {
    long ret = 0;
    Exception ex = null;
    StringBuffer sqlBuf = new StringBuffer();

    try {
      sqlBuf.append(originalSql);
      String whereClause = this.whereBuf.toString().trim();
      if (whereClause.length() > 0) {
        sqlBuf.append(" where ");
        sqlBuf.append(whereClause);
      }

      String sql = sqlBuf.toString();

      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery(sql);
      if (rs != null) {
        if (rs.next()) {
          ret = rs.getLong(1);
        }
      }
    } catch (Exception e) {
      LogTool.logError(e, this.getClass().getName());
      LogTool.logText(sqlBuf.toString(), this.getClass().getName());
      ex = e;
    } finally {
      if (ex != null) throw ex;
      return ret;
    }
  }
Exemple #3
0
  /*
   * execute select sql and return hashtable, key==column name, value==column value
   */
  protected List<Map<String, String>> executeSelectSql(String sql, DataSource ds) throws Exception {
    List<Map<String, String>> ret = new ArrayList<Map<String, String>>();

    Connection conn = null;
    Exception ex = null;
    StringBuffer sqlBuf = new StringBuffer();

    try {
      conn = ds.getConnection();
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery(sql);

      if (rs != null) {
        ResultSetMetaData rsMd = rs.getMetaData();
        int columnCount = rsMd.getColumnCount();

        while (rs.next()) {
          Map<String, String> rowDataMap = new Hashtable<String, String>();

          for (int i = 1; i < columnCount + 1; i++) {
            String columnName = rsMd.getColumnName(i);
            String columnTypeName = rsMd.getColumnTypeName(i);
            String columnValue = "";

            if (columnTypeName.toLowerCase().indexOf("int") != -1) {
              columnValue = String.valueOf(rs.getInt(columnName));
            } else if (columnTypeName.toLowerCase().indexOf("varchar") != -1
                || columnTypeName.toLowerCase().indexOf("text") != -1) {
              columnValue = String.valueOf(rs.getString(columnName));
            } else if (columnTypeName.toLowerCase().indexOf("float") != -1) {
              columnValue = String.valueOf(rs.getFloat(columnName));
            } else if (columnTypeName.toLowerCase().indexOf("double") != -1) {
              columnValue = String.valueOf(rs.getDouble(columnName));
            } else if (columnTypeName.toLowerCase().indexOf("date") != -1) {
              columnValue = String.valueOf(rs.getDate(columnName));
            } else if (columnTypeName.toLowerCase().indexOf("boolean") != -1) {
              columnValue = String.valueOf(rs.getBoolean(columnName));
            } else if (columnTypeName.toLowerCase().indexOf("decimal") != -1) {
              columnValue = String.valueOf(rs.getBigDecimal(columnName));
            }

            rowDataMap.put(columnName, columnValue);
            ret.add(rowDataMap);
          }
        }
      }
    } catch (Exception e) {
      LogTool.logError(e, this.getClass().getName());
      LogTool.logText(sqlBuf.toString(), this.getClass().getName());
      ex = e;
    } finally {
      DBManager.getInstance().closeDBConn(conn);
      if (ex != null) {
        throw ex;
      }
      return ret;
    }
  }
Exemple #4
0
 /*
  * execute update sql
  */
 public void executeUpdateOrDeleteSql(String sql, DataSource ds) throws Exception {
   Connection conn = null;
   Exception ex = null;
   try {
     conn = ds.getConnection();
     Statement stmt = conn.createStatement();
     stmt.executeUpdate(sql);
   } 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;
   }
 }
Exemple #5
0
  protected int selectDtoCount() throws Exception {
    int ret = 0;
    this.appendWhereBuffer(this.sqlBuf);

    Connection conn = null;
    Exception ex = null;
    try {
      conn = DBManager.getInstance().getDataSource().getConnection();
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery(this.sqlBuf.toString());
      if (rs.next()) {
        ret = rs.getInt(1);
      }
    } catch (Exception e) {
      LogTool.logError(e, this.getClass().getName());
      LogTool.logText(this.sqlBuf.toString(), this.getClass().getName());
      ex = e;
    } finally {
      DBManager.getInstance().closeDBConn(conn);
      if (ex != null) throw ex;
      return ret;
    }
  }
Exemple #6
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;
      }
    }
  }