Akdora’s Blog

Programming, Oracle, Life, Fun

Live Combo Search with ExtJS February 4, 2009

Filed under: Ajax,ExtJS,Java,PL/SQL — Akdora @ 10:39 am
Tags: , , , , , , , , , , , , , , ,

Live Combo Search with ExtJS

 

If you trying to list thousands of data in a combobox, then you have a problem. I faced this problem a while ago. I was developing the presentation layer with ExtJS. ExtJS has a live search solution for this situation. Check the web page: http://extjs.com/deploy/dev/examples/form/forum-search.html

I will explain the whole story from beginning to end about this online dynamic checkbox control. I will use Oracle XE 10g, Java-Struts with JPS pages and ExtJS. We will pass the data with JSON. You may make some changes in the code. It all depends on you. My metod is just a suggestion.

 

Lets create a procedure that returns a cursor. It also takes an input parameter that’s our search string. We have 60000 rows in  table company. It is also recommended to create an Function-Based Index (FBI) on “lower(t. company_name)”.

s

CREATE OR REPLACE PROCEDURE prc_company_list

(

     cresult  OUT t_cursor,

     v_search IN VARCHAR2

) IS

     lsearch VARCHAR2(50);

BEGIN

     lsearch := lower(v_search);

     OPEN cresult FOR

          SELECT t.company_code, t.company_name

            FROM tcompany t

           WHERE lower(t. company_name) LIKE ‘%’ || lsearch || ‘%’

           ORDER BY t. company_name;

 

END prc_company_list;

 

After created or prosedure We will call it from persistant java object.

//Firstly we create a object to hold our data

————————CompanyForm.java———————–

package com.myProject.Forms;

import org.apache.struts.action.ActionForm;

 

public class CompanyForm extends ActionForm{

                          

                public KfiCompanyForm() {

        super();

                }

 

                private String companyNameSel;

                private String companyNameIDSel;

               

                public String getCompanyNameSel() {

                               return companyNameSel;

                }

                public void setCompanyNameSel(String companyNameSel) {

                               this.companyNameSel = companyNameSel;

                }

                public String getCompanyNameIDSel() {

                               return companyNameIDSel;

                }

                public void setCompanyNameIDSel(String companyNameIDSel) {

                               this.companyNameIDSel = companyNameIDSel;

                }

}

 

———————–MyList.java———————————

package com.myProject.Persistent;

import java.util.ArrayList;

import java.util.HashMap;

 

public abstract class MyList extends Persistent {

    protected ArrayList list;

    // holds list of objects (model or persistent) returning from a query.

    protected HashMap map;

    // holds list of objects (model or persistent) returning from a query.

    // Collection type refs

    protected int CollectionType = 0; // 1 for prepared , 2 for callable stmnt

    protected static final int LIST = 1; //

    protected static final int MAP = 2; //

    public MyList() {

        super();

        list = null;

    }

 

    public final java.util.ArrayList getList() {

        return list;

    }

 

    public void setList(java.util.ArrayList newList) {

        CollectionType = LIST;

        list = newList;

    }

 

    public java.util.HashMap getMap() {

        return map;

    }

 

    public void setMap(java.util.HashMap newMap) {

 

        CollectionType = MAP;

        map = newMap;

    }

}

//This classs calls the procedure that we created and sets the data to List

———————CompanyListPersistent.java——————-

package com.myProject.Persistent;

 

import java.util.ArrayList;

import com.myProject.Forms.KfiCompanyForm;

 

public class CompanyListPersistent extends MyList {

 

                private static final String companyList = ” begin  prc_company_list(cresult => ? ,”

                                                                                                                       + ” v_search => ? ); end; “;

 

                public void fetchCompanyNameList(String searchString) throws Exception {

                               try {

                                               super.parseCallableStatement(companyList);

                                               registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);

                                               setParameter(2, searchString);

 

                                               execute();

                                               super.setOutCallableRefCursor(1);

 

                                               if (getResult() != null) {

                                                               ArrayList l = new ArrayList();

                                                               setList(l);

                                                               while (getNextRow()) {

                                                                              KfiCompanyForm kficompanyForm = new KfiCompanyForm();

                                                                              kficompanyForm.setCompanyNameIDSel(getStringResult(1));

                                                                              kficompanyForm.setCompanyNameSel(getStringResult(2));

                                                                              getList().add(kficompanyForm);

                                                               }

                                               }

                               } catch (Throwable e) {

                                               catchMe(e);

                               } finally {

                                               finalizeMe();

                               }

                }

}

 

————————CampaignAction.java——————–

package com.myProject.Actions;

 

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

 

import org.apache.struts.action.Action;

import org.apache.struts.action.ActionError;

import org.apache.struts.action.ActionErrors;

import org.apache.struts.action.ActionForm;

import org.apache.struts.action.ActionForward;

import org.apache.struts.action.ActionMapping;

import org.apache.struts.action.ActionMessage;

import org.apache.struts.action.ActionMessages;

import net.sf.json.JSONArray;

 

public class CampaignAction extends Action {

 

                public ActionForward execute(ActionMapping actionMapping,

                                               ActionForm actionForm, HttpServletRequest request,

                                               HttpServletResponse response) throws Exception {

                               String action = null;

                               action = request.getParameter(“action”);

                               ActionErrors errors = new ActionErrors();

                               ActionMessages messages = new ActionMessages();

                               ActionMessage msg = new ActionMessage(“”);

                               errors.clear();

                               KfiCampaignForm myForm = (KfiCampaignForm) actionForm;

                              

                               try {

                                              

                                               if (action != null) {

                                                                             

                                                               if (action.equals(“getcompany”))

                                                               {

                                                                              String searchString = request.getParameter(“searchString”);

 

                                                                              CompanyListPersistent companyNameList = new CompanyListPersistent();

                                                                              if (searchString == null || searchString.compareTo(“”) == 0) {

                                                                                              searchString = “XwSaDaAdAdASdasdlashdlajs”; //It will empty at first search

                                                                              }

                                                                              //We call the CompanyListPersistent class to retrieve the data

                                                                              companyNameList.fetchCompanyNameList(searchString);

                                                                             

                                                                              //And we convert it into json string

                                                                              JSONArray jsonArray = JSONArray.fromObject(companyNameList.getList());

                                                                             

                                                                              String myOut = “{“jsonRoot”:” + jsonArray.toString()+ “}”;

                                                                              response.setContentType(“text/html;charset=iso-8859-9”);

                                                                              response.getWriter().write(myOut);

 

                                                                              //then we can return the json string, but it will not map any page

                                                                              return actionMapping.findForward(“”);

                                                               }

                                               }

                                               return actionMapping.findForward(“myPage”); //we wrote it struts-config.xml

                               } catch (Exception e) {

                                               e.printStackTrace();

                                               request.getSession().setAttribute(“error”, e.toString());

                                               return actionMapping.findForward(“failure”);

                               }

                }

}

 

——————–liveSearch.jsp—————————————

<%@ taglib uri=”/WEB-INF/struts-bean.tld” prefix=”bean” %>

<%@ taglib uri=”/WEB-INF/struts-html.tld” prefix=”html” %>

<%@ taglib uri=”/WEB-INF/struts-logic.tld” prefix=”logic” %>

 

<link rel=”stylesheet” type=”text/css” href=”/myProject/finansalIhtiyaclar/resources/extjs/resources/css/ext-all.css”>

 

<%@ page contentType=”text/html;charset=iso-8859-9″%>

 

<html>

<head>

<META HTTP-EQUIV=”Pragma” CONTENT=”no-cache”>

<META HTTP-EQUIV=”Expires” CONTENT=”-1″>

<META HTTP-EQUIV=”CACHE-CONTROL” CONTENT=”NO-CACHE”>

</head>

<body>

 

<script type=”text/javascript” src=”/myProject/resources/extjs/ext-base.js”></script>

<script type=”text/javascript”  src=”/myProject/resources/extjs/ext-all-debug.js”></script>

 

 

<script type=”text/javascript”>

 

    Ext.onReady(function() {

   

    Ext.Ajax.defaultHeaders = {‘Accept’: ‘application/json’};

 

                var dsCompany= new Ext.data.JsonStore({

                    url: ‘/myProject/kfiCampaignForm.do?action=getcompany’,

                    root: ‘jsonRoot’,

                    fields: [‘companyNameIDSel’, ‘companyNameSel’],

                    disableCaching:false,

                    autoLoad:true                 

                });

                              

    var myFormPanel = new Ext.FormPanel({

        labelWidth: 180,

        id : ‘myForm’,

        frame:true,

        title: ‘XXX Screen’,

        bodyStyle:’padding:5px 5px 0′,

        width: 480,

        defaults: {width: 170},

        defaultType: ‘textfield’,

        bodyBorder: false,

        bodyStyle:’padding:5px 5px 0; border-width: 0px;’,

        monitorValid:true,

                               allowBlank:false,

                               items: [{  

            xtype:’fieldset’,

            title: ‘Lütfen sorgulama kriterlerini seçiniz’,

            collapsible: false,

            autoHeight: true,

            autoWidth : true,

            defaults: {width: 230},

            defaultType: ‘textfield’,

            waitMsgTarget: true,

                waitMsg:’Loading’,

           

                        items: [

                                new Ext.form.Hidden({

                                               name: ‘companyNameHid’,

                                               hideLabel : true,

                                               value: ”

                                }),

                new Ext.form.ComboBox({

                                                       store: dsCompany,

                                                       xtype:’combo’,

                                                       fieldLabel: ‘Company Name (At least 4 Chr)’,

                                name: ‘selectedCompanyName1’,

                                id:’selectedCompanyName1′,

                                hiddenId:’selectedCompanyName’,

                                                                              hiddenName:’selectedCompanyName’,

                                                       displayField:’companyNameSel’,

                                                       valueField:’companyNameSel’,

                                                       typeAhead: false,

                                                                              triggerAction:’all’,

                                                                              selectOnFocus: true,

                                                       onKeyUp : function(){

                                                                       Ext.getCmp(‘myForm’).getForm().findField(‘companyNameHid’).setValue(this.getEl().dom.value);

                                                               if(this.getEl().dom.value.length > 3)

                                                               {

                                                                               this.store.baseParams = { searchString:this.getEl().dom.value };

                                                                               this.store.reload();

                                                               }

                                                               else

                                                               {

                                                                              this.collapse();

                                                                              this.store.reload();

                                                               }

                                                                             

                                                               },

                                                       loadingText: ‘Yükleniyor…’

                                                   })       

        ]

        }

        ],

 

        buttons: [

        {

            text: ‘Submit’,

            handler: submitForm

 

        }],

        renderTo: ‘content’

 

    });

  

               

                var myMask = new Ext.LoadMask(Ext.getBody(), {msg:”Lütfen bekleyiniz…”});

    function submitForm()

    {  

                               //If we write something that is not in list of combobox. Then combobox value set null

                               //So we set it a hidden variable,

                if(Ext.get(‘selectedCompanyName’).dom.value == ” || Ext.get(‘selectedCompanyName’).dom.value == null)

                               {

                                               if( Ext.get(‘companyNameHid’).dom.value != ” && Ext.get(‘companyNameHid’) != null ) {

                                                               Ext.getCmp(‘myFormPanel’).getForm().findField(‘selectedCompanyName’).setValue(Ext.get(‘companyNameHid’).dom.value);

                                               }

                               }

                               

                //check the comboBox is set ?

                if(kampanyaTanimForm.getForm().isValid() == true)

                {

                              

                                               myMask.show();

                                               var myForm=Ext.getCmp(‘myForm’);

                                               myForm.getForm().getEl().dom.setAttribute(‘method’, ‘POST’);

                                               myForm.getForm().getEl().dom.setAttribute(‘action’, ‘/myProject/collectingForm.do?action=submitCompany’);

                                               myForm.getForm().getEl().dom.submit();

                    } 

                    else

                    {                                                        

                                   Ext.Msg.show({ title: ‘Form Kontrolü Hatası’, msg: ‘Gerekli Tüm Alanları Doldurmadınız!!’,

                                                                                                              buttons: Ext.Msg.OK, icon: Ext.MessageBox.ERROR

                                               });          

                    }

 

 

 

 

 

 

 

 

 

 

 

8 Responses to “Live Combo Search with ExtJS”

  1. mike Says:

    I was looking for something like this , thanks

  2. akdora Says:

    you wellcome🙂

  3. baran Says:

    bro eline sağlık

  4. v pills Says:

    Thank you for informing

  5. bob Says:

    hey,
    can you share the complete code source ?
    Especially the main JSP ?

    Thanks. Great share.

  6. Sneha Vasanth Says:

    Thanks a lot for that , was looking for exactly this!!! could u share the complete source code?

  7. Anish Sahare Says:

    Respected Sir
    I have to store json data to oracle table using procedure .
    for this which type of package i require to perform above operation .
    if u have any sample procedure for json data insertion into oracle table .
    plz send to email_id (sahareanish@gmail.com) .

    Regard’s
    Anish Sashare .


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s