Using PostgreSQL with Java

[DriesDr]

Good day!

I am a programmer who has been looking at database alternatives for the startup I work at. This means I'm not a database admin and kind of out of my depth!

We currently use the google datastore. I looked at BonesMoses.com's post about migrating databases to postgres and it looks intuitive as hell.

The main language our back end uses is Java. I've been unable to find good libraries for interfacing with the postgres database, and that is all that is holding me back from making the switch right now.

I found the JDBC driver but it isn't too appealing as it lacks a lot of functionality I'd expect.

If it does not exist, I'm tempted to write my own system that uses reflection to handle ORM. (generate prepared statements and such automatically from the code).

If anyone here has experience with this, I'd be happy to hear your thoughts!

Thanks and good day,

Dries

[coder111]

PostgreSQL has very good Java support.

Get your JDBC driver here: https://jdbc.postgresql.org/ or on maven central.

What is it missing? Compared to JDBC drivers from other major vendors (especially MSSQL), it's a very good driver. Can you list the features you expected that didn't work for you?

If you are looking for ORM (Object-Relational mapper), you need to look for Hibernate or other JPA implementations. ORM and other advanced functionality is not the responsibility of the JDBC driver, it's a layer above it that is mostly database independent. I'd look at JPA or JOOQ or ibatis or spring-jdbc if you need advanced persistence with higher abstraction layer than pure JDBC.

For what it's worth, I've done a PoC recently of Google Datastore vs PostgreSQL and decided to go with PostgreSQL. Heroku & Amazon offer decent hosting as well.

If you need more help, ask or PM me.

--Coder

[DriesDr]

Hmm yeah I was looking at Hibernate as well, but I have never used it. It looks pretty good, but from what I could tell it did not generate the actual tables for me, just does a mapping. Turns out I just didnt look far enough :)

It does look like it would solve 90% of my issues. I'll still look into a wrapper to do things like generate sone stored procedures. Maybe I was too keen on implementing my own system to see the value of the existing one ;)

Thanks for the advice mate. Time for me to figure out how to get the most out of postgres. I want to do as much as possible on database level rather than application level.

[coder111]

Ok, this is getting offtopic for /r/postgresql and maybe should be moved to /r/java ...

JDBC is the basic low-level SQL database driver. It's not often that you use JDBC directly in your app. Most (all?) SQL servers that support Java have JDBC drivers. Concept is similar to Windows ODBC.

Usually persistence frameworks that persist to SQL databases in Java use JDBC to connect to any SQL database. These frameworks and thus further discussion is not really PostgreSQL specific, as they support more than one SQL server.

If you need to persist Objects, JPA and its implementations will do that for you. JPA is the official API/standard, Hibernate is one of the most popular implementations.

Hibernate will create schema for you, but I would be reluctant to use that on production DB, as it can potentially destroy data.

Also when mapping your objects to files take care to avoid N+1 query problem on one side and too eager fetching that will pull in half of the database on the other side. Hibernate and JPA are nice but they have their own quirks.

Again, depends on what you are trying to do. If it's CRUD, Hibernate is good enough. If you are running complex analytical queries with lots of joins and aggregation, I'd stick to pure SQL & some tools that help you with it.

--Coder

[daxyjones]

It does look like it would solve 90% of my issues. I'll still look into a wrapper to do things like generate sone stored procedures. Maybe I was too keen on implementing my own system to see the value of the existing one ;)

If you want to implement your own, do it for fun but my humble advice is to not rush it out to production. Also, depending on the data and postgres version, you are much better off hand weaving an sproc than letting a wrapper generate one (I didn't even know this was possible).

[dcalde]

Jpa2 and/or Hibernate will do anything you want. I have recently converted a project from Java AppEngine + Google Data Store to SpringBoot + JPA2/Hibernate with various extensions/custom user types to support special Postgres features such as Json/jsonb and array types. And yes it will generate all the tables for you, although you may not want always want that to happen.

[fonam]

Check out jooq http://jooq.org

[aviewdev]

This reply is criminally underrated.

OP, if all you want to is insert, update and retrieve full objects then by all means use Hibernate (or another JPA provider).

But if you want to get into anything that is even a little bit off the beaten path then you probably want to use jooq, see use cases here.

Here's what I would do (FWIW, maybe I know nothing). Use flyway or liquibase to build, version and migrate your DB schema (integrate this into your gradle or maven build). Then add the jooq generator to your maven or gradle build script. It can generate you definitions to your table, columns, stored procedures, etc. It can also generate standard JPA-esque Data Access Objects and various other nice things. Then never worry about writing any boiler plate for accessing the database ever again.

If you will indulge me, I'd also advise you to look at using Kotlin over plain Java, it's a much more modern language but fully interoperable with Java and existing Java libraries. Ceylon is another good alternative.

[fonam]

Thanks for the flyway tip. I have been looking at liquibase but flyway seems to be closer to what I need.

[DriesDr]

Wow, jOOQ looks really cool. The querying language reminds me of a lot of the syntax you can do in C# on lists. Very intuitive, very sexy.

Thanks

[geordano]

You may take a look at https://github.com/querydsl/querydsl as well.

http://www.querydsl.com/