mybatis调用存储过程（带返回值）

mybatis xml文件设置：

1. 配置调用存储过程：select标签中配置：statement=“CELLABLE”
2. 标识参数:mode=IN/mode=OUT/mode=INOUT
3. 参数类型：jdbcType=“mybatis数据库对应格式”

mybatis注解配置：

1. @Select（value=“”）
2. 标识参数:mode=IN/mode=OUT/mode=INOUT
3. 参数类型：jdbcType=“mybatis数据库对应格式”
4. 配置调用存储过程：@Options(statementType = StatementType.CALLABLE)

带返回值：

1. 传入Map<String,Object>对象，在配置文件设置对应返回参数，自动映射到map中

不带返回值：

1. 可以不声明参数类型，类似select传参

存储过程创建1：

CREATE PROCEDURE `NewProc`(IN name VARCHAR(50),IN addr VARCHAR(100),IN contact VARCHAR(20),IN tel VARCHAR(12),IN  account VARCHAR(20),IN pwd VARCHAR(20), IN `character` TINYINT)
BEGIN
DECLARE loginid INT;
INSERT INTO 
t_login (login_account,login_pwd,login_character) VALUES(account,pwd,`character`);
SELECT login_id INTO loginid FROM t_login WHERE login_account = account AND login_pwd = pwd AND login_character = `character`;
INSERT INTO t_company (company_name,company_addr,company_contact,company_tel,company_login_id) VALUES (name,addr,contact,tel,loginid);
END;

存储过程创建2：

CREATE PROCEDURE `NewProc`(IN `companyid` INT,OUT result INT)
BEGIN
	DECLARE loginid INT;
	SELECT company_login_id INTO loginid FROM t_company WHERE company_id = companyid AND flag=0;
	UPDATE t_login SET flag = 1 WHERE login_id = `loginid` AND flag=0;
	UPDATE t_company SET flag = 1 WHERE company_id = companyid AND flag=0;
SET result = 1;
END;

注解配置SQL语句（map中存储输入参数）：

	@Select(value = "call add_company(" + "#{name,mode=IN,jdbcType=VARCHAR},"
			+ "#{addr,mode=IN,jdbcType=VARCHAR}," +
			"#{contact,mode=IN,jdbcType=VARCHAR}," +
			"#{tel,mode=IN,jdbcType=VARCHAR}," +
			"#{account,mode=IN,jdbcType=VARCHAR}," +
			"#{pwd,mode=IN,jdbcType=VARCHAR}," +
			"#{character,mode=IN,jdbcType=TINYINT}," +
			"#{reuslt,mode=OUT,jdbcType=TINYINT})")

	@Options(statementType = StatementType.CALLABLE)
	public Boolean insertCompanyBean(Map<String, Object> maop);

注解配置SQL语句（map中存入bean，bean中输入作为输入参数）

	@Select(value = "call add_company("
			+ "#{bean.name,mode=IN,jdbcType=VARCHAR},"
			+ "#{bean.addr,mode=IN,jdbcType=VARCHAR},"
			+ "#{bean.contact,mode=IN,jdbcType=VARCHAR},"
			+ "#{bean.tel,mode=IN,jdbcType=VARCHAR},"
			+ "#{bean.account,mode=IN,jdbcType=VARCHAR},"
			+ "#{bean.pwd,mode=IN,jdbcType=VARCHAR},"
			+ "#{bean.character,mode=IN,jdbcType=TINYINT},"
			+ "#{result,mode=OUT,jdbcType=TINYINT})")

	@Options(statementType = StatementType.CALLABLE)
	public Boolean insertCompanyBean(Map<String, Object> map);

xml Mapper配置SQL2语句：

	<select id="deleteCompanyBean" parameterType="java.util.Map" statementType="CALLABLE">
	{
	call
	delete_company(
	#{bean.id,mode=IN,jdbcType=BIGINT},#{result,mode=OUT,jdbcType=TINYINT})
	}
	</select>

测试类：

public class CompanyTest {
	/**
	 * 测试注解配置存储过程，map放输入参数
	 * @throws IOException
	 */
	@Test
	public void insertCompany() throws IOException {
		ApplicationContext context = new ClassPathXmlApplicationContext("config/applicationContext.xml");
		ICompanyDao dao = context.getBean(ICompanyDao.class);
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("name", "name");
		map.put("addr", "addr");
		map.put("contact", "contact");
		map.put("tel", "tel");
		map.put("account", "account");
		map.put("pwd", "pwd");
		map.put("character", 1);
		System.out.println(dao.insertCompanyBean(map));
		System.out.println(map.get("reuslt"));
	}

	/**
	 * 测试注解存储过程：map中传入bean，bean中存放输入参数
	 * @throws IOException
	 */
	@Test
	public void insertCompany2() throws IOException {
		ApplicationContext context = new ClassPathXmlApplicationContext("config/applicationContext.xml");
		ICompanyDao dao = context.getBean(ICompanyDao.class);
		Map<String, Object> map = new HashMap<String, Object>();
		CompanyBean bean = new CompanyBean();
		bean.setName("name");
		bean.setAddr("addr");
		bean.setTel("tel");
		bean.setContact("contact");
		bean.setPwd("pwd");
		bean.setCharacter(1);
		map.put("bean", bean);
		System.out.println(dao.insertCompanyBean(map));
		System.out.println(map.get("reuslt"));
	}

	/**
	 * 测试xml配置mapper存储过程：map中传入bean，bean中存放输入参数
	 * @throws IOException
	 */
	@Test
	public void insertCompany3() throws IOException {
		ApplicationContext context = new ClassPathXmlApplicationContext("config/applicationContext.xml");
		ICompanyDao dao = context.getBean(ICompanyDao.class);
		Map<String, Object> map = new HashMap<String, Object>();
		CompanyBean bean = new CompanyBean();
		bean.setId(1);
		map.put("bean", bean);
		System.out.println(dao.deleteCompanyBean(map));
		System.out.println(map.get("result"));
	}
}