« Back to Portlets

Add Datasource to Portlet

Introduction

This article describes how to add an additional data source to your portlets.

Environment

This article was written with the following environment:

  • Container: Tomcat 6.0.18 + Liferay 5.2.3
  • IDE: NetBeans IDE 6.7 + Portal Pack 3.0.7
  • Database: MySQL 5.1

Procedure

Declare the Datasource

First, edit the file $CATALINA_HOME/context.xml and add the following lines:

<Resource name="jdbc/mydb"
     auth="Container"
     type="javax.sql.DataSource"
     maxActive="100"
     maxIdle="30"
     maxWait="10000"
     username="username"
     password=""
     driverClassName="com.mysql.jdbc.Driver"
     url="jdbc:mysql://localhost:3306/mydb?autoReconnect=true"/>

Add the lines before closing the tag context: </Context>

Add Datasource to Portlet

Second, create a new portlet, and now we can add the following lines in the view jsp of our portlet:

     <%@page contentType="text/html"%>
     <%@page pageEncoding="UTF-8"%>
     <%@ page import="java.sql.*, javax.portlet.*, javax.naming.*, javax.sql.*" %>
     <%@ taglib uri="http://java.sun.com/portlet_2_0" prefix="portlet"%>
     <portlet:defineObjects />
     <%PortletPreferences prefs = renderRequest.getPreferences();%> 
     
     VIEW MODE
     <%
     String DATASOURCE_CONTEXT = "java:comp/env/jdbc/mydb";
     Connection result = null;
     try {
     Context initialContext = new InitialContext();
     if ( initialContext == null){
     out.println("JNDI problem. Cannot get InitialContext.");
     }
     DataSource datasource = (DataSource)initialContext.lookup(DATASOURCE_CONTEXT);
     if (datasource != null) {
     result = datasource.getConnection();
     out.println("Lookup datasource.");
     Statement stmt = result.createStatement();
     ResultSet rs = stmt.executeQuery("select * from mytable");
     while(rs.next()){
     out.println (rs.getString(1));
     }
     stmt.close();
     result.close();
     }
     else {
     out.println("Failed to lookup datasource.");
     }
     }
     catch ( NamingException ex ) {
     out.println("Cannot get connection: " + ex);
     }
     catch(SQLException ex){
     out.println("Cannot get connection: " + ex);
     }
     %>

We are using the data source declared "jdbc/mydb," and if it is available we start using it. If not available, the data source is not used.

0 Attachments
29510 Views
Average (1 Vote)
The average rating is 4.0 stars out of 5.
Comments
Threaded Replies Author Date
Hello.. a few questions... Can the datasource... Kelly C October 7, 2009 3:05 PM
We got this to work! This was a very very... Kelly C October 9, 2009 10:37 AM
can you share some insights about how did you... Matteo Moci January 28, 2010 6:32 AM
Hey.. sorry I didn't see this reply until just... Kelly C April 1, 2010 4:20 PM
Sorry, I meant, I added the lines from part 2 Kelly C April 1, 2010 4:20 PM
Hi, Is it possible to use this datacontext for... kss rao March 4, 2011 3:32 AM
This doesn't seem to work for Liferay 6.0 ... Terrence Szeto April 14, 2011 10:00 AM
the context.xml in my tomcat is getting reset... Fox Mulder November 11, 2011 6:06 AM
esse negócio de adicionar novo resource no... ricardo wolosker January 20, 2012 5:20 AM
This wiki... Manuel de la Peña February 8, 2012 12:07 AM
I try and throw Exception.... Tú Trịnh February 5, 2013 12:54 AM

Hello.. a few questions...
Can the datasource be declared in portal-ext.properties instead of context.xml?

If it can, what lines would need to be different inside view.jsp?

Thanks!!
Posted on 10/7/09 3:05 PM.
We got this to work! This was a very very helpful post.. thank you!
Posted on 10/9/09 10:37 AM in reply to Kelly C.
can you share some insights about how did you make it work?
I'd like to have a spring bean dataSource based on configuration taken from portal-ext.properties
Posted on 1/28/10 6:32 AM in reply to Kelly C.
Hey.. sorry I didn't see this reply until just now.

The only thing I really did was edit the context.xml file.
Mine was in tomcat-6.0.18/conf/
I didn't do anything with portal-ext.properties

The I added the lines from part 1(with my own queries and etc) to my custom java classes, for the portlets..
Posted on 4/1/10 4:20 PM in reply to Matteo Moci.
Sorry, I meant, I added the lines from part 2
Posted on 4/1/10 4:20 PM in reply to Kelly C.
Hi,
Is it possible to use this datacontext for connection.datasource property in hibernate.xml.cfg file. which is inside <Portlet>/WEB-INF/lib/somejar.jar file of that defined portlet

Thanks
Posted on 3/4/11 3:32 AM.
This doesn't seem to work for Liferay 6.0

Review http://tomcat.apache.org/tomcat-6.0-doc/jndi-resources-howto.html#JDBC Data Sources

All Data Source information would go within the Webapp, no messing with the root Context.xml

In the META-INF of your WebApp, add a Context.xml there and put the <Resource/> (as in part 2 of the procedure above).

In your WEB-INF/web.xml, add a <resource-ref> definition there (see Tomcat documentation)

This seems to work for me.

Oh and my lookup was java:comp/env/jdbc/LiferayDataSource
Posted on 4/14/11 10:00 AM.
the context.xml in my tomcat is getting reset every now and so often. had to go back and configure it in portal-ext.properties

but i need to gain access to it via JNDI!
Posted on 11/11/11 6:06 AM.
esse negócio de adicionar novo resource no arquivo context.xml do TOMCAT também não está funcionando. Eu não sei o que acontece mas as vezes o arquivo context.xml é recriado??? quando isso acontece, o datasource é apagado.
Posted on 1/20/12 5:20 AM in reply to Daniel Mueller.
This wiki (http://www.liferay.com/es/community/wiki/-/wiki/Main/Connecting+to+Separate+Dat­abase+Using+Build-service+in+Plugins+SDK) uses a better pattern to create portlets with another datasource.
Posted on 2/8/12 12:07 AM in reply to ricardo wolosker.
I try and throw Exception.
javax.naming.NoInitialContextException: Need to specify class name in environment or system property, or as an applet parameter, or in an application resource file: java.naming.factory.initial.
Please help!
Posted on 2/5/13 12:54 AM.