Archive

Archive for March, 2013

SQL Joins explained in simple terms

Joins are used to join two or more tables in SQL based on the common data.
In this post I have explained below joins

1. Inner Join
2. Left outer join
3. Right outer join

Example:  Let’s say we have an Employee table and Department table. Where every employee is associated with Department, new employees many not have Department.
And Every Department will have employees, for new Department, employees may not be there.

Department Table with data:
department_tableEmployee table with data:
employee_data

Here department_id is the foreign key to Department table, Department 3 doesn’t have any employees associated with it.  And Employees 4,5 are not associated with any Department.

Now will perform different type of joins and examine the results:

1. Inner Join: Intersection of two tables is called inner join, Only the matched data will be returned from the two tables

Query: select e.*, d.* from employee e inner join department d on e.department_id = d.id
Resulted data :
inner_join

The employees who doesn’t have any department and Department which doesn’t have employees won’t be returned.
In Venn diagram it looks like this:

inner_join_first
2. Left outer join: returns all the data from the left table and only matched data from the right table.

Query: select e.*, d.* from employee e left outer join department d on e.department_id = d.id
Resulted data :
left_outer_join

Returns all the 5 records from the Employee table and only 3 records from the Department table.
In the place of Department details for the employees 4,5( who doesn’t have department associated ) gives NULL values:
Venn diagram looks like this:
left_outer_join_venn

If we do left outer join from Department to Employee the result would be:
Query:  select  d.*, e.* from  department d left outer join employee e on  d.id = e.department_id

Resulted Data:

left_outer_join_reverse
Returns all the data from Department table and in the place of employee details for Department 3 gives NULL values:

3. Right Outer Join:

Returns all the data from right side table and only matched data from the left side table.
Query: select e.*, d.* from employee e right outer join department d on e.department_id = d.id

Resulted data:

right_outer_join

Returned all records from Department table and NULL values in the place Department 3 as no employees associated with it.
Venn diagram looks like this:
right_outer_join_venn

Thanks for reading.

Advertisements
Categories: SQL

Spring @Cacheable and @CacheEvict explained in simple terms

March 23, 2013 8 comments

In Spring 3.1 we have a feature called Caching, It caches the data based on java method execution, if the java method executed before with same parameters it returns the cached data, other wise it will execute the method and puts the data into cache.

To enable caching :


<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:cache="http://www.springframework.org/schema/cache"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/cache http://www.springframework.org/schema/cache/spring-cache.xsd">
<cache:annotation-driven />
//your beans
</beans>

<cache:annotation-driven />  Will recognize the spring cache annotations @Cacheable and @CacheEvict

Spring cache provides

@Cacheable annotation and @CacheEvict annotation.

Put @Cacheable on the method which you want to cache.

Ex:


@Cacheable(value="messagecache", key="#id", condition="id < 10")

public String getMessage(int id){

return "hello"+id;

}

Here getMessage() method is marked with @Cacheable, whenever getMessage() is called it will check the messagecache, if the data is already in messagecache it will return that otherwise it will executes the getMessage() and returns data.

@Cacheable annotations has 3 attributes

  1. Value : is the cache name and it is mandatory, in example it is “messagecache”
  2. Key: based on this data will be cached and it is optional
  3. Condition:  based on the condition data will be cached. In example if the id < 10 then only data will be cached otherwise won’t. it is optional

@CacheEvict annotation will be used to delete the data from existing cache.

@CacheEvict(“employees”)

public void saveEmployee(Employee e){

}

Here whenever a saveEmployee() is called cache will be deleted.

@CacheEvict has 5 attributes:

  1. Value, 2 Key, 3 condition are similar to @Cacheable, apart from these 3 we have another 2 attributes
    1. allEntries : is a Boolean type and delete entire cache
    2. beforeInvocation: is Boolean type and will delete the cache before the method execution

Will go through one small example:

In this example I have used ehcache, refer www.ehcache.org for more information

applicationContext.xml


<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:p="http://www.springframework.org/schema/p"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:cache="http://www.springframework.org/schema/cache"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/cache
http://www.springframework.org/schema/cache/spring-cache.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd">

<!-- Scans within the base package of the application for @Components to configure as beans -->
<context:component-scan base-package="com" />

<!-- Process cache annotations -->
<cache:annotation-driven />

<!-- Configuration for using Ehcache as the cache manager-->
<bean id="cacheManager" p:cache-manager-ref="ehcache"/>
<bean id="ehcache" p:config-location="classpath:ehcache.xml"/>

<bean id="employee" class="com.java2practice.model.Employee"/>

</beans>

ehcache.xml


<ehcache>
<diskStore path="java.io.tmpdir"/>
<cache name="employeeCache"
maxElementsInMemory="10000"
eternal="false"
timeToIdleSeconds="120"
timeToLiveSeconds="120"
overflowToDisk="true"
maxElementsOnDisk="10000000"
diskPersistent="false"
diskExpiryThreadIntervalSeconds="120"
memoryStoreEvictionPolicy="LRU"/>

</ehcache>

Employee pojo:


package com.java2practice.model;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.cache.annotation.Cacheable;

public class Employee {

Logger logger = LoggerFactory.getLogger(getClass());

@Cacheable(value="employeeCache", key = "#id")
public String getEmployee(Integer id){
logger.info("get employee called");
return "employee"+id;
}

}

This Employee class is injected into controller:

Controller :


package com.java2practice.web;
import java.util.HashMap;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import com.java2practice.model.Employee;
@Controller
public class WebController {

@Autowired
Employee employee;

@RequestMapping("/index.htm")
public String homePage(@RequestParam(required= false) Integer id, HashMap<String, String> map){
map.put("message", employee.getEmployee(id));
return "index";
}
}

And finally here is my JSP code:


<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>

<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Cache Example</title>
</head>
<body>
<h1>This is ${message }</h1>
</body>
</html>

First time for the URL : http://localhost:8080/springcache/index.htm?id=1

Employee class getEmployee() method will be executed and data will be placed in cache. second time for the same request with id=1 getEmployee() method wont be executed, this can be seen by using the log message “get employee called” prints only for the first time.

And for the http://localhost:8080/springcache/index.htm?id=2

getEmployee() will be called and data will be placed in cache.

Here id is the Key, if id changes method will be executed and data will be placed in cache with the key.

When ever the requests comes for the @Cacheable annotated method, spring will check the key in corresponding cache if the cache has key in it. data will be returned from the cache other wise method will be executed.

Instead of ehcache we can use Spring SimpleCacheManager also:


<!-- Configuration for using SimpleCacheManager as the cache manager-->
 <bean id="cacheManager" class="org.springframework.cache.support.SimpleCacheManager" >
 <property name="caches">
 <set>
 <bean class="org.springframework.cache.concurrent.ConcurrentMapCacheFactoryBean"  p:name="employeeCache"/>
 </set>
 </property>
 </bean>

Categories: Spring

How to use LinkedIn Javascript API

March 14, 2013 13 comments

Before integrating with Linkedin javascript API we need to register our application with Linkedin.

To do this go to  https://www.linkedin.com/secure/developer, in the Javascript domains add your application URL, localhost is accepted

Below code will describes how to add linkedin sign in button to your application.

Sample JSP file:


<!-- 1. Include the LinkedIn JavaScript API and define a onLoad callback function -->

<script type="text/javascript" src="http://platform.linkedin.com/in.js">

api_key: your_api_key

scope: r_network,r_emailaddress,r_fullprofile,r_basicprofile,r_contactinfo

</script>

</head>

<body>

<!-- need to be logged in to use Search; if not, offer a login button -->

<script type="IN/Login"></script>

</body>

That’s it, linkedin sign in button will appear in your page, if you click on it based on the scope it will ask your permissions.

For searching people from your application , we have to use Linkedin People Search API

To use linkedin search API, i have developed small program. It will ask for first name and last name, based on the given data it will search in linked in and prints the result in UI.


<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<title>LinkedIn JavaScript API Hello World</title>

<script src="http://code.jquery.com/jquery-1.8.2.js"></script>

<!-- 1. Include the LinkedIn JavaScript API and define a onLoad callback function -->

<script type="text/javascript" src="http://platform.linkedin.com/in.js">

api_key: your_api_key

scope: r_network,r_emailaddress,r_fullprofile,r_basicprofile,r_contactinfo

</script>

<script type="text/javascript">

function searchClick() {

alert($("#firstNameId").val()+":"+$("#lastNameId").val());

if (!IN.ENV.auth.oauth_token) {

alert("You must login w/ LinkedIn to use the Search functionality!");

return;

}

IN.API.PeopleSearch()

.fields("id", "firstName", "lastName","emailAddress","headline","industry","pictureUrl","positions",

"summary","numConnections")

.params({

"first-name": $("#firstNameId").val(),

"last-name": $("#lastNameId").val(),

"count":25

})

.result(function(result, metadata) {

setSearchResults(result.people.values);

});

}

function setSearchResults(values) {

var table = $("#resulttable");

table.append('<tr><th>First Name</th><th>Last Name</th><th>Head Line</th><th>Industry</th><th>Picture</th><th>No Of Connections</th><th>Summary</th><th>Positions</th></tr>');

for (i in values) {

try{

var person = values[i];

var positionsStr = "<ul>";

for(i in person.positions.values){

positionsStr+="<li>"+person.positions.values[i].company.name+"</li>";

}

console.log(positionsStr);

table.append('<tr><td>'+

person.firstName+'</td><td>'+

person.lastName+'</td><td>'+

person.headline+'</td><td>'+

person.industry+'</td><td><img src="'+

person.pictureUrl+'"/></td><td>'+

person.numConnections+'</td><td>'+

person.summary+'</td><td>'+

positionsStr+'</ul></td></tr>')

}catch(err){alert(err);}

}

}

</script>

</head>

<body>

<!-- need to be logged in to use Search; if not, offer a login button -->

<div align="right">

<script type="IN/Login"></script>

</div>

<center>

<p>Basic test of the People Search API via Connect.</p>

First Name: <input type="text" name="firstName" id="firstNameId"/><br/>

Last Name: <input type="text" name="lastName" id="lastNameId"/><br/>

<input type="button" name="searchLinkedIn" value="Search LinkedIn!" onclick="searchClick();"/>

</center>

<table id="resulttable">

</table>

</body>

</html>

Thats it, for further info regarding Linkedin API http://api.linkedin.com/

Categories: linkedin

How to pass JSON Object string or JSONArray string from javascript to spring controller

March 14, 2013 13 comments

We usually send primitive data to spring controller by using @RequestParam annotation. But how to pass whole JSONObject string or JSONArray string to spring controller directly.

For that we have to include below jar files in buildpath

  1. jackson-core-asl-1.7.3.jar
  2. jackson-mapper-asl-1.7.3.jar

I have created Person pojo which will be mapped with javascript JSONObject exactly, Whatever the identifiers are there in this POJO should be there in Javascript JSON.

public class Person implements Serializable{
private String id;
private String firstName;
private String lastName;
//setters and getters
}

Pojo should implement Serializable interface, as  Jackson will serialize and deserialize to send data between server and client.

Our json data is :
{"persons": [
{
"firstName": "Ramesh",
"id": "id1",
"lastName": "Kotha"
},
{
"firstName": "Sathish",
"id": "id2",
"lastName": "Kotha"
}
]
}

Below is an ajax request from jsp.

$.ajax({
type: 'POST',
dataType: 'json',
contentType:'application/json',
url: "create_persons.htm",
data:JSON.stringify(arr),
success: function(data, textStatus ){
console.log(data);
//alert("success");
},
error: function(xhr, textStatus, errorThrown){
//alert('request failed'+errorThrown);
}
});

Now will see Spring controller Code:

@RequestMapping(value="/create_persons.htm")
public @ResponseBody createPerson(@RequestBody Person[] persons){
//here you can persons array as normal
for(Person person : persons){
System.out.println(person.getId());
}
}

By seeing @RequestBody annontation json data will be converted into Java Person[] and passed to persons array.

Add this in your Configuration file :

<bean id="jacksonMessageConverter" class="org.springframework.http.converter.json.MappingJacksonHttpMessageConverter"></bean>
<bean class="org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter">
<property name="messageConverters">
<list>
<ref bean="jacksonMessageConverter"/>
</list>
</property>

That’s it, now if you pass json string to the spring controller, it will be converted into java POJO.

Categories: Java, Spring