Calling Stored Procedure Using Spring JdbcTemplate

MySQL stored Procedure

DELIMITER $$

DROP PROCEDURE IF EXISTS `test1`.`tenderinsertproc` $$
CREATE PROCEDURE `tenderinsertproc`(IN tenderNo int(10),dName varchar(45),tDesc varchar(128),tDate datetime,isp tinyint(1),isa tinyint(1),isl tinyint(1))
BEGIN
  insert into tender_master(tender_no, department_name, tender_description, tender_date, is_pending, is_archive, is_live) values(tenderNo,dName,tDesc,tDate,isp,isa,isl);
END $$

DELIMITER ;

TenderInsertStoredProcedure.java

package com.webapp;

import java.sql.Types;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.StoredProcedure;

public class TenderInsertStoredProcedure extends StoredProcedure {

    TenderInsertStoredProcedure() {
    }

    TenderInsertStoredProcedure(DataSource dataSource) {
        this.setDataSource(dataSource);
        this.setSql("tenderinsertproc");
        this.declareParameter(new SqlParameter("tenderNo", Types.INTEGER));
        this.declareParameter(new SqlParameter("dName", Types.VARCHAR));
        this.declareParameter(new SqlParameter("tDesc", Types.VARCHAR));
        this.declareParameter(new SqlParameter("tDate", Types.DATE));
        this.declareParameter(new SqlParameter("isp", Types.TINYINT));
        this.declareParameter(new SqlParameter("isa", Types.TINYINT));
        this.declareParameter(new SqlParameter("isl", Types.TINYINT));
        this.compile();
    }

    public void TenderCreateProcedure() {

        try {
            Map inParams = new HashMap();
            inParams.put("tenderNo", 52);
            inParams.put("dName", "taher");
            inParams.put("tDesc", "JavaHunter");
            inParams.put("tDate", new Date());
            inParams.put("isp", Boolean.FALSE);
            inParams.put("isa", Boolean.FALSE);
            inParams.put("isl", Boolean.TRUE);
ArrayList<LinkedHashMap<String, Object>> list = (ArrayList<LinkedHashMap<String, Object>>) execute(inParams).get("#result-set-1");
            if(!list.isEmpty()){
            for (LinkedHashMap<String, Object> linkedHashMap : list) {
                  linkedHashMap.get("exact name of returning field");
            }
        } catch (Exception e) {
            System.out.println("Error Man : " + e);
        }
    }

    public static void main(String[] args) {
        try {
            ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
            BasicDataSource dataSource = (BasicDataSource) context.getBean("dataSource");
            TenderInsertStoredProcedure tisp = new TenderInsertStoredProcedure(dataSource);
            tisp.TenderCreateProcedure();
        } catch (Exception exception) {
            System.out.println("Eroooorrror : " + exception);
        }
    }
}
Advertisements
This entry was posted in Hibernate, Spring and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s