How to call Stored Procedure using jdbcTemplate and store result in object DTO/String/List/Result se

[sushant verma]

Hi, I'm bit confused with the calling pattern of Stored Procedure using jdbcTemplate.
Do i need to add a new class everytime a new SP is to be called?

I WANT to call a SP with some IN and OUT parameters and want to store the result in some DTO object and want to do this using spring in such a manner
that many functions can be listed in a sinlge class some XXXDAO.java

So that it can be called from a bllImp class with some input arguments and gets a class object DTO/String/List/Result set in return.

Please give a running example or provide a .war with the example code or any link where i can clear my doubts.

Thanks in Advance

[Mark Spritzler]

Basically, you have to create a separate class for each Stored Procedure, but you can re-use an instance over and over for many calls to the stored procedure with different values for the IN parameters.

This class extends Spring's Stored Procedure class.

In its constructor you are telling Spring what are the IN and OUT parameters and what types they are.

Then when you use this class, you call the execute method on it passing in values for the IN parameters and it returns a Map with all the values returned as OUT parameters.

Hope that clears it up for you

Mark

[sushant verma]

Thanks for your reply but I found how to do it i a single class with different Stored Procedure names:




================================PROCEDURE sp_authenticateUserPwd============================= DELIMITER // DROP PROCEDURE IF EXISTS sp_authenticateUserPwd// CREATE PROCEDURE sp_authenticateUserPwd(IN userName VARCHAR(20),OUT password VARCHAR(20), OUT userExists CHAR(1)) BEGIN DECLARE userNameCount INT DEFAULT 0; SELECT count(name) into userNameCount FROM people where name=userName; IF userNameCount=1 THEN set userExists=1; SELECT likes into password from people where name=userName; ELSE set userExists=0; END IF; END // DELIMITER ; ================================PROCEDURE sp_authenticateUser==================================================== DELIMITER // DROP PROCEDURE IF EXISTS sp_authenticateUser// CREATE PROCEDURE sp_authenticateUser(IN in_userName VARCHAR(20),IN in_password VARCHAR(20),OUT out_isUserValid CHAR(1), OUT out_userExists CHAR(1)) BEGIN DECLARE userNameCount INT DEFAULT 0; DECLARE userPassword VARCHAR(20) DEFAULT null; SELECT count(name) into userNameCount FROM people where name=in_userName; IF userNameCount=1 THEN set out_userExists=1; SELECT likes into userPassword from people where name=in_userName; IF in_password=userPassword THEN set out_isUserValid=1; ELSE set out_isUserValid=0; END IF; ELSE set out_userExists=0; set out_isUserValid=0; END IF; END // DELIMITER ;

//================================MY JAVA DAO CLASS====================================================

package com.sushant.verma.test.dao;

import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;

import com.sushant.verma.dal.UserDto;

public class JdbcUserDtoDao{

	private static final long serialVersionUID = 1464567574634L;
	private static Logger log = LogManager.getLogger(JdbcUserDtoDao.class);
	private SimpleJdbcCall procReaduserDto;
	
	public void setDataSource(DataSource dataSource) {
		procReaduserDto =new SimpleJdbcCall(dataSource);
	}

//================================method for sp_authenticateUserPwd ================================================


	/**
	 * With Single IN parameter 
	 * @param userEmail
	 * @return UserDto Object
	 */

	public UserDto readUser(String userEmail) {
		this.procReaduserDto.withProcedureName("sp_authenticateUserPwd");
		SqlParameterSource in = new MapSqlParameterSource().addValue("userName", userEmail);
		Map<String,Object> out = procReaduserDto.execute(in);
		log.debug("|__out=="+out);
		UserDto userDto = new UserDto();
		userDto.setUserEmail(userEmail);
		userDto.setUserPwd((String) out.get("password"));
		userDto.setUserExists((String) out.get("userexists"));
		return userDto;
	}


//================================Overloaded method for sp_authenticateUser ================================================

/**
 * With Two IN parameter
 * @param userEmail
 * @param password
 * @return UserDto object
 */

	public UserDto readUser(String userEmail,String password) {
		this.procReaduserDto.withProcedureName("sp_authenticateUser");
		
		Map<String,Object> args=new HashMap<String,Object>();
		args.put("in_userName", userEmail);
		args.put("in_password", password);
		Map<String,Object> out = procReaduserDto.execute(args);
		log.debug("|__out=="+out);
		
		UserDto userDto = new UserDto();
		userDto.setUserExists((String) out.get("out_userexists"));
		String userValid=(String) out.get("out_isuservalid");
		if(userValid.equals("1"))
			userDto.setUserValid(true);
		else
			userDto.setUserValid(false);

		return userDto;
	}

}

I tested it and it worked all fine...

[Mark Spritzler]

The only problem is that it is not Threadsafe.

If two threads are running at the same time, one calls one method, the other calls the other method, then the procReaduserDto SimpleJdbcCall instance might have the wrong procedure name for the wrong thread.

Mark

[sushant verma]

Thanks Mark for your reply, can you please guide me how to make it ThreadSafe.... will it be right to make it threadLocal...
I have modified the JdbcUserDtoDao class and separated into two parent and child and instantiated JdbcCallDataSource using Spring IOC

//-----------------parent-----------------------------------
package com.sushant.verma.common.dbConnect;

import javax.sql.DataSource;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;

public class JdbcCallDataSource{

	private static final long serialVersionUID = 1464567574634L;
	private static Logger log = LogManager.getLogger(JdbcCallDataSource.class);
	protected static SimpleJdbcCall simpleJdbcCallDataSource;
	
	public void setDataSource(DataSource dataSource) {
		log.info("@ JdbcCallDataSource setDataSource() with thread="+Thread.currentThread());
		simpleJdbcCallDataSource =new SimpleJdbcCall(dataSource);
		log.debug("|__simpleJdbcCallDataSource=="+simpleJdbcCallDataSource);
	}

}

//-----------------child-----------------------------------
package com.sushant.verma.login.dao;

import java.util.HashMap;
import java.util.Map;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;

import com.sushant.verma.common.dbConnect.JdbcCallDataSource;
import com.sushant.verma.dal.UserDto;

public class SpLoginDao extends JdbcCallDataSource{

	private static final long serialVersionUID = 1464567574634L;
	private static Logger log = LogManager.getLogger(SpLoginDao.class);
	
	public UserDto readUser(String userEmail) {
		simpleJdbcCallDataSource.withProcedureName("sp_authenticateUserPwd");
		SqlParameterSource in = new MapSqlParameterSource().addValue("userName", userEmail);
		Map<String,Object> out = simpleJdbcCallDataSource.execute(in);
		log.debug("|__out=="+out);
		UserDto userDto = new UserDto();
		userDto.setUserEmail(userEmail);
		userDto.setUserPwd((String) out.get("password"));
		userDto.setUserExists((String) out.get("userexists"));
		return userDto;
	}

	public UserDto readUser(String userEmail,String password) {
		log.debug("@ readUser with thread="+Thread.currentThread());
		simpleJdbcCallDataSource.withProcedureName("sp_authenticateUser");
		
		Map<String,Object> args=new HashMap<String,Object>();
		args.put("in_userName", userEmail);
		args.put("in_password", password);
		Map<String,Object> out = simpleJdbcCallDataSource.execute(args);
		log.debug("|__out=="+out);
		
		UserDto userDto = new UserDto();
		userDto.setUserExists((String) out.get("out_userexists"));
		String userValid=(String) out.get("out_isuservalid");
		if(userValid.equals("1"))
			userDto.setUserValid(true);
		else
			userDto.setUserValid(false);

		return userDto;
	}

}

and please explain how to check any function to be threadsafe.

[Mark Spritzler]

Making a property static doesn't change Thread safety.

Personally, I would go back to my first recommendation of creating a StoredProcedure class for each and every stored procedure you have. Don't try to be fancy or tricky to see if you can use just one, getting that to work is too much work, much more than creating one per stored procedure.

You could create a Map where you have many SimpleJdbcCall objects in the map. One for each stored procedure, then the key is the stored procedure map. The problem with this is that it is actually less OO best practices than what I said before.


I recommend doing this.

public class MyFirstStoredProcedure extends StoredProcedure{ ….}

public class MySecondStoredProcedure extends StoredProcedure{…}

That is the best OO design that you can use to re-use the Stored Procedure class in whatever DAOs you want.

Mark

To test Thread safety, create two different threads and make one do one thing and the other do the other thing at the same time.

Mark

[sushant verma]

Thanks Mark for your valuable input.

Just to check the thread safety of my method i created this :

package com.sushant.verma.test;

import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.jdbc.datasource.AbstractDriverBasedDataSource;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import com.sushant.verma.dal.UserDto;

public class MyThread implements Runnable{
	private static Logger log=LogManager.getLogger(MyThread.class);
	/**
	 * creating Instance Variables
	 */
	private UserDto userDto;
	private static SimpleJdbcCall procReaduserDto;
	/**
	 * name and password VALUE set at instance level to be used
	 */
	private String name="sushant";
	private String pwd="verma";


	public static void setDataSource(DataSource dataSource) {
		log.debug("Inside setDataSource ");
		/**
		 * procReaduserDto Object to be created by the main thread in start to be used by multiple threads
		 * as in the case of Spring IOC
		 */
		procReaduserDto =new SimpleJdbcCall(dataSource);  
	} 

	public static void main(String [] args) throws Throwable  
	{
		/**
		 * calling setDataSource with new dataSource object
		 * in the start before starting thread as in Spring IOC
		 */
		DataSource dataSource = new DriverManagerDataSource();
		((DriverManagerDataSource) dataSource).setDriverClassName( "com.mysql.jdbc.Driver");
		((AbstractDriverBasedDataSource) dataSource).setUrl( "jdbc:mysql://localhost:3306/test");
		((AbstractDriverBasedDataSource) dataSource).setUsername( "root");
		((AbstractDriverBasedDataSource) dataSource).setPassword( "admin"); 
		setDataSource(dataSource);

		log.debug("========Creating & Starting Threads===========");
		MyThread obj1 = new MyThread();
		MyThread obj2 = new MyThread();
		new Thread(obj1).start();
		new Thread(obj2).start();
		// main thread is ending here,
		// Thread-0 and Thread-1 continue to run.
	}

	public void run()
	{
		try {
			/**
			 * Iterating for 3 times
			 */
			for (int i=0; i<3; i++) {
				log.info("************************loop "+i+" begins************************");
				if(Thread.currentThread().getName().equals("Thread-1")){
					/**
					 * changing Instance Variable name/pwd value for thread-1 
					 */
					setName("wrong_userName");
					setPwd("wrong_password");
				}

				/**
				 * calling the readUser method for both the threads with different name/pwd values
				 */
				userDto=readUser(name, pwd);
				/**
				 * Thread-1 should return __isUserValid=false & __getUserExists=0
				 * Thread-0 should return __isUserValid=true & __getUserExists=1
				 */
				log.debug(" |__isUserValid="+userDto.isUserValid());
				log.debug(" |__getUserExists="+userDto.getUserExists());
				log.info("~~~~~~~~~loop "+i+" ends~~~~~~~~~");
			}
		} catch (Throwable t) { }
	}


	public UserDto readUser(String userEmail,String password) {
		/**
		 * procReaduserDto object value should be same as created before the thread starts as to be the case with Spring IOC
		 */
		log.debug(" @ readUser with procReaduserDto="+procReaduserDto);
		MyThread.procReaduserDto.withProcedureName("sp_authenticateUser");  

		Map<String,Object> args=new HashMap<String,Object>();  
		args.put("in_userName", userEmail);  
		args.put("in_password", password);  
		Map<String,Object> out = procReaduserDto.execute(args);  

		UserDto userDto = new UserDto();  
		userDto.setUserExists((String) out.get("out_userexists"));  
		String userValid=(String) out.get("out_isuservalid");  
		if(userValid.equals("1"))  
			userDto.setUserValid(true);  
		else  
			userDto.setUserValid(false);  

		return userDto;  
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getPwd() {
		return pwd;
	}

	public void setPwd(String pwd) {
		this.pwd = pwd;
	}


}

Its result is below for 3 iterations in the format: "TIME | THREAD NAME | METHOD NAME | LINE NO | logs "

01:59:56,025 | main |  setDataSource() | L-31 : Inside setDataSource 
 01:59:56,053 | main |  main() | L-52 : ========Creating & Starting Threads===========
 01:59:56,054 | Thread-0 |  run() | L-68 : ************************loop 0 begins************************
 01:59:56,054 | Thread-1 |  run() | L-68 : ************************loop 0 begins************************
 01:59:56,055 | Thread-0 |  readUser() | L-97 :  @ readUser with procReaduserDto=org.springframework.jdbc.core.simple.SimpleJdbcCall@1bd4722
 01:59:56,055 | Thread-1 |  readUser() | L-97 :  @ readUser with procReaduserDto=org.springframework.jdbc.core.simple.SimpleJdbcCall@1bd4722
 01:59:56,547 | Thread-0 |  run() | L-85 :  |__isUserValid=true
 01:59:56,547 | Thread-0 |  run() | L-86 :  |__getUserExists=1
 01:59:56,548 | Thread-0 |  run() | L-87 : ~~~~~~~~~loop 0 ends~~~~~~~~~
 01:59:56,549 | Thread-0 |  run() | L-68 : ************************loop 1 begins************************
 01:59:56,549 | Thread-0 |  readUser() | L-97 :  @ readUser with procReaduserDto=org.springframework.jdbc.core.simple.SimpleJdbcCall@1bd4722
 01:59:56,550 | Thread-1 |  run() | L-85 :  |__isUserValid=false
 01:59:56,559 | Thread-1 |  run() | L-86 :  |__getUserExists=0
 01:59:56,559 | Thread-1 |  run() | L-87 : ~~~~~~~~~loop 0 ends~~~~~~~~~
 01:59:56,559 | Thread-1 |  run() | L-68 : ************************loop 1 begins************************
 01:59:56,560 | Thread-1 |  readUser() | L-97 :  @ readUser with procReaduserDto=org.springframework.jdbc.core.simple.SimpleJdbcCall@1bd4722
 01:59:56,590 | Thread-0 |  run() | L-85 :  |__isUserValid=true
 01:59:56,591 | Thread-0 |  run() | L-86 :  |__getUserExists=1
 01:59:56,591 | Thread-0 |  run() | L-87 : ~~~~~~~~~loop 1 ends~~~~~~~~~
 01:59:56,591 | Thread-0 |  run() | L-68 : ************************loop 2 begins************************
 01:59:56,592 | Thread-0 |  readUser() | L-97 :  @ readUser with procReaduserDto=org.springframework.jdbc.core.simple.SimpleJdbcCall@1bd4722
 01:59:56,599 | Thread-1 |  run() | L-85 :  |__isUserValid=false
 01:59:56,599 | Thread-1 |  run() | L-86 :  |__getUserExists=0
 01:59:56,599 | Thread-1 |  run() | L-87 : ~~~~~~~~~loop 1 ends~~~~~~~~~
 01:59:56,600 | Thread-1 |  run() | L-68 : ************************loop 2 begins************************
 01:59:56,600 | Thread-1 |  readUser() | L-97 :  @ readUser with procReaduserDto=org.springframework.jdbc.core.simple.SimpleJdbcCall@1bd4722
 01:59:56,622 | Thread-0 |  run() | L-85 :  |__isUserValid=true
 01:59:56,623 | Thread-0 |  run() | L-86 :  |__getUserExists=1
 01:59:56,623 | Thread-0 |  run() | L-87 : ~~~~~~~~~loop 2 ends~~~~~~~~~
 01:59:56,638 | Thread-1 |  run() | L-85 :  |__isUserValid=false
 01:59:56,639 | Thread-1 |  run() | L-86 :  |__getUserExists=0
 01:59:56,639 | Thread-1 |  run() | L-87 : ~~~~~~~~~loop 2 ends~~~~~~~~~
 
 

The same method is being called by two threads simultaneously at the same time (loop 0) and as expected for
thread 0 :
|__isUserValid=true
|__getUserExists=1

thread 1:
|__isUserValid=false
|__getUserExists=0





Is that sufficient to check for thread safety of a method?
I mean to say the same method is being called by two threads simultaneously and with different parameters and gets separate result of their own.

Please Check and correct me if i missed something or if I'm wrong on thread safety check.

Thanks & regards
Sushant Verma

[Mark Spritzler]

Basically, you have the concept correct, but it appears all Threads in your example are always calling the exact same Stored Procedure. So the issue won't show up. You have to have one thread call one stored procedure and another thread call the other stored procedure at the exact same time, and you have to have this happen multiple times, because trying to catch the thread issue in a simple test is all about timing. So I would hope you could have many threads all running simultaneously all calling different Stored Procedures at the exact same time over and over again, and you will eventually catch the threading issue with your code.

I would just forget about your approach and use the simpler approach. Create a single class StoredProcedure for each and every Stored Procedure you have in your database, then you won't have these issues. To me you are over complicating things going your route and will cause possible problems with Threading.

Simple is always the better solution.

Thanks

Mark