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;
	}
}










转载于:https://www.cnblogs.com/jzdwajue/p/6924053.html

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值