[toc]
数据库连表
通过两张表相互关联,(某个相同的字段)实现两个数据库相互联系,通过连表查询语句可以同时访问两个表的数据.
需求示例
两张表;user表(有name,Email等属性),VerificationCode表(有Email,Verification等属性,)
现在想要实现两个接口功能,
1:通过Email同时查询name与Verification的值并返回;
2:通过name同时查询Email与Verification的值并返回
1通过Email同时查询name与Verification的值并返回
在UserRepository中，我们使用@Query注解来定义自定义查询语句，实现了通过Email同时查询name与Verification的功能：
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
@Query("SELECT u.name, v.verification FROM User u JOIN VerificationCode v ON u.email = v.email WHERE u.email = :email")
List<Object[]> findUserInfoByEmail(@Param("email") String email);
User findByEmail(String email);
}
实现接口功能:
@RestController
public class UserController {
private final UserRepository userRepository;
@Autowired
public UserController(UserRepository userRepository) {
this.userRepository = userRepository;
}
@GetMapping("/user-info-by-email")
public ResponseEntity<Map<String, String>> getUserInfoByEmail(@RequestParam String email) {
List<Object[]> result = userRepository.findUserInfoByEmail(email);
if (!result.isEmpty()) {
Object[] data = result.get(0);
Map<String, String> resultMap = new HashMap<>();
resultMap.put("name", (String) data[0]);
resultMap.put("verification", (String) data[1]);
return ResponseEntity.ok(resultMap);
} else {
return ResponseEntity.notFound().build();
}
}
}
2 通过name同时查询Email与Verification的值并返回
在VerificationCodeRepository中，我们同样使用@Query注解定义自定义查询语句，实现了通过name同时查询Email与Verification的功能：
@Repository
public interface VerificationCodeRepository extends JpaRepository<VerificationCode, Long> {
@Query("SELECT u.email, v.verification FROM User u JOIN VerificationCode v ON u.email = v.email WHERE u.name = :name")
List<Object[]> findVerificationInfoByName(@Param("name") String name);
VerificationCode findByEmail(String email);
}
功能实现:
@RestController
public class VerificationCodeController {
private final VerificationCodeRepository verificationCodeRepository;
@Autowired
public VerificationCodeController(VerificationCodeRepository verificationCodeRepository) {
this.verificationCodeRepository = verificationCodeRepository;
}
@GetMapping("/verification-info-by-name")
public ResponseEntity<Map<String, String>> getVerificationInfoByName(@RequestParam String name) {
List<Object[]> result = verificationCodeRepository.findVerificationInfoByName(name);
if (!result.isEmpty()) {
Object[] data = result.get(0);
Map<String, String> resultMap = new HashMap<>();
resultMap.put("email", (String) data[0]);
resultMap.put("verification", (String) data[1]);
return ResponseEntity.ok(resultMap);
} else {
return ResponseEntity.notFound().build();
}
}
}
简单的讲解了连表查询,主要是查询语句的定义与数据返回的设置
本文由mdnice多平台发布
