Notes for Oracle SQL Usage

 

General Functions

Regardless of the logic purpose of the following codes, let go through each function that have been used within them

 

Round(Avg(To_Char(SysDate-7* RowNum,’ DD Month YYYY’)))

 

SYSDATE: Returns the current date-time from the server where the

database is located

 

TO_CHAR(date,format): Converts a date to a string in the specified format

 

ROWNUM: is an Oracle pseudo column which numbers the rows in a result set, which you can use directly within your SQL query. With SysDate-7* RowNum, you would get a list of dates that has an interval of a week time.

 

AVG(expression) : Returns the average of the values in a set of rows

 

ROUND(number, decimal places): Rounds a value to the specified number of decimal places

 

Decode(Ceil((To_Char(SysDate,’YYYY’)-:paramDate)/5),1,’1′,2,’2′,3,’3′,’4′)

 

CEIL(number): number is the value used to find the smallest integer value. See the difference against Round();

 

DECODE: has the functionality of an IF-THEN-ELSE statement. The value returned by Ceil() will be checked against each following parameter. So, if it’s 1, the decode() function will return ‘1’. else if 2, return ‘2’. else if 3, return, if neither of the preceding ones it will all be ‘4’.

 

Trunc(Add_Months(SysDate,+1*(RowNum-1)

 

TRUNC(datetime): Removes the time component from a date-time value

 

ADD_MONTHS(date, number ofmonths): Adds the specified number of months to the date value (subtracts months if the number of months is negative)

 

 

Cascaded Select

Cascaded select query is very often used for query base on the records set which need to be collected by other queries. Like the following example, the select query needs to be based on the records sets a and b, which need to be collected by the two inner queries.

 

WITH

a AS(

            SELECT …

)

,b As(

            SELECT …

)

SELECT *

FROM a, b

 

One more thing need to be aware is the alias. It is recommended to provides alias for columns that need to be returned by the inner query, although they could be referred by their column name by default.

 

Outer Joins

Joins could be specified either in the From clause or in the Where clause. The difference is the expression. (used by oracle 9i and after)

Select *

from a,

left out join b on a.placeId=b.placeId

 

The preceding code will be also expressed by the following code. (used by oracle8i and ealier, see http://www.kingtraining.com/confdownloads/downloads/Oracle9iJoin_paper.pdf)

Select * from a, b

Where a.placeId =(+)b.placeId

 

FIRST_VALUE, OVER and PARTION BY

        Select Distinct

        DE.Display_Name Company

        ,CRP.PURCHASE_ID ID

        ,First_Value(CR.Published_Date) Over(Partition By DE.Display_Name Order By CRP.Purchase_Date Desc)

        CR_Published_Date

        From CompanyReportPurchase CRP, CompanyReport CR, DirectoryEntry DE

        Where CR.Report_ID=CRP.Report_ID

        AND DE.COMPANY_ID = CRP.COMPANY_ID

The highlighted code above is trying to fetch the CR.Published_Date value from first row (by First_value) of the records returned by the later part. The code inside the Over() function is running based on the records returned by the where clause. Inside the Over() function, the Partition By DE.Display_Name will subgroup the records by DE.Display_Name and order them by CRP.Purchase_Date. 

 

The general syntax of this analytic function could be concluded as:

 

Function(arg1,…, argn) OVER ( [PARTITION BY <…>] [ORDER BY <….>] [<window_clause>] )

 

Reference:

http://www.psoug.org/reference/date_func.html

http://vpf-web.harvard.edu/applications/ad_hoc/key_functions_in_oracle_sql.pdf

 

 

Advertisements
Posted in Database | Leave a comment

JQuery: Auto Complete Plugin

Have found the JQuery is really handy to duel with complex javascript tasks. In this article, I am going to introduce the auto-complete plugin.

 

First of all, like many other JQuery plugins, you need to have the JQuery library (you may also need JSelect library as well) available, which you can download from.

http://docs.jquery.com/Downloading_jQuery

 

Second, you need to include the auto complete library and its style sheet, which you can download from the link that have been referenced at the end of this article

 

Basic use of Auto-Complete

 

The logic behind the auto-complete plugin is quite straight forward. The prompt for auto-complete is derived either from a string array or an url.  The url is normally pointed to a dynamic web page generator, such as servlet. The out put of the page should be a string array (separated by line breaks) as well. If you want to have a pair value as each items of the array, you need to separated the pair value by “|”. For example

 

Qingdao|2549

Qinglan|8179

 

Let’s see an example how to use this auto-complete plugin.

 

$(document).ready(function(){

    $(‘#periodTypeSelect’).jSelect();

    $("#BY_NAME").autocomplete(‘<c:url value="/ajax/places.htm"/>’, {

        onItemSelect:function(li) {

             findValue(li,"#PLACE");  // start recursive call

        },

        minChars: 3,

        selectOnly:true

    });

});

 

$("#BY_NAME") specifies the input elements id. <c:url value="/ajax/places.htm"/> indicates where to fetch the resource data.

 

onItemSelect is function that will be called when an item is selected. The autocompleter will specify a single argument, being the LI element selected. This LI element will have an attribute "extra" that contains an array of all cells that the backend specified.

 

findValue() will examine the value currently in the input element and look it’s value up to see if it can find a matching value. This function can potentially perform an AJAX operation, therefore the findValue() function does not return a value. Instead, you need to specific a onFindValue callback function that will run. This method is valuable if you need to set the Autocomplete input element to a value via JavaScript and the "value" of the text field is mapped to extended properties stored in the LI element’s "extra" property.

 

If selectOnly is set to true, and there is only one autocomplete when the user hits tab/return, it will be selected even if it has not been handpicked by keyboard or mouse action. This overrides selectFirst.

 

minChars specifies the number of chars that required to trigger the autocomplete function.

 

You might wonder how your input passed to server side. This process is actually hidden in the box. When you type anything into the input “BY_NAME” element it will forward a request to your specified url as the value of the parameter “q”.  

 

Using with MultiActionController

Since the auto-complete plugin will append the “?q=” suffix after its request string, you can’t specify  addition parameters directly after the url. Instead, you need to set the additional parameters with extraParams method.

 

$(document).ready(function(){

    $(‘#periodTypeSelect’).jSelect();

    $("#BY_NAME").autocomplete(‘<c:url value="/ajax/places.htm"/>’, {

        minChars: 3,

        selectOnly:true

    });

    $("#BY_AUTHORITY").autocomplete(‘<c:url value="/ajax/places.htm"/>’, {

        extraParams: { display:"byAuthority" },

        minChars: 3

    });

});

 

extraParams: { display:"byAuthority" } the method will append the &display=byAuthority after /ajax/places.htm?q=youInput, which will enable you to use an extended class from MultiActionController to handle all your auto-complete requests.

 

For details of how to use this plugin you can find from its documentation at

http://www.pengoworks.com/workshop/jquery/autocomplete_docs.txt

 

Reference: http://www.pengoworks.com/workshop/jquery/autocomplete.htm

 

Posted in Javascript | Leave a comment

Potential problem with Open Session in View

This article is to introduce the solution of an open connection problem for Open Session in View

 

Servlet 2.3 Filter that binds a Hibernate Session to the thread for the entire processing of the request. Intended for the "Open Session in View" pattern, i.e. to allow for lazy loading in web views despite the original transactions already being completed.

 

This filter makes Hibernate Sessions available via the current thread, which will be autodetected by transaction managers. OpenSessionInViewFilter or  openSessionInViewInterceptor should close the connection  after processing HTTP request ideally.

 

<bean id="sessionFactory"

class="org.springframework.orm.hibernate3.LocalSessionFactoryBean" scope="singleton">

<property name="configLocation" value="classpath:orm/hibernate.cfg.xml" />               

 </bean>

 

<?xml version=’1.0′ encoding=’utf-8′?>

<!DOCTYPE hibernate-configuration PUBLIC

        "-//Hibernate/Hibernate Configuration DTD//EN"

        "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

    <session-factory>

        ….

        <property name="hibernate.connection.datasource">java:comp/env/jdbc/mysqlDB</property>

        …..

    </session-factory>

</hibernate-configuration>

 

The preceding code might be often used by developers. However, there would be a problem that openSessionInViewFilter will not close the connection after processing HTTP request. (For example, you initialise a new session that not using getSession within HibernateTemplate, will not be managed by openSessionInViewFilter)

 

If we pass dataSource as property of org.springframework.orm.hibernate3.LocalSessionFactoryBean rather than via hibernate-configuration it should work fine.

 

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean" scope="singleton">

<property name="configLocation" value="classpath:orm/hibernate.cfg.xml" />       

                <property name="dataSource" ref="dataSource"/>       

</bean>

 

<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">

                <property name="jndiName" value="java:comp/env/jdbc/mysqlDB"/>   

</bean>

 

<?xml version=’1.0′ encoding=’utf-8′?>

<!DOCTYPE hibernate-configuration PUBLIC

        "-//Hibernate/Hibernate Configuration DTD//EN"

        "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

    <session-factory>

        ….

        <!–<property name="hibernate.connection.datasource">java:comp/env/jdbc/mysqlDB</property>–>

        …..

    </session-factory>

</hibernate-configuration>

 

Besides, there might be a problem if you mix using anything other than hibernate, such as jdbc api (java.sql.Connection,java.sql.CallableStatement) we can use org.springframework.jdbc.object.StoredProcedure (spring framework managed) instead of jdbc core api to fix this problem

 

Posted in Spring | Leave a comment

Anotation Basic Use 2

Annotation for Transaction

The @Transactional annotation may be placed before an interface definition, a method on an interface, a class definition, or a public method on a class. However, please note that the mere presence of the @Transactional annotation is not enough to actually turn on the transactional behavior – the @Transactional annotation is simply metadata that can be consumed by something that is @Transactional-aware and that can use the metadata to configure the appropriate beans with transactional behavior. In the case of the above example, it is the presence of the <tx:annotation-driven/> element that switches on the transactional behavior.

 

@Transactional

public class DocumentDaoImpl extends HibernateDaoSupport implements DocumentDao {

 

<!– enable the configuration of transactional behavior based on annotations –>

<tx:annotation-driven transaction-manager="transactionManager"/>

 

<bean id="transactionManager"

class="org.springframework.orm.hibernate3.HibernateTransactionManager">

    <property name="sessionFactory" ref="sessionFactory"/>

</bean>

 

Annotation for Caches

 

Using annotation for caches could help to reduce a lot of the xml bulk in the xml configuration file (see the earlier article regarding to the osCache. http://mybiblebook.spaces.live.com/Blog/cns!8D4E97688DE1E193!458.entry?sa=752205576) – and should make cache configuration simpler and easier to configure.

 

Caching is now setup on a method by method basis – there are two parts to the configuration:

 

1)    Setup a caching model.

2)    Setup method annotation

 

1.    A caching model is kinda like a group of caching configuration – we can specify various properties- ie:

 

·         An oscache group

·         Cache refresh period

·         cron expression

 

These are grouped under a model ID – eg

 

          <prop key="testCaching">cacheName=testCache</prop>

          <prop key="testCaching">refreshPeriod=20</prop>

 

This would setup a caching model with an ID of ‘testCaching’  – which use a cache group name of ‘testCache’ – refreshing items after 20 seconds.

 

2.    To annotate the method we want to cache – we simply add the following to the implementation (not the interface) –

 

    @Cacheable(modelId = "testCaching")

    public int getDual() {

        try {

            return getJdbcTemplate().queryForInt("select 56 from dual");

        } catch(DataAccessException de) {

            // crap

            return -1;

        }

    }

 

This would setup this method to use the ‘testCaching’ caching model. 

 

The following is an example how you write the configuration in details. This includes what type cache model will be used, what sort of caches are available and etc.

 

<bean id="autoproxy" class="org.springframework.aop.framework.autoproxy.DefaultAdvisorAutoProxyCreator"/>

 

<bean id="cacheManager" class="org.springmodules.cache.provider.oscache.OsCacheManagerFactoryBean"/>

 

<bean id="cacheProviderFacade" class="org.springmodules.cache.provider.oscache.OsCacheFacade">

    <property name="cacheManager" ref="cacheManager"/>

    <property name="failQuietlyEnabled" value="false"/>

</bean>

 

<bean id="cachingAttributeSource"

      class="org.springmodules.cache.annotations.AnnotationCachingAttributeSource">

</bean>

 

<bean id="cachingAttributeSourceAdvisor"

      class="org.springmodules.cache.interceptor.caching.CachingAttributeSourceAdvisor">

    <constructor-arg ref="cachingInterceptor"/>

</bean>

 

<bean id="cachingInterceptor"

      class="org.springmodules.cache.interceptor.caching.MetadataCachingInterceptor">

    <property name="cacheProviderFacade" ref="cacheProviderFacade"/>

    <property name="cachingAttributeSource" ref="cachingAttributeSource"/>

    <property name="cachingModels">

        <props>

            <!– short cache –>

            <prop key="shortCacheModel">cacheName=shortCache</prop>

            <prop key="shortCacheModel">refreshPeriod=1000</prop>

 

            <!– medium cache –>

            <prop key="mediumCacheModel">cacheName=shortCache</prop>

            <prop key="mediumCacheModel">refreshPeriod=10000</prop>

 

            <!– long cache –>

            <prop key="longCacheModel">cacheName=longCache</prop>

            <prop key="longCacheModel">cronExpression=0 0 0 1 * ?</prop>

        </props>

    </property>

</bean>

 

Reference:

https://springmodules.dev.java.net/docs/reference/0.8/html/cache.html

Posted in Spring | Leave a comment

Notes for Styles

I hate to do any style works, but sometimes you are left no choice and you have to do it yourself. Here is a little list of the ones that annoyed quite often.

 

Background:

The properties that can be set, are (in order): background-color, background-image, background-repeat, background-attachment, and background-position.

background: #00ff00 url(‘smiley.gif’) no-repeat fixed center;

 

The background-image style is often used as separator (normally, it is 1px image) between two sections in an Html web page, which you normally need to specify that the direction you want to repeat the image, such as

background: url(‘smiley.gif’) repeat-y

 

You would also sometimes want to display an image in the same line as your text. In this case you would need to combine the background with padding.

padding: 2px 10px 0px 20px;

background: url(/images/icon1.png) no-repeat;

If you apply this style with <p> tag, you will get the text displayed on the right side of the image. The trick is you tweaking the text with padding, but your image is always left at the position where <p> tag start.

 

Besides, if you are running this in a jsp page, it is suggested to use the JSTL url tag, as this would include context path.

background: url(<c: url=”/images/icon1.png”/>) no-repeat;

 

 

Float:

The float property specifies whether or not a box (an element) should float. This is especially useful when you want to display two horizontally paralleled elements.

 

In the case you want to have a div A on the left side of the other one (div B). You need to specify two style elements with div A:

float:left;  width:100px;

 

Span:

If you only want to apply different style for the text in a string, using nested span tag would be easier than div tag (as introduced above, you have to specify the float direction and width for the div boxes, otherwise they would be displayed as a stack in vertical direction).  

 

.ex1 {font-size:small;} .ex2 {font-size:x-small;}

 

<span class="ex1">This is small.

<span class="ex2">This is smaller.</span>

</span>

 

This will result something like this

This is small. This is smaller.

 

If you want to have a line break before the second sentence, you can apply block style.

.ex2 {font-size:x-small; display:block;}

 

 

Posted in Style Control | Leave a comment

Using Customer PropertyEditor for Form Data Binding

 

When working with Spring forms, we normally bind the form properties into a command class. However, there would be some troubles if your command class has some properties that are not String, as all values from the form are passed as String. Therefore, you would expect some facilities to handle the conversion and this is what would be discussed in this article

 

The SimpleFormController inherits initBinder() method that allows you to register custom editors for certain fields of your command class. For instance, you will be able to transform Date objects into a String pattern and back, in order to allow your JavaBeans to have Date properties and still be able to set and display them in an HTML interface.

 

public void initBinder(HttpServletRequest request, ServletRequestDataBinder binder) {

      binder.registerCustomEditor(Date.class,

new CustomDateEditor(new SimpleDateFormat("dd/MM/yyyy"), false));

                    

}

 

DataBinder that allows for setting property values onto a target object, including support for validation and binding result analysis. The binding process can be customized through specifying allowed fields, required fields, custom editors, etc. For example, like in the preceding code, the binder allows you to use the CustomDateEditor to covert/reset the value of any Date object that bound in the form. The CustomDateEditor implements PropertyEditorSupport which has two default methods: The setAsText(String text) Parse the Date from the given text, using the specified DateFormat; getAsText() formats the Date as String, using the specified DateFormat.

 

CustomDateEditor is not really a customer property editor as it is actually from spring propertyeditors package. You can write your own one just like CustomDateEditor. The simplest usage is to overwrite the setAsText(String text) and getAsText() methods. For example:

 

//bind your MetadataPropertyEditor to metadata object

binder.registerCustomEditor(Metadata.class, new MetadataPropertyEditor());

 

//overwrite the methods

    public void setAsText(String text) throws IllegalArgumentException {

        Metadata metadata = new Metadata();

        metadata.setMetadataId(Long.valueOf(text));

        setValue(metadata);

    }

 

    public String getAsText() {

        Metadata metadata= (Metadata) getValue();

        if (metadata!=null) {

            return metadata.getMetadataId().toString();

        }

        return "";

    }

Posted in Spring | Leave a comment

Spring Security (3) Implementation of Proxies

To create an  AOP proxy in Spring, you should use the ProxyFactoryBean. This gives complete control over the pointcuts and advice that will apply, and their ordering. However, there are simpler options that are preferable if you don’t need such control. Within the ProxyFactoryBean, you need to provide some property values.

proxyInterfaces – where you specify which interface you would like to create proxy for

interceptorNames – a list of inceptor names. The interceptor will be used to do some business before and after running the method.

target – A reference to the implementation class

<bean id="documentViewService" class="org.springframework.aop.framework.ProxyFactoryBean">

<property name="proxyInterfaces"

            value="com.hongliang.DocumentViewService"/>

    <property name="interceptorNames">

        <list>

             <value>documentViewServiceInterceptor</value>

        </list>

    </property>

    <property name="target">

        <ref bean="documentViewServiceImpl"/>

    </property>

</bean>

 

Then you can specify your interceptor class with the logic that you would like to add to the method while accessing, such as security check.

 

<bean id="documentViewServiceInterceptor"

class="com.hongliang.DocumentViewServiceInterceptor">

    <property name="documentViewAccessDecisionManager">

        <ref local="documentViewAccessDecisionManager"/>

    </property>

    <property name="accessDecisionManager">

        <ref local="documentViewAccessDecisionManager"/>

</property>

<property name="exemptMethods">

     <set>

            <value>getIssues</value>

        </set>

    </property>

</bean>

 

The interceptor class has predefined some properties, which includes the name of the method that should be excluded to apply the logic specified by the interceptor. In order to prevent some redundant work, you might want to exclude those methods before invoking them. To do this, you need to specify it inside the beforeInvocation() method.

 

public class DocumentViewServiceInterceptor extends MethodSecurityInterceptor implements Serializable {

private DocumentViewAccessDecisionManager documentViewAccessDecisionManager;

public InterceptorStatusToken beforeInvocation(Object object) {

            //skip the exempt methods

}

public Object afterInvocation(InterceptorStatusToken token, Object returnedObject) {

Authentication authenticated = SecurityContextHolder.getContext().getAuthentication();

try {

    this.documentViewAccessDecisionManager.decide(authenticated, returnedObject, null);

} catch (InsufficientAuthenticationException e) {

               //your solution if user not authorised 

}

}                                          

}

 

As you can see from the preceding code, we use documentViewAccessDecisionManager which implements AccessDecisionManager to decide whether user have the authorisation to access the current method. If user does not authorised to access, an InsufficientAuthenticationException will be thrown. If such an exception is caught in the interceptor class, you can specify your solution in the catch clause.

 

Reference:

http://static.springframework.org/spring/docs/1.2.x/reference/aop.html

http://static.springframework.org/spring/docs/2.5.x/reference/aop.html#aop-proxying

Posted in Spring | 1 Comment