« Retour - 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.

Moyenne (2 Voter)
L'estimation moyenne est de 2.5 étoiles sur 5.
Commentaires
Réponses Auteur Données
Hello.. a few questions... Can the datasource... Kelly C 7 octobre 2009 15:05
We got this to work! This was a very very... Kelly C 9 octobre 2009 10:37
can you share some insights about how did you... Matteo Moci 28 janvier 2010 06:32
Hey.. sorry I didn't see this reply until just... Kelly C 1 avril 2010 16:20
Sorry, I meant, I added the lines from part 2 Kelly C 1 avril 2010 16:20
Hi, Is it possible to use this datacontext for... kss rao 4 mars 2011 03:32
This doesn't seem to work for Liferay 6.0 ... Terrence Szeto 14 avril 2011 10:00
the context.xml in my tomcat is getting reset... Fox Mulder 11 novembre 2011 06:06
esse negócio de adicionar novo resource no... ricardo wolosker 20 janvier 2012 05:20
This wiki... Manuel de la Peña 8 février 2012 00:07
I try and throw Exception.... Tú Trịnh 5 février 2013 00:54

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!!
Publié le 07/10/09 15:05.
We got this to work! This was a very very helpful post.. thank you!
Publié le 09/10/09 10:37 en réponse à 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
Publié le 28/01/10 06:32 en réponse à 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..
Publié le 01/04/10 16:20 en réponse à Matteo Moci.
Sorry, I meant, I added the lines from part 2
Publié le 01/04/10 16:20 en réponse à 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
Publié le 04/03/11 03:32.
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
Publié le 14/04/11 10:00.
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!
Publié le 11/11/11 06:06.
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.
Publié le 20/01/12 05:20 en réponse à 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.
Publié le 08/02/12 00:07 en réponse à 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!
Publié le 05/02/13 00:54.