java - 值在提交时发生变化。 |可调用语句

Question

我在使用 DAO 类和 StoredProcedure 时遇到了一个奇怪的问题，发生的事情是我使用了一个 CallableStatement 对象，它有 15 个 IN 参数，从 HTML 表单中正确检索字段 id_color 它甚至在 CallableStatement setter 方法中设置它应该如何设置，但是当它被发送到数据库时 id_color 被值 3 覆盖这里是“上下文”:我有以下类 DAO.CoverDAO 处理此表的 CRUD 操作

CREATE TABLE `cover_details` (
  `refno` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `shape` tinyint(3) unsigned NOT NULL ,
  `id_color` tinyint(3) unsigned NOT NULL ',
  `reversefold` bit(1) NOT NULL DEFAULT b'0' ,
  `x` decimal(6,3) unsigned NOT NULL ,
  `y` decimal(6,3) unsigned NOT NULL DEFAULT '0.000',
  `typecut` varchar(10) NOT NULL,
  `cornershape` varchar(20) NOT NULL,
  `z` decimal(6,3) unsigned DEFAULT '0.000' ,
  `othercornerradius` decimal(6,3) unsigned DEFAULT '0.000'',
  `skirt` decimal(5,3) unsigned NOT NULL DEFAULT '7.000',
  `foamTaper` varchar(3) NOT NULL,
  `foamDensity` decimal(2,1) unsigned NOT NULL ,
  `straplocation` char(1) NOT NULL ',
  `straplength` decimal(6,3) unsigned NOT NULL,
  `strapinset` decimal(6,3) unsigned NOT NULL,
  `spayear` varchar(20) DEFAULT 'Not Specified',
  `spamake` varchar(20) DEFAULT 'Not Specified',
  `spabrand` varchar(20) DEFAULT 'Not Specified',
  PRIMARY KEY (`refno`)
) ENGINE=MyISAM AUTO_INCREMENT=143 DEFAULT CHARSET=latin1 $$

The the way covers are being inserted is by a stored procedure, which is the following:

CREATE DEFINER=`root`@`%` PROCEDURE `putCover`(
                                    IN shape TINYINT,
                                    IN color TINYINT,
                                    IN reverse_fold BIT,
                                    IN x DECIMAL(6,3), 
                                    IN y DECIMAL(6,3),
                                    IN type_cut VARCHAR(10),
                                    IN corner_shape VARCHAR(10),
                                    IN cutsize DECIMAL(6,3),
                                    IN corner_radius DECIMAL(6,3),
                                    IN skirt DECIMAL(5,3),
                                    IN foam_taper VARCHAR(7),
                                    IN foam_density DECIMAL(2,1),
                                    IN strap_location CHAR(1),
                                    IN strap_length DECIMAL(6,3),
                                    IN strap_inset DECIMAL(6,3)
                                    )
BEGIN
    INSERT INTO `dbre`.`cover_details` 
    (`dbre`.`cover_details`.`shape`,
     `dbre`.`cover_details`.`id_color`,
     `dbre`.`cover_details`.`reversefold`,
     `dbre`.`cover_details`.`x`,
     `dbre`.`cover_details`.`y`,
     `dbre`.`cover_details`.`typecut`,
     `dbre`.`cover_details`.`cornershape`,
     `dbre`.`cover_details`.`z`,
     `dbre`.`cover_details`.`othercornerradius`,
     `dbre`.`cover_details`.`skirt`,
     `dbre`.`cover_details`.`foamTaper`,
     `dbre`.`cover_details`.`foamDensity`,
     `dbre`.`cover_details`.`strapLocation`,
     `dbre`.`cover_details`.`strapInset`,
     `dbre`.`cover_details`.`strapLength`
     )
    VALUES
    (shape,color,reverse_fold,
     x,y,type_cut,corner_shape,
     cutsize,corner_radius,skirt,foam_taper,foam_density,
     strap_location,strap_inset,strap_length);
END

正如您所看到的，它基本上只是填充了每个字段，现在，创建封面的 CoverDAO.create(CoverDTO cover) 方法如下所示:

public void create(CoverDTO cover) throws DAOException {
        Connection link = null;
        CallableStatement query = null;
        try {
            link = MySQL.getConnection();
            link.setAutoCommit(false);
            query = link.prepareCall(
                "{CALL putCover(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}"
                                    );
            query.setByte(1,cover.getShape().byteValue());
            query.setByte(2,cover.getColor().byteValue());
            query.setBoolean(3, cover.getReverseFold());
            query.setBigDecimal(4,cover.getX());
            query.setBigDecimal(5,cover.getY());
            query.setString(6,cover.getTypeCut());
            query.setString(7,cover.getCornerShape());
            query.setBigDecimal(8, cover.getZ());
            query.setBigDecimal(9, cover.getCornerRadius());
            query.setBigDecimal(10, cover.getSkirt());
            query.setString(11, cover.getFoamTaper());
            query.setBigDecimal(12, cover.getFoamDensity());
            query.setString(13, cover.getStrapLocation());
            query.setBigDecimal(14, cover.getStrapLength());
            query.setBigDecimal(15, cover.getStrapInset());
            query.executeUpdate();
            link.commit();
        } catch (SQLException e) {
            throw new DAOException(e);
        } finally {
            close(link, query);
        }
    }

CoverDTO 由访问器方法组成，MySQL 对象基本上返回池中的连接。

这是带有虚拟但适当数据的 pset 查询:putCover(1,10,0,80.0,80.0,'F','Cut',0.0,0,15.0,'4x2',1.5,'A',10.0,5.0)(删除了一些尾随零)正如您所看到的，当我在第二个参数中写入数据库而不是 10 时，一切都很好，写入了 3。我做了以下事情:

追踪 id_color 值到 create 方法，仍然被 3 替换

在 DAO create 方法中硬编码值，仍然被 3 替换

从 MySQL Workbench 调用过程，它工作正常，所以我假设创建方法中发生了一些事情，非常感谢任何帮助。

Answer 1

存储过程声明有误:

IN color TINYINT(3)

将其更改为:

IN color TINYINT

希望它能奏效。