Mybatis学习日志二

一、动态sql

  1、动态SQL:if 语句

   

<select id="selectUserByUsernameAndSex" resultType="user" parameterType="com.ys.po.User">
    select * from user where
        <if test="username != null">
           username=#{username}
        </if>
         
        <if test="username != null">
           and sex=#{sex}
        </if>
</select>

 

  2、动态SQL:if+where 语句

<select id="selectUserByUsernameAndSex" resultType="user" parameterType="com.ys.po.User">
    select * from user
    <where>
        <if test="username != null">
           username=#{username}
        </if>
         
        <if test="username != null">
           and sex=#{sex}
        </if>
    </where>
</select>

 

  3、动态SQL:if+set 语句

<!-- 根据 id 更新 user 表的数据 -->
<update id="updateUserById" parameterType="com.ys.po.User">
    update user u
        <set>
            <if test="username != null and username != ''">
                u.username = #{username},
            </if>
            <if test="sex != null and sex != ''">
                u.sex = #{sex}
            </if>
        </set>
     
     where id=#{id}
</update>

 

  4、动态SQL:choose(when,otherwise) 语句

<select id="selectUserByChoose" resultType="com.ys.po.User" parameterType="com.ys.po.User">
      select * from user
      <where>
          <choose>
              <when test="id !='' and id != null">
                  id=#{id}
              </when>
              <when test="username !='' and username != null">
                  and username=#{username}
              </when>
              <otherwise>
                  and sex=#{sex}
              </otherwise>
          </choose>
      </where>
  </select>

 

  5、动态SQL:trim 语句

<select id="selectUserByUsernameAndSex" resultType="user" parameterType="com.ys.po.User">
        select * from user
        <!-- <where>
            <if test="username != null">
               username=#{username}
            </if>
             
            <if test="username != null">
               and sex=#{sex}
            </if>
        </where>  -->
        <trim prefix="where" prefixOverrides="and | or">
            <if test="username != null">
               and username=#{username}
            </if>
            <if test="sex != null">
               and sex=#{sex}
            </if>
        </trim>
    </select>

 

  6、动态SQL: SQL 片段

  <!-- 定义sql片段 -->
    <sql id="usersColum">id,name,age,sex</sql>
    <select id="getUser" parameterType="int" resultType="Users">
          select 
          <!-- 引用sql片段 -->
          <include refid="usersColum"></include>
          from users where id = #{id}   
    </select>

 

  7、动态SQL: foreach 语句

  <delete id="delete">
        delete from users 
        <where>
         <!--
             collection:指定输入对象中的集合属性
             item:每次遍历生成的对象
             open:开始遍历时的拼接字符串
                 close:结束时拼接的字符串
                 separator:遍历对象之间需要拼接的字符串
                 select * from user where 1=1 and id in (1,2,3)
             -->
            <foreach collection="ids" open=" id in (" close=")" separator="," item="id">
                #{id}
            </foreach>
        </where> 
    </delete>

  8.动态sql与模糊查询

  <select id="getUser" parameterType="int" resultType="Users">
          select 
          <!-- 引用sql片段 -->
          <include refid="usersColum"></include>
          from users where id like concat('%',#{id},'%')    
    </select>

 二、逆向工程

  通过前面的学习,在实际开发中,我们基本上能对mybatis应用自如了,但是我们发现了一个问题,所有操作都是围绕着po类,xxxMapper.xml文件,xxxMapper接口等文件来进行的。如果实际开发中数据库的表特别多,那么我们需要手动去写每一张表的po类,xxxMapper.xml,xxxMapper.java文件,这显然需要花费巨大的精力,而且可能由于表字段太多,写错了而不知道也是可能的。

  所以我们在实际开发中,一般使用逆向工程方式来自动生成所需的文件。

  1.新建一个工程并导入相应的jar包

  

  2.创建配置文件generator.xml

<?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>
  <!-- mysql驱动jar所在的位置 -->
  <classPathEntry location="E:\\NJDF\\mysql-connector-java-5.1.47.jar\\" />
    
  <!-- 数据源的信息 -->    
  <context id="DB2Tables" targetRuntime="MyBatis3">
      <!-- 禁止所有注释 -->
    <commentGenerator>
      <property name="suppressAllComments" value="true" />
    </commentGenerator>
    <jdbcConnection driverClass="com.mysql.jdbc.Driver"
        connectionURL="jdbc:mysql://localhost:3306/mybatis"
        userId="root"
        password="123456">
    </jdbcConnection>

    <javaTypeResolver >
      <property name="forceBigDecimals" value="false" />
    </javaTypeResolver>
    
    <!-- 生产的实体类所在的位置 -->
    <javaModelGenerator targetPackage="com.zhiyou100.klb.bean" targetProject="./src">
      <property name="enableSubPackages" value="true" />
      <property name="trimStrings" value="true" />
    </javaModelGenerator>

    <!-- 生成的映射文件所在的位置 -->
    <sqlMapGenerator targetPackage="com.zhiyou100.klb.mapper"  targetProject="./src">
      <property name="enableSubPackages" value="true" />
    </sqlMapGenerator>
    
    <!-- 生产的dao文件所在的位置 -->
    <javaClientGenerator type="XMLMAPPER" targetPackage="com.zhiyou100.klb.dao"  targetProject="./src">
      <property name="enableSubPackages" value="true" />
    </javaClientGenerator>

    <!-- 某张表与实体类的对象关系
         schema:该表所在的数据库
         tableName:表名
         domainOb:实体类名
     -->
    <table schema="mybatis" tableName="users" domainObjectName="Users" enableCountByExample="false"
    enableDeleteByExample="false" enableSelectByExample="false" enableUpdateByExample="false">
      <property name="useActualColumnNames" value="true"/>
      <generatedKey column="ID" sqlStatement="DB2" identity="true" />
      <columnOverride column="DATE_FIELD" property="startDate" />
      <ignoreColumn column="FRED" />
      <columnOverride column="LONG_VARCHAR_FIELD" jdbcType="VARCHAR" />
    </table>

  </context>
</generatorConfiguration>

  3.运行主程序生成代码

package com.zhiyou100.klb.test;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

import org.mybatis.generator.api.MyBatisGenerator;
import org.mybatis.generator.config.Configuration;
import org.mybatis.generator.config.xml.ConfigurationParser;
import org.mybatis.generator.internal.DefaultShellCallback;

public class Test {
    public static void main(String[] args) throws Exception{
        List<String> warnings = new ArrayList<String>();
        boolean overwrite = true;
        File configFile = new File("generator.xml");
        ConfigurationParser cp = new ConfigurationParser(warnings);
        Configuration config = cp.parseConfiguration(configFile);
        DefaultShellCallback callback = new DefaultShellCallback(overwrite);
        MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
        myBatisGenerator.generate(null);
    }
}

 三、分页查询助手pagehelper

   1.引入jar包

  

  2.在MyBatis配置xml中配置拦截器插件

<!-- 
    plugins在配置文件中的位置必须符合要求,否则会报错,顺序如下:
    properties?, settings?, 
    typeAliases?, typeHandlers?, 
    objectFactory?,objectWrapperFactory?, 
    plugins?, 
    environments?, databaseIdProvider?, mappers?
-->
<plugins>
    <!-- com.github.pagehelper为PageHelper类所在包名 -->
    <plugin interceptor="com.github.pagehelper.PageInterceptor">
        <!-- 使用下面的方式配置参数,后面会有所有的参数介绍 -->
        <property name="param1" value="value1"/>
    </plugin>
</plugins>

  3.如何在代码中使用,使用pageInfo的用法

//获取第1页,10条内容,默认查询总数count
PageHelper.startPage(1, 10);
List<Country> list = countryMapper.selectAll();
//用PageInfo对结果进行包装
PageInfo page = new PageInfo(list);
//测试PageInfo全部属性
//PageInfo包含了非常全面的分页属性
assertEquals(1, page.getPageNum());
assertEquals(10, page.getPageSize());
assertEquals(1, page.getStartRow());
assertEquals(10, page.getEndRow());
assertEquals(183, page.getTotal());
assertEquals(19, page.getPages());
assertEquals(1, page.getFirstPage());
assertEquals(8, page.getLastPage());
assertEquals(true, page.isFirstPage());
assertEquals(false, page.isLastPage());
assertEquals(false, page.isHasPreviousPage());
assertEquals(true, page.isHasNextPage());

 

posted on 2019-08-30 21:47  乱数  阅读(134)  评论(0编辑  收藏  举报