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
Comments
Does this tutorial require you to install additional libs on your development machine. Do we need to include oracle.jdbc.OracleDriver? If so, you that should be stated in the overview and a link provided.
Thanks Scott. Added few links at the end of the documentation.