Win a copy of Event Streams in Action this week in the Java in General forum!

Mike Parish

Greenhorn
+ Follow
since Oct 24, 2003
Cows and Likes
Cows
Total received
0
In last 30 days
0
Total given
0
Likes
Total received
1
Received in last 30 days
0
Total given
0
Given in last 30 days
0
Forums and Threads
Scavenger Hunt
expand Ranch Hand Scavenger Hunt
expand Greenhorn Scavenger Hunt

Recent posts by Mike Parish

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.
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.
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.
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?!





Wow! Thanks. Servlets/JSP is probably the method I'll use. I have some experience with them.

Some PHP developers tried to convince me that MVC architecture was possible. I never bought it.

I use Java, (a lot of Lists & ArrayLists), to work on the database. Much more efficient to bundle the return data.

My only other concern is finding a Web Host that will support an Website using JAVA Servlet / JSP at a reasonable cost. I have to sell this to my clients.
6 months ago
Been using PHP to develop Website that require access to either Oracle or MySQL databases. It's easy and a lot of Internet Service Provider support PHP/MySQL platforms, yet not so many support Java Servlets.
I prefer Java and always wanted to migrate to my website development Servlets/JSTL.
Is this a wise decision? Is there a better combination of software apps to develop website using JAVA?

Most site I help develop with are not mobile sites, usually used by mid-sized companies for administration/inventory using MySQL.
6 months ago
You're right and I have decided to use classes. Way easier in the long run. Some I can reuse, but the nature of the business is extract data as required by the client, hence there may be a need to constantly review and/or update  classes.

It just seemed so inefficient. My background is as a Oracle/MySQL DBA; efficiency is the everything.  

I got toss into the Java/JSP development due to a temporary shortage qualified Java developers, however plans are afoot to hire some quickly.

I'm learning, ever so slowly, but I'm learning.  
Have you looked at RazorSQL for mac?

I've use Razor FTL for mac and it works.
That exactly what I was thinking.

I can easily create a 'masterlist' class, then via select statement (I'm pretty good at SQL) extract what I need, populate the 'masterclass' with data and pass that to the JSP.

In this case it's easy, I've normalized the schema to a few critical table that, by using property constructed SQL statements, will efficiently extract the dataset.

I could ever create a generic format layer and pass that along to the JSP.

A lot easier to maintain and places DB extraction on the DB sides, where is should be.

OK, I got there. DBA have very large blinkers.

Nothing more dangerous than a DBA like me that thinks I know more than I really know.

Regards.
Tried the delimiter, works great but......

I see your point about the my DBUtil is NOT a model. A row does represent something and that thing should be handle as a class.

Could I get your thoughts on creating a class for every possible select statement. Just to maintain the code if any select statement is modified the corresponding class must be changed and/or any new select needed a new class. Does that not seem quite inefficient?  Whereas passing a kluged array removes that problem.  
I do. I have a DBUtil class that is called by my controller to handler all Database actions.

The problem remains: I need a mechanism to hold extracted data from DB, that is then passed to a JSP Page for viewing.  It's the accumulation of data into a container that can be passed to the JSP: It's temporary, should not require a permanent class.
If I need to create a class to reflex every selection criteria it is conceivable that I could end up with a multitude of classes.

Your correct, either solution is not optimal and probably violates the MVC design, but I'm not smart enough to figure out how to get around the initial problem.
Thank you for all the replies. I really like the Object array.

I could create a 'master' class that incorporates the fields from all the table then use a select statement to limit the amount of data retrieved from the DB but that seems inefficient.

Currently, there is only 2 select statement against the base but that will change as the software matures. I don't want to create a bunch of class to response to each select statement.
Another fix is to incorporate the SQL into the JSP, but I'm trying to follow MVC protocols, running through a controller and limiting programming logic in JSP pages. I'm trying to not replicate PHP code from previous releases and more toward Java MVC design.

If Java would allow a mixed variable array container (perhaps it does I don't have the experience, I'm a Oracle/MySQL DBA with enough Java to be dangerous) so that a temporary memory location could accommodate data selection for processing.

I shall test both the Object array and the ArrayList<String> and see how they work.

I'll let you know how it goes.

Again Thanks!
Here's my problem.

My SQL returns data from two different table.

My select statement returns two fields from one table Fam1 (one int and one String) and one field from another table Fam2. But how do I handle the result set? I need some type of container (List/Map/Arraylist) that I can load with  
with different data types.  In this case each array element would need to store (int, string, string).

I need a list to be populate a JSP page.

Its easy when you have a class, read each column from the resultset into the appropriate variable, create a new object using the class constructor. I've not no class, no constructor, just a set of data.

I'm stuck!
Growing up in Northern Canada (British Columbia, near the Yukon border) anything amount less than 30cm was considered a light snowfall. Now I live in Toronto, and anything over 3 cm is considered a snow storm.  Wow!
1 year ago
I get your point.

This is really a display issue.

I just want to overwrite the line.

Currently, every time an email is sent by the server function sendEmail I display that email address in a new row on the client machine (using an echo statement). Next time through the loop another row is displayed in the table (another <td></td>).

Is there anyway to not generate another row? Just overwrite the existing row?