Spring mybatis 多表查询

栏目: Java · 发布时间: 6年前

内容简介：复杂的多表要自己写，默认的可以自动生成5172

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


以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。

码农书籍
Neural Networks for Applied Sciences and Engineering

Neural Networks for Applied Sciences and Engineering

Samarasinghe, Sandhya / CRC Pr I Llc / 2006-9 / $ 118.59

In response to the exponentially increasing need to analyze vast amounts of data, Neural Networks for Applied Sciences and Engineering: From Fundamentals to Complex Pattern Recognition provides scient......一起来看看 《Neural Networks for Applied Sciences and Engineering》 这本书的介绍吧!

码农工具
SHA 加密
SHA 加密

SHA 加密工具

Markdown 在线编辑器
Markdown 在线编辑器

Markdown 在线编辑器

UNIX 时间戳转换
UNIX 时间戳转换

UNIX 时间戳转换

  • New
  • 文章
  • 话题
  • 教程
    • 关注 码农网 公众号
    版权所有，保留一切权利！© 2018-2025 码农网 粤ICP备17054400号-3