TableCommand


1   /*
2    * $Id: TableCommand.java,v 1.1 2005/02/27 00:24:54 rbair Exp $
3    *
4    * Copyright 2004 Sun Microsystems, Inc., 4150 Network Circle,
5    * Santa Clara, California 95054, U.S.A. All rights reserved.
6    */
7   
8   package org.jdesktop.dataset.provider.sql;
9   
10  import java.beans.PropertyChangeListener  ;
11  import java.beans.PropertyChangeSupport  ;
12  import java.sql.PreparedStatement  ;
13  import java.util.HashMap  ;
14  import java.util.List  ;
15  import java.util.Map  ;
16  import org.jdesktop.dataset.DataColumn;
17  import org.jdesktop.dataset.DataRow;
18  
19  /**
20   * A simplified DataCommand for use with an SQLDataProvider. This DataCommand
21   * is based on selecting all of the columns in a given table. No joins are used.
22   * Because this is a simple table based DataCommand, it can infer the insert,
23   * update, and delete SQL statements to use.
24   * <p>
25   * If you need explicit select, insert, update, or delete DataCommands, use
26   * SelectCommand, InsertCommand, UpdateCommand or DeleteCommand, respectively.
27   *
28   * @author rbair
29   */
30  public class TableCommand extends AbstractSqlCommand {
31      /**
32       * The name of the table from which to get results. If this value is
33       * null, then the TableCommand is in an uninitialized state
34       */
35      private String   tableName;
36      /**
37       * The where clause for this query. This is never null, but may be empty
38       */
39      private String   whereClause = "";
40      /**
41       * The order by clause for this query. This is never null, but may be empty
42       */
43      private String   orderByClause = "";
44      /**
45       * The having clause for this query. This is never null, but may be empty
46       */
47      private String   havingClause = "";
48      
49      /**
50       * Helper used for notifying of bean property changes. In particular, this
51       * is used to notify of changes in the tableName or various clauses
52       */
53      private PropertyChangeSupport   pcs = new PropertyChangeSupport  (this);
54      
55      /** 
56       * Creates a new instance of TableCommand 
57       */
58      public TableCommand() {
59          this(null, null);
60      }
61      
62      public TableCommand(String   tableName) {
63          this(tableName, null);
64      }
65      
66      public TableCommand(String   tableName, String   whereClause) {
67          setTableName(tableName);
68          setWhereClause(whereClause);
69      }
70      
71      /**
72       * Sets the name of the table in the Database from which to load/save
73       * data
74       */
75      public void setTableName(String   tableName) {
76          if (this.tableName != tableName) {
77              String   oldValue = this.tableName;
78              this.tableName = tableName;
79              pcs.firePropertyChange("tableName", oldValue, tableName);
80          }
81      }
82      
83      /**
84       * Sets the where clause to use in the query. This clause *must* include
85       * the &quot;where&quot; keyword
86       */
87      public void setWhereClause(String   clause) {
88          if (whereClause != clause) {
89              String   oldValue = this.whereClause;
90              whereClause = clause == null ? "" : clause;
91              pcs.firePropertyChange("whereClause", oldValue, whereClause);
92          }
93      }
94      
95      public void setOrderByClause(String   clause) {
96          if (orderByClause != clause) {
97              String   oldValue = this.orderByClause;
98              orderByClause = clause == null ? "" : clause;
99              pcs.firePropertyChange("orderByClause", oldValue, orderByClause);
100         }
101     }
102     
103     public void setHavingClause(String   clause) {
104         if (havingClause != clause) {
105             String   oldValue = this.havingClause;
106             havingClause = clause == null ? "" : clause;
107             pcs.firePropertyChange("havingClause", oldValue, havingClause);
108         }
109     }
110     
111 //
112 //    public void executeSaveQuery(DataSet ds) {
113 //      if (ds == null) {
114 //          return;
115 //      }
116 //      
117 //        if (!(ds instanceof RowSetDataSet)) {
118 //            throw new IllegalArgumentException("The SimpleJDBCQueryTask " +
119 //                    "cannot save data sources that it did not generate");
120 //        }
121 //        
122 //        try {
123 //            CachedRowSet crs = ((RowSetDataSet)ds).getRowSet();
124 //            /*
125 //             * HACK! This next line (setTransactionIsolation) is a total hack,
126 //             * needed because the RI for CachedRowSetWriter tries to set the
127 //             * transaction isolation level to the CachedRowSets level.
128 //             * Unfortunately, the default level is unacceptable for HSQL.
129 //             * The RI probably needs to be hacked so that the CachedRowSetImpl
130 //             * will have its transaction isolation set to a level acceptable
131 //             * by the Database.
132 //             */
133 //            crs.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
134 //            crs.acceptChanges(dsc.getConnection());
135 //            dsc.getConnection().commit();
136 //        } catch (Exception e) {
137 //            e.printStackTrace();
138 //        }
139 //    }
140 //
141     /**
142      * 
143      * @param listener
144      */
145     public void addPropertyChangeListener(PropertyChangeListener   listener) {
146         pcs.addPropertyChangeListener(listener);
147     }
148 
149     /**
150      * 
151      * @param propertyName
152      * @param listener
153      */
154     public void addPropertyChangeListener(String   propertyName,
155             PropertyChangeListener   listener) {
156         pcs.addPropertyChangeListener(propertyName, listener);
157     }
158     
159     /**
160      *
161      */
162     public void removePropertyChangeListener(String   propertyName, PropertyChangeListener   listener) {
163         pcs.removePropertyChangeListener(propertyName, listener);
164     }
165 
166     public String  [] getParameterNames() {
167         return super.getParameterNames(new String  []{whereClause, orderByClause, havingClause});
168     }
169 
170     private PreparedStatement   createPreparedStatement(String   parameterizedSql, JDBCDataConnection conn) throws Exception   {
171         //replace all of the named parameters in the sql with their
172         //corrosponding values. This is done by first converting the sql
173         //to proper JDBC sql by inserting '?' for each and every param.
174         //As this is done, a record is kept of which parameters go with
175         //which indexes. Then, the parameter values are applied.
176         //map containing the indexes for each named param
177         Map  <String  ,List  <Integer  >> indexes = new HashMap  <String  ,List  <Integer  >>();
178         String   sql = constructSql(parameterizedSql, indexes);
179 
180         PreparedStatement   ps = conn.prepareStatement(sql);
181 
182         //now, apply the given set of parameters
183         for (String   paramName : getParameterNames()) {
184             List  <Integer  > list = indexes.get(paramName);
185             if (list != null) {
186                 for (int index : list) {
187                     ps.setObject(index + 1, getParameter(paramName));
188                 }
189             }
190         }
191         return ps;
192     }
193     
194     protected PreparedStatement   getSelectStatement(JDBCDataConnection conn) throws Exception   {
195         if (tableName == null) {
196             //this TableCommand has not been configured, throw an exception
197             throw new Exception  ("TableCommand not configured with a table name");
198         }
199 
200         try {
201             //construct the select sql by combining the tableName portion and
202             //the various clause portions
203             StringBuilder   buffer = new StringBuilder  ();
204             buffer.append("select * from ");
205             buffer.append(tableName);
206             buffer.append(" ");
207             buffer.append(whereClause);
208             buffer.append(" ");
209             buffer.append(orderByClause);
210             buffer.append(" ");
211             buffer.append(havingClause);
212             String   sql = buffer.toString().trim();
213             return createPreparedStatement(sql, conn);
214         } catch (Exception   e) {
215             e.printStackTrace();
216             return null;
217         }
218     }
219 
220     protected PreparedStatement   getUpdateStatement(JDBCDataConnection conn, DataRow row) throws Exception   {
221         //XXX TODO SHOWSTOPPER!!! This SQL needs to be much, much more robust. Only the changed values
222         //should be updated, and the keycolumns are never set as the basis for the update!!!
223         
224         if (tableName == null) {
225             //this TableCommand has not been configured, throw an exception
226             throw new Exception  ("TableCommand not configured with a table name");
227         }
228 
229         try {
230             //construct the select sql by combining the tableName portion and
231             //the various clause portions
232             StringBuilder   buffer = new StringBuilder  ();
233             buffer.append("update ");
234             buffer.append(tableName);
235             buffer.append(" set ");
236             //iterate over all of the columns in the row, adding them all in here
237             for (DataColumn col : row.getTable().getColumns()) {
238                 buffer.append(col.getName());
239                 buffer.append(" = ?, ");
240             }
241             buffer.delete(buffer.length()-2, buffer.length());
242             buffer.append(" where ");
243             for (DataColumn col : row.getTable().getColumns()) {
244                 if (col.isKeyColumn()) {
245                     buffer.append(col.getName());
246                     buffer.append(" = ? and ");
247                 }
248             }
249             buffer.delete(buffer.length() - 4, buffer.length());
250             String   sql = buffer.toString().trim();
251             return createPreparedStatement(sql, conn);
252         } catch (Exception   e) {
253             e.printStackTrace();
254             return null;
255         }
256     }
257 
258     protected PreparedStatement   getInsertStatement(JDBCDataConnection conn, DataRow row) throws Exception   {
259         if (tableName == null) {
260             //this TableCommand has not been configured, throw an exception
261             throw new Exception  ("TableCommand not configured with a table name");
262         }
263 
264         try {
265             StringBuilder   buffer = new StringBuilder  ();
266             buffer.append("insert into ");
267             buffer.append(tableName);
268             buffer.append("(");
269             for (DataColumn col : row.getTable().getColumns()) {
270                 buffer.append(col.getName());
271                 buffer.append(", ");
272             }
273             buffer.replace(buffer.length()-1, buffer.length(), ")");
274             buffer.append(" values(");
275             for (DataColumn col : row.getTable().getColumns()) {
276                 buffer.append("?, ");
277             }
278             buffer.replace(buffer.length()-1, buffer.length(), ")");
279             String   sql = buffer.toString().trim();
280             return createPreparedStatement(sql, conn);
281         } catch (Exception   e) {
282             e.printStackTrace();
283             return null;
284         }
285     }
286 
287     protected PreparedStatement   getDeleteStatement(JDBCDataConnection conn, DataRow row) throws Exception   {
288         //XXX TODO SHOWSTOPPER!!! The keycolumns are never set as the basis for the delete!!!
289         
290         if (tableName == null) {
291             //this TableCommand has not been configured, throw an exception
292             throw new Exception  ("TableCommand not configured with a table name");
293         }
294 
295         try {
296             StringBuilder   buffer = new StringBuilder  ();
297             buffer.append("delete from ");
298             buffer.append(tableName);
299             buffer.append(" where ");
300             for (DataColumn col : row.getTable().getColumns()) {
301                 if (col.isKeyColumn()) {
302                     buffer.append(col.getName());
303                     buffer.append(" = ? and ");
304                 }
305             }
306             buffer.delete(buffer.length() - 4, buffer.length());
307             String   sql = buffer.toString().trim();
308             return createPreparedStatement(sql, conn);
309         } catch (Exception   e) {
310             e.printStackTrace();
311             return null;
312         }
313     }
314 }
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags