MyBatis 的简单应用

秒速五厘米 2022-01-16 12:45 326阅读 0赞

[2019独角兽企业重金招聘Python工程师标准>>> ][2019_Python_] ![hot3.png][]

**介绍**

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

**目录**  
![13162913\_8A9L.jpg][13162913_8A9L.jpg]  
  
**MyBatis-config.xml 中 set 的说明 \[\]: 表示 可能的不太正确**

Xml代码

<settings>  
              
            <setting name="cacheEnabled" value="true"/>  
              
              
            <setting name="lazyLoadingEnabled" value="true"/>  
              
              
            <setting name="aggressiveLazyLoading" value="true"/>  
              
              
            <setting name="multipleResultSetsEnabled" value="true"/>  
              
              
            <setting name="useColumnLabel" value="true"/>  
              
              
            <setting name="useGeneratedKeys" value="false"/>  
              
              
            <setting name="autoMappingBehavior" value="PARTIAL"/>  
              
              
            <setting name="defaultExecutorType" value="SIMPLE"/>  
              
              
            <setting name="defaultStatementTimeout" value="25000"/>  
              
              
            <setting name="safeRowBoundsEnabled" value="false"/>  
              
              
            <setting name="mapUnderscoreToCamelCase" value="false"/>  
              
              
            <setting name="localCacheScope" value="SESSION"/>  
              
              
            <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代码

<?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 resource="mybatis-config.properties">  
              
        </properties>  
         
          
          
        <typeAliases>  
            <typeAlias type="com.mybatis.student.Student" alias="Student"/>  
            <typeAlias type="com.mybatis.classes.Classes" alias="Classes"/>  
              
        </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**

Xml代码

driver=oracle.jdbc.driver.OracleDriver  
    url=jdbc:oracle:thin:@127.0.0.1:1521:orcl  
    username=luob  
    password=luob

Student.java

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代码

<?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">  
          
          
          
        <cache eviction="FIFO" readOnly="true" size="256" flushInterval="60000"/>  
           
          
        <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>  
          
          
        <select id="queryStudentByName" resultType="Student" parameterType="String">  
            select * from student where sname like #{property,javaType=String,jdbcType=VARCHAR}  
        </select>  
          
          
        <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>  
          
          
        <select id="selectMapResult" resultMap="studentResultMap" parameterType="String">  
            select <include refid="studentColumns"/> from STUDENT where sname like #{sname}  
        </select>   
          
          
        <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>  
          
          
        <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>  
          
          
        <select id="selectStudentByIds" resultType="Student">  
            select * from student   
            where sid in  
            <foreach collection="list" item="itm" index="index" open="(" separator="," close=")">  
                #{itm}  
            </foreach>  
        </select>  
          
          
        <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>  
          
        <select id="getAllStudentAfterupdate" statementType="CALLABLE" useCache="true" parameterMap="procedureParam">  
            {call LUOB.pro_getallstudent(?,?,?)}  
        </select>  
          
         
     </mapper>

Classes.java

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代码

<?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>  
          
          
        <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>  
          
          
        <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**

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

转载于:https://my.oschina.net/u/2246951/blog/820016

[2019_Python_]: https://my.oschina.net/u/2663968/blog/3061697
[hot3.png]: /images/20220114/e1737fe256e940ff95b0d486cc1d2258.png
[13162913_8A9L.jpg]: https://static.oschina.net/uploads/img/201701/13162913_8A9L.jpg