mybatis之联表查询

首页 > 代码库 > mybatis之联表查询

2024-09-03 17:09:43 217人阅读

今天碰到了一个问题，就是要在三张表里面各取一部分数据然后组成一个list传到前台页面显示。但是并不想在后台做太多判断，（因为涉及到for循环）会拉慢运行速度。正好用的框架是spring+springMVC+mybatis，所以很自然的就想到了联表查询。

一开始认为mybatis编写语句很简单，但是在编写的时候遇到了一些细节问题，所以发文记录一下。

先说一下背景：

框架：spring+springMVC+mybatis

表结构：

1、主表

技术分享

2、从表

技术分享

从表的uid对应主表的id，并将主表的id设为主键

接下来开始解析代码

先用mybatis的generator自动生成dao、mapper、model层

model层如下：

User.java

package am.model;

import java.util.List;

public class User {
    private Integer id;

    private String username;

    private String pwd;
    
    private List<Student> students;

    public Integer getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username == null ? null : username.trim();
    }

    public String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd == null ? null : pwd.trim();
    }

	public List<Student> getStudents() {
		return students;
	}

	public void setStudents(List<Student> students) {
		this.students = students;
	}
    
    
}

Student.java

package am.model;

public class Student {
    private Integer id;

    private String position;

    private String level;

    private Integer uid;

    public Integer getId() {
        return id;
    }

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

    public String getPosition() {
        return position;
    }

    public void setPosition(String position) {
        this.position = position == null ? null : position.trim();
    }

    public String getLevel() {
        return level;
    }

    public void setLevel(String level) {
        this.level = level == null ? null : level.trim();
    }

    public Integer getUid() {
        return uid;
    }

    public void setUid(Integer uid) {
        this.uid = uid;
    }
}

需要注意的是在User表里面添加了成员变量

private List<Student> students;

这是因为联表查询时Student表为从表，你最终要将得到的数据放在主表User里

mapping层

UserMapper.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="am.dao.UserMapper" >
  <resultMap id="BaseResultMap" type="am.model.User" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="username" property="username" jdbcType="VARCHAR" />
    <result column="pwd" property="pwd" jdbcType="VARCHAR" />
  </resultMap>
  
  <resultMap id="studentForListMap" type="am.model.User" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="username" property="username" jdbcType="VARCHAR" />
    <result column="pwd" property="pwd" jdbcType="VARCHAR" />
    <collection property="students" javaType="java.util.List" ofType="am.model.Student">  
            <result column="id" property="id" jdbcType="INTEGER" />
		    <result column="position" property="position" jdbcType="VARCHAR" />
		    <result column="level" property="level" jdbcType="VARCHAR" />  
    </collection> 
  </resultMap>
  
  <select id="studentForList" resultMap="studentForListMap" >
    select u.id,u.username,u.pwd,s.position,s.level from user u left join student s on u.id = s.uid
  </select>
  
  <sql id="Base_Column_List" >
    id, username, pwd
  </sql>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
    select 
    <include refid="Base_Column_List" />
    from user
    where id = #{id,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
    delete from user
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="am.model.User" >
    insert into user (id, username, pwd
      )
    values (#{id,jdbcType=INTEGER}, #{username,jdbcType=VARCHAR}, #{pwd,jdbcType=VARCHAR}
      )
  </insert>
  <insert id="insertSelective" parameterType="am.model.User" >
    insert into user
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>
      <if test="username != null" >
        username,
      </if>
      <if test="pwd != null" >
        pwd,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=INTEGER},
      </if>
      <if test="username != null" >
        #{username,jdbcType=VARCHAR},
      </if>
      <if test="pwd != null" >
        #{pwd,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="am.model.User" >
    update user
    <set >
      <if test="username != null" >
        username = #{username,jdbcType=VARCHAR},
      </if>
      <if test="pwd != null" >
        pwd = #{pwd,jdbcType=VARCHAR},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="am.model.User" >
    update user
    set username = #{username,jdbcType=VARCHAR},
      pwd = #{pwd,jdbcType=VARCHAR}
    where id = #{id,jdbcType=INTEGER}
  </update>
</mapper>

需要注意

1、property="students"中的students必须与User.java中的private List<Student> students;中的students命名一样

2、resultMap="studentForListMap"必须与<resultMap id="studentForListMap" type="am.model.User" >中的id一致

<resultMap id="studentForListMap" type="am.model.User" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="username" property="username" jdbcType="VARCHAR" />
    <result column="pwd" property="pwd" jdbcType="VARCHAR" />
    <collection property="students" javaType="java.util.List" ofType="am.model.Student">  
            <result column="id" property="id" jdbcType="INTEGER" />
		    <result column="position" property="position" jdbcType="VARCHAR" />
		    <result column="level" property="level" jdbcType="VARCHAR" />  
    </collection> 
  </resultMap>
  
  <select id="studentForList" resultMap="studentForListMap" >
    select u.id,u.username,u.pwd,s.position,s.level from user u left join student s on u.id = s.uid
  </select>

dao层

UserMapper.java

package am.dao;

import java.util.List;

import am.model.User;

public interface UserMapper {
    int deleteByPrimaryKey(Integer id);

    int insert(User record);

    int insertSelective(User record);

    User selectByPrimaryKey(Integer id);

    int updateByPrimaryKeySelective(User record);

    int updateByPrimaryKey(User record);
    
    List<User> queryForList();
    
    List<User> studentForList();
}

spring-mybatis.xml

<?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:context="http://www.springframework.org/schema/context"
	xmlns:mvc="http://www.springframework.org/schema/mvc"
	xsi:schemaLocation="http://www.springframework.org/schema/beans  
                        http://www.springframework.org/schema/beans/spring-beans-3.1.xsd  
                        http://www.springframework.org/schema/context  
                        http://www.springframework.org/schema/context/spring-context-3.1.xsd  
                        http://www.springframework.org/schema/mvc  
                        http://www.springframework.org/schema/mvc/spring-mvc-4.0.xsd">
	<!-- 自动扫描 -->
	<context:component-scan base-package="am.*" />
	<!-- 引入配置文件 -->
	<bean id="propertyConfigurer"
		class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
		<property name="location" value="http://www.mamicode.com/classpath:jdbc.properties" />
	</bean>

	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
		destroy-method="close">
		<property name="driverClassName" value="http://www.mamicode.com/${driver}" />
		<property name="url" value="http://www.mamicode.com/${url}" />
		<property name="username" value="http://www.mamicode.com/${username}" />
		<property name="password" value="http://www.mamicode.com/${password}" />
		<!-- 初始化连接大小 -->
		<property name="initialSize" value="http://www.mamicode.com/${initialSize}"></property>
		<!-- 连接池最大数量 -->
		<property name="maxActive" value="http://www.mamicode.com/${maxActive}"></property>
		<!-- 连接池最大空闲 -->
		<property name="maxIdle" value="http://www.mamicode.com/${maxIdle}"></property>
		<!-- 连接池最小空闲 -->
		<property name="minIdle" value="http://www.mamicode.com/${minIdle}"></property>
		<!-- 获取连接最大等待时间 -->
		<property name="maxWait" value="http://www.mamicode.com/${maxWait}"></property>
	</bean>

	<!-- spring和MyBatis完美整合，不需要mybatis的配置映射文件 -->
	<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<!-- 自动扫描mapping.xml文件 -->
		<property name="mapperLocations" value="http://www.mamicode.com/classpath:am/mapping/*.xml"></property>
	</bean>

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

</beans>

我用serviceImp层来继承service层实现dao层，当然你也可以直接实现dao层，在这里就不多做赘述了。

分享一下项目包结构：

技术分享

最后做一点简单的测试

MybatisController

package am.controller;

import java.util.List;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import com.alibaba.fastjson.JSON;

import am.model.User;
import am.service.UserService;


@Controller  
@RequestMapping("/my")
public class MybatisController {
	
	@Resource
	private UserService userService;
	
	@RequestMapping("/t")  
	 public ModelAndView test(HttpServletRequest request){  
		 ModelAndView view = new ModelAndView("test");
		 List<User> users = userService.studentForList();
		 String s = JSON.toJSONString(users);
		 System.out.println(JSON.toJSONString(users));
		 return view;
	 }
}

success，得到想要数据。

mybatis之联表查询

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > mybatis之联表查询

mybatis之联表查询

看完仍有疑问？有类似问题直接问程序猿