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