用Java代码实现多表联查 jpa实现多表联查

转载

lemon 2023-09-01 11:51:17

文章标签 用Java代码实现多表联查 jpa vue ajax bootstrap 文章分类 Java 后端开发

基于 JPA 显示城市信息

一、JPA

JPA 定义

二、任务

实战

1.项目结构
2.项目配置信息
3.实体
4.映射
5.控制器
6.UI

三、效果图

一、JPA

JPA 定义

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

@Entity
@Table
@Id
@Column
@OneToOne
@OneToMany
@ManyToOne

二、任务

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

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

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

用Java代码实现多表联查 jpa实现多表联查_ajax

实战

1.项目结构

用Java代码实现多表联查 jpa实现多表联查_jpa_02

2.项目配置信息

用Java代码实现多表联查 jpa实现多表联查_vue_03

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>