java - 将图像插入 BYTES 列 - 类型 "binary"不存在

Question

我正在尝试使用 jOOQ 将来自 Spring 的 MultipartFile 字段的图像插入到 Postgres 的 BYTEA 列中，但我一直收到令人困惑的错误消息。这是什么意思，应该如何插入？

CREATE TABLE image
(
    id      SERIAL    PRIMARY KEY,
    data    BYTEA     NOT NULL
)

import org.springframework.web.multipart.MultipartFile;

public class AddIMageForm {
    private MultipartFile image;
}

import javax.persistence.Column;

public class Image {

    @Column(name = "id")
    private Integer id;

    @Column(name = "data")
    private byte[] data;
}

import static com.test.Tables.IMAGE;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.jooq.DSLContext;

@Controller
public class AddImageController {

  @Autowired
  DSLContext jooq;

  @RequestMapping(value = "/addImage", method = RequestMethod.POST)
  public ModelAndView addImagePost(Model model, 
              @ModelAttribute("addImageForm") AddImageForm addImageForm) {

    byte[] imageBytes = addImageForm.getImage().getBytes();
    jooq.insertInto(IMAGE).columns(IMAGE.DATA).values(imageBytes).execute();
    return new ModelAndView("/viewImage");
  }
}

执行时会给出令人困惑的错误信息:

org.jooq.exception.DataAccessException: 
SQL [insert into "image" ("data") values (cast(? as binary))]; 
ERROR: type "binary" does not exist
  Position: 58
    at org.jooq_3.10.8.H2.debug(Unknown Source)
    at org.jooq.impl.Tools.translate(Tools.java:2241)
        ...

Answer 1

您的异常堆栈跟踪显示了原因。您已经配置了 SQLDialect.H2 方言，但在 PostgreSQL 上运行了您的查询。使用 SQLDialect.POSTGRES 方言。