ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Paul Carr" <Paul.C...@express-gifts.co.uk>
Subject Search screen : HOW TO
Date Tue, 12 Sep 2006 11:33:19 GMT

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