Article summary

Summary
Discusses how to create an Adobe Experience Manager (AEM) 6.4 HTL component that queries data from MySQL. This article covers the following Experience Manager developer concepts: 
  • how to use a DataSourePool to connect to a relational database
  • how to use the UBER 6.4 JAR
  • how to use the JDBC Java API within an Experience Manager service 
  • how to display application data in a tabular format
  • how to use AJAX and handle the result set by using an HTL component
Digital Marketing Solution(s) Adobe Experience Manager
Audience
Developer (intermediate)
Required Skills
Java, JQuery, SQL
AEM Versions(s) Adobe Experience Manager 6.4
Video https://youtu.be/LWjCdzqDjTQ

Introduction

You can create an Adobe Experience Manager 6.4 application that queries data located in a relational database and displays the data within a web page. In this article, you use a com.day.commons.datasource.poolservice.DataSourcePool instance to set up a connection to a MySQL database.  Then you can use Java JDBC API to perform database operations, such as executing a query. For information, see Java JDBC API.

The following illustration shows the Experience Manager component developed in this article that displays data queried from MySQL. 

Client
An Experience Manager component displaying data queries from MySQL

The following illustration shows the employee table that belongs to a MySQL database. Notice that the previous illustration shows this data within an Experience Manager component.  

table
A MySQL Table named employee

Note:

Before following along with this development article, setup MySQL and create a database schema named test that contains the employee table with the fields shown in the previous illustration. Note that idemployee field is the primary key and auto incremented. See www.mysql.com.

Configure the DataSourcePool connection properties

Add a configuration for the JDBC Connections Pool service that uses the JDBC driver to create data source objects. The OSGi bundle created in this development article uses this service to connect to the MySQL database. The following illustration shows the configuration used in this article. 

config
DataSourcePool Configuration

To configure a DataSourcePool, perform these tasks:

1. Login to Apache Felix Web Console at http://server:port/system/console/bundles (default admin user = admin with password= admin).

2. Click the Configuration tab.

3. Click the + icon that appears in the Day Commons JDBC Connections Pool row.

4. Enter the following values:

  • JBDC Driver class - the driver class to use to connect to the database. To connect to MySQL, enter com.mysql.jdbc.Driver.
  • JDBC connection URI - the URI to the database. In this example, enter jdbc:mysql://localhost:3306/test.
  • Username - the username to use to connect to MySQL. 
  • Password - the corresponding password.
  • Datasource name - the data source name. This is the value that you reference in the Java logic located in the OSGi bundle. In this example, enter Employee

5. Click Save.

Create an Experience Manager Maven 13 archetype project

You can create an Experience Manager archetype project by using the Maven archetype plugin. In this example, assume that the working directory is C:\AdobeCQ. 

Maven
Default files created by the Maven archetype plugin

Note:

To create an Adobe Maven Archetype, you need to setup Maven. For information, see this article Creating an Adobe Experience Manager 6.4 Project using Adobe Maven Archetype 13.

To create an Experience Manager archetype project, perform these steps:

1. Open the command prompt and go to your working directory (for example, C:\AdobeCQ).

2. Run the following Maven command:

mvn org.apache.maven.plugins:maven-archetype-plugin:2.4:generate -DarchetypeGroupId=com.adobe.granite.archetypes -DarchetypeArtifactId=aem-project-archetype -DarchetypeVersion=13 -DarchetypeCatalog=https://repo.adobe.com/nexus/content/groups/public/

3. When prompted, specify the following information:

  • groupId - QuerySQL64
  • artifactId - QuerySQL64
  • version - 1.0-SNAPSHOT
  • package - com.aem.cust
  • appsFolderName - QuerySQL64
  • artifactName - QuerySQL64
  • componentGroupName - QuerySQL64
  • contentFolderName - QuerySQL64
  • cssId - QuerySQL64
  • packageGroup - QuerySQL64
  • siteName - QuerySQL64

4. WHen prompted, specify Y.

5. Once done, you will see a message like:

[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 01:42 min
[INFO] Finished at: 2016-04-25T14:34:19-04:00
[INFO] Final Memory: 16M/463M
[INFO] ------------------------------------------------------------------------

6. Change the working directory to QuerySQL64 and then enter the following command.

mvn eclipse:eclipse

After you run this command, you can import the project into Eclipse as discussed in the next section.

Add Java files to the Maven project using Eclipse

To make it easier to work with the Maven generated project, import it into the Eclipse development environment, as shown in the following illustration.

project
The Eclipse Import Project dialog

Add the following Java files to the com.aem.cust.core package:

  • A Java class named Employee that stores employee information.
  • A Java interface named EmployeeInter.
  • A Java class named EmployeeImpl that implements the EmployeeInter interface.

The next step is to add Java files to the com.aem.cust.core.servlets package. The Java files that you create in this section use the Java JDBC API. 

Employee class

The following code represents the Employee class that is used to store employee information. 

package com.aem.cust.core;

//Stores employee information
public class Employee {
     
    //Define private class members
    private String name ;
    private String address ;
    private String position; 
    private String age;
    private String date;
    private String salary;
     
     
    public void setName(String name)
    {
        this.name = name; 
    }
     
    public String getName()
    {
        return this.name;
    }
    
    public void setAddress(String address)
    {
        this.address = address; 
    }
    
    public String getAddress()
    {
        return this.address ; 
    }
     
    public void setPosition(String position)
    {
        this.position = position; 
    }
     
    public String getPosition()
    {
        return this.position;
    }
     
     
    public void setAge(String age)
    {
        this.age = age; 
    }
     
    public String getAge()
    {
        return this.age;
    }
     
    public void setDate(String date)
    {
        this.date = date; 
    }
     
    public String getDate()
    {
        return this.date;
    }
    
    public void setSalary(String salary)
    {
        this.salary = salary; 
    }
     
    public String getSalary()
    {
        return this.salary;
    }
 
}

EmployeeInter interface

The following code represents the EmployeeInter interface. This interface contains a method signature named  getEmployeeDataSQL. The implementation logic for this method is located in the EmployeeImpl class. 

The following Java code represents the EmployeeInter interface. 

package com.aem.cust.core;

public interface EmployeeInter {

	public String getEmployeeDataSQL(); 

}

EmployeeImpl class

The EmployeeImpl class uses the following declarative services annoations

  • @Component – defines the class as a component
  • @Reference – injects a DataSourcePool into the service  

For information about these annotations, see Official OSGi Declarative Services Annotations in AEM.

In this development article, a DataSourcePool instance is injected into the getConnection method. This method uses a DataSourcePool to return a Connection instance to the database. To inject a DataSourcePool instance, you use the @Reference annotation to define a class member.

The following Java code represents the EmployeeImpl class. 

package com.aem.cust.core;

//DS Annotations
import org.osgi.service.component.annotations.Component;
import org.osgi.service.component.annotations.Reference; 


import org.w3c.dom.Document;
import org.w3c.dom.Element;
    
    
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
    
import java.io.StringWriter;
import java.util.Iterator;
import java.util.List;
import java.util.ArrayList;
    

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import java.util.HashMap; 
import java.util.Map; 

import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;


//SQL import statements 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import com.day.commons.datasource.poolservice.DataSourcePool;
import javax.sql.DataSource;


@Component
public class EmployeeImpl implements EmployeeInter {
	
	/** Default log. */
	protected final Logger log = LoggerFactory.getLogger(this.getClass());
	
	@Reference
    private DataSourcePool source;
	            

	//Get Customer Data from MySQL
	public String getEmployeeDataSQL() 
	{
		
		 DataSource dataSource = null;
         Connection c = null;
         String query = "";
         Employee employee = null;
         List<Employee> employList = new ArrayList<Employee>();
         try{
 			
        	 //Query data from the Employee table located in MySQL
        	 c =  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; 
                                                    
             //Specify the SQL Statement to query data from, the empployee table
              query = "Select name,address,job,age,start,salary FROM employee";
             
              pstmt = c.prepareStatement(query); 
              rs = pstmt.executeQuery();
        	 
              while (rs.next()) 
              {
            	  //For each employee record-- create an Employee instance
                  employee = new Employee();
                   
                  //Populate Employee object with data from MySQL
                  employee.setName(rs.getString(1));
                  employee.setAddress(rs.getString(2));
                  employee.setPosition(rs.getString(3));
                  employee.setAge(rs.getString(4));
                  employee.setDate(rs.getString(5));
                  employee.setSalary(rs.getString(6));
                   
                 //Push the Employee Object to the list
                 employList.add(employee);
       
                    
              }            
        	 
           return convertToString(toXml(employList));  
 		}
 		catch (Exception e)
 		{
 			e.printStackTrace(); 
 		}
		
		return null; 
	}
	
	
	//Returns a connection using the configured DataSourcePool 
	  private Connection getConnection()
	  {
	           DataSource dataSource = null;
	           Connection con = null;
	           try
	           {
	               //Inject the DataSourcePool right here! 
	        	   log.info("GET CONNECTION");
	               dataSource = (DataSource) source.getDataSource("Employee");
	               con = dataSource.getConnection();
	               
	               log.info("CONNECTION is returned");
	               return con;
	                
	             }
	           catch (Exception e)
	           {
	               e.printStackTrace(); 
	           }
	               return null; 
	  }
	        
		//Convert Employee data retrieved from MySQL
		//into an XML schema to pass back to client
		private Document toXml(List<Employee> employeeList) {
		try
		{
		    DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
		    DocumentBuilder builder = factory.newDocumentBuilder();
		    Document doc = builder.newDocument();
		                    
		    //Start building the XML to pass back to the AEM client
		    Element root = doc.createElement( "Employees" );
		    doc.appendChild( root );
		                 
		    //Get the elements from the collection
		    int custCount = employeeList.size();
		       
		    //Iterate through the collection to build up the DOM           
		     for ( int index=0; index < custCount; index++) {
		    
		         //Get the Employee object from the collection
		         Employee myEmployee = (Employee)employeeList.get(index);
		                         
		         Element Employee = doc.createElement( "Employee" );
		         root.appendChild( Employee );
		                          
		         //Add rest of data as child elements to Employee
		         //Set Name
		         Element name = doc.createElement( "Name" );
		         name.appendChild( doc.createTextNode(myEmployee.getName() ) );
		         Employee.appendChild( name );
		                                                             
		         //Set Address
		         Element address = doc.createElement( "Address" );
		         address.appendChild( doc.createTextNode(myEmployee.getAddress() ) );
		         Employee.appendChild( address );
		                       
		         //Set position
		         Element position = doc.createElement( "Position" );
		         position.appendChild( doc.createTextNode(myEmployee.getPosition() ) );
		         Employee.appendChild( position );
		                      
		         //Set age
		         Element age = doc.createElement( "Age" );
		         age.appendChild( doc.createTextNode(myEmployee.getAge()) );
		         Employee.appendChild( age );
		         
		         //Set Date
		         Element date = doc.createElement( "Date" );
		         date.appendChild( doc.createTextNode(myEmployee.getDate()) );
		         Employee.appendChild( date );
		         
		         //Set sal
		         Element salary = doc.createElement( "Salary" );
		         salary.appendChild( doc.createTextNode(myEmployee.getSalary()) );
		         Employee.appendChild( salary );
		      }
		               
		return doc;
		}
		catch(Exception e)
		{
		    e.printStackTrace();
		    }
		return null;
		}
		    
		    
		private String convertToString(Document xml)
		{
		try {
		   Transformer transformer = TransformerFactory.newInstance().newTransformer();
		  StreamResult result = new StreamResult(new StringWriter());
		  DOMSource source = new DOMSource(xml);
		  transformer.transform(source, result);
		  return result.getWriter().toString();
		} catch(Exception ex) {
		      ex.printStackTrace();
		}
		  return null;
		 }
		   
		 }

SimpleServlet

Modify the SimpleServlet class in the com.aem.cust.core.servlets package. In this example, a @Reference annotation is used to get an EmployeeInter instance. Then the getEmployeeDataSQL method is invoked and the servlet returns the XML string that contains the employee data.  

The following Java code represents the SimpleServlet class. 

package com.aem.cust.core.servlets;

import org.apache.sling.api.SlingHttpServletRequest;
import org.apache.sling.api.SlingHttpServletResponse;
import org.apache.sling.api.resource.Resource;
import org.apache.sling.api.servlets.HttpConstants;
import org.apache.sling.api.servlets.SlingAllMethodsServlet;
import org.apache.sling.api.servlets.SlingSafeMethodsServlet;
import org.apache.sling.api.resource.ValueMap;
import org.osgi.framework.Constants;
import org.osgi.service.component.annotations.Component;
import org.osgi.service.component.annotations.Reference;
import javax.servlet.Servlet;
import javax.servlet.ServletException;
import java.io.IOException;
import com.aem.cust.core.EmployeeInter ; 


import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


@Component(service=Servlet.class,
        property={
                Constants.SERVICE_DESCRIPTION + "=Simple Demo Servlet",
                "sling.servlet.methods=" + HttpConstants.METHOD_GET,
                "sling.servlet.paths="+ "/bin/myCustData"
           })
public class SimpleServlet extends SlingAllMethodsServlet {
 
    private static final long serialVersionUid = 1L;
     
    private final Logger logger = LoggerFactory.getLogger(getClass());
     
    @Reference
    private EmployeeInter emplData;
 
    @Override
    protected void doGet(final SlingHttpServletRequest req,
            final SlingHttpServletResponse resp) throws ServletException, IOException {
        
        try
        {
        logger.info("About to call");  
            
        String data=  emplData.getEmployeeDataSQL(); 
        logger.info("DATA IS "+data);   
        resp.getWriter().write(data);
     
        }
        catch (Exception e)
        {
            e.printStackTrace(); 
        }
                 
        }
  
}

Add the MySQL driver file to Experience Manager

You have to deploy a bundle to Experience Manager that contains the database driver file. In this development article, the name of the database driver file is mysql-connector-java-5.1.22-bin.

To learn how to place a JDBC Driver file into an OSGi bundle, see "Add the MySQL driver file to Experience Manager" in https://helpx.adobe.com/experience-manager/using/datasourcepool.html.

Note:

If you do not add a database driver file, then the Experience Manager service cannot interact with the relational database. 

Modify the Maven POM file

Add the following POM dependency to the POM file located at C:\AdobeCQ\QuerySQL64.

<dependency>
    <groupId>com.adobe.aem</groupId>
    <artifactId>uber-jar</artifactId>
    <version>6.4.0</version>
    <classifier>apis</classifier>
    <scope>provided</scope>
</dependency>
               
  <dependency>
       <groupId>org.apache.geronimo.specs</groupId>
       <artifactId>geronimo-atinject_1.0_spec</artifactId>
       <version>1.0</version>
       <scope>provided</scope>
   </dependency>

When you add new Java classes under core, you need to modify a POM file to successfully build the OSGi bundle. You modify the POM file located at C:\AdobeCQ\QuerySQL64\core. The following code represents this POM file.

<?xml version="1.0" encoding="UTF-8"?>
<!--
 |  Copyright 2017 Adobe Systems Incorporated
 |
 |  Licensed under the Apache License, Version 2.0 (the "License");
 |  you may not use this file except in compliance with the License.
 |  You may obtain a copy of the License at
 |
 |      http://www.apache.org/licenses/LICENSE-2.0
 |
 |  Unless required by applicable law or agreed to in writing, software
 |  distributed under the License is distributed on an "AS IS" BASIS,
 |  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 |  See the License for the specific language governing permissions and
 |  limitations under the License.
-->
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>QuerySQL64</groupId>
        <artifactId>QuerySQL64</artifactId>
        <version>1.0-SNAPSHOT</version>
        <relativePath>../pom.xml</relativePath>
    </parent>
    <artifactId>QuerySQL64.core</artifactId>
    <packaging>bundle</packaging>
    <name>QuerySQL64 - Core</name>
    <description>Core bundle for QuerySQL64</description>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.sling</groupId>
                <artifactId>maven-sling-plugin</artifactId>
            </plugin>
            <plugin>
                <groupId>org.apache.felix</groupId>
                <artifactId>maven-bundle-plugin</artifactId>
                <extensions>true</extensions>
                <configuration>
                    <instructions>
                        <!-- Import any version of javax.inject, to allow running on multiple versions of AEM -->
                        <Import-Package>javax.inject;version=0.0.0,*</Import-Package>
                        <Sling-Model-Packages>
                            com.aem.cust.core
                        </Sling-Model-Packages>
                    </instructions>
                </configuration>
            </plugin>
        </plugins>
    </build>

    <dependencies>
        <!-- OSGi Dependencies -->
        <dependency>
            <groupId>com.adobe.aem</groupId>
            <artifactId>uber-jar</artifactId>
            <classifier>apis</classifier>
        </dependency>
      
        <dependency>
            <groupId>org.apache.geronimo.specs</groupId>
            <artifactId>geronimo-atinject_1.0_spec</artifactId>
        </dependency>
        <dependency>
            <groupId>org.osgi</groupId>
            <artifactId>osgi.core</artifactId>
        </dependency>
        <dependency>
            <groupId>org.osgi</groupId>
            <artifactId>osgi.cmpn</artifactId>
        </dependency>
        <dependency>
            <groupId>org.osgi</groupId>
            <artifactId>osgi.annotation</artifactId>
        </dependency>
        <!-- Other Dependencies -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
        </dependency>
        <dependency>
            <groupId>javax.jcr</groupId>
            <artifactId>jcr</artifactId>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>servlet-api</artifactId>
        </dependency>
        <dependency>
            <groupId>com.adobe.aem</groupId>
            <artifactId>uber-jar</artifactId>
            <classifier>apis</classifier>
        </dependency>
        <dependency>
            <groupId>org.apache.sling</groupId>
            <artifactId>org.apache.sling.models.api</artifactId>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mockito</groupId>
            <artifactId>mockito-core</artifactId>
        </dependency>
        <dependency>
            <groupId>junit-addons</groupId>
            <artifactId>junit-addons</artifactId>
        </dependency>
    </dependencies>
</project>

Build the OSGi bundle using Maven

To build the OSGi bundle by using Maven, perform these steps:

  1. Open the command prompt and go to the C:\AdobeCQ\QuerySQL64.
  2. Run the following maven command: mvn -PautoInstallPackage install.
  3. The OSGi component can be found in the following folder: C:\AdobeCQ\QuerySQL64\core\target. The file name of the OSGi component is QuerySQL64.core-1.0-SNAPSHOT.jar.

The command -PautoInstallPackage automatically deploys the OSGi bundle to Experience Manager.

After you deploy the OSGi bundle, you will be able to see it in the Apache Felix Web Console (http://localhost:4502/system/console/configMgr).

OSGi
Apache Felix Web Console Bundles view

Add CSS and JQuery files to a CQ:ClientLibraryFolder node 

When you build the Maven Archetype 13 project, a client lib folder was automatically generated at this JCR location. 

/apps/QuerySQL64/clientlibs

In this example,  a data grid plugin is used named DataTables. This plugin is used to display the employee data a tabular format (see the illustration shown at the start of this article).

Download the DataTables plugin from the following URL:

http://www.datatables.net/

Download and extract the DataTables archive file. The Experience Manager component uses these files from the DataTables archive file:

  • datatables.min.css
  • datatables.min.js
 

Place these two files into the clientlibs folder, as shown in this illustration.

clientlib2
The JS and CSS file in a clientlib

Make sure that the clientlib-base has these two properties. 

clientlib
Two properties for the clientlibs folder

After you create the Clientlibs folder, add two CSS files, and the JQuery library files, and two map text files.

Note:

Notice that the categories is QuerySQL64.base. This clientlibs folder is referenced in this file: /apps/QuerySQL64/components/structure/page/customheaderlibs.html. Notice the following line of code: 

<sly data-sly-use.clientLib="/libs/granite/sightly/templates/clientlib.html"

     data-sly-call="${clientlib.css @ categories='QuerySQL64.base'}"/>

Text files

You have to add two text files to the clientlibs folder. These text files map to the JS file and the CSS file. The names of the text files are: css.txt and js.txt

The css.txt file contains the CSS file name: datatables.min.css.

The js.txt file contains the JS file name: datatables.min.js.

Note:

You can develop an easy and fast way to upload files to client lib folders. See the following article for more information: Creating Java Swing applications that posts files to AEM ClientLibs folders

Create the HTL Front End Component

When you use the Maven Archetype 13 archetype to create an Experience Manager project, a default front-end project is created, as shown in the following illustration.

project1
Default files created by Adobe Maven 13 Archetype project

Note:

For information about the default files created by the Maven 13 Archetype project, see this community article: Creating an Adobe Experience Manager 6.4 Project using Adobe Maven Archetype 13.

Add HTL code

For the purpose of this article, the HTL code is written within a Maven Archetype 13 default component located here:

/apps/QuerySQL64/components/content/helloworld

Add the following HTML code to the helloworld.html file. 

<script>


 $(document).ready(function() {
    var table = $('#myTable').DataTable();


    $('#submit').click( function() {




		 //Use JQuery AJAX request to post data to a Sling Servlet
    $.ajax({
         type: 'GET',    
         url:'/bin/myCustData',
         data:'type='+ 'data',
         success: function(msg){

           var xml = msg; 

            var oTable = $('#myTable').dataTable();
            oTable.fnClearTable(true);



             //Loop through this function for each Employee element
                //in the returned XML
                 $(xml).find('Employee').each(function(){
                         
                    var $field = $(this);
                    var Name = $field.find('Name').text();
                    var Job = $field.find('Position').text(); 
                    var Address = $field.find('Address').text();
                    var Age = $field.find('Age').text();
                    var Date = $field.find('Date').text();   
                    var Salary = $field.find('Salary').text();     
 
                    //Set the new data 
                    oTable.fnAddData( [
                        Name,
                        Job,
                        Address,
                        Age,
                        Date,
                        Salary,,]
                    );
            
                    });



            }

            
			});





  });  //end of click

}); // end ready
</script>



 <div>


<table id="myTable" class="display" style="width:100%">
        <thead>
            <tr>
                <th>${properties.text}</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>Tiger Nixon</td>
                <td>System Architect</td>
                <td>Edinburgh</td>
                <td>61</td>
                <td>2011/04/25</td>
                <td>$320,800</td>
            </tr>

        </tbody>
        <tfoot>
            <tr>
                <th>${properties.text}</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
            <input type="button" value="Submit"  name="submit" id="submit" value="Get Employees">
        </tfoot>
    </table>

    </div>

In this example, notice an ajax call is made within the script tag:

 

   $.ajax({
         type: 'GET',    
         url:'/bin/myCustData',
         data:'type='+ 'data',
         success: function(msg){

           var xml = msg; 

            var oTable = $('#myTable').dataTable();
            oTable.fnClearTable(true);



             //Loop through this function for each Employee element
                //in the returned XML
                 $(xml).find('Employee').each(function(){
                         
                    var $field = $(this);
                    var Name = $field.find('Name').text();
                    var Job = $field.find('Position').text(); 
                    var Address = $field.find('Address').text();
                    var Age = $field.find('Age').text();
                    var Date = $field.find('Date').text();   
                    var Salary = $field.find('Salary').text();     
 
                    //Set the new data 
                    oTable.fnAddData( [
                        Name,
                        Job,
                        Address,
                        Age,
                        Date,
                        Salary,,]
                    );
            
                    });

            }
            
});

This call is made when the user clicks the Get Employee data button in the HTML table. An AJAX call is made to the simple servlet that you created earlier in this development article. The servlet returns XML data and the code parses the XML and sets the grid with this code: 

  $(xml).find('Employee').each(function(){

                        var $field = $(this);

                    var Name = $field.find('Name').text();

                    var Job = $field.find('Position').text(); 

                    var Address = $field.find('Address').text();

                    var Age = $field.find('Age').text();

                    var Date = $field.find('Date').text();   

                    var Salary = $field.find('Salary').text();     

 

                    //Set the new data 

                    oTable.fnAddData( [

                        Name,

                        Job,

                        Address,

                        Age,

                        Date,

                        Salary,,]

                    );

               });

View the output of the HTL component

To access the component, enter the following URL:

http://localhost:4502/editor.html/content/QuerySQL64/en.html

Click the Get Employees button. The following video shows data queried from MySQL.


See also

This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License  Twitter™ and Facebook posts are not covered under the terms of Creative Commons.

Legal Notices   |   Online Privacy Policy