mybatis中调用输出参数为游标的存储过程，及存储函数的调用

为什么80%的码农都做不了架构师？>>>   

1.mybatis中调用存储过程

存储过程

CREATE OR REPLACE procedure getStudentByScore(scores IN NUMBER,students OUT SYS_REFCURSOR) is
begin
OPEN students FOR SELECT * from student WHERE score = scores;
end getStudentByScore;

mapper接口

void selectStudentByscore(Map<String, Object> params);

mapper映射

<select id="selectStudentByscore" statementType="CALLABLE" parameterType="map">
		<![CDATA[
			call getStudentByScore(
			#{scores,mode=IN,jdbcType=INTEGER},
			#{students,mode=OUT,jdbcType=CURSOR,javaType=java.sql.ResultSet,resultMap=baseResultMap}
			)
		]]>
</select> 	

测试

public static List<Student> procedureTest(int scores) throws IOException {
	String resource = "sqlMapConfig.xml";
	InputStream inputStream = Resources.getResourceAsStream(resource);
	SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
	SqlSession session = sessionFactory.openSession();
	StudentMapper studentMapper = session.getMapper(StudentMapper.class);
	Map<String, Object> parameter = new HashMap<String, Object>();
	parameter.put("scores", scores);
	studentMapper.selectStudentByscore(parameter);
	return (List<Student>)parameter.get("students");
}

public static void main(String[] args) throws IOException {
    Student student = procedureTest(111).get(1);
    System.out.println(student.getUsername());
}

 

mybatis 中调用存储函数

存储过程

create or replace function queryEmpIncome(eno in number) return number
as
   psal emp.sal%type;
   pcomm emp.comm%type;
begin
   select sal,comm into psal,pcomm from emp where empno=eno;
   return psal*12+nvl(pcomm,0);
end;
/

mapper接口

void selectEmpSalByEmpno(Map<String, Object> params);

mapper映射

<select id="selectEmpSalByEmpno" statementType="CALLABLE" parameterType="map">
		<![CDATA[
			{#{salCount,mode=OUT,jdbcType=INTEGER} = call queryEmpIncome(
				#{eno,mode=IN,jdbcType=INTEGER}
			)}
		]]>
</select>

测试

public static int selectEmpSalByEmpnoTest(int empno) throws IOException {
	String resource = "sqlMapConfig.xml";
	InputStream inputStream = Resources.getResourceAsStream(resource);
	SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
	SqlSession session = sessionFactory.openSession();
	StudentMapper studentMapper = session.getMapper(StudentMapper.class);
	Map<String, Object> parameter = new HashMap<String, Object>();
	parameter.put("eno", empno);
	studentMapper.selectEmpSalByEmpno(parameter);
	return (Integer)parameter.get("salCount");
}

public static void main(String[] args) throws IOException {
	int salCount = selectEmpSalByEmpnoTest(7369);
	System.out.println(salCount);
}

 

转载于:https://my.oschina.net/hfzj/blog/736631