Java案例怎么实现关联查询？

本文目录导读：

1. 纯JDBC方式
2. MyBatis框架
3. Spring Data JPA
4. MyBatis-Plus
5. QueryDSL（高级查询）
6. DTO映射方式（推荐）
7. 实践建议

在Java中实现关联查询（多表查询）主要有几种方式，取决于你使用的技术栈，以下是常见的几种实现方案：

纯JDBC方式

直接编写SQL进行多表关联查询：

public class UserOrderDao {
    public List<UserOrderVO> getUserOrders() {
        String sql = "SELECT u.id, u.name, o.order_id, o.amount " +
                     "FROM users u " +
                     "LEFT JOIN orders o ON u.id = o.user_id";
        List<UserOrderVO> result = new ArrayList<>();
        try (Connection conn = DriverManager.getConnection(url, username, password);
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                UserOrderVO vo = new UserOrderVO();
                vo.setUserId(rs.getInt("id"));
                vo.setUserName(rs.getString("name"));
                vo.setOrderId(rs.getString("order_id"));
                vo.setAmount(rs.getBigDecimal("amount"));
                result.add(vo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }
}

MyBatis框架

XML配置方式：

<!-- UserMapper.xml -->
<mapper namespace="com.example.mapper.UserMapper">
    <!-- 一对多关联查询 -->
    <resultMap id="userOrderMap" type="User">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <collection property="orders" ofType="Order">
            <id property="orderId" column="order_id"/>
            <result property="amount" column="amount"/>
        </collection>
    </resultMap>
    <select id="getUserOrders" resultMap="userOrderMap">
        SELECT u.id, u.name, o.order_id, o.amount
        FROM users u
        LEFT JOIN orders o ON u.id = o.user_id
    </select>
</mapper>

注解方式：

@Mapper
public interface UserMapper {
    @Select("SELECT u.*, o.* FROM users u LEFT JOIN orders o ON u.id = o.user_id")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "name", column = "name"),
        @Result(property = "orders", column = "id",
                many = @Many(select = "com.example.mapper.OrderMapper.findByUserId"))
    })
    List<User> getUsersWithOrders();
}

Spring Data JPA

实体关联注解：

@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    @OneToMany(mappedBy = "user", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private List<Order> orders;
}
@Entity
@Table(name = "orders")
public class Order {
    @Id
    private String orderId;
    private BigDecimal amount;
    @ManyToOne
    @JoinColumn(name = "user_id")
    private User user;
}

Repository查询：

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    // 使用JPQL进行关联查询
    @Query("SELECT u FROM User u JOIN FETCH u.orders WHERE u.id = :userId")
    User findUserWithOrders(@Param("userId") Long userId);
    // 使用原生SQL
    @Query(value = "SELECT u.*, o.* FROM users u LEFT JOIN orders o ON u.id = o.user_id", 
           nativeQuery = true)
    List<Object[]> findUserOrdersNative();
}

MyBatis-Plus

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> {
    public List<UserVO> getUserOrders() {
        return baseMapper.selectUserOrders();
    }
}
// Mapper接口
@Mapper
public interface UserMapper extends BaseMapper<User> {
    // 使用注解SQL
    @Select("SELECT u.*, o.order_id, o.amount FROM users u " +
            "LEFT JOIN orders o ON u.id = o.user_id")
    List<UserVO> selectUserOrders();
}

QueryDSL（高级查询）

@Repository
public class UserRepositoryImpl {
    @PersistenceContext
    private EntityManager em;
    public List<User> findUsersWithOrders() {
        JPAQueryFactory queryFactory = new JPAQueryFactory(em);
        QUser user = QUser.user;
        QOrder order = QOrder.order;
        return queryFactory
            .selectFrom(user)
            .leftJoin(user.orders, order)
            .fetchJoin()
            .fetch();
    }
}

DTO映射方式（推荐）

// DTO类
public class UserOrderDTO {
    private Long userId;
    private String userName;
    private String orderId;
    private BigDecimal amount;
}
// 使用MapStruct进行对象映射
@Mapper(componentModel = "spring")
public interface UserOrderMapper {
    UserOrderDTO toDTO(User user, Order order);
}
// 服务层实现
@Service
public class UserService {
    @Autowired
    private UserRepository userRepository;
    @Autowired
    private UserOrderMapper mapper;
    public List<UserOrderDTO> getUserOrders() {
        // 使用JOIN FETCH避免N+1问题
        List<User> users = userRepository.findAllWithOrders();
        List<UserOrderDTO> result = new ArrayList<>();
        for (User user : users) {
            for (Order order : user.getOrders()) {
                result.add(mapper.toDTO(user, order));
            }
        }
        return result;
    }
}

实践建议

1. 性能优化：

   • 使用JOIN FETCH避免N+1问题
   • 合理使用懒加载和急加载
   • 考虑使用分页查询

2. 代码规范：

   • 创建专门的DTO/VO类接收关联查询结果
   • 避免在实体类中直接返回关联查询结果

3. 事务管理：

   • 关联查询通常在同一个事务中完成
   • 使用@Transactional保证数据一致性

选择哪种方式主要取决于项目使用的ORM框架、性能要求以及团队规范，对于新项目，推荐使用Spring Data JPA或MyBatis-Plus。