Spring mybatis 多表查询

Spring mybatis 多表查询

mybatis - 略sql写在xml里

id, video_card_direct_id, video_card_name, video_card_percent, video_card_type, video_card_finial_percent 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 where count_date >= #{startDate} and count_date <= #{endDate} group by vcd.count_date,vc.video_card_type select from video_card where id = #{id,jdbcType=INTEGER} delete from video_card where id = #{id,jdbcType=INTEGER} 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 into video_card id, video_card_direct_id, video_card_name, video_card_percent, video_card_type, video_card_finial_percent, #{id,jdbcType=INTEGER}, #{videoCardDirectId,jdbcType=INTEGER}, #{videoCardName,jdbcType=VARCHAR}, #{videoCardPercent,jdbcType=REAL}, #{videoCardType,jdbcType=INTEGER}, #{videoCardFinialPercent,jdbcType=REAL}, update video_card 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 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}

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

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 selectByTimeLimit(@Param("startDate") Date startDate, @Param("endDate") Date endDate); int updateByPrimaryKeySelective(VideoCard record); int updateByPrimaryKey(VideoCard record);}

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

使用

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 allVideoCards = videoCardMapper.selectByTimeLimit(sdf.parse(stringStart),sdf.parse(stringEnd)); System.out.println("test"); } }

porm依赖

mysql mysql-connector-java com.alibaba druid org.mybatis mybatis org.mybatis mybatis-spring

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。