Function-based indexes used as constraints

Let's suppose we want to avoid duplicity in the values of a column produced by a bad checking in a formulary of a program, that ends up allowing to add existing values, with the only difference that the user adds leading/trailing blank spaces, or changes one letter to upper/lower case.

A function based index does not only permits to speed up queries by expresion. I recommend this video for a brief explanation:

A function based index can also be used as a type of constraint. Given the example at the beggining, we could create an index of type "UNIQUE" to prevent duplicity cleaning the value to be inserted and checking its uniqueness.

create unique index INDEX_NAME on TABLE (lower(trim(COL_1)));

Once the index is added to the table, any insertion with a duplicate value (with blank spaces or lower/upper case changed) will throw the follow exception:

SQL Error: ORA-00001: unique constraint (TABLE.INDEX_NAME) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.

Spring Jackson library example to consume JSON returned by REST service

A brief example of how to use the Spring Jackson library to consume a JSON returned by a REST service. The class below is a Singleton that initializes the RestTemple one time only.

import java.util.ArrayList;
import java.util.List;
import org.springframework.http.MediaType;
import org.springframework.http.client.CommonsClientHttpRequestFactory;
import org.springframework.http.converter.HttpMessageConverter;
import org.springframework.http.converter.json.MappingJacksonHttpMessageConverter;
import org.springframework.web.client.RestTemplate;

public class RestJsonTest {
 
 private static RestJsonTest instance;
 private RestTemplate restTemplate;
 private String url ="http://rest.service.url";
 
 public static RestJsonTest getInstance() {
  if (instance == null) {
   instance = new RestJsonTest();
  }
  return instance;
 }
 
 private RestJsonTest() {
  // Setup the RestTemplate configuration.
  restTemplate = new RestTemplate();
  restTemplate.setRequestFactory(new CommonsClientHttpRequestFactory());
  List<HttpMessageConverter<?>> messageConverterList = restTemplate.getMessageConverters();
  
  // Set HTTP Message converter using a JSON implementation.
  MappingJacksonHttpMessageConverter jsonMessageConverter = new MappingJacksonHttpMessageConverter();
  
  // Add supported media type returned by BI API.
  List<MediaType> supportedMediaTypes = new ArrayList<MediaType>();
  supportedMediaTypes.add(new MediaType("text", "plain"));
  supportedMediaTypes.add(new MediaType("application", "json"));
  jsonMessageConverter.setSupportedMediaTypes(supportedMediaTypes);
  messageConverterList.add(jsonMessageConverter);
  restTemplate.setMessageConverters(messageConverterList);
 }
 
 public SearchResults searchResults() {
  return restTemplate.getForObject(url, SearchResults.class);  
 }
 
 public static void main(String[] args) {
  RestJsonTest jsonTest = RestJsonTest.getInstance();
  SearchResults results = jsonTest.searchResults();
 }
}

The mapping of the JSON to Java classes can be done via annotations like it's shown here:

package com.bodybuilding.api.commerce.clientservice;

import java.util.List;
import org.codehaus.jackson.annotate.JsonIgnoreProperties;
import org.codehaus.jackson.annotate.JsonProperty;


/**
 *{
 * "search_keywords":"Social Networks",
 *  "total_time":200,
 *  "results":{
 *   "result_01":{
 *    "url":"http://www.facebook.com",
 *    "rank": "1"
 *   },
 *   "result_02":{
 *   "url":"http://www.twitter.com",
 *    "rank": "2"
 *   }
 *  }
 * }
 */
@JsonIgnoreProperties(ignoreUnknown=true)
public class SearchResults {
 
 @JsonProperty("search_keywords")
 private String keywords;
 
 @JsonProperty("total_time")
 private long totalTime;
 
 @JsonProperty("results")
 private List<SearchResult> results;
 
 public String getKeywords() {
  return keywords;
 }
 public void setKeywords(String keywords) {
  this.keywords = keywords;
 }
 public long getTotalTime() {
  return totalTime;
 }
 public void setTotalTime(long totalTime) {
  this.totalTime = totalTime;
 }
 public List<SearchResult> getResults() {
  return results;
 }
 public void setResults(List<SearchResult> results) {
  this.results = results;
 } 
}

import org.codehaus.jackson.annotate.JsonIgnoreProperties;
import org.codehaus.jackson.annotate.JsonProperty;

@JsonIgnoreProperties(ignoreUnknown=true)
public class SearchResult {
 
 @JsonProperty("url")
 private String url;
 
 @JsonProperty("rank")
 private int rank;
 
 public String getUrl() {
  return url;
 }
 public void setUrl(String url) {
  this.url = url;
 }
 public int getRank() {
  return rank;
 }
 public void setRank(int rank) {
  this.rank = rank;
 }
}

The RestTemplate bean can be also configure with Spring injection:

<bean id="restTemplate" class="org.springframework.web.client.RestTemplate">
     <property name="requestFactory">
      <bean id="clientHttpRequestFactory" class="org.springframework.http.client.CommonsClientHttpRequestFactory" />   
     </property>
     <property name="messageConverters">
      <list>
       <bean id="jsonMessageConverter" class="org.springframework.http.converter.json.MappingJacksonHttpMessageConverter">
        <property name="supportedMediaTypes">
         <list>
          <bean id="jsonMediaTypeTextPlain" class="org.springframework.http.MediaType">
           <constructor-arg value="text"/>
          <constructor-arg value="plain"/>
          </bean>
          <bean id="jsonMediaTypeApplicationJson" class="org.springframework.http.MediaType">
           <constructor-arg value="application"/>
          <constructor-arg value="json"/>
          </bean>
         </list>
        </property>
       </bean>
      </list>
     </property>  
</bean>