2009/05/20 - Apache Shale has been retired.
For more information, please explore the Attic.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 package org.apache.shale.examples.sqlbrowser;
21
22 import java.sql.Connection;
23 import java.sql.PreparedStatement;
24 import java.sql.ResultSet;
25 import java.sql.ResultSetMetaData;
26 import java.sql.SQLException;
27 import java.util.List;
28 import javax.faces.application.FacesMessage;
29 import javax.faces.component.UIColumn;
30 import javax.faces.component.UIComponent;
31 import javax.faces.component.html.HtmlDataTable;
32 import javax.faces.component.html.HtmlOutputText;
33 import javax.faces.context.FacesContext;
34 import javax.faces.model.ResultSetDataModel;
35 import javax.sql.DataSource;
36 import org.apache.shale.tiger.managed.Bean;
37 import org.apache.shale.tiger.managed.Scope;
38 import org.apache.shale.tiger.view.Destroy;
39 import org.apache.shale.tiger.view.Prerender;
40 import org.apache.shale.tiger.view.View;
41
42 /***
43 * <p>Backing bean for the SQL query demo page.</p>
44 */
45 @Bean(name="query", scope=Scope.REQUEST)
46 @View
47 public class Query {
48
49
50
51
52
53 /***
54 * <p>The JDBC connection we will be using.</p>
55 */
56 private Connection conn = null;
57
58
59 /***
60 * <p>The JDBC result set we will be using.</p>
61 */
62 private ResultSet rs = null;
63
64
65 /***
66 * <p>The JDBC statement we will be using.</p>
67 */
68 private PreparedStatement stmt = null;
69
70
71
72
73
74 /***
75 * <p>Flag indicating whether the dynamically constructed table has been
76 * completed, and should therefore be displayed.</p>
77 */
78 private boolean completed = false;
79
80 public boolean isCompleted() {
81 return this.completed;
82 }
83
84 public void setCompleted(boolean completed) {
85 this.completed = completed;
86 }
87
88
89 /***
90 * <p>The name of the JNDI data source we should use for querying.</p>
91 */
92 private String dataSource = null;
93
94 public String getDataSource() {
95 return this.dataSource;
96 }
97
98 public void setDataSource(String dataSource) {
99 this.dataSource = dataSource;
100 }
101
102
103
104 /***
105 * <p>The SQL query we should execute.</p>
106 */
107 private String query = null;
108
109 public String getQuery() {
110 return this.query;
111 }
112
113 public void setQuery(String query) {
114 this.query = query;
115 }
116
117
118 /***
119 * <p>The results table we will dynamically construct.</p>
120 */
121 private HtmlDataTable results = new HtmlDataTable();
122
123 public HtmlDataTable getResults() {
124 return this.results;
125 }
126
127 public void setResults(HtmlDataTable results) {
128 this.results = results;
129 }
130
131
132 /***
133 * <p>The <code>ResultSetDataModel</code> to which we will bind
134 * our data table component.</p>
135 */
136 private ResultSetDataModel resultSetDataModel = null;
137
138 public ResultSetDataModel getResultSetDataModel() {
139 return this.resultSetDataModel;
140 }
141
142
143
144
145
146 /***
147 * <p>Process the submit of the query form.</p>
148 */
149 public String execute() {
150
151 return null;
152
153 }
154
155
156
157
158
159 /***
160 * <p>Perform the dynamic query, and set up the corresponding columns.</p>
161 */
162 @Prerender
163 public void prerender() {
164
165
166 if ((query == null) || (query.length() < 1)) {
167 return;
168 }
169
170
171 FacesContext context = FacesContext.getCurrentInstance();
172 try {
173
174
175
176 DataSource ds = (DataSource)
177 context.getExternalContext().getApplicationMap().get(Listener.INTERNAL_DATA_SOURCE);
178 conn = ds.getConnection();
179
180
181 stmt = conn.prepareStatement(query,
182 ResultSet.TYPE_SCROLL_INSENSITIVE,
183 ResultSet.CONCUR_READ_ONLY);
184 rs = stmt.executeQuery();
185
186
187 ResultSetMetaData rsmd = rs.getMetaData();
188 resultSetDataModel = new ResultSetDataModel(rs);
189 results.setFirst(0);
190
191
192 List children = results.getChildren();
193 children.clear();
194 for (int i = 1; i <= rsmd.getColumnCount(); i++) {
195 UIColumn column = new UIColumn();
196 column.setId("column" + i);
197 children.add(column);
198 HtmlOutputText header = new HtmlOutputText();
199 String label = rsmd.getColumnLabel(i);
200 if ((label == null) || (label.length() < 1)) {
201 label = rsmd.getColumnName(i);
202 }
203 header.setValue(label);
204 column.setHeader(header);
205 HtmlOutputText data = new HtmlOutputText();
206 data.setId("data" + i);
207 data.setValueBinding("value",
208 context.getApplication().createValueBinding("#{current['" + rsmd.getColumnName(i) + "']}"));
209 column.getChildren().add(data);
210 }
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225 completed = true;
226
227 } catch (Exception e) {
228
229 context.addMessage
230 (null, new FacesMessage("Exception executing this query: " + e));
231 while (e != null) {
232 context.getExternalContext().
233 log("Exception executing this query", e);
234 if (e instanceof SQLException) {
235 e = ((SQLException) e).getNextException();
236 } else {
237 e = null;
238 }
239 }
240 setCompleted(false);
241
242 }
243
244 }
245
246
247 /***
248 * <p>Clean up after rendering is complete, and close the connection
249 * (which returns it to the connection pool).</p>
250 */
251 @Destroy
252 public void destroy() {
253
254
255 if (rs != null) {
256 try {
257 rs.close();
258 } catch (SQLException e) {
259 }
260 }
261
262
263 if (stmt != null) {
264 try {
265 stmt.close();
266 } catch (SQLException e) {
267 }
268 }
269
270
271 if (conn != null) {
272 try {
273 conn.close();
274 } catch (SQLException e) {
275 }
276 }
277
278 }
279
280
281 }