ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "M Muthukumaran" <MMu...@yodlee.com>
Subject RE: Search screen : HOW TO
Date Tue, 12 Sep 2006 11:45:39 GMT

For furthur clarification, What happens when we hardcode the
JOB_STATUS_C in xml file like 

<.........."jow.JOB_STATUS_C = 0 or #jobStatC:INTEGER# = -1".....>

Also why not you can make use of <dynamic> tag's like

<dynamic>
	<isNotNull property="jobStatC">
		........
		........
	</isNotNull>
</dynamic>

I don't know the exact syntax, please do check some documentation if it
is useful.

Thanks
Muthu



-----Original Message-----
From: Paul Carr [mailto:Paul.Carr@express-gifts.co.uk] 
Sent: Tuesday, September 12, 2006 5:03 PM
To: user-java@ibatis.apache.org
Subject: Search screen : HOW TO


HiAll,
 	 I'm developing a JSP screen to allow users to search a database
of orders on various criteria. There are several text boxes on the
screen where you can enter things like

Item id
Barcode id
Date
Process id
Etc

And click search....  the backend then runs this SQL query:-

  <select id="screenQuery"
parameterClass="egl.valueobjects.CustomObject"
resultMap="customQueryResult">
select
PMP.MANUFACTURE_PROCESS_ID,MP.MANUFACTURE_PROCESS_DESC_T,P.ITEM_ID
,P.ITEM_DESCRIPTION_T,MR.RETAILER_GROUP_ID,MRL.ORDER_Q,JOW.JOB_K,
JOW.JOB_STATUS_C,
JT.TASK_K,MRL.ORDER_DUE_D,MRPL.PERS_LINE_ID,MRPL.PERS_T,MRL.RETAILER_GRO
UP_SEQUENCE_ID
from oper.JOB_OF_WORK jow, oper.PRODUCT_MANUFACTURE_PROCESS
pmp,OPER.PRODUCT P, oper.Manufacture_Process mp, oper.JOB_TASK
jt,oper.MANUFACTURE_REQUEST_PERS_LINE mrpl,
oper.MANUFACTURE_REQUEST_LINE mrl, oper.MANUFACTURE_REQUEST mr where
(mr.RETAILER_GROUP_ID like #groupId:VARCHAR# or #groupId:VARCHAR# =
'noentrynoentrynoentrynoentrynoentry') and (mrl.BARCODE_T LIKE
#barcode:VARCHAR# or #barcode:VARCHAR# =
'noentrynoentrynoentrynoentrynoentry') and (pmp.MANUFACTURE_PROCESS_ID =
#processId:INTEGER# or #processId:INTEGER# = -1) and (jow.JOB_K =
#jobId:INTEGER# or #jobId:INTEGER# = -1) and (jow.JOB_STATUS_C =
#jobStatC:INTEGER# or #jobStatC:INTEGER# = -1) and (mrl.ORDER_DUE_D =
#dueDate:DATE# or #dueDate:DATE# =
date('1970-01-01')) andjt.MANUFACTURE_REQUEST_K =
mr.MANUFACTURE_REQUEST_K and mr.MANUFACTURE_REQUEST_K =
mrl.MANUFACTURE_REQUEST_K and mrl.MANUFACTURE_REQUEST_LINE_K =
jt.MANUFACTURE_REQUEST_LINE_K and mrl.MANUFACTURE_REQUEST_LINE_K =
mrpl.MANUFACTURE_REQUEST_LINE_K and MRL.ITEM_ID = P.ITEM_ID AND
MP.MANUFACTURE_PROCESS_ID = PMP.MANUFACTURE_PROCESS_ID AND
pmp.MANUFACTURE_PROCESS_SEQ_ID = jow.MANUFACTURE_PROCESS_SEQ_ID and
pmp.ITEM_ID = p.ITEM_ID and jow.ITEM_ID = pmp.ITEM_ID and jow.JOB_K =
jt.JOB_K and jt.MANUFACTURE_REQUEST_LINE_K =
mrl.MANUFACTURE_REQUEST_LINE_K and mrl.ROW_ACTIVE_F='Y'
order by pmp.MANUFACTURE_PROCESS_ID,pmp.ITEM_ID,jow.JOB_K
desc,mrl.RETAILER_GROUP_SEQUENCE_ID
</select>

<resultMap class="egl.valueobjects.CustomPersData"
id="customQueryResult">
    <result column="MANUFACTURE_PROCESS_ID" jdbcType="INTEGER"
property="manufactureProcessId"/>
    <result column="MANUFACTURE_PROCESS_DESC_T" jdbcType="VARCHAR"
property="manufactureProcessDescT"/>
    <result column="ITEM_ID" jdbcType="DECIMAL" property="itemId"/>
    <result column="ITEM_DESCRIPTION_T" jdbcType="VARCHAR"
property="itemDescriptionT"/>
    <result column="RETAILER_GROUP_ID" jdbcType="VARCHAR"
property="retailerGroupId"/>
    <result column="ORDER_Q" jdbcType="INTEGER" property="orderQ"/>
    <result column="JOB_K" jdbcType="INTEGER" property="jobK"/>
    <result column="JOB_STATUS_C" jdbcType="INTEGER"
property="jobStatusC"/>
    <result column="TASK_K" jdbcType="INTEGER" property="taskK"/>
    <result column="ORDER_DUE_D" jdbcType="DATE" property="orderDueD"/>
    <result column="PERS_LINE_ID" jdbcType="INTEGER"
property="persLineId"/>
    <result column="PERS_T" jdbcType="VARCHAR" property="persT"/>
    <result column="RETAILER_GROUP_SEQUENCE_ID" jdbcType="INTEGER"
property="retailerGroupSeqId"/>
  </resultMap>
 

The CustomObject initialises all numbers fields to -1 and all strings to
'noentrynoentrynoentrynoentrynoentry'(so if you don't enter any data in
the screen fields it still works ok)

When I run this query with only JOB_STATUS_C set to '0' it takes
absolutely ages (22 mins!)

When I run the query through TOAD with hardcoded parameters it takes 1
second

I then put the hardcoded version that took 1 second into my sqlmap, and
sure enough it only took 1 second.

I've attached the source for CustomObject... it appears to be a problem
with converting the bind params, so I have two questions.

(1) Why does this way take so long.
(2) This seems a horrible way to implement what I'm trying to do,
('noentrynoentry') is there a better way ?





CustomObject:
package egl.valueobjects;

import java.util.Date;
import java.util.Vector;

/**
 * An object for custom SQL queries
 * @author paul.carr
 *
 */
public class CustomObject extends ValueObject {

	private Integer noEntryInteger = new Integer(-1);
	private String noEntryString =
"noentrynoentrynoentrynoentrynoentry";
	private String retailer = getNoEntryString();
	private Date noEntryDate = new Date(1000);
	private StringBuffer jobList = new StringBuffer();
	private Integer itemId = getNoEntryInteger();
	private Integer processId = getNoEntryInteger();
	private Integer jobId = getNoEntryInteger();
	private Date dueDate = getNoEntryDate();
	private String groupId = getNoEntryString();
	private String barcode = getNoEntryString();
	private Integer jobStatC = getNoEntryInteger();

	/**
	 * setter
	 * @param itemId
	 */
	public void setItemId(Integer itemId)
	{
		this.itemId = itemId;
	}

	/**
	 * getter
	 * @return item id
	 */
	public Integer getItemId()
	{
		return itemId;
	}

	/**
	 * setter
	 * @param processId
	 */
	public void setProcessId(Integer processId)
	{
		this.processId = processId;
	}

	/**
	 * getter
	 * @return process id
	 */
	public Integer getProcessId()
	{
		return processId;
	}

	/**
	 * setter
	 * @param jobId
	 */
	public void setJobId(Integer jobId)
	{
		this.jobId = jobId;
	}

	/**
	 * getter
	 * @return job id
	 */
	public Integer getJobId()
	{
		return jobId;
	}

	/**
	 * setter
	 * @param dueDate
	 */
	public void setDueDate(Date dueDate)
	{
		this.dueDate = dueDate;
	}

	/**
	 * getter
	 * @return due date
	 */
	public Date getDueDate()
	{
		return dueDate;
	}

	/**
	 * setter
	 * @param groupIdIn
	 */
	public void setGroupId(String groupIdIn)
	{
		this.groupId = "%" + groupIdIn + "%";
	}

	/**
	 * getter
	 * @return group Id
	 */
	public String getGroupId()
	{
		return groupId;
	}

	/**
	 * setter
	 * @param barcodeIn
	 */
	public void setBarcode(String barcodeIn)
	{
		this.barcode = "%"+barcodeIn+"%" ;
//		this.barcode = barcodeIn ;
	}

	/**
	 * getter
	 * @return barcode
	 */
	public String getBarcode()
	{
		return barcode;
	}

	/**
	 * @param noEntryInteger
	 */
	public void setNoEntryInteger(Integer noEntryInteger)
	{
		this.noEntryInteger = noEntryInteger;
	}

	/**
	 * @return noEntryInteger
	 */
	public Integer getNoEntryInteger()
	{
		return noEntryInteger;
	}

	/**
	 * setter
	 * @param noEntryString
	 */
	public void setNoEntryString(String noEntryString)
	{
		this.noEntryString = noEntryString;
	}

	/**
	 * getter
	 * @return noEntryString "noentry"
	 */
	public String getNoEntryString()
	{
		return noEntryString;
	}

	/**
	 * setter
	 * @param noEntryDate
	 */
	public void setNoEntryDate(Date noEntryDate)
	{
		this.noEntryDate = noEntryDate;
	}

	/**
	 * getter
	 * @return noEntryDate (1970)
	 */
	public Date getNoEntryDate()
	{
		return noEntryDate;
	}

	/**
	 * setter
	 * @param jobStatC
	 */
	public void setJobStatC(Integer jobStatC)
	{
		this.jobStatC = jobStatC;
	}

	/**
	 * getter
	 * @return job status code
	 */
	public Integer getJobStatC()
	{
		return jobStatC;
	}

	/**
	 * setter
	 * @param jobList
	 */
	public void setJobList(String jobList)
	{
		this.jobList = new StringBuffer(jobList);
	}

	/**
	 * getter
	 * @return list of jobs
	 */
	public String getJobList()
	{
		return jobList.toString();
	}

	/**
	 * Builds a String representation from a list of
	 * Jobs.
	 * @param l
	 */
	public void buildJobList(Vector l)
	{
		jobList = new StringBuffer();
		for (int i = 0; i < l.size(); i++)
		{
			jobList.append( (String)l.get(i));
			if(i < (l.size() - 1))
			{
				jobList.append(",");
			}
		}
	}

	/**
	 * setter
	 * @param retailer
	 */
	public void setRetailer(String retailer)
	{
		this.retailer = "%" + retailer + "%";
	}

	/**
	 * getter
	 * 
	 * @return retailer
	 */
	public String getRetailer()
	{
		return retailer;
	}
}

_____________________________________________________________________
NOTE:  This email and any information contained within or attached in a
separate file is confidential and intended solely for the Individual to
whom it is addressed. The information or data included is solely for the
purpose indicated or previously agreed. Any information or data included
with this e-mail remains the property of Findel PLC and the recipient
will refrain from utilising the information for any purpose other than
that indicated and upon request will destroy the information and remove
it from their records.  Any views or opinions presented are solely those
of the author and do not necessarily represent those of Findel PLC. If
you are not the intended recipient, be advised that you have received
this email in error and that any use, dissemination, forwarding,
printing, or copying of this email is strictly prohibited. No warranties
or assurances are made in relation to the safety and content of this
e-mail and any attachments.  No liability is accepted for any
consequences arising from it. Findel Plc reserves the right to monitor
all e-mail communications through its internal and external networks.If
you have received this email in error please notify our IT helpdesk on
+44(0) 1254 303030

Mime
View raw message