SpringBoot+Mybatis注解方式实现关联查询

￡神魔★判官ぃ 2022-01-05 09:49 411阅读 0赞

User实体类

package per.czt.pojo;
    
    import org.springframework.boot.autoconfigure.domain.EntityScan;
    
    
    
    public class User {
    	private Integer user_id;
    
    	private Town town;
    
    	private String user_account;
    	private String user_password;
    	public Integer getUser_id() {
    		return user_id;
    	}
    
    	public void setUser_id(Integer user_id) {
    		this.user_id = user_id;
    	}
    
    	
    	public Town getTown() {
    		return town;
    	}
    
    	public void setTown(Town town) {
    		this.town = town;
    	}
    
    
    
    	public String getUser_account() {
    		return user_account;
    	}
    
    	public void setUser_account(String user_account) {
    		this.user_account = user_account;
    	}
    
    	public String getUser_password() {
    		return user_password;
    	}
    
    	public void setUser_password(String user_password) {
    		this.user_password = user_password;
    	}
    
    	
    	public User() {
    		super();
    		// TODO Auto-generated constructor stub
    	}
    	
    	
    	
    
    }

Town实体类

package per.czt.pojo;
    
    public class Town {
    	private Integer town_id;
    	private String town_name;
    	/*private Integer city_id;*/
    	private City city;
    	public Integer getTown_id() {
    		return town_id;
    	}
    	public void setTown_id(Integer town_id) {
    		this.town_id = town_id;
    	}
    	public String getTown_name() {
    		return town_name;
    	}
    	public void setTown_name(String town_name) {
    		this.town_name = town_name;
    	}
    	
    	public City getCity() {
    		return city;
    	}
    	public void setCity(City city) {
    		this.city = city;
    	}
    	public Town() {
    		super();
    		// TODO Auto-generated constructor stub
    	}
    	public Town(Integer town_id, String town_name, City city) {
    		super();
    		this.town_id = town_id;
    		this.town_name = town_name;
    		this.city = city;
    	}
    	
    }

City实体类

package per.czt.pojo;
    
    public class City {
    	private Integer city_id;
    	private String city_name;
    	/*private Integer country_id;*/
    	private Country country;
    	public City() {
    		super();
    		// TODO Auto-generated constructor stub
    	}
    	public Integer getCity_id() {
    		return city_id;
    	}
    	public void setCity_id(Integer city_id) {
    		this.city_id = city_id;
    	}
    	public String getCity_name() {
    		return city_name;
    	}
    	public void setCity_name(String city_name) {
    		this.city_name = city_name;
    	}
    	public Country getCountry() {
    		return country;
    	}
    	public void setCountry(Country country) {
    		this.country = country;
    	}
    	public City(Integer city_id, String city_name, Country country) {
    		super();
    		this.city_id = city_id;
    		this.city_name = city_name;
    		this.country = country;
    	}
    	
    }

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="per.czt.mapper.UserMapper">
    </mapper>

UserMapper接口

package per.czt.mapper;
    
    import java.util.List;
    
    import org.apache.ibatis.annotations.One;
    import org.apache.ibatis.annotations.Result;
    import org.apache.ibatis.annotations.Results;
    import org.apache.ibatis.annotations.Select;
    
    import per.czt.pojo.User;
    
    public interface UserMapper {
    	@Select("select * from user")
    	@Results({@Result(property="town",column="town_id",one=@One(select="per.czt.mapper.TownMapper.findTownById"))})
    	public List<User> searchAllUsers();
    
    }

TownMapper.xml文件

TownMapper接口

package per.czt.mapper;
    
    import org.apache.ibatis.annotations.One;
    import org.apache.ibatis.annotations.Result;
    import org.apache.ibatis.annotations.Results;
    import org.apache.ibatis.annotations.Select;
    
    import per.czt.pojo.Town;
    
    public interface TownMapper {
    	@Select("select * from town where town_id=#{town_id}")
    	@Results({@Result(column="city_id",property="city",one=@One(select="per.czt.mapper.CityMapper.findCityById"))})
    	public Town findTownById(Integer town_id);
    
    }

CityMapper.xml文件

CityMapper接口

package per.czt.mapper;
    
    import org.apache.ibatis.annotations.One;
    import org.apache.ibatis.annotations.Result;
    import org.apache.ibatis.annotations.Results;
    import org.apache.ibatis.annotations.Select;
    
    import per.czt.pojo.City;
    
    public interface CityMapper {
    	@Select("select * from city where city_id=#{city_id}")
    	public City findCityById(Integer city_id);
    }

UserService接口

package per.czt.service;
    
    import java.util.List;
    
    import per.czt.pojo.User;
    
    public interface UserService {
    	public List<User> searchAllUsers();
    
    }

UserServiceImpl 实现类

package per.czt.service.impl;
    
    import java.util.List;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import org.springframework.transaction.annotation.Transactional;
    
    import per.czt.mapper.UserMapper;
    import per.czt.pojo.User;
    import per.czt.service.UserService;
    @Service
    @Transactional
    public class UserServiceImpl implements UserService {
    	@Autowired
    	private UserMapper userMapper;
    
    	@Override
    	public List<User> searchAllUsers() {
    		return userMapper.searchAllUsers();
    	}
    
    }

UserController

package per.czt.controller;
    
    import java.util.List;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Controller;
    import org.springframework.ui.Model;
    import org.springframework.web.bind.annotation.PathVariable;
    import org.springframework.web.bind.annotation.RequestMapping;
    
    import per.czt.pojo.User;
    import per.czt.service.UserService;
    
    @Controller
    @RequestMapping("/user")
    public class UserController {
    	@Autowired
    	private UserService userService;
    	
    	
    	@RequestMapping("/{page}")
    	public String showPage(@PathVariable String page) {
    		return page;
    	}
    	@RequestMapping("/showAll")
    	public String showAllUsers(Model model) {
    		
    		
    		List<User> userList=userService.searchAllUsers();
    		model.addAttribute("userList",userList);
    		System.out.println("userList:"+userList);
    		for(User u:userList) {
    			System.out.println("user_id:"+u.getUser_id());
    			System.out.println("user_account:"+u.getUser_account());
    			System.out.println("user_password:"+u.getUser_password());
    			System.out.println("user_profession:"+u.getProfession());
    			System.out.println("user_town:"+u.getTown());
    			System.out.println("user_town_id:"+u.getTown().getTown_id());
    			System.out.println("user_town_name:"+u.getTown().getTown_name());
    			System.out.println("user_city:"+u.getTown().getCity());
    			System.out.println("user_city_id:"+u.getTown().getCity().getCity_id());
    			System.out.println("user_city_name:"+u.getTown().getCity().getCity_name())；
    			System.out.println();
    		}
    		return "userlist";
    	}
    	
    	
    
    }

view层:userList.html，这里使用了thymeleaf模板

<!DOCTYPE html >
    <html xmlns:th="http://www.thymeleaf.org">
    <head>
    <meta charset="UTF-8"/>
    <title>显示所有用户</title>
    </head>
    <body>
    	<table width="50%" border="1" align="center">
    		<tr>
    			<th>用户ID</th>
    			<th>用户账号</th>
    			<th>用户密码</th>
    			<th>乡镇名称</th>
    			<th>城市名称</th>
    		</tr>
    		<tr th:each="user:${userList}">
    			<td th:text="${user.user_id}"></td>
    			<td th:text="${user.user_account}"></td>
    			<td th:text="${user.user_password}"></td>
    			<td th:text="${user.town.town_name}"></td>
    			<td th:text="${user.town.city.city_name}"></td>
    		</tr>
    	
    	</table>
    </body>
    </html>

SpringBoot启动类

package per.czt;
    
    import org.mybatis.spring.annotation.MapperScan;
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    
    /**
     * Hello world!
     *
     */
    @SpringBootApplication
    @MapperScan("per.czt.mapper")
    public class App 
    {
        public static void main( String[] args )
        {
            SpringApplication.run(App.class, args);
        }
    }

运行结果  
![在这里插入图片描述][20190623181613533.png]

[20190623181613533.png]: /images/20211229/710ad54eee4745caa4b7ed656df44b0f.png