在做测试的时候本来加入oscache ,但是在使用的时候 ,发下 初始化异常,不知道需要注意那些地方, 希望 "da xia " 们 留言 谢谢!!!!
介绍
目录
MyBatis-config.xml 中 set 的说明 []: 表示 可能的不太正确
表,序列 ,存储过程 的创建
存储过程
测试
序列
学生表
班级表
mybatis-config.xml
mybatis-config.properties
Student.java
StudentMapper.xml
Classes.java
ClassesMapper.xml
TestStudentAndClasses.java
介绍
- 1.例子中包含了 mybatis 的常用sql的写法
- 2.动态sql 的应用
- 3.存储过程的使用
目录
![](http://dl.iteye.com/upload/attachment/0076/9292/4dddfe5e-b817-3e2d-82b3-bb5fdebb7bd1.jpg)
MyBatis-config.xml 中 set 的说明 []: 表示 可能的不太正确
- <!-- 配置设置 -->
- <settings>
- <!-- 配置全局性 cache 的 ( 开 / 关) default:true -->
- <setting name="cacheEnabled" value="true"/>
- <!-- 是否使用 懒加载 关联对象 同 hibernate中的延迟加载 一样 default:true -->
- <setting name="lazyLoadingEnabled" value="true"/>
- <!-- [当对象使用延迟加载时 属性的加载取决于能被引用到的那些延迟属性,否则,按需加载(需要的是时候才去加载)] -->
- <setting name="aggressiveLazyLoading" value="true"/>
- <!-- 是否允许单条sql 返回多个数据集 (取决于驱动的兼容性) default:true -->
- <setting name="multipleResultSetsEnabled" value="true"/>
- <!-- 是否可以使用列的别名 (取决于驱动的兼容性) default:true-->
- <setting name="useColumnLabel" value="true"/>
- <!--允许JDBC 生成主键。需要驱动器支持。如果设为了true,这个设置将强制使用被生成的主键,有一些驱动器不兼容不过仍然可以执行。 default:false-->
- <setting name="useGeneratedKeys" value="false"/>
- <!--指定 MyBatis 如何自动映射 数据基表的列 NONE:不隐射 PARTIAL:部分 FULL:全部-->
- <setting name="autoMappingBehavior" value="PARTIAL"/>
- <!-- 这是默认的执行类型
- SIMPLE :简单
- REUSE:执行器可能重复使用prepared statements 语句
- BATCH:执行器可以重复执行语句和批量更新
- -->
- <setting name="defaultExecutorType" value="SIMPLE"/>
- <!-- 设置驱动等待数据响应的超时数 默认没有设置-->
- <setting name="defaultStatementTimeout" value="25000"/>
- <!-- [是否启用 行内嵌套语句 defaut:false] -->
- <setting name="safeRowBoundsEnabled" value="false"/>
- <!-- [是否 启用 数据中 A_column 自动映射 到 java类中驼峰命名的属性 default:fasle] -->
- <setting name="mapUnderscoreToCamelCase" value="false"/>
- <!-- 设置本地缓存范围 session:就会有数据的共享 statement:语句范围 (这样就不会有数据的共享 ) defalut:session -->
- <setting name="localCacheScope" value="SESSION"/>
- <!-- 设置但JDBC类型为空时,某些驱动程序 要指定值,default:other -->
- <setting name="jdbcTypeForNull" value="OTHER"/>
- <!-- 设置触发延迟加载的方法 -->
- <setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>
- </settings>
表,序列 ,存储过程 的创建
存储过程
- create or replace procedure pro_getAllStudent
- (
- v_sid number,
- v_sname varchar2,
- userList_cursor out sys_refcursor
- )
- as
- begin
- update student set sname=v_sname where sid=v_sid;
- open userList_cursor for select* from student;
- end;
测试
- SQL> declare
- 2 v_student_row student%rowtype;
- 3 v_sid student.sid%type:=11;
- 4 v_sname student.sname%type:='张浩';
- 5 v_student_rows sys_refcursor;
- 6 begin
- 7 pro_getAllStudent(v_sid,v_sname,v_student_rows);
- 8 loop
- 9 fetch v_student_rows into v_student_row;
- 10 exit when v_student_rows%notfound;
- 11 Dbms_Output.put_line('第'||v_student_rows%rowcount||'行,学生id'||v_student_row.sid||'--姓名:'||v_student_row.sname);
- 12 end loop;
- 13 close v_student_rows;
- 14 end;
- 15 /
序列
- -- Create sequence
- create sequence STUDENT_SEQ
- minvalue 1
- maxvalue 999999999999999999999999999
- start with 32
- increment by 1
- cache 20;
学生表
- create table STUDENT
- (
- SID NUMBER(8) primary key not null,
- SNAME VARCHAR2(20) not null,
- MAJOR VARCHAR2(100),
- BIRTH DATE,
- SCORE NUMBER(6,2),
- CID NUMBER(8),
- STATUS CHAR(3)
- )
班级表
- -- Create table
- create table CLASSES
- (
- CID NUMBER(8) primary key not null,
- CNAME VARCHAR2(20) not null,
- TEACHER VARCHAR2(25),
- CREATEDATE DATE
- )
mybatis-config.xml
- <?xml version="1.0" encoding="UTF-8"?>
- <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
- "http://mybatis.org/dtd/mybatis-3-config.dtd">
- <configuration>
- <!-- 配置的元素顺序 properties?, settings?, typeAliases?, typeHandlers?,
- objectFactory?, objectWrapperFactory?, proxyFactory?, plugins?,
- environments?, databaseIdProvider?, mappers -->
- <!-- 使用属性文件 而且可以在这里这是 覆盖文件中的值 -->
- <properties resource="mybatis-config.properties">
- <!--
- <property name="username" value="admin"/>
- <property name="password" value="123456"/>
- -->
- </properties>
- <!-- 别名的配置 -->
- <typeAliases>
- <typeAlias type="com.mybatis.student.Student" alias="Student"/>
- <typeAlias type="com.mybatis.classes.Classes" alias="Classes"/>
- <!--
- 也可以使用 包范围来配置
- <package name="com.mybatis"/>
- -->
- </typeAliases>
- <environments default="development">
- <environment id="development">
- <transactionManager type="JDBC"/>
- <dataSource type="POOLED">
- <property name="driver" value="${driver}"/>
- <property name="url" value="${url}"/>
- <property name="username" value="${username}"/>
- <property name="password" value="${password}"/>
- </dataSource>
- </environment>
- </environments>
- <mappers>
- <mapper resource="com/mybatis/student/StudentMapper.xml"/>
- <mapper resource="com/mybatis/classes/ClassesMapper.xml"/>
- </mappers>
- </configuration>
mybatis-config.properties
- driver=oracle.jdbc.driver.OracleDriver
- url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
- username=luob
- password=luob
Student.java
- package com.mybatis.student;
- import java.io.Serializable;
- import java.util.Date;
- import com.mybatis.classes.Classes;
- @SuppressWarnings("serial")
- public class Student implements Serializable {
- private int sid;
- private String sname;
- private String major;
- private Date birth;
- private float score;
- private int cid;
- private int status;
- //get set
StudentMapper.xml
- <?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.mybatis.student">
- <!-- <!ELEMENT mapper (
- cache-ref | cache | resultMap* | parameterMap* | sql*
- | insert* | update* | delete* | select* )+> -->
- <!-- 设置缓存 如果用户需要登录 需要设置这种类型 type=org.mybatis.caches.oscache.LoggingOSCache-->
- <cache eviction="FIFO" readOnly="true" size="256" flushInterval="60000"/>
- <!-- 定义可以重用的sql 代码片段 -->
- <sql id="studentColumns">sid,sname,score</sql>
- <!-- 自定义结果集 -->
- <resultMap type="Student" id="studentResultMap">
- <id property="sid" column="SID"/>
- <result property="sname" column="SNAME"/>
- <result property="score" column="SCORE"/>
- </resultMap>
- <resultMap type="Student" id="studentAllResultMap">
- <id property="sid" column="SID"/>
- <result property="sname" column="SNAME"/>
- <result property="major" column="MAJOR"/>
- <result property="birth" column="BIRTH"/>
- <result property="score" column="SCORE"/>
- <result property="cid" column="CID"/>
- <result property="status" column="STATUS"/>
- </resultMap>
- <!-- 只用构造函数 创建对象 对于那些 比较少的列 -->
- <resultMap type="Student" id="studentAndClassesResultMap">
- <constructor>
- <idArg column="SID" javaType="int"/>
- <arg column="SNAME" javaType="String"/>
- <arg column="SCORE" javaType="float"/>
- </constructor>
- <association property="classes" javaType="Classes" resultMap="com.mybatis.classes.classesResultMap"/>
- </resultMap>
- <select id="selectStudentAndClassBySname" parameterType="String" resultMap="studentAndClassesResultMap">
- select s.sid,s.sname,s.score,c.cid,c.cname,c.teacher,c.createdate from student s left join classes c on s.cid=c.cid where s.sname=#{sname}
- </select>
- <insert id="addStudentBySequence" parameterType="Student" >
- <selectKey keyProperty="sid" resultType="int" order="BEFORE">
- select STUDENT_SEQ.nextVal from dual
- </selectKey>
- insert into student(sid,sname,major,birth,score)
- values (#{sid},#{sname},#{major},#{birth},#{score})
- </insert>
- <insert id="addStudent" parameterType="Student">
- insert into student(sid,sname,major,birth,score)
- values (#{sid},#{sname},#{major},#{birth},#{score})
- </insert>
- <delete id="delStudentById" parameterType="int">
- delete student where sid=#{sid}
- </delete>
- <select id="queryAllStudent" resultType="Student" useCache="true" flushCache="false" timeout="10000">
- select * from student
- </select>
- <!-- 参数可以指定一个特定的数据类型 还可以使用自定类型处理: typeHandler=MyTypeHandler -->
- <select id="queryStudentByName" resultType="Student" parameterType="String">
- select * from student where sname like #{property,javaType=String,jdbcType=VARCHAR}
- </select>
- <!-- 参数可以指定一个特定的数据类型 对于数字类型 ,numericScale=2 用于设置小数类型 -->
- <select id="queryStudentById" resultType="Student" parameterType="int">
- select * from student where sid=#{property,javaType=int,jdbcType=NUMERIC}
- </select>
- <update id="updStudentById" parameterType="Student">
- update student
- <trim prefix="SET" suffixOverrides=",">
- <if test="sname !=null">sname=#{sname},</if>
- <if test="major !=null">majoir=#{major},</if>
- <if test="birth !=null">birth=#{birth},</if>
- <if test="score !=null">score=#{score}</if>
- </trim>
- where sid=#{sid}
- </update>
- <!-- 在这里 利用了 可重用的sql代码片段 -->
- <select id="selectMapResult" resultMap="studentResultMap" parameterType="String">
- select <include refid="studentColumns"/> from STUDENT where sname like #{sname}
- </select>
- <!-- Dynamic Sql 使用 if -->
- <select id="selectStudentByDynamicSql" parameterType="Student" resultType="Student">
- select * from student
- <where>
- <if test="sname !=null">
- sname like #{sname}
- </if>
- <if test="sid !=null">
- AND sid=#{sid}
- </if>
- </where>
- </select>
- <!-- 采用 OGNL 表达式 来配置动态sql 使用trim 去掉 where 中多余的 and 或者 or where choose when otherwise-->
- <select id="selectStudentByDynamicSqlChoose" parameterType="Student" resultType="Student">
- select * from student
- <trim prefix="WHERE" prefixOverrides="AND | OR ">
- <choose>
- <when test=" sname !=null and sname.length() >0 ">
- sname like #{sname}
- </when>
- <when test="sid !=null and sid>0">
- AND sid = #{sid}
- </when>
- <otherwise>
- AND status='1'
- </otherwise>
- </choose>
- </trim>
- </select>
- <!-- 使用foreach 遍历list 只能小写-->
- <select id="selectStudentByIds" resultType="Student">
- select * from student
- where sid in
- <foreach collection="list" item="itm" index="index" open="(" separator="," close=")">
- #{itm}
- </foreach>
- </select>
- <!-- 使用foreach 遍历arry 只能小写 -->
- <select id="selectStudentByIdArray" resultType="Student">
- select * from student
- where sid in
- <foreach collection="array" item="itm" index="index" open="(" separator="," close=")">
- #{itm}
- </foreach>
- </select>
- <parameterMap type="map" id="procedureParam">
- <parameter property="sid" javaType="int" jdbcType="NUMERIC" mode="IN" />
- <parameter property="sname" javaType="String" jdbcType="VARCHAR" mode="IN" />
- <parameter property="studentList" javaType="ResultSet" jdbcType="CURSOR" mode="OUT" resultMap="studentAllResultMap"/>
- </parameterMap>
- <!--传入map集合参数 ,调用 待用游标存储过程(先执行 修改后然后查询所有) -->
- <select id="getAllStudentAfterupdate" statementType="CALLABLE" useCache="true" parameterMap="procedureParam">
- {call LUOB.pro_getallstudent(?,?,?)}
- </select>
- </mapper>
Classes.java
- package com.mybatis.classes;
- import java.sql.Date;
- import java.util.List;
- import com.mybatis.student.Student;
- public class Classes {
- private int cid;
- private String cname;
- private String teacher;
- private Date createDate;
- private List<Student> students;
- //get set
ClassesMapper.xml
- <?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.mybatis.classes">
- <!-- 设置 缓存共享 -->
- <cache-ref namespace="com.mybatis.student"/>
- <resultMap type="Classes" id="classesResultMap">
- <id column="CID" property="cid"/>
- <result column="CNAME" property="cname"/>
- <result column="TEACHER" property="teacher"/>
- <result column="CREATEDATE" property="createDate"/>
- </resultMap>
- <!-- columnPrefix:别名前缀 -->
- <resultMap type="Classes" id="classesAndStudentListResultMap">
- <id column="CID" property="cid"/>
- <result column="CNAME" property="cname"/>
- <result column="TEACHER" property="teacher"/>
- <result column="CREATEDATE" property="createDate"/>
- <collection property="students" ofType="Student" resultMap="com.mybatis.student.studentResultMap" columnPrefix="stu_"/>
- </resultMap>
- <!-- 下面采用了 别名 stu_ 来区分列名 -->
- <select id="selectClassAndStudentListById" resultMap="classesAndStudentListResultMap" parameterType="int">
- select
- c.cid,
- c.cname,
- c.teacher,
- c.createdate,
- s.sid stu_sid,
- s.sname stu_sname,
- s.score stu_score
- from student s right join classes c on s.cid=c.cid where c.cid=#{cid}
- </select>
- </mapper>
TestStudentAndClasses.java
- package com.mybatis.student;
- import java.io.IOException;
- import java.io.Reader;
- import java.sql.CallableStatement;
- import java.sql.Connection;
- import java.sql.Date;
- import java.sql.DriverManager;
- import java.sql.ResultSet;
- import java.util.ArrayList;
- import java.util.HashMap;
- import java.util.List;
- 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 org.junit.Before;
- import org.junit.Test;
- import com.mybatis.classes.Classes;
- public class TestStudentAndClasses {
- private SqlSessionFactory sqlSessionFactory;
- @Before
- public void init() throws IOException{
- Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
- sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
- }
- /**
- * 测试新增 手动给 sid
- */
- @Test
- public void testAddStudent(){
- SqlSession session=sqlSessionFactory.openSession();
- Student student =new Student();
- student.setSid(35);
- student.setSname("Guider");
- student.setScore(100);
- student.setMajor("Games");
- student.setBirth(Date.valueOf("2008-08-08"));
- session.insert("com.mybatis.student.addStudent", student);
- session.commit();
- session.close();
- }
- /**
- * 测试新增 采用序列 给sid
- */
- @Test
- public void testAddStudentBySequence(){
- SqlSession session=sqlSessionFactory.openSession();
- Student student =new Student();
- student.setSname("Provdwer");
- student.setScore(100);
- student.setMajor("Games");
- student.setBirth(Date.valueOf("2008-08-08"));
- session.insert("com.mybatis.student.addStudentBySequence", student);
- session.commit();
- session.close();
- }
- /**
- * 测试删除
- */
- @Test
- public void testDelStudentById(){
- SqlSession session=sqlSessionFactory.openSession();
- session.delete("com.mybatis.student.delStudentById", 12);
- session.commit();
- session.close();
- }
- /**
- * 测试根据 sid更新
- */
- @Test
- public void testUpdStudentById(){
- SqlSession session=sqlSessionFactory.openSession();
- Student student =new Student();
- student.setSid(0);
- student.setSname("Sandy");
- student.setScore(100);
- student.setMajor("sandy");
- student.setBirth(Date.valueOf("2008-08-08"));
- session.update("com.mybatis.student.addStudentBySequence", student);
- session.commit();
- session.close();
- }
- /**
- * 测试查询所有
- */
- @Test
- public void testQueryAllStudent(){
- List<Student> stuList=new ArrayList<Student>();
- SqlSession session=sqlSessionFactory.openSession();
- stuList=session.selectList("com.mybatis.student.queryAllStudent");
- session.commit();
- session.close();
- for (Student student : stuList) {
- System.out.println(student);
- }
- }
- /**
- * 测试根据 name 模糊查询
- */
- @Test
- public void testQueryStudentByName(){
- List<Student> stuList=new ArrayList<Student>();
- SqlSession session=sqlSessionFactory.openSession();
- stuList=session.selectList("com.mybatis.student.queryStudentByName","%l%");
- session.commit();
- session.close();
- for (Student student : stuList) {
- System.out.println(student);
- }
- }
- /**
- * 测个根据sid查找一个对象
- */
- @Test
- public void testQueryStudentById(){
- SqlSession session=sqlSessionFactory.openSession();
- Student student=(Student)session.selectOne("com.mybatis.student.queryStudentById",1);
- session.close();
- System.out.println(student);
- }
- /**
- * 测试 使用resultMap 自定返回值集合
- */
- @Test
- public void testStudentResultMap(){
- List<Student> stuList=new ArrayList<Student>();
- SqlSession session=sqlSessionFactory.openSession();
- stuList=session.selectList("com.mybatis.student.selectMapResult","%l%");
- session.close();
- for (Student student : stuList) {
- System.out.println(student);
- }
- }
- /**
- * 测试 左连接查 一对一 的 关系
- */
- @Test
- public void testSelectStudentAndClassBySname(){
- List<Student> stuList=new ArrayList<Student>();
- SqlSession session=sqlSessionFactory.openSession();
- stuList=session.selectList("com.mybatis.student.selectStudentAndClassBySname","luob");
- session.close();
- for (Student student : stuList) {
- System.out.println(student+"//--"+student.getClasses());
- }
- }
- /**
- * 测试 多对一的 关系的 右连接的查询
- */
- @Test
- public void testSelectClassAndStudentListById(){
- SqlSession session=sqlSessionFactory.openSession();
- Classes classes=(Classes)session.selectOne("com.mybatis.classes.selectClassAndStudentListById",1);
- session.close();
- System.out.println(classes);
- for (Student student : classes.getStudents()) {
- System.out.println(student+"//--"+student.getClasses());
- }
- }
- /**
- * 测试 动态sql 的 应用 where if ognl
- */
- @Test
- public void testSelectStudentByDynamicSql(){
- Student pstudent=new Student();
- pstudent.setSid(1);
- List<Student> stuList=new ArrayList<Student>();
- SqlSession session=sqlSessionFactory.openSession();
- stuList=session.selectList("com.mybatis.student.selectStudentByDynamicSql",pstudent);
- session.close();
- for (Student student : stuList) {
- System.out.println(student+"//--"+student.getClasses());
- }
- }
- /**
- * 测试 动态sql 的choose where when otherwise
- */
- @Test
- public void testSelectStudentByDynamicSqlChoose(){
- Student pstudent=new Student();
- pstudent.setSid(1);
- //pstudent.setSname("luob");
- List<Student> stuList=new ArrayList<Student>();
- SqlSession session=sqlSessionFactory.openSession();
- stuList=session.selectList("com.mybatis.student.selectStudentByDynamicSqlChoose",pstudent);
- session.close();
- for (Student student : stuList) {
- System.out.println(student+"//--"+student.getClasses());
- }
- }
- /**
- * 测试 动态sql 中foreach 的使用 传入 集合list 参数
- */
- @Test
- public void testSelectStudentByIds(){
- ArrayList<Integer> ids=new ArrayList<Integer>();
- ids.add(1);
- ids.add(6);
- ids.add(21);
- ids.add(23);
- List<Student> stuList=new ArrayList<Student>();
- SqlSession session=sqlSessionFactory.openSession();
- stuList=session.selectList("com.mybatis.student.selectStudentByIds",ids);
- session.close();
- for (Student student : stuList) {
- System.out.println(student+"//--"+student.getClasses());
- }
- }
- /**
- * 测试 动态sql 中foreach 的使用 传入 数组array 参数
- */
- @Test
- public void testSelectStudentByIdArray(){
- List<Student> stuList=new ArrayList<Student>();
- Integer[] idArry=new Integer[]{1,6,21,23};
- SqlSession session=sqlSessionFactory.openSession();
- stuList=session.selectList("com.mybatis.student.selectStudentByIdArray",idArry);
- session.close();
- for (Student student : stuList) {
- System.out.println(student+"//--"+student.getClasses());
- }
- }
- /**
- * 测试调用 存储过程 里面有游标哦 返回多个结果
- */
- @Test
- public void testGetAllStudentAfterupdate(){
- List<Student> stuList=new ArrayList<Student>();
- Map map = new HashMap();
- map.put("sid", 10);
- map.put("sname", "张翰");
- map.put("studentList",stuList);
- SqlSession session=sqlSessionFactory.openSession();
- session.selectOne("com.mybatis.student.getAllStudentAfterupdate",map);
- stuList=(ArrayList<Student>)map.get("studentList");
- for (Student student : stuList) {
- System.out.println(student+"//--"+student.getClasses());
- }
- session.close();
- }
- /**
- * 使用jdbc 测试 游标的创建是否成功
- */
- @Test
- public void testJdbcProcedure(){
- Connection con=null;
- try {
- Class.forName("oracle.jdbc.driver.OracleDriver");
- con=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","luob","luob");
- CallableStatement cs=con.prepareCall("{call LUOB.pro_getallstudent(?,?,?)}");
- cs.setInt(1, 10);
- cs.setString(2,"张翰");
- //!!! 注意这里 type 在Types中 没有这个类型
- cs.registerOutParameter(3,oracle.jdbc.OracleTypes.CURSOR);
- cs.execute();
- ResultSet rs=(ResultSet)cs.getObject(3);
- while(rs.next()){
- Student student=new Student();
- student.setSid(rs.getInt(1));
- student.setSname(rs.getString(2));
- student.setMajor(rs.getString(3));
- student.setBirth(rs.getDate(4));
- student.setScore(rs.getFloat(5));
- student.setCid(rs.getInt(6));
- student.setStatus(rs.getInt(7));
- System.out.println(student);
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- }