mysql在枚举字段（DB）中插入字符串值（java）时获取“列的数据被截断”

Question

在我的mysql表中，有一个枚举字段“spe_gender”

mysql> desc tbl_sswltdata_persons;
+-----------------+-----------------------+------+-----+---------+----------------+
| Field           | Type                  | Null | Key | Default | Extra          |
+-----------------+-----------------------+------+-----+---------+----------------+
| spe_id          | bigint(20) unsigned   | NO   | PRI | NULL    | auto_increment |
| spe_sen_id      | bigint(20) unsigned   | NO   | MUL | NULL    |                |
| spe_gender      | enum('male','female') | YES  |     | NULL    |                |
| spe_is_deceased | tinyint(1)            | NO   |     | 0       |                |
| spe_birth_place | varchar(255)          | YES  |     | NULL    |                |
| spe_create_date | datetime              | YES  |     | NULL    |                |
| spe_update_date | datetime              | YES  |     | NULL    |                |
+-----------------+-----------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

因此，我创建了一个POJO类：

public class SswltdataPersons implements Serializable {
    private static final long serialVersionUID = 1L;

    private long spe_id;
    private long spe_sen_id;
    private String spe_gender;
    private String spe_is_deceased;
    private String spe_birth_place;
    private String spe_create_date;
    private String spe_update_date;

    // .........

    public String getSpe_gender() {
        return spe_gender;
    }
    public void setSpe_gender(String spe_gender) {
        this.spe_gender = spe_gender;
    }

    // ......

}

当我试图将数据写入这个表时，我遇到了一个异常

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL 
    [INSERT INTO iwpro_imp.tbl_sswltdata_persons VALUES(?,?,?,?,?,?,?)]; 
    Data truncated for column 'spe_gender' at row 1; nested exception is java.sql.BatchUpdateException: Data truncated for column 'spe_gender' at row 1

我认为问题在于（通过java）在枚举字段（以DB为单位）中插入字符串值。这里是我的方法，我得到了例外

@Transactional(value="transactionManager_iwpro_imp", rollbackFor = Exception.class)
    public void saveAllPersons(final List<SswltdataPersons> list) {


        String sql = "INSERT INTO iwpro_imp.tbl_sswltdata_persons VALUES(?,?,?,?,?,?,?)";

        try{
            jdbcTemplate.update("SET foreign_key_checks = 0");

            List<List<SswltdataPersons>> batchLists = Lists.partition(list, batchSize);

            for(final List<SswltdataPersons> batch : batchLists) {
                BatchPreparedStatementSetter bpss = new BatchPreparedStatementSetter() {
                    @Override
                    public void setValues(PreparedStatement ps, int index) throws SQLException {
                        SswltdataPersons dataObject = batch.get(index);
                        ps.setLong(1, dataObject.getSpe_id());
                        ps.setLong(2, dataObject.getSpe_sen_id());
                        ps.setString(3, dataObject.getSpe_gender());
                        ps.setString(4, dataObject.getSpe_is_deceased());
                        ps.setString(5, dataObject.getSpe_birth_place());
                        ps.setString(6, dataObject.getSpe_create_date());
                        ps.setString(7, dataObject.getSpe_update_date());
                    }

                    @Override
                    public int getBatchSize() {
                        return batch.size();
                    }
                };
                jdbcTemplate.batchUpdate(sql, bpss);
            }

            jdbcTemplate.update("SET foreign_key_checks = 1");

        }catch(Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            logger.error("\n\nUnexpected Exception:\n", e);
            e.printStackTrace();
        }
    }

我不能在DB中插入这个枚举值吗

Answer 1

在java代码中，将spe_gender声明为枚举类型

private Gender spe_gender

其中性别是一个枚举类

public enum Gender {
    MALE,
    FEMALE
}

Answer 2

谢谢Mick Memmonic的建议。成功了

替换

ps.setString(3, dataObject.getSpe_gender().isEmpty());

与

ps.setString(3, dataObject.getSpe_gender().isEmpty() ? null : dataObject.getSpe_gender());

为我工作。谢谢大家