MyBatis执行动态sql及存储过程

1 利用MyBatis进行模糊查询

    在利用MyBatis进行模糊查询时候，需要用到concat，注意在Oracle中concat连接的是两个参数，不是三个参数；

     持久化类：

package com.web.entity;

public class Student {
	private Integer studentId;
	private String studentName;
	private Classes classes;
	public Integer getStudentId() {
		return studentId;
	}
	public void setStudentId(Integer studentId) {
		this.studentId = studentId;
	}
	public String getStudentName() {
		return studentName;
	}
	public void setStudentName(String studentName) {
		this.studentName = studentName;
	}
	public Classes getClasses() {
		return classes;
	}
	public void setClasses(Classes classes) {
		this.classes = classes;
	}
	
}

dao中方法：

package com.web.dao;

import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import com.web.entity.Student;

public class StudentDao {
	private int res=-1;
	private SqlSession sqlSession=null;
	public  SqlSession getSqlSession(){
		
		try{
			String resource="mybatis-config.xml";
			InputStream is=Resources.getResourceAsStream(resource);
			SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(is);
			sqlSession=sqlSessionFactory.openSession(true);
			
			
		}catch(Exception e){
			System.out.println("出错");
			e.printStackTrace();
		}
		return sqlSession;
	}
	
	
	public Student findTStudentByName(Student student){
		sqlSession=getSqlSession();
		Student s=sqlSession.selectOne("lk", student);
		return s;
	}
	
	/*public int getCount(Integer classesid){
		int result=-1;
		Map<String, Integer> map=new HashMap<String, Integer>();
		map.put("classesid", classesid);
		map.put("studentcount", -1);
		sqlSession=getSqlSession();
		sqlSession.selectOne("gc", map);
		result=map.get("studentcount");
		return result;
	}*/
}

映射文件：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.pb.dao.StudentDao">
<!-- 	<resultMap type="Student" id="ts">
		<id property="studentId" column="studentId" />
		<result property="studentName" column="studentName" />
		<association property="classes" column="classesId"
			javaType="Classes">
			<id property="classesId" column="classesId" />
			<result property="classesName" column="classesName" />
		</association>
	</resultMap>
	<select id="selectonetoone" resultMap="ts" parameterType="Student">
		select
		s.studentid,s.studentname,c.classesname
		from
		student s
		left join classes c
		on s.classesid=c.classesid
		where s.studentid=#{studentId}
	</select> -->
	
	<select id="lk" resultMap="ts" parameterType="Student">
		select * from student s where s.studentname like concat(concat('%','h'),'%')
	</select>
	
	<!-- <select id="gc" parameterMap="gm" statementType="CALLABLE">
		call pro_student(?,?)
	</select>
	<parameterMap type="java.util.Map" id="gm">
		<parameter property="classesid" mode="IN" jdbcType="INTEGER"/>
		<parameter property="studentcount" mode="OUT" jdbcType="INTEGER"/>
	</parameterMap> -->
	
</mapper>

2 利用MyBatis执行存储过程：

   在映射文件中定义一个parameterMap作为参数，设置statementType="CALLABLE"，过程的参数放置？站位符。

   在方法中也创建一个Map，放置参数，SqlSession调用方法的时候传入该Map即可。

存储过程：

create or replace procedure pro_student(
cid in number,
coun out number
)is
begin
select count(*) into coun from student s where s.classesid=cid;
end;

配置文件：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.pb.dao.StudentDao">
<!-- 	<resultMap type="Student" id="ts">
		<id property="studentId" column="studentId" />
		<result property="studentName" column="studentName" />
		<association property="classes" column="classesId"
			javaType="Classes">
			<id property="classesId" column="classesId" />
			<result property="classesName" column="classesName" />
		</association>
	</resultMap>
	<select id="selectonetoone" resultMap="ts" parameterType="Student">
		select
		s.studentid,s.studentname,c.classesname
		from
		student s
		left join classes c
		on s.classesid=c.classesid
		where s.studentid=#{studentId}
	</select> -->
	
	<select id="lk" resultMap="ts" parameterType="Student">
		select * from student s where s.studentname like concat(concat('%','h'),'%')
	</select>
	
	<select id="gc" parameterMap="gm" statementType="CALLABLE">
		call pro_student(?,?)
	</select>
	<parameterMap type="java.util.Map" id="gm">
		<parameter property="classesid" mode="IN" jdbcType="INTEGER"/>
		<parameter property="studentcount" mode="OUT" jdbcType="INTEGER"/>
	</parameterMap>
	
</mapper>

dao中方法：

package com.web.dao;

import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import com.web.entity.Student;

public class StudentDao {
	private int res=-1;
	private SqlSession sqlSession=null;
	public  SqlSession getSqlSession(){
		
		try{
			String resource="mybatis-config.xml";
			InputStream is=Resources.getResourceAsStream(resource);
			SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(is);
			sqlSession=sqlSessionFactory.openSession(true);
			
			
		}catch(Exception e){
			System.out.println("出错");
			e.printStackTrace();
		}
		return sqlSession;
	}
	
	
	public Student findTStudentByName(Student student){
		sqlSession=getSqlSession();
		Student s=sqlSession.selectOne("lk", student);
		return s;
	}
	
	public int getCount(Integer classesid){
		int result=-1;
		Map<String, Integer> map=new HashMap<String, Integer>();
		map.put("classesid", classesid);
		map.put("studentcount", -1);
		sqlSession=getSqlSession();
		sqlSession.selectOne("gc", map);
		result=map.get("studentcount");
		return result;
	}
}

MyBatis执行动态sql及存储过程

标签：mybatis   sql   存储过程