Folks, have you had a look at what pgjdbc does with your InputStream or OutputStream? Hint: spare yourself the premature optimisation. Just use byte[] :)
Bind values: https://github.com/pgjdbc/pgjdbc/blob/release/42.7.x/pgjdbc/src/main/java/org/postgresql/util/StreamWrapper.java#L46 Results: https://github.com/pgjdbc/pgjdbc/blob/release/42.7.x/pgjdbc/src/main/java/org/postgresql/jdbc/PgResultSet.java#L2804 Cheers Lukas On Wed, Nov 29, 2023 at 9:48 PM 'tod...@googlemail.com' via jOOQ User Group <jooq-user@googlegroups.com> wrote: > Hi Sverre, > > in my apps (Spring Boot + Vaadin + JOOQ) I also have to store files > uploaded by the user. > If I do this in the Postgresql, which is BTW not everytime a good > solution, I usually split document meta data (filename, MIME type, size > etc.) from document content (the byte array only). > Often, the UI shows some document meta data in the first place and storing > and retrieving has to be therefore pretty easy,. > And my method for storing the maybe large content looks like this, and > surprisingly the comment contains a link to this group WHY it look so :-) > > /** > * @see https://groups.google.com/g/jooq-user/c/Gwn0rce_J-Q Daher > wird das hier > */ > private void storeContent(Integer id, byte[] content) { > > if (this.jooq.configuration().connectionProvider() instanceof > DataSourceConnectionProvider dscp) { > var dataSource = dscp.dataSource(); > try (Connection conn = dataSource.getConnection(); > ByteArrayInputStream is = new > ByteArrayInputStream(content); > PreparedStatement ps = conn.prepareStatement("insert into > document_storage (id, content) values (?, ?)")) { > ps.setInt(1, id); > ps.setBinaryStream(2, is, content.length); > ps.executeUpdate(); > } catch (Exception ex) { > log.error(ex.getMessage()); > } > } > } > > Hope that helps > > Kind regards > Dominik > > On Wednesday, 29 November 2023 at 18:30:18 UTC+1 sverre.ale...@gmail.com > wrote: > >> Hi, >> >> I have a use case where I store and retrieve large blobs (1 MB - 200 MB) >> from a postgres database. >> >> On the input side I've managed to stream spring boot MultipartFiles from >> a REST interface all the way into the DB using the following code: >> public class StreamingBlobBinding implements Binding<byte[], InputStream> >> { >> @Override >> public Converter<byte[], InputStream> converter() { >> return new Converter<>() { >> >> @Override >> public InputStream from(byte[] bytes) { >> return new ByteArrayInputStream(bytes); >> } >> >> @Override >> public byte[] to(InputStream inputStream) { >> try { >> return IOUtils.toByteArray(inputStream); >> } catch (IOException e) { >> throw new RuntimeException(e); >> } >> } >> >> @Override >> public Class<byte[]> fromType() { >> return byte[].class; >> } >> >> @Override >> public Class<InputStream> toType() { >> return InputStream.class; >> } >> }; >> } >> >> @Override >> public void sql(BindingSQLContext<InputStream> ctx) { >> if (ctx.render().paramType() == ParamType.INLINED) { >> ctx.render().visit(DSL.inline(ctx.convert(converter()).value())); >> } else { >> ctx.render().sql(ctx.variable()); >> } >> } >> >> @Override >> public void register(BindingRegisterContext<InputStream> ctx) throws >> SQLException >> { >> ctx.statement().registerOutParameter(ctx.index(), Types.BLOB); >> } >> >> @Override >> public void set(BindingSetStatementContext<InputStream> ctx) throws >> SQLException >> { >> // This line is responsible for the streaming >> ctx.statement().setBinaryStream(ctx.index(), ctx.value()); >> } >> >> @Override >> public void set(BindingSetSQLOutputContext<InputStream> >> bindingSetSQLOutputContext) throws SQLException { >> throw new SQLFeatureNotSupportedException(); >> } >> >> @Override >> public void get(BindingGetResultSetContext<InputStream> >> bindingGetResultSetContext) throws SQLException { >> throw new SQLFeatureNotSupportedException(); >> } >> >> @Override >> public void get(BindingGetStatementContext<InputStream> >> bindingGetStatementContext) throws SQLException { >> throw new SQLFeatureNotSupportedException(); >> } >> >> @Override >> public void get(BindingGetSQLInputContext<InputStream> >> bindingGetSQLInputContext) throws SQLException { >> throw new SQLFeatureNotSupportedException(); >> } >> } >> >> and >> >> public static long saveFileData(MultipartFile file) throws IOException { >> final var id = uow.ctx.nextval(FILE_DATA_SEQ); >> var inputStreamType = FILE_DATA.DATA.getDataType().asConvertedDataType(new >> StreamingBlobBinding()); >> var dataColumn = DSL.field(FILE_DATA.DATA.getName(), inputStreamType); >> var result = uow.ctx.insertInto(FILE_DATA) >> .columns(FILE_DATA.ID, dataColumn) >> .values(DSL.val(id, FILE_DATA.ID.getDataType()), >> DSL.val(file.getInputStream(), inputStreamType)) >> .execute(); >> >> if (result != 1) { >> throw new DataAccessException("Unable to store filedata"); >> } >> >> return id; >> } >> >> >> I'm having more difficulty getting it to work the other way around, i.e. >> having Jooq fetch a BLOB from postgres and storing the result using a >> OutputStream. Do anyone have any tips on how I can achieve this? >> >> Best regards, >> Sverre Aleksandersen >> > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to jooq-user+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/jooq-user/6a8de7e2-bd55-4033-86fd-0c9bc943a592n%40googlegroups.com > <https://groups.google.com/d/msgid/jooq-user/6a8de7e2-bd55-4033-86fd-0c9bc943a592n%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO4Pm-iH8jN-qpnvyre0_faVFLgPdwmEkmxwgkNuvv52Rg%40mail.gmail.com.
