This week's book giveaway is in the Go forum.
We're giving away four copies of Head First Go and have Jay McGavren on-line!
See this thread for details.
Win a copy of Head First Go this week in the Go forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Bear Bibeault
  • Paul Clapham
  • Jeanne Boyarsky
Sheriffs:
  • Devaka Cooray
  • Junilu Lacar
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Ron McLeod
  • Tim Holloway
  • Claude Moore
  • Stephan van Hulst
Bartenders:
  • Winston Gutkowski
  • Carey Brown
  • Frits Walraven

mySql select statement with returning JSON data types via Java  RSS feed

 
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So I have this test MySQL db with a table test1 with JSON datatype our_data:

'{\"cars\": {\"chev\": \"bolt\", \"ford\": \"explorer\", \"jeep\": \"ranger\"}}'

I can connect successfully to the base on if a select without JSON_KEYS

     

return a string, however if I add a JSON_KEYS

     

I get:

SQL Connection to database established!
Connection Failed! Check output console
Column 'our_data' not found., 0

I'm pretty sure it's because JSON_KEYS(our_data) does not return a String Object!

I'm thinking I've got some parsing to do. But what return type does the select with JSON_KEYS return? And how does one parse an JSON OBJECT from a MySQL DB?
Is there a sassy myRs.getJSONTYPETHING("our_data") that returns the correct object? Do I have to cast the returned value? A million questions?

This completely new to me and I'm just testing whether it's a good idea to include JSON OBJECTS in a MySQL DB.

Ideas? Thoughts? Solutions? Directions? Bad Idea?!





 
Saloon Keeper
Posts: 2410
296
Android Angular Framework Eclipse IDE Java Linux MySQL Database Redhat TypeScript
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
   - Are you running MySQL version 5.7 or higher?
   - Does your column have a datatype of JSON?
   - Have you tried running the query from the command line?
 
Mike Parish
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Running MySQL Version 8.0.15.
Created the table with command "create table test1 (our_data JSON)";
I can use all JSON commands (JSON_PRETTY, JSON_KEYS, JSON_CONTAINS_PATH, etc) from the command line.
 
Ron McLeod
Saloon Keeper
Posts: 2410
296
Android Angular Framework Eclipse IDE Java Linux MySQL Database Redhat TypeScript
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please post the output of select JSON_KEYS(our_data) from test1 when run from the command line.

Does it have a column heading of our_data ?
 
Mike Parish
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
mysql> select our_data from test1;


+------------------------------------------------------------------+
| our_data                                                         |
+------------------------------------------------------------------+
| {"cars": {"chev": "bolt", "ford": "explorer", "jeep": "ranger"}} |
+------------------------------------------------------------------+
1 row in set (0.00 sec)



mysql> select JSON_KEYS(our_data) from test1;
+---------------------+
| JSON_KEYS(our_data) |
+---------------------+
| ["cars"]            |
+---------------------+
1 row in set (0.00 sec)


Return from the command line run on Terminal on my Mac.

It fails using Java.
 
Ron McLeod
Saloon Keeper
Posts: 2410
296
Android Angular Framework Eclipse IDE Java Linux MySQL Database Redhat TypeScript
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Mike Parish wrote:mysql> select JSON_KEYS(our_data) from test1;
+---------------------+
| JSON_KEYS(our_data) |
+---------------------+
| ["cars"]            |
+---------------------+
1 row in set (0.00 sec)



It fails using Java.


It is failing because in your code, you are specifying a column name of our_data, but the result is using a column name of JSON_KEYS(our_data)
Three possible fixes:
   - change your code to specify column name of JSON_KEYS(our_data): myRs.getString("JSON_KEYS(our_data)")
   - change your code you specify a column number rather than name: myRs.getString(1)
   - change your query to return a column name of our_data: select JSON_KEYS(our_data) AS our_data from test1;
 
Mike Parish
Greenhorn
Posts: 24
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So, following your advise:




Tada Result:

Doc is: ["cars"] <- exactly what I see from the command line.

Once you see it, it makes so much sense. I think it's time to practise select/insert/delete/update commands in MySQL.

Thanks for all your help.
 
Ron McLeod
Saloon Keeper
Posts: 2410
296
Android Angular Framework Eclipse IDE Java Linux MySQL Database Redhat TypeScript
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Great.  Thanks for reporting back on your success.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!