Simple utility to execute database query from within CQ

Simple utility to execute database query and see the results within CQ

1. Add a node (lets call it “databaseQuery”) of type sling:Folder under /etc/importers

2.  Add a property called sling:resourceType to this node with value database/importdata

3. As in the below screenshot, database/importdata correspond to the nodes below. So, basically, create the node database of type nt:folder under /apps. Create a component called importdata underneath the database node.

4. importdata.POST.jsp simply includes this statement:

<%@include file=”importdata.jsp”%>

5. Next, we need to write the code to connect to database etc..All this code goes inside importdata.jsp.

**********************************************************

<%@page session=”false” import=”javax.sql.DataSource,

org.apache.commons.lang.StringEscapeUtils, oracle.jdbc.OracleDriver” %>

<%@ page import=”com.day.commons.datasource.poolservice.DataSourceNotFoundException” %>

<%@ page import=”com.day.commons.datasource.poolservice.DataSourcePool, javax.jcr.query.Query” %>

<%@ page import=”javax.sql.DataSource” %>

<%@ page import=”java.sql.Connection” %>

<%@ page import=”java.sql.SQLException” %>

<%@ page import=”java.sql.Statement” %>

<%@ page import=”java.sql.ResultSet, java.sql.ResultSetMetaData” %>

<%@page import=”com.day.cq.commons.jcr.JcrUtil, org.apache.commons.lang.StringEscapeUtils”%>

 

<%@include file=”/libs/wcm/global.jsp” %>

<head>

<script src=”/libs/cq/ui/resources/cq-ui.js” type=”text/javascript”></script>

</head>

<body>

<h2>Database Import</h2>

<%

Node node = resource.adaptTo(Node.class);

Session s = node.getSession();

 

String action = request.getParameter(“action”);

if (action == null) {

%>

<form method=”POST”>

<br>

<b>Data Source Name*:</b><br> <input type=”text” size=”50″ name=”dataSourceName” value=””><br>

<br>

<br>

<b>Query*:</b><br><textarea name=”cmd” cols=”100″ rows=”25″></textarea><br>

<br>

<br>

<input type=”hidden” name=”action” value=”create”>

<input type=”submit” value=”Execute Query”>

</form>

<%  } else if (“create”.equals(action)) {

out.write(“<br>*******START”);

DataSource ds = null;

String dataSourceName = request.getParameter(“dataSourceName”);

String sStatement = request.getParameter(“cmd”);

DataSourcePool dbService = sling.getService(DataSourcePool.class);

out.write(“<br><br>Getting dbService : ” + dbService);

try {

out.write(“<br><br>Getting DataSource.. “);

ds = (DataSource) dbService.getDataSource(dataSourceName);

} catch(Exception e) {

out.write(“<br><br>Exception while getting DataSource :: “+ dataSourceName + ”  :: <br>******<br> ” +  e.fillInStackTrace() + “<br>******<br>”);

}

if(ds != null) {

%>

<br><br></br>

Found DataSource <%=dataSourceName%>.

<%

if(sStatement != null) {

try {

Class.forName(“oracle.jdbc.OracleDriver”, true,

super.getClass().getClassLoader());

final Connection connection = ds.getConnection();

final Statement statement = connection.createStatement();

final ResultSet resultSet = statement.executeQuery(sStatement);

ResultSetMetaData rsmd = resultSet.getMetaData();

int numColumns = rsmd.getColumnCount();

out.write(“<br><br>connection : ” + connection);

out.write(“<br><br>resultSet : ” + resultSet);

 

// Here goes additional processing of this data for example, store this data to CRX nodes

 

} catch (Exception e) {

out.write(“<br>Exception while getting connection:  :: <br>******<br> ” +  e.fillInStackTrace() + “<br>******<br>”);

}

}

 

} else out.write(“<br><br>DataSource is null ::  ” + ds);

out.write(“<br>*******END”);

}

%>

**********************************************************

(Edited)

Make sure to configure Datasource and add appropriate database Driver as in the below documentation:

http://dev.day.com/content/kb/home/cq5/Development/HowToConfigureSlingDatasource.html

http://blogs.adobe.com/learningwem/2012/02/29/cq5-4-java-sql-sqlexception-no-suitable-driver-found-for/

 

You may download the above jsp code here: importdata