Stringtify对象未能作为JSON存储在mysql中

Question

我试图使用hibernate压缩字符串列表并将其以JSON格式存储在mysql中：

List<String> options = new ArrayList<>();
String first = "What is your name?";
options.add(first);

String option = objectMapper.writeValueAsString(options);
// option value is: ["what is your name?"]

我的桌子结构：

CREATE TABLE question(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  label VARCHAR(150) NULL,
  question_type VARCHAR(200) NULL,
  mandatory TINYINT DEFAULT 0,
  editable TINYINT DEFAULT 0,
  `option` JSON NULL
);

实体：

@Entity
@Table(name = "question")
public class Question implements Serializable {

    /**
     * 
     */
    private static final long serialVersionUID = 2209760405736391727L;

    private int id;

    private String label;

    private QuestionType questionType;

    private boolean mandatory;

    private boolean editable;

    private String options;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    @Column(name = "label")
    public String getLabel() {
        return label;
    }

    public void setLabel(String label) {
        this.label = label;
    }

    @Column(name = "question_type")
    @Enumerated(EnumType.STRING)
    public QuestionType getQuestionType() {
        return questionType;
    }

    public void setQuestionType(QuestionType questionType) {
        this.questionType = questionType;
    }

    @Column(name = "mandatory")
    public boolean isMandatory() {
        return mandatory;
    }

    public void setMandatory(boolean mandatory) {
        this.mandatory = mandatory;
    }

    @Column(name = "editable")
    public boolean isEditable() {
        return editable;
    }

    public void setEditable(boolean editable) {
        this.editable = editable;
    }

    @Column(name = "option")
    public String getOptions() {
        return options;
    }

    public void setOptions(String options) {
        this.options = options;
    }
}

但是，它没有坚持到mysql中：

错误：could not execute statement

详细错误：Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'option, question_type) values (0, 'label 1', 1, '["what is your name?"]', 'SINGL' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

我试着在网上搜索，但没有得到任何提示。

Answer 1

问题仅仅在于您的option列的名称，因为它是mysql中的关键字。您在ddl语句中引用了它，但是当hibernate生成查询时，它不会引用每个默认名称。您可以通过设置(这将引用所有db标识符)来更改它：

hibernate.globally_quoted_identifiers=true

或者您可以更改列映射：

@Column(name="\"option\"")

或者，不要将关键字用作数据库架构中的名称。