I am using Flyway for all database migrations. It is time to handle binary data (images) when migrating. I am using Postgresql and Spring Data JPA.

First I had this field resulting in db column photo oid using Postgresql

@Entity
public class Person {
  // omitted 

  @Lob
  private byte[] photo;
}

My migration scripts look something like this

V1__CREATE_DB.sql
V2__INSERT_PERSON.sql
V3__INSERT_PHOTO.java

At first I did not manage to successfully migrate (update) a person with photo using JdbcTemplate. Later I found out that I could change the type oid to bytea by doing this.

@Lob
@Type(type = "org.hibernate.type.BinaryType")
private byte[] photo;

I then made the migration code looks like this

public void migrate(Context context) throws IOException {
  JdbcTemplate template = ...
  List<String> locations = ... // photo physical locations/paths

  for(String location: locations) {
    InputStream image = ... // from location
    Long id = ... // get id from image name

    template.update("UPDATE person SET photo = ? where id = "   id,
      new Object[] { new SqlLobValue(image.readAllBytes(), new DefaultLobHandler()) },
      new int[] { Types.BLOB }
    );
  }
}

This V3__ migration works as expected however

• Is there a better way to implement this migration and should I be able to also do this for oid and in that case how?

• Is there a reason for not choosing bytea over oid except for obvious storage capacity differences?

CodePudding user response：

After almost breaking Google I finally managed to find a solution for how to update column photo oid with JdbcTemplate.

DefaultLobHandler lobHandler = new DefaultLobHandler();
lobHandler.setWrapAsLob(true);

jdbcTemplate.execute("UPDATE person SET photo = ? where id = ?", new AbstractLobCreatingPreparedStatementCallback(lobHandler) {                                                       
  protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException {     
    lobCreator.setBlobAsBinaryStream(ps, 1, image, image.available());                                        
    ps.setLong(2, id);
  }
}