首页 > 代码库 > Note_JavaWeb_MyBatis3

Note_JavaWeb_MyBatis3

Jar包

mybatis-3.2.8.jar

junit4.4.jar

log4j-1.2.17.jar

常用类

Resources

SqlSession

SqlSessionFactory

SqlSessionFactoryBuilder

配置文件

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

<configuration>

properties

<properties resource="jdbc.properties"/>

typeAliases

    <!-- <typeAliases>

        <typeAlias alias="Student" type="com.java1234.model.Student"/>

    </typeAliases> -->

    <typeAliases>

        <package name="com.java1234.model"/>

    </typeAliases>

 

environments

    <environments default="development">

        <environment id="development">

transactionManager

            <transactionManager type="JDBC" />

dataSource

            <dataSource type="POOLED">

                <property name="driver" value="http://www.mamicode.com/${jdbc.driverClassName}" />

                <property name="url" value="http://www.mamicode.com/${jdbc.url}" />

                <property name="username" value="http://www.mamicode.com/${jdbc.username}" />

                <property name="password" value="http://www.mamicode.com/${jdbc.password}" />

            </dataSource>

        </environment>

</environments>

mappers

    <mappers>

        <!-- <mapper resource="com/java1234/mappers/StudentMapper.xml" /> -->

        <!-- <mapper class="com.java1234.mappers.StudentMapper"/> -->

        <package name="com.java1234.mappers"/>

    </mappers>

 

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

<mapper namespace="com.java1234.mappers.StudentMapper">

    <insert id="add" parameterType="Student"  >

        insert into t_student values(null,#{name},#{age})

    </insert>

</mapper>

jdbc.properties

jdbc.driverClassName=com.mysql.jdbc.Driver

jdbc.url=jdbc:mysql://localhost:3306/db_mybatis

jdbc.username=root

jdbc.password=123456

 

log4j.properties

log4j.rootLogger=info,appender1,appender2

 

log4j.appender.appender1=org.apache.log4j.ConsoleAppender

 

log4j.appender.appender2=org.apache.log4j.FileAppender

log4j.appender.appender2.File=C:/logFile.txt

 

log4j.appender.appender1.layout=org.apache.log4j.TTCCLayout

log4j.appender.appender2.layout=org.apache.log4j.TTCCLayout 

 

SqlSessionFactoryUtil.java

package com.java1234.util;

 

import java.io.InputStream;

 

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

 

public class SqlSessionFactoryUtil {

    private static SqlSessionFactory sqlSessionFactory;

    public static SqlSessionFactory getSqlSessionFactory(){

        if(sqlSessionFactory==null){

            InputStream inputStream=null;

            try{

                inputStream=Resources.getResourceAsStream("mybatis-config.xml");

                sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);

            }catch(Exception e){

                e.printStackTrace();

            }

        }

        return sqlSessionFactory;

    }

    public static SqlSession openSession(){

        return getSqlSessionFactory().openSession();

    }

}

 

---------- 一头扎进MyBatis3 ----------

MyBatisPro01

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

<configuration>

<!-- <properties resource="jdbc.properties"/> -->

properties

    <properties>

        <property name="jdbc.driverClassName" value="http://www.mamicode.com/com.mysql.jdbc.Driver"/>

        <property name="jdbc.url" value="http://www.mamicode.com/jdbc:mysql://localhost:3306/db_mybatis"/>

        <property name="jdbc.username" value="http://www.mamicode.com/root"/>

        <property name="jdbc.password" value="http://www.mamicode.com/123456"/>

</properties>

typeAliases

    <!-- <typeAliases>

        <typeAlias alias="Student" type="com.java1234.model.Student"/>

    </typeAliases> -->

    <typeAliases>

        <package name="com.java1234.model"/>

</typeAliases>

environments

    <environments default="development">

        <environment id="development">

transactionManager

            <transactionManager type="JDBC" />

dataSource

            <dataSource type="POOLED">

                <property name="driver" value="http://www.mamicode.com/${jdbc.driverClassName}" />

                <property name="url" value="http://www.mamicode.com/${jdbc.url}" />

                <property name="username" value="http://www.mamicode.com/${jdbc.username}" />

                <property name="password" value="http://www.mamicode.com/${jdbc.password}" />

            </dataSource>

        </environment>

        <environment id="test">

            <transactionManager type="JDBC" />

            <dataSource type="POOLED">

                <property name="driver" value="http://www.mamicode.com/${jdbc.driverClassName}" />

                <property name="url" value="http://www.mamicode.com/${jdbc.url}" />

                <property name="username" value="http://www.mamicode.com/${jdbc.username}" />

                <property name="password" value="http://www.mamicode.com/${jdbc.password}" />

            </dataSource>

        </environment>

</environments>

mappers

    <mappers>

        <!-- <mapper resource="com/java1234/mappers/StudentMapper.xml" /> -->

        <!-- <mapper class="com.java1234.mappers.StudentMapper"/> -->

        <package name="com.java1234.mappers"/>

    </mappers>

</configuration>

jdbc.properties

jdbc.driverClassName=com.mysql.jdbc.Driver

jdbc.url=jdbc:mysql://localhost:3306/db_mybatis

jdbc.username=root

jdbc.password=123456

log4j.properties

log4j.rootLogger=info,appender1,appender2

 

log4j.appender.appender1=org.apache.log4j.ConsoleAppender

 

log4j.appender.appender2=org.apache.log4j.FileAppender

log4j.appender.appender2.File=C:/logFile.txt

 

log4j.appender.appender1.layout=org.apache.log4j.TTCCLayout

log4j.appender.appender2.layout=org.apache.log4j.TTCCLayout 

SqlSessionFactoryUtil.java

package com.java1234.util;

 

import java.io.InputStream;

 

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

 

public class SqlSessionFactoryUtil {

 

    private static SqlSessionFactory sqlSessionFactory;

   

    public static SqlSessionFactory getSqlSessionFactory(){

        if(sqlSessionFactory==null){

            InputStream inputStream=null;

            try{

                inputStream=Resources.getResourceAsStream("mybatis-config.xml");

                sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);

            }catch(Exception e){

                e.printStackTrace();

            }

        }

        return sqlSessionFactory;

    }

   

    public static SqlSession openSession(){

        return getSqlSessionFactory().openSession();

    }

}

StudentTest.java

package com.java1234.service;

 

import org.apache.ibatis.session.SqlSession;

import org.apache.log4j.Logger;

 

import com.java1234.mappers.StudentMapper;

import com.java1234.model.Student;

import com.java1234.util.SqlSessionFactoryUtil;

 

public class StudentTest {

 

    private static Logger logger=Logger.getLogger(StudentTest.class);

   

    public static void main(String[] args) {

        SqlSession sqlSession=SqlSessionFactoryUtil.openSession();

        StudentMapper studentMapper=sqlSession.getMapper(StudentMapper.class);

        Student student=new Student("李四",11);

        int result=studentMapper.add(student);

        sqlSession.commit();

        if(result>0){

            logger.info("添加成功!");

        }

    }

}

StudentTest2.java

package com.java1234.service;

 

import java.util.List;

 

import org.apache.ibatis.session.SqlSession;

import org.apache.log4j.Logger;

import org.junit.After;

import org.junit.Before;

import org.junit.Test;

 

import com.java1234.mappers.StudentMapper;

import com.java1234.model.Student;

import com.java1234.util.SqlSessionFactoryUtil;

 

public class StudentTest2 {

 

    private static Logger logger=Logger.getLogger(StudentTest.class);

    private SqlSession sqlSession=null;

    private StudentMapper studentMapper=null;

   

    /**

     * 测试方法前调用

     * @throws Exception

     */

@Before

    @Before

    public void setUp() throws Exception {

        sqlSession=SqlSessionFactoryUtil.openSession();

        studentMapper=sqlSession.getMapper(StudentMapper.class);

    }

 

    /**

     * 测试方法后调用

     * @throws Exception

     */

@After

    @After

    public void tearDown() throws Exception {

        sqlSession.close();

    }

@Test

    @Test

    public void testAdd() {

        logger.info("添加学生");

        Student student=new Student("王五",12);

        studentMapper.add(student);

        sqlSession.commit();

    }

   

    @Test

    public void testUpdate(){

        logger.info("修改学生");

        Student student=new Student(8,"王五2",13);

        studentMapper.update(student);

        sqlSession.commit();

    }

   

    @Test

    public void testDelete(){

        logger.info("删除学生");

        studentMapper.delete(8);

        sqlSession.commit();

    }

   

    @Test

    public void testFindById(){

        logger.info("通过ID查找学生");

        Student student=studentMapper.findById(1);

        System.out.println(student);

    }

 

    @Test

    public void testFind(){

        logger.info("查找所有学生");

        List<Student> studentList=studentMapper.find();

        for(Student s:studentList){

            System.out.println(s);

        }

    }

}

StudentTest3.java

package com.java1234.service;

 

 

import org.apache.ibatis.session.SqlSession;

import org.apache.log4j.Logger;

import org.junit.After;

import org.junit.Before;

import org.junit.Test;

 

import com.java1234.mappers.StudentMapper;

import com.java1234.model.Student;

import com.java1234.util.SqlSessionFactoryUtil;

 

public class StudentTest3 {

 

    private static Logger logger=Logger.getLogger(StudentTest.class);

    private SqlSession sqlSession=null;

    private StudentMapper studentMapper=null;

   

    /**

     * 测试方法前调用

     * @throws Exception

     */

    @Before

    public void setUp() throws Exception {

        sqlSession=SqlSessionFactoryUtil.openSession();

        studentMapper=sqlSession.getMapper(StudentMapper.class);

    }

 

    /**

     * 测试方法后调用

     * @throws Exception

     */

    @After

    public void tearDown() throws Exception {

        sqlSession.close();

    }

 

    @Test

    public void testFindStudentWithAddress() {

        logger.info("查询学生(带地址)");

        Student student=studentMapper.findStudentWithAddress(3);

        System.out.println(student);

    }

   

    @Test

    public void testFindStudentWithGrade(){

        logger.info("查询学生(带年级)");

        Student student=studentMapper.findStudentWithAddress(1);

        System.out.println(student);

    }

   

}

GradeTest.java

package com.java1234.service;

 

 

import org.apache.ibatis.session.SqlSession;

import org.apache.log4j.Logger;

import org.junit.After;

import org.junit.Before;

import org.junit.Test;

 

import com.java1234.mappers.GradeMapper;

import com.java1234.model.Grade;

import com.java1234.util.SqlSessionFactoryUtil;

 

public class GradeTest {

 

    private static Logger logger=Logger.getLogger(StudentTest.class);

    private SqlSession sqlSession=null;

    private GradeMapper gradeMapper=null;

   

    /**

     * 测试方法前调用

     * @throws Exception

     */

    @Before

    public void setUp() throws Exception {

        sqlSession=SqlSessionFactoryUtil.openSession();

        gradeMapper=sqlSession.getMapper(GradeMapper.class);

    }

 

    /**

     * 测试方法后调用

     * @throws Exception

     */

    @After

    public void tearDown() throws Exception {

        sqlSession.close();

    }

 

    @Test

    public void testFindGradeWithStudents() {

        logger.info("查询年级(带学生)");

        Grade grade=gradeMapper.findById(1);

        System.out.println(grade);

    }

   

}

AddressMapper.java

package com.java1234.mappers;

 

import com.java1234.model.Address;

 

public interface AddressMapper {

 

    public Address findById(Integer id);

 

}

AddressMapper.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

<mapper namespace="com.java1234.mappers.AddressMapper">

resultMap

    <resultMap type="Address" id="AddressResult">

        <result property="id" column="id"/>

        <result property="sheng" column="sheng"/>

        <result property="shi" column="shi"/>

        <result property="qu" column="qu"/>

    </resultMap>

   

    <select id="findById" parameterType="Integer" resultType="Address">

        select * from t_address where id=#{id}

    </select>

 

</mapper>

GradeMapper.java

package com.java1234.mappers;

 

import com.java1234.model.Grade;

 

public interface GradeMapper {

 

    public Grade findById(Integer id);

 

}

 

GradeMapper.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.java1234.mappers.GradeMapper">

 

    <resultMap type="Grade" id="GradeResult">

        <result property="id" column="id"/>

        <result property="gradeName" column="gradeName"/>

collection

        <collection property="students" column="id" select="com.java1234.mappers.StudentMapper.findByGradeId"></collection>

    </resultMap>

   

    <select id="findById" parameterType="Integer" resultMap="GradeResult">

        select * from t_grade where id=#{id}

    </select>

 

</mapper>

StudentMapper.java

package com.java1234.mappers;

 

import java.util.List;

 

import com.java1234.model.Student;

 

public interface StudentMapper {

 

    public int add(Student student);

   

    public int update(Student student);

   

    public int delete(Integer id);

   

    public Student findById(Integer id);

   

    public List<Student> find();

   

    public Student findStudentWithAddress(Integer id);

   

    public Student findByGradeId(Integer gradeId);

}

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.java1234.mappers.StudentMapper">

 

    <!-- <resultMap type="Student" id="StudentResult">

        <id property="id" column="id"/>

        <result property="name" column="name"/>

        <result property="age" column="age"/>

       

        <result property="address.id" column="addressId"/>

        <result property="address.sheng" column="sheng"/>

        <result property="address.shi" column="shi"/>

        <result property="address.qu" column="qu"/>

    </resultMap> -->

   

    <!-- <resultMap type="Address" id="AddressResult">

        <result property="id" column="id"/>

        <result property="sheng" column="sheng"/>

        <result property="shi" column="shi"/>

        <result property="qu" column="qu"/>

    </resultMap>

   

    <resultMap type="Student" id="StudentResult">

        <id property="id" column="id"/>

        <result property="name" column="name"/>

        <result property="age" column="age"/>

association

        <association property="address" resultMap="AddressResult"/>

    </resultMap> -->

   

    <!-- <resultMap type="Student" id="StudentResult">

        <id property="id" column="id"/>

        <result property="name" column="name"/>

        <result property="age" column="age"/>

association

        <association property="address" javaType="Address">

            <result property="id" column="id"/>

            <result property="sheng" column="sheng"/>

            <result property="shi" column="shi"/>

            <result property="qu" column="qu"/>

        </association>

    </resultMap> -->

   

    <resultMap type="Student" id="StudentResult">

        <id property="id" column="id"/>

        <result property="name" column="name"/>

        <result property="age" column="age"/>

association

        <association property="address" column="addressId" select="com.java1234.mappers.AddressMapper.findById"></association>

        <association property="grade" column="gradeId" select="com.java1234.mappers.GradeMapper.findById"></association>

    </resultMap>

   

    <select id="findStudentWithAddress" resultMap="StudentResult" parameterType="Integer">

        select * from t_student t1,t_address t2 where t1.addressId=t2.id and t1.id=#{id}

    </select>

   

    <select id="findByGradeId" resultMap="StudentResult" parameterType="Integer">

        select * from t_student where gradeId=#{gradeId}

    </select>

 

    <insert id="add" parameterType="Student"  >

        insert into t_student values(null,#{name},#{age})

    </insert>

 

    <update id="update" parameterType="Student">

        update t_student set name=#{name},age=#{age} where id=#{id}

    </update>

   

    <delete id="delete" parameterType="Integer">

        delete from t_student where id=#{id}

    </delete>

   

    <select id="findById" parameterType="Integer" resultType="Student">

        select * from t_student where id=#{id}

    </select>

   

    <select id="find" resultMap="StudentResult">

        select * from t_student

    </select>

</mapper>

Student.java

package com.java1234.model;

 

public class Student {

 

    private Integer id;

    private String name;

    private Integer age;

    private Address address;

    private Grade grade;

   

   

    public Student() {

        super();

        // TODO Auto-generated constructor stub

    }

   

 

    public Student(Integer id, String name, Integer age) {

        super();

        this.id = id;

        this.name = name;

        this.age = age;

    }

 

 

    public Student(String name, Integer age) {

        super();

        this.name = name;

        this.age = age;

    }

 

    public Integer getId() {

        return id;

    }

    public void setId(Integer id) {

        this.id = id;

    }

    public String getName() {

        return name;

    }

    public void setName(String name) {

        this.name = name;

    }

    public Integer getAge() {

        return age;

    }

    public void setAge(Integer age) {

        this.age = age;

    }

    public Address getAddress() {

        return address;

    }

 

 

    public void setAddress(Address address) {

        this.address = address;

    }

 

   

    public Grade getGrade() {

        return grade;

    }

 

    public void setGrade(Grade grade) {

        this.grade = grade;

    }

 

 

 

 

    @Override

    public String toString() {

        return "Student [id=" + id + ", name=" + name + ", age=" + age

                + ", address=" + address + ", grade=" + grade + "]";

    }

 

 

 

   

 

 

   

   

   

}

Grade.java

package com.java1234.model;

 

import java.util.List;

 

public class Grade {

 

    private Integer id;

    private String gradeName;

    private List<Student> students;

   

    public Integer getId() {

        return id;

    }

    public void setId(Integer id) {

        this.id = id;

    }

    public String getGradeName() {

        return gradeName;

    }

    public void setGradeName(String gradeName) {

        this.gradeName = gradeName;

    }

    public List<Student> getStudents() {

        return students;

    }

    public void setStudents(List<Student> students) {

        this.students = students;

    }

    @Override

    public String toString() {

        return "Grade [id=" + id + ", gradeName=" + gradeName +"]";

    }

   

   

}

Address.java

package com.java1234.model;

 

public class Address {

 

    private Integer id;

    private String sheng;

    private String shi;

    private String qu;

   

    public Integer getId() {

        return id;

    }

    public void setId(Integer id) {

        this.id = id;

    }

    public String getSheng() {

        return sheng;

    }

    public void setSheng(String sheng) {

        this.sheng = sheng;

    }

    public String getShi() {

        return shi;

    }

    public void setShi(String shi) {

        this.shi = shi;

    }

    public String getQu() {

        return qu;

    }

    public void setQu(String qu) {

        this.qu = qu;

    }

    @Override

    public String toString() {

        return "Address [id=" + id + ", sheng=" + sheng + ", shi=" + shi

                + ", qu=" + qu + "]";

    }

   

   

}

 

MyBatisPro02

StudentMapper.java

package com.java1234.mappers;

 

import java.util.List;

import java.util.Map;

 

import org.apache.ibatis.session.RowBounds;

 

import com.java1234.model.Student;

 

 

public interface StudentMapper {

 

    public List<Student> searchStudents(Map<String,Object> map);

   

    public List<Student> searchStudents2(Map<String,Object> map);

   

    public List<Student> searchStudents3(Map<String,Object> map);

   

    public List<Student> searchStudents4(Map<String,Object> map);

   

    public List<Student> searchStudents5(Map<String,Object> map);

   

    public List<Student> searchStudents6(String name,int age);

   

    public int updateStudent(Student student);

   

    public int insertStudent(Student student);

   

    public Student getStudentById(Integer id);

   

    public List<Student> findStudents(RowBounds rowBounds);

   

    public List<Student> findStudents2(Map<String,Object> map);

}

 

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.java1234.mappers.StudentMapper">

 

    <!--

        1,size:表示缓存cache中能容纳的最大元素数。默认是1024;

        2,flushInterval:定义缓存刷新周期,以毫秒计;

        3,eviction:定义缓存的移除机制;默认是LRU(least recently userd,最近最少使用),还有FIFO(first in first out,先进先出)

        4,readOnly:默认值是false,假如是true的话,缓存只能读。

     -->

    <cache size="1024" flushInterval="60000" eviction="LRU" readOnly="false"/>

   

    <resultMap type="Student" id="StudentResult">

        <id property="id" column="id"/>

        <result property="name" column="name"/>

        <result property="age" column="age"/>

    </resultMap>

   

    <select id="findStudents" resultMap="StudentResult" flushCache="false" useCache="true">

        select * from t_student

    </select>

   

    <select id="findStudents2" parameterType="Map" resultMap="StudentResult">

        select * from t_student

        <if test="start!=null and size!=null">

            limit #{start},#{size}

        </if>

    </select>

   

    <insert id="insertStudent" parameterType="Student" flushCache="true">

        insert into t_student values(null,#{name},#{age},#{pic},#{remark});

    </insert>

   

    <select id="getStudentById" parameterType="Integer" resultType="Student">

        select * from t_student where id=#{id}

    </select>

   

    <select id="searchStudents6" resultMap="StudentResult">

        select * from t_student where name like #{param1} and age=#{param2}

    </select>

   

    <select id="searchStudents" parameterType="Map" resultMap="StudentResult">

        select * from t_student

         where gradeId=#{gradeId}

         <if test="name!=null">

            and name like #{name}

         </if>

         <if test="age!=nulll">

            and age=#{age}

         </if>

    </select>

   

    <select id="searchStudents2" parameterType="Map" resultMap="StudentResult">

        select * from t_student

         <choose>

            <when test="searchBy==‘gradeId‘">

                where gradeId=#{gradeId}

            </when>

            <when test="searchBy==‘name‘">

                where name like #{name}

            </when>

            <otherwise>

                where age=#{age}

            </otherwise>

         </choose>

        

    </select>

   

    <select id="searchStudents3" parameterType="Map" resultMap="StudentResult">

        select * from t_student

         <where>

             <if test="gradeId!=null">

                gradeId=#{gradeId}

             </if>

             <if test="name!=null">

                and name like #{name}

             </if>

             <if test="age!=nulll">

                and age=#{age}

             </if>

         </where>

    </select>

   

    <select id="searchStudents4" parameterType="Map" resultMap="StudentResult">

        select * from t_student

         <trim prefix="where" prefixOverrides="and|or">

             <if test="gradeId!=null">

                gradeId=#{gradeId}

             </if>

             <if test="name!=null">

                and name like #{name}

             </if>

             <if test="age!=nulll">

                and age=#{age}

             </if>

         </trim>

    </select>

   

    <select id="searchStudents5" parameterType="Map" resultMap="StudentResult">

        select * from t_student

         <if test="gradeIds!=null">

            <where>

                gradeId in

                <foreach item="gradeId" collection="gradeIds" open="(" separator="," close=")">

                 #{gradeId}

                </foreach>

            </where>

         </if>

    </select>

   

    <update id="updateStudent" parameterType="Student">

        update t_student

        <set>

         <if test="name!=null">

            name=#{name},

         </if>

         <if test="age!=null">

            age=#{age},

         </if>

        </set>

        where id=#{id}

    </update>

</mapper>

StudentTest.java

package com.java1234.service;

 

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

 

import org.apache.ibatis.session.SqlSession;

import org.apache.log4j.Logger;

import org.junit.After;

import org.junit.Before;

import org.junit.Test;

 

import com.java1234.mappers.StudentMapper;

import com.java1234.model.Student;

import com.java1234.util.SqlSessionFactoryUtil;

 

public class StudentTest {

 

    private static Logger logger=Logger.getLogger(StudentTest.class);

    private SqlSession sqlSession=null;

    private StudentMapper studentMapper=null;

   

    /**

     * 测试方法前调用

     * @throws Exception

     */

    @Before

    public void setUp() throws Exception {

        sqlSession=SqlSessionFactoryUtil.openSession();

        studentMapper=sqlSession.getMapper(StudentMapper.class);

    }

 

    /**

     * 测试方法后调用

     * @throws Exception

     */

    @After

    public void tearDown() throws Exception {

        sqlSession.close();

    }

 

    @Test

    public void testSearchStudents() {

        logger.info("添加学生(带条件)");

        Map<String,Object> map=new HashMap<String,Object>();

        map.put("gradeId", 2);

        // map.put("name", "%李%");

        // map.put("age", 11);

        List<Student> studentList=studentMapper.searchStudents(map);

        for(Student student:studentList){

            System.out.println(student);

        }

    }

   

    @Test

    public void testSearchStudents2() {

        logger.info("添加学生(带条件)");

        Map<String,Object> map=new HashMap<String,Object>();

        map.put("searchBy", "age");

        map.put("gradeId", 2);

        map.put("name", "%李%");

        map.put("age", 11);

        List<Student> studentList=studentMapper.searchStudents2(map);

        for(Student student:studentList){

            System.out.println(student);

        }

    }

 

    @Test

    public void testSearchStudents3() {

        logger.info("添加学生(带条件)");

        Map<String,Object> map=new HashMap<String,Object>();

        map.put("gradeId", 2);

        map.put("name", "%李%");

        map.put("age", 11);

        List<Student> studentList=studentMapper.searchStudents3(map);

        for(Student student:studentList){

            System.out.println(student);

        }

    }

   

    @Test

    public void testSearchStudents4() {

        logger.info("添加学生(带条件)");

        Map<String,Object> map=new HashMap<String,Object>();

        map.put("gradeId", 2);

        // map.put("name", "%李%");

        // map.put("age", 11);

        List<Student> studentList=studentMapper.searchStudents4(map);

        for(Student student:studentList){

            System.out.println(student);

        }

    }

   

    @Test

    public void testSearchStudents5() {

        logger.info("添加学生(带条件)");

        Map<String,Object> map=new HashMap<String,Object>();

        List<Integer> gradeIds=new ArrayList<Integer>();

        // gradeIds.add(1);

        gradeIds.add(2);

        map.put("gradeIds", gradeIds);

        List<Student> studentList=studentMapper.searchStudents5(map);

        for(Student student:studentList){

            System.out.println(student);

        }

    }

   

    @Test

    public void testUpdateStudent(){

        logger.info("更新学生(带条件)");

        Student student=new Student();

        student.setId(1);

        student.setName("张三3");

        student.setAge(13);

        studentMapper.updateStudent(student);

        sqlSession.commit();

    }

}

StudentTest2.java

package com.java1234.service;

 

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.InputStream;

import java.io.OutputStream;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

 

import org.apache.ibatis.session.SqlSession;

import org.apache.log4j.Logger;

import org.junit.After;

import org.junit.Before;

import org.junit.Test;

 

import com.java1234.mappers.StudentMapper;

import com.java1234.model.Student;

import com.java1234.util.SqlSessionFactoryUtil;

 

public class StudentTest2 {

 

    private static Logger logger=Logger.getLogger(StudentTest2.class);

    private SqlSession sqlSession=null;

    private StudentMapper studentMapper=null;

   

    /**

     * 测试方法前调用

     * @throws Exception

     */

    @Before

    public void setUp() throws Exception {

        sqlSession=SqlSessionFactoryUtil.openSession();

        studentMapper=sqlSession.getMapper(StudentMapper.class);

    }

 

    /**

     * 测试方法后调用

     * @throws Exception

     */

    @After

    public void tearDown() throws Exception {

        sqlSession.close();

    }

 

   

    @Test

    public void testInsertStudent(){

        logger.info("添加学生");

        Student student=new Student();

        student.setName("张三4");

        student.setAge(14);

        student.setRemark("很长的本文...");

        byte []pic=null;

        try{

            File file=new File("c://boy.jpg");

            InputStream inputStream=new FileInputStream(file);

            pic=new byte[inputStream.available()];

            inputStream.read(pic);

            inputStream.close();

        }catch(Exception e){

            e.printStackTrace();

        }

        student.setPic(pic);

        studentMapper.insertStudent(student);

        sqlSession.commit();

    }

   

    @Test

    public void testGetStudentById(){

        logger.info("通过ID查找学生");

        Student student=studentMapper.getStudentById(4);

        System.out.println(student);

        byte []pic=student.getPic();

        try{

            File file=new File("d://boy2.jpg");

            OutputStream outputStream=new FileOutputStream(file);

            outputStream.write(pic);

            outputStream.close();

        }catch(Exception e){

            e.printStackTrace();

        }

    }

   

    @Test

    public void testSearchStudents6() {

        logger.info("添加学生(带条件)");

        List<Student> studentList=studentMapper.searchStudents6("%3%",12);

        for(Student student:studentList){

            System.out.println(student);

        }

    }

}

StudentTest3.java

package com.java1234.service;

 

import java.util.HashMap;

import java.util.List;

import java.util.Map;

 

import org.apache.ibatis.session.RowBounds;

import org.apache.ibatis.session.SqlSession;

import org.apache.log4j.Logger;

import org.junit.After;

import org.junit.Before;

import org.junit.Test;

 

import com.java1234.mappers.StudentMapper;

import com.java1234.model.Student;

import com.java1234.util.SqlSessionFactoryUtil;

 

public class StudentTest3 {

 

    private static Logger logger=Logger.getLogger(StudentTest3.class);

    private SqlSession sqlSession=null;

    private StudentMapper studentMapper=null;

   

    /**

     * 测试方法前调用

     * @throws Exception

     */

    @Before

    public void setUp() throws Exception {

        sqlSession=SqlSessionFactoryUtil.openSession();

        studentMapper=sqlSession.getMapper(StudentMapper.class);

    }

 

    /**

     * 测试方法后调用

     * @throws Exception

     */

    @After

    public void tearDown() throws Exception {

        sqlSession.close();

    }

 

   

    @Test

    public void testFindStudent(){

        logger.info("查询学生");

        int offset=0,limit=3;

        RowBounds rowBounds=new RowBounds(offset,limit);

        List<Student> studentList=studentMapper.findStudents(rowBounds);

        for(Student student:studentList){

            System.out.println(student);

        }

    }

   

    @Test

    public void testFindStudent2(){

        logger.info("查询学生");

        Map<String,Object> map=new HashMap<String,Object>();

        map.put("start", 3);

        map.put("size", 3);

        List<Student> studentList=studentMapper.findStudents2(map);

        for(Student student:studentList){

            System.out.println(student);

        }

    }

}

MyBatisPro03

AddressMapper.java

package com.java1234.mappers;

 

import org.apache.ibatis.annotations.Select;

 

import com.java1234.model.Address;

 

public interface AddressMapper {

 

    @Select("select * from t_address where id=#{id}")

    public Address findById(Integer id);

 

}

GradeMapper.java

package com.java1234.mappers;

 

import org.apache.ibatis.annotations.Many;

import org.apache.ibatis.annotations.One;

import org.apache.ibatis.annotations.Result;

import org.apache.ibatis.annotations.Results;

import org.apache.ibatis.annotations.Select;

 

import com.java1234.model.Grade;

 

public interface GradeMapper {

 

    @Select("select * from t_grade where id=#{id}")

    @Results(

            {

                @Result(id=true,column="id",property="id"),

                @Result(column="gradeName",property="gradeName"),

                @Result(column="id",property="students",many=@Many(select="com.java1234.mappers.StudentMapper.selectStudentByGradeId"))

            }

    )

    public Grade findById(Integer id);

 

}

StudentMapper.java

package com.java1234.mappers;

 

import java.util.List;

 

import org.apache.ibatis.annotations.Delete;

import org.apache.ibatis.annotations.Insert;

import org.apache.ibatis.annotations.One;

import org.apache.ibatis.annotations.Result;

import org.apache.ibatis.annotations.Results;

import org.apache.ibatis.annotations.Select;

import org.apache.ibatis.annotations.Update;

 

import com.java1234.model.Student;

 

 

public interface StudentMapper {

 

    @Insert("insert into t_student values(null,#{name},#{age})")

    public int insertStudent(Student student);

   

    @Update("update t_student set name=#{name},age=#{age} where id=#{id}")

    public int updateStudent(Student student);

   

    @Delete("delete from t_student where id=#{id}")

    public int deleteStudent(int id);

   

    @Select("select * from t_student where id=#{id}")

    public Student getStudentById(Integer id);

   

    @Select("select * from t_student")

    @Results(

            {

                @Result(id=true,column="id",property="id"),

                @Result(column="name",property="name"),

                @Result(column="age",property="age")

            }

    )

    public List<Student> findStudents();

   

    @Select("select * from t_student where id=#{id}")

    @Results(

            {

                @Result(id=true,column="id",property="id"),

                @Result(column="name",property="name"),

                @Result(column="age",property="age"),

                @Result(column="addressId",property="address",one=@One(select="com.java1234.mappers.AddressMapper.findById"))

            }

    )

    public Student selectStudentWithAddress(int id);

   

    @Select("select * from t_student where gradeId=#{gradeId}")

    @Results(

            {

                @Result(id=true,column="id",property="id"),

                @Result(column="name",property="name"),

                @Result(column="age",property="age"),

                @Result(column="addressId",property="address",one=@One(select="com.java1234.mappers.AddressMapper.findById"))

            }

    )

    public Student selectStudentByGradeId(int gradeId);

   

    @Select("select * from t_student where id=#{id}")

    @Results(

            {

                @Result(id=true,column="id",property="id"),

                @Result(column="name",property="name"),

                @Result(column="age",property="age"),

                @Result(column="addressId",property="address",one=@One(select="com.java1234.mappers.AddressMapper.findById")),

                @Result(column="gradeId",property="grade",one=@One(select="com.java1234.mappers.GradeMapper.findById"))

            }

    )

    public Student selectStudentWithAddressAndGrade(int id);

}

StudentTest.java

package com.java1234.service;

 

 

import java.util.List;

 

import org.apache.ibatis.session.SqlSession;

import org.apache.log4j.Logger;

import org.junit.After;

import org.junit.Before;

import org.junit.Test;

 

import com.java1234.mappers.StudentMapper;

import com.java1234.model.Student;

import com.java1234.util.SqlSessionFactoryUtil;

 

public class StudentTest {

 

    private static Logger logger=Logger.getLogger(StudentTest.class);

    private SqlSession sqlSession=null;

    private StudentMapper studentMapper=null;

   

    /**

     * 测试方法前调用

     * @throws Exception

     */

    @Before

    public void setUp() throws Exception {

        sqlSession=SqlSessionFactoryUtil.openSession();

        studentMapper=sqlSession.getMapper(StudentMapper.class);

    }

 

    /**

     * 测试方法后调用

     * @throws Exception

     */

    @After

    public void tearDown() throws Exception {

        sqlSession.close();

    }

 

    @Test

    public void testInsert() {

        logger.info("添加学生");

        Student student=new Student("琪琪",11);

        studentMapper.insertStudent(student);

        sqlSession.commit();

    }

   

    @Test

    public void testUpdate() {

        logger.info("更新学生");

        Student student=new Student(6,"琪琪2",12);

        studentMapper.updateStudent(student);

        sqlSession.commit();

    }

   

    @Test

    public void testDelete() {

        logger.info("删除学生");

        studentMapper.deleteStudent(6);

        sqlSession.commit();

    }

   

    @Test

    public void testGetById() {

        logger.info("通过ID查找学生");

        Student student=studentMapper.getStudentById(1);

        System.out.println(student);

    }

   

    @Test

    public void testFindStudents() {

        logger.info("查找所有学生");

        List<Student> studentList=studentMapper.findStudents();

        for(Student student:studentList){

            System.out.println(student);

        }

    }

 

}

StudentTest2.java

package com.java1234.service;

 

 

import java.util.List;

 

import org.apache.ibatis.session.SqlSession;

import org.apache.log4j.Logger;

import org.junit.After;

import org.junit.Before;

import org.junit.Test;

 

import com.java1234.mappers.GradeMapper;

import com.java1234.mappers.StudentMapper;

import com.java1234.model.Grade;

import com.java1234.model.Student;

import com.java1234.util.SqlSessionFactoryUtil;

 

public class StudentTest2 {

 

    private static Logger logger=Logger.getLogger(StudentTest2.class);

    private SqlSession sqlSession=null;

    private StudentMapper studentMapper=null;

    private GradeMapper gradeMapper=null;

   

    /**

     * 测试方法前调用

     * @throws Exception

     */

    @Before

    public void setUp() throws Exception {

        sqlSession=SqlSessionFactoryUtil.openSession();

        studentMapper=sqlSession.getMapper(StudentMapper.class);

        gradeMapper=sqlSession.getMapper(GradeMapper.class);

    }

 

    /**

     * 测试方法后调用

     * @throws Exception

     */

    @After

    public void tearDown() throws Exception {

        sqlSession.close();

    }

 

    @Test

    public void testSelectStudentWithAddress() {

        logger.info("查找学生(带地址)");

        Student student=studentMapper.selectStudentWithAddress(3);

        System.out.println(student);

    }

   

    @Test

    public void testSelectGradeWithStudents() {

        logger.info("查找年级(带学生)");

        Grade grade=gradeMapper.findById(2);

        System.out.println(grade);

        List<Student> studentList=grade.getStudents();

        for(Student student:studentList){

            System.out.println(student);

        }

    }

   

    @Test

    public void testSelectStudentWithAddressAndGrade() {

        logger.info("查找学生(带年级,带地址)");

        Student student=studentMapper.selectStudentWithAddressAndGrade(1);

        System.out.println(student);

    }

   

}

MyBatisPro04

StudentMapper.java

package com.java1234.mappers;

 

import java.util.List;

import java.util.Map;

 

import org.apache.ibatis.annotations.DeleteProvider;

import org.apache.ibatis.annotations.InsertProvider;

import org.apache.ibatis.annotations.Result;

import org.apache.ibatis.annotations.Results;

import org.apache.ibatis.annotations.Select;

import org.apache.ibatis.annotations.SelectProvider;

import org.apache.ibatis.annotations.UpdateProvider;

 

import com.java1234.model.Student;

 

 

public interface StudentMapper {

 

    @InsertProvider(type=StudentDynaSqlProvider.class,method="insertStudent")

    public int insertStudent(Student student);

   

    @UpdateProvider(type=StudentDynaSqlProvider.class,method="updateStudent")

    public int updateStudent(Student student);

   

    @DeleteProvider(type=StudentDynaSqlProvider.class,method="deleteStudent")

    public int deleteStudent(int id);

   

    @SelectProvider(type=StudentDynaSqlProvider.class,method="getStudentById")

    public Student getStudentById(Integer id);

   

    @SelectProvider(type=StudentDynaSqlProvider.class,method="findStudents")

    public List<Student> findStudents(Map<String,Object> map);

   

}

StudentDynaSqlProvider.java

package com.java1234.mappers;

 

import java.util.Map;

 

import org.apache.ibatis.jdbc.SQL;

 

import com.java1234.model.Student;

 

public class StudentDynaSqlProvider {

 

    public String insertStudent(final Student student){

        return new SQL(){

            {

                INSERT_INTO("t_student");

                if(student.getName()!=null){

                    VALUES("name", "#{name}");

                }

                if(student.getAge()!=null){

                    VALUES("age", "#{age}");

                }

            }

        }.toString();

    }

   

    public String updateStudent(final Student student){

        return new SQL(){

            {

                UPDATE("t_student");

                if(student.getName()!=null){

                    SET("name=#{name}");

                }

                if(student.getAge()!=null){

                    SET("age=#{age}");

                }

                WHERE("id=#{id}");

            }

        }.toString();

    }

   

    public String deleteStudent(){

        return new SQL(){

            {

                DELETE_FROM("t_student");

                WHERE("id=#{id}");

            }

        }.toString();

    }

   

    public String getStudentById(){

        return new SQL(){

            {

                SELECT("*");

                FROM("t_student");

                WHERE("id=#{id}");

            }

        }.toString();

    }

   

    public String findStudents(final Map<String,Object> map){

        return new SQL(){

            {

                SELECT("*");

                FROM("t_student");

                StringBuffer sb=new StringBuffer();

                if(map.get("name")!=null){

                    sb.append(" and name like ‘"+map.get("name")+"‘");

                }

                if(map.get("age")!=null){

                    sb.append(" and age="+map.get("age"));

                }

                if(!sb.toString().equals("")){

                    WHERE(sb.toString().replaceFirst("and", ""));                  

                }

            }

        }.toString();

    }

}

StudentTest.java

package com.java1234.service;

 

 

import java.util.HashMap;

import java.util.List;

import java.util.Map;

 

import org.apache.ibatis.session.SqlSession;

import org.apache.log4j.Logger;

import org.junit.After;

import org.junit.Before;

import org.junit.Test;

 

import com.java1234.mappers.StudentMapper;

import com.java1234.model.Student;

import com.java1234.util.SqlSessionFactoryUtil;

 

public class StudentTest {

 

    private static Logger logger=Logger.getLogger(StudentTest.class);

    private SqlSession sqlSession=null;

    private StudentMapper studentMapper=null;

   

    /**

     * 测试方法前调用

     * @throws Exception

     */

    @Before

    public void setUp() throws Exception {

        sqlSession=SqlSessionFactoryUtil.openSession();

        studentMapper=sqlSession.getMapper(StudentMapper.class);

    }

 

    /**

     * 测试方法后调用

     * @throws Exception

     */

    @After

    public void tearDown() throws Exception {

        sqlSession.close();

    }

 

    @Test

    public void testInsert() {

        logger.info("添加学生");

        Student student=new Student("琪琪",11);

        studentMapper.insertStudent(student);

        sqlSession.commit();

    }

   

    @Test

    public void testUpdate() {

        logger.info("更新学生");

        Student student=new Student(6,"琪琪2",12);

        studentMapper.updateStudent(student);

        sqlSession.commit();

    }

   

    @Test

    public void testDelete() {

        logger.info("删除学生");

        studentMapper.deleteStudent(6);

        sqlSession.commit();

    }

   

    @Test

    public void testGetById() {

        logger.info("通过ID查找学生");

        Student student=studentMapper.getStudentById(1);

        System.out.println(student);

    }

   

    @Test

    public void testFindStudents() {

        logger.info("查找所有学生");

        Map<String,Object> map=new HashMap<String,Object>();

        // map.put("name", "%张%");

        // map.put("age", 12);

        List<Student> studentList=studentMapper.findStudents(map);

        for(Student student:studentList){

            System.out.println(student);

        }

    }

 

}

MyBatisPro05

web.xml

<?xml version="1.0" encoding="UTF-8"?>

<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">

  <display-name>MyBatisPro05</display-name>

  <welcome-file-list>

    <welcome-file>index.jsp</welcome-file>

  </welcome-file-list>

 

    <!-- Spring配置文件 -->

    <context-param>

        <param-name>contextConfigLocation</param-name>

        <param-value>classpath:applicationContext.xml</param-value>

    </context-param>

    <!-- 编码过滤器 -->

    <filter>

        <filter-name>encodingFilter</filter-name>

        <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>

        <async-supported>true</async-supported>

        <init-param>

            <param-name>encoding</param-name>

            <param-value>UTF-8</param-value>

        </init-param>

    </filter>

    <filter-mapping>

        <filter-name>encodingFilter</filter-name>

        <url-pattern>/*</url-pattern>

    </filter-mapping>

    <!-- Spring监听器 -->

    <listener>

        <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>

    </listener>

   

    <!-- 添加对springmvc的支持 -->

    <servlet>

        <servlet-name>springMVC</servlet-name>

        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>

        <init-param>

            <param-name>contextConfigLocation</param-name>

            <param-value>classpath:spring-mvc.xml</param-value>

        </init-param>

        <load-on-startup>1</load-on-startup>

        <async-supported>true</async-supported>

    </servlet>

    <servlet-mapping>

        <servlet-name>springMVC</servlet-name>

        <url-pattern>*.do</url-pattern>

    </servlet-mapping>

</web-app>

spring-mvc.xml

<?xml version="1.0" encoding="UTF-8"?>   

<beans xmlns="http://www.springframework.org/schema/beans"   

    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  

    xmlns:p="http://www.springframework.org/schema/p" 

    xmlns:aop="http://www.springframework.org/schema/aop"  

    xmlns:context="http://www.springframework.org/schema/context" 

    xmlns:jee="http://www.springframework.org/schema/jee" 

    xmlns:tx="http://www.springframework.org/schema/tx" 

    xsi:schemaLocation="   

        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd 

        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd 

        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd 

        http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-4.0.xsd 

        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd">   

 

    <!-- 使用注解的包,包括子集 -->

    <context:component-scan base-package="com.java1234.controller" />

 

    <!-- 视图解析器 -->

    <bean id="viewResolver"

        class="org.springframework.web.servlet.view.InternalResourceViewResolver">

        <property name="prefix" value="http://www.mamicode.com/" />

        <property name="suffix" value="http://www.mamicode.com/.jsp"></property>

    </bean>

 

</beans> 

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>

    <!-- 别名 -->

    <typeAliases>

        <package name="com.java1234.entity"/>

    </typeAliases>

</configuration>

log4j.properties

log4j.rootLogger=info,appender1,appender2

 

log4j.appender.appender1=org.apache.log4j.ConsoleAppender

 

log4j.appender.appender2=org.apache.log4j.FileAppender

log4j.appender.appender2.File=C:/logFile.txt

 

log4j.appender.appender1.layout=org.apache.log4j.TTCCLayout

log4j.appender.appender2.layout=org.apache.log4j.TTCCLayout 

applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>   

<beans xmlns="http://www.springframework.org/schema/beans"   

    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  

    xmlns:p="http://www.springframework.org/schema/p" 

    xmlns:aop="http://www.springframework.org/schema/aop"  

    xmlns:context="http://www.springframework.org/schema/context" 

    xmlns:jee="http://www.springframework.org/schema/jee" 

    xmlns:tx="http://www.springframework.org/schema/tx" 

    xsi:schemaLocation="   

        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd 

        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd 

        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd 

        http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-4.0.xsd 

        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd">   

       

    <!-- 自动扫描 -->

    <context:component-scan base-package="com.java1234.dao" />

    <context:component-scan base-package="com.java1234.service" />

   

<!-- 配置数据源 -->

dataSource

    <bean id="dataSource"

        class="org.springframework.jdbc.datasource.DriverManagerDataSource">

        <property name="driverClassName" value="http://www.mamicode.com/com.mysql.jdbc.Driver"/>

        <property name="url" value="http://www.mamicode.com/jdbc:mysql://localhost:3306/db_mybatis"/>

        <property name="username" value="http://www.mamicode.com/root"/>

        <property name="password" value="http://www.mamicode.com/123456"/>

    </bean>

 

sqlSessionFactory

    <!-- 配置mybatis的sqlSessionFactory -->

    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">

        <property name="dataSource" ref="dataSource" />

        <!-- 自动扫描mappers.xml文件 -->

        <property name="mapperLocations" value="http://www.mamicode.com/classpath:com/java1234/mappers/*.xml"></property>

        <!-- mybatis配置文件 -->

        <property name="configLocation" value="http://www.mamicode.com/classpath:mybatis-config.xml"></property>

    </bean>

 

    <!-- DAO接口所在包名,Spring会自动查找其下的类 -->

    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">

        <property name="basePackage" value="http://www.mamicode.com/com.java1234.dao" />

        <property name="sqlSessionFactoryBeanName" value="http://www.mamicode.com/sqlSessionFactory"></property>

    </bean>

 

    <!-- (事务管理)transaction manager, use JtaTransactionManager for global tx -->

    <bean id="transactionManager"

        class="org.springframework.jdbc.datasource.DataSourceTransactionManager">

        <property name="dataSource" ref="dataSource" />

    </bean>

   

    <!-- 配置事务通知属性 --> 

    <tx:advice id="txAdvice" transaction-manager="transactionManager"> 

        <!-- 定义事务传播属性 --> 

        <tx:attributes> 

            <tx:method name="insert*" propagation="REQUIRED" /> 

            <tx:method name="update*" propagation="REQUIRED" /> 

            <tx:method name="edit*" propagation="REQUIRED" /> 

            <tx:method name="save*" propagation="REQUIRED" /> 

            <tx:method name="add*" propagation="REQUIRED" /> 

            <tx:method name="new*" propagation="REQUIRED" /> 

            <tx:method name="set*" propagation="REQUIRED" /> 

            <tx:method name="remove*" propagation="REQUIRED" /> 

            <tx:method name="delete*" propagation="REQUIRED" /> 

            <tx:method name="change*" propagation="REQUIRED" /> 

            <tx:method name="get*" propagation="REQUIRED" read-only="true" /> 

            <tx:method name="find*" propagation="REQUIRED" read-only="true" /> 

            <tx:method name="load*" propagation="REQUIRED" read-only="true" /> 

            <tx:method name="*" propagation="REQUIRED" read-only="true" /> 

        </tx:attributes> 

    </tx:advice> 

 

    <!-- 配置事务切面 --> 

    <aop:config> 

        <aop:pointcut id="serviceOperation" 

            expression="execution(* com.java1234.service.*.*(..))" /> 

        <aop:advisor advice-ref="txAdvice" pointcut-ref="serviceOperation" /> 

    </aop:config> 

 

</beans>

UserService.java

package com.java1234.service;

 

import com.java1234.entity.User;

 

public interface UserService {

 

    public User login(User user);

}

UserServiceImpl.java

package com.java1234.service.impl;

 

import javax.annotation.Resource;

 

import org.springframework.stereotype.Service;

 

import com.java1234.dao.UserDao;

import com.java1234.entity.User;

import com.java1234.service.UserService;

 

@Service("userService")

@Service("userService")

public class UserServiceImpl implements UserService{

 

@Resource

    @Resource

    private UserDao userDao;

   

    @Override

    public User login(User user) {

        return userDao.login(user);

    }

 

}

 

第一章 问候 MyBatis他大爷

第一节:MyBatis 简介

第二节:MyBatis 版HelloWorld 实现

第二章 MyBatis 项目配置

第一节:environments

第二节:transactionManager

第三节:dataSource

第四节:properties

第五节:typeAliases

第六节:mappers

第七节:配置Log4j 日志

MyBatis 支持多个环境,可以任意配置;

MyBatis 支持两种类型的事务管理器:JDBC 和MANAGED(托管);

JDBC:应用程序负责管理数据库连接的生命周期;

MANAGED : 由应用服务器负责管理数据库连接的生命周期; ( 一般商业服务器才有此功能, 如

JBOSS,WebLogic)

用来配置数据源;类型有:UNPOOLED,POOLED,JNDI;

UNPOOLED,没有连接池,每次数据库操作,MyBatis 都会创建一个新的连接,用完后,关闭;适合小并发

项目;

POOLED,用上了连接池;

JNDI,使用应用服务器配置JNDI 数据源获取数据库连接;

配置属性

给类的完成限定名取别名,方便使用;

引入映射文件

第三章 使用XML 配置SQL 映射器

第一节:INSERT 映射语句

第二节:UPDATE 映射语句

第三节:DELETE 映射语句

第四节:SELECT 映射语句

StudentTest2.java

package com.java1234.service;

 

import java.util.List;

 

import org.apache.ibatis.session.SqlSession;

import org.apache.log4j.Logger;

import org.junit.After;

import org.junit.Before;

import org.junit.Test;

 

import com.java1234.mappers.StudentMapper;

import com.java1234.model.Student;

import com.java1234.util.SqlSessionFactoryUtil;

 

public class StudentTest2 {

 

    private static Logger logger=Logger.getLogger(StudentTest.class);

    private SqlSession sqlSession=null;

    private StudentMapper studentMapper=null;

   

    /**

     * 测试方法前调用

     * @throws Exception

     */

    @Before

    public void setUp() throws Exception {

        sqlSession=SqlSessionFactoryUtil.openSession();

        studentMapper=sqlSession.getMapper(StudentMapper.class);

    }

 

    /**

     * 测试方法后调用

     * @throws Exception

     */

    @After

    public void tearDown() throws Exception {

        sqlSession.close();

    }

 

    @Test

    public void testAdd() {

        logger.info("添加学生");

        Student student=new Student("王五",12);

        studentMapper.add(student);

        sqlSession.commit();

    }

   

    @Test

    public void testUpdate(){

        logger.info("修改学生");

        Student student=new Student(8,"王五2",13);

        studentMapper.update(student);

        sqlSession.commit();

    }

   

    @Test

    public void testDelete(){

        logger.info("删除学生");

        studentMapper.delete(8);

        sqlSession.commit();

    }

   

    @Test

    public void testFindById(){

        logger.info("通过ID查找学生");

        Student student=studentMapper.findById(1);

        System.out.println(student);

    }

 

    @Test

    public void testFind(){

        logger.info("查找所有学生");

        List<Student> studentList=studentMapper.find();

        for(Student s:studentList){

            System.out.println(s);

        }

    }

}

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.java1234.mappers.StudentMapper">

resultMap

    <resultMap type="Student" id="StudentResult">

        <id property="id" column="id"/>

        <result property="name" column="name"/>

        <result property="age" column="age"/>

    </resultMap>

 

    <insert id="add" parameterType="Student"  >

        insert into t_student values(null,#{name},#{age})

    </insert>

 

    <update id="update" parameterType="Student">

        update t_student set name=#{name},age=#{age} where id=#{id}

    </update>

   

    <delete id="delete" parameterType="Integer">

        delete from t_student where id=#{id}

    </delete>

   

    <select id="findById" parameterType="Integer" resultType="Student">

        select * from t_student where id=#{id}

    </select>

   

    <select id="find" resultMap="StudentResult">

        select * from t_student

    </select>

</mapper>

 

第四章 MyBatis 关系映射

第一节:一对一关系实现

第二节:一对多关系实现

第五章 动态SQL

第一节:if 条件

第二节:choose,when 和otherwise 条件

第三节:where 条件

第四节:trim 条件

第五节:foreach 循环

第六节:set 条件

1,自动加上where;

2,如果where 子句以and 或者or 开头,则自动删除第一个and 或者or;

功能和where 元素类似,提供了前缀,后缀功能,更加灵活;

1,自动加上set;

2,自动剔除最后一个逗号“,”;

第六章 Mybatis 杂项

java1234 官网群14:235664654

第一节:处理CLOB、BLOB 类型数据

第二节:传入多个输入参数

第三节:Mybatis 分页

第四节:Mybatis 缓存

1,逻辑分页;

2,物理分页;

Mybatis 默认情况下,MyBatis 启用一级缓存,即同一个SqlSession 接口对象调用了相同的select 语句,则直

接会从缓存中返回结果,而不是再查询一次数据库;

开发者可以自己配置二级缓存,二级缓存是全局的;

默认情况下,select 使用缓存的,insert update delete 是不使用缓存的;

第七章 使用注解配置SQL 映射器

第一节:基本映射语句

第二节:结果集映射语句

第三节:关系映射

第四节:动态SQL

1,@Insert

2,@Update

3,@Delete

4,@Select

1,一对一映射;

2,一对多映射;

@InsertProvider

@UpdateProvider

@DeleteProvider

@SelectProvider

第八章 Mybatis 与Spring,SpringMvc 整合

第一节:Spring 与SpringMvc 整合

第二节:Spring 与Mybatis 整合

 

---------- MyBatis Generator

详解

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE generatorConfiguration

  PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"

"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

<!-- 配置生成器 -->

<generatorConfiguration>

<!-- 可以用于加载配置项或者配置文件,在整个配置文件中就可以使用${propertyKey}的方式来引用配置项

    resource:配置资源加载地址,使用resource,MBG从classpath开始找,比如com/myproject/generatorConfig.properties       

    url:配置资源加载地质,使用URL的方式,比如file:///C:/myfolder/generatorConfig.properties.

    注意,两个属性只能选址一个;

 

    另外,如果使用了mybatis-generator-maven-plugin,那么在pom.xml中定义的properties都可以直接在generatorConfig.xml中使用

<properties resource="" url="" />

 -->

 

 <!-- 在MBG工作的时候,需要额外加载的依赖包

     location属性指明加载jar/zip包的全路径

<classPathEntry location="/Program Files/IBM/SQLLIB/java/db2java.zip" />

  -->

 

<!--

    context:生成一组对象的环境

    id:必选,上下文id,用于在生成错误时提示

    defaultModelType:指定生成对象的样式

        1,conditional:类似hierarchical;

        2,flat:所有内容(主键,blob)等全部生成在一个对象中;

        3,hierarchical:主键生成一个XXKey对象(key class),Blob等单独生成一个对象,其他简单属性在一个对象中(record class)

    targetRuntime:

        1,MyBatis3:默认的值,生成基于MyBatis3.x以上版本的内容,包括XXXBySample;

        2,MyBatis3Simple:类似MyBatis3,只是不生成XXXBySample;

    introspectedColumnImpl:类全限定名,用于扩展MBG

-->

<context id="mysql" defaultModelType="hierarchical" targetRuntime="MyBatis3Simple" >

 

    <!-- 自动识别数据库关键字,默认false,如果设置为true,根据SqlReservedWords中定义的关键字列表;

        一般保留默认值,遇到数据库关键字(Java关键字),使用columnOverride覆盖

     -->

    <property name="autoDelimitKeywords" value="http://www.mamicode.com/false"/>

    <!-- 生成的Java文件的编码 -->

    <property name="javaFileEncoding" value="http://www.mamicode.com/UTF-8"/>

    <!-- 格式化java代码 -->

    <property name="javaFormatter" value="http://www.mamicode.com/org.mybatis.generator.api.dom.DefaultJavaFormatter"/>

    <!-- 格式化XML代码 -->

    <property name="xmlFormatter" value="http://www.mamicode.com/org.mybatis.generator.api.dom.DefaultXmlFormatter"/>

 

    <!-- beginningDelimiter和endingDelimiter:指明数据库的用于标记数据库对象名的符号,比如ORACLE就是双引号,MYSQL默认是`反引号; -->

    <property name="beginningDelimiter" value="http://www.mamicode.com/`"/>

    <property name="endingDelimiter" value="http://www.mamicode.com/`"/>

 

    <!-- 必须要有的,使用这个配置链接数据库

        @TODO:是否可以扩展

     -->

    <jdbcConnection driverClass="com.mysql.jdbc.Driver" connectionURL="jdbc:mysql:///pss" userId="root" password="admin">

        <!-- 这里面可以设置property属性,每一个property属性都设置到配置的Driver上 -->

    </jdbcConnection>

 

    <!-- java类型处理器

        用于处理DB中的类型到Java中的类型,默认使用JavaTypeResolverDefaultImpl;

        注意一点,默认会先尝试使用Integer,Long,Short等来对应DECIMAL和 NUMERIC数据类型;

    -->

    <javaTypeResolver type="org.mybatis.generator.internal.types.JavaTypeResolverDefaultImpl">

        <!--

            true:使用BigDecimal对应DECIMAL和 NUMERIC数据类型

            false:默认,

                scale>0;length>18:使用BigDecimal;

                scale=0;length[10,18]:使用Long;

                scale=0;length[5,9]:使用Integer;

                scale=0;length<5:使用Short;

         -->

        <property name="forceBigDecimals" value="http://www.mamicode.com/false"/>

    </javaTypeResolver>

 

 

    <!-- java模型创建器,是必须要的元素

        负责:1,key类(见context的defaultModelType);2,java类;3,查询类

        targetPackage:生成的类要放的包,真实的包受enableSubPackages属性控制;

        targetProject:目标项目,指定一个存在的目录下,生成的内容会放到指定目录中,如果目录不存在,MBG不会自动建目录

     -->

    <javaModelGenerator targetPackage="com._520it.mybatis.domain" targetProject="src/main/java">

        <!--  for MyBatis3/MyBatis3Simple

            自动为每一个生成的类创建一个构造方法,构造方法包含了所有的field;而不是使用setter;

         -->

        <property name="constructorBased" value="http://www.mamicode.com/false"/>

 

        <!-- 在targetPackage的基础上,根据数据库的schema再生成一层package,最终生成的类放在这个package下,默认为false -->

        <property name="enableSubPackages" value="http://www.mamicode.com/true"/>

 

        <!-- for MyBatis3 / MyBatis3Simple

            是否创建一个不可变的类,如果为true,

            那么MBG会创建一个没有setter方法的类,取而代之的是类似constructorBased的类

         -->

        <property name="immutable" value="http://www.mamicode.com/false"/>

 

        <!-- 设置一个根对象,

            如果设置了这个根对象,那么生成的keyClass或者recordClass会继承这个类;在Table的rootClass属性中可以覆盖该选项

            注意:如果在key class或者record class中有root class相同的属性,MBG就不会重新生成这些属性了,包括:

                1,属性名相同,类型相同,有相同的getter/setter方法;

         -->

        <property name="rootClass" value="http://www.mamicode.com/com._520it.mybatis.domain.BaseDomain"/>

 

        <!-- 设置是否在getter方法中,对String类型字段调用trim()方法 -->

        <property name="trimStrings" value="http://www.mamicode.com/true"/>

    </javaModelGenerator>

 

 

    <!-- 生成SQL map的XML文件生成器,

        注意,在Mybatis3之后,我们可以使用mapper.xml文件+Mapper接口(或者不用mapper接口),

            或者只使用Mapper接口+Annotation,所以,如果 javaClientGenerator配置中配置了需要生成XML的话,这个元素就必须配置

        targetPackage/targetProject:同javaModelGenerator

     -->

    <sqlMapGenerator targetPackage="com._520it.mybatis.mapper" targetProject="src/main/resources">

        <!-- 在targetPackage的基础上,根据数据库的schema再生成一层package,最终生成的类放在这个package下,默认为false -->

        <property name="enableSubPackages" value="http://www.mamicode.com/true"/>

    </sqlMapGenerator>

 

 

    <!-- 对于mybatis来说,即生成Mapper接口,注意,如果没有配置该元素,那么默认不会生成Mapper接口

        targetPackage/targetProject:同javaModelGenerator

        type:选择怎么生成mapper接口(在MyBatis3/MyBatis3Simple下):

            1,ANNOTATEDMAPPER:会生成使用Mapper接口+Annotation的方式创建(SQL生成在annotation中),不会生成对应的XML;

            2,MIXEDMAPPER:使用混合配置,会生成Mapper接口,并适当添加合适的Annotation,但是XML会生成在XML中;

            3,XMLMAPPER:会生成Mapper接口,接口完全依赖XML;

        注意,如果context是MyBatis3Simple:只支持ANNOTATEDMAPPER和XMLMAPPER

    -->

    <javaClientGenerator targetPackage="com._520it.mybatis.mapper" type="ANNOTATEDMAPPER" targetProject="src/main/java">

        <!-- 在targetPackage的基础上,根据数据库的schema再生成一层package,最终生成的类放在这个package下,默认为false -->

        <property name="enableSubPackages" value="http://www.mamicode.com/true"/>

 

        <!-- 可以为所有生成的接口添加一个父接口,但是MBG只负责生成,不负责检查

        <property name="rootInterface" value=""/>

         -->

    </javaClientGenerator>

 

    <!-- 选择一个table来生成相关文件,可以有一个或多个table,必须要有table元素

        选择的table会生成一下文件:

        1,SQL map文件

        2,生成一个主键类;

        3,除了BLOB和主键的其他字段的类;

        4,包含BLOB的类;

        5,一个用户生成动态查询的条件类(selectByExample, deleteByExample),可选;

        6,Mapper接口(可选)

 

        tableName(必要):要生成对象的表名;

        注意:大小写敏感问题。正常情况下,MBG会自动的去识别数据库标识符的大小写敏感度,在一般情况下,MBG会

            根据设置的schema,catalog或tablename去查询数据表,按照下面的流程:

            1,如果schema,catalog或tablename中有空格,那么设置的是什么格式,就精确的使用指定的大小写格式去查询;

            2,否则,如果数据库的标识符使用大写的,那么MBG自动把表名变成大写再查找;

            3,否则,如果数据库的标识符使用小写的,那么MBG自动把表名变成小写再查找;

            4,否则,使用指定的大小写格式查询;

        另外的,如果在创建表的时候,使用的""把数据库对象规定大小写,就算数据库标识符是使用的大写,在这种情况下也会使用给定的大小写来创建表名;

        这个时候,请设置delimitIdentifiers="true"即可保留大小写格式;

 

        可选:

        1,schema:数据库的schema;

        2,catalog:数据库的catalog;

        3,alias:为数据表设置的别名,如果设置了alias,那么生成的所有的SELECT SQL语句中,列名会变成:alias_actualColumnName

        4,domainObjectName:生成的domain类的名字,如果不设置,直接使用表名作为domain类的名字;可以设置为somepck.domainName,那么会自动把domainName类再放到somepck包里面;

        5,enableInsert(默认true):指定是否生成insert语句;

        6,enableSelectByPrimaryKey(默认true):指定是否生成按照主键查询对象的语句(就是getById或get);

        7,enableSelectByExample(默认true):MyBatis3Simple为false,指定是否生成动态查询语句;

        8,enableUpdateByPrimaryKey(默认true):指定是否生成按照主键修改对象的语句(即update);

        9,enableDeleteByPrimaryKey(默认true):指定是否生成按照主键删除对象的语句(即delete);

        10,enableDeleteByExample(默认true):MyBatis3Simple为false,指定是否生成动态删除语句;

        11,enableCountByExample(默认true):MyBatis3Simple为false,指定是否生成动态查询总条数语句(用于分页的总条数查询);

        12,enableUpdateByExample(默认true):MyBatis3Simple为false,指定是否生成动态修改语句(只修改对象中不为空的属性);

        13,modelType:参考context元素的defaultModelType,相当于覆盖;

        14,delimitIdentifiers:参考tableName的解释,注意,默认的delimitIdentifiers是双引号,如果类似MYSQL这样的数据库,使用的是`(反引号,那么还需要设置context的beginningDelimiter和endingDelimiter属性)

        15,delimitAllColumns:设置是否所有生成的SQL中的列名都使用标识符引起来。默认为false,delimitIdentifiers参考context的属性

 

        注意,table里面很多参数都是对javaModelGenerator,context等元素的默认属性的一个复写;

     -->

    <table tableName="userinfo" >

 

        <!-- 参考 javaModelGenerator 的 constructorBased属性-->

        <property name="constructorBased" value="http://www.mamicode.com/false"/>

 

        <!-- 默认为false,如果设置为true,在生成的SQL中,table名字不会加上catalog或schema; -->

        <property name="ignoreQualifiersAtRuntime" value="http://www.mamicode.com/false"/>

 

        <!-- 参考 javaModelGenerator 的 immutable 属性 -->

        <property name="immutable" value="http://www.mamicode.com/false"/>

 

        <!-- 指定是否只生成domain类,如果设置为true,只生成domain类,如果还配置了sqlMapGenerator,那么在mapper XML文件中,只生成resultMap元素 -->

        <property name="modelOnly" value="http://www.mamicode.com/false"/>

 

        <!-- 参考 javaModelGenerator 的 rootClass 属性

        <property name="rootClass" value=""/>

         -->

 

        <!-- 参考javaClientGenerator 的  rootInterface 属性

        <property name="rootInterface" value=""/>

        -->

 

        <!-- 如果设置了runtimeCatalog,那么在生成的SQL中,使用该指定的catalog,而不是table元素上的catalog

        <property name="runtimeCatalog" value=""/>

        -->

 

        <!-- 如果设置了runtimeSchema,那么在生成的SQL中,使用该指定的schema,而不是table元素上的schema

        <property name="runtimeSchema" value=""/>

        -->

 

        <!-- 如果设置了runtimeTableName,那么在生成的SQL中,使用该指定的tablename,而不是table元素上的tablename

        <property name="runtimeTableName" value=""/>

        -->

 

        <!-- 注意,该属性只针对MyBatis3Simple有用;

            如果选择的runtime是MyBatis3Simple,那么会生成一个SelectAll方法,如果指定了selectAllOrderByClause,那么会在该SQL中添加指定的这个order条件;

         -->

        <property name="selectAllOrderByClause" value="http://www.mamicode.com/age desc,username asc"/>

 

        <!-- 如果设置为true,生成的model类会直接使用column本身的名字,而不会再使用驼峰命名方法,比如BORN_DATE,生成的属性名字就是BORN_DATE,而不会是bornDate -->

        <property name="useActualColumnNames" value="http://www.mamicode.com/false"/>

 

 

        <!-- generatedKey用于生成生成主键的方法,

            如果设置了该元素,MBG会在生成的<insert>元素中生成一条正确的<selectKey>元素,该元素可选

            column:主键的列名;

            sqlStatement:要生成的selectKey语句,有以下可选项:

                Cloudscape:相当于selectKey的SQL为: VALUES IDENTITY_VAL_LOCAL()

                DB2       :相当于selectKey的SQL为: VALUES IDENTITY_VAL_LOCAL()

                DB2_MF    :相当于selectKey的SQL为:SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1

                Derby      :相当于selectKey的SQL为:VALUES IDENTITY_VAL_LOCAL()

                HSQLDB      :相当于selectKey的SQL为:CALL IDENTITY()

                Informix  :相当于selectKey的SQL为:select dbinfo(‘sqlca.sqlerrd1‘) from systables where tabid=1

                MySql      :相当于selectKey的SQL为:SELECT LAST_INSERT_ID()

                SqlServer :相当于selectKey的SQL为:SELECT SCOPE_IDENTITY()

                SYBASE      :相当于selectKey的SQL为:SELECT @@IDENTITY

                JDBC      :相当于在生成的insert元素上添加useGeneratedKeys="true"和keyProperty属性

        <generatedKey column="" sqlStatement=""/>

         -->

 

        <!--

            该元素会在根据表中列名计算对象属性名之前先重命名列名,非常适合用于表中的列都有公用的前缀字符串的时候,

            比如列名为:CUST_ID,CUST_NAME,CUST_EMAIL,CUST_ADDRESS等;

            那么就可以设置searchString为"^CUST_",并使用空白替换,那么生成的Customer对象中的属性名称就不是

            custId,custName等,而是先被替换为ID,NAME,EMAIL,然后变成属性:id,name,email;

 

            注意,MBG是使用java.util.regex.Matcher.replaceAll来替换searchString和replaceString的,

            如果使用了columnOverride元素,该属性无效;

 

        <columnRenamingRule searchString="" replaceString=""/>

         -->

 

 

         <!-- 用来修改表中某个列的属性,MBG会使用修改后的列来生成domain的属性;

             column:要重新设置的列名;

             注意,一个table元素中可以有多个columnOverride元素哈~

          -->

         <columnOverride column="username">

             <!-- 使用property属性来指定列要生成的属性名称 -->

             <property name="property" value="http://www.mamicode.com/userName"/>

 

             <!-- javaType用于指定生成的domain的属性类型,使用类型的全限定名

             <property name="javaType" value=""/>

              -->

 

             <!-- jdbcType用于指定该列的JDBC类型

             <property name="jdbcType" value=""/>

              -->

 

             <!-- typeHandler 用于指定该列使用到的TypeHandler,如果要指定,配置类型处理器的全限定名

                 注意,mybatis中,不会生成到mybatis-config.xml中的typeHandler

                 只会生成类似:where id = #{id,jdbcType=BIGINT,typeHandler=com._520it.mybatis.MyTypeHandler}的参数描述

             <property name="jdbcType" value=""/>

             -->

 

             <!-- 参考table元素的delimitAllColumns配置,默认为false

             <property name="delimitedColumnName" value=""/>

              -->

         </columnOverride>

 

         <!-- ignoreColumn设置一个MGB忽略的列,如果设置了改列,那么在生成的domain中,生成的SQL中,都不会有该列出现

             column:指定要忽略的列的名字;

             delimitedColumnName:参考table元素的delimitAllColumns配置,默认为false

 

             注意,一个table元素中可以有多个ignoreColumn元素

         <ignoreColumn column="deptId" delimitedColumnName=""/>

         -->

    </table>

 

</context>

 

</generatorConfiguration>

 

动态SQL 1

MyBatis的动态SQL是基于OGNL表达式的,它可以帮助我们方便的在SQL语句中实现某些逻辑。

MyBatis中用于实现动态SQL的元素主要有:

if

where

set

choose(when,otherwise)

trim

foreach

1、if标签

if标签可用在许多类型的sql语句中,我们以查询为例。首先看一个很普通的查询:

<!-- 查询学生list,like姓名 --> 

<select id="getStudentListLikeName" parameterType="StudentEntity" resultMap="studentResultMap"> 

    SELECT * from STUDENT_TBL ST  

WHERE ST.STUDENT_NAME LIKE CONCAT(CONCAT(‘%‘, #{studentName}),‘%‘) 

</select> 

但是此时如果studentName为null,此语句很可能报错或查询结果为空。此时我们使用if动态sql语句先进行判断,如果值为null或等于空字符串,我们就不进行此条件的判断,增加灵活性。

参数为实体类StudentEntity。将实体类中所有的属性均进行判断,如果不为空则执行判断条件。

<!-- 2 if(判断参数) - 将实体类不为空的属性作为where条件 --> 

<select id="getStudentList_if" resultMap="resultMap_studentEntity" parameterType="liming.student.manager.data.model.StudentEntity"> 

    SELECT ST.STUDENT_ID, 

           ST.STUDENT_NAME, 

           ST.STUDENT_SEX, 

           ST.STUDENT_BIRTHDAY, 

           ST.STUDENT_PHOTO, 

           ST.CLASS_ID, 

           ST.PLACE_ID 

      FROM STUDENT_TBL ST  

     WHERE 

    <if test="studentName !=null "> 

        ST.STUDENT_NAME LIKE CONCAT(CONCAT(‘%‘, #{studentName, jdbcType=VARCHAR}),‘%‘) 

    </if> 

    <if test="studentSex != null and studentSex != ‘‘ "> 

        AND ST.STUDENT_SEX = #{studentSex, jdbcType=INTEGER} 

    </if> 

    <if test="studentBirthday != null "> 

        AND ST.STUDENT_BIRTHDAY = #{studentBirthday, jdbcType=DATE} 

    </if> 

    <if test="classId != null and classId!= ‘‘ "> 

        AND ST.CLASS_ID = #{classId, jdbcType=VARCHAR} 

    </if> 

    <if test="classEntity != null and classEntity.classId !=null and classEntity.classId !=‘ ‘ "> 

        AND ST.CLASS_ID = #{classEntity.classId, jdbcType=VARCHAR} 

    </if> 

    <if test="placeId != null and placeId != ‘‘ "> 

        AND ST.PLACE_ID = #{placeId, jdbcType=VARCHAR} 

    </if> 

    <if test="placeEntity != null and placeEntity.placeId != null and placeEntity.placeId != ‘‘ "> 

        AND ST.PLACE_ID = #{placeEntity.placeId, jdbcType=VARCHAR} 

    </if> 

    <if test="studentId != null and studentId != ‘‘ "> 

        AND ST.STUDENT_ID = #{studentId, jdbcType=VARCHAR} 

    </if>  

</select> 

使用时比较灵活, new一个这样的实体类,我们需要限制那个条件,只需要附上相应的值就会where这个条件,相反不去赋值就可以不在where中判断。

public void select_test_2_1() { 

    StudentEntity entity = new StudentEntity(); 

    entity.setStudentName(""); 

    entity.setStudentSex(1); 

    entity.setStudentBirthday(DateUtil.parse("1985-05-28")); 

    entity.setClassId("20000001"); 

    //entity.setPlaceId("70000001"); 

    List<StudentEntity> list = this.dynamicSqlMapper.getStudentList_if(entity); 

    for (StudentEntity e : list) { 

        System.out.println(e.toString()); 

    } 

2、if + where 的条件判断

当where中的条件使用的if标签较多时,这样的组合可能会导致错误。我们以在1中的查询语句为例子,当Java代码按如下方法调用时:

@Test 

public void select_test_2_1() { 

    StudentEntity entity = new StudentEntity(); 

    entity.setStudentName(null); 

    entity.setStudentSex(1); 

    List<StudentEntity> list = this.dynamicSqlMapper.getStudentList_if(entity); 

    for (StudentEntity e : list) { 

        System.out.println(e.toString()); 

    } 

如果上面例子,参数studentName为null,将不会进行STUDENT_NAME列的判断,则会直接导“WHERE AND”关键字多余的错误SQL。

这时我们可以使用where动态语句来解决。这个“where”标签会知道如果它包含的标签中有返回值的话,它就插入一个‘where’。此外,如果标签返回的内容是以AND 或OR 开头的,则它会剔除掉。

上面例子修改为:

<!-- 3 select - where/if(判断参数) - 将实体类不为空的属性作为where条件 --> 

<select id="getStudentList_whereIf" resultMap="resultMap_studentEntity" parameterType="liming.student.manager.data.model.StudentEntity"> 

    SELECT ST.STUDENT_ID, 

           ST.STUDENT_NAME, 

           ST.STUDENT_SEX, 

           ST.STUDENT_BIRTHDAY, 

           ST.STUDENT_PHOTO, 

           ST.CLASS_ID, 

           ST.PLACE_ID 

      FROM STUDENT_TBL ST  

    <where> 

        <if test="studentName !=null "> 

            ST.STUDENT_NAME LIKE CONCAT(CONCAT(‘%‘, #{studentName, jdbcType=VARCHAR}),‘%‘) 

        </if> 

        <if test="studentSex != null and studentSex != ‘‘ "> 

            AND ST.STUDENT_SEX = #{studentSex, jdbcType=INTEGER} 

        </if> 

        <if test="studentBirthday != null "> 

            AND ST.STUDENT_BIRTHDAY = #{studentBirthday, jdbcType=DATE} 

        </if> 

        <if test="classId != null and classId!= ‘‘ "> 

            AND ST.CLASS_ID = #{classId, jdbcType=VARCHAR} 

        </if> 

        <if test="classEntity != null and classEntity.classId !=null and classEntity.classId !=‘ ‘ "> 

            AND ST.CLASS_ID = #{classEntity.classId, jdbcType=VARCHAR} 

        </if> 

        <if test="placeId != null and placeId != ‘‘ "> 

            AND ST.PLACE_ID = #{placeId, jdbcType=VARCHAR} 

        </if> 

        <if test="placeEntity != null and placeEntity.placeId != null and placeEntity.placeId != ‘‘ "> 

            AND ST.PLACE_ID = #{placeEntity.placeId, jdbcType=VARCHAR} 

        </if> 

        <if test="studentId != null and studentId != ‘‘ "> 

            AND ST.STUDENT_ID = #{studentId, jdbcType=VARCHAR} 

        </if> 

    </where>   

</select> 

3、if + set 的更新语句

当update语句中没有使用if标签时,如果有一个参数为null,都会导致错误。

当在update语句中使用if标签时,如果前面的if没有执行,则或导致逗号多余错误。使用set标签可以将动态的配置SET 关键字,和剔除追加到条件末尾的任何不相关的逗号。如果set包含的内容为空的话则会出错。

使用if+set标签修改后,如果某项为null则不进行更新,而是保持数据库原值。如下示例:

<!-- 4 if/set(判断参数) - 将实体类不为空的属性更新 --> 

<update id="updateStudent_if_set" parameterType="liming.student.manager.data.model.StudentEntity"> 

    UPDATE STUDENT_TBL 

    <set> 

        <if test="studentName != null and studentName != ‘‘ "> 

            STUDENT_TBL.STUDENT_NAME = #{studentName}, 

        </if> 

        <if test="studentSex != null and studentSex != ‘‘ "> 

            STUDENT_TBL.STUDENT_SEX = #{studentSex}, 

        </if> 

        <if test="studentBirthday != null "> 

            STUDENT_TBL.STUDENT_BIRTHDAY = #{studentBirthday}, 

        </if> 

        <if test="studentPhoto != null "> 

            STUDENT_TBL.STUDENT_PHOTO = #{studentPhoto, javaType=byte[], jdbcType=BLOB, typeHandler=org.apache.ibatis.type.BlobTypeHandler}, 

        </if> 

        <if test="classId != ‘‘ "> 

            STUDENT_TBL.CLASS_ID = #{classId} 

        </if> 

        <if test="placeId != ‘‘ "> 

            STUDENT_TBL.PLACE_ID = #{placeId} 

        </if> 

    </set> 

    WHERE STUDENT_TBL.STUDENT_ID = #{studentId};     

</update> 

4、choose (when, otherwise)

有时候我们并不想应用所有的条件,而只是想从多个选项中选择一个。而使用if标签时,只要test中的表达式为true,就会执行if标签中的条件。MyBatis提供了choose 元素。if标签是与(and)的关系,而choose标签是或(or)的关系。

choose标签是按顺序判断其内部when标签中的test条件出否成立,如果有一个成立,则choose结束。当choose中所有when的条件都不满则时,则执行otherwise中的sql。类似于Java 的switch 语句,choose为switch,when为case,otherwise则为default。

例如下面例子,同样把所有可以限制的条件都写上,方面使用。choose会从上到下选择一个when标签的test为true的sql执行。安全考虑,我们使用where将choose包起来,放置关键字多于错误。

<!-- 6 choose(判断参数) - 按顺序将实体类第一个不为空的属性作为where条件 --> 

<select id="getStudentList_choose" resultMap="resultMap_studentEntity" parameterType="liming.student.manager.data.model.StudentEntity"> 

    SELECT ST.STUDENT_ID, 

           ST.STUDENT_NAME, 

           ST.STUDENT_SEX, 

           ST.STUDENT_BIRTHDAY, 

           ST.STUDENT_PHOTO, 

           ST.CLASS_ID, 

           ST.PLACE_ID 

      FROM STUDENT_TBL ST  

    <where> 

        <choose> 

            <when test="studentName !=null "> 

                ST.STUDENT_NAME LIKE CONCAT(CONCAT(‘%‘, #{studentName, jdbcType=VARCHAR}),‘%‘) 

            </when > 

            <when test="studentSex != null and studentSex != ‘‘ "> 

                AND ST.STUDENT_SEX = #{studentSex, jdbcType=INTEGER} 

            </when > 

            <when test="studentBirthday != null "> 

                AND ST.STUDENT_BIRTHDAY = #{studentBirthday, jdbcType=DATE} 

            </when > 

            <when test="classId != null and classId!= ‘‘ "> 

                AND ST.CLASS_ID = #{classId, jdbcType=VARCHAR} 

            </when > 

            <when test="classEntity != null and classEntity.classId !=null and classEntity.classId !=‘ ‘ "> 

                AND ST.CLASS_ID = #{classEntity.classId, jdbcType=VARCHAR} 

            </when > 

            <when test="placeId != null and placeId != ‘‘ "> 

                AND ST.PLACE_ID = #{placeId, jdbcType=VARCHAR} 

            </when > 

            <when test="placeEntity != null and placeEntity.placeId != null and placeEntity.placeId != ‘‘ "> 

                AND ST.PLACE_ID = #{placeEntity.placeId, jdbcType=VARCHAR} 

            </when > 

            <when test="studentId != null and studentId != ‘‘ "> 

                AND ST.STUDENT_ID = #{studentId, jdbcType=VARCHAR} 

            </when > 

            <otherwise> 

            </otherwise> 

        </choose> 

    </where>   

</select> 

5、trim标签

trim元素的主要功能是可以在自己包含的内容前加上某些前缀,也可以在其后加上某些后缀,与之对应的属性是prefix和suffix;可以把包含内容的首部某些内容覆盖,即忽略,也可以把尾部的某些内容覆盖,对应的属性是prefixOverrides和suffixOverrides。正因为trim有这样的功能,所以我们也可以非常简单的利用trim来代替where/set标签的功能,示例代码如下:

trim代替where标签:

<!-- 5.1 if/trim代替where(判断参数) - 将实体类不为空的属性作为where条件 --> 

<select id="getStudentList_if_trim" resultMap="resultMap_studentEntity"> 

    SELECT ST.STUDENT_ID, 

           ST.STUDENT_NAME, 

           ST.STUDENT_SEX, 

           ST.STUDENT_BIRTHDAY, 

           ST.STUDENT_PHOTO, 

           ST.CLASS_ID, 

           ST.PLACE_ID 

      FROM STUDENT_TBL ST  

    <trim prefix="WHERE" prefixOverrides="AND|OR"> 

        <if test="studentName !=null "> 

            ST.STUDENT_NAME LIKE CONCAT(CONCAT(‘%‘, #{studentName, jdbcType=VARCHAR}),‘%‘) 

        </if> 

        <if test="studentSex != null and studentSex != ‘‘ "> 

            AND ST.STUDENT_SEX = #{studentSex, jdbcType=INTEGER} 

        </if> 

        <if test="studentBirthday != null "> 

            AND ST.STUDENT_BIRTHDAY = #{studentBirthday, jdbcType=DATE} 

        </if> 

        <if test="classId != null and classId!= ‘‘ "> 

            AND ST.CLASS_ID = #{classId, jdbcType=VARCHAR} 

        </if> 

        <if test="classEntity != null and classEntity.classId !=null and classEntity.classId !=‘ ‘ "> 

            AND ST.CLASS_ID = #{classEntity.classId, jdbcType=VARCHAR} 

        </if> 

        <if test="placeId != null and placeId != ‘‘ "> 

            AND ST.PLACE_ID = #{placeId, jdbcType=VARCHAR} 

        </if> 

        <if test="placeEntity != null and placeEntity.placeId != null and placeEntity.placeId != ‘‘ "> 

            AND ST.PLACE_ID = #{placeEntity.placeId, jdbcType=VARCHAR} 

        </if> 

        <if test="studentId != null and studentId != ‘‘ "> 

            AND ST.STUDENT_ID = #{studentId, jdbcType=VARCHAR} 

        </if> 

    </trim>    

</select>

trim代替set标签

<!-- 5.2 if/trim代替set(判断参数) - 将实体类不为空的属性更新 --> 

<update id="updateStudent_if_trim" parameterType="liming.student.manager.data.model.StudentEntity"> 

    UPDATE STUDENT_TBL 

    <trim prefix="SET" suffixOverrides=","> 

        <if test="studentName != null and studentName != ‘‘ "> 

            STUDENT_TBL.STUDENT_NAME = #{studentName}, 

        </if> 

        <if test="studentSex != null and studentSex != ‘‘ "> 

            STUDENT_TBL.STUDENT_SEX = #{studentSex}, 

        </if> 

        <if test="studentBirthday != null "> 

            STUDENT_TBL.STUDENT_BIRTHDAY = #{studentBirthday}, 

        </if> 

        <if test="studentPhoto != null "> 

            STUDENT_TBL.STUDENT_PHOTO = #{studentPhoto, javaType=byte[], jdbcType=BLOB, typeHandler=org.apache.ibatis.type.BlobTypeHandler}, 

        </if> 

        <if test="classId != ‘‘ "> 

            STUDENT_TBL.CLASS_ID = #{classId}, 

        </if> 

        <if test="placeId != ‘‘ "> 

            STUDENT_TBL.PLACE_ID = #{placeId} 

        </if> 

    </trim> 

    WHERE STUDENT_TBL.STUDENT_ID = #{studentId} 

</update>

6、foreach标签

foreach的主要用在构建in条件中,它可以在SQL语句中进行迭代一个集合。foreach元素的属性主要有item,index,collection,open,separator,close。

item表示集合中每一个元素进行迭代时的别名;

index指定一个名字,用于表示在迭代过程中,每次迭代到的位置;

open表示该语句以什么开始;

separator表示在每次进行迭代之间以什么符号作为分隔符;

close表示以什么结束;

在使用foreach的时候最关键的也是最容易出错的就是collection属性,该属性是必须指定的,但是在不同情况下,该属性的值是不一样的,主要有一下3种情况:

如果传入的是单参数且参数类型是一个List的时候,collection属性值为list

如果传入的是单参数且参数类型是一个array数组的时候,collection的属性值为array

如果传入的参数是多个的时候,我们就需要把它们封装成一个Map了,当然单参数也可以封装成map,实际上如果你在传入参数的时候,在MyBatis里面也是会把它封装成一个Map的,map的key就是参数名,所以这个时候collection属性值就是传入的List或array对象在自己封装的map里面的key

下面分别来看看上述三种情况的示例代码:

1)单参数List的类型:

<select id="dynamicForeachTest" resultType="Blog"> 

    select * from t_blog where id in 

    <foreach collection="list" index="index" item="item" open="(" separator="," close=")"> 

        #{item} 

    </foreach> 

</select> 

上述collection的值为list,对应的Mapper是这样的:

public List<Blog> dynamicForeachTest(List<Integer> ids); 

测试代码:

@Test 

public void dynamicForeachTest() { 

    SqlSession session = Util.getSqlSessionFactory().openSession(); 

    BlogMapper blogMapper = session.getMapper(BlogMapper.class); 

    List<Integer> ids = new ArrayList<Integer>(); 

    ids.add(1); 

    ids.add(3); 

    ids.add(6); 

    List<Blog> blogs = blogMapper.dynamicForeachTest(ids); 

    for (Blog blog : blogs) 

        System.out.println(blog); 

    session.close(); 

}

2)单参数array数组的类型:

<select id="dynamicForeach2Test" resultType="Blog"> 

    select * from t_blog where id in 

    <foreach collection="array" index="index" item="item" open="(" separator="," close=")"> 

        #{item} 

    </foreach> 

</select> 

上述collection为array,对应的Mapper代码:

public List<Blog> dynamicForeach2Test(int[] ids); 

测试代码:

@Test 

public void dynamicForeach2Test() { 

    SqlSession session = Util.getSqlSessionFactory().openSession(); 

    BlogMapper blogMapper = session.getMapper(BlogMapper.class); 

    int[] ids = new int[] {1,3,6,9}; 

    List<Blog> blogs = blogMapper.dynamicForeach2Test(ids); 

    for (Blog blog : blogs) 

        System.out.println(blog); 

    session.close(); 

3)自己把参数封装成Map的类型

<select id="dynamicForeach3Test" resultType="Blog"> 

    select * from t_blog where title like "%"#{title}"%" and id in 

    <foreach collection="ids" index="index" item="item" open="(" separator="," close=")"> 

        #{item} 

    </foreach> 

</select> 

上述collection的值为ids,是传入的参数Map的key,对应的Mapper代码:

public List<Blog> dynamicForeach3Test(Map<String, Object> params);

测试代码:

@Test 

public void dynamicForeach3Test() { 

    SqlSession session = Util.getSqlSessionFactory().openSession(); 

    BlogMapper blogMapper = session.getMapper(BlogMapper.class); 

    final List<Integer> ids = new ArrayList<Integer>(); 

    ids.add(1); 

    ids.add(2); 

    ids.add(3); 

    ids.add(6); 

    ids.add(7); 

    ids.add(9); 

    Map<String, Object> params = new HashMap<String, Object>(); 

    params.put("ids", ids); 

    params.put("title", "中国"); 

    List<Blog> blogs = blogMapper.dynamicForeach3Test(params); 

    for (Blog blog : blogs) 

        System.out.println(blog); 

    session.close(); 

}

 

动态SQL 2

1、动态SQL片段

通过SQL片段达到代码复用

<!-- 动态条件分页查询 -->

<sql id="sql_count">

        select count(*)

</sql>

<sql id="sql_select">

        select *

</sql>

<sql id="sql_where">

        from icp

        <dynamic prepend="where">

                <isNotEmpty prepend="and" property="name">

                        name like ‘%$name$%‘

                </isNotEmpty>

                <isNotEmpty prepend="and" property="path">

                        path like ‘%path$%‘

                </isNotEmpty>

                <isNotEmpty prepend="and" property="area_id">

                        area_id = #area_id#

                </isNotEmpty>

                <isNotEmpty prepend="and" property="hided">

                        hided = #hided#

                </isNotEmpty>

        </dynamic>

        <dynamic prepend="">

                <isNotNull property="_start">

                        <isNotNull property="_size">

                                limit #_start#, #_size#

                        </isNotNull>

                </isNotNull>

        </dynamic>

</sql>

<select id="findByParamsForCount" parameterClass="map" resultClass="int">

        <include refid="sql_count"/>

        <include refid="sql_where"/>

</select>

<select id="findByParams" parameterClass="map" resultMap="icp.result_base">

        <include refid="sql_select"/>

        <include refid="sql_where"/>

</select>

 

2、数字范围查询

所传参数名称是捏造所得,非数据库字段,比如_img_size_ge、_img_size_lt字段

<isNotEmpty prepend="and" property="_img_size_ge">

        <![CDATA[

        img_size >= #_img_size_ge#

]]>

</isNotEmpty>

<isNotEmpty prepend="and" property="_img_size_lt">

        <![CDATA[

        img_size < #_img_size_lt#

]]>

</isNotEmpty>

 

多次使用一个参数也是允许的

<isNotEmpty prepend="and" property="_now">

        <![CDATA[

                    execplantime >= #_now#

             ]]>

</isNotEmpty>

<isNotEmpty prepend="and" property="_now">

        <![CDATA[

                    closeplantime <= #_now#

             ]]>

</isNotEmpty>

 

3、时间范围查询

<isNotEmpty prepend="" property="_starttime">

        <isNotEmpty prepend="and" property="_endtime">

                <![CDATA[

                createtime >= #_starttime#

                and createtime < #_endtime#

         ]]>

        </isNotEmpty>

</isNotEmpty>

 

4、in查询

<isNotEmpty prepend="and" property="_in_state">

        state in (‘$_in_state$‘)

</isNotEmpty>

 

5、like查询

<isNotEmpty prepend="and" property="chnameone">

        (chnameone like ‘%$chnameone$%‘ or spellinitial like ‘%$chnameone$%‘)

</isNotEmpty>

<isNotEmpty prepend="and" property="chnametwo">

        chnametwo like ‘%$chnametwo$%‘

</isNotEmpty>

 

6、or条件

<isEqual prepend="and" property="_exeable" compareValue="http://www.mamicode.com/N">

        <![CDATA[

        (t.finished=‘11‘    or t.failure=3)

]]>

</isEqual>

 

<isEqual prepend="and" property="_exeable" compareValue="http://www.mamicode.com/Y">

        <![CDATA[

        t.finished in (‘10‘,‘19‘) and t.failure<3

]]>

</isEqual>

 

7、where子查询

<isNotEmpty prepend="" property="exprogramcode">

        <isNotEmpty prepend="" property="isRational">

                <isEqual prepend="and" property="isRational" compareValue="http://www.mamicode.com/N">

                        code not in

                        (select t.contentcode

                        from cms_ccm_programcontent t

                        where t.contenttype=‘MZNRLX_MA‘

                        and t.programcode = #exprogramcode#)

                </isEqual>

        </isNotEmpty>

</isNotEmpty>

8

<select id="findByProgramcode" parameterClass="string" resultMap="cms_ccm_material.result">

        select *

        from cms_ccm_material

        where code in

        (select t.contentcode

        from cms_ccm_programcontent t

        where t.contenttype = ‘MZNRLX_MA‘

        and programcode = #value#)

        order by updatetime desc

</select>

 

9、函数的使用

<!-- 添加 -->

<insert id="insert" parameterClass="RuleMaster">

        insert into rulemaster(

        name,

        createtime,

        updatetime,

        remark

        ) values (

        #name#,

        now(),

        now(),

        #remark#

        )

        <selectKey keyProperty="id" resultClass="long">

                select LAST_INSERT_ID()

        </selectKey>

</insert>

<!-- 更新 -->

<update id="update" parameterClass="RuleMaster">

        update rulemaster set

        name = #name#,

        updatetime = now(),

        remark = #remark#

        where id = #id#

</update>

 

10、map结果集

<!-- 动态条件分页查询 -->

<sql id="sql_count">

        select count(a.*)

</sql>

<sql id="sql_select">

        select a.id                vid,

        a.img             imgurl,

        a.img_s         imgfile,

        b.vfilename vfilename,

b.name            name,

        c.id                sid,

        c.url             url,

        c.filename    filename,

        c.status        status

</sql>

<sql id="sql_where">

        From secfiles c, juji b, videoinfo a

        where

        a.id = b. videoid

        and b.id = c.segmentid

        and c.status = 0

        order by a.id asc,b.id asc,c.sortnum asc

        <dynamic prepend="">

                <isNotNull property="_start">

                        <isNotNull property="_size">

                                limit #_start#, #_size#

                        </isNotNull>

                </isNotNull>

        </dynamic>

</sql>

<!-- 返回没有下载的记录总数 -->

<select id="getUndownFilesForCount" parameterClass="map" resultClass="int">

        <include refid="sql_count"/>

        <include refid="sql_where"/>

</select>

<!-- 返回没有下载的记录 -->

<select id="getUndownFiles" parameterClass="map" resultClass="java.util.HashMap">

        <include refid="sql_select"/>

        <include refid="sql_where"/>

</select>

 

11、trim

 trim是更灵活的去处多余关键字的标签,他可以实践where和set的效果。

 

 where例子的等效trim语句:

Xml代码 

<!-- 查询学生list,like姓名,=性别 -->  

<select id="getStudentListWhere" parameterType="StudentEntity" resultMap="studentResultMap">  

    SELECT * from STUDENT_TBL ST   

    <trim prefix="WHERE" prefixOverrides="AND|OR">  

        <if test="studentName!=null and studentName!=‘‘ ">  

            ST.STUDENT_NAME LIKE CONCAT(CONCAT(‘%‘, #{studentName}),‘%‘)   

        </if>  

        <if test="studentSex!= null and studentSex!= ‘‘ ">  

            AND ST.STUDENT_SEX = #{studentSex}   

        </if>  

    </trim>  

</select>

 

 

set例子的等效trim语句:

Xml代码 

<!-- 更新学生信息 -->  

<update id="updateStudent" parameterType="StudentEntity">  

    UPDATE STUDENT_TBL   

    <trim prefix="SET" suffixOverrides=",">  

        <if test="studentName!=null and studentName!=‘‘ ">  

            STUDENT_TBL.STUDENT_NAME = #{studentName},   

        </if>  

        <if test="studentSex!=null and studentSex!=‘‘ ">  

            STUDENT_TBL.STUDENT_SEX = #{studentSex},   

        </if>  

        <if test="studentBirthday!=null ">  

            STUDENT_TBL.STUDENT_BIRTHDAY = #{studentBirthday},   

        </if>  

        <if test="classEntity!=null and classEntity.classID!=null and classEntity.classID!=‘‘ ">  

            STUDENT_TBL.CLASS_ID = #{classEntity.classID}   

        </if>  

    </trim>  

    WHERE STUDENT_TBL.STUDENT_ID = #{studentID};   

</update>  

 

 

12、choose (when, otherwise)

         有时候我们并不想应用所有的条件,而只是想从多个选项中选择一个。MyBatis提供了choose 元素,按顺序判断when中的条件出否成立,如果有一个成立,则choose结束。当choose中所有when的条件都不满则时,则执行 otherwise中的sql。类似于Java 的switch 语句,choose为switch,when为case,otherwise则为default。

         if是与(and)的关系,而choose是或(or)的关系。

 

         例如下面例子,同样把所有可以限制的条件都写上,方面使用。选择条件顺序,when标签的从上到下的书写顺序:

Xml代码 

<!-- 查询学生list,like姓名、或=性别、或=生日、或=班级,使用choose -->  

<select id="getStudentListChooseEntity" parameterType="StudentEntity" resultMap="studentResultMap">  

    SELECT * from STUDENT_TBL ST   

    <where>  

        <choose>  

            <when test="studentName!=null and studentName!=‘‘ ">  

                    ST.STUDENT_NAME LIKE CONCAT(CONCAT(‘%‘, #{studentName}),‘%‘)   

            </when>  

            <when test="studentSex!= null and studentSex!= ‘‘ ">  

                    AND ST.STUDENT_SEX = #{studentSex}   

            </when>   

            <when test="studentBirthday!=null">  

                AND ST.STUDENT_BIRTHDAY = #{studentBirthday}   

            </when>  

            <when test="classEntity!=null and classEntity.classID !=null and classEntity.classID!=‘‘ ">  

                AND ST.CLASS_ID = #{classEntity.classID}   

            </when>  

            <otherwise>  

                   

            </otherwise>  

        </choose>  

    </where>  

</select> 

 

动态SQL 3

1. if

作为程序猿,谁不懂 if !  在mybatis中也能用 if 啦:

<select id="findUserById" resultType="user">

           select * from user where

           <if test="id != null">

               id=#{id}

           </if>

            and deleteFlag=0;

</select>

上面例子: 如果传入的id 不为空, 那么才会SQL才拼接id = #{id}。 这个相信大家看一样就能明白,不多说。

细心的人会发现一个问题:“你这不对啊! 要是你传入的id为null,  那么你这最终的SQL语句不就成了 select * from user where and deleteFlag=0,  这语句有问题!”

是啊,这时候,mybatis的 where 标签就该隆重登场啦:

 

2. where

咱们通过where改造一下上面的例子:

<select id="findUserById" resultType="user">

           select * from user

           <where>

               <if test="id != null">

                   id=#{id}

               </if>

               and deleteFlag=0;

           </where>

 </select>

有些人就要问了: “你这都是些什么玩意儿! 跟上面的相比, 不就是多了个where标签嘛! 那这个还会不会出现  select * from user where and deleteFlag=0 ?”

的确,从表面上来看,就是多了个where标签而已, 不过实质上, mybatis是对它做了处理,当它遇到AND或者OR这些,它知道怎么处理。其实我们可以通过 trim 标签去自定义这种处理规则。

 

3. trim

上面的where标签,其实用trim 可以表示如下:

<trim prefix="WHERE" prefixOverrides="AND |OR ">

  ...

</trim>

它的意思就是: 当WHERE后紧随AND或则OR的时候,就去除AND或者OR。 除了WHERE以外, 其实还有一个比较经典的实现,那就是SET。

 

4. set

<update id="updateUser" parameterType="com.dy.entity.User">

           update user set

           <if test="name != null">

               name = #{name},

           </if>

           <if test="password != null">

               password = #{password},

           </if>

           <if test="age != null">

               age = #{age}

           </if>

           <where>

               <if test="id != null">

                   id = #{id}

               </if>

               and deleteFlag = 0;

           </where>

</update>

问题又来了: “如果我只有name不为null,  那么这SQL不就成了 update set name = #{name}, where ........ ?  你那name后面那逗号会导致出错啊!”

是的,这时候,就可以用mybatis为我们提供的set 标签了。下面是通过set标签改造后:

<update id="updateUser" parameterType="com.dy.entity.User">

           update user

        <set>

          <if test="name != null">name = #{name},</if>

             <if test="password != null">password = #{password},</if>

             <if test="age != null">age = #{age},</if>

        </set>

           <where>

               <if test="id != null">

                   id = #{id}

               </if>

               and deleteFlag = 0;

           </where>

</update>

这个用trim 可表示为:

<trim prefix="SET" suffixOverrides=",">

  ...

</trim>

WHERE是使用的 prefixOverrides(前缀), SET是使用的 suffixOverrides (后缀), 看明白了吧!

 

5. foreach

java中有for, 可通过for循环, 同样, mybatis中有foreach, 可通过它实现循环,循环的对象当然主要是java容器和数组。

<select id="selectPostIn" resultType="domain.blog.Post">

  SELECT *

  FROM POST P

  WHERE ID in

  <foreach item="item" index="index" collection="list"

      open="(" separator="," close=")">

        #{item}

  </foreach>

</select>

将一个 List 实例或者数组作为参数对象传给 MyBatis,当这么做的时候,MyBatis 会自动将它包装在一个 Map 中并以名称为键。List 实例将会以“list”作为键,而数组实例的键将是“array”。同样, 当循环的对象为map的时候,index其实就是map的key。

 

6. choose

Java中有switch,  mybatis有choose。

<select id="findActiveBlogLike"

     resultType="Blog">

  SELECT * FROM BLOG WHERE state = ‘ACTIVE’

  <choose>

    <when test="title != null">

      AND title like #{title}

    </when>

    <when test="author != null and author.name != null">

      AND author_name like #{author.name}

    </when>

    <otherwise>

      AND featured = 1

    </otherwise>

  </choose>

</select>

以上例子中: 当title和author都不为null的时候, 那么选择二选一(前者优先), 如果都为null, 那么就选择 otherwise中的, 如果tilte和author只有一个不为null, 那么就选择不为null的那个。

纵观mybatis的动态SQL, 强大而简单, 相信大家简单看一下就能使用了。

好啦,本次就写到这!下篇文章将结合mybatis的源码分析一次sql语句执行的整个过程。

 

End

Note_JavaWeb_MyBatis3