Print Alphabi tFrom Number

public static void printAlphabitFromNumber(){
for(int i=0;i<26;i++)
System.out.print((char)('A'+i));// ABCDEFGHIJKLMNOPQRSTUVWXYZ
}

Enum with case Insensitive pattern (?i:)

public static enum SubjectType {
//case Insensitive pattern ?i:
MICRO_CENTRIFUGE("(?i:MIX)","max"),
FATHER("(?i:F[C2BV])", "F");

final String _suffix;
final String _lowercase;
//this contractor execute two times.
SubjectType(String suffix,String lowercase) {
_suffix = suffix;
_lowercase=lowercase;
}
//suffix : any String .."fc"
public static SubjectType parse(String suffix) {
for (SubjectType s : values()) {
//know matches()works as case Insensitive..for 'fc' also it return true.
if (suffix.matches(s._suffix)) return s;
}
return null;
}
}

DateDiffDays and max date detween two date's

1.Date Diff Days :
static public long dateDiffDays(Date d1, Date d2)
{
Calendar c = Calendar.getInstance();
c.clear();
c.setTime(d1);
long t1 = c.getTimeInMillis();

c.clear();
c.setTime(d2);
long t2 = c.getTimeInMillis();

return (t1 - t2) / 1000L / 60L / 60L / 24L;
}
2. Max date :
static public Date max(Date d1, Date d2) {
if (d1==null) return d2;
if (d2==null) return d1;
if (d1.after(d2)) return d1;
return d2;
}

Remove Special Characters from String using Regular Expression

The following two examples space also consider as special char so I added /s in pattern. If you don't want you remove that.

1. Remove Special Characters from String using Regular Expression with Matcher.

String inputStr = "Shekhar' reddy* optra# systems?";
String patternStr = "([^a-zA-Z0-9 /s])";
Pattern pattern = Pattern.compile(patternStr);
Matcher matcher = pattern.matcher(inputStr);
StringBuffer buf = new StringBuffer();
while ((matcher.find())) {
matcher.appendReplacement(buf, "");
}
matcher.appendTail(buf);
String result = buf.toString();
System.out.println(result);//Shekhar reddy optra systems

2. Remove Special Characters from String using Regular Expression with split() method.
String inputStr = "Shekhar' reddy* optra# systems?";
String patternStr2 = "[^a-zA-Z0-9 /s]";
Pattern pattern2 = Pattern.compile(patternStr2);
String[] str=pattern2.split(inputStr);
StringBuffer buf1 = new StringBuffer();
for(String str1:str){
buf1.append(str1);
}
System.out.println(buf1);//Shekhar reddy optra systems

PDF_read_write_diplay_operations

Invoking service to display the PDF file (the PDF file DB)
Jsp file

Table creation:
DROP TABLE IF EXISTS `thorlabswebservice`.`pdf`;
CREATE TABLE `thorlabswebservice`.`pdf` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(45) default NULL,
`file` blob,
`content_type` varchar(45) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;








Open PDF




Controller Class: PDFController .java
package com.optrasystems.PDF;
import java.io.InputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.validation.Errors;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import com.optrasystems.model.User;
import com.optrasystems.mvc.validator.LoginValidator;
import com.optrasystems.mvc.validator.UserValidator;
import com.optrasystems.service.ImageProcessService;
import com.optrasystems.service.PilotSystemWebservice;
import com.optrasystems.service.UserService;
import com.optrasystems.util.PDF;

@Controller
public class PDFController {

/**
* this controller
*/
@RequestMapping("/PDF")
public String PDFOpen(HttpServletRequest request,HttpServletResponse response) {

//PDF pdf=userService.getPDF();
//request.getSession().setAttribute("pdf",pdf);
InputStream inputStream =null;
try
{
response.setContentType("application/pdf");

byte byteArray[] = new byte[5000];
PDF pdf=getPDF();
inputStream =pdf.getInputStream();

//following method open dialog box for "open", "save", nad "cancel"
//response.setHeader("Content-disposition", "attachment; filename="+pdf.getFileName()+".pdf");

//following method open the file directly....this method open the file in current page so need to open in pop-up
response.setHeader("Content-disposition", "inline; filename="+pdf.getFileName()+".pdf");

ServletOutputStream outStream = response.getOutputStream();

int n = 0;
while ( ( n = inputStream.read(byteArray)) != -1)
{
outStream.write(byteArray, 0, n);
}

outStream.flush();

inputStream.close();
outStream.close();
}
catch (Exception e)
{
e.printStackTrace();
}finally{
try{inputStream.close();

}catch (Exception e) {
e.printStackTrace();
}
}
//return "viewPDF";
return null;
}

//reading Pdf from DB.
public PDF getPDF(){

try{
// PDF class is DTO class
PDF result = (PDF) this.getHibernateTemplate().execute(
(new HibernateCallback() {

public PDF doInHibernate(Session session)
throws HibernateException, SQLException {
//reading Pdf from DB.
String squery = " SELECT name,file FROM PDF WHERE id = 3";
Statement statement = session.connection().createStatement();
ResultSet resultSet = statement.executeQuery(squery.toString());
resultSet.next();
PDF pdf=new PDF();
pdf.setFileName(resultSet.getString(1));
pdf.setInputStream(resultSet.getBinaryStream(2));
return pdf;
}

}));
return result;

}catch (Exception e) {
logger.error(""+ e.getMessage());
}
return null;
}
}

'PDF' DTO class: PDF.java
package com.optrasystems.util;
import java.io.InputStream;

/**
* @author k.panchani
*
*/
public class PDF {

private String fileName;
private InputStream inputStream;


public String getFileName() {
return fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName;
}
public InputStream getInputStream() {
return inputStream;
}
public void setInputStream(InputStream inputStream) {
this.inputStream = inputStream;
}
}
==========================================================================
General Reading and writing PDF file with DB

/**
*
*/
package com.optrasystems.PDF;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

/**
* @author k.panchani
* this class having oprations related to PDF file
*
*/
public class TestConnection {

/**
* @param args
*/
public static void main(String[] args) {
try{
TestWritePDF();
//TestReadPDF();
//Runtime.getRuntime().exec("rundll32 url.dll,FileProtocolHandler " + "c:\\chart.pdf");
}catch (Exception e) {
e.printStackTrace();
}

}
/** this method takes pdf file from C:\\Visio-PilotSystemSequence.pdf and
* inserting Pdf file into DB
*/
public static void TestWritePDF()throws Exception{
Connection c=null;
try{
c=getConnection();
String sql = "INSERT INTO PDF VALUES (?, ?, ?,?)";
PreparedStatement stmt = c.prepareStatement(sql);
stmt.setInt(1, 3);
stmt.setString(2, "Visioccccccc");

File image = new File("C:\\Visio-PilotSystemSequence.pdf");
FileInputStream fis = new FileInputStream(image);
stmt.setBinaryStream(3, fis, (int) image.length());
//stmt.setInt(4,image.)
stmt.execute();
c.close();

}catch (Exception e) {
e.printStackTrace();
}finally{
if(c!=null){
c.close();
}
}
}
//reading PDF file from DB and Storing in "E" drive
public static void TestReadPDF()throws Exception{
Connection c=null;
try {
c=getConnection();
String sql = "SELECT name,file FROM PDF WHERE id = 2 ";
PreparedStatement stmt = c.prepareStatement(sql);
ResultSet resultSet = stmt.executeQuery();
while (resultSet.next()) {
String name = resultSet.getString(1);
File image = new File("E:\\"+name+".pdf");
FileOutputStream fos = new FileOutputStream(image);

byte[] buffer = new byte[1];
InputStream is = resultSet.getBinaryStream(2);
while (is.read(buffer) > 0) {
fos.write(buffer);
}
}
}catch (Exception e) {
e.printStackTrace();
}finally{
if(c!=null){
c.close();
}
}
}

//this method get the Pdf file as bytes and return to caller
public static byte[] getBLOB() throws Exception {
Connection conn=null;
ResultSet rs = null;
PreparedStatement pstmt = null;
String query = "SELECT file FROM PDF WHERE id = ?";
try {
conn=getConnection();
pstmt = conn.prepareStatement(query);
pstmt.setInt(1, 2);
rs = pstmt.executeQuery();
rs.next();
Blob blob = rs.getBlob("file");
// materialize BLOB onto client
return blob.getBytes(1, (int) blob.length());
} finally {
rs.close();
pstmt.close();
conn.close();
}
}
public Connection getConnection(){
try
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
return DriverManager.getConnection("jdbc:mysql://localhost:3306/thorlabs","root","");
//return DriverManager.getConnection("jdbc:mysql://192.168.100.137:3306/formsjsf","root","pass");

}
catch(Exception e){
System.out.println("error:ConnectionMaker::getConnection()");
e.printStackTrace();
return null;
}
}

}

How to use ENUM in Hibernate

Using ENUM in Hibernate
1. create enum type
public enum LoginStatus {

ACTIVE,
INACTIVE
}

2. add property with Enum type
private LoginStatus status;

public LoginStatus getStatus() {
return status;
}

public void setStatus(LoginStatus status) {
this.status = status;
}

3. if we use the above code this will insert the entries as index of ENUM values (0,1) in DB insteade of ACTIVE,INACTIVE values

if we want to insert as values then we need to create new table with ENUM values.

Complete JAXB Example

Complete JAXB Example
Xml file: foo.xml




shekhar

12
Hyb
AP

Kajal

12
Hyb
AP

12
Hyb
AP


12
Hyb
AP



Pojo classes :
RootElement.java
package com.client.optrasystems.service;
@XmlRootElement
public class RootElement {
int step;
List element=new ArrayList();
List subElement=new ArrayList();

@XmlAttribute
public int getStep() {
return step;
}
public void setStep(int step) {
this.step = step;
}
@XmlElementWrapper(name="list")
@XmlElement(name="Element")
public List getElement() {
return element;
}

public void setElement(List element) {
this.element = element;
}

@XmlElement(name="SubElement")
public List getSubElement() {
return subElement;
}
public void setSubElement(List subElement) {
this.subElement = subElement;
}

}
SubElement.java
package com.client.optrasystems.service;
@XmlRootElement
public class SubElement {

private Integer home;
private String city;
private String state;

@XmlElement
public Integer getHome() {
return home;
}
public void setHome(Integer home) {
this.home = home;
}
@XmlElement
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}

@XmlElement
public String getState() {
return state;
}
public void setState(String state) {
this.state = state;
}

}
Element.java
package com.client.optrasystems.service;
@XmlRootElement
public class Element {
Integer size;
String name;
Address address;

@XmlAttribute
public Integer getSize() {
return size;
}
public void setSize(Integer size) {
this.size = size;
}
@XmlElement
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@XmlElement(name="address")
public Address getAddress() {
return address;
}
public void setAddress(Address address) {
this.address = address;
}
}
Address.java
package com.client.optrasystems.service;
public class Address {

private Integer home;
private String city;
private String state;

@XmlElement
public Integer getHome() {
return home;
}
public void setHome(Integer home) {
this.home = home;
}
@XmlElement
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
@XmlElement
public String getState() {
return state;
}
public void setState(String state) {
this.state = state;
}
}
Test class
package com.client.optrasystems.service;
import java.io.File;
import java.io.FileOutputStream;
import javax.xml.bind.JAXBContext;
import javax.xml.bind.JAXBElement;
import javax.xml.bind.Marshaller;
import javax.xml.bind.Unmarshaller;
import javax.xml.transform.stream.StreamSource;

public class TestWS {
public static void main(String[] args) {
try
{
JAXBContext jc=JAXBContext.newInstance(RootElement.class);
//creating java object from XML.
Unmarshaller u =jc.createUnmarshaller();
JAXBElement root = u.unmarshal(new StreamSource(new File("D:\\workspace\\webservice_client\\src\\com\\client\\optrasystems\\service\\foo.xml")),RootElement.class);
RootElement foo = root.getValue();
//creating xml file from java object using Jaxb
Marshaller marshaller=jc.createMarshaller();
marshaller.marshal(foo, new FileOutputStream(new File("c:\\foo.xml")));
}catch(Exception e){
e.printStackTrace();
}
}
}

converting java.util.Date to java.sql.Date and java.sql.Date to java.util.Date

Date lDate=new Date();
System.out.println("date :"+lDate);
// convert util date to sql with pattern (sql date supports yyyy-MM-dd only)
SimpleDateFormat m_dateFormat1 = new SimpleDateFormat("yyyy-MM-dd");
java.sql.Date lDate1=java.sql.Date.valueOf(m_dateFormat1.format(lDate));
System.out.println("Sql date :"+lDate1.toString());

// convert sql date to util date with pattern
SimpleDateFormat m_dateFormat2 = new SimpleDateFormat("yyyyMMdd");
String ldateDate=m_dateFormat1.format(lDate1);
System.out.println("util date :"+m_dateFormat2.parse(ldateDate));


OutPut:

date :Thu Aug 20 17:20:26 IST 2009
Sql date :2009-08-20
util date :Mon Dec 08 00:00:00 IST 2008

java code to parse XML using Dom parser

1.xml file
--------

























2.java code to parse the data. here ThorImageExperimentXML is pojo class having all related properties


@Override
public ThorImageExperimentXML getExperimentInformationFromXML(String xmlPath) {
// TODO Auto-generated method stub
ParseXMLFile(xmlPath);
webserviceImageProcessDao.saveXMLExperiment(thorImageExperimentXML);
return thorImageExperimentXML;
}

private ThorImageExperimentXML ParseXMLFile(String xmlFileName) {
Node root;
try {
Document doc = parseFile(xmlFileName);
root = doc.getDocumentElement();

return writeDocumentToOutput(root, 0);

} catch (Exception e) {
e.printStackTrace();
} finally {
root = null;

}
return writeDocumentToOutput(root, 0);
}

private Document parseFile(String fileName) {
Document doc = null;
try {
DocumentBuilder docBuilder;
DocumentBuilderFactory docBuilderFactory = DocumentBuilderFactory
.newInstance();
docBuilderFactory.setIgnoringElementContentWhitespace(true);
docBuilder = docBuilderFactory.newDocumentBuilder();
File sourceFile = new File(fileName);
try {
doc = docBuilder.parse(sourceFile);
sourceFile = null;
} catch (SAXException e) {
return null;
}
} catch (Exception e) {
if (logger.isDebugEnabled()) {
logger.debug("Error occured while parsing xml "
+ e.getMessage());
}
}
return doc;
}

private ThorImageExperimentXML writeDocumentToOutput(Node node, int indent) {
String value = "";

try {
// get element name

String nodeName = node.getNodeName();

// get element value
String nodeValue = getElementValue(node);
// get attributes of element
NamedNodeMap attributes = node.getAttributes();

if (nodeName.toString().equals("Name")) {
for (int i = 0; i < attributes.getLength(); i++) {
Node attribute = attributes.item(i);
if (attribute.getNodeName().toString().equals("name")) {
System.out.println("The Exact attribute Name== "
+ attribute.getNodeValue().toString());
thorImageExperimentXML.setName(attribute.getNodeValue().toString());
}
}
}

if (nodeName.toString().equals("Date")) {
for (int i = 0; i < attributes.getLength(); i++) {
Node attribute = attributes.item(i);
if (attribute.getNodeName().toString().equals("date")) {
System.out.println("The Exact attribute Date== "
+ attribute.getNodeValue().toString().replace("/", "-"));
//java.sql.Date.valueOf(attribute.getNodeValue().toString().replace("/", "-"))
thorImageExperimentXML.setDate(attribute.getNodeValue().toString());
}
}
}

if (nodeName.toString().equals("User")) {
for (int i = 0; i < attributes.getLength(); i++) {
Node attribute = attributes.item(i);
if (attribute.getNodeName().toString().equals("name")) {
System.out.println("The Exact attribute User== "
+ attribute.getNodeValue().toString());
thorImageExperimentXML.setUserName(attribute.getNodeValue().toString());
}
}
}

if (nodeName.toString().equals("Computer")) {
for (int i = 0; i < attributes.getLength(); i++) {
Node attribute = attributes.item(i);
if (attribute.getNodeName().toString().equals("name")) {
System.out.println("The Exact attribute Computer== "
+ attribute.getNodeValue().toString());
}
thorImageExperimentXML.setComputerName(attribute.getNodeValue().toString());
}
}

if (nodeName.toString().equals("Software")) {
for (int i = 0; i < attributes.getLength(); i++) {
Node attribute = attributes.item(i);
if (attribute.getNodeName().toString().equals("version")) {
System.out.println("The Exact attribute Software== "
+ attribute.getNodeValue().toString());
}
thorImageExperimentXML.setSoftwareVersion(Float.parseFloat(attribute.getNodeValue().toString()));
}
}

if (nodeName.toString().equals("Camera")) {
for (int i = 0; i < attributes.getLength(); i++) {
Node attribute = attributes.item(i);
if (attribute.getNodeName().toString().equals("name")) {
System.out.println("The Exact attribute Camera== "
+ attribute.getNodeValue().toString());
thorImageExperimentXML.setCameraName(attribute.getNodeValue().toString());
}
if (attribute.getNodeName().toString().equals("width")) {
System.out.println("The Exact attribute Camera== "
+ attribute.getNodeValue().toString());
thorImageExperimentXML.setCameraWidth(Float.parseFloat(attribute.getNodeValue().toString()));
}
if (attribute.getNodeName().toString().equals("height")) {
System.out.println("The Exact attribute Camera== "
+ attribute.getNodeValue().toString());
thorImageExperimentXML.setCameraHeight(Float.parseFloat(attribute.getNodeValue().toString()));
}
if (attribute.getNodeName().toString()
.equals("pixelSizeUM")) {
System.out.println("The Exact attribute Camera== "
+ attribute.getNodeValue().toString());
thorImageExperimentXML.setCameraPixelSizeUM(Float.parseFloat(attribute.getNodeValue().toString()));
}
}
}

if (nodeName.toString().equals("Magnification")) {
for (int i = 0; i < attributes.getLength(); i++) {
Node attribute = attributes.item(i);
if (attribute.getNodeName().toString().equals("mag")) {
System.out.println("The Exact attribute Magnification== "
+ attribute.getNodeValue().toString());
}
thorImageExperimentXML.setMagnificationMag(Float.parseFloat(attribute.getNodeValue().toString()));
}
}

if (nodeName.toString().equals("Wavelength")) {
for (int i = 0; i < attributes.getLength(); i++) {
Node attribute = attributes.item(i);
if (attribute.getNodeName().toString().equals("name")) {
System.out.println("The Exact attribute Wavelength== "
+ attribute.getNodeValue().toString());
thorImageExperimentXML.setWavelengthName(attribute.getNodeValue().toString());
}
if (attribute.getNodeName().toString().equals(
"exposureTimeMS")) {
System.out.println("The Exact attribute Wavelength== "
+ attribute.getNodeValue().toString());
thorImageExperimentXML.setWavelengthExposureTimeMS(Float.parseFloat(attribute.getNodeValue().toString()));
}
}
}

if (nodeName.toString().equals("ZStage")) {
for (int i = 0; i < attributes.getLength(); i++) {
Node attribute = attributes.item(i);
if (attribute.getNodeName().toString().equals("name")) {
System.out.println("The Exact attribute ZStage== "
+ attribute.getNodeValue().toString());
thorImageExperimentXML.setZstageName(attribute.getNodeValue().toString());
}
if (attribute.getNodeName().toString().equals("steps")) {
System.out.println("The Exact attribute ZStage== "
+ attribute.getNodeValue().toString());
thorImageExperimentXML.setZstageSteps(Float.parseFloat(attribute.getNodeValue().toString()));
}
if (attribute.getNodeName().toString().equals("stepSizeUM")) {
System.out.println("The Exact attribute ZStage== "
+ attribute.getNodeValue().toString());
thorImageExperimentXML.setZstageStepSizeUM(Float.parseFloat(attribute.getNodeValue().toString()));
}
}
}

if (nodeName.toString().equals("Timelapse")) {
for (int i = 0; i < attributes.getLength(); i++) {
Node attribute = attributes.item(i);
if (attribute.getNodeName().toString().equals("timepoints")) {
System.out.println("The Exact attribute Timelapse== "
+ attribute.getNodeValue().toString());
thorImageExperimentXML.setTimelapseIntervalSec(Float.parseFloat(attribute.getNodeValue().toString()));
}
if (attribute.getNodeName().toString()
.equals("intervalSec")) {
System.out.println("The Exact attribute Timelapse== "
+ attribute.getNodeValue().toString());
thorImageExperimentXML.setTimelapseTimePoints(Float.parseFloat(attribute.getNodeValue().toString()));
}
}
}

if (nodeName.toString().equals("Sample")) {
for (int i = 0; i < attributes.getLength(); i++) {
Node attribute = attributes.item(i);
if (attribute.getNodeName().toString().equals("type")) {
System.out.println("The Exact attribute Sample type== "
+ attribute.getNodeValue().toString());
thorImageExperimentXML.setSampleType(Float.parseFloat(attribute.getNodeValue().toString()));
}
if (attribute.getNodeName().toString().equals("offsetXMM")) {
System.out.println("The Exact attribute Sample X== "
+ attribute.getNodeValue().toString());
thorImageExperimentXML.setSampleOffsetXMM(Float.parseFloat(attribute.getNodeValue().toString()));
}
if (attribute.getNodeName().toString().equals("offsetYMM")) {
System.out.println("The Exact attribute Sample Y== "
+ attribute.getNodeValue().toString());
thorImageExperimentXML.setSampleOffsetYMM(Float.parseFloat(attribute.getNodeValue().toString()));
}
}
}

if (nodeName.toString().equals("Wells")) {
for (int i = 0; i < attributes.getLength(); i++) {
Node attribute = attributes.item(i);
if (attribute.getNodeName().toString().equals("rows")) {
System.out.println("The Exact attribute Wells== "
+ attribute.getNodeValue().toString());
thorImageExperimentXML.setWellsRows(Float.parseFloat(attribute.getNodeValue().toString()));
}
if (attribute.getNodeName().toString().equals("columns")) {
System.out.println("The Exact attribute Wells== "
+ attribute.getNodeValue().toString());
thorImageExperimentXML.setWellsColumns(Float.parseFloat(attribute.getNodeValue().toString()));
}
if (attribute.getNodeName().toString().equals(
"wellOffsetXMM")) {
System.out.println("The Exact attribute Wells== "
+ attribute.getNodeValue().toString());
thorImageExperimentXML.setWellsWellOffsetXMM(Float.parseFloat(attribute.getNodeValue().toString()));
}
if (attribute.getNodeName().toString().equals(
"wellOffsetYMM")) {
System.out.println("The Exact attribute Wells== "
+ attribute.getNodeValue().toString());
thorImageExperimentXML.setWellsWellOffsetYMM(Float.parseFloat(attribute.getNodeValue().toString()));
}
}
}

if (nodeName.toString().equals("SubImages")) {
for (int i = 0; i < attributes.getLength(); i++) {
Node attribute = attributes.item(i);
if (attribute.getNodeName().toString().equals("subRows")) {
System.out.println("The Exact attribute SubImages== "
+ attribute.getNodeValue().toString());
thorImageExperimentXML.setSubimagesSubRows(Float.parseFloat(attribute.getNodeValue().toString()));
}
if (attribute.getNodeName().toString().equals("subColumns")) {
System.out.println("The Exact attribute SubImages== "
+ attribute.getNodeValue().toString());
thorImageExperimentXML.setSubimagesSubColumns(Float.parseFloat(attribute.getNodeValue().toString()));
}
if (attribute.getNodeName().toString().equals(
"transOffsetXMM")) {
System.out.println("The Exact attribute SubImages== "
+ attribute.getNodeValue().toString());
thorImageExperimentXML.setSubimagesTransOffsetXMM(Float.parseFloat(attribute.getNodeValue().toString()));
}
if (attribute.getNodeName().toString().equals(
"transOffsetYMM")) {
System.out.println("The Exact attribute SubImages== "
+ attribute.getNodeValue().toString());
thorImageExperimentXML.setSubimagesTransOffsetYMM(Float.parseFloat(attribute.getNodeValue().toString()));
}
}
}

if (nodeName.toString().equals("Comments")) {
for (int i = 0; i < attributes.getLength(); i++) {
Node attribute = attributes.item(i);
if (attribute.getNodeName().toString().equals("text")) {
System.out.println("The Exact attribute Comments== "
+ attribute.getNodeValue().toString());
}
thorImageExperimentXML.setCommentsText(attribute.getNodeValue().toString());
}
}
// write all child nodes recursively
NodeList children = node.getChildNodes();

// middle="";
for (int i = 0; i < children.getLength(); i++) {
Node child = children.item(i);
if (child.getNodeType() == Node.ELEMENT_NODE) {
// if(node.getNodeName().equals("Computer"))
valuecount = i;
writeDocumentToOutput(child, indent + 2);

}
}
nodeName = null;
nodeValue = null;
} catch (Exception e) {
if (logger.isDebugEnabled()) {
e.printStackTrace();
logger
.debug("Error occured in Comman Class while writing to document "
+ e.getMessage());
}
}
return thorImageExperimentXML;
}

private final String getElementValue(Node elem) {
try {
Node kid;
if (elem != null) {
if (elem.hasChildNodes()) {
for (kid = elem.getFirstChild(); kid != null; kid = kid
.getNextSibling()) {
if (kid.getNodeType() == Node.TEXT_NODE) {
return kid.getNodeValue();
}
}
}
}
} catch (Exception e) {
if (logger.isDebugEnabled()) {
logger
.debug("Error occured in Comman Class while getting element value "
+ e.getMessage());
}
}
return "";
}

Getting Image meta data using image File path

the following method return list of metadata info


public static List> readMetaData(String file) {
try {

// Crating image reader for given image.
IFormatReader reader = new ImageReader();

reader.setMetadataFiltered(true);
reader.setOriginalMetadataPopulated(true);

//Passing image path to reader.
reader.setId(file);

List> result = new ArrayList>();

// Extracting core Metadata.
ArrayList coreMataData = new ArrayList();
// Core Mata Data
coreMataData.add(new ImageMatadata("Image Count", reader
.getImageCount()));
coreMataData.add(new ImageMatadata("RGB", reader.isRGB() + " ("
+ reader.getRGBChannelCount() + ")"));
coreMataData.add(new ImageMatadata("Interleaved", reader
.isInterleaved()));
coreMataData.add(new ImageMatadata("Indexed", reader.isIndexed()));
coreMataData.add(new ImageMatadata("Width", reader.getSizeX()));
coreMataData.add(new ImageMatadata("Height", reader.getSizeY()));
coreMataData.add(new ImageMatadata("SizeZ", reader.getSizeZ()));
coreMataData.add(new ImageMatadata("SizeT", reader.getSizeC()));
coreMataData.add(new ImageMatadata("SizeC", reader.getSizeT()));
coreMataData.add(new ImageMatadata("Thumbnail size", reader
.getThumbSizeX()
+ " x " + reader.getThumbSizeY()));
coreMataData.add(new ImageMatadata("Endianness", reader
.isLittleEndian() ? "intel (little)" : "motorola (big)"));
coreMataData
.add(new ImageMatadata("Dimension order", reader
.getDimensionOrder()
+ (reader.isOrderCertain() ? " (certain)"
: " (uncertain)")));
coreMataData.add(new ImageMatadata("Pixel type", FormatTools
.getPixelTypeString(reader.getPixelType())));
coreMataData.add(new ImageMatadata("Metadata complete", reader
.isMetadataComplete()));
coreMataData.add(new ImageMatadata("Thumbnail series", reader
.isThumbnailSeries()));

result.add(coreMataData);

// Extracting Format Specific Data

ArrayList formatSpecificMeta = new ArrayList();

Hashtable formatMeta = reader.getMetadata();

if (formatSpecificMeta != null && formatMeta.size() > 0) {
Enumeration keys = formatMeta.keys();
while (keys.hasMoreElements()) {
String key = (String) keys.nextElement();
formatSpecificMeta.add(new ImageMatadata(key, formatMeta
.get(key)));

}
}
result.add(formatSpecificMeta);

return result;

} catch (FormatException e) {

logger.debug("Problem in getting matadata ", e);
} catch (IOException e) {
logger.debug("Problem in getting matadata ", e);
}

return null;
}


ImageMatadata .java:

public class ImageMatadata implements Serializable{

private String name;

private Object value;

public ImageMatadata(String name, Object value) {
this.name = name;
this.value = value;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

public Object getValue() {
return value;
}

public void setValue(Object value) {
this.value = value;
}

}

Hibernate : how to get the data from many to many relation tables

Hibernate : how to get the data from many to many relation tables :

Query : select project from Project as project join project.users user where user.userID=:userId


Example:

List projectList =(ArrayList)getHibernateTemplate().execute(new HibernateCallback(){
@Override
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
String lHql="select project from Project as project join project.users user where user.userID=:userId";
Query lQuery=session.createQuery(lHql);
lQuery.setLong("userId", pUserId);
List lProject=lQuery.list();
return lProject;
}
});

imp Queris in Promotion dao

package com.meijer.foodlab.dao.spring;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.ScrollableResults;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.meijer.foodlab.dao.IPromotionDao;
import com.meijer.foodlab.model.domain.Coupon;
import com.meijer.foodlab.model.domain.CouponListResult;
import com.meijer.foodlab.model.domain.IngredientItemPromotion;
import com.meijer.foodlab.model.domain.Promotion;
import com.meijer.foodlab.model.domain.PromotionCategory;
import com.meijer.foodlab.model.domain.PromotionList;
import com.meijer.foodlab.model.domain.Special;
import com.meijer.foodlab.model.domain.UpcPromotion;

/**
* @author kiranreddy
*
*/
public class PromotionDao extends BaseSpringDao implements IPromotionDao {
private static final Logger LOG = Logger.getLogger(PromotionDao.class);

protected JdbcTemplate m_jdbcTemplate;

public PromotionDao() {
super(Promotion.class, "Promotion");
}

public PromotionDao(Class pClazz, String pEntityName) {
super(pClazz, pEntityName);
}

public List findByIds(final List pPromotionIds) {

if (LOG.isDebugEnabled()) {
LOG.debug("finding Promotions by Ids");
}
try {
List lPromotion = (List)getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {
return pSession.createQuery(
"select this from Promotion as this where this.promotionId in (:pIds)")
.setParameterList("pIds", pPromotionIds)
.list();
}
});

if (null != lPromotion) {
if (LOG.isDebugEnabled()) {
LOG.debug("finding Promotions by Ids");
}
}
return lPromotion;

} catch (RuntimeException lRe) {
LOG.error("finding Promotions by Ids", lRe);
throw lRe;
}

}

public List findSpecialsByStoreId( final Integer pStoreId){
List lList = new ArrayList();

if(pStoreId == null){
lList = (List)getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {
return pSession.createQuery("select this from Promotion this left join this.stores store" +
" where store.storeId is NULL and this.status='ACTIVE' and this.special=true" +
" and (current_date() between date(this.activationDate) and date(this.expirationDate))" +
" order by this.promotionId")
.list();
}
});
}else{
lList = (List)getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {
return pSession.createQuery("select this from Promotion this left join this.stores store" +
" where (store.storeId = :pStoreId or this.store.storeId is NULL) and this.status='ACTIVE' and this.special=true" +
" and (current_date() between date(this.activationDate) and date(this.expirationDate))" +
" order by this.promotionId")
.setInteger("pStoreId", pStoreId)
.list();
}
});
}
return lList;
}


public CouponListResult findCouponsByStoreId( final Integer pChainId, final Integer pStoreId,final Integer pOffset,final Integer pMaxResults){
CouponListResult lList = new CouponListResult();

String join = " left join this.brand br ";
String where = " and (br.chain.id IS NULL";
if(null != pChainId){
where += " or br.chain.id = "+pChainId+") ";
} else {
where += ")";
}
final String lChainJoinCriteria = join;
final String lChainWhereCriteria = where;

if(pStoreId == null){
lList = (CouponListResult)getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

Long lCount = (Long)pSession.createQuery("select count(distinct this) from Promotion this left join this.ingredientPromotions itempro left join itempro.ingredientItem ingitem left join ingitem.ingredients ings " +
" left join this.stores store " + lChainJoinCriteria +
" where store.storeId is NULL and this.status='ACTIVE' and this.special=false" +
" and (current_date() between date(this.activationDate) and date(this.expirationDate)) " + lChainWhereCriteria +
" order by ings.category desc")
.uniqueResult();

List lPromotions = pSession.createQuery("select distinct this,ings.category from Promotion this left join this.ingredientPromotions itempro left join itempro.ingredientItem ingitem left join ingitem.ingredients ings " +
" left join this.stores store " + lChainJoinCriteria +
" where store.storeId is NULL and this.status='ACTIVE' and this.special=false" +
" and (current_date() between date(this.activationDate) and date(this.expirationDate)) " + lChainWhereCriteria +
" group by this order by ings.category desc")
.setFirstResult(pOffset.intValue())
.setMaxResults(pMaxResults.intValue())
.list();

List lPromos = new ArrayList();
for(Object[] lPromotion : lPromotions){
Promotion lPromo = (Promotion)lPromotion[0];
String lCategory = (String)lPromotion[1];
lPromo.setCategory(lCategory);
lPromos.add(lPromo);
}

CouponListResult lList = new CouponListResult();
lList.setList(lPromos);
lList.setMaxResults(pMaxResults);
lList.setOffset(pOffset);
lList.setTotalCount(lCount.intValue());
return lList;
}
});
} else {

lList = (CouponListResult)getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

Long lCount = (Long)pSession.createQuery("select count(distinct this) from Promotion this left join this.ingredientPromotions itempro left join itempro.ingredientItem ingitem left join ingitem.ingredients ings " +
" left join this.stores store" + lChainJoinCriteria +
" where (store.unitId = :pStoreId or store.storeId is NULL) and this.status='ACTIVE' and this.special=false" +
" and (current_date() between date(this.activationDate) and date(this.expirationDate)) " + lChainWhereCriteria +
" order by ings.category")
.setInteger("pStoreId", pStoreId)
.uniqueResult();

List lPromotions = pSession.createQuery("select distinct this,ings.category from Promotion this left join this.ingredientPromotions itempro left join itempro.ingredientItem ingitem left join ingitem.ingredients ings " +
" left join this.stores store" + lChainJoinCriteria +
" where (store.unitId = :pStoreId or store.storeId is NULL) and this.status='ACTIVE' and this.special=false" +
" and (current_date() between date(this.activationDate) and date(this.expirationDate)) " + lChainWhereCriteria +
" group by this order by ings.category desc")
.setInteger("pStoreId", pStoreId)
.setFirstResult(pOffset.intValue())
.setMaxResults(pMaxResults.intValue())
.list();

List lPromos = new ArrayList();
for(Object[] lPromotion : lPromotions){
Promotion lPromo = (Promotion)lPromotion[0];
String lCategory = (String)lPromotion[1];
lPromo.setCategory(lCategory);
lPromos.add(lPromo);
}

CouponListResult lList = new CouponListResult();
lList.setList(lPromos);
lList.setMaxResults(pMaxResults);
lList.setOffset(pOffset);
lList.setTotalCount(lCount.intValue());
return lList;
}
});
}

return lList;
}

public PromotionList findByCategory(final Integer pCategoryId, final int pOffset, final int pMaxResults) {

if (LOG.isDebugEnabled()) {
LOG.debug("find promotions by category id");
}
try {
Object lResult = getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

Long count = (Long)pSession.createQuery(
"select count(promotions) from PromotionCategory this" +
" join fetch this.promotions as promotions" +
" where this.promotionCategoryId = :id" +
" group by this")
.setInteger("id", pCategoryId)
.uniqueResult();

List lResult = (List)pSession.createQuery(
"select p" +
" from PromotionCategory this join fetch this.promotions p" +
" where this.promotionCategoryId = :id")
.setInteger("id", pCategoryId)
.setFirstResult(pOffset)
.setMaxResults(pMaxResults)
.list();

PromotionList lPromotionResults = new PromotionList();
lPromotionResults.setList(lResult);
lPromotionResults.setMaxResults(pMaxResults);
lPromotionResults.setOffset(pOffset);
lPromotionResults.setTotalCount(count.intValue());


if (LOG.isDebugEnabled()) {
LOG.debug("find promotions by category id successful, found " + lResult.size() + " Promotions");
}

return lPromotionResults;
}
});

PromotionList lPromotions = (PromotionList)lResult;
return lPromotions;

} catch (RuntimeException lRe) {
LOG.error("find promotions by category id failed", lRe);
throw lRe;
}

}

public PromotionList findActivePromotionsByCategory(final Integer pChainId, final String pCategoryName,final Integer pStoreId, final int pOffset, final int pMaxResults) {

if (LOG.isDebugEnabled()) {
LOG.debug("find promotions by category name");
}
try {
Object lResult = getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

String join = " left join store.chain ch ";
String where = " and (ch IS NULL";
if(null != pChainId){
where += " or ch.id = "+pChainId+") ";
} else {
where += ")";
}

Long count = (Long)pSession.createQuery("select count(distinct promo) from PromotionCategory cat " +
" inner join cat.promotions promo left join promo.ingredientPromotions ingpromos left join ingpromos.ingredientItem ingitem left join ingitem.ingredients ings " +
" inner join promo.stores store " + join +
" where store.unitId = :pStoreId and promo.status='ACTIVE'" +
" and cat.name = :pCatName " +
" and cat.active = :pStatus" +
" and ( :date between date(promo.activationDate) and date(promo.expirationDate) ) " + where +
" order by ings.category desc")
.setString("pCatName", pCategoryName)
.setDate("date", new Date())
.setInteger("pStoreId", pStoreId)
.setBoolean("pStatus", true)
.uniqueResult();

List lResult = (List)pSession.createQuery("select promo,ings.category from PromotionCategory cat " +
" inner join cat.promotions promo left join promo.ingredientPromotions ingpromos left join ingpromos.ingredientItem ingitem left join ingitem.ingredients ings " +
" inner join promo.stores store" + join +
" where store.unitId = :pStoreId and promo.status='ACTIVE'" +
" and cat.name = :pCatName " +
" and cat.active = :pStatus" +
" and ( :date between date(promo.activationDate) and date(promo.expirationDate) )" + where +
" group by promo order by ings.category desc")
.setDate("date", new Date())
.setFirstResult(pOffset)
.setMaxResults(pMaxResults)
.setString("pCatName", pCategoryName)
.setInteger("pStoreId", pStoreId)
.setBoolean("pStatus", true)
.list();

List lPromos = new ArrayList();
for(Object[] lPromotion : lResult){
Promotion lPromo = (Promotion)lPromotion[0];
String lCategory = (String)lPromotion[1];
lPromo.setCategory(lCategory);
lPromos.add(lPromo);
}

PromotionList lPromotionResults = new PromotionList();
lPromotionResults.setList(lPromos);
lPromotionResults.setMaxResults(pMaxResults);
lPromotionResults.setOffset(pOffset);
lPromotionResults.setTotalCount(count.intValue());


if (LOG.isDebugEnabled()) {
LOG.debug("find promotions by category name successful, found " + lResult.size() + " Promotions");
}

return lPromotionResults;
}
});

PromotionList lPromotions = (PromotionList)lResult;
return lPromotions;

} catch (RuntimeException lRe) {
LOG.error("find promotions by category name failed", lRe);
throw lRe;
}
}
/**
* Returns the OneStops on given date range with support to paging.
*
* Finds promotions by date range irrespective of status, used for admin services.
*/
public PromotionList findOneStopsByDateRange(final Date pStateDate,final Date pEndDate,final int pOffSet,final int pMaxResults) {
try {
Object lResult = getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

//forming queries
String lQueryforCount = "select count(promotion) from Promotion promotion"+
" where ( DATE(promotion.activationDate) between DATE(:pStart) and DATE(:pEnd)) "+
"or ( DATE(promotion.expirationDate) between DATE(:pStart) and DATE(:pEnd)) and promotion.special = true "+
"order by promotion.activationDate";
String lQueryForOneStops = "select promotion from Promotion promotion"+
" where ( DATE(promotion.activationDate) between DATE(:pStart) and DATE(:pEnd)) "+
"or ( DATE(promotion.expirationDate) between DATE(:pStart) and DATE(:pEnd)) and promotion.special = true group by promotion.promotionId "+
"order by promotion.activationDate";

Long count = (Long)pSession.createQuery(lQueryforCount)
.setDate("pStart", pStateDate)
.setDate("pEnd",pEndDate)
.uniqueResult();

List lResult = (List) pSession.createQuery(lQueryForOneStops)
.setFirstResult(pOffSet)
.setMaxResults(pMaxResults)
.setDate("pStart", pStateDate)
.setDate("pEnd",pEndDate)
.list();

PromotionList lPromotionResults = new PromotionList();
lPromotionResults.setList(lResult);
lPromotionResults.setMaxResults(pMaxResults);
lPromotionResults.setOffset(pOffSet);
lPromotionResults.setTotalCount(count.intValue());

if (LOG.isDebugEnabled()) {
LOG.debug("findOneStopsByDateRange successfull. found " + lResult.size() + " Promotions");
}

return lPromotionResults;
}
});

PromotionList lPromotions = (PromotionList)lResult;
return lPromotions;
} catch (RuntimeException e) {
LOG.error("findOneStopsByDateRange failed", e);
throw e;
}
}

/**
* Returns the promotions on given date range with support to paging.
*
* Finds promotions by date range irrespective of status, used for admin services.
*/
public PromotionList findPromotionsByDateRange(final Date pStateDate,final Date pEndDate,final int pOffSet,final int pMaxResults) {
try {
Object lResult = getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

//forming queries
String lQueryforCount = "select count(promotion) from Promotion promotion"+
" where ((DATE(promotion.activationDate) between DATE(:pStart) and DATE(:pEnd)) "+
"or (DATE(promotion.expirationDate) between DATE(:pStart) and DATE(:pEnd))) "+
"order by promotion.activationDate";
String lQueryForOneStops = "select promotion from Promotion promotion"+
" where ((DATE(promotion.activationDate) between DATE(:pStart) and DATE(:pEnd)) "+
"or (DATE(promotion.expirationDate) between DATE(:pStart) and DATE(:pEnd))) " +
"group by promotion.promotionId "+
"order by promotion.activationDate";

Long count = (Long)pSession.createQuery(lQueryforCount)
.setDate("pStart", pStateDate)
.setDate("pEnd",pEndDate)
.uniqueResult();

List lResult = (List) pSession.createQuery(lQueryForOneStops)
.setFirstResult(pOffSet)
.setMaxResults(pMaxResults)
.setDate("pStart", pStateDate)
.setDate("pEnd",pEndDate)
.list();

PromotionList lPromotionResults = new PromotionList();
lPromotionResults.setList(lResult);
lPromotionResults.setMaxResults(pMaxResults);
lPromotionResults.setOffset(pOffSet);
lPromotionResults.setTotalCount(count.intValue());

if (LOG.isDebugEnabled()) {
LOG.debug("findPromotionsByDateRange successfull. found " + lResult.size() + " Promotions");
}

return lPromotionResults;
}
});

PromotionList lPromotions = (PromotionList)lResult;
return lPromotions;
} catch (RuntimeException e) {
LOG.error("findPromotionsByDateRange failed", e);
throw e;
}
}

public PromotionList findPendingPromotionsByDateRange(final int pOffSet,final int pMaxResults,final Integer pUnitId) {
try {
Object lResult = getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

String lBeforeCriteria = "";
String lCriteria = "";
if(null != pUnitId) {
lBeforeCriteria = " left join promotion.stores store ";
lCriteria = " and store.unitId = "+pUnitId.intValue();
}
//forming queries
String lQueryforCount = "select count(promotion) from Promotion promotion"+
lBeforeCriteria +
" where promotion.status='PENDING' " +
lCriteria +
" order by promotion.activationDate";
String lQueryForOneStops = "select promotion from Promotion promotion"+
lBeforeCriteria +
" where promotion.status='PENDING' " +
lCriteria +
" group by promotion.promotionId " +
" order by promotion.activationDate";

Long count = (Long)pSession.createQuery(lQueryforCount)
.uniqueResult();

List lResult = (List) pSession.createQuery(lQueryForOneStops)
.setFirstResult(pOffSet)
.setMaxResults(pMaxResults)
.list();

PromotionList lPromotionResults = new PromotionList();
lPromotionResults.setList(lResult);
lPromotionResults.setMaxResults(pMaxResults);
lPromotionResults.setOffset(pOffSet);
lPromotionResults.setTotalCount(count.intValue());

if (LOG.isDebugEnabled()) {
LOG.debug("findPromotionsByDateRange successfull. found " + lResult.size() + " Promotions");
}

return lPromotionResults;
}
});

PromotionList lPromotions = (PromotionList)lResult;
return lPromotions;
} catch (RuntimeException e) {
LOG.error("findPromotionsByDateRange failed", e);
throw e;
}
}

public List findPromotionByExternalId(final String pExternalId) {
if (LOG.isDebugEnabled()) {
LOG.debug("finding Promotions by ExternalId");
}
try {
List lPromotion = (List)getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {
return pSession.createQuery(
"select this from Promotion as this where this.externalId = :pExternalId")
.setString("pExternalId", pExternalId)
.list();
}
});

if (null != lPromotion) {
if (LOG.isDebugEnabled()) {
LOG.debug("finding Promotions by Ids");
}
}
return lPromotion;

} catch (RuntimeException lRe) {
LOG.error("finding Promotions by Ids", lRe);
throw lRe;
}
}
/**
* Finds promotions by store id irrespective of status and date range, used for admin services.
*/
public PromotionList findPromotionsByStoreId(final Integer pId, final Integer pOffset,final Integer pMaxResults) {
try {
Object lResult = getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

//forming queries
String lQueryforCount = "select count(this) from Promotion this left join this.stores store" +
" where (store.unitId = :pStoreId) " +
" order by this.promotionId";

String lQueryForList = "select this from Promotion this left join this.stores store" +
" where (store.unitId = :pStoreId) " +
" order by this.promotionId";

Long count = (Long)pSession.createQuery(lQueryforCount)
.setInteger("pStoreId", pId)
.uniqueResult();

List lResult = (List) pSession.createQuery(lQueryForList)
.setFirstResult(pOffset)
.setMaxResults(pMaxResults)
.setInteger("pStoreId", pId)
.list();

PromotionList lPromotionResults = new PromotionList();
lPromotionResults.setList(lResult);
lPromotionResults.setMaxResults(pMaxResults);
lPromotionResults.setOffset(pOffset);
lPromotionResults.setTotalCount(count.intValue());

if (LOG.isDebugEnabled()) {
LOG.debug("findPromotionsByStoreId successfull. found " + lResult.size() + " Promotions");
}

return lPromotionResults;
}
});

PromotionList lPromotions = (PromotionList)lResult;
return lPromotions;
} catch (RuntimeException e) {
LOG.error("findPromotionsByStoreId failed", e);
throw e;
}
}

public Integer deletePromotionByBatchId(final Integer pBatchId){
if (LOG.isDebugEnabled()) {
LOG.debug("Deleting Promotion by BatchId");
}
try {

String[] lDeleteQueries = new String[] {
"delete cm from fl_promotion_category_membership cm " +
"where cm.promotion_id in " +
"(select p.promotion_id from fl_promotion p where p.batch_id = " + pBatchId + ")",

"delete cm from fl_onestop_promotion_membership cm " +
"where " +
"cm.promotion_id in " +
"(select p.promotion_id from fl_promotion p where p.batch_id = " + pBatchId + ")",

"delete cm from fl_upc_promotion cm " +
"where " +
"cm.promotion_id in " +
"(select p.promotion_id from fl_promotion p where p.batch_id = " + pBatchId + ")",

"delete cm from fl_store_promotion cm " +
"where " +
"cm.promotion_id in " +
"(select p.promotion_id from fl_promotion p where p.batch_id = " + pBatchId + ")",

"delete cm from fl_ingredient_item_promotion cm " +
"where " +
"cm.promotion_id in " +
"(select p.promotion_id from fl_promotion p where p.batch_id = " + pBatchId + ")",

"delete cm from fl_shopping_list_promotion cm " +
"where " +
"cm.promotion_id in " +
"(select p.promotion_id from fl_promotion p where p.batch_id = " + pBatchId + ")",

"delete p from fl_promotion p where p.batch_id = " + pBatchId};


int[] lCounts = m_jdbcTemplate.batchUpdate(lDeleteQueries);

//last result
Integer lResult = lCounts[lCounts.length -1];

//force cache clearing
getHibernateTemplate().getSessionFactory().evict(PromotionCategory.class);
getHibernateTemplate().getSessionFactory().evict(Promotion.class);
getHibernateTemplate().getSessionFactory().evict(IngredientItemPromotion.class);
getHibernateTemplate().getSessionFactory().evict(Special.class);
getHibernateTemplate().getSessionFactory().evict(UpcPromotion.class);

if (LOG.isDebugEnabled()) {
LOG.debug("deleted " + lResult + " promotions");
}

return lResult;
}catch (RuntimeException e) {
LOG.error("deletePromotionByBatchId failed", e);
throw e;
}
}

public Integer deleteSpecials(){
if (LOG.isDebugEnabled()) {
LOG.debug("Deleting specials");
}
try {

String[] lDeleteQueries = new String[] {
"delete cm from fl_promotion_category_membership cm " +
"where cm.promotion_id in " +
"(select p.promotion_id from fl_promotion p where p.special = 1)",

"delete cm from fl_onestop_promotion_membership cm " +
"where " +
"cm.promotion_id in " +
"(select p.promotion_id from fl_promotion p where p.special = 1)",

"delete cm from fl_upc_promotion cm " +
"where " +
"cm.promotion_id in " +
"(select p.promotion_id from fl_promotion p where p.special = 1)",

"delete cm from fl_store_promotion cm " +
"where " +
"cm.promotion_id in " +
"(select p.promotion_id from fl_promotion p where p.special = 1)",

"delete cm from fl_ingredient_item_promotion cm " +
"where " +
"cm.promotion_id in " +
"(select p.promotion_id from fl_promotion p where p.special = 1)",

"delete cm from fl_shopping_list_promotion cm " +
"where " +
"cm.promotion_id in " +
"(select p.promotion_id from fl_promotion p where p.special = 1)",

"delete p from fl_promotion p where p.special = 1"};


int[] lCounts = m_jdbcTemplate.batchUpdate(lDeleteQueries);

//last result
Integer lResult = lCounts[lCounts.length -1];

//force cache clearing
getHibernateTemplate().getSessionFactory().evict(PromotionCategory.class);
getHibernateTemplate().getSessionFactory().evict(Promotion.class);
getHibernateTemplate().getSessionFactory().evict(IngredientItemPromotion.class);
getHibernateTemplate().getSessionFactory().evict(Special.class);
getHibernateTemplate().getSessionFactory().evict(UpcPromotion.class);

if (LOG.isDebugEnabled()) {
LOG.debug("deleted " + lResult + " specials");
}

return lResult;
}catch (RuntimeException e) {
LOG.error("deleteSpecials failed", e);
throw e;
}
}





public List deleteExpiredPromotions(){
if (LOG.isDebugEnabled()) {
LOG.debug("Deleting Promotion by BatchId");
}
try {

Object lQueryResult = getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

List lExternalIds = new ArrayList();

ScrollableResults lPromotions = pSession.createQuery("select this from Promotion this where now() > this.expirationDate")
.scroll();

if(lPromotions.first()) {
do {
Promotion lPromotion = (Promotion)lPromotions.get(0);
lExternalIds.add(lPromotion.getImage());
lPromotion.getIngredientPromotions().clear();
pSession.delete(lPromotion);
} while (lPromotions.next());
}

return lExternalIds;

}
});

List lResult = (List)lQueryResult;

if (LOG.isDebugEnabled()) {
LOG.debug("Deleting promotions["+lResult+"] by BatchId");
}
return lResult;
}catch (RuntimeException e) {
LOG.error("deletePromotionByBatchId failed", e);
throw e;
}
}

public List getPromotionImagesByBatchId(final Integer pBatchId){
if (LOG.isDebugEnabled()) {
LOG.debug("Deleting Promotion by BatchId");
}
try {

List lResult = (List) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {


List lImages = pSession.createQuery("select distinct this.image from Promotion this where this.batchId=:pBatchId")
.setInteger("pBatchId", pBatchId)
.list();
return lImages;

}
});

if (LOG.isDebugEnabled()) {
LOG.debug("Found Promotion Images["+lResult+"] for BatchId:"+pBatchId);
}
return lResult;
}catch (RuntimeException e) {
LOG.error("getPromotionImagesByBatchId failed", e);
throw e;
}
}

public PromotionList findPromotionByDateRangeTypeStatus(final Date pStartDate, final Date pEndDate,final String pType,final boolean pStatus,final String pSortField, final String pSortDirection,final int pOffset,final int pMaxResults,final String pServeType){
try {
Object lResult = getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

String lStatusCriteria = "";
if(!pStatus){
lStatusCriteria = "and promotion.status='ACTIVE' ";
} else {
lStatusCriteria = "and (promotion.status='ACTIVE' or promotion.status='DISABLED')";
}

String lSortField = pSortField;
if(null == pSortField){
lSortField = "promotionId";
}
String lSortDirection = pSortDirection;
if(null == pSortDirection){
lSortDirection = "asc";
}

String lServeType = "and promotion.special = false ";
if(null != pServeType && "specials".equals(pServeType)) {
lServeType = "and promotion.special=true ";
}

String lType="";
if(null != pType && !"".equals(pType)) {
lType = "and promotion.sourceType = '"+pType+"'";
}
//forming queries
String lQueryforCount = "select count(promotion) from Promotion promotion"+
" where ((DATE(promotion.activationDate) between DATE(:pStart) and DATE(:pEnd)) "+
"or (DATE(promotion.expirationDate) between DATE(:pStart) and DATE(:pEnd))) " +
lType +
lStatusCriteria +
lServeType +
"order by promotion."+lSortField+" "+lSortDirection;
String lQueryForOneStops = "select promotion from Promotion promotion"+
" where ((DATE(promotion.activationDate) between DATE(:pStart) and DATE(:pEnd)) "+
"or (DATE(promotion.expirationDate) between DATE(:pStart) and DATE(:pEnd))) " +
lType +
lStatusCriteria +
lServeType +
"group by promotion.promotionId "+
"order by promotion."+lSortField+" "+lSortDirection;

Long count = (Long)pSession.createQuery(lQueryforCount)
.setDate("pStart", pStartDate)
.setDate("pEnd",pEndDate)
.uniqueResult();

List lResult = (List) pSession.createQuery(lQueryForOneStops)
.setFirstResult(pOffset)
.setMaxResults(pMaxResults)
.setDate("pStart", pStartDate)
.setDate("pEnd",pEndDate)
.list();

PromotionList lPromotionResults = new PromotionList();
lPromotionResults.setList(lResult);
lPromotionResults.setMaxResults(pMaxResults);
lPromotionResults.setOffset(pOffset);
lPromotionResults.setTotalCount(count.intValue());
lPromotionResults.setSortField(pSortField);
lPromotionResults.setSortDirection(pSortDirection);

if (LOG.isDebugEnabled()) {
LOG.debug("findPromotionsByDateRange successfull. found " + lResult.size() + " Promotions");
}

return lPromotionResults;
}
});

PromotionList lPromotions = (PromotionList)lResult;
return lPromotions;
} catch (RuntimeException e) {
LOG.error("findPromotionsByDateRange failed", e);
throw e;
}
}

public PromotionList findPromotionByActiveDateTypeStatus(final Date pActiveDate,final String pType,final String pDescription, final boolean pStatus,final boolean pExpired, final String pSortField,final String pSortDirection,final int pOffset,final int pMaxResults, final String pServeType){
try {
Object lResult = getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

String lStatusCriteria = "";
if(!pStatus){
lStatusCriteria = " promotion.status='ACTIVE' ";
} else {
lStatusCriteria = " (promotion.status='ACTIVE' or promotion.status='DISABLED')";
}

String lSortField = pSortField;
if(null == pSortField || "".equals(pSortField)){
lSortField = "promotionId";
}
String lSortDirection = pSortDirection;
if(null == pSortDirection){
lSortDirection = "asc";
}

String lServeType = " promotion.special = false ";
if(null != pServeType && "specials".equals(pServeType)) {
lServeType = " promotion.special=true ";
}

String lDateCriteria = "";
if (null != pActiveDate) {
lDateCriteria = "((DATE(:pActiveDate) between DATE(promotion.activationDate) and DATE(promotion.expirationDate)))";
}

String lExpiryCriteria = "";
if(!pExpired) {
lExpiryCriteria = "((DATE(now()) <= DATE(promotion.expirationDate)))";
}

String lType="";
String lJoinCriteria = "";
if(null != pType && !"".equals(pType)) {
Integer lBrandId = Integer.valueOf(pType);
lJoinCriteria = "left join promotion.brand brand";
lType = " brand.id = "+lBrandId+"";
}

String lDescCriteria = "";
if(null != pDescription && !"".equals(pDescription)) {
String lDescription = pDescription;
if(pDescription.contains("'")){
lDescription = pDescription.replaceAll("'", "_");
}
lDescCriteria = " promotion.description like '%"+lDescription+"%'";
}

//forming queries
String lQueryforCount = "select count(promotion) from Promotion promotion "+ lJoinCriteria +" where "+
lDateCriteria + (!"".equals(lDateCriteria) ? " and " : "") +
lType + (!"".equals(lType) ? " and " : "") +
lStatusCriteria + (!"".equals(lStatusCriteria) ? " and " : "") +
lExpiryCriteria + (!"".equals(lExpiryCriteria) ? " and " : "") +
lDescCriteria + (!"".equals(lDescCriteria) ? " and " : "") +
lServeType +
"order by promotion."+lSortField+" "+lSortDirection;

String lQueryForOneStops = "select promotion from Promotion promotion "+ lJoinCriteria +" where "+
lDateCriteria + (!"".equals(lDateCriteria) ? " and " : "") +
lType + (!"".equals(lType) ? " and " : "") +
lStatusCriteria + (!"".equals(lStatusCriteria) ? " and " : "") +
lExpiryCriteria + (!"".equals(lExpiryCriteria) ? " and " : "") +
lDescCriteria + (!"".equals(lDescCriteria) ? " and " : "") +
lServeType +
"group by promotion.promotionId "+
"order by promotion."+lSortField+" "+lSortDirection;

//execute the query for the entity count
Query lCountQuery = pSession.createQuery(lQueryforCount);
if (!"".equals(lDateCriteria)) {
lCountQuery.setDate("pActiveDate", pActiveDate);
}
Long count = (Long)lCountQuery.uniqueResult();

//execute the query for the entities
Query lResultsQuery = pSession.createQuery(lQueryForOneStops)
.setFirstResult(pOffset)
.setMaxResults(pMaxResults);

if (!"".equals(lDateCriteria)) {
lResultsQuery.setDate("pActiveDate", pActiveDate);
}
List lResult = (List)lResultsQuery.list();


//transform the results in the ListResult
PromotionList lPromotionResults = new PromotionList();
lPromotionResults.setList(lResult);
lPromotionResults.setMaxResults(pMaxResults);
lPromotionResults.setOffset(pOffset);
lPromotionResults.setTotalCount(count.intValue());
lPromotionResults.setSortField(pSortField);
lPromotionResults.setSortDirection(pSortDirection);

if (LOG.isDebugEnabled()) {
LOG.debug("findPromotionsByDateRange successfull. found " + lResult.size() + " Promotions");
}

return lPromotionResults;
}
});

PromotionList lPromotions = (PromotionList)lResult;
return lPromotions;
} catch (RuntimeException e) {
LOG.error("findPromotionByActiveDateTypeStatus failed", e);
throw e;
}
}

public PromotionList findMostViewedCoupons(final Integer pOffSet,final Integer pMaxResults){
PromotionList lList = null;
try{
lList = (PromotionList) getHibernateTemplate().execute(new HibernateCallback(){
public Object doInHibernate(Session pSession)throws HibernateException, SQLException{

String lIdsQuery = "select ref_id as refId from fl_event where category='COUPON' and event='View' and date >= DATE_SUB(now(),INTERVAL 7 DAY) " +
"group by ref_id order by count(ref_id) desc";

List lIds = pSession.createSQLQuery(lIdsQuery).addScalar("refId", Hibernate.INTEGER)
.list();

String lIdsSepByComma = lIds.toString();
lIdsSepByComma = lIdsSepByComma.substring(1,lIdsSepByComma.length()-1);

List lList = null;
if(null != lIds && !lIds.isEmpty()){

String lStringQuery = "select pro.promotion_id as id," +
" pro.code as code," +
" pro.title as title," +
" pro.description as description," +
" pro.activation_date as activationDate," +
" pro.expiration_date as expirationDate," +
" pro.image as image," +
" pro.source_type as sourceType," +
" pro.special as special" +
" from fl_promotion as pro where promotion_id in" +
"("+lIdsSepByComma+") order by field(promotion_id, "+lIdsSepByComma+") ";

Query lQuery = pSession.createSQLQuery(lStringQuery)
.addScalar("id", Hibernate.INTEGER)
.addScalar("code", Hibernate.STRING)
.addScalar("title", Hibernate.STRING)
.addScalar("description", Hibernate.STRING)
.addScalar("activationDate",Hibernate.DATE)
.addScalar("expirationDate",Hibernate.DATE)
.addScalar("image",Hibernate.STRING)
.addScalar("sourceType", Hibernate.STRING)
.addScalar("special", Hibernate.BOOLEAN);

lList = lQuery.setFirstResult(pOffSet)
.setFirstResult(pOffSet)
.setMaxResults(pMaxResults)
.setResultTransformer(Transformers.aliasToBean(Coupon.class))
.list();
}

PromotionList lTmpList = new PromotionList();
lTmpList.setMaxResults(pMaxResults);
lTmpList.setOffset(pOffSet);
lTmpList.setList(lList);
return lTmpList;
}
});
return lList;
}catch(RuntimeException e){
LOG.error("findMostViewedCoupons failed, "+e);
throw e;
}
}


/**
* Get the promotion based on imageName.
*/
public Promotion findByImageName(final String pPromotionImage) {

if (LOG.isDebugEnabled()) {
LOG.debug("finding Promotion by ImageName ["+(pPromotionImage == null ? "NA" : pPromotionImage)+"]");
}
try {
List lPromotions = (List) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {
return pSession.createQuery(
"select this from Special as this where this.image = :pImage" +
" and (this.status='ACTIVE' or this.status='PENDING')")
.setString("pImage", pPromotionImage)
.list();
}
});

if (null != lPromotions) {
if (LOG.isDebugEnabled()) {
LOG.debug("finding Promotions by ImageName successful. Numbers of finding ["+lPromotions.size()+"]");
}
}
if(lPromotions == null || lPromotions.isEmpty()) {
return null;
} else {
return lPromotions.get(0);
}

} catch (RuntimeException lRe) {
LOG.error("Error finding Promotions by ImageName", lRe);
throw lRe;
}

}

/**
* Find the number of promotion map with given store
* @return int
*/
public int findStorePromotionByIds(final Integer pStoreId, final Integer pPromotionId){

if (LOG.isDebugEnabled()) {
LOG.debug("finding StorePromotion by storeId and promotionId");
}
Integer count = new Integer(0);
try {
count = (Integer) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

//forming queries
String lQueryforCount = "select count(1) as count from fl_store_promotion this where" +
" this.store_id = "+pStoreId+" and this.promotion_id = "+pPromotionId;

//execute the query for the entity count
Query lCountQuery = pSession.createSQLQuery(lQueryforCount).addScalar("count", Hibernate.INTEGER);

Integer count = (Integer)lCountQuery.uniqueResult();


if (LOG.isDebugEnabled()) {
LOG.debug("Successfully found StorePromotion by Ids. Count ["+(count == null ? 0 : count.intValue())+"]");
}
if(count == null) {
return 0;
} else {
return count.intValue();
}
}
});

return count.intValue();
} catch (RuntimeException e) {
LOG.error("findStorePromotionByIds failed", e);
throw e;
}
}

public List findByUpcId(final Long pUpcItemId, final Integer pStoreId) {
List lPromotions = null;
try {
lPromotions = (List) getHibernateTemplate().execute(new HibernateCallback(){
public Object doInHibernate(Session pSession) throws SQLException, HibernateException {
//current date
Date date = new Date();

if(null == pStoreId) {
String lQuery = "select promo from IngredientItemPromotion ingItemPromo left join ingItemPromo.promotion promo left join promo.stores as store , UpcIngredient upcIng " +
" where upcIng.ingredientItem = ingItemPromo.ingredientItem" +
" and upcIng.upc.code = :code" +
" and promo.activationDate <= :actDate" +
" and promo.expirationDate >= :expDate" +
" and promo.status = 'ACTIVE'" +
" and store.storeId IS NULL" +
" and promo.special is not true";
List lPromos = pSession.createQuery(lQuery).setLong("code", pUpcItemId)
.setDate("actDate", date)
.setDate("expDate", date)
.list();
return lPromos;
} else {

String lQuery = "select promo from IngredientItemPromotion ingItemPromo left join ingItemPromo.promotion promo left join promo.stores as store , UpcIngredient upcIng " +
" where upcIng.ingredientItem = ingItemPromo.ingredientItem" +
" and upcIng.upc.code = :code" +
" and promo.activationDate <= :actDate" +
" and promo.expirationDate >= :expDate" +
" and promo.status = 'ACTIVE'" +
" and ((promo.special is true and store.unitId = :pStoreId) or "+
" (promo.special is not true and (store.unitId = :pStoreId OR store.storeId IS NULL))) ";

List lPromos = pSession.createQuery(lQuery).setLong("code", pUpcItemId)
.setDate("actDate", date)
.setDate("expDate", date)
.setInteger("pStoreId", pStoreId)
.list();
return lPromos;
}


}
});
return lPromotions;
}catch(RuntimeException e) {
LOG.error("findByUpcId failed", e);
throw e;
}
}

public List findByBrandId(final Integer pBrandId) {
if (LOG.isDebugEnabled()) {
LOG.debug("finding promotions with brand id");
}
List lPromotions = null;

try {
lPromotions = (List) getHibernateTemplate().execute(new HibernateCallback(){
public Object doInHibernate(Session pSession) throws SQLException, HibernateException {
List lPromotions = pSession.createQuery("from Coupon coupon where coupon.brand.id = :pBrandId ").setInteger("pBrandId",pBrandId).list();

if(null == lPromotions || lPromotions.isEmpty()){
return null;
}
return lPromotions;
}
});
} catch(RuntimeException lRe){
LOG.error("finding promotions with brand id failed.", lRe);
throw lRe;
}

return lPromotions;
}

public void bulkUpdateByBrand(final Integer pBrandId){

if (LOG.isDebugEnabled()) {
LOG.debug("Update promotions with brand id");
}

Integer rowCount=null;
try{
rowCount =(Integer)getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession) throws HibernateException, SQLException {

String hql = "update Promotion promotion set promotion.brand = :brand where promotion.brand.id = :brandId";
Query lQuery = pSession.createQuery(hql);
lQuery.setString("brand",null);
lQuery.setInteger("brandId",pBrandId);
return lQuery.executeUpdate();
}
});
}catch(RuntimeException e) {
LOG.error("fail to update the promotions :", e);
throw e;
}
if (LOG.isDebugEnabled()) {
LOG.debug("updated Promotions :"+rowCount);
}
}

public Collection findNonTargetedCouponsIncCoupons() {
return findByNamedQuery("promotion.non_targeted_coupons_inc_coupons", null);
}

public Collection findTargetedCouponsIncCoupons(String zipCode) {
return findByNamedQueryAndNamedParams("promotion.targeted_coupons_inc_coupons", new String[] { "zipCode" }, new String[] { zipCode });
}

public void setJdbcTemplate(JdbcTemplate pJdbcTemplate) {
m_jdbcTemplate = pJdbcTemplate;
}

public PromotionList findByStoreRecipeCategoryIngredientIds(final List storeIds,final List recipeIds,final List categoryIds,
final List ingredientIds,final String type,final Integer offset,final Integer maxResults) {
PromotionList promotionList = null;
try {
promotionList = (PromotionList) getHibernateTemplate().execute(new HibernateCallback(){

public Object doInHibernate(Session session) throws HibernateException, SQLException {
PromotionList list = new PromotionList();

StringBuffer selectForCount = new StringBuffer("select count(distinct promotion) ");
StringBuffer select = new StringBuffer("select promotion ");
StringBuffer from = new StringBuffer(" from ");
StringBuffer where = new StringBuffer(" where ");
boolean recipeIdsExist = false, storeIdsExist = false, categoryIdsExist = false, ingredientIdsExist = false;

String fromIncaseRecipeIdsIngredientIds = " Recipe recipe inner join recipe.recipeIngredients recipeIngredient join recipeIngredient.ingredient ingredient" +
" join ingredient.ingredientItem ingredientItem inner join ingredientItem.ingredientItemPromotions ingredientItemPromotion" +
" join ingredientItemPromotion.promotion promotion";

//check if recipeId exist.
if(recipeIds != null && !recipeIds.isEmpty()) {
from.append(fromIncaseRecipeIdsIngredientIds);
String tmpWhere = " recipe.recipeId in (:recipeIds) and recipe.status = 'ACTIVE' ";
select = new StringBuffer("select promotion, ingredient.ingredientId ");
where.append(tmpWhere);
recipeIdsExist = true;
}

//check if ingredientIds exist
if(ingredientIds != null && !ingredientIds.isEmpty()) {
String tmpWhere = checkifcontains(where.toString(), "and", " and ");
tmpWhere += " ingredient.ingredientId in (:ingredientIds) ";
if(!recipeIdsExist) {
from.append(fromIncaseRecipeIdsIngredientIds);
tmpWhere += " and recipe.status = 'ACTIVE' ";
}
select = new StringBuffer("select promotion, ingredient.ingredientId ");
where.append(tmpWhere);
ingredientIdsExist = true;
}

//check if storeId exist
if(storeIds != null && !storeIds.isEmpty()) {
String tmpWhere = " ";
if(ingredientIdsExist || recipeIdsExist) {
String tmpFrom = " left join promotion.stores store";
from.append(tmpFrom);
} else {
select = new StringBuffer("select promotion ");
String tmpFrom = " Promotion promotion inner join promotion.stores store";
from.append(tmpFrom);
}
where.append(tmpWhere);
storeIdsExist = true;
}

//check if categoryIds exist
if(categoryIds != null && !categoryIds.isEmpty()){
String tmpWhere = checkifcontains(where.toString(), "and", " and ");
where.trimToSize();
if(where.indexOf("where")>-1 && where.toString().contains("and") && !where.toString().endsWith("and")){
tmpWhere = " and ";
}
tmpWhere += " promotionCategory.promotionCategoryId in (:categoryIds) and promotionCategory.active = 1";
if(storeIdsExist || ingredientIdsExist) {
String tmpFrom = " inner join promotion.promotionCategory promotionCategory";
from.append(tmpFrom);
} else {
select = new StringBuffer("select promotion ");
String tmpFrom = " Promotion promotion inner join promotion.promotionCategory promotionCategory";
from.append(tmpFrom);
}
where.append(tmpWhere);
categoryIdsExist = true;
}

if(!from.toString().contains("promotion")) {
String tmpFrom = " Promotion promotion ";
from.append(tmpFrom);
}

String tmpWhere = checkifcontains(where.toString(), "and", " and ");;
if(tmpWhere.equals("")) {
tmpWhere = " and ";
}
tmpWhere += " (now() between date(promotion.activationDate) and date(promotion.expirationDate)) ";
tmpWhere += " and promotion.status = 'ACTIVE'";
if(type.equals("SPECIAL")) {
tmpWhere += " and promotion.special=1 ";
if(from.indexOf("store")>-1){
tmpWhere += " and store.unitId in (:storeIds) ";
}
} else if (type.equals("COUPON")) {
tmpWhere += " and promotion.special=0 ";
if(from.indexOf("store")>-1){
tmpWhere += " and store.storeId IS NULL ";
}
} else {
if(from.indexOf("store")>-1){
tmpWhere += " and ((promotion.special is true and store.unitId in (:storeIds)) or " +
" (promotion.special is not true and (store.unitId in (:storeIds) or store.storeId is NULL)))";
}
}
where.append(tmpWhere);

String stringCountQuery = selectForCount.append(from).append(where).toString();
String stringMainQuery = select.append(from).append(where).toString();

Query queryForCount = session.createQuery(stringCountQuery);
updateQueryWithParameterList(queryForCount, "recipeIds", recipeIds, recipeIdsExist);
updateQueryWithParameterList(queryForCount, "categoryIds", categoryIds, categoryIdsExist);
if(stringCountQuery.indexOf(":storeIds")>-1){
updateQueryWithParameterList(queryForCount, "storeIds", storeIds, storeIdsExist);
}
updateQueryWithParameterList(queryForCount, "ingredientIds", ingredientIds, ingredientIdsExist);

//getting counts
Long promotionCount = (Long)queryForCount.uniqueResult();

Query query = session.createQuery(stringMainQuery);
updateQueryWithParameterList(query, "recipeIds", recipeIds, recipeIdsExist);
updateQueryWithParameterList(query, "categoryIds", categoryIds, categoryIdsExist);
if(stringMainQuery.indexOf(":storeIds")>-1){
updateQueryWithParameterList(query, "storeIds", storeIds, storeIdsExist);
}
updateQueryWithParameterList(query, "ingredientIds", ingredientIds, ingredientIdsExist);

//getting list
List promotionList = null;
if(select.indexOf("ingredientId")>-1){
List objects = query.setFirstResult(offset.intValue())
.setMaxResults(maxResults.intValue())
.list();

if(objects !=null && !objects.isEmpty()) {
promotionList = new ArrayList();
Map map = new HashMap();
for(Object[] object: objects) {
Promotion promotion = (Promotion)object[0];
Integer ingredientId = (Integer)object[1];
if(map.containsKey(promotion.getPromotionId())){
promotion = map.get(promotion.getPromotionId());
promotion.addIngredientId(ingredientId);
continue;
}
promotion.addIngredientId(ingredientId);
promotionList.add(promotion);
map.put(promotion.getPromotionId(), promotion);
}
}

} else {
promotionList = query.setFirstResult(offset.intValue())
.setMaxResults(maxResults.intValue())
.list();
}


list.setOffset(offset);
list.setMaxResults(maxResults);
list.setTotalCount(promotionCount.intValue());

if(promotionList !=null && !promotionList.isEmpty()) {
list.setList(promotionList);
}

return list;
}

private String checkifcontains(String originalString, String searchString, String returnString){
if(originalString.contains(searchString)) {
return returnString;
}
return "";
}

private void updateQueryWithParameterList(Query hibernateQuery, String parameterName, List list, boolean flag) {
if(flag) {
hibernateQuery.setParameterList(parameterName, list);
}
}
});

if(LOG.isDebugEnabled()) {
LOG.debug("success");
}

return promotionList;
} catch (RuntimeException ex) {
LOG.error("findByStoreRecipeCategoryIngredientIds failed", ex);
throw ex;
}
}
}

imp queries PromotionCategorydao

package com.meijer.foodlab.dao.spring;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.meijer.foodlab.dao.IPromotionCategoryDao;
import com.meijer.foodlab.model.domain.PromotionCategory;
import com.meijer.foodlab.model.domain.PromotionCategoryList;

/**
* @author dbusch
*/
public class PromotionCategoryDaoImpl extends BaseSpringDao implements IPromotionCategoryDao {
private static final Logger LOG = Logger.getLogger(PromotionCategoryDaoImpl.class);

protected JdbcTemplate m_jdbcTemplate;

public PromotionCategoryDaoImpl() {
super(PromotionCategory.class, "PromotionCategory");
}

public PromotionCategoryDaoImpl(Class pClazz, String pEntityName) {
super(pClazz, pEntityName);
}

public PromotionCategory findByRefName(final String pName) {
if (LOG.isDebugEnabled()) {
LOG.debug("finding PromotionCategory by refName");
}
try {
PromotionCategory lCategory = (PromotionCategory)getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {
return pSession.createQuery(
"select this from PromotionCategory as this join fetch this.promotions where this.refName = :refName")
.setString("refName", pName)
.uniqueResult();
}
});

if (null != lCategory) {
if (LOG.isDebugEnabled()) {
LOG.debug("finding PromotionCategory by refName successful");
}
}
return lCategory;

} catch (RuntimeException lRe) {
LOG.error("finding PromotionCategory by refName failed", lRe);
throw lRe;
}

}

public List getActivePromotionsForStore(final Integer pStoreId) {
if (LOG.isDebugEnabled()) {
LOG.debug("finding active promotions by store");
}
try {
List lResult = (List) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {
List lCounts = pSession.createQuery(
"select cat.name, count(distinct promo.promotionId) as qty, cat.image " +
" from PromotionCategory cat" +
" inner join cat.promotions as promo " +
" inner join promo.stores store" +
" where cat.active = true " +
" and promo.special = true" +
" and promo.status = 'ACTIVE'" +
" and store.unitId = :pStoreId" +
" and store.status = 'ACTIVE'" +
" and ( now() between date(promo.activationDate) and date(promo.expirationDate) )" +
" group by cat.name " +
" having count(distinct promo.promotionId) > 0 " +
" order by cat.sortIdx")
.setInteger("pStoreId", pStoreId)
.list();
return lCounts;

}
});
if (null != lResult) {
if (LOG.isDebugEnabled()) {
LOG.debug("finding active promotions by store successful, found: " + lResult.size());
}
}

return lResult;

} catch (RuntimeException lRe) {
LOG.error("finding active promotions by store failed", lRe);
throw lRe;
}
}

/**
* Find the number of promotion map with the category.
* @return int
*/
public int findByPromotionCategoryByIds(final Integer pPromotionId, final Integer pPromotionCategoryId) {

if (LOG.isDebugEnabled()) {
LOG.debug("finding PromotionCategoryByIds for Promotion Id ["+pPromotionId+"] And PromotionCategoryId ["+pPromotionCategoryId+"]");
}
Integer count = new Integer(0);
try {
count = (Integer) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

//forming queries
String lQueryforCount = "select count(1) as count from fl_promotion_category_membership this where" +
" this.promotion_category_id = "+pPromotionCategoryId+" and this.promotion_id = "+pPromotionId;

//execute the query for the entity count
Query lCountQuery = pSession.createSQLQuery(lQueryforCount).addScalar("count", Hibernate.INTEGER);

Integer count = (Integer)lCountQuery.uniqueResult();


if (LOG.isDebugEnabled()) {
LOG.debug("findPromotionCategoryByIds successful. Found count ["+(count == null ? 0 : count.intValue())+"]");
}
if(count == null) {
return 0;
} else {
return count.intValue();
}
}
});

return count.intValue();
} catch (RuntimeException e) {
LOG.error("findPromotionCategoryByIds failed", e);
throw e;
}
}

/**
* Find PromotionCategory based on 'name' field value.
*/
public PromotionCategory findByName(final String pName) {
if (LOG.isDebugEnabled()) {
LOG.debug("finding PromotionCategory by Name ["+(pName == null ? "NA" : pName)+"]");
}
try {
PromotionCategory lCategory = (PromotionCategory)getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {
List lList = pSession.createQuery(
"select this from PromotionCategory as this where this.name = :pName")
.setString("pName", pName)
.setMaxResults(1)
.list();

if(lList == null || lList.isEmpty()) {
return null;
}
return lList.get(0);
}
});

if (LOG.isDebugEnabled()) {
LOG.debug("finding PromotionCategory by Name successful");
}
return lCategory;

} catch (RuntimeException lRe) {
LOG.error("finding PromotionCategory by Name failed", lRe);
throw lRe;
}

}

public PromotionCategoryList searchCategory(final String pName, final boolean pActive, final int pOffset, final int pMaxResults) {
try {
Object lResult = getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

String lStatusCriteria = "";
if(!pActive){
lStatusCriteria = " where category.active= 1 ";
} else {
lStatusCriteria = " where (category.active=0 OR category.active=1)";
}

String lNameCriteria = "", lName = pName;
if(pName != null) {
if(pName.indexOf("'")!=-1){
lName = pName.replaceAll("'", "_");
}
lNameCriteria = "category.name like '%"+ lName +"%'";
}

String lChainCriteria = "";

//forming queries
String lQueryforCount = "select count(category) from PromotionCategory category" +
lStatusCriteria + (lNameCriteria == "" ? "" : " and ") +
lNameCriteria + (lChainCriteria == "" ? "" : " and ") +
lChainCriteria + " order by category.promotionCategoryId asc" ;

String lQueryForCategory = "select category from PromotionCategory category" +
lStatusCriteria + (lNameCriteria == "" ? "" : " and ") +
lNameCriteria + (lChainCriteria == "" ? "" : " and ") +
lChainCriteria + " order by category.sortIdx asc" ;

Long count = (Long)pSession.createQuery(lQueryforCount).uniqueResult();


List lResult = pSession.createQuery(lQueryForCategory)
.setFirstResult(pOffset)
.setMaxResults(pMaxResults)
.list();

PromotionCategoryList lResults = new PromotionCategoryList();
lResults.setCategories(lResult);
lResults.setMaxResults(pMaxResults);
lResults.setOffset(pOffset);
lResults.setTotalCount(count.intValue());

if (LOG.isDebugEnabled()) {
LOG.debug("searchCategory successfull. found " + lResult.size() + " PromotionCategory");
}

return lResults;
}
});

PromotionCategoryList lPromotionCategories = (PromotionCategoryList)lResult;
return lPromotionCategories;

} catch (RuntimeException e) {
LOG.error("searchCategory failed", e);
throw e;
}
}

/**
* Delete the PromotionCategory and associated membership of the category
* @param pCategoryId
* @return
*/
public Integer deleteCategory(final Integer pCategoryId) {
if (LOG.isDebugEnabled()) {
LOG.debug("Deleting PromotionCategory by CategoryId");
}
try {

String[] lDeleteQueries = new String[] {
"delete cm from fl_promotion_category_membership cm " +
"where cm.promotion_category_id = " +pCategoryId ,

"delete p from fl_promotion_category p where p.promotion_category_id = " + pCategoryId};


int[] lCounts = m_jdbcTemplate.batchUpdate(lDeleteQueries);

//last result
Integer lResult = lCounts[lCounts.length -1];

//force cache clearing
getHibernateTemplate().getSessionFactory().evict(PromotionCategory.class);

if (LOG.isDebugEnabled()) {
LOG.debug("deleted " + lResult + " promotioncategory");
}

return lResult;
}catch (RuntimeException e) {
LOG.error("deleteCategory failed", e);
throw e;
}
}

public List getActiveCategories() {
return getHibernateTemplate().find("from PromotionCategory where active is true order by sortIdx asc");
}

@SuppressWarnings("unchecked")
public List findCategoriesByName(final String pName) {
List lResult = new ArrayList();
try {
lResult = (List) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

String lNameCriteria = "";
if(pName != null) {
lNameCriteria = " category.name = :pName ";
}

//forming queries
String lQueryForCategory =
"select category from PromotionCategory category " +
"where" + lNameCriteria +
" order by category.promotionCategoryId asc";

Query lQuery = pSession.createQuery(lQueryForCategory);
if(pName != null){
lQuery.setString("pName", pName);
}

List lResult = (List) lQuery.list();

if (LOG.isDebugEnabled()) {
LOG.debug("findCategoriesByNameChain successfull. found " + lResult.size() + " PromotionCategory");
}

return lResult;
}
});

return lResult;

} catch (RuntimeException e) {
LOG.error("findCategoriesByNameChain failed", e);
throw e;
}
}

public int[] updatePromotionCategories(Integer pPromotionId,List pCategoryIds){

try{
String lQueryString="delete cm from fl_promotion_category_membership cm where cm.promotion_id ="+pPromotionId;
m_jdbcTemplate.update(lQueryString);

String[] lDeleteQueries = new String[pCategoryIds.size()];

for(int i=0;i lDeleteQueries[i]= "insert into fl_promotion_category_membership values ("+ pCategoryIds.get(i)+","+pPromotionId+")";
}

int[] upadtes=m_jdbcTemplate.batchUpdate(lDeleteQueries);

return upadtes;
}catch(RuntimeException ex){
LOG.error("error while updating the Promotion Categories :"+ex.getMessage());
throw ex;
}
}

public List getPromotionCategoriesWithPromotionId(final Integer pPomotionIds){

try {
List lResult = (List) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

//forming queries
String lQueryForCategory ="select promotion_category_id from fl_promotion_category_membership where promotion_id ="+pPomotionIds;

Query lQuery = pSession.createSQLQuery(lQueryForCategory);
return lQuery.list();
}
});
return lResult;
}catch(Exception ex){
LOG.error("error while getting the Promotion CategoryIds :"+ex.getMessage());
}
return null;
}

public List getActivePromotionsForStoreIds(final List storeIds,final String promotionType){
if (LOG.isDebugEnabled()) {
LOG.debug("finding active promotions by store");
}
try {
List lResult = (List) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session pSession)
throws HibernateException, SQLException {

String lSelectClause="select cat, count(distinct promo.promotionId) as qty, cat.image " +
" from PromotionCategory cat" +
" inner join cat.promotions as promo " +
" inner join promo.stores store" ;

String lWhereClause=" where cat.active = true " +
" and promo.status = 'ACTIVE'" +
" and store.status = 'ACTIVE'" +
" and ( now() between date(promo.activationDate) and date(promo.expirationDate))" +
" and store.unitId in (:ids) " ;

if (null!=promotionType){
if ("SPECIAL".equals(promotionType))
lWhereClause+=" and promo.special = true";
else if("COUPON".equals(promotionType))
lWhereClause+=" and promo.special = false";
}

lWhereClause+=" group by cat.name " +
" having count(distinct promo.promotionId) > 0 " +
" order by cat.sortIdx";

List lCounts = pSession.createQuery(lSelectClause+lWhereClause)
.setParameterList("ids", storeIds)
.list();
return lCounts;

}
});

if (LOG.isDebugEnabled()) {
LOG.debug("finding active promotions by store ids successful, found: " + lResult.size());
}
return lResult;
} catch (RuntimeException lRe) {
LOG.error("finding active promotions by store failed", lRe);
throw lRe;
}
}

public JdbcTemplate getJdbcTemplate() {
return this.m_jdbcTemplate;
}
public void setJdbcTemplate(JdbcTemplate pJdbcTemplate) {
this.m_jdbcTemplate = pJdbcTemplate;
}


}