Change. It's something we all live with every day. We change our clothes, jobs and sometimes even spouses. Change can be a good thing. Some say not to fear change, but to embrace it! Wisdom tells us to always be prepared for change. Bill U's company likes to be prepared for change.

They have a lot of DAOs to access the data in his company's database. There is a base class that can be extended to make it easier when writing all that annoying, pesky SQL. This class has some helpful functions for getting and closing connections (sane stuff), and then there are the constants for SQL 'literals'. The class contains some helper functions where the constants are used, and some other functions where the constants are not used. This way if they change the database vendor and the definition of a SPACE changes, they can mostly just fix the constants and the data layer will still work.

public abstract class DAOJDBCBase {
  protected static final String COMMA = ",";
  protected static final String INSERT_INTO = "INSERT INTO ";
  protected static final String DELETE = "DELETE ";
  protected static final String VALUES = " VALUES ";
  protected static final String UPDATE = "UPDATE ";
  protected static final String SET = " SET ";
  protected static final String SELECT = "SELECT ";
  protected static final String COUNT = "COUNT";
  protected static final String FROM = " FROM ";
  protected static final String AND = " AND ";
  protected static final String OR = " OR ";
  protected static final String WHERE = " WHERE ";
  protected static final String SPACE = " ";
  protected static final String EQUALS = "=?";
  protected static final String EQUAL = "=";
  protected static final String GREATER_EQUAL = ">=";
  protected static final String LESS_EQUAL = "<=";
  protected static final String OUTER_JOIN = "(+)=";
  protected static final String DOT = ".";
  protected static final String PLACEHOLDER = "?";
  protected static final String STAR = "*";
  protected static final String BRACE_BEGIN = " (";
  protected static final String BRACE_END = ") ";
  protected static final String IN = " IN ";
  protected static final String BETWEEN = " BETWEEN ? AND ?";
  protected static final String ROWNUM = "ROWNUM";
  protected static final String ORDER_BY = " ORDER BY ";
  protected static final String GROUP_BY = " GROUP BY ";
  protected static final String INNER_JOIN = " INNER JOIN ";
  protected static final String ON = " ON ";
  protected static final String HAVING = " HAVING ";
  protected static final String DESC = " DESC";
  protected static final String ASC = " ASC";
  protected static final String FOR_UPDATE = " FOR UPDATE";
  protected static final String MIN = "min(";
  protected static final String ISNULL = " IS NULL ";
  protected static final String ISNOTNULL = " IS NOT NULL ";
  protected static final String CASE = " CASE ";
  protected static final String WHEN = " WHEN ";
  protected static final String THEN = " THEN ";
  protected static final String END = " END ";
  protected static final int ONE_DAY_MINUS_ONE_MILLISEC = 86399999;
  protected static final int FETCH_SIZE_1000 = 1000;
  private final boolean workOnArchive;

  protected String getInsertQuery(String viewname, 
                                  String colums,
                                  String placeholders, 
                                  String additionalColumns,
                                  String additionalPlaceholders) {
    StringBuilder sql = new StringBuilder(1024);
    sql.append(INSERT_INTO).append(viewname).append(BRACE_BEGIN).append(colums);
    if (additionalColumns != null && additionalColumns.length() != 0) {
       sql.append(COMMA).append(additionalColumns);
    }
    sql.append(BRACE_END).append(VALUES).append(BRACE_BEGIN).append(placeholders);
    if (additionalPlaceholders != null && additionalPlaceholders.length() != 0) {
       sql.append(COMMA).append(additionalPlaceholders);
    }
    sql.append(BRACE_END);
    return sql.toString();
  }

  protected String getInsertQuery(String viewname, 
                                  String colums,
                                  String placeholders, 
                                  String additionalColumns,
                                  String additionalPlaceholders) {
    StringBuilder sql = new StringBuilder(1024);
    sql.append(INSERT_INTO).append(viewname).append(BRACE_BEGIN).append(colums);
    if (additionalColumns != null && additionalColumns.length() != 0) {
       sql.append(COMMA).append(additionalColumns);
    }
    sql.append(BRACE_END).append(VALUES).append(BRACE_BEGIN).append(placeholders);
    if (additionalPlaceholders != null && additionalPlaceholders.length() != 0) {
       sql.append(COMMA).append(additionalPlaceholders);
    }
    sql.append(BRACE_END);
    return sql.toString();
  }

  protected String getUpdateQueryWithLiteral(String viewname, 
                                             String columsForUpdate,
                                             String additionalColumns, 
                                             String customerIdColumn,
                                             String textOidColumn) {
    StringBuilder sql = new StringBuilder(1024);
    sql.append(UPDATE).append(viewname).append(SET).append(columsForUpdate);
    if (additionalColumns != null && additionalColumns.length() != 0) {
       sql.append(additionalColumns);
    }
    sql.append(WHERE);
    appendCustIdClause(sql, customerIdColumn);
    sql.append(AND).append(textOidColumn).append(EQUALS);
    return sql.toString();
  }

  protected String getUpdateQueryWithLdbId(String viewname, String columsForUpdate) {
    StringBuilder sql = new StringBuilder(1024);
    sql.append(UPDATE).append(viewname).append(SET).append(columsForUpdate);
    sql.append(WHERE);
    sql.append(DBUtil.LDBID_COLUMN).append(EQUALS);
    sql.append(AND).append(DBUtil.CUST_ID_COLUMN).append(EQUALS);
    sql.append(AND).append(DBUtil.TEXT_OID_COLUMN).append(EQUALS);
    return sql.toString();
  }

  protected String addCustomerIdCondition(String custIdColumnName) {
    if (useLiteralCustomerId) {
       return " " + custIdColumnName + " = " + CustIdUtil.getCustIdAsString() + " ";
    }
    return " " + custIdColumnName + " = ? ";
  }

  protected void appendCustIdClause(StringBuilder sb, String custIdColumnName) {
    if (sb == null) {
       return;
    }
    sb.append(" ");
    sb.append(custIdColumnName);
    if (useLiteralCustomerId) {
       sb.append(EQUAL);
       sb.append(CustIdUtil.getCustIdAsString());
       sb.append(" ");
    } else {
       sb.append(EQUALS);
       sb.append(" ");
    }
  }

  protected void appendCustIdClause(StringBuffer sb, String custIdColumnName) {
    if (sb == null) {
       return;
    }
    sb.append(" ");
    sb.append(custIdColumnName);
     	
    if (useLiteralCustomerId) {
       sb.append(EQUAL);
       sb.append(CustIdUtil.getCustIdAsString());
       sb.append(" ");
    } else {
       sb.append(EQUALS);
       sb.append(" ");
    }
  }

  // Sane connection, etc. code omitted for brevity

}
[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!