tablecommand.java

DataBuffer在Java中使用ADO.NET

	String sql = "";
        try {
            //construct the select sql by combining the tableName portion and
            //the various clause portions
            StringBuilder buffer = new StringBuilder();
            buffer.append("select * from ");
            buffer.append("\"").append(tableName).append("\"");
            buffer.append(" ");
            buffer.append(whereClause);
            buffer.append(" ");
            buffer.append(orderByClause);
            buffer.append(" ");
            buffer.append(havingClause);
            sql = buffer.toString().trim();
            LOG.log(Level.FINE, "Generated Select SQL: " + sql);
            return createPreparedStatement(sql, conn);
        } catch (Exception e) {
	    LOG.log(Level.WARNING, "Problem with creating select SQL statement {0}", sql);
	    LOG.log(Level.WARNING, e.getMessage(), e);
            return null;
        }
    }

    protected PreparedStatement getUpdateStatement(JDBCDataConnection conn, DataRow row) throws Exception {
        if (tableName == null) {
            //this TableCommand has not been configured, throw an exception
            throw new Exception("TableCommand not configured with a table name");
        }

	//use the custom SQL, if there is any
	if (super.getUpdateSQL() != null) {
	    return super.getUpdateStatement(conn, row);
	}
	
	String sql = "";
        try {
            Map<String,Object> values = new HashMap<String,Object>();
            //construct the select sql by combining the tableName portion and
            //the various clause portions
            StringBuilder buffer = new StringBuilder();
            buffer.append("update ");
            buffer.append("\"").append(tableName).append("\"");
            buffer.append(" set ");
            //iterate over all of the columns in the row. Each cell that has been
            //modified needs to be included in this update statement
            List<DataColumn> columns = getUpdateableColumns(row.getTable().getColumns());
            int modCount = 0;
            for (int i=0; i<columns.size(); i++) {
                DataColumn col = columns.get(i);
                if (row.isModified(col)) {
                    buffer.append("\"").append(col.getName()).append("\"");
                    buffer.append(" = :" + col.getName() + ", ");
                    values.put(col.getName(), row.getValue(col));
                    modCount++;
                }
            }
            //if nothing was modified, skip this row
            if (modCount == 0) {
                return null;
            }
            //remove the trailing comma
            buffer.delete(buffer.length()-2, buffer.length());
            
            //do the where clause
            buffer.append(" where ");
            int keyColCount = 0;
            for (int i=0; i<columns.size(); i++) {
                DataColumn col = columns.get(i);
                if (col.isKeyColumn()) {
                    buffer.append("\"").append(col.getName()).append("\"");
                    buffer.append(" = :orig_" + col.getName() + " and ");
                    values.put("orig_" + col.getName(), row.getReferenceValue(col));
                    keyColCount++;
                }
            }
            if (keyColCount == 0) {
                LOG.log(Level.WARNING, "!!! No key columns were specified, the entire table '{0}' will be updated!!", tableName);
                //remove the where clause
                buffer.delete(buffer.length() - 7, buffer.length());
            } else {
                buffer.delete(buffer.length() - 4, buffer.length());
            }
            
            sql = buffer.toString().trim();
            LOG.log(Level.FINE, "Generated Update SQL: " + sql);
            return super.prepareStatement(sql, values, conn);
        } catch (Exception e) {
	    LOG.log(Level.WARNING, "Problem with creating update SQL statement {0}", sql);
	    LOG.log(Level.WARNING, e.getMessage(), e);
            return null;
        }
    }

    protected PreparedStatement getInsertStatement(JDBCDataConnection conn, DataRow row) throws Exception {
        if (tableName == null) {
            //this TableCommand has not been configured, throw an exception
            throw new Exception("TableCommand not configured with a table name");
        }

	//use the custom SQL, if there is any
	if (super.getInsertSQL() != null) {
	    return super.getInsertStatement(conn, row);
	}

	String sql = "";
        try {
            Map<String,Object> values = new HashMap<String,Object>();
            StringBuilder buffer = new StringBuilder();
            buffer.append("insert into ");
            buffer.append("\"").append(tableName).append("\"");
            buffer.append("(");
            List<DataColumn> cols = getUpdateableColumns(row.getTable().getColumns());
            for (DataColumn col : cols) {
                buffer.append("\"").append(col.getName()).append("\"");
                buffer.append(", ");
            }
            buffer.replace(buffer.length()-2, buffer.length(), ")");
            buffer.append(" values(");
            for (DataColumn col : cols) {
                buffer.append(":" + col.getName() + ", ");
                values.put(col.getName(), row.getValue(col));
            }
            buffer.replace(buffer.length()-2, buffer.length(), ")");
            sql = buffer.toString().trim();
            LOG.log(Level.FINE, "Generated Insert SQL: " + sql);
            return super.prepareStatement(sql, values, conn);
        } catch (Exception e) {
	    LOG.log(Level.WARNING, "Problem with creating insert SQL statement {0}", sql);
	    LOG.log(Level.WARNING, e.getMessage(), e);
            return null;
        }
    }

    protected PreparedStatement getDeleteStatement(JDBCDataConnection conn, DataRow row) throws Exception {
        if (tableName == null) {
            //this TableCommand has not been configured, throw an exception
            throw new Exception("TableCommand not configured with a table name");
        }

	//use the custom SQL, if there is any
	if (super.getDeleteSQL() != null) {
	    return super.getDeleteStatement(conn, row);
	}
	
	String sql = "";
        try {
            Map<String,Object> values = new HashMap<String,Object>();
            StringBuilder buffer = new StringBuilder();
            buffer.append("delete from ");
            buffer.append("\"").append(tableName).append("\"");
            buffer.append(" where ");
            int keyColCount = 0;
            List<DataColumn> columns = row.getTable().getColumns();
            for (int i=0; i<columns.size(); i++) {
                DataColumn col = columns.get(i);
                if (col.isKeyColumn()) {
                    buffer.append("\"").append(col.getName()).append("\"");
                    buffer.append(" = :orig_" + col.getName() + " and ");
                    values.put("orig_" + col.getName(), row.getReferenceValue(col));
                    keyColCount++;
                }
            }
            if (keyColCount == 0) {
                LOG.log(Level.WARNING, "!!! No key columns were specified, the entire table '" + tableName + "' will be deleted!!");
                //remove the where clause
                buffer.delete(buffer.length() - 7, buffer.length());
            } else {
                buffer.delete(buffer.length() - 4, buffer.length());
            }
            
            sql = buffer.toString().trim();
            LOG.log(Level.FINE, "Generated Delete SQL: " + sql);
            return super.prepareStatement(sql, values, conn);
        } catch (Exception e) {
	    LOG.log(Level.WARNING, "Problem with creating delete SQL statement {0}", sql);
	    LOG.log(Level.WARNING, e.getMessage(), e);
            return null;
        }
    }
    
    /**
     * Returns a list of columns that can be involved in an update or insert
     * statement
     */
    private List<DataColumn> getUpdateableColumns(List<DataColumn> cols) {
        List<DataColumn> results = new ArrayList<DataColumn>();
        for (DataColumn col : cols) {
            if (col.getExpression() == null || col.getExpression().equals("")) {
                results.add(col);
            }
        }
        return results;
    }
}