1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17 package org.apache.portals.gems.browser;
18
19 import java.io.IOException;
20 import java.security.AccessControlContext;
21 import java.security.AccessController;
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.sql.Types;
28 import java.util.ArrayList;
29 import java.util.Iterator;
30 import java.util.List;
31
32 import javax.naming.Context;
33 import javax.naming.InitialContext;
34 import javax.portlet.ActionRequest;
35 import javax.portlet.ActionResponse;
36 import javax.portlet.PortletException;
37 import javax.portlet.PortletMode;
38 import javax.portlet.PortletPreferences;
39 import javax.portlet.PortletRequest;
40 import javax.portlet.PortletSession;
41 import javax.portlet.RenderRequest;
42 import javax.portlet.RenderResponse;
43 import javax.security.auth.Subject;
44 import javax.sql.DataSource;
45
46 import org.apache.commons.dbcp.BasicDataSource;
47 import org.apache.jetspeed.security.JSSubject;
48 import org.apache.jetspeed.sso.SSOContext;
49 import org.apache.jetspeed.sso.SSOException;
50 import org.apache.portals.bridges.util.PreferencesHelper;
51 import org.apache.portals.gems.util.StatusMessage;
52 import org.apache.portals.messaging.PortletMessaging;
53
54 /***
55 * DatabaseBrowserPortlet
56 *
57 * @author <a href="mailto:taylor@apache.org">David Sean Taylor </a>
58 * @version $Id: DatabaseBrowserPortlet.java 516448 2007-03-09 16:25:47Z ate $
59 */
60 public class DatabaseBrowserPortlet
61 extends BrowserPortlet
62 implements Browser
63 {
64 /***
65 * Execute the sql statement as specified by the user or the default, and
66 * store the resultSet in a vector.
67 *
68 * @param sql
69 * The sql statement to be executed.
70 * @param data
71 * The turbine rundata context for this request.
72 */
73 public void getRows(RenderRequest request, String sql, int windowSize)
74 throws Exception
75 {
76 List resultSetList = new ArrayList();
77 List resultSetTitleList = new ArrayList();
78 List resultSetTypeList = new ArrayList();
79
80 Connection con = null;
81 PreparedStatement selectStmt = null;
82 ResultSet rs = null;
83
84 PortletSession session = request.getPortletSession();
85 try
86 {
87 String poolname = getPreference(request, POOLNAME, null);
88 if (poolname == null || poolname.length() == 0)
89 {
90 con = getConnection(request);
91 }
92 else
93 {
94 con = getConnection(poolname);
95 }
96 selectStmt = con.prepareStatement(sql);
97
98 readSqlParameters(request);
99 Iterator it = sqlParameters.iterator();
100 int ix = 0;
101 while (it.hasNext())
102 {
103 ix++;
104 Object object = it.next();
105 selectStmt.setObject(ix, object);
106 }
107 rs = selectStmt.executeQuery();
108 ResultSetMetaData rsmd = rs.getMetaData();
109 int columnNum = rsmd.getColumnCount();
110
111
112
113
114 List userObjList = (List) session.getAttribute(USER_OBJECTS);
115 int userObjListSize = 0;
116 if (userObjList != null)
117 {
118 userObjListSize = userObjList.size();
119 }
120
121
122
123
124
125
126 boolean[] columnDisplayed = new boolean[columnNum + userObjListSize];
127
128
129
130
131
132 for (int i = 1; i <= columnNum; i++)
133 {
134 int type = rsmd.getColumnType(i);
135 if (!((type == Types.BLOB) || (type == Types.CLOB)
136 || (type == Types.BINARY)
137 || (type == Types.LONGVARBINARY) || (type == Types.VARBINARY)))
138 {
139 resultSetTitleList.add(rsmd.getColumnName(i));
140 resultSetTypeList.add(String.valueOf(type));
141 columnDisplayed[i - 1] = true;
142 } else
143 {
144 columnDisplayed[i - 1] = false;
145 }
146 }
147
148 for (int i = columnNum; i < columnNum + userObjListSize; i++)
149 {
150 ActionParameter usrObj = (ActionParameter) userObjList.get(i
151 - columnNum);
152 resultSetTitleList.add(usrObj.getName());
153 resultSetTypeList.add(usrObj.getType());
154 columnDisplayed[i] = true;
155
156
157 }
158
159
160
161 int index = 0;
162 while (rs.next())
163 {
164 List row = new ArrayList(columnNum);
165
166 for (int i = 1; i <= columnNum; i++)
167 {
168 if (columnDisplayed[i - 1])
169 {
170 Object obj = rs.getObject(i);
171 if (obj == null)
172 {
173 obj = VELOCITY_NULL_ENTRY;
174 }
175 row.add(obj);
176 }
177 }
178 for (int i = columnNum; i < columnNum + userObjListSize; i++)
179 {
180 ActionParameter usrObj = (ActionParameter) userObjList
181 .get(i - columnNum);
182 if (columnDisplayed[i])
183 {
184 Class c = Class.forName(usrObj.getType());
185 row.add(c.newInstance());
186 populate(index, i, row);
187 }
188 }
189
190 if (filter(row, request))
191 {
192 continue;
193 }
194
195 resultSetList.add(row);
196 index++;
197 }
198 BrowserIterator iterator = new DatabaseBrowserIterator(
199 resultSetList, resultSetTitleList, resultSetTypeList,
200 windowSize);
201 setBrowserIterator(request, iterator);
202
203 } catch (SQLException e)
204 {
205 throw e;
206 } finally
207 {
208 try
209 {
210 if (null != selectStmt) selectStmt.close();
211 if (null != rs) rs.close();
212 if (null != con)
213 {
214 closeConnection(con);
215 }
216
217 } catch (Exception e)
218 {
219 throw e;
220 }
221 }
222
223 }
224
225
226
227
228
229
230 public Connection getConnection(PortletRequest request)
231 throws Exception
232 {
233 Connection con = null;
234 try
235 {
236 PortletPreferences prefs = request.getPreferences();
237 String dsType = prefs.getValue("DatasourceType", null);
238 if (dsType == null)
239 {
240 throw new SQLException("No DataSource provided");
241 }
242 if (dsType.equals("jndi"))
243 {
244 Context ctx = new InitialContext();
245 String dsName = prefs.getValue("JndiDatasource", "");
246 Context envContext = (Context)ctx.lookup("java:/comp/env");
247 DataSource ds = (DataSource)envContext.lookup(dsName);
248 con = ds.getConnection();
249 }
250 else if (dsType.equals("dbcp"))
251 {
252 BasicDataSource ds = new BasicDataSource();
253 ds.setDriverClassName(prefs.getValue("JdbcDriver", ""));
254 ds.setUrl(prefs.getValue("JdbcConnection", ""));
255 ds.setUsername(prefs.getValue("JdbcUsername", ""));
256 ds.setPassword(prefs.getValue("JdbcPassword", ""));
257
258 con = ds.getConnection();
259 }
260 else if (dsType.equals("sso"))
261 {
262
263
264
265
266 BasicDataSource ds = new BasicDataSource();
267 ds.setDriverClassName(prefs.getValue("SSOJdbcDriver", ""));
268 ds.setUrl(prefs.getValue("SSOJdbcConnection", ""));
269 String ssoURL = prefs.getValue("SSOSite", "");
270
271
272 SSOContext credentials = null;
273 try
274 {
275 if (sso == null)
276 throw new SSOException("SSO Not supported.");
277
278 credentials = sso.getCredentials(getSubject(), ssoURL);
279 }
280 catch(SSOException ssoex)
281 {
282 throw new Exception("SSO credential lookup failed. Error: " + ssoex.getMessage());
283 }
284
285 String ssoUserName = credentials.getRemotePrincipalName();
286 String ssoPWD = credentials.getRemoteCredential();
287 ds.setUsername(ssoUserName);
288 ds.setPassword( ssoPWD );
289 con = ds.getConnection();
290 }
291 else
292 {
293 throw new SQLException("No DataSource provided");
294 }
295
296 }
297 catch (Exception e)
298 {
299 throw new Exception("Failed to connect", e);
300 }
301 return con;
302 }
303
304 public Connection getConnection(String poolName)
305 {
306 return null;
307 }
308
309 public void closeConnection(Connection con)
310 {
311 try
312 {
313 con.close();
314 }
315 catch (SQLException e)
316 {
317 log.error("Cant close connection", e);
318 }
319
320 }
321
322 public void doEdit(RenderRequest request, RenderResponse response)
323 throws PortletException, IOException
324 {
325 response.setContentType("text/html");
326 StatusMessage msg = (StatusMessage)PortletMessaging.consume(request, "DatabaseBrowserPortlet", "dbConnectTest");
327 if (msg != null)
328 {
329 this.getContext(request).put("statusMsg", msg);
330 }
331 super.doEdit(request, response);
332 }
333
334
335 public void processAction(ActionRequest request, ActionResponse response)
336 throws PortletException, IOException
337 {
338 if (request.getPortletMode() == PortletMode.EDIT)
339 {
340 String test = request.getParameter("Test");
341 if (test != null && test.equals("Test"))
342 {
343 try
344 {
345 PortletPreferences prefs = request.getPreferences();
346 PreferencesHelper.requestParamsToPreferences(request);
347 prefs.store();
348 getConnection(request);
349 StatusMessage msg = new StatusMessage("Connection made successfully.", StatusMessage.SUCCESS);
350 PortletMessaging.publish(request, "DatabaseBrowserPortlet", "dbConnectTest", msg);
351 }
352 catch (Exception e)
353 {
354 String msg = e.toString();
355 Throwable cause = e.getCause();
356 if (cause != null)
357 {
358 msg = msg + ", " + cause.getMessage();
359 }
360 StatusMessage sm = new StatusMessage(msg, StatusMessage.ERROR);
361 PortletMessaging.publish(request, "DatabaseBrowserPortlet", "dbConnectTest", sm);
362 }
363 response.setPortletMode(PortletMode.EDIT);
364 return;
365 }
366 }
367 super.processAction(request, response);
368 }
369
370 private Subject getSubject()
371 {
372 AccessControlContext context = AccessController.getContext();
373 return JSSubject.getSubject(context);
374 }
375 }