基于Mysql的存储过程的 SSM实现

首先创建存储过程，如下：

获取总条目：

drop procedure if exists sp_total;
create procedure sp_total(IN $name varchar(20))
begin
  if($name is null) then
   select count(id) from t_user;
  else
   select count(id) from t_user where name like concat('%',$name,'%');
  end if;
end
call sp_total('是');

分页查询：

drop procedure if exists sp_plist;
create procedure sp_plist(IN $offset int,IN $pagesize int,IN $name varchar(20))
begin
  if($name is null) then
     select * from t_user limit $offset,$pagesize;
   else
      select * from t_user where name like  concat('%',$name,'%')
       limit $offset,$pagesize;
  end if;
end
call sp_plist(5,3,'否');

添加和修改：

drop procedure if exists sp_save;
create procedure sp_save(IN $id int,IN $name varchar(20),IN $age int)
begin
  if exists (select * from t_user where id=$id) then
     update t_user set name=$name,age=$age where id=$id;
  else
      insert into t_user(name,age) values($name,$age);
 end if;
end
 
call sp_save(6,'猪八戒',21);

删除：

BEGIN
  if exists (select * from t_user where id=$id)
  then
    delete from t_user where id=$id;
 end if;	

END

查询ID：

drop procedure if exists sp_find;
create procedure sp_find(IN $id int)
 select * from t_user where id=$id;

call sp_find(3);

实体类：

public class User {

	private Integer id;
	private String name;
	private Integer age;
   //省去 get set
}

Mapper：

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.mapping.StatementType;

import com.bw.ssm.entity.User;
public interface UserMapper {

	@Select("{ call sp_total(#{name})}")
	@Options(statementType=StatementType.CALLABLE)
	int getTotal(String name);
	
	
	@Select("{call sp_plist(#{offset},#{pagesize},#{mohu})}")
	@Options(statementType=StatementType.CALLABLE)
	List<User> plist(@Param("offset")int offset,@Param("pagesize")int pagesize,@Param("mohu")String mohu);
	
	@Select("{ call sp_list()}")
	@Options(statementType=StatementType.CALLABLE)
	List<User> findAll();
	
	@Options(statementType=StatementType.CALLABLE)
	@Insert("{call sp_save(#{id},#{name},#{age})}")
	int save(User user);
	
	@Options(statementType= StatementType.CALLABLE)
	@Select("{call sp_find(#{id})}")
	User findById(int id);
	
	@Options(statementType= StatementType.CALLABLE)
	@Delete("{call sp_del(#{id})}")
	int delete(int id);
}