介紹
1.例子中包含了 mybatis 的常用sql的寫(xiě)法
2.動(dòng)態(tài)sql 的應(yīng)用
3.存儲(chǔ)過(guò)程的使用
目錄
MyBatis-config.xml 中 set 的說(shuō)明 []: 表示 可能的不太正確
<!-- 配置設(shè)置 -->
<settings>
<!-- 配置全局性 cache 的 ( 開(kāi) / 關(guān)) default:true -->
<setting name="cacheEnabled" value="true"/>
<!-- 是否使用 懶加載 關(guān)聯(lián)對(duì)象 同 hibernate中的延遲加載 一樣 default:true -->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- [當(dāng)對(duì)象使用延遲加載時(shí) 屬性的加載取決于能被引用到的那些延遲屬性,否則,按需加載(需要的是時(shí)候才去加載)] -->
<setting name="aggressiveLazyLoading" value="true"/>
<!-- 是否允許單條sql 返回多個(gè)數(shù)據(jù)集 (取決于驅(qū)動(dòng)的兼容性) default:true -->
<setting name="multipleResultSetsEnabled" value="true"/>
<!-- 是否可以使用列的別名 (取決于驅(qū)動(dòng)的兼容性) default:true-->
<setting name="useColumnLabel" value="true"/>
<!--允許JDBC 生成主鍵。需要驅(qū)動(dòng)器支持。如果設(shè)為了true，這個(gè)設(shè)置將強(qiáng)制使用被生成的主鍵，有一些驅(qū)動(dòng)器不兼容不過(guò)仍然可以執(zhí)行。 default:false-->
<setting name="useGeneratedKeys" value="false"/>
<!--指定 MyBatis 如何自動(dòng)映射 數(shù)據(jù)基表的列 NONE：不隱射 PARTIAL:部分 FULL:全部-->
<setting name="autoMappingBehavior" value="PARTIAL"/>
<!-- 這是默認(rèn)的執(zhí)行類型
SIMPLE :簡(jiǎn)單
REUSE:執(zhí)行器可能重復(fù)使用prepared statements 語(yǔ)句
BATCH:執(zhí)行器可以重復(fù)執(zhí)行語(yǔ)句和批量更新
-->
<setting name="defaultExecutorType" value="SIMPLE"/>
<!-- 設(shè)置驅(qū)動(dòng)等待數(shù)據(jù)響應(yīng)的超時(shí)數(shù) 默認(rèn)沒(méi)有設(shè)置-->
<setting name="defaultStatementTimeout" value="25000"/>
<!-- [是否啟用 行內(nèi)嵌套語(yǔ)句 defaut:false] -->
<setting name="safeRowBoundsEnabled" value="false"/>
<!-- [是否 啟用 數(shù)據(jù)中 A_column 自動(dòng)映射 到 java類中駝峰命名的屬性 default:fasle] -->
<setting name="mapUnderscoreToCamelCase" value="false"/>
<!-- 設(shè)置本地緩存范圍 session:就會(huì)有數(shù)據(jù)的共享 statement:語(yǔ)句范圍 (這樣就不會(huì)有數(shù)據(jù)的共享 ) defalut:session -->
<setting name="localCacheScope" value="SESSION"/>
<!-- 設(shè)置但JDBC類型為空時(shí),某些驅(qū)動(dòng)程序 要指定值,default:other -->
<setting name="jdbcTypeForNull" value="OTHER"/>
<!-- 設(shè)置觸發(fā)延遲加載的方法 -->
<setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>
</settings>
表,序列 ,存儲(chǔ)過(guò)程 的創(chuàng)建
存儲(chǔ)過(guò)程
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;
測(cè)試
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||'行,學(xué)生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;
學(xué)生表
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)
)
班級(jí)表
-- 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"/>
<!--
也可以使用 包范圍來(lái)配置
<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* )+> -->
<!-- 設(shè)置緩存 如果用戶需要登錄 需要設(shè)置這種類型 type=org.mybatis.caches.oscache.LoggingOSCache-->
<cache eviction="FIFO" readOnly="true" size="256" flushInterval="60000"/>
<!-- 定義可以重用的sql 代碼片段 -->
<sql id="studentColumns">sid,sname,score</sql>
<!-- 自定義結(jié)果集 -->
<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>
<!-- 只用構(gòu)造函數(shù) 創(chuàng)建對(duì)象 對(duì)于那些 比較少的列 -->
<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>
<!-- 參數(shù)可以指定一個(gè)特定的數(shù)據(jù)類型 還可以使用自定類型處理: typeHandler=MyTypeHandler -->
<select id="queryStudentByName" resultType="Student" parameterType="String">
select * from student where sname like #{property,javaType=String,jdbcType=VARCHAR}
</select>
<!-- 參數(shù)可以指定一個(gè)特定的數(shù)據(jù)類型 對(duì)于數(shù)字類型 ,numericScale=2 用于設(shè)置小數(shù)類型 -->
<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 表達(dá)式 來(lái)配置動(dòng)態(tài)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 只能小寫(xiě)-->
<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 只能小寫(xiě) -->
<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集合參數(shù) ,調(diào)用 待用游標(biāo)存儲(chǔ)過(guò)程(先執(zhí)行 修改后然后查詢所有) -->
<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">
<!-- 設(shè)置 緩存共享 -->
<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_ 來(lái)區(qū)分列名 -->
<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);
}
/**
* 測(cè)試新增 手動(dòng)給 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();
}
/**
* 測(cè)試新增 采用序列 給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();
}
/**
* 測(cè)試刪除
*/
@Test
public void testDelStudentById(){
SqlSession session=sqlSessionFactory.openSession();
session.delete("com.mybatis.student.delStudentById", 12);
session.commit();
session.close();
}
/**
* 測(cè)試根據(jù) 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();
}
/**
* 測(cè)試查詢所有
*/
@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);
}
}
/**
* 測(cè)試根據(jù) 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);
}
}
/**
* 測(cè)個(gè)根據(jù)sid查找一個(gè)對(duì)象
*/
@Test
public void testQueryStudentById(){
SqlSession session=sqlSessionFactory.openSession();
Student student=(Student)session.selectOne("com.mybatis.student.queryStudentById",1);
session.close();
System.out.println(student);
}
/**
* 測(cè)試 使用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);
}
}
/**
* 測(cè)試 左連接查 一對(duì)一 的 關(guān)系
*/
@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+"http://--"+student.getClasses());
}
}
/**
* 測(cè)試 多對(duì)一的 關(guān)系的 右連接的查詢
*/
@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+"http://--"+student.getClasses());
}
}
/**
* 測(cè)試 動(dòng)態(tài)sql 的 應(yīng)用 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+"http://--"+student.getClasses());
}
}
/**
* 測(cè)試 動(dòng)態(tài)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+"http://--"+student.getClasses());
}
}
/**
* 測(cè)試 動(dòng)態(tài)sql 中foreach 的使用 傳入 集合list 參數(shù)
*/
@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+"http://--"+student.getClasses());
}
}
/**
* 測(cè)試 動(dòng)態(tài)sql 中foreach 的使用 傳入 數(shù)組array 參數(shù)
*/
@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+"http://--"+student.getClasses());
}
}
/**
* 測(cè)試調(diào)用 存儲(chǔ)過(guò)程 里面有游標(biāo)哦 返回多個(gè)結(jié)果
*/
@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+"http://--"+student.getClasses());
}
session.close();
}
/**
* 使用jdbc 測(cè)試 游標(biāo)的創(chuàng)建是否成功
*/
@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中 沒(méi)有這個(gè)類型
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();
}
}
}
MyBatis 的簡(jiǎn)單應(yīng)用