Java中SQLite数据库关联Car与Wheel类的实现问题

方法1：预加载Wheel缓存（推荐，减少数据库查询次数） #

先把所有Wheel数据从数据库查出来，存入以wheel_id为键、Wheel对象为值的Map中，后续创建Car时直接从Map取对应Wheel，避免重复查询。

代码示例 #

// 先实现获取所有Wheel的方法
private Map<Long, Wheel> getAllWheels() {
    Map<Long, Wheel> wheelMap = new HashMap<>();
    String wheelQuery = "SELECT wheel_id, param1, param2 FROM WHEEL_DB";
    try {
        this.main.getDatabase().connect();
        ResultSet rs = this.main.getDatabase().getResultOf(wheelQuery);
        while (rs.next()) {
            Wheel wheel = new Wheel(
                rs.getLong("wheel_id"),
                rs.getString("param1"),
                rs.getString("param2")
            );
            wheelMap.put(wheel.getWheelId(), wheel);
        }
        rs.close();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        this.main.getDatabase().disconnect();
    }
    return wheelMap;
}

// 修改后的getCarManager方法
public ArrayList<Car> getCarManager() {
    ArrayList<Car> carList = new ArrayList<>();
    // 预加载所有Wheel到缓存
    Map<Long, Wheel> wheelMap = getAllWheels();
    
    String carQuery = "SELECT car_id, wheel_id, other FROM CAR_DB ORDER BY car_id";
    try {
        this.main.getDatabase().connect();
        ResultSet rslt = this.main.getDatabase().getResultOf(carQuery);
        while (rslt.next()) {
            Long carId = rslt.getLong("car_id");
            Long wheelId = rslt.getLong("wheel_id");
            String other = rslt.getString("other");
            
            // 从缓存获取对应Wheel，若不存在可处理为null或抛出异常
            Wheel wheel = wheelMap.get(wheelId);
            
            // 创建Car实例（确保Car构造方法参数为car_id, wheel, other）
            carList.add(new Car(carId, wheel, other));
        }
        rslt.close();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        this.main.getDatabase().disconnect();
    }
    return carList;
}

方法2：创建Car时实时查询Wheel（适合小数据量场景） #

如果Wheel数据量小，可在遍历Car结果集时，根据每个wheel_id单独查询对应的Wheel对象。

代码示例 #

// 新增根据wheel_id查询单个Wheel的方法
private Wheel getWheelById(Long wheelId) {
    Wheel wheel = null;
    String query = "SELECT wheel_id, param1, param2 FROM WHEEL_DB WHERE wheel_id = ?";
    try {
        this.main.getDatabase().connect();
        // 建议用PreparedStatement防止SQL注入，此处假设数据库工具支持参数化查询
        ResultSet rs = this.main.getDatabase().getResultOf(query, wheelId);
        if (rs.next()) {
            wheel = new Wheel(
                rs.getLong("wheel_id"),
                rs.getString("param1"),
                rs.getString("param2")
            );
        }
        rs.close();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        this.main.getDatabase().disconnect();
    }
    return wheel;
}

// 修改后的getCarManager方法
public ArrayList<Car> getCarManager() {
    ArrayList<Car> carList = new ArrayList<>();
    String query = "SELECT car_id, wheel_id, other FROM CAR_DB ORDER BY car_id";
    try {
        this.main.getDatabase().connect();
        ResultSet rslt = this.main.getDatabase().getResultOf(query);
        while (rslt.next()) {
            Long carId = rslt.getLong("car_id");
            Long wheelId = rslt.getLong("wheel_id");
            String other = rslt.getString("other");
            
            // 根据wheel_id查询对应Wheel对象
            Wheel wheel = getWheelById(wheelId);
            
            // 创建Car实例
            carList.add(new Car(carId, wheel, other));
        }
        rslt.close();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        this.main.getDatabase().disconnect();
    }
    return carList;
}

额外注意事项 #

• 确保Car类的构造方法参数顺序、类型正确，例如public Car(Long carId, Wheel wheel, String other)
• 若数据库中存在Car对应的wheel_id不存在的情况，需处理wheel为null的场景，比如添加非空校验或设置默认值
• 方法1的缓存方式可避免N+1查询问题（每查一个Car就查一次Wheel），性能更优，适合数据量大的场景

内容的提问来源于stack exchange，提问作者vmicrobio