介绍
目录
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.存储过程的使用
目录
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(); } } }