Tuesday, April 24

Author: Umair Shahid

Using Java Database Interface (jDBI) with PostgreSQL

2ndQuadrant, Umair's PlanetPostgreSQL
jDBI is designed to be the middle ground between high level ORMs like JPA & Hibernate on one hand and raw JDBC on the other. You can think of it as a layer of convenience on top of JDBC that is still at a lower level than ORMs. When working with JDBC, you are exposed to the raw workings of the SQL language and that of the database you are connecting to. jDBI tries to expose API that encapsulates this and is oriented towards Java programmers rather than database developers. It does so in two different styles, namely fluent style and SQL object style. Fluent style provides the facility of inline querying within your Java program whereas the SQL object style simplifies DAO creation using annotations. Both styles are demonstrated below. Why Use jDBI? You should consider using jDBI (more…)

PGConf APAC 2018 – bigger & better each year

2ndQuadrant, Umair's PlanetPostgreSQL
Last week saw the 3rd event in as many years in Singapore, and yet this time something was very different. For the last 2 years, we had been conducting the conference as a PostgreSQL track with FOSSASIA. This year, we were able to pull off an entirely independent, 2-day event completely focused on PostgreSQL! The event kicked off with a keynote from Joe Conway (flying in all the way from USA!), who talked about the features to look forward to in PostgreSQL 11. The release is currently scheduled for September 2018. This was followed by 8 talks on the first day and 7 talks on the second day. We also had a round of lightening talks, which were followed by a speaker/sponsor dinner. As an organizer, one of the things that made the conference very exciting for me was the quality  (more…)

Using EclipseLink with PostgreSQL

2ndQuadrant, Umair's PlanetPostgreSQL
1. Introduction EclipseLink was announced in 2008 as the JPA 2.0 implementation from the Eclipse Foundation. It is based on the TopLink project from which then Oracle contributed code to the EclipseLink project. The project delivers an open source runtime framework supporting the Java Persistence API standards. The EclipseLink project provides a proven, commercial quality persistence solution that can be used in both Java SE and Java EE applications. EclipseLink is open source and is distributed under the Eclipse Public License. 2. Implementation Details Like Hibernate, EclipseLink is also fully JPA 2.0 compliant. This makes the implementation details quite similar to those already described in a preceding Hibernate blog. For the illustration below, we will continue using the (more…)

Using Java ORMs with PostgreSQL – MyBatis

2ndQuadrant, Umair's PlanetPostgreSQL
In my previous blogs, I wrote about Hibernate Query Language (HQL) and Querydsl in detail, now I'm going to talk about MyBatis. While ORMs typically map Java objects to database tables (or vice versa), MyBatis takes a different approach by mapping Java methods to SQL statements. This gives you complete control over writing SQL and its subsequent execution. With the help of a mapper, MyBatis also allows automatic mapping of database objects to Java objects. Like all other Java persistence frameworks, the main aim of MyBatis is to reduce the time and coding requirements of talking to a database using raw JDBC. It is licensed as Apache License 2.0 and is free to use. Why Use MyBatis? MyBatis design has a database-centric approach, so if your application is driven by relational (more…)

PostgreSQL 11 – Server-side Procedures (Part 2)

2ndQuadrant, Umair's PlanetPostgreSQL
Transaction control in PL procedures A couple of months back, I wrote about how we now have the ability to write Stored Procedures in PostgreSQL. This post follows up on that and talks about the next step that was implemented: transaction control in PL procedures. The feature was committed on 22-Jan-2018. With this addition, you now have the ability to call COMMIT and ROLLBACK commands in PL/pgSQL from within a procedure. To illustrate: CREATE TABLE test1 (a int); CREATE PROCEDURE transaction_test1() AS $$ BEGIN    FOR i IN 0..9 LOOP        INSERT INTO test1 (a) VALUES (i);        IF i % 2 = 0 THEN            RAISE NOTICE 'i=%, txid=% will be committed', i, txid_current();            COMMIT;        ELSE (more…)

PostgreSQL 11 – Server-side Procedures (Part 1)

Umair's PlanetPostgreSQL
Last week marked a rather big step in the PostgreSQL world that went largely unnoticed. Thanks to the work done by 2ndQuadrant contributors, we now have the ability to write Stored Procedures in PostgreSQL! (Ok, well not exactly now but we will have the ability once PostgreSQL 11 comes out) A procedure is essentially a set of commands to be executed in a particular order. As opposed to functions, procedures are not required to return a value. With this addition, you can now invoke a procedure by simply using the new CALL statement rather than using SELECT. The implementation is fully compliant with the SQL standard and will allow users to write procedures that are somewhat compatible with DB2, MySQL, and to a lesser extent, Oracle. The commit from last week adds new commands CALL (more…)
v10, The Best PostgreSQL Yet?

v10, The Best PostgreSQL Yet?

Umair's PlanetPostgreSQL
The short answer … Hell Yeah! The long answer lies in extensive improvements and the impressive new feature list that makes up this major release - which, by the way, changes the version scheme of PostgreSQL as well (more details on that here). This wiki page lists out, in detail, all the new features in PostgreSQL 10, but for the purpose of this blog, I will focus on some of the exciting features contributed by 2ndQuadrant. (more…)

Java Object Oriented Querying (jOOQ)

Umair's PlanetPostgreSQL
jOOQ is an ORM-alternative that is relational model centric rather than domain model centric like most ORMs. For example, while Hibernate lets you write Java code and then automatically translates it to SQL, jOOQ let’s you write relational objects in your database using SQL and then automatically generates Java code to map to those relational objects. The writers of jOOQ believe in the power of SQL and assume that you want low level control of the SQL running for your application. This means that you can write your database and your schema without having to worry about how (and if!) it can be handled in Java. Why Use jOOQ? While JPA provides a huge framework with a great deal of flexibility and power, it can very quickly become quite complex. jOOQ provides a simpler interface for (more…)

2ndQuadrant – Not the Gartner Magic Quadrant

2ndQuadrant
It’s still appropriate though. Because that is what we are - thought leaders in open source PostgreSQL. But that's not what I am here to talk about. The name "2ndQuadrant" comes from "The Seven Habits of Highly Effective People" by Stephen Covey, specifically Habit 3 "Put First Things First", p.151. It refers to the classification of tasks in terms of 2 axes: Importance and Urgency. The second quadrant is the Important, Not Urgent quadrant. According to Covey, if you concentrate on doing work in that space, it leads to "vision, perspective, balance, discipline, control and few crises" - i.e. you focus on your long-term vision and your ability to execute short to medium term goals is also impeccable. These are all qualities highly valued by every individual at 2ndQuadrant; so (more…)

Processing PostgreSQL JSON & JSONB data in Java

Umair's PlanetPostgreSQL
This is part of a series of blogs on Java & PostgreSQL. For links to other blogs in this series, please scroll to the end of this post. Starting v9.2, PostgreSQL is providing native data type support for JSON objects. Subsequent releases introduced JSONB (binary formatted JSON objects) and many data manipulation functions for JSONs, making it a very powerful tool for NoSQL operations. String manipulation and parsing are very expensive operations in a database, so although you could have potentially stored JSON objects in strings in PostgreSQL before, introduction of the native data type has taken away overheads and made throughput a lot faster for JSON manipulation. JSON & JSONB JSONB was introduced as a native data type in v9.4 of PostgreSQL and it stores JSON objects (more…)