I works nicely with HTL - you can use Sling Models. Here is the Service Interface for MySQL operation:
CustDataService
package com.aem.community.core;
public interface CustDataService {
public String getTotalCustomers() ;
}
CustDataServiceImp (contains app logic)
package com.aem.community.core;
import org.osgi.service.component.annotations.Component;
import org.osgi.service.component.annotations.Reference;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
//Add the DataSourcePool package
import com.day.commons.datasource.poolservice.DataSourcePool;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import javax.sql.DataSource;
@Component(service = CustDataService.class,
immediate = true
)
public class CustDataServiceImp implements CustDataService{
private final Logger logger = LoggerFactory.getLogger(getClass());
@Reference
private DataSourcePool source;
//Returns the number of customs in the Customer table
public String getTotalCustomers()
{
Connection c = null;
int rowcount = 0;
String strCount ="";
try
{
logger.info("**** ABOUT TO CREATE CONNECTION");
// Create a Connection object
c = getConnection();
ResultSet rs = null;
Statement s = c.createStatement();
Statement stmt = c.createStatement();
//Query all
String query = "select * from customer" ;
rs = stmt.executeQuery(query) ;
while (rs.next())
{
rowcount++;
}
logger.info("**** CUST REC IS "+rowcount) ;
strCount = java.lang.Integer.toString(rowcount);
return strCount;
}
catch(Exception e)
{
e.printStackTrace() ;
}
return "" ;
}
//Returns a connection using the configured DataSourcePool
public 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;
}
}
THis is the great part - we can call into the Service from the Sling Model class like this:
/*
* Copyright 2015 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.
*/
package com.aem.community.core.models;
import javax.annotation.PostConstruct;
import javax.inject.Inject;
import javax.inject.Named;
import org.apache.sling.api.resource.Resource;
import org.apache.sling.models.annotations.Default;
import org.apache.sling.models.annotations.Model;
import org.apache.sling.settings.SlingSettingsService;
import com.aem.community.core.CustDataService;
@Model(adaptables=Resource.class)
public class HelloWorldModel {
@Inject
private SlingSettingsService settings;
@Inject
private CustDataService custSer;
@Inject @Named("sling:resourceType") @Default(values="No resourceType")
protected String resourceType;
private String message;
@PostConstruct
protected void init() {
message = "\tHello World!\n";
message += "\tThis is instance: " + settings.getSlingId() + "\n";
message += "\tNumber of Customer Records: " + custSer.getTotalCustomers() + "\n";
}
public String getMessage() {
return message;
}
}
We will get all of this into a HELPX artilce that we will release early next week.