sqlcommand.java

java实现浏览器等本地桌面的功能

        //corrosponding values. This is done by first converting the sql
        //to proper JDBC sql by inserting '?' for each and every param.
        //As this is done, a record is kept of which parameters go with
        //which indexes. Then, the parameter values are applied.
        //map containing the indexes for each named param
        Map<String,List<Integer>> indexes = new HashMap<String,List<Integer>>();
        String sql = constructSql(parameterizedSql, indexes);

        PreparedStatement ps = conn.prepareStatement(sql);

        //now, apply the given set of parameters
        for (String paramName : getParameterNames()) {
            List<Integer> list = indexes.get(paramName);
            if (list != null) {
                for (int index : list) {
                    ps.setObject(index + 1, getParameter(paramName));
                }
            }
        }
        return ps;
    }
    
    protected PreparedStatement getSelectStatement(JDBCDataConnection conn) throws Exception {
        if (custom) {
            if (selectSql == null) {
                //this SQLCommand has not been configured, throw an exception
                throw new Exception("SQLCommand not configured with a select sql statement");
            }

            try {
                return createPreparedStatement(selectSql, conn);
            } catch (Exception e) {
                System.err.println(selectSql);
                e.printStackTrace();
                return null;
            }
        } else {
            if (tableName == null) {
                //this TableCommand has not been configured, throw an exception
                throw new Exception("TableCommand not configured with a table name");
            }

            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(tableName);
                buffer.append(" ");
                buffer.append(whereClause);
                buffer.append(" ");
                buffer.append(orderByClause);
                buffer.append(" ");
                buffer.append(havingClause);
                String sql = buffer.toString().trim();
                return createPreparedStatement(sql, conn);
            } catch (Exception e) {
                e.printStackTrace();
                return null;
            }
        }
    }
    
    protected PreparedStatement getUpdateStatement(JDBCDataConnection conn, DataRow row) throws Exception {
        if (custom) {
            if (updateSql == null) {
                //this SQLCommand has not been configured, throw an exception
                throw new Exception("SQLCommand not configured with an update sql statement");
            }

            try {
                Map<String,Object> values = new HashMap<String,Object>();
                //iterate over all of the columns in the row.
                List<DataColumn> columns = row.getTable().getColumns();
                for (int i=0; i<columns.size(); i++) {
                    DataColumn col = columns.get(i);
                    values.put(col.getName(), row.getValue(col));
                }

                //do the where clause
                int keyColCount = 0;
                for (int i=0; i<columns.size(); i++) {
                    DataColumn col = columns.get(i);
                    if (col.isKeyColumn()) {
                        values.put("orig_" + col.getName(), row.getOriginalValue(col));
                        keyColCount++;
                    }
                }

                return super.prepareStatement(updateSql, values, conn);
            } catch (Exception e) {
                System.err.println(updateSql);
                e.printStackTrace();
                return null;
            }
        } else {
            if (tableName == null) {
                //this TableCommand has not been configured, throw an exception
                throw new Exception("TableCommand not configured with a table name");
            }

            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(tableName);
                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 = 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(col.getName());
                        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(col.getName());
                        buffer.append(" = :orig_" + col.getName() + " and ");
                        values.put("orig_" + col.getName(), row.getOriginalValue(col));
                        keyColCount++;
                    }
                }
                if (keyColCount == 0) {
                    System.err.println("WARNING!!! No key columns were specified, the entire table '" + tableName + "' will be updated!!");
                    //remove the where clause
                    buffer.delete(buffer.length() - 7, buffer.length());
                } else {
                    buffer.delete(buffer.length() - 4, buffer.length());
                }

                String sql = buffer.toString().trim();
                return super.prepareStatement(sql, values, conn);
            } catch (Exception e) {
                e.printStackTrace();
                return null;
            }
        }
    }

    protected PreparedStatement getInsertStatement(JDBCDataConnection conn, DataRow row) throws Exception {
        if (custom) {
            if (insertSql == null) {
                //this SQLCommand has not been configured, throw an exception
                throw new Exception("SQLCommand not configured with an insert sql statement");
            }

            try {
                Map<String,Object> values = new HashMap<String,Object>();
                for (DataColumn col : row.getTable().getColumns()) {
                    values.put(col.getName(), row.getValue(col));
                }
                return super.prepareStatement(insertSql, values, conn);
            } catch (Exception e) {
                System.err.println(insertSql);
                e.printStackTrace();
                return null;
            }
        } else {
            if (tableName == null) {
                //this TableCommand has not been configured, throw an exception
                throw new Exception("TableCommand not configured with a table name");
            }

            try {
                Map<String,Object> values = new HashMap<String,Object>();
                StringBuilder buffer = new StringBuilder();
                buffer.append("insert into ");
                buffer.append(tableName);
                buffer.append("(");
                for (DataColumn col : row.getTable().getColumns()) {
                    buffer.append(col.getName());
                    buffer.append(", ");
                }
                buffer.replace(buffer.length()-2, buffer.length(), ")");
                buffer.append(" values(");
                for (DataColumn col : row.getTable().getColumns()) {
                    buffer.append(":" + col.getName() + ", ");
                    values.put(col.getName(), row.getValue(col));
                }
                buffer.replace(buffer.length()-2, buffer.length(), ")");
                String sql = buffer.toString().trim();
                return super.prepareStatement(sql, values, conn);
            } catch (Exception e) {
                e.printStackTrace();
                return null;
            }
        }
    }

    protected PreparedStatement getDeleteStatement(JDBCDataConnection conn, DataRow row) throws Exception {
        if (custom) {
            if (deleteSql == null) {
                //this SQLCommand has not been configured, throw an exception
                throw new Exception("SQLCommand not configured with a delete sql statement");
            }

            try {
                Map<String,Object> values = new HashMap<String,Object>();
                List<DataColumn> columns = row.getTable().getColumns();
                for (int i=0; i<columns.size(); i++) {
                    DataColumn col = columns.get(i);
                    if (col.isKeyColumn()) {
                        values.put("orig_" + col.getName(), row.getOriginalValue(col));
                    }
                }
                return super.prepareStatement(deleteSql, values, conn);
            } catch (Exception e) {
                System.err.println(deleteSql);
                e.printStackTrace();
                return null;
            }
        } else {
            if (tableName == null) {
                //this TableCommand has not been configured, throw an exception
                throw new Exception("TableCommand not configured with a table name");
            }

            try {
                Map<String,Object> values = new HashMap<String,Object>();
                StringBuilder buffer = new StringBuilder();
                buffer.append("delete from ");
                buffer.append(tableName);
                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(col.getName());
                        buffer.append(" = :orig_" + col.getName() + " and ");
                        values.put("orig_" + col.getName(), row.getOriginalValue(col));
                        keyColCount++;
                    }
                }
                if (keyColCount == 0) {
                    System.err.println("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());
                }

                String sql = buffer.toString().trim();
                return super.prepareStatement(sql, values, conn);
            } catch (Exception e) {
                e.printStackTrace();
                return null;
            }
        }
    }
}