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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/jooq-user/4c49856a-a3e5-454d-937d-9b62576fac22n%40googlegroups.com.
