MyBatis3（二）

第五章 动态 SQL

第一节：if 条件
第二节：choose，when和 otherwise条件
第三节：where条件

1，自动加上 where；（有的企业喜欢加where 1=1 and, 这样算法和性能不是很好）
2，如果 where子句以 and或者 or 开头，则自动删除第一个 and或者 or；
第四节：trim 条件

功能和 where 元素类似，提供了前缀，后缀功能，更加灵活；
第五节：foreach循环
第六节：set 条件

1，自动加上 set；
2，自动剔除最后一个逗号“，”；

<?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"/>
    </resultMap>
    
    <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">     <!--  去掉第一个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> 

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

 

第一节：处理 CLOB、BLOB 类型数据
第二节：传入多个输入参数

    <resultMap type="Student" id="StudentResult">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
    </resultMap>
    
    <insert id="insertStudent" parameterType="Student">
        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>
    
    <!-- 传入多个输入参数,以后都是用map -->
    <select id="searchStudents6" resultMap="StudentResult">
        select * from t_student where name like #{param1} and age=#{param2}
    </select>

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



========================================


public class Student {

    private Integer id;
    private String name;
    private Integer age;
    private byte[] pic;
    private String remark;
    

}

CREATE TABLE `t_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `pic` longblob,
  `remark` longtext,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

第三节：Mybatis 分页
1，逻辑分页；（原理是把所有数据查出来放到内存中再截取处理，正式项目不适用）
2，物理分页；

第四节：Mybatis 缓存

（高并发的查询，可以减轻数据库的压力，提高性能，但是内存要大）Mybatis 默认情况下，MyBatis 启用一级缓存，即同一个 SqlSession 接口对象调用了相同的 select 语句，则直接会从缓存中返回结果，而不是再查询一次数据库；
开发者可以自己配置二级缓存，二级缓存是全局的；（上面相当于针对某一用户，二级缓存相当于所有用户，实际开发要搞的就是二级。）
默认情况下，select 使用缓存的useCache="true" flushCache="false"，insert update delete 是不使用缓存的flushCache="true"；

<!--
        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>

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

 

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

第一节：基本映射语句
1，@Insert
2，@Update
3，@Delete
4，@Select

第二节：结果集映射语句

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

第三节：关系映射
1，一对一映射；one=@One()
2，一对多映射；many=@Many()  （还不成熟，代码有缺陷，有时候会莫名其妙的报错）

@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);


============================================

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

}

第四节：动态 SQL
@InsertProvider
@UpdateProvider
@DeleteProvider
@SelectProvider

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

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

 

第八章 Mybatis 与Spring，SpringMvc整合

把sqlSessionFactory交给spring管理，需要配置一下。

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

<?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" />
    
    <!-- 配置数据源 -->
    <bean id="dataSource"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/db_mybatis"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
    </bean>

    <!-- 配置mybatis的sqlSessionFactory -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <!-- 自动扫描mappers.xml文件 -->
        <property name="mapperLocations" value="classpath:com/java1234/mappers/*.xml"></property>
        <!-- mybatis配置文件 -->
        <property name="configLocation" value="classpath:mybatis-config.xml"></property>
    </bean>

    <!-- DAO接口所在包名，Spring会自动查找其下的类 -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.java1234.dao" />
        <property name="sqlSessionFactoryBeanName" value="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>

<?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="/" />
        <property name="suffix" value=".jsp"></property>
    </bean>

</beans>  

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

@Controller
@RequestMapping("/user")
public class UserController {

    @Resource
    private UserService userService;
    
    @RequestMapping("/login")
    public String login(User user,HttpServletRequest request){
        User resultUser=userService.login(user);
        if(resultUser==null){
            request.setAttribute("user", user);
            request.setAttribute("errorMsg", "用户名或密码错误！");
            return "index";
        }else{
            HttpSession session=request.getSession();
            session.setAttribute("currentUser", resultUser);
            return "redirect:/success.jsp";
        }
    }
}


============================

@Service("userService")
public class UserServiceImpl implements UserService{

    @Resource
    private UserDao userDao;
    
    @Override
    public User login(User user) {
        return userDao.login(user);
    }

}