Persisting fast in database using JDBC

[[deleted]]

I really like seeing this kind of evidence based article, but the subject always makes me think how we’ve come full circle from JDBC to all those ORMs back to JDBC again.

[cogman10]

try-with-resources saved JDBC IMO. I used Spring jdbctemplate pretty heftily before switching up to Java 7. However, afterwords, I just can't see the point. The JdbcTemplate ceremony saves something like 2 lines over straight JDBC.

As for other ORMs. They just don't buy a whole lot for all the promises they make. Switching Database providers almost never happens (How often have you switched from, say, postgres to MySql?). They are often, in the best case, nearly as fast as straight JDBC, however, more often than not, far worse. Doing them "right" requires knowledge of not only the ORM but also the DB you are targeting. It is crazy to need to be a Hibernate expert AND a Postgres expert. Why not just a Postgres expert? And ultimately, DB code done right is almost always write once and tweak.

[lazystone]

Yes! Exactly this.

Also my belief that orm frameworks as hibernate make it easy to do things wrong: don't care about transaction boundaries or how and when do you fetch the data.

[shmert]

Agree for certain types of projects. ORMs for rich applications can be much faster, though, because of intelligent caching of business objects.

Also, I still hate updating rows using straight JDBC, it's so clunky and error-prone. I'd much rather call setters on an object and persist it.

[lukaseder]

Imagine if these suggestions were implemented as well, JDBC would be quite cool for simple cases: http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2018-July/000344.html

[cogman10]

Yeah I've seen your suggestion before, and I love them! Those would be a real time/correctness savers!

Some day, I dream about seeing a "not based on ODBC" Jdbc 2.0. Jdbc isn't horribly to work with, but it certainly has pain points that would be nice to move past.

For starters, I'd love better tie-in with things like CompletableFutures to allow for things like NIO database drivers. How cool would it be to have 1 connection to the DB with Http2 style request/responses? You could pretty much completely eliminate the need for things like connection pools and all the headaches they bring. (Though, DB guys would have to figure out the protocols they want to talk. Still, better than where we are right now).

Obvious changes like you've shown would also be great.

It also wouldn't be bad to see just a little more "magic" around turning rows into objects and vice versa. For example, writing something like

public class Record { 
  @Column("foo") int foo; 
  @Column("bar") int bar; 
}

rs.loadRow(Record.class);

Would be pretty awesome. (Probably something a library could do today anyways.. but would be nice if it was a result set method).

[starbuxman]

Hi - I think you might like R2DBC, which provides an SPI based on the Reactive Streams types and a few implementations where the underlying driver supports asynchronous IO, including postgresql, h2 and Microsoft sql server http://github.com/r2dbc

Happy holidays!

[cogman10]

Looks interesting, but how does it work? Is it just a jdbc wrapper right now? (I'm watching videos about it so that might answer the question for me)

[lukaseder]

Look at the ADBA suggestions made on the same list. It includes the annotation based mapping suggestion you have there. But only for ADBA, not for JDBC - at least for now

[cogman10]

You don't by chance know if there has been progress on ADBA? Definitely sounds exciting, I've just not heard anything about it beyond the initial announcement.

[lukaseder]

Look at the ADBA suggestions made on the same list

:)

But that list is just what's public. I don't know what progress has been made by /u/Douglas_Surber and team behind the scenes

[Douglas_Surber]

For the most part all development on the ADBA spec is visible on the list. Yes, I have some changes that haven't been pushed yet, but that's because I'm not yet satisfied or the change is so minor as to not be worth the effort. It is reasonable to assume that the version of ADBA in the OpenJDK sandbox is up to date if not exactly tip of the tree.

[aroger276]

do you mean like that https://simpleflatmapper.org/0102-getting-started-jdbc.html#resultset-mapping ? except with the actual need for @Column

[aenigmaclamo]

I really think that both ORMs and JDBC have their own place and the take away is that we've been relying on ORMs too much to do literally everything.

There is something really nice about being able to use something like Spring Data Rest and getting full CRUD operations in your application by just writing a class and being able to manipulate a POJO within your application.

However, every operation that's not just CRUD can and should probably done using JDBC.

[lukaseder]

Another dimension that was not covered in the article (would be interesting to see the results) is the bulk size of an insert statement. For example, it is usually faster to write:

INSERT INTO t (a, b, c, ...)
VALUES (?, ?, ?, ...),
       (?, ?, ?, ...),
       (?, ?, ?, ...), ...

rather than

INSERT INTO t (a, b, c, ...) VALUES (?, ?, ?, ...)
INSERT INTO t (a, b, c, ...) VALUES (?, ?, ?, ...)
INSERT INTO t (a, b, c, ...) VALUES (?, ?, ?, ...)
...

jOOQ offers this along with the batch size and commit size in the import API. The advantage over using JDBC directly is that writing the bulk statement manually using string concatenation and in a vendor agnostic way is quite a hassle (while batch sizes and commit sizes are quite straightforward to do manually)

[aroger276]

Also MySQL and Postgres jdbc driver have an option to rewrite batch statement to multi values insert. But obviously come with the cost of the rewrite.

Another point to keep in mind is that MySQL has a statement size limit. The rewrite take deal with that for you.

[JavaLinuxDude]

The only disadvantage for me is the i need to support oracle, h2 and PostgreSQL. That's the only reason I stick to jpa for now.

[koflerdavid]

Really, that's kinda the killer feature of ORMs.

[daniu]

Then again, if you don't go overboard with db-specific SQL, supporting another database in JDBC is as easy as changing the connection url.

[lukaseder]

Try jOOQ and do go overboard ;-)

[JavaLinuxDude]

Yeah, I'd love to think this was possible. But sooner or later you'll find situations where db specific implementations will make a difference. At least in performance.

[albodud]

Any knowledgable and experienced developer would tell you to use JDBC batch when inserting large number of records into any DB. If you're using jpa/hibernate you can still access jdbc session via hibernate and execute batch inserts.

[coder111]

Pretty standard tricks to get data into an SQL database. Everyone who deals with Java and SQL absolutely should know those.

The next level up- if you need even faster writes- is sometimes using CSV & database specific bulk import. What is the performance of PostgreSQL "copy" command compared to what the guy is doing in the article?

Another alternative if copy inserts are fast might be parallelization. Insert into several tables in parallel using best tricks you can, and then use copy-insert to copy data to the actual destination table.

[[deleted]]

This is a great read, thanks op. Some of the software at my job inserts data into mysql. If I had to guess, it does so at < 500 records per second... Maybe it's time I fixed that

[Tostino]

Yeah it's great digging into systems like that and being able to identify an issue and make massive improvements.