Spring Data Rest - PATCH Postgres jsonb字段

2 回答

• 2

  好吧，你的EntityManager不知道你的jsonObject字段里面有一些结构，它是纯字符串 . 您应该实现自己的解决方法 . 如何开始工作的一个例子就是https://github.com/bazar-nazar/pgjson但是这样的方法每次都需要你从数据库中读取对象，并进行另一次序列化/反序列化往返 .

  但如果您使用的是postgresql，则可以使用它的所有功能（注意：这会使您的应用程序与postgresql紧密结合，因此数据库将变得更难更换）

  我建议实现自定义jdbc查询，就像简单的例子：

  public static class JsonPatchRequest {
      String path;
      String operation;
      String value;
  }
  
  
  @Inject
  private JdbcTemplate jdbcTemplate;
  
  @PatchMapping(value = "/example/{id}") 
  public void doPatch(@PathVariable("id") Long id, @RequestBody JsonPatchRequest patchRequest) {
      // this line should transform your request path from  "/jsonobject/foo/bar"  to "{foo,bar}" string
      String postgresqlpath = "{" + patchRequest.path.replaceFirst("/jsonobject/", "").replaceAll("/", ",") + "}";
  
      switch(patchRequest.operation) {
          case "replace" :
              jdbcTemplate.execute("UPDATE example SET jsonobject = jsonb_set(jsonobject, ?, jsonb ?) WHERE id = ?", new PreparedStatementCallback<Void>() {
                  @Override
                  public Void doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                      ps.setString(1, postgresqlpath);
  
                      // this one transforms pure value, to string-escaped value (manual workaround)   so  'value' should become '"value"'
                      ps.setString(2, "\"".concat(patchRequest.value).concat("\""));
  
                      ps.setLong(3, id);
  
                      ps.execute();
                      return null;
                  }
              });
              break;
          case "delete" :
              jdbcTemplate.execute("UPDATE example SET jsonobject = jsonobject #- ? WHERE id = ? ", new PreparedStatementCallback<Void>() {
                  @Override
                  public Void doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                      ps.setString(1, postgresqlpath);
                      ps.setLong(2, id);
                      ps.execute();
                      return null;
                  }
              });
              break;
      }
  }
  

  还要注意：第一种方法会强制你制作预定义类型的jsonobjet字段，因此它可以用纯规范化实体替换，因此与它没什么关系 . 第二种方法不会强迫你在你的json中有任何类型的结构 .

  希望这会帮助你 .

  回复于 2024-04-26T08:23:53+08:00
• 1

  Assumes Hibernate 5 is used as JPA implementation

  使 jsonobject 字段成为特定的类类型（包含所需的字段）而不是 String .

  然后，您可以为 jsonb 类型添加自定义Hibernate用户类型 .

  @Entity
  @Table(name = "examples")
  public class Example {
  
      @Id
      @GeneratedValue(strategy = GenerationType.IDENTITY)
      private long id;
  
      @Basic
      @Type(type = "com.package.JsonObjectType")
      private JsonObject jsonobject;
  }
  

  自定义类型实现非常冗长，但实际上它使用Jackson的 ObjectMapper 将对象作为 String 传递给JDBC语句（反之亦然，从ResultSet中检索） .

  public class JsonObjectType implements UserType {
  
      private ObjectMapper mapper = new ObjectMapper();
  
      @Override
      public int[] sqlTypes() {
          return new int[]{Types.JAVA_OBJECT};
      }
  
      @Override
      public Class<JsonObject> returnedClass() {
          return JsonObject.class;
      }
  
      @Override
      public Object nullSafeGet(ResultSet rs, String[] names, SharedSessionContractImplementor session, Object owner) throws HibernateException, SQLException {
          final String cellContent = rs.getString(names[0]);
          if (cellContent == null) {
              return null;
          }
          try {
              return mapper.readValue(cellContent.getBytes("UTF-8"), returnedClass());
          } catch (final Exception ex) {
              throw new HibernateException("Failed to convert String to Invoice: " + ex.getMessage(), ex);
          }
      }
  
      @Override
      public void nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor session) throws HibernateException, SQLException {
          if (value == null) {
              st.setNull(index, Types.OTHER);
              return;
          }
          try {
              final StringWriter w = new StringWriter();
              mapper.writeValue(w, value);
              w.flush();
              st.setObject(index, w.toString(), Types.OTHER);
          } catch (final Exception ex) {
              throw new HibernateException("Failed to convert Invoice to String: " + ex.getMessage(), ex);
          }
      }
  
      @Override
      public Object deepCopy(final Object value) throws HibernateException {
          try {
              // use serialization to create a deep copy
              ByteArrayOutputStream bos = new ByteArrayOutputStream();
              ObjectOutputStream oos = new ObjectOutputStream(bos);
              oos.writeObject(value);
              oos.flush();
              oos.close();
              bos.close();
  
              ByteArrayInputStream bais = new ByteArrayInputStream(bos.toByteArray());
              return new ObjectInputStream(bais).readObject();
          } catch (ClassNotFoundException | IOException ex) {
              throw new HibernateException(ex);
          }
      }
  
      @Override
      public boolean isMutable() {
          return true;
      }
  
      @Override
      public Serializable disassemble(final Object value) throws HibernateException {
          return (Serializable) this.deepCopy(value);
      }
  
      @Override
      public Object assemble(final Serializable cached, final Object owner) throws HibernateException {
          return this.deepCopy(cached);
      }
  
      @Override
      public Object replace(final Object original, final Object target, final Object owner) throws HibernateException {
          return this.deepCopy(original);
      }
  
      @Override
      public boolean equals(final Object obj1, final Object obj2) throws HibernateException {
          if (obj1 == null) {
              return obj2 == null;
          }
          return obj1.equals(obj2);
      }
  
      @Override
      public int hashCode(final Object obj) throws HibernateException {
          return obj.hashCode();
      }
  }
  

  最后，您需要告诉hibernate将Java对象存储为 jsonb Postgre类型 . 这意味着创建自定义方言类（并在其中配置） .

  public class MyPostgreSQL94Dialect extends PostgreSQL94Dialect {
  
      public MyPostgreSQL94Dialect() {
          this.registerColumnType(Types.JAVA_OBJECT, "jsonb");
      }
  }
  

  有了这些，你应该没问题，Spring Data Rest补丁机制应该可行 .

  PS

  答案很大程度上受到了this github repo的启发，它基本上是相同的，但是使用了Hibernate 4.看一下 .

  回复于 2024-04-26T08:23:53+08:00