Get DataSourcePool for use in servlet

smacdonald2008 wrote...

AT the end of the log -- you have:

ERROR* [OsgiInstallerImpl] org.apache.sling.servlets.resolver.internal.SlingServletResolver bindServlet: Servlet service not available from reference [javax.servlet.Servlet]

Looks like your issue is with the Java Sling Servlet.

For testing purposes -- try building an OSGi bundle with a custom interface and Java class and injecting a DataSourcePool into it - as shown in the article. I have done this many times in CQ and it works nicely. 

Try separating use of the DataSourcePool from a Sling Servlet. Lets make sure that you can successfully get the DataSourcePool working from a POJO (Plain Old Java Object) wrapped in an OSGi. Once you do that - we will know that your DataSourcePool Service is working. 

After that -- we will know for sure its a servelt problem -- not a DataSourcePool issue. 

 

 

I created OSGi bundle using the article you posted but it doesn't work either... Here is how my testing component, java interface and java class are made

Interface

package info.pckg.test; public interface TestIface{ public String[] selectStringValues(String query, String[] params); }

Class

package info.pckg.test; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import javax.sql.DataSource; import org.apache.felix.scr.annotations.Component; import org.apache.felix.scr.annotations.Reference; import org.apache.felix.scr.annotations.Service; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.day.commons.datasource.poolservice.DataSourcePool; @Component @Service public class TestClass implements TestIface{ private static final Logger log = LoggerFactory.getLogger(TestClass.class); @Reference private DataSourcePool source; private Connection getConnection(){ log.info("getConnection here"); DataSource ds = null; Connection con = null; if(source == null){ log.error("source == null!"); } try{ ds = (DataSource) source.getDataSource("intranetConnection"); con = ds.getConnection(); return con; }catch(Exception e){ log.error("Exception "+e.getClass().getName()+" while getting connection. Msg: "+e.getMessage()); } return null; } @Override public String[] selectStringValues(String query, String[] params) { log.info("selectStringValues here!"); Connection con = null; PreparedStatement preparedStatement = null; String ret[] = null; try{ con = this.getConnection(); preparedStatement = con.prepareStatement(query); for(int i = 1; i < params.length; i++){ preparedStatement.setString(i, params[i-1]); } ResultSet rs = preparedStatement.executeQuery(); ArrayList<String> tmp = new ArrayList<String>(); while(rs.next()){ tmp.add(rs.getString(1)); } try{ ret = tmp.toArray(new String[tmp.size()]); }catch(Exception e){ log.error("Exception while converting ArrayList to array. Exc: "+e.getClass().getName()+": "+e.getMessage()); } }catch(Exception e){ log.error("Exception "+e.getClass().getName()+": "+e.getMessage()); }finally{ if(con != null){ try{ con.close(); }catch(Exception e){ log.warn("Exception "+e.getClass().getName()+"(not fatal): "+e.getMessage()); } } if(preparedStatement != null){ try{ preparedStatement.close(); }catch(Exception e){ log.warn("Exception "+e.getClass().getName()+"(not fatal): "+e.getMessage()); } } } return ret; } }

Component

<%@include file="/libs/foundation/global.jsp"%> <%@page import="com.day.text.Text, com.day.cq.wcm.api.WCMMode" %> <%@page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %><% String home = Text.getAbsoluteParent(currentPage.getPath(), 2); if(WCMMode.fromRequest(request) == WCMMode.EDIT){ out.println("Edit mode"); } info.hartmann.test.TestIface test = sling.getService(info.hartmann.test.TestIface.class); String[] retVals = test.selectStringValues("SELECT [column] FROM [DB].[dbo].[table] WHERE [anotherColumn] = ?", new String[]{"param"}); %> <table> <% for(int i = 0; i < retVals.length; i++){ out.println("<tr><td>"+retVals[i]+"</td></tr>\n"); } %> </table>

And these are the errors I get when I try to run the component - http://pastebin.com/8v21jGWE

I will combine the use of a Sling Servlet and a DataSourcePool instance.

I will create a new community article and post it. Once done -- I will post the URL to the article here. 

Is your servlet path listed in the Sling Servlet Resolver (org.apache.sling.servlets.resolver.SlingServletResolver) service?

scott

Hi,

I've already tried to create my servlet like this, but whenever I try to use @Reference, I get HTTP 403 Forbidden error. This is how my servlet looks like

package my.pckg.servlets; import java.io.IOException; import java.sql.Connection; import javax.servlet.ServletException; import javax.sql.DataSource; import org.apache.felix.scr.annotations.Component; import org.apache.felix.scr.annotations.Properties; import org.apache.felix.scr.annotations.Property; import org.apache.felix.scr.annotations.Reference; import org.apache.felix.scr.annotations.Service; import org.apache.sling.api.SlingHttpServletRequest; import org.apache.sling.api.SlingHttpServletResponse; import org.apache.sling.api.servlets.SlingAllMethodsServlet; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.day.commons.datasource.poolservice.DataSourcePool; @Component(immediate = true) @Service(value=javax.servlet.Servlet.class) @Properties(value={ @Property(name="sling.servlet.methods", value={"GET"}), @Property(name="sling.servlet.paths", value={"/myservices/saveandinvite"}) }) public class SaveAndInvitePeople extends SlingAllMethodsServlet{ private static final long serialVersionUID = 7923689671005539630L; private static final Logger log = LoggerFactory.getLogger(SaveAndInvitePeople.class); @Reference private DataSourcePool source; //private DataSourcePool dataSourcePool = null; @Override protected void doGet(SlingHttpServletRequest request, SlingHttpServletResponse response) throws ServletException, IOException{ log.info("doGet"); /*this.dataSourcePool = Activator.getDataSourcePool(); if(this.dataSourcePool == null){ log.info("datasourcepool == null!"); }*/ if(this.source == null){ log.info("datasourcepool == null!"); } try{ //DataSource ds = (DataSource)this.dataSourcePool.getDataSource("myConnection"); DataSource ds = (DataSource)this.source.getDataSource("myConnection"); if(ds == null){ log.error("DataSource is null!"); return; } log.info("datasource not null! gonna try to get connection!"); Connection con = null; try{ con = ds.getConnection(); }catch(Exception e){ log.error("Exception "+e.getClass().getName()+": "+e.getMessage()); }finally{ if(con != null){ try{ con.close(); }catch(Exception e){ log.error("Exception "+e.getClass().getName()+": "+e.getMessage()); } } } log.info("looks OK!"); }catch(Exception e){ log.error("Exception "+e.getClass().getName()+": "+e.getMessage()); } } @Override protected void doPost(SlingHttpServletRequest request, SlingHttpServletResponse response) throws ServletException, IOException{ } }

and when I try to access localhost:4502/myservices/saveandinvite, I get this error

Forbidden Cannot serve request to /myservice/saveandinvite/ in org.apache.sling.servlets.get.DefaultGetServlet Request Progress: 0 (2014-01-29 11:51:40) TIMER_START{Request Processing} 0 (2014-01-29 11:51:40) COMMENT timer_end format is {<elapsed msec>,<timer name>} <optional message> 0 (2014-01-29 11:51:40) LOG Method=GET, PathInfo=/myservice/saveandinvite/ 0 (2014-01-29 11:51:40) TIMER_START{ResourceResolution} 0 (2014-01-29 11:51:40) TIMER_END{0,ResourceResolution} URI=/myservice/saveandinvite/ resolves to Resource=, type=sling:syntheticResourceProviderResource, path=/myservice/saveandinvite, resource=[SyntheticResource, type=sling:syntheticResourceProviderResource, path=/myservice/saveandinvite] 0 (2014-01-29 11:51:40) LOG Resource Path Info: SlingRequestPathInfo: path='/myservice/saveandinvite', selectorString='null', extension='null', suffix='/' 0 (2014-01-29 11:51:40) TIMER_START{ServletResolution} 0 (2014-01-29 11:51:40) TIMER_START{resolveServlet(, type=sling:syntheticResourceProviderResource, path=/myservice/saveandinvite, resource=[SyntheticResource, type=sling:syntheticResourceProviderResource, path=/myservice/saveandinvite])} 2 (2014-01-29 11:51:40) TIMER_END{2,resolveServlet(, type=sling:syntheticResourceProviderResource, path=/myservice/saveandinvite, resource=[SyntheticResource, type=sling:syntheticResourceProviderResource, path=/myservice/saveandinvite])} Using servlet org.apache.sling.servlets.get.DefaultGetServlet 2 (2014-01-29 11:51:40) TIMER_END{2,ServletResolution} URI=/myservice/saveandinvite/ handled by Servlet=org.apache.sling.servlets.get.DefaultGetServlet 2 (2014-01-29 11:51:40) LOG Applying Requestfilters 2 (2014-01-29 11:51:40) LOG Calling filter: org.apache.sling.bgservlets.impl.BackgroundServletStarterFilter 2 (2014-01-29 11:51:40) LOG Calling filter: org.apache.sling.i18n.impl.I18NFilter 2 (2014-01-29 11:51:40) LOG Calling filter: org.apache.sling.rewriter.impl.RewriterFilter 2 (2014-01-29 11:51:40) LOG Calling filter: com.day.cq.wcm.designimporter.CanvasPageDeleteRequestFilter 2 (2014-01-29 11:51:40) LOG Calling filter: com.day.cq.wcm.core.impl.WCMRequestFilter 2 (2014-01-29 11:51:40) LOG Calling filter: cz.devsoft.hartmann.project20130901v01.impl.filters.LoggingFilter 2 (2014-01-29 11:51:40) LOG Calling filter: com.adobe.granite.optout.impl.OptOutFilter 2 (2014-01-29 11:51:40) LOG Calling filter: com.day.cq.theme.impl.ThemeResolverFilter 2 (2014-01-29 11:51:40) LOG Calling filter: com.day.cq.wcm.foundation.forms.impl.FormsHandlingServlet 2 (2014-01-29 11:51:40) LOG Calling filter: org.apache.sling.engine.impl.debug.RequestProgressTrackerLogFilter 2 (2014-01-29 11:51:40) LOG Calling filter: com.day.cq.wcm.mobile.core.impl.redirect.RedirectFilter 2 (2014-01-29 11:51:40) LOG RedirectFilter did not redirect (request extension does not match) 2 (2014-01-29 11:51:40) LOG Calling filter: com.day.cq.wcm.core.impl.warp.TimeWarpFilter 2 (2014-01-29 11:51:40) LOG Applying Componentfilters 2 (2014-01-29 11:51:40) LOG Calling filter: com.day.cq.wcm.core.impl.WCMComponentFilter 2 (2014-01-29 11:51:40) LOG Calling filter: com.day.cq.wcm.core.impl.WCMDebugFilter 2 (2014-01-29 11:51:40) TIMER_START{org.apache.sling.servlets.get.DefaultGetServlet#0} 2 (2014-01-29 11:51:40) LOG Using org.apache.sling.servlets.get.impl.helpers.StreamRendererServlet to render for extension=null 2 (2014-01-29 11:51:40) LOG Applying Error filters 2 (2014-01-29 11:51:40) LOG Calling filter: org.apache.sling.rewriter.impl.RewriterFilter 2 (2014-01-29 11:51:40) TIMER_START{handleError:status=403} 6 (2014-01-29 11:51:40) TIMER_END{4,handleError:status=403} Using handler /libs/sling/servlet/errorhandler/default.jsp 22 (2014-01-29 11:51:40) LOG Found processor for post processing ProcessorConfiguration: {contentTypes=[text/html],order=-1, active=true, valid=true, processErrorResponse=true, pipeline=(generator=Config(type=htmlparser, config={}), transformers=(Config(type=linkchecker, config={}), Config(type=mobile, config=org.apache.sling.jcr.resource.JcrPropertyMap@49f984de), Config(type=mobiledebug, config=org.apache.sling.jcr.resource.JcrPropertyMap@1c8230c3), Config(type=contentsync, config=org.apache.sling.jcr.resource.JcrPropertyMap@274f60d4), serializer=Config(type=htmlwriter, config={}))} 23 (2014-01-29 11:51:40) TIMER_END{23,Request Processing} Dumping SlingRequestProgressTracker Entries ApacheSling/2.2 (Day-Servlet-Engine/4.1.42, OpenJDK 64-Bit Server VM 1.7.0_51, Linux 2.6.32-431.3.1.el6.x86_64 amd64)

AT the end of the log -- you have:

ERROR* [OsgiInstallerImpl] org.apache.sling.servlets.resolver.internal.SlingServletResolver bindServlet: Servlet service not available from reference [javax.servlet.Servlet]

Looks like your issue is with the Java Sling Servlet.

For testing purposes -- try building an OSGi bundle with a custom interface and Java class and injecting a DataSourcePool into it - as shown in the article. I have done this many times in CQ and it works nicely. 

Try separating use of the DataSourcePool from a Sling Servlet. Lets make sure that you can successfully get the DataSourcePool working from a POJO (Plain Old Java Object) wrapped in an OSGi. Once you do that - we will know that your DataSourcePool Service is working. 

After that -- we will know for sure its a servelt problem -- not a DataSourcePool issue. 

I've found these errors in error.log - http://pastebin.com/dhy4NfL4 (sorry, it's too long to paste it here...).

The following community article works and been tested in CQ 5.5 and 5.6. It hooks into MySQL:

http://helpx.adobe.com/experience-manager/using/datasourcepool.html

In this development article - we build the OSGi bundle using Maven.