sqlcommand.java

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

/*
 * $Id: SQLCommand.java,v 1.10 2005/10/10 17:01:15 rbair Exp $
 *
 * Copyright 2005 Sun Microsystems, Inc., 4150 Network Circle,
 * Santa Clara, California 95054, U.S.A. All rights reserved.
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 * 
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 * 
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
 */

package org.jdesktop.dataset.provider.sql;

import java.beans.PropertyChangeListener;
import java.beans.PropertyChangeSupport;
import java.sql.PreparedStatement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.jdesktop.dataset.DataColumn;
import org.jdesktop.dataset.DataRow;

/**
 * <p>A fully customizeable DataCommand for use with a {@link SQLDataProvider}. This
 * DataCommand requires the user to specify their select, insert, update and
 * delete sql statements manually using {@link #setSelectSQL(String)}, 
 * {@link #setInsertSQL(String)}, {@link #setUpdateSQL(String)}, 
 * {@link #setDeleteSQL(String)}. 
 * 
 * <p>The SQL for these statements can
 * contain named parameters. As a DataCommand, the SQLCommand can track values for named
 * parameters ({@link org.jdesktop.dataset.DataCommand#setParameter(String, Object)}, so that when the 
 * SQLDataProvider requests the PreparedStatement to execute, the parameters in the SQL
 * statement are assigned the values stored in the DataCommand. For example, suppose your
 * SQLCommand instance was only to work with a row in the Employee table for "Jones". You'd
 * do the following:
 * <pre>
 * SQLCommand cmd = new SQLCommand();
 * cmd.setSelectSql("Select * from Employee where name = :emp-name");
 * cmd.setParameter("emp-name", "Jones");
 * </pre>
 * The INSERT, UPDATE, and DELETE statements can access any of the parameters defined in this
 * DataCommand as well.
 * 
 * <p>SQLCommand also allows you to specify a simplified set of criteria to base your query on. You may,
 * for instance, simply specify the name of the table and the select, insert, update and delete queries
 * will be automatically generated for you. A flag indicates whether to use custom SQL queries, or to use
 * the simplified query generation routines</p>
 *
 * @author rbair
 */
 // TODO Optionally, a count SQL statement can be specified which will be used for provider better feedback to the user (percent done, total number of records to read, time estimate, etc).
public class SQLCommand extends AbstractSqlCommand {
    private String deleteSql;
    private String insertSql;
    private String selectSql;
    private String updateSql;
    private boolean custom;
    /**
     * The name of the table from which to get results. If this value is
     * null, then the TableCommand is in an uninitialized state
     */
    private String tableName;
    /**
     * The where clause for this query. This is never null, but may be empty
     */
    private String whereClause = "";
    /**
     * The order by clause for this query. This is never null, but may be empty
     */
    private String orderByClause = "";
    /**
     * The having clause for this query. This is never null, but may be empty
     */
    private String havingClause = "";
    
    /**
     * Helper used for notifying of bean property changes.
     */
    private PropertyChangeSupport pcs = new PropertyChangeSupport(this);
    
    /** 
     * Creates a new instance of TableCommand 
     */
    public SQLCommand() {
    }

    public SQLCommand(String tableName) {
        this(tableName, null);
    }
    
    public SQLCommand(String tableName, String whereClause) {
        setTableName(tableName);
        setWhereClause(whereClause);
    }

    /**
     */
    public void setSelectSQL(String sql) {
        // TODO: this is a string, why use != (PWW 04/25/05)
        if (this.selectSql != sql) {
            String oldValue = this.selectSql;
            this.selectSql = sql;
            custom = true;
            pcs.firePropertyChange("selectSql", oldValue, sql);
        }
    }
    
    public String getSelectSQL() {
        return selectSql;
    }
    
    /**
     */
    public void setUpdateSQL(String sql) {
        if (this.updateSql != sql) {
            String oldValue = this.updateSql;
            this.updateSql = sql;
            custom = true;
            pcs.firePropertyChange("updateSql", oldValue, sql);
        }
    }
    
    public String getUpdateSQL() {
        return updateSql;
    }
    
    /**
     */
    public void setInsertSQL(String sql) {
        if (this.insertSql != sql) {
            String oldValue = this.insertSql;
            this.insertSql = sql;
            custom = true;
            pcs.firePropertyChange("insertSql", oldValue, sql);
        }
    }
    
    public String getInsertSQL() {
        return insertSql;
    }
    
    /**
     */
    public void setDeleteSQL(String sql) {
        if (this.deleteSql != sql) {
            String oldValue = this.deleteSql;
            this.deleteSql = sql;
            custom = true;
            pcs.firePropertyChange("deleteSql", oldValue, sql);
        }
    }
    
    public String getDeleteSQL() {
        return deleteSql;
    }
    
    public boolean isCustom() {
        return custom;
    }
    
    public void setCustom(boolean c) {
        if (custom != c) {
            custom = c;
            pcs.firePropertyChange("custom", !custom, custom);
        }
    }
    
    /**
     * Sets the name of the table in the Database from which to load/save
     * data
     */
    public void setTableName(String tableName) {
        if (this.tableName != tableName) {
            String oldValue = this.tableName;
            this.tableName = tableName;
            pcs.firePropertyChange("tableName", oldValue, tableName);
        }
    }
    
    public String getTableName() {
        return tableName;
    }
    
    /**
     * Sets the where clause to use in the query. This clause *must* include
     * the &quot;where&quot; keyword
     */
    public void setWhereClause(String clause) {
        if (whereClause != clause) {
            String oldValue = this.whereClause;
            whereClause = clause == null ? "" : clause;
            pcs.firePropertyChange("whereClause", oldValue, whereClause);
        }
    }
    
    public String getWhereClause() {
        return whereClause;
    }
    
    public void setOrderByClause(String clause) {
        if (orderByClause != clause) {
            String oldValue = this.orderByClause;
            orderByClause = clause == null ? "" : clause;
            pcs.firePropertyChange("orderByClause", oldValue, orderByClause);
        }
    }
    
    public String getOrderByClause() {
        return orderByClause;
    }
    
    public void setHavingClause(String clause) {
        if (havingClause != clause) {
            String oldValue = this.havingClause;
            havingClause = clause == null ? "" : clause;
            pcs.firePropertyChange("havingClause", oldValue, havingClause);
        }
    }
    
    public String getHavingClause() {
        return havingClause;
    }
    
    /**
     * 
     * @param listener
     */
    public void addPropertyChangeListener(PropertyChangeListener listener) {
        pcs.addPropertyChangeListener(listener);
    }

    /**
     * 
     * @param propertyName
     * @param listener
     */
    public void addPropertyChangeListener(String propertyName,
            PropertyChangeListener listener) {
        pcs.addPropertyChangeListener(propertyName, listener);
    }
    
    /**
     *
     */
    public void removePropertyChangeListener(String propertyName, PropertyChangeListener listener) {
    	pcs.removePropertyChangeListener(propertyName, listener);
    }

    public String[] getParameterNames() {
        if (custom) {
            return super.getParameterNames(new String[]{selectSql, updateSql, insertSql, deleteSql});
        } else {
            return super.getParameterNames(new String[]{whereClause, orderByClause, havingClause});
        }
    }

    private PreparedStatement createPreparedStatement(String parameterizedSql, JDBCDataConnection conn) throws Exception {
        //replace all of the named parameters in the sql with their