mybatis Sqlserver 存储过程 结果集

 

Sqlserver返回结果集的存储过程调用方式：

 

一、存储过程：

 

create procedure selectCount
 @num int =0
 as
 begin
 SELECT COUNT(0) FROM mytable
 end
 
 create procedure selectId
@num int =0
as
begin
SELECT id FROM mytable
end

 

 

二、Mybatis的映射文件

 

<select id="selectSpreplshowcmds" parameterType="java.lang.Integer" statementType="CALLABLE"  resultMap="BaseResultMap">
           { call sp_replshowcmds(#{maxtrans,jdbcType=INTEGER,mode=IN})}
    </select>

    <select id="selectCount" resultType="java.lang.Integer">
        SELECT COUNT(0) FROM mytable;
    </select>

    <select id="selectCountProcedure" parameterType="java.lang.Integer" statementType="CALLABLE"  resultType="java.lang.Integer">
        { call selectCount(#{num,jdbcType=INTEGER,mode=IN})}
    </select>

    <select id="selectId" parameterType="java.lang.Integer" statementType="CALLABLE"  resultType="java.lang.Integer">
        { call selectId(#{num,jdbcType=INTEGER,mode=IN})}
    </select>

 

其实就是sql不一样 其他调用都一样的。注意加上 statementType="CALLABLE"

Mapper接口

@Repository
public interface SpreplshowcmdsMapper {


    List<Spreplshowcmds> selectSpreplshowcmds(Integer maxtrans);

    Integer selectCount();

    Integer selectCountProcedure(Integer num);

    List<Integer> selectId(Integer num);
}

 

service接口

public interface SpreplshowcmdsService {

    List<Spreplshowcmds> selectSpreplshowcmds(Integer maxtrans);

    Integer selectCount();
    Integer selectCountProcedure(Integer num);

    List<Integer> selectId(Integer num);

}

 

service实现

@Service("spreplshowcmdsService")
public class SpreplshowcmdsServiceImpl implements SpreplshowcmdsService {

    @Resource(name ="spreplshowcmdsMapper")
    private SpreplshowcmdsMapper spreplshowcmdsMapper;

    @Override
    public List<Spreplshowcmds> selectSpreplshowcmds(Integer maxtrans) {

        return spreplshowcmdsMapper.selectSpreplshowcmds(maxtrans);
    }

    @Override
    public Integer selectCount() {
        return spreplshowcmdsMapper.selectCount();
    }

    @Override
    public Integer selectCountProcedure(Integer num) {
        return spreplshowcmdsMapper.selectCountProcedure(1);
    }

    @Override
    public List<Integer> selectId(Integer num) {
        return spreplshowcmdsMapper.selectId(1);
    }
}

 

测试调用

public class App {

    public static void main(String[] args) {


        ConfigurableApplicationContext context = new ClassPathXmlApplicationContext("classpath:applicationContext.xml");
        SpreplshowcmdsService spreplshowcmdsService = ( SpreplshowcmdsService)context.getBean("spreplshowcmdsService");
        Integer count= spreplshowcmdsService.selectCount();
        System.out.println(count);

        Integer countProcedure = spreplshowcmdsService.selectCountProcedure(1);
        System.out.println(countProcedure);

        List<Integer> idList = spreplshowcmdsService.selectId(1);
        System.out.println(idList);


        List<Spreplshowcmds> spreplshowcmdsList = spreplshowcmdsService.selectSpreplshowcmds(100);
        System.out.println("qqqqqqq");
        System.out.println(spreplshowcmdsList.size());

    }


}

 

可以参考mybatis的源码，

https://github.com/mybatis/mybatis-3