We help IT Professionals succeed at work.

Multiple Stored procedures in one DAO using Spring JDBC?

heyday2004
heyday2004 asked
on
Here is the sample stored procedure in Spring JDBC. My question is how to add another stored procedure to JdbcActorDao? For example, I have another method: readActorByAge(int Age)? Seems below JdbcActorDao is bound to Procedure read_actor?
 
public class JdbcActorDao implements ActorDao {
    private SimpleJdbcTemplate simpleJdbcTemplate;
    private SimpleJdbcCall procReadActor;
 
    public void setDataSource(DataSource dataSource) {
        this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
        this.procReadActor =
                new SimpleJdbcCall(dataSource)
                        .withProcedureName("read_actor");
    }
 
    public Actor readActor(Long id) {
        SqlParameterSource in = new MapSqlParameterSource()
                .addValue("in_id", id);
        Map out = procReadActor.execute(in);
        Actor actor = new Actor();
        actor.setId(id);
        actor.setFirstName((String) out.get("out_first_name"));
        actor.setLastName((String) out.get("out_last_name"));
        actor.setBirthDate((Date) out.get("out_birth_date"));
        return actor;
    }
 
Thanks for any explanation.
Comment
Watch Question


Jsust try this
public void setSimpleJdbcTemplate(DataSource dataSource) {
        this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
        this.procReadActor =
                new SimpleJdbcCall(dataSource)
                        .withProcedureName("read_actor");
    }


Author

Commented:
how to add  and execute another stored procedure in the example? thanks.
Mick BarryJava Developer
Top Expert 2010
Commented:
Create another instance of SimpleJdbcCall
getting procedure name as dynamically !!

Author

Commented:
sorry, i'm a newbie, suppose if I want to add execute another stored procedure: read_actor_2, what is the  sample code to do this in the above Spring provided example? Seems setDataSource is bound with one stored procedure (default?), that's why i dont quite understand. Thanks.
Mick BarryJava Developer
Top Expert 2010
Commented:
you can create another instance of SimpleJdbcCall, or calling withProcedureName() should also work.

Check this
package com.test;  

   

import java.sql.ResultSet;  

import java.sql.SQLException;  

import java.sql.Types;  

import java.util.List;  

import java.util.Map;  

   

import javax.sql.DataSource;  

   

import org.springframework.beans.factory.annotation.Autowired;  

import org.springframework.beans.factory.annotation.Required;  

import org.springframework.context.ApplicationContext;  

import org.springframework.context.support.ClassPathXmlApplicationContext;  

import org.springframework.jdbc.core.SqlOutParameter;  

import org.springframework.jdbc.core.simple.ParameterizedRowMapper;  

import org.springframework.jdbc.core.simple.SimpleJdbcCall;  

import org.springframework.stereotype.Repository;  

   

@Repository("countryDataManager")  

public class CountryDataManagerImpl {  

   

    private SimpleJdbcCall countryProcedure;  

    private SimpleJdbcCall procedureWithTwoResultSet;  

   

    @Autowired 

    @Required 

    public void setDataSource(DataSource dataSource) {  

        String procedureName = "QUALIFIER.PROCNAME";  

        String procedureWithMultipleRS = "QUALIFIER.PROCNAME";  

   

        countryProcedure = new SimpleJdbcCall(dataSource)  

        .withoutProcedureColumnMetaDataAccess()  

        .withProcedureName(procedureName)  

        .declareParameters(new SqlOutParameter("RETURNCODE", Types.INTEGER))  

        .declareParameters(new SqlOutParameter("RETURNMSG", Types.VARCHAR))  

        .returningResultSet("countries", new ParameterizedRowMapper<Country>() {  

            public Country mapRow(ResultSet rs, int rowNum)  

                    throws SQLException {  

                Country country = new Country();  

                country.setId(rs.getInt(1));  

                country.setName(rs.getString(2));  

                return country;  

            }  

        });  

        procedureWithTwoResultSet = new SimpleJdbcCall(dataSource)  

        .withoutProcedureColumnMetaDataAccess()  

        .withProcedureName(procedureWithMultipleRS)  

        .declareParameters(new SqlOutParameter("RETURNCODE", Types.INTEGER))  

        .declareParameters(new SqlOutParameter("RETURNMSG", Types.VARCHAR))  

        .returningResultSet("result1", new ParameterizedRowMapper<String>() {  

            public String mapRow(ResultSet rs, int rowNum)  

                    throws SQLException {  

                return rs.getString(1);  

            }  

        })  

        .returningResultSet("result2", new ParameterizedRowMapper<String>() {  

            public String mapRow(ResultSet rs, int rowNum)  

                    throws SQLException {  

                return rs.getString(1);  

            }  

        });  

    }  

   

    public void readOutParameters() {  

        Map result = countryProcedure.execute();  

        System.out.println("RETURNCODE: " + result.get("RETURNCODE"));  

        System.out.println("RETURNMSG: " + result.get("RETURNMSG"));  

    }  

   

    public void readResultSet() {  

        Map result = countryProcedure.execute();  

        System.out.println(result.get("countries"));  

    }  

   

    public void readMultipleResultSets() {  

        Map result = procedureWithTwoResultSet.execute();  

        System.out.println("result 1: ");  

        System.out.println(result.get("result1"));  

   

        System.out.println("result 2:");  

        System.out.println(result.get("result2"));  

    }  

   

    public static void main(String args[]) {  

        ApplicationContext context = new ClassPathXmlApplicationContext(  

                new String[] { "applicationContext.xml" });  

        CountryDataManagerImpl countryDataManager = (CountryDataManagerImpl) context  

                .getBean("countryDataManager");  

        countryDataManager.readOutParameters();  

        countryDataManager.readResultSet();  

        countryDataManager.readMultipleResultSets();  

   

    }  

}

Open in new window

Author

Commented:
Sorry, I didnt have chance to come back here. Many thanks for the great answers.