java多表联合查询，使用spring-data-jpa实现简单的两表联查

2023-09-25 阅读 32 评论 0

摘要：关于Spring-data-jpa的配置+Springboot+Spring-data-jpa的简单操作简单操作+分布式服务设置一个永远不重复的ID 初学Spring套餐家族中的Spring-data-jpa发现可以更简单的实现一些基本的增删改查，以下是一些基础操作，希望可以给初学者一些帮助 sprin

关于Spring-data-jpa的配置+Springboot+Spring-data-jpa的简单操作简单操作+分布式服务设置一个永远不重复的ID

初学Spring套餐家族中的Spring-data-jpa发现可以更简单的实现一些基本的增删改查，以下是一些基础操作，希望可以给初学者一些帮助

spring家族

Spring Boot
spring Cloud
Spring framework
Spring-dataSpring data-jpa(简单的增删改查)

java多表联合查询、jpa配置

jpa的底层是基于hibernate
多表联查的时候会用到一对多,多对一的关系等springboot一旦是一个web程序,记得配置数据源

spring:datasource:driver-class-name: com.mysql.cj.jdbc.Driverusername: rootpassword: rooturl: jdbc:mysql://localhost:3306/studentdb?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&zeroDateTimeBehavior=CONVERT_TO_NULLjpa:database: mysqlhibernate:ddl-auto: updatenaming:physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImplshow-sql: trueprofiles:active: local

实体类快速建表

@Data
@Entity
public class Student {@Idprivate Long stuid;private String name;private String sex;@ManyToOneprivate Grade grade;
}

Id列的注解
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
/*Id表示主键  主键有生成策略*/
/*GenerationType.IDENTITY唯一列还自动增长*/
/*GenerationType.AUTO自动增长*/
/*Oracle中是没有自动增长的 设置GenerationType.SEQUENCE  使用序列进行增长*/
/*GeneratedValue 自动增长生成的value值*/
普通列注解
@Column

启动后自动建表成功
在这里插入图片描述
Dao(数据访问层)接口

public interface StudentMapper extends JpaRepository<Student,Long>, JpaSpecificationExecutor<Student> {
}

jpa自定义复杂查询、ServiceImpl类

@Service
public class StudentServiceImpl {@Autowiredprivate StudentMapper studentMapper;public Page<Student> students(Integer pageNum, Integer size){if(pageNum==null||pageNum<0){pageNum=0;}if (size==null){size=2;}return studentMapper.findAll(PageRequest.of(pageNum,size));}public void del(Long id){try {studentMapper.deleteById(id);}catch (Exception e){e.printStackTrace();}}public Student getByid(Long id){Optional<Student> byId = studentMapper.findById(id);return byId.get();}public Student add(Student student){return studentMapper.save(student);}public Student upd(Student student){return studentMapper.save(student);}
}

因为id我没有给自增所以要手动给id并且不能重复
先创建工具类

public class IdWorker {// 时间起始标记点，作为基准，一般取系统的最近时间（一旦确定不能变动）private final static long twepoch = 1288834974657L;// 机器标识位数private final static long workerIdBits = 5L;// 数据中心标识位数private final static long datacenterIdBits = 5L;// 机器ID最大值private final static long maxWorkerId = -1L ^ (-1L << workerIdBits);// 数据中心ID最大值private final static long maxDatacenterId = -1L ^ (-1L << datacenterIdBits);// 毫秒内自增位private final static long sequenceBits = 12L;// 机器ID偏左移12位private final static long workerIdShift = sequenceBits;// 数据中心ID左移17位private final static long datacenterIdShift = sequenceBits + workerIdBits;// 时间毫秒左移22位private final static long timestampLeftShift = sequenceBits + workerIdBits + datacenterIdBits;private final static long sequenceMask = -1L ^ (-1L << sequenceBits);/* 上次生产id时间戳 */private static long lastTimestamp = -1L;// 0，并发控制private long sequence = 0L;private final long workerId;// 数据标识id部分private final long datacenterId;public IdWorker(){this.datacenterId = getDatacenterId(maxDatacenterId);this.workerId = getMaxWorkerId(datacenterId, maxWorkerId);}/*** @param workerId*            工作机器ID* @param datacenterId*            序列号*/public IdWorker(long workerId, long datacenterId) {if (workerId > maxWorkerId || workerId < 0) {throw new IllegalArgumentException(String.format("worker Id can't be greater than %d or less than 0", maxWorkerId));}if (datacenterId > maxDatacenterId || datacenterId < 0) {throw new IllegalArgumentException(String.format("datacenter Id can't be greater than %d or less than 0", maxDatacenterId));}this.workerId = workerId;this.datacenterId = datacenterId;}/*** 获取下一个ID** @return*/public synchronized long nextId() {long timestamp = timeGen();if (timestamp < lastTimestamp) {throw new RuntimeException(String.format("Clock moved backwards.  Refusing to generate id for %d milliseconds", lastTimestamp - timestamp));}if (lastTimestamp == timestamp) {// 当前毫秒内，则+1sequence = (sequence + 1) & sequenceMask;if (sequence == 0) {// 当前毫秒内计数满了，则等待下一秒timestamp = tilNextMillis(lastTimestamp);}} else {sequence = 0L;}lastTimestamp = timestamp;// ID偏移组合生成最终的ID，并返回IDlong nextId = ((timestamp - twepoch) << timestampLeftShift)| (datacenterId << datacenterIdShift)| (workerId << workerIdShift) | sequence;return nextId;}private long tilNextMillis(final long lastTimestamp) {long timestamp = this.timeGen();while (timestamp <= lastTimestamp) {timestamp = this.timeGen();}return timestamp;}private long timeGen() {return System.currentTimeMillis();}/*** <p>* 获取 maxWorkerId* </p>*/protected static long getMaxWorkerId(long datacenterId, long maxWorkerId) {StringBuffer mpid = new StringBuffer();mpid.append(datacenterId);String name = ManagementFactory.getRuntimeMXBean().getName();if (!name.isEmpty()) {/** GET jvmPid*/mpid.append(name.split("@")[0]);}/** MAC + PID 的 hashcode 获取16个低位*/return (mpid.toString().hashCode() & 0xffff) % (maxWorkerId + 1);}/*** <p>* 数据标识id部分* </p>*/protected static long getDatacenterId(long maxDatacenterId) {long id = 0L;try {InetAddress ip = InetAddress.getLocalHost();NetworkInterface network = NetworkInterface.getByInetAddress(ip);if (network == null) {id = 1L;} else {byte[] mac = network.getHardwareAddress();id = ((0x000000FF & (long) mac[mac.length - 1])| (0x0000FF00 & (((long) mac[mac.length - 2]) << 8))) >> 6;id = id % (maxDatacenterId + 1);}} catch (Exception e) {System.out.println(" getDatacenterId: " + e.getMessage());}return id;}

注入

@SpringBootApplication
public class SpringDataJpa01Application {public static void main(String[] args) {SpringApplication.run(SpringDataJpa01Application.class, args);}@Beanpublic IdWorker sb(){return new IdWorker();}
}

JPA mybatis。调用新增方法是执行
在这里插入图片描述
Controller(控制层)类

@Controller
public class StudentController {@Autowiredprivate StudentServiceImpl studentService;@Autowiredprivate GradeMapper gradeMapper;@Autowiredprivate IdWorker idWorker;@GetMapping("/students")public String findAll(Integer pageNum, Integer size, Model model) {Page<Student> students = studentService.students(pageNum, size);model.addAttribute("students", students);return "index";}@DeleteMapping("/student/{id}")public String del(@PathVariable("id") Long id) {studentService.del(id);return "redirect:/students";}@GetMapping("/student")public String edit(Long id, Model model) {model.addAttribute("grades", gradeMapper.findAll());if (id != null) {model.addAttribute("students", studentService.getByid(id));}return "edit";}@PostMapping("/student")public String add(Student student) {student.setStuid(idWorker.nextId());studentService.add(student);return "redirect:/students";}@PutMapping("/student")public String upd(Student student) {studentService.upd(student);return "redirect:/students";}
}

index.html页面首页

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head><meta charset="UTF-8"><title>Title</title>
</head>
<body>
<table border="1"><a th:href="@{/student}">添加</a><form action="">name：<input type="text"><input type="submit" value="搜索"></form><tr><td>#</td><td>姓名</td><td>性别</td><td>年级</td><td>操作</td></tr><tr th:each="stu : ${students}"><td th:text="${stu.stuid}">#</td><td th:text="${stu.name}">姓名</td><td th:text="${stu.sex}">性别</td><td th:text="${stu.grade.name}">年级</td><td><button class="del" th:attr="del_uri=@{/student/}+${stu.stuid}">删除</button><a th:href="@{/student(id=${stu.stuid})}">修改</a></td></tr>
</table>
<form id="DelFoem" action="" method="post"><input type="hidden" name="_method" value="delete">
</form>
<a th:href="@{/students(pageNum=1)}">首页</a>
<a th:href="@{/students(pageNum=${students.number}-1)}">上一页</a>
<a th:if="${students.hasNext()}" th:href="@{/students(pageNum=${students.number}+1)}">下一页</a>
<a th:if="${!students.hasNext()}" th:href="@{/students(pageNum=${students.totalPages}-1)}">下一页</a>
<a th:href="@{/students(pageNum=${students.totalPages}-1)}">尾页</a>
共[[${students.totalPages}]]页，当前第[[${students.number}+1]]
</body>
<script type="text/javascript" th:src="@{/js/jquery-1.12.4.js}"></script>
<script type="text/javascript">$(".del").click(function () {$("#DelFoem").attr("action", $(this).attr("del_uri")).submit();})
</script>
</html>

添加修改页面

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head><meta charset="UTF-8"><title>Title</title>
</head>
<body>
<table><form th:action="@{/student}" method="post"><input type="hidden" name="_method" value="put" th:if="${students!=null}"><input type="hidden" name="stuid" th:value="${students?.stuid}" th:if="${students!=null}"><tr><td>name:<input type="text" name="name" th:value="${students?.name}"></td></tr><tr><td>sex:<input type="text" name="sex" th:value="${students?.sex}"></td></tr><tr><td>grade:<select name="grade.gradeId"><option th:each="grade : ${grades}"th:value="${grade.gradeId}"th:text="${grade.name}"th:selected="${students!=null&&students?.grade.gradeId==grade.gradeId}"></option></select></td></tr><tr><td><input type="submit" th:value="${students==null?'添加':'修改'}"></td></tr></form>
</table>
</body>
</html>