Spring mybatis 多表查询

1. mybatis 下载

mybatis-generator

2. sql写在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.decent.nvda.mapper.VideoCardMapper">
  <resultMap id="BaseResultMap" type="com.decent.nvda.bean.VideoCard">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="video_card_direct_id" jdbcType="INTEGER" property="videoCardDirectId" />
    <result column="video_card_name" jdbcType="VARCHAR" property="videoCardName" />
    <result column="video_card_percent" jdbcType="REAL" property="videoCardPercent" />
    <result column="video_card_type" jdbcType="INTEGER" property="videoCardType" />
    <result column="video_card_finial_percent" jdbcType="REAL" property="videoCardFinialPercent" />
  </resultMap>
  <sql id="Base_Column_List">
    id, video_card_direct_id, video_card_name, video_card_percent, video_card_type, video_card_finial_percent
  </sql>
  <resultMap id="AllInfoResultMap" type="com.decent.nvda.bean.AllVideoCard">
    <result column="count_date" jdbcType="DATE" property="countDate" />
    <result column="video_card_type" jdbcType="INTEGER" property="videoCardType" />
    <result column="s_video_card_finial_percent" jdbcType="REAL" property="s_videoCardFinialPercent" />
  </resultMap>
  <select id="selectByTimeLimit" resultMap="AllInfoResultMap">
    SELECT vcd.count_date,vc.video_card_type,sum(vc.video_card_finial_percent) as s_video_card_finial_percent
    from video_card as vc
    join video_card_direct as vcd on vc.video_card_direct_id = vcd.id
    <if test='startDate != null and endDate != null'>
      where
      count_date >= #{startDate}
      and count_date <= #{endDate}
    </if>
    group by vcd.count_date,vc.video_card_type
  </select>

  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from video_card
    where id = #{id,jdbcType=INTEGER}
  </select>

  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from video_card
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.decent.nvda.bean.VideoCard">
    insert into video_card (id, video_card_direct_id, video_card_name,
    video_card_percent, video_card_type, video_card_finial_percent
    )
    values (#{id,jdbcType=INTEGER}, #{videoCardDirectId,jdbcType=INTEGER}, #{videoCardName,jdbcType=VARCHAR},
    #{videoCardPercent,jdbcType=REAL}, #{videoCardType,jdbcType=INTEGER}, #{videoCardFinialPercent,jdbcType=REAL}
    )
  </insert>
  <insert id="insertSelective" parameterType="com.decent.nvda.bean.VideoCard">
    insert into video_card
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="id != null">
        id,
      </if>
      <if test="videoCardDirectId != null">
        video_card_direct_id,
      </if>
      <if test="videoCardName != null">
        video_card_name,
      </if>
      <if test="videoCardPercent != null">
        video_card_percent,
      </if>
      <if test="videoCardType != null">
        video_card_type,
      </if>
      <if test="videoCardFinialPercent != null">
        video_card_finial_percent,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="id != null">
        #{id,jdbcType=INTEGER},
      </if>
      <if test="videoCardDirectId != null">
        #{videoCardDirectId,jdbcType=INTEGER},
      </if>
      <if test="videoCardName != null">
        #{videoCardName,jdbcType=VARCHAR},
      </if>
      <if test="videoCardPercent != null">
        #{videoCardPercent,jdbcType=REAL},
      </if>
      <if test="videoCardType != null">
        #{videoCardType,jdbcType=INTEGER},
      </if>
      <if test="videoCardFinialPercent != null">
        #{videoCardFinialPercent,jdbcType=REAL},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.decent.nvda.bean.VideoCard">
    update video_card
    <set>
      <if test="videoCardDirectId != null">
        video_card_direct_id = #{videoCardDirectId,jdbcType=INTEGER},
      </if>
      <if test="videoCardName != null">
        video_card_name = #{videoCardName,jdbcType=VARCHAR},
      </if>
      <if test="videoCardPercent != null">
        video_card_percent = #{videoCardPercent,jdbcType=REAL},
      </if>
      <if test="videoCardType != null">
        video_card_type = #{videoCardType,jdbcType=INTEGER},
      </if>
      <if test="videoCardFinialPercent != null">
        video_card_finial_percent = #{videoCardFinialPercent,jdbcType=REAL},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.decent.nvda.bean.VideoCard">
    update video_card
    set video_card_direct_id = #{videoCardDirectId,jdbcType=INTEGER},
    video_card_name = #{videoCardName,jdbcType=VARCHAR},
    video_card_percent = #{videoCardPercent,jdbcType=REAL},
    video_card_type = #{videoCardType,jdbcType=INTEGER},
    video_card_finial_percent = #{videoCardFinialPercent,jdbcType=REAL}
    where id = #{id,jdbcType=INTEGER}
  </update>
</mapper>

复杂的多表要自己写，默认的可以自动生成

3.map java

package com.decent.nvda.mapper;

import com.decent.nvda.bean.AllVideoCard;
import com.decent.nvda.bean.VideoCard;
import org.apache.ibatis.annotations.Param;

import java.util.Date;
import java.util.List;

public interface VideoCardMapper {
    int deleteByPrimaryKey(Integer id);

    int insert(VideoCard record);

    int insertSelective(VideoCard record);

    VideoCard selectByPrimaryKey(Integer id);

    List<AllVideoCard> selectByTimeLimit(@Param("startDate") Date startDate, @Param("endDate") Date endDate);

    int updateByPrimaryKeySelective(VideoCard record);

    int updateByPrimaryKey(VideoCard record);
}

4. bean

package com.decent.nvda.bean;

public class VideoCard {
    private Integer id;

    private Integer videoCardDirectId;

    private String videoCardName;

    private Float videoCardPercent;

    private Integer videoCardType;

    private Float videoCardFinialPercent;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getVideoCardDirectId() {
        return videoCardDirectId;
    }

    public void setVideoCardDirectId(Integer videoCardDirectId) {
        this.videoCardDirectId = videoCardDirectId;
    }

    public String getVideoCardName() {
        return videoCardName;
    }

    public void setVideoCardName(String videoCardName) {
        this.videoCardName = videoCardName == null ? null : videoCardName.trim();
    }

    public Float getVideoCardPercent() {
        return videoCardPercent;
    }

    public void setVideoCardPercent(Float videoCardPercent) {
        this.videoCardPercent = videoCardPercent;
    }

    public Integer getVideoCardType() {
        return videoCardType;
    }

    public void setVideoCardType(Integer videoCardType) {
        this.videoCardType = videoCardType;
    }

    public Float getVideoCardFinialPercent() {
        return videoCardFinialPercent;
    }

    public void setVideoCardFinialPercent(Float videoCardFinialPercent) {
        this.videoCardFinialPercent = videoCardFinialPercent;
    }
}

5. 使用

package com.decent.nvda.task;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.annotation.Resource;

import com.decent.nvda.bean.AllVideoCard;
import com.decent.nvda.bean.VideoCard;
import com.decent.nvda.mapper.VideoCardMapper;
import org.junit.Test;

import com.alibaba.fastjson.JSON;
import com.yiqiniu.common.utils.DateUtils;

public class TaskTest extends SimpleTest{

	@Resource
	private VideoCardMapper videoCardMapper;

	@Test
	public void test() throws ParseException {
//		VideoCard videoCard = videoCardMapper.selectByPrimaryKey(43216);
		String stringStart = "2016-10-24 21:59:06";
		String stringEnd = "2018-12-07 21:59:06";
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		List<AllVideoCard> allVideoCards = videoCardMapper.selectByTimeLimit(sdf.parse(stringStart),sdf.parse(stringEnd));
        System.out.println("test");
	}
	
}

6. porm依赖

<!-- mysql -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <!-- druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
        </dependency>

        <!-- mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
        </dependency>

5172