Java子查询案例怎么改写优化

本文目录导读：

1. 案例1：使用子查询查找最大值的记录
2. 案例2：使用IN子查询
3. 案例3：复杂的嵌套子查询
4. 案例4：使用Java代码处理
5. 案例5：使用Stream API优化
6. 性能优化建议

我来通过几个典型案例,演示如何优化Java中的子查询写法。

案例1：使用子查询查找最大值的记录

原始写法（低效）

// 查询每个部门工资最高的员工
@Repository
public class EmployeeRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    public List<Employee> getTopPaidEmployees() {
        String sql = """
            SELECT e.* FROM employees e 
            WHERE e.salary = (
                SELECT MAX(salary) FROM employees 
                WHERE department_id = e.department_id
            )
            """;
        return jdbcTemplate.query(sql, employeeRowMapper);
    }
}

优化方案1：使用JOIN代替子查询

public List<Employee> getTopPaidEmployeesOptimized() {
    String sql = """
        SELECT e.* FROM employees e
        INNER JOIN (
            SELECT department_id, MAX(salary) as max_salary
            FROM employees
            GROUP BY department_id
        ) dept_max ON e.department_id = dept_max.department_id 
                   AND e.salary = dept_max.max_salary
        """;
    return jdbcTemplate.query(sql, employeeRowMapper);
}

优化方案2：使用窗口函数

public List<Employee> getTopPaidEmployeesWindow() {
    String sql = """
        SELECT department_id, employee_id, name, salary
        FROM (
            SELECT *,
                   RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rnk
            FROM employees
        ) ranked
        WHERE rnk = 1
        """;
    return jdbcTemplate.query(sql, employeeRowMapper);
}

案例2：使用IN子查询

原始写法

// 查询有订单的用户
public List<User> getUsersWithOrders() {
    String sql = """
        SELECT * FROM users 
        WHERE user_id IN (
            SELECT DISTINCT user_id FROM orders WHERE amount > 100
        )
        """;
    return jdbcTemplate.query(sql, userRowMapper);
}

优化方案：使用EXISTS

public List<User> getUsersWithOrdersOptimized() {
    String sql = """
        SELECT u.* FROM users u
        WHERE EXISTS (
            SELECT 1 FROM orders o 
            WHERE o.user_id = u.user_id AND o.amount > 100
        )
        """;
    return jdbcTemplate.query(sql, userRowMapper);
}

案例3：复杂的嵌套子查询

原始写法

// 查询每个分类中销售额最高的商品
public List<Product> getTopSellingProducts() {
    String sql = """
        SELECT p.* FROM products p
        WHERE p.product_id IN (
            SELECT product_id FROM (
                SELECT product_id,
                       ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales DESC) as rn
                FROM sales
            ) tmp WHERE rn = 1
        )
        """;
    return jdbcTemplate.query(sql, productRowMapper);
}

优化方案：使用JOIN + 派生表

public List<Product> getTopSellingProductsOptimized() {
    String sql = """
        SELECT p.*, s.sales
        FROM products p
        INNER JOIN (
            SELECT product_id, category_id, sales,
                   ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales DESC) as rn
            FROM sales
        ) s ON p.product_id = s.product_id AND s.rn = 1
        """;
    return jdbcTemplate.query(sql, productRowMapper);
}

案例4：使用Java代码处理

原始SQL子查询方式

// 查询某个时间段内活跃用户的信息
public List<User> getActiveUsers(Date startDate, Date endDate) {
    String sql = """
        SELECT * FROM users 
        WHERE user_id IN (
            SELECT user_id FROM user_actions 
            WHERE action_date BETWEEN ? AND ?
        )
        """;
    return jdbcTemplate.query(sql, userRowMapper, startDate, endDate);
}

优化方案：分步查询 + Java处理

public List<User> getActiveUsersOptimized(Date startDate, Date endDate) {
    // 第一步：获取活跃用户ID
    String actionSql = """
        SELECT DISTINCT user_id 
        FROM user_actions 
        WHERE action_date BETWEEN ? AND ?
        """;
    List<Long> activeUserIds = jdbcTemplate.queryForList(actionSql, 
        Long.class, startDate, endDate);
    if (activeUserIds.isEmpty()) {
        return Collections.emptyList();
    }
    // 第二步：批量查询用户信息
    String userSql = buildBatchQuery(activeUserIds.size());
    return jdbcTemplate.query(userSql, userRowMapper, activeUserIds.toArray());
}
private String buildBatchQuery(int size) {
    // 使用IN查询，但限制数量避免SQL过长
    StringBuilder sql = new StringBuilder("SELECT * FROM users WHERE user_id IN (");
    for (int i = 0; i < size; i++) {
        sql.append("?");
        if (i < size - 1) sql.append(",");
    }
    sql.append(")");
    return sql.toString();
}

案例5：使用Stream API优化

原始多查询方式

public List<Order> getLargeOrdersForUser(Long userId) {
    // 先查用户信息
    User user = userRepository.findById(userId);
    // 再查订单
    String sql = """
        SELECT * FROM orders 
        WHERE user_id = ? AND amount > (
            SELECT AVG(amount) FROM orders WHERE user_id = ?
        )
        """;
    return jdbcTemplate.query(sql, orderRowMapper, userId, userId);
}

优化方案：单次查询

public List<Order> getLargeOrdersForUserOptimized(Long userId) {
    String sql = """
        SELECT o.* 
        FROM orders o
        CROSS JOIN (
            SELECT AVG(amount) as avg_amount 
            FROM orders 
            WHERE user_id = ?
        ) avg_table
        WHERE o.user_id = ? AND o.amount > avg_table.avg_amount
        """;
    return jdbcTemplate.query(sql, orderRowMapper, userId, userId);
}

性能优化建议

添加适当的索引

// 示例：为子查询相关的字段创建索引
@Component
public class DatabaseOptimizer {
    public void createOptimizedIndexes() {
        // 为关联字段创建索引
        String createIndex1 = """
            CREATE INDEX idx_orders_user_id ON orders(user_id)
            """;
        // 为排序和聚合字段创建复合索引
        String createIndex2 = """
            CREATE INDEX idx_sales_category_amount ON sales(category_id, amount DESC)
            """;
        // 执行创建索引
        jdbcTemplate.execute(createIndex1);
        jdbcTemplate.execute(createIndex2);
    }
}

使用缓存

@Service
public class CachedSubQueryService {
    @Autowired
    private RedisTemplate<String, List<Long>> redisTemplate;
    public List<Employee> getTopPaidEmployeesWithCache() {
        String cacheKey = "top_paid_employees";
        // 尝试从缓存获取
        List<Employee> cached = (List<Employee>) redisTemplate.opsForValue().get(cacheKey);
        if (cached != null) {
            return cached;
        }
        // 缓存未命中，执行查询
        List<Employee> result = executeActualQuery();
        // 存入缓存，设置过期时间
        redisTemplate.opsForValue().set(cacheKey, result, 1, TimeUnit.HOURS);
        return result;
    }
}

优化子查询的核心策略：

1. 使用JOIN代替子查询（特别是关联子查询）
2. 使用EXISTS代替IN（当检查存在性时）
3. 窗口函数替代（处理排名、分组取Top N等场景）
4. 分步查询 + Java处理（复杂逻辑）
5. 添加适当索引（关联字段、排序字段）
6. 使用缓存（不常变化的数据）

选择哪种优化方式取决于具体场景：

• 数据量大时,优先考虑JOIN+索引
• 存在性检查用EXISTS
• 复杂统计用窗口函数
• 业务逻辑复杂用Java处理