Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to retrieve jsonb column through JDBC

How to retrieve jsonb column through JDBC

From
Alexander Farber
Date:
Hello,

what do you use to retrieve a jsonb column using JDBC?

I have tried

        Object last_tiles = rs.getObject("last_tiles");

and the resulting Object seems to be a String.

Then I have called (using Jetty JSON class here):

         Object last_tiles = JSON.parse(rs.getString("last_tiles"));

And it seems to work ok... but I am still curious, what is the recommended (or maybe planned for future) way for retrieving jsonb data in Java.

Also

         List last_tiles = (List) JSON.parse(rs.getString("last_tiles"));

has not work for me even though the string is:

 [{"col": 7, "row": 8, "value": 1, "letter": "A"}, {"col": 7, "row": 7, "value": 2, "letter": "B"}, {"col": 7, "row": 9, "value": 2, "letter": "C"}]

but it is probably the problem of the Jetty class I am using and not of JDBC...

Regards
Alex


Re: How to retrieve jsonb column through JDBC

From
Dave Cramer
Date:
Unfortunately JSONB is output in text. So this is the way it is done.


On 27 August 2016 at 06:39, Alexander Farber <alexander.farber@gmail.com> wrote:
Hello,

what do you use to retrieve a jsonb column using JDBC?

I have tried

        Object last_tiles = rs.getObject("last_tiles");

and the resulting Object seems to be a String.

Then I have called (using Jetty JSON class here):

         Object last_tiles = JSON.parse(rs.getString("last_tiles"));

And it seems to work ok... but I am still curious, what is the recommended (or maybe planned for future) way for retrieving jsonb data in Java.

Also

         List last_tiles = (List) JSON.parse(rs.getString("last_tiles"));

has not work for me even though the string is:

 [{"col": 7, "row": 8, "value": 1, "letter": "A"}, {"col": 7, "row": 7, "value": 2, "letter": "B"}, {"col": 7, "row": 9, "value": 2, "letter": "C"}]

but it is probably the problem of the Jetty class I am using and not of JDBC...

Regards
Alex



Re: How to retrieve jsonb column through JDBC

From
Merlin Moncure
Date:
On Sat, Aug 27, 2016 at 5:39 AM, Alexander Farber
<alexander.farber@gmail.com> wrote:
> Hello,
>
> what do you use to retrieve a jsonb column using JDBC?
>
> I have tried
>
>         Object last_tiles = rs.getObject("last_tiles");
>
> and the resulting Object seems to be a String.
>
> Then I have called (using Jetty JSON class here):
>
>          Object last_tiles = JSON.parse(rs.getString("last_tiles"));
>
> And it seems to work ok... but I am still curious, what is the recommended
> (or maybe planned for future) way for retrieving jsonb data in Java.
>
> Also
>
>          List last_tiles = (List) JSON.parse(rs.getString("last_tiles"));
>
> has not work for me even though the string is:
>
>  [{"col": 7, "row": 8, "value": 1, "letter": "A"}, {"col": 7, "row": 7,
> "value": 2, "letter": "B"}, {"col": 7, "row": 9, "value": 2, "letter": "C"}]
>
> but it is probably the problem of the Jetty class I am using and not of
> JDBC...

huh.  what exactly is failing? are you getting a parse exception?

merlin

Re: How to retrieve jsonb column through JDBC

From
Alexander Farber
Date:


On Mon, Aug 29, 2016 at 7:50 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
On Sat, Aug 27, 2016 at 5:39 AM, Alexander Farber
<alexander.farber@gmail.com> wrote:
>
>          List last_tiles = (List) JSON.parse(rs.getString("last_tiles"));
>
> has not work for me even though the string is:
>
>  [{"col": 7, "row": 8, "value": 1, "letter": "A"}, {"col": 7, "row": 7,
> "value": 2, "letter": "B"}, {"col": 7, "row": 9, "value": 2, "letter": "C"}]
>
> but it is probably the problem of the Jetty class I am using and not of
> JDBC...

huh.  what exactly is failing? are you getting a parse exception?


fails with:

java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to java.util.List

Regards
Alex

Re: How to retrieve jsonb column through JDBC

From
Alban Hertroys
Date:
> On 29 Aug 2016, at 20:23, Alexander Farber <alexander.farber@gmail.com> wrote:
> On Mon, Aug 29, 2016 at 7:50 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Sat, Aug 27, 2016 at 5:39 AM, Alexander Farber
> <alexander.farber@gmail.com> wrote:
> >
> >          List last_tiles = (List) JSON.parse(rs.getString("last_tiles"));
> >
> > has not work for me even though the string is:
> >
> >  [{"col": 7, "row": 8, "value": 1, "letter": "A"}, {"col": 7, "row": 7,
> > "value": 2, "letter": "B"}, {"col": 7, "row": 9, "value": 2, "letter": "C"}]
> >
> > but it is probably the problem of the Jetty class I am using and not of
> > JDBC...
>
> huh.  what exactly is failing? are you getting a parse exception?
>
>
http://download.eclipse.org/jetty/9.3.11.v20160721/apidocs/org/eclipse/jetty/util/ajax/JSON.html#parse-java.lang.String-
>
> fails with:
>
> java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to java.util.List

I'm not 100% sure it's the root of the ClassCastException here, but I'm pretty sure that Java will want to know what
classof items that List consists of. 
If that doesn't ring a bell for you, spend some time reading about "Java generic classes" (not to be confused with
"generalJava classes"). 

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.