CODEWITHSUNDEEP
javasqlxmlmybatisibatis
ReyAnthonyRenacia
ReyAnthonyRenacia

Reputation: 17613

Call a Stored Procedure in MyBatis

Im using SqlServer and MyBatis.

Been looking for several examples online but nothing seems to fit my need. The stored_procedure Im calling accepts 5 parameters and returns 1 value- for example

give_discount (FirstName, LastName,Gender, DateOfBirth, Age )

all of which are strings. The stored_procedure then returns a STRING which is supposed to tell you how much discount you're entitled to.

Those five attributes (FirstName, LastName, DateOfBirth, Age, Gender) are stored in a POJO named Person.java

So let's say I created an instance of Person.java 

Person person1 = new Person();
person1.setFirstName("Joe");
person1.setLastName("Higashi");
person1.setGender("M");
person1.setDateOfBirth("1990-01-01");
person1.setAge("29");

this is my mapperXML

 <resultMap id = "discountValueParams"  type="com.test.Person">
      <result property = "FirstName"    column = "FirstName"/>
      <result property = "LastName"     column = "LastName"/>
      <result property = "Gender"       column = "Gender"/>
      <result property = "DateOfBirth"  column = "DateOfBirth"/>
      <result property = "Age"          column = "Age"/>
 </resultMap>

So the question is:

How do I pass person1 to my stored_procedure so it will use its currently assigned values and return a value?

Is this correct in XML mapper?

<select id = "getDiscountValue" resultType = "String" parameterType = "com.test.Person" statementType = "CALLABLE">
  {call dbo.get_discount_value(
       #{FirstName,  jdbcType = NVARCHAR},
       #{LastName,  jdbcType = NVARCHAR},
       #{Gender,  jdbcType = NVARCHAR},
       #{DateOfBirth,  jdbcType = NVARCHAR},
       #{"Age",  jdbcType = NVARCHAR}
   )}

And the java method is:

public String getDiscountMethod( Person person) {

            String discountValue= "";

            Map<String, Object> map = new HashMap<>();
            map.put("FirstName", person.getFirstName  );
            map.put("LastName", person.getLastName());
            map.put("Gender", person.getGender());
            map.put("DateOfBirth", person.getBday());
            map.put("Age", person.getAge());

            SqlSession session = MyBatisUtil.getSqlSessionFactory().openSession();
            try {
                XmlMapper xmlmapper = session.getMapper(XmlMapper.class);
                discountValue= applicationMapper.getDiscountValue(map) ;
                session.commit();
            } catch (Exception e) {
                LOGGER.error(e);
            } finally {
                session.close();
            }
            return discountValue;

} 

mapperInterface.java
public interface UpstreamXmlMapper {
     public String callStoredProcedure (StoredProcRef storedProcRef);
}

Given these lines, how do I make the proper call? I know Im lacking and obviously may have erroneously implemented something. Need proper guidance.

Updated:

Stored Procedure variables in SqlServer:

ALTER PROCEDURE [dbo].[get_discount_for_customer]
    @formCode nvarchar(50),
    @locationCode nvarchar(10),
    @gender nvarchar(1),
    @dateOfBirth date,
    @labResultFormId bigint
AS
..SP body ..

**

**

Upvotes: 2

Views: 19445

Answers (1)

ave
ave

Reputation: 3594

I think I got it.
Your procedure returns 7 columns from a query, right?

I'll explain using the following simple table and a procedure.

create table users (
  id int,
  name varchar(20),
  note varchar(20)
);

create procedure MY_PROC 
  @ARG1 varchar(10)
as
begin
  select * from users where name = @ARG1;
end

The procedure returns 3 columns (id, name and note).
I'll show you a way to return only note column.

The mapper method would look as follows (you can use POJO instead of Map).

List<Map<String, ?>> myProc(String arg1);

If it always returns one row, it can be...

Map<String, ?> myProc(String arg1);

And here are the result map and the statement:

<resultMap type="map" id="userRM" autoMapping="false">
  <id column="id" />
  <result column="note" property="note" />
</resultMap>

<select id="myProc" statementType="CALLABLE" resultMap="userRM">
  {call MY_PROC(#{arg1, mode=IN, jdbcType=VARCHAR})}
</select>

The returned map(s) will have one entry with a key "note".

  • <id /> may not be necessary, but it's a good practice to supply it.
  • autoMapping="false" is to avoid unnecessary columns to be auto-mapped.

An executable demo project:
https://github.com/harawata/mybatis-issues/tree/master/so-58802623

Upvotes: 5

Related Questions