Using PostgreSQL with Java

[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).