@Entity
@Table(name = "sys_user")
public class User extends BaseEntity implements Serializable {
    @Id
    @Column(name = "user_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @ApiModelProperty(value = "ID", hidden = true)
    private Long id;
    // 關(guān)聯(lián)角色
    @ManyToMany(fetch = FetchType.EAGER)
    @ApiModelProperty(value = "用戶角色")
    @JoinTable(name = "sys_users_roles",
            joinColumns = {@JoinColumn(name = "user_id", referencedColumnName = "user_id")},
            inverseJoinColumns = {@JoinColumn(name = "role_id", referencedColumnName = "role_id")})
    private Set<Role> roles;
    @ManyToMany(fetch = FetchType.EAGER)
    @ApiModelProperty(value = "用戶崗位")
    @JoinTable(name = "sys_users_jobs",
            joinColumns = {@JoinColumn(name = "user_id", referencedColumnName = "user_id")},
            inverseJoinColumns = {@JoinColumn(name = "job_id", referencedColumnName = "job_id")})
    private Set<Job> jobs;
}

public interface UserRepository extends JpaRepository<User, Long>, JpaSpecificationExecutor<User> {
    /**
     * 根據(jù)用戶名查找用戶
     */
    @Query("select u from User u left join fetch u.roles where u.username = :username")
    User findByUsername(@Param("username") String username);
    /**
     * 根據(jù)郵箱查找用戶
     */
    User findByEmail(String email);
    /**
     * 模糊查詢用戶
     */
    @Query("select u from User u where u.username like %:blurry% or u.nickName like %:blurry% or u.email like %:blurry%")
    List<User> findByBlurry(@Param("blurry") String blurry);
}

public class QueryHelp {
    public static <R, Q> PageResult<R> getPageResult(Page<Q> page, JPAQueryFactory jpaQueryFactory,
                                                     QConverter<R, Q> converter) {
        List<R> content = page.getContent().stream()
                .map(converter::convertToDto)
                .collect(Collectors.toList());
        return new PageResult<>(content, page.getTotalElements(), page.getNumber() + 1, page.getSize());
    }
}

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD, ElementType.METHOD})
public @interface DataPermission {
    // 數(shù)據(jù)權(quán)限注解實(shí)現(xiàn)
}

// 在需要關(guān)聯(lián)查詢的地方使用fetch join
@Query("select u from User u left join fetch u.roles left join fetch u.jobs where u.id = :id")
User findById(@Param("id") Long id);

@Service
@Transactional
public class UserServiceImpl implements UserService {
    @Override
    @Transactional(rollbackFor = Exception.class)
    public User create(User resources) {
        // 業(yè)務(wù)邏輯
        return userRepository.save(resources);
    }
    @Override
    @Transactional(readOnly = true)
    public User findById(Long id) {
        return userRepository.findById(id).orElse(null);
    }
}

public interface UserService {
    PageResult<UserDto> queryAll(UserQueryCriteria criteria, Pageable pageable);
}
@Service
public class UserServiceImpl implements UserService {
    @Override
    public PageResult<UserDto> queryAll(UserQueryCriteria criteria, Pageable pageable) {
        Page<User> page = userRepository.findAll((root, query, criteriaBuilder) -> QueryHelp.getPredicate(root, criteria, criteriaBuilder), pageable);
        return new PageResult<>(page.getContent().stream().map(userMapper::toDto).collect(Collectors.toList()), page.getTotalElements(), page.getNumber() + 1, page.getSize());
    }
}

// 使用@Query注解進(jìn)行復(fù)雜關(guān)聯(lián)查詢
@Query("SELECT new me.zhengjie.modules.system.service.dto.UserDto(u.id, u.username, u.nickName, u.email, u.phone, u.gender, u.avatarName, u.avatarPath, u.enabled, u.isAdmin, u.dept, u.jobs, u.roles, u.createTime) FROM User u LEFT JOIN u.dept d LEFT JOIN u.jobs j LEFT JOIN u.roles r WHERE (:deptIds IS NULL OR d.id IN :deptIds) AND (:jobIds IS NULL OR j.id IN :jobIds)")
List<UserDto> findUsersWithConditions(@Param("deptIds") List<Long> deptIds, @Param("jobIds") List<Long> jobIds);

# application.yml中的JPA配置
spring:
  jpa:
    hibernate:
      ddl-auto: validate # 生產(chǎn)環(huán)境使用validate，開發(fā)環(huán)境可以使用update
    show-sql: true
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQL8Dialect
        format_sql: true

try {
    userRepository.save(user);
} catch (DataIntegrityViolationException e) {
    throw new BadRequestException("數(shù)據(jù)完整性違反：" + e.getMessage());
} catch (OptimisticLockingFailureException e) {
    throw new BadRequestException("樂觀鎖異常，請重試");
}

@SpringBootTest
@Transactional
@TestPropertySource(locations = "classpath:application-test.properties")
class UserRepositoryTest {
    @Autowired
    private UserRepository userRepository;
    @Test
    void testFindByUsername() {
        User user = new User();
        user.setUsername("testuser");
        user.setPassword("password");
        userRepository.save(user);
        User found = userRepository.findByUsername("testuser");
        assertThat(found).isNotNull();
        assertThat(found.getUsername()).isEqualTo("testuser");
    }
}