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.