Support to write via java.io.Reader to use in java.sql.PreparedStatement?

[rdehuyss]

Is your feature request related to a problem? Please describe.

I think I'm missing something trivial but I would like to stream objects to a PreparedStatement using PreparedStatement.setCharacterStream(int parameterIndex, java.io.Reader reader).

The thing is that SQL drivers are pull based (they start consuming the reader when executeUpdate is called) and Jackson is push based (it writes to a Writer or an OutputStream). Hence, there is no easy way to stream a Java object in JSON format to the database.

All examples out there first map each object to a String causing excess GC and memory allocation that could be avoided.

We could used PipedInputStream and PipedOutputStream and link those but it requires extra thread management which is a road I'm not ready to go down yet.

Describe the solution you'd like

A way that Jackson provides a Reader for a Java object. This Reader would allow to read the Java object as Json.

Usage example

preparedStatement.setCharacterStream(1, objectMapper.writeValueViaReader(object));

Additional context

This is for https://github.com/jobrunr/jobrunr

[pjfanning]

How about https://docs.oracle.com/javase/8/docs/api/java/io/PipedWriter.html ?

[pjfanning]

My view is ask is achievable with existing classes and that there are higher priorities for Jackson development. If a working solution was provided by a third party, maybe that would work but the key thing is not introduce new lib dependencies and that the code is well tested and easily maintainable.

[rdehuyss]

How about https://docs.oracle.com/javase/8/docs/api/java/io/PipedWriter.html ?

See my original post where I mention the PipedInputStream and PipedOutputStream. The extra thread management in a high concurrent system is something I'd like to avoid.

[rdehuyss]

And p.s.: thanks for providing and supporting this awesome open-source library. I think 90% of the world is working thanks to Jackson 😄 .

[JooHyukKim]

See my original post where I ...

Could you share also here link to the mentioned original post?

[pjfanning]

I don't think we need any changes in Jackson. What @rdehuyss needs is a class that converts a Writer to a Reader. Using a Search Engine for this returns just PipedWriter and https://ostermiller.org/utils/CircularBuffer.html. It is not an issue that many coders have taken on or that most have given up because of the complexity. The Ostermiller solution, in theory, allows you to constrain memory usage by limiting the buffer size - at the expense of blocking on the reads and writes.

It is not an easy problem to solve, having a solution that allows the same thread to push data and also at the same pull data.
With Virtual Threads in recent Java releases, the thread overhead becomes very low and makes PipedWriter/PipedReader the most obvious way to go.

If constraining memory usage is what is needed then you would probably want to look at using a Reactive Framework (eg Flux) where you can apply back pressure to code producing the JSON when the DB writes start falling behind - and they likely will fall behind because they are I/O bound while the JSON producer is done in memory. Many other users would separate the tasks and push the JSON to some sort of queue or topic using Kafka, ActiveMQ, etc.

[cowtowncoder]

Yeah, handling push/pull impedance conversion is a rather tricky thing to do and I don't think this is something to try to solve at Jakson(-databind) level. There must be solutions for this in existence...

[rdehuyss]

First of all - thanks again for answering so swiftly and providing your valuable input.

The reason I'm quite hesitant to use a PipedWriter / PipedReader or PipedInputStream / PipedOutputStream is that there is just too much risk and complexity for the moment:

• Although we would wish for the world to be already on Java 21 with Virtual Threads, only 5% is using Java 21 (see https://newrelic.com/resources/report/2024-state-of-the-java-ecosystem)
• Java 21 with virtual threads and JDBC drivers don't always work well together yet due to synchronized usage and Thread pinning (this happened to one of our users just last month blocking the complete application).
• In case this is consumed in Java without support for Virtual Threads, an ExecutorService would be needed to manage the thread pool for the PipedWriter / PipedReader. How big does this thread pool need to be? Same as the amount of connections the connection pool allows? What would you use as a default? These are threads that JobRunr then can not use for processing Jobs.
• When using the PipedWriter / PipedReader or the CircularBuffer, these are again short-lived objects (although small objects) that will only live for the duration of copying the data and will need to be garbage collected.

The reason I think this would be an awesome feature:

• most databases now support JSON out of the box. This would really ease saving JSON quickly without overhead to the database. In high throughput scenario's with larger JSON documents (in our case if we go to > 10k jobs/sec), this results in quite a lot of unnecessary GC.

• I'm just trying to follow the best practices from Jackson 😄 :

  Use "unrefined" (least processed) forms of input: i.e., do not try decorating input sources and output targets:
  Input: byte[] is best if you have it; InputStream second best; followed by Reader -- and in every case, do NOT try reading input into a String!

  From what I know, 95% of the world (if not more) is converting it to a String and then saving that String to the database 😆 .

So, although I understand this is not an easy nor trivial feature, I just wanted to see your opinion on this and discuss whether this is worth further exploring...

@JooHyukKim

Could you share also here link to the mentioned original post?

Sorry, I meant my original question:

We could used PipedInputStream and PipedOutputStream and link those but it requires extra thread management which is a road I'm not ready to go down yet.

Edit

I just starred the repo also - I don't know why I did not do this before 🤦 . Again, from all the JSON libraries we support, Jackson Databind really rules!

[cowtowncoder]

Right. Fundamentally with blocking I/O, someone or something must manage threading, if matching impedance between OutputStream (or Writer) something writes out to, and InputStream / Reader the other side needs. At least I do not see a way around that, due to blocking nature of things.
Jackson does not do any thread management all reading/writing is within single Thread.
At least if keeping amount of buffering to low fixed length (a buffer or two full of data max).
So thread handling would be whole new part of machinery, for output side.

Alternative approach would be asynchronous handling but:

1. Jackson only supports async reading, not writing (although I'm sure async writing would be doable... somehow)
2. PreparedStatement is based on blocking I/O abstraction anyway.

Having said all that, it is an interesting problem/challenge -- it's for output side, and I am curious which other libraries, frameworks have done this. I can see its applicability, just not yet approach to implement it.

ps. I think this may belong more to jackson-core (streaming layer), but it's ok to keep issue here for now.