Hi,
I am trying to connect CQ5 with MySql.
I followed the below link to Injecting a DataSourcePool into Adobe Experience Manager Sling Servlets
http://helpx.adobe.com/experience-manager/using/custom-sling-servlets1.html
I have specified the below details in /system/console/configMgr ----> JDBC Connection pool Settings
Username = root ,
Password = root123 ,
JDBC Driver Class= com.mysql.jdbc.Driver,
JDBC connection URI = jdbc:mysql://localhost:3306/test ,
DatasourceName = Customer.
On Server side (SlingServlet Method Post), am trying to connect the MySql Datasource.
private Connection getConnection()
{
DataSource dataSource = null;
Connection con = null;
try
{
dataSource = (DataSource) source.getDataSource("Customer");
con = dataSource.getConnection();
return con;
}
catch (Exception e)
{
e.printStackTrace();
}
return null;
}
On invoking the above mentioned function, am getting exception at runtime
Connection c = getConnection();
Exception
com.day.commons.datasource.poolservice.DataSourceNotFoundException: No data source found with name 'Customer' (after asking 0 providers)
at com.day.commons.datasource.poolservice.impl.DataSourcePoolImpl.getDataSource(DataSourcePoolImpl.java:109)
at com.adobe.cq.sling.ds.HandleClaim.getConnection(HandleClaim.java:134)
at com.adobe.cq.sling.ds.HandleClaim.injestCustData(HandleClaim.java:155)
at com.adobe.cq.sling.ds.HandleClaim.doPost(HandleClaim.java:97)
at org.apache.sling.api.servlets.SlingAllMethodsServlet.mayService(SlingAllMethodsServlet.java:148)
Solved! Go to Solution.
The other thing to check is the bundle fragment -- make sure that it contains both the JSON Lib and the MySQL Driver file:
Add the MySQL driver file and org.json.simple.JSONObject data type to Adobe CQ
You have to deploy a bundle fragment to Adobe CQ that contains the database driver file and the org.json.simple.JSONObject class to Adobe CQ. The reason is because the doPost method in the Sling Servlet uses the JSONObject class to encode form data to JSON formatted data. If you do not add this class to Adobe CQ, then you are unable to place the OSGi bundle that contains the Sling Servlet into an Active state. Likewise, without the MySQL driver file, you cannot persist data into MySQL using the DataSourcePool that you configured.
Just tested this - here is the client that you will see once done:
[img]client.png[/img]
Once submitted to the sling servlet -- its persisted into MySQL -- as shown here:
[img]database.png[/img]
It looks like CQ cannot find your Customer DataSource that you configured. Can you double check and make sure that it's configured properly?
Please attach a Screenshot of it.
Views
Replies
Total Likes
The other thing to check is the bundle fragment -- make sure that it contains both the JSON Lib and the MySQL Driver file:
Add the MySQL driver file and org.json.simple.JSONObject data type to Adobe CQ
You have to deploy a bundle fragment to Adobe CQ that contains the database driver file and the org.json.simple.JSONObject class to Adobe CQ. The reason is because the doPost method in the Sling Servlet uses the JSONObject class to encode form data to JSON formatted data. If you do not add this class to Adobe CQ, then you are unable to place the OSGi bundle that contains the Sling Servlet into an Active state. Likewise, without the MySQL driver file, you cannot persist data into MySQL using the DataSourcePool that you configured.
Just tested this - here is the client that you will see once done:
[img]client.png[/img]
Once submitted to the sling servlet -- its persisted into MySQL -- as shown here:
[img]database.png[/img]
Hi,
I tried implementing as specified, but still getting the error, I have attached the screenshots, errorlog and java code.
I have used mysql-connector-java-5.1.29-bin.jar and json-simple-1.1.1.jar to create a json-mysql bundle.
Please identify am i doing anything wrong. If possible share some sample code and screen shots.
sourcecode of java
package com.adobe.cq.sling.ds; import java.io.BufferedReader; import java.io.IOException; import java.io.InputStream; import java.io.InputStreamReader; import java.io.PrintWriter; import java.net.HttpURLConnection; import java.net.URL; import java.rmi.ServerException; import java.util.Collection; import java.util.Dictionary; import java.util.Iterator; import java.util.List; import org.apache.felix.scr.annotations.Properties; import org.apache.felix.scr.annotations.Property; import org.apache.felix.scr.annotations.Reference; import org.apache.felix.scr.annotations.sling.SlingServlet; import org.apache.sling.api.SlingHttpServletRequest; import org.apache.sling.api.SlingHttpServletResponse; import org.apache.sling.api.servlets.SlingSafeMethodsServlet; import org.apache.sling.commons.osgi.OsgiUtil; import org.apache.sling.jcr.api.SlingRepository; import org.apache.felix.scr.annotations.Reference; import org.osgi.service.component.ComponentContext; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import javax.jcr.Session; import javax.jcr.Node; import org.json.simple.JSONObject; import java.util.UUID; //import MySQL APIs import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; import java.sql.SQLException; import javax.sql.DataSource; //Import CQ DataSOurcePool import com.day.commons.datasource.poolservice.DataSourcePool; @SlingServlet(paths="/bin/mySearch", methods = "POST", metatype=true) public class HandleClaim extends org.apache.sling.api.servlets.SlingAllMethodsServlet { private static final long serialVersionUID = 2598426539166789515L; protected final Logger log = LoggerFactory.getLogger(this.getClass()); @Reference private DataSourcePool source; @Override protected void doPost(SlingHttpServletRequest request, SlingHttpServletResponse response) throws ServerException, IOException { try { //Get the submitted form data that is sent from the //CQ web page String id = UUID.randomUUID().toString(); String firstName = request.getParameter("firstName"); String lastName = request.getParameter("lastName"); String address = request.getParameter("address"); String cat = request.getParameter("cat"); String state = request.getParameter("state"); String details = request.getParameter("details"); String date = request.getParameter("date"); String city = request.getParameter("city"); log.info("HELLO WORLD"); log.info("$##$#$#$#$#$#$#$#$#$#$"); log.info("HELLO WORLD"); //Persist the Data into MySQL by using connection build with the DataSourcePool int x= injestCustData(firstName, lastName, address, details); log.info("THIS IS X"+x); //Encode the submitted form data to JSON JSONObject obj=new JSONObject(); obj.put("id",id); obj.put("firstname",firstName); obj.put("lastname",lastName); obj.put("address",address); obj.put("cat",cat); obj.put("state",state); obj.put("details",details); obj.put("date",date); obj.put("city",city); //Get the JSON formatted data String jsonData = obj.toJSONString(); //Return the JSON formatted data response.getWriter().write(jsonData); } catch(Exception e) { e.printStackTrace(); } } //Returns a connection using the configured DataSourcePool private Connection getConnection() { DataSource dataSource = null; Connection con = null; try { //Inject the DataSourcePool right here! dataSource = (DataSource) source.getDataSource("Customer"); con = dataSource.getConnection(); return con; } catch (Exception e) { e.printStackTrace(); } return null; } //Adds a new customer record in the Customer table public int injestCustData(String firstName, String lastName, String phone, String desc) { Connection c = null; //Connection connection = null; int rowCount= 0; try { log.info("WE ARE IN INJECT CUSTOMER DATA"); // Create a Connection object c = getConnection(); //THE FLOW IS NOT WORKING FROM HERE........ //c = ConnectionHelper.getConnection(); ResultSet rs = null; Statement s = c.createStatement(); Statement scount = c.createStatement(); //Use prepared statements to protected against SQL injection attacks PreparedStatement pstmt = null; PreparedStatement ps = null; //Set the query and use a preparedStatement String query = "Select * FROM Customer"; pstmt = c.prepareStatement(query); rs = pstmt.executeQuery(); log.info("******************************************"); while (rs.next()) rowCount++; //Set the PK value int pkVal = rowCount + 2; String insert = "INSERT INTO Customer(custId,custFirst,custLast,custDesc,custAddress) VALUES(?, ?, ?, ?, ?);"; ps = c.prepareStatement(insert); ps.setInt(1, pkVal); ps.setString(2, firstName); ps.setString(3, lastName); ps.setString(4, phone); ps.setString(5, desc); ps.execute(); log.info("END OF CUST DATA"); return pkVal; } catch (Exception e) { e.printStackTrace(); } finally { try { c.close(); } catch (SQLException e) { e.printStackTrace(); } } return 0; } }
Views
Replies
Total Likes
Screen Shot for Connection
Views
Replies
Total Likes
OSGI BUNDLE Screenshot.
About Mysql and JSON Bundle
Views
Replies
Total Likes
There is an issue somewhere in your environment. I copied the code you pasted and it works fine. I even see your log messages:
07.03.2014 12:12:06.022 *INFO* [0:0:0:0:0:0:0:1 [1394212326021] POST /bin/mySearch HTTP/1.1] com.adobe.cq.sling.ds.HandleClaim $##$#$#$#$#$#$#$#$#$#$
07.03.2014 12:12:06.022 *INFO* [0:0:0:0:0:0:0:1 [1394212326021] POST /bin/mySearch HTTP/1.1] com.adobe.cq.sling.ds.HandleClaim HELLO WORLD
07.03.2014 12:12:06.022 *INFO* [0:0:0:0:0:0:0:1 [1394212326021] POST /bin/mySearch HTTP/1.1] com.adobe.cq.sling.ds.HandleClaim WE ARE IN INJECT CUSTOMER DATA
07.03.2014 12:12:06.042 *INFO* [0:0:0:0:0:0:0:1 [1394212326021] POST /bin/mySearch HTTP/1.1] com.adobe.cq.sling.ds.HandleClaim ******************************************
07.03.2014 12:12:06.043 *INFO* [0:0:0:0:0:0:0:1 [1394212326021] POST /bin/mySearch HTTP/1.1] com.adobe.cq.sling.ds.HandleClaim END OF CUST DATA
07.03.2014 12:12:06.043 *INFO* [0:0:0:0:0:0:0:1 [1394212326021] POST /bin/mySearch HTTP/1.1] com.adobe.cq.sling.ds.HandleClaim THIS IS X31
What version of CQ are you using?
To rule out if there is an issue with DataSourcePool-- can you try doing MySQL interaction without a DataSourcePool. IN this use case -- you code your own connection classes instead of using DataSourcePool. See this article:
http://helpx.adobe.com/experience-manager/using/persisting-cq-data-relational-database.html
IN this article - note use of the ConnectionHelper class.
Try that and let me know if you have better success. Once that works -- i can help you nail down the issue with DataSourcePool. If needed, we can setup a conference call.
Hi,
It is some problem with conversion of MySQL connector to Bundle.
It is not importing all the packages. Now I created the bundle for the MySQL using Indigo.
Now it is working fine. It is connected to MySQL
Thanks for suggestion.
Views
Replies
Total Likes
I am glad that you got it working. Your code was fine!!
Views
Replies
Total Likes
I faced similar issue. If this is of any help
After adding the driver I was still seeing the error. Once I went to the OSGi config and set the JDBC prpoerties again and hit save. it worked.
Views
Replies
Total Likes
Views
Like
Replies
Views
Likes
Replies