KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > jdesktop > dataset > provider > sql > SQLDataProvider


1 /*
2  * $Id: SQLDataProvider.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 import java.sql.PreparedStatement JavaDoc;
10 import java.sql.ResultSet JavaDoc;
11 import java.sql.ResultSetMetaData JavaDoc;
12 import java.util.ArrayList JavaDoc;
13 import java.util.HashMap JavaDoc;
14 import java.util.List JavaDoc;
15 import java.util.Map JavaDoc;
16 import org.jdesktop.dataset.DataColumn;
17 import org.jdesktop.dataset.DataProvider;
18 import org.jdesktop.dataset.DataRow;
19 import org.jdesktop.dataset.DataTable;
20 import org.jdesktop.dataset.event.TableChangeEvent;
21 import org.jdesktop.dataset.provider.LoadTask;
22 import org.jdesktop.dataset.provider.SaveTask;
23
24 /**
25  * SQL based DataProvider for a JDNC DataSet. This implementation handles
26  * retrieving values from a database table, and persisting changes back
27  * to the table.
28  *
29  * @author rbair
30  */
31 public class SQLDataProvider extends DataProvider {
32     private TableCommand tableCommand;
33     private JDBCDataConnection conn;
34     
35     /**
36      * Creates a new instance of SQLDataProvider
37      */
38     public SQLDataProvider() {
39     }
40     
41     public SQLDataProvider(String JavaDoc tableName) {
42         tableCommand = new TableCommand(tableName);
43     }
44     
45     public SQLDataProvider(String JavaDoc tableName, String JavaDoc whereClause) {
46         tableCommand = new TableCommand(tableName, whereClause);
47     }
48     
49     public void setTableCommand(TableCommand command) {
50         this.tableCommand = command;
51         //TODO should have javabeans property on this...
52 }
53
54     public TableCommand getTableCommand() {
55         return tableCommand;
56     }
57     
58     public void setDataConnection(JDBCDataConnection c) {
59         this.conn = c;
60     }
61     
62     public JDBCDataConnection getDataConnection() {
63         return conn;
64     }
65     
66     /**
67      * @inheritDoc
68      */
69     protected LoadTask createLoadTask(DataTable[] tables) {
70         return new LoadTask(tables) {
71             protected void readData(DataTable[] tables) throws Exception JavaDoc {
72                 if (conn == null) {
73                     //no connection, short circuit
74 return;
75                 }
76                 if (tableCommand == null) {
77                     //there isn't any command to run, so short circuit the method
78 return;
79                 }
80                 //TODO when selectCommand exists, add it to the check here
81
82                 //set the progess count
83 setMinimum(0);
84                 setMaximum(tables.length);
85                 //construct and execute a resultset for each table in turn.
86 //as each table is finished, call scheduleLoad.
87 for (DataTable table : tables) {
88                     try {
89                         PreparedStatement JavaDoc stmt = tableCommand.getSelectStatement(conn);
90                         ResultSet JavaDoc rs = stmt.executeQuery();
91
92                         //collect the column names from the result set so that
93 //I can retrieve the data from the result set into the
94 //column based on matching column names
95 ResultSetMetaData JavaDoc md = rs.getMetaData();
96                         List JavaDoc<String JavaDoc> names = new ArrayList JavaDoc<String JavaDoc>();
97                         List JavaDoc<DataColumn> columns = table.getColumns();
98                         for (int i=0; i<columns.size(); i++) {
99                             String JavaDoc name = columns.get(i).getName();
100                             for (int j=0; j<md.getColumnCount(); j++) {
101                                 if (name.equalsIgnoreCase(md.getColumnName(j+1))) {
102                                     names.add(name);
103                                 }
104                             }
105                         }
106                         
107                         //iterate over the result set. Every 50 items, schedule a load
108 List JavaDoc<Map JavaDoc<String JavaDoc,Object JavaDoc>> rows = new ArrayList JavaDoc<Map JavaDoc<String JavaDoc,Object JavaDoc>>(60);
109                         while (rs.next()) {
110                             if (rows.size() >= 50) {
111                                 LoadItem item = new LoadItem<List JavaDoc<Map JavaDoc<String JavaDoc,Object JavaDoc>>>(table, rows);
112                                 scheduleLoad(item);
113                                 rows = new ArrayList JavaDoc<Map JavaDoc<String JavaDoc,Object JavaDoc>>(60);
114                             }
115                             //create a row
116 Map JavaDoc<String JavaDoc,Object JavaDoc> row = new HashMap JavaDoc<String JavaDoc,Object JavaDoc>();
117                             for (String JavaDoc name : names) {
118                                 row.put(name, rs.getObject(name));
119                             }
120                             rows.add(row);
121                         }
122                         //close the result set
123 rs.close();
124                         //load the remaining items
125 LoadItem item = new LoadItem<List JavaDoc<Map JavaDoc<String JavaDoc,Object JavaDoc>>>(table, rows);
126                         scheduleLoad(item);
127                     } catch (Exception JavaDoc e) {
128                         e.printStackTrace();
129                     }
130                     setProgress(getProgress() + 1);
131                 }
132                 setProgress(getMaximum());
133             }
134             
135             /**
136              * @inheritDoc
137              */
138             protected void loadData(LoadItem[] items) {
139                 for (LoadItem<List JavaDoc<Map JavaDoc<String JavaDoc,Object JavaDoc>>> item : items) {
140                     for (Map JavaDoc<String JavaDoc,Object JavaDoc> row : item.data) {
141                         DataRow r = item.table.appendRowNoEvent();
142                         for (String JavaDoc col : row.keySet()) {
143                             r.setValue(col, row.get(col));
144                         }
145                         r.setStatus(DataRow.DataRowStatus.UNCHANGED);
146                     }
147                     item.table.fireDataTableChanged(new TableChangeEvent(item.table));
148                 }
149             }
150         };
151     }
152
153     /**
154      * @inheritDoc
155      */
156     protected SaveTask createSaveTask(DataTable[] tables) {
157         return new SaveTask(tables) {
158             protected void saveData(DataTable[] tables) throws Exception JavaDoc {
159                 if (conn == null) {
160                     //no connection, short circuit
161 return;
162                 }
163                 if (tableCommand == null) {
164                     //there isn't any command to run, so short circuit the method
165 return;
166                 }
167                 //TODO when selectCommand exists, add it to the check here
168
169                 //set the progess count
170 setMinimum(0);
171                 setMaximum(tables.length);
172                 for (DataTable table : tables) {
173                     //fetch the set of rows from the table
174 List JavaDoc<DataRow> rows = table.getRows();
175                     //for each row, either insert it, update it, delete it, or
176 //ignore it, depending on the row flag
177 for (DataRow row : rows) {
178                         PreparedStatement JavaDoc stmt = null;
179                         switch (row.getStatus()) {
180                             case UPDATED:
181                                 stmt = tableCommand.getUpdateStatement(conn, row);
182                                 break;
183                             case INSERTED:
184                                 stmt = tableCommand.getInsertStatement(conn, row);
185                                 break;
186                             case DELETED:
187                                 stmt = tableCommand.getDeleteStatement(conn, row);
188                                 break;
189                             default:
190                                 //do nothing
191 break;
192                         }
193                         
194                         if (stmt != null) {
195                             conn.executeUpdate(stmt);
196                         }
197                     }
198                     setProgress(getProgress() + 1);
199                 }
200                 setProgress(getMaximum());
201             }
202         };
203     }
204     
205 }
206
Popular Tags