Announcement Announcement Module
Collapse
No announcement yet.
Can we use SqlParameterSource for executing procedure. Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Can we use SqlParameterSource for executing procedure.

    Hi All,

    Defined procedure & execution as follows:
    Code:
    private class EmployeeProcedure extends StoredProcedure{
    		public EmployeeProcedure(){
    		super();            
    	        setDataSource(jdbcTemplate.getDataSource());
    	        setSql(CREATE_EMPLOYEE_PROC);
    			declareParameter(new SqlOutParameter("return_employee_id", Types.VARCHAR));
    			declareParameter(new SqlParameter("emp_name", Types.VARCHAR));			
    			declareParameter(new SqlParameter("emp_salary",Types.VARCHAR));				
    			declareParameter(new SqlParameter("emp_doj", Types.TIMESTAMP));
    			
    			declareParameter(new SqlParameter("dept_no", Types.VARCHAR));
    			declareParameter(new SqlParameter("dept_name", Types.VARCHAR));			
    		
    			setResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE);
    	        compile();
    	        logger.info("Procedure to be executed: "+getSql());
    		}
    	}
    
          public String createEmpWithDept(Employee emp, Department dept)
    			throws Exception {
    		String emp_id = null;
    		try{
    			
    			EmployeeProcedure procedure = new EmployeeProcedure();
    			Map<Object, Object> inParams = new LinkedHashMap<Object, Object>();			
    			
    			inParams.put("emp_name", emp.getEmp_name());
    			inParams.put("emp_salary", emp.getEmp_salary());
    			inParams.put("emp_doj", emp.getEmp_doj());
    
    			inParams.put("dept_no", dept.getDept_no());
    			inParams.put("dept_name", dept.getDept_name());
    			
    			logger.info("createInquiry input parameters:"+inParams);
    			
    			
    			Map<Object, Object> results = (Map<Object, Object>)procedure.execute(inParams);			
    			emp_id = (String)results.get("return_employee_id");
    			logger.info("New Inquiry inserted with inquiry_id: "+inquiry_id);
    			if(null==emp_id){
    				logger.error("An error occured while adding new Inquiry");
    				throw new Exception();
    			}	
    			
    		}
    		catch (DataAccessException ex) {
    			ex.printStackTrace();
                logger.error("DataAccessException in createEmpWithDept: "+ex.toString());
                throw new Exception();
    		}
    		catch (Exception e) {
    			logger.error("Exception in createEmpWithDept: "+e.toString());
    			throw new Exception();
    		}
    		return emp_id;
    	}
    This code works fine, but I'd like to know whether can I use SqlParameterSource for executing this procedure rather than taking values from Employee and Department beans..

    Ex:
    Code:
        SqlParameterSource empSource =
    new BeanPropertySqlParameterSource(emp);
    
        SqlParameterSource deptSource =
    new BeanPropertySqlParameterSource(dept);
    
    Map<Object, Object> results = (Map<Object, Object>)procedure.execute(empSource,deptSource);			
    			emp_id = (String)results.get("return_employee_id");
    Please give me your advice, how to change this code so that we can execute procedure with BeanPropertySqlParameterSource rather than taking each property from bean class.

    Thanks for your help in advance.

    Regards,
    Sharath Karnati.
Working...
X