基于JPA实现多表连接查询

一、JPA

JPA 定义

• Java 持久化 API
• Java 官方定义的 ORM 规范，只是一套规范，并没有提供底层的实现（基于抽象工厂设计模式，将定义与实现解耦），Hibernate 、TopLink 都是 JPA 的提供商，它们实现了 JPA 规范
• JPA 基于注解的方式实现了 实体类 到 关系表 之间的映射
  • @Entity
  • @Table
  • @Id
  • @Column
  • @OneToOne
  • @OneToMany
  • @ManyToOne

JPA 官方参考文档

二、任务

使用 MySQL 提供的实例数据库 world，基于 JPA 设计两个实体 City 和 Country 映射数据库中已存在的表，实现多表连接查询，在 UI 界面分页显示每个城市的信息：

• 编号
• 城市名称
• 城市人口
• 所在国家
• 所属洲

要点：根据 city 表中的 countrycode 字段 与 country 表中的 code 字段进行匹配，实现多表查询

实战

1.项目结构

2.项目配置信息

3.实体

• City

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.OneToOne;
import javax.persistence.Table;

@Entity
@Table(name = "city")
public class City {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	Long id;
	
	String name;
	
	Long population;
	
	@ManyToOne//(targetEntity = Country.class)
	@JoinColumn(name="countrycode",referencedColumnName = "code")
	Country country;
	
	public City() {
	}

	public Long getId() {
		return id;
	}

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

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public Long getPopulation() {
		return population;
	}

	public void setPopulation(Long population) {
		this.population = population;
	}

	public Country getCountry() {
		return country;
	}

	public void setCountry(Country country) {
		this.country = country;
	}

	@Override
	public String toString() {
		return "City [id=" + id + ", name=" + name + ", population=" + population + ", country=" + country + "]";
	}
	
}

• Country

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "country")
public class Country {

	@Id
	String code;
	
	String name;
	
	String continent;
	
	public Country() {
	}

	public String getCode() {
		return code;
	}

	public void setCode(String code) {
		this.code = code;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getContinent() {
		return continent;
	}

	public void setContinent(String continent) {
		this.continent = continent;
	}

	@Override
	public String toString() {
		return "Country [code=" + code + ", name=" + name + ", continent=" + continent + "]";
	}
	
}

4.映射

• CityRepository
  无需自定义业务逻辑，直接继承接口即可，该接口支持分页和排序

import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface CityRepository extends PagingAndSortingRepository<City, Long>{
	
}

• CountryRepository

import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;
@Repository
public interface CountryRepository extends PagingAndSortingRepository<Country, String>{

}

5.控制器

• HomeController

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;

@Controller
public class HomeController {
	// 返回视图
	@GetMapping("/")
	public String home() {
		return "index.html";
	}
}

• CityController

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/api/city")
public class CityController {

	@Autowired
	CityRepository cityRepository;
	
	/**
	 * GET '/api/city'
	 * 
	 * @param page 当前页码
	 * @param size 每页的记录数默认设定为10，一页加载10条
	 * @return
	 */
	@GetMapping
	public Page<City> findCity(
			@RequestParam(name = "p",defaultValue = "0") int page,
			@RequestParam(name = "n",defaultValue = "10") int size){
		
		// 分页规则
		Pageable pageable = PageRequest.of(page, size);
		
		// 可按城市人口数量进行降序排序
		pageable = PageRequest.of(page, size,Sort.by("population").descending());
		
		return cityRepository.findAll(pageable);
	}

}

6.UI

基于 Vue、AJAX、Bootstrap

<!doctype html>
<html lang="en">

<head>
    <title>City</title>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">

    <!-- Vue js-->
    <script src="js/vue.js"></script>
    <!-- AJAX js-->
    <script src="js/axios.min.js"></script>
</head>


<body>

    <!-- View UI-->
    <div id="app">

        <!-- 展板 -->
        <div class="jumbotron jumbotron-fluid py-1 pl-5 mb-0">
            <div class="container">
                <h1 class="display-3">Cities Of The World</h1>
                <p class="lead">基于 <span class="badge badge-pill badge-danger">JPA</span> 分页显示</p>
                <p class="lead">作者：某某某</p>
            </div>
        </div>

        <!-- 容器 -->
        <div class="container-fluid">
            <!-- 表格 -->
            <table class="table table-striped table-bordered mt-2">
                <!-- 表头 -->
                <thead class="table-dark">
                    <tr class="text-center">
                        <th style="width: 3em;">
                            <input type="checkbox">
                        </th>
                        <th style="width: 8em;">编号</th>
                        <th style="width: 20em;">城市名称</th>
                        <th style="width: 10em;">城市人口 [↓]</th>
                        <th>所在国家</th>
                        <th style="width: 18em;">所属洲</th>
                    </tr>
                </thead>
                <!-- 表体 -->
                <tbody>
                    <!-- v-for 声明式渲染 城市数据 -->
                    <tr v-for="(city, index) in cityList" :key="index">
                        <td class="text-center">
                            <input type="checkbox">
                        </td>
                        <td class="text-center">{{city.id}}</td>
                        <td>{{city.name}}</td>
                        <td>{{city.population}}</td>
                        <td>{{city.country.name}}</td>
                        <td>{{city.country.continent}}</td>
                    </tr>
                </tbody>
            </table>
        </div>

        <!-- 分页导航 -->
        <nav aria-label="Page navigation">
            <ul class="pagination justify-content-center">

                <!-- 上一页 -->
                <li class="page-item" @click="prevPage()" :class="{'disabled':isFirst}">
                    <a class="page-link" href="#" aria-label="Previous">
                        <span aria-hidden="true">上一页</span>
                        <span class="sr-only">Previous</span>
                    </a>
                </li>

                <!-- v-for 声明式渲染 页码 -->
                <li class="page-item" :class="{active : (n-1)===currentPage}" @click="page(n-1)" v-for="n in totalPages" v-if="showPage(n)" :key="n">
                    <a class="page-link" href="#">{{showPage(n)}}</a>
                </li>

                <!-- 下一页 -->
                <li class="page-item" @click="nextPage()" :class="{'disabled':isLast}">
                    <a class="page-link" href="#" aria-label="Next">
                        <span aria-hidden="true">下一页</span>
                        <span class="sr-only">Next</span>
                    </a>
                </li>

                <!-- 页码快速定位 -->
                <li>
                    <!-- 输入页码按回车键即可跳转 或者 点击跳转按钮-->
                    转到 <input @keyup.enter="goPage()" style="width: 3em;" type="text" v-model="goPageIndex"> 页
                    <button class="btn btn-outline-warning " @click="goPage()">跳转</button>
                </li>
            </ul>
        </nav>

    </div>

    <!-- JS -->
    <Script>
        // 创建一个 Vue 实例
        let v = new Vue({
            // 绑定
            el: '#app',
            // 数据
            data: {
                // 城市列表
                cityList: [],
                // 总页码
                totalPages: 0,
                // 当前页码
                currentPage: 0,
                // 是否第一页
                isFirst: '',
                // 是否最后一页
                isLast: '',
                // 输入的页码
                goPageIndex: ''
            },
            // 方法
            methods: {
                // 加载数据
                loadCityList: function() {
                    // GET '/api/city'
                    const url = '/api/city';
                    axios.get(url)
                        .then(res => {
                            console.log('加载城市列表数据', res.data);
                            // 城市列表
                            this.cityList = res.data.content;
                            // 当前页
                            this.currentPage = res.data.number;
                            // 总页数
                            this.totalPages = res.data.totalPages;
                            // 状态
                            this.isFirst = res.data.first;
                            this.isLast = res.data.last;
                        })
                        .catch(err => {
                            console.error(err);
                        })
                },
                // 点击页码进行跳转
                page: function(n) {
                    // GET '/api/city?p=123'
                    const url = `/api/city?p=${n}`;
                    axios.get(url)
                        .then(res => {
                            console.log('换页', n + 1);
                            this.cityList = res.data.content;
                            this.totalPages = res.data.totalPages;
                            this.currentPage = res.data.number;
                        })
                        .catch(err => {
                            console.error(err);
                        })
                },
                // 上一页
                prevPage: function() {
                    if (this.currentPage > 0) {
                        this.currentPage -= 1;
                        this.page(this.currentPage);
                    } else {
                        return;
                    }
                },
                // 下一页
                nextPage: function() {
                    if (this.currentPage < this.totalPages - 1) {
                        this.currentPage += 1;
                        this.page(this.currentPage);
                    } else {
                        return;
                    }
                },
                // 输入页码快速定位
                goPage: function() {
                    let goPageIndex = parseInt(this.goPageIndex);
                    if (goPageIndex > 0 && goPageIndex <= this.totalPages) {
                        this.page(this.goPageIndex - 1);
                        this.goPageIndex = '';
                    }
                },
                // 页码过多时显示省略号
                showPage(n) {
                    // 前两个和最后两个始终显示
                    if (n < 3 || (n > this.totalPages - 2)) {
                        return n;
                    }
                    // 当前页的前一页和后一页始终显示 
                    else if (n <= this.currentPage + 2 && n >= this.currentPage) {
                        return n;
                    }
                    // 当前页的前前页和后后页显示 ... 
                    else if (n === this.currentPage + 3 || n === this.currentPage - 1) {
                        return '...';
                    }
                    // 其余的不显示 
                    else {
                        return false;
                    }
                }
            },
            // 回调函数
            // 创建页面时自动调用 加载数据
            created() {
                this.loadCityList();
            },
        })
    </Script>

    <!-- Optional JavaScript -->
    <!-- jQuery first, then Popper.js, then Bootstrap JS -->
    <script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
</body>

</html>

三、效果图

每页10条记录，切分成408页，按照城市人口进行降序排序