View Javadoc

1   /*
2    * Licensed to the Apache Software Foundation (ASF) under one or more
3    * contributor license agreements.  See the NOTICE file distributed with
4    * this work for additional information regarding copyright ownership.
5    * The ASF licenses this file to You under the Apache License, Version 2.0
6    * (the "License"); you may not use this file except in compliance with
7    * the License.  You may obtain a copy of the License at
8    * 
9    *      http://www.apache.org/licenses/LICENSE-2.0
10   * 
11   * Unless required by applicable law or agreed to in writing, software
12   * distributed under the License is distributed on an "AS IS" BASIS,
13   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14   * See the License for the specific language governing permissions and
15   * limitations under the License.
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              * get the user object types to be displayed and add them to the
112              * title list as well as the result set list
113              */
114             List userObjList = (List) session.getAttribute(USER_OBJECTS);
115             int userObjListSize = 0;
116             if (userObjList != null)
117             {
118                 userObjListSize = userObjList.size();
119             }
120             //System.out.println("User List Size = "+ userObjListSize);
121             /*
122              * the array columnDisplayed maintains a boolean value for each
123              * column index. Only the columns that are set to true are added to
124              * the resultSetList, resultSetTitleList and resultSetTypeList.
125              */
126             boolean[] columnDisplayed = new boolean[columnNum + userObjListSize];
127 
128             /*
129              * this for loop constructs the columnDisplayed array as well as
130              * adds to the resultSetTitleList and resultSetTypeList
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                 //System.out.println("User List Name = "+ usrObj.getName()+"
156                 // Type = "+usrObj.getType());
157             }
158             /*
159              * this while loop adds each row to the resultSetList
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) //closes con also
213                 {
214                     closeConnection(con);
215                 }
216 
217             } catch (Exception e)
218             {
219                 throw e;
220             }
221         }
222 
223     }
224     
225 
226     /*
227      * Connection Management
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     //            ds.setUrl("jdbc:mysql://j2-server/j2");
258                   con = ds.getConnection();                  
259             }
260             else if (dsType.equals("sso"))
261             {
262                 /*
263                  * For SSO the user has to define the JDBCdriver and JdbcConnection (URL)
264                  * but the credentials for the db come from the SSO storage
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                 // SSO API lookup
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); // TODO: complete this 
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 }