MyBatis 的简单应用

介绍

1.例子中包含了 mybatis 的常用sql的写法2.动态sql 的应用3.存储过程的使用

目录


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)asbegin  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_SEQminvalue 1maxvalue 999999999999999999999999999start with 32increment by 1cache 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 tablecreate 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.OracleDriverurl=jdbc:oracle:thin:@127.0.0.1:1521:orclusername=luobpassword=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;@Beforepublic void init() throws IOException{Reader reader = Resources.getResourceAsReader("mybatis-config.xml"); sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader); }/** * 测试新增  手动给 sid */@Testpublic 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 */@Testpublic 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();}/** * 测试删除 */@Testpublic void testDelStudentById(){SqlSession session=sqlSessionFactory.openSession();session.delete("com.mybatis.student.delStudentById", 12);session.commit();session.close();}/** * 测试根据 sid更新 */@Testpublic 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();}/** * 测试查询所有 */@Testpublic 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 模糊查询 */@Testpublic 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查找一个对象 */@Testpublic void testQueryStudentById(){SqlSession session=sqlSessionFactory.openSession();Student student=(Student)session.selectOne("com.mybatis.student.queryStudentById",1);session.close();System.out.println(student);}/** * 测试 使用resultMap 自定返回值集合 */@Testpublic 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);}}/** * 测试 左连接查  一对一 的 关系 */@Testpublic 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());}}/** * 测试 多对一的 关系的 右连接的查询 */@Testpublic 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 */@Testpublic 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 */@Testpublic 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 参数  */@Testpublic 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 参数  */@Testpublic 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());}}/** * 测试调用 存储过程   里面有游标哦   返回多个结果  */@Testpublic 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 测试 游标的创建是否成功  */@Testpublic 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();}}}