mybatis Sqlserver 存储过程 结果集

qqruser

　　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