在SQL关联表创建条目时遇空值及PreparedStatement语法错误求助

核心问题分析 #

1. PreparedStatement多语句执行失败 #

默认情况下，JDBC的PreparedStatement不支持在单个语句中执行多条以分号分隔的SQL，即便你在SQL客户端能正常运行，JDBC驱动会将整段内容当作单条SQL解析，直接导致语法错误。

2. 获取自增ID的方式存在并发风险 #

通过SELECT order_id INTO @newid ... ORDER BY order_id DESC LIMIT 1获取最新order_id的逻辑，在多线程并发场景下会拿到其他线程插入的ID，完全不可靠。

3. readLatest()返回空值的原因 #

• create方法与readLatest()使用独立连接，若create方法的事务未提交，readLatest()用新连接无法查询到刚插入的数据。
• readLatest()中直接调用resultSet.next()，若查询结果为空（比如无数据）会抛出SQLException，最终导致方法返回null。

修复代码 #

1. 修改create方法，用getGeneratedKeys获取自增ID并保证事务一致性 #

@Override
public Order create(Order order) {
    Connection connection = null;
    try {
        connection = DBUtils.getInstance().getConnection();
        // 关闭自动提交，开启事务
        connection.setAutoCommit(false);
        
        // 第一步：插入orders表，获取自增order_id
        String insertOrderSql = "INSERT INTO orders(fk_customer_id) VALUES (?)";
        try (PreparedStatement orderStmt = connection.prepareStatement(insertOrderSql, PreparedStatement.RETURN_GENERATED_KEYS)) {
            orderStmt.setLong(1, order.getId());
            orderStmt.executeUpdate();
            
            // 获取生成的order_id
            Long newOrderId = null;
            try (ResultSet rs = orderStmt.getGeneratedKeys()) {
                if (rs.next()) {
                    newOrderId = rs.getLong(1);
                }
            }
            
            if (newOrderId == null) {
                throw new RuntimeException("Failed to get generated order_id");
            }
            
            // 第二步：插入orders_items表
            String insertItemSql = "INSERT INTO orders_items(fk_order_id, fk_item_id, quantity) VALUES (?, ?, ?)";
            try (PreparedStatement itemStmt = connection.prepareStatement(insertItemSql)) {
                itemStmt.setLong(1, newOrderId);
                itemStmt.setLong(2, order.getItem_id());
                itemStmt.setInt(3, order.getQuantity());
                itemStmt.executeUpdate();
            }
            
            // 提交事务
            connection.commit();
            
            // 查询刚创建的订单（避免并发问题）
            return readOrderById(newOrderId);
        } catch (Exception e) {
            // 回滚事务
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (SQLException ex) {
                    LOGGER.error("Rollback failed", ex);
                }
            }
            LOGGER.debug(e);
            LOGGER.error(e.getMessage());
        } finally {
            if (connection != null) {
                try {
                    connection.setAutoCommit(true);
                    connection.close();
                } catch (SQLException e) {
                    LOGGER.error("Close connection failed", e);
                }
            }
        }
    } catch (Exception e) {
        LOGGER.debug(e);
        LOGGER.error(e.getMessage());
    }
    return order;
}

// 新增根据ID查询订单的方法，替代readLatest避免并发问题
private Order readOrderById(Long orderId) {
    try (Connection connection = DBUtils.getInstance().getConnection();
         PreparedStatement statement = connection.prepareStatement("SELECT * FROM orders WHERE order_id = ?")) {
        statement.setLong(1, orderId);
        try (ResultSet resultSet = statement.executeQuery()) {
            if (resultSet.next()) {
                return modelFromResultSet(resultSet);
            }
        }
    } catch (Exception e) {
        LOGGER.debug(e);
        LOGGER.error(e.getMessage());
    }
    return null;
}

2. 修复readLatest()的空指针问题 #

public Order readLatest() {
    try (Connection connection = DBUtils.getInstance().getConnection();
         Statement statement = connection.createStatement();
         ResultSet resultSet = statement.executeQuery("SELECT * FROM orders ORDER BY order_id DESC LIMIT 1")) {
        // 先判断是否有结果
        if (resultSet.next()) {
            return modelFromResultSet(resultSet);
        }
    } catch (Exception e) {
        LOGGER.debug(e);
        LOGGER.error(e.getMessage());
    }
    return null;
}

额外说明 #

• 若一定要用多语句执行（不推荐），需在JDBC URL中添加allowMultiQueries=true参数（以MySQL为例），但这种方式会增加SQL注入风险，且不利于事务控制。
• 始终用getGeneratedKeys()获取自增主键，这是JDBC标准方式，安全且可靠。
• 涉及多表操作时必须开启事务，保证数据一致性。

内容的提问来源于stack exchange，提问作者Jade P.