Win a copy of OCP Java SE 8 Programmer II Exam Study Guide this week in the OCP forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

mysql select into List/Arraylist without a class  RSS feed

 
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!
 
Sheriff
Posts: 23451
46
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Mike Parish wrote: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.



So make a class then. Or are you working on a project which forbids you from creating new classes? Hopefully not, that would be an awful environment to work in. Anyway I don't see anything in the rest of your post which prevents you from modelling a row of your ResultSet as an object of some class.
 
Ranch Foreman
Posts: 360
10
Java Notepad
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I agree with Paul.

Just adding some details:

In case you don't want to create a class you can make an Object array. Something like this:



Use the outList in the report you are printing.

But, instead of using Object [] for a row of data, a class can be made like this:

 
Prasad Saya
Ranch Foreman
Posts: 360
10
Java Notepad
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
But, using an Object[] to represent a row's data has the disadvantage that there is no 'name' for each field. One need to be careful while using such a way.
 
Prasad Saya
Ranch Foreman
Posts: 360
10
Java Notepad
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Here is another way:

Use a ArrayList<String> to store the the result set data.
Define a String delimiter = "|";

Within the result set loop:
String row = Integer.toString(int intColumnValue) + delimiter + firstString + delimiter + secondString;
list.add(row);


In the report:
Get the string from the list.
Use string.split(delimiter) to get a String array of three elements.
If needed, convert the integer string to an int, using the Integer.parseInt() method.

Also, StringBuilder objects can be used, instead of a String, in similar way. The append() and toString() methods in StringBuilder can be used.
 
Mike Parish
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!
 
Rancher
Posts: 3385
38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you're doing MVC then you really ought to have a Model that represents the data in your View.
And no, an  Object[] or List<String> is not really a model.
 
Prasad Saya
Ranch Foreman
Posts: 360
10
Java Notepad
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

If Java would allow a mixed variable array container...



What is a mixed variable array container? I am curious to know.

 
Mike Parish
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Dave Tolls
Rancher
Posts: 3385
38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
DBUtil is not a model.
It's a way of getting data from the DB.

That data (well, each "row" of that data") represents something.
That something ought to have a model, ie a Java class associated with it.

The front end (your JSP) should not have to know that index 0 of an Object[] is an id, and index 1 is a name, and so on.
It should simply have the model, which will be self explanatory.

I know coming from a db environment you are used to working with cursors around your queries, and crunching based on that, but the crunching is normally pretty close to the query (at least when I wrote PLSQL packages).
In Java, your display code is a couple of layers away from the query, and so needs something a little less fragile and error prone.  Besides, it's an OO language, so you probably ought to get used to using it as such.
 
Prasad Saya
Ranch Foreman
Posts: 360
10
Java Notepad
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Okay. About the mixed variable array container. I think this is what you have on your mind:



The output:
[1, A, 25.9]

The code compiles with all kinds of threats (warnings, not errors), if one is using Java SE 5 or above. But, get an output.
 
Mike Parish
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.  
 
Dave Tolls
Rancher
Posts: 3385
38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As it is intended for the JSP, the model(s) in this case ought to be based on those pages.

You may be able to merge some together. 

For example, a page displaying a table of Things, but only showing the name/date/id for example, so that the user can select one of them and then display all the detail for the selected Thing.
Well, to me that's a single model object called Thing.  You may only populate some of the fields of the class for the first (table) page, but it's still essentially the same thing.

Not knowing what the app is it's harder t say.  But, expect to have a lot more model classes than anything else.
 
Mike Parish
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Dave Tolls
Rancher
Posts: 3385
38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Works both ways.
I know just about enough to be a menace with Explain Plan...give me an Ask Tom column and I think I know it all!
 
Saloon Keeper
Posts: 19269
85
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What you are attempting to do is re-create the Object Relational Model (ORM) architecture.

In ORM, the model units are called Entity Classes, and the ORM framework manages the database connections and populates/saves entity values to and from the database.

Originally there were quite a few ORM systems in existence and I've had to deal with several roll-your-own ORMs in addition to commercial/open-source ones, but these days, there's an ORM standard built into the Enterprise Java JEE specs. It's part of the EJB3 sub-spec and it's called Java Persistence Architecture (JPA). It does not require EJBs or an EJB server to use, but EJBs make use of it. One of the most popular JPA implementations is Hibernate JPA (the original Hibernate was one of the pre-JPA ORM systems and is not totally compatible with JPA). We have an entire forum on the Ranch for ORMs, in fact.

As you have determined, there are basically 2 ways to house data coming from a database SELECT (whether it's a simple table, JOIN, or something even more complex). One is to return the data as a collection of heterogeneous tuples. For example, a row can be an array of Objects, where element 0 might be an Integer, element 1 might be a String, element 3 might be a Character, and so forth. If you prefer, you can swap out an Object array for a List<Object> or some other congenial tuple container.

The other way is strongly-typed mapping, which is what an Entity class is. The advantage of this is that the elements of the tuple are pinned down as to type, which means that you can access them in a type-safe manner (which usually means more readable code and less error-prone type-casting). An additional benefit is that you are accessing by name instead of column number, which means that the application code doesn't care if the columns of the SELECT move around.

For simple apps, roll-your own is less work and less overhead. Then again, Java isn't the best language to use for simple apps. When you have complex apps, having an ORM manage the data is less overall work, and much less debugging in the database operations section of the code. Plus an ORM has tuning and caching options.

As a final note, JPA Entity objects are POJOs. That means that you can use them freely in non-database code without needing to add extra definitions like the old DTO objects of pre-EJB3 days. And since a lot of web MVC frameworks such as JavaServer Faces are also POJO-oriented, you can use the same classes and instances in both the database layer as ORM Model objects and presentation layer as UI presentation Model objects.
 
Paul Clapham
Sheriff
Posts: 23451
46
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Mike Parish wrote: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.



I don't see why it's a problem if you create several classes. And I'm not sure what your distinction between "temporary" and "permanent" classes is -- if you asked me what the difference was, I would have to say that a "temporary" class was one which your design needs now, but you expect your design to change so that it won't be needed at some future time. Whereas I get the impression that you don't want to create a class simply for the purpose of transporting data from the database to a JSP page. There's nothing wrong with creating a class for that purpose.

But you don't need one for "every selection criteria". If you've got "Select x, y, z from Recipes" then creating a Java Beans class to contain x, y, and z would be normal. You'd also use that same class for "Select x, y, z from Recipes where garnish = 'strawberries'" and for "Select x, y, z from Recipes where q in (select name from Poison)" and for many other queries.

And since I mentioned Java Beans: those are the ideal tool for passing data to a JSP. You simply include some JSTL code which gets the data from an object of such a class using the name of a property. This makes it easy to read. Using an array of objects would require something much messier and harder to understand. Java Beans were designed for this task, so they're the answer to your question.
 
Mike Parish
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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. 
 
Prasad Saya
Ranch Foreman
Posts: 360
10
Java Notepad
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There is book "Core Servlets and JSP - Vol 1", by Marty Hall. In the past, I had found the book very useful when learning and writing JSP.

Starting with Chapter 10: Overview of JSP Technology and ending with Chapter 16: Simplifying Access to Java Code: The JSP 2.0 Expression Language, all the chapters 10 thru 16 cover aspects and the usage of JSP in various situations. Note that this is not an advanced writing.

Marty Hall (the book author) also has a website where you will find some useful info building the JSP applications: http://www.coreservlets.com/

Hope you will find something useful with the above info in putting together your app.
 
Tim Holloway
Saloon Keeper
Posts: 19269
85
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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



Two great myths:

1. Assembly language is more efficient than high-level languages
2. Raw (JDBC) data access is more efficient than an ORM system

Sound logical don't they? Intuitively obvious.

But wrong.

More accurately, at small scales, these statements are true. You have very precise control over what gets executed and when and how, so how could a mere mechanical process top that?

The answer is: when you scale up.

One of my most famous examples was when I took a mis-behaving assembly language utility for the OS/MVS operating system and rewrote it from scratch using IBM's Pascal/VS. This was a system catalog backup utility. It ran for about an hour each night. And then died.

The rewrite not only ran to successful completion in 20 minutes, it added some options to validate the catalog and even to rename cataloged files.

A lot of this was a different algorithm, and the Pascal version did take a lot more core to run, but memory wasn't as tight as CPU time, so who cared?

The thing that killed me on assembly language once and for all was when I looked at the generated code and realized that not only was the generated code extremely well-optimized (unlike the relatively brain-dead compilers before 1980), but also that it could globally re-optimize register usage each and every time I re-compiled. I'm not saying I couldn't do as well myself, but A) who's obsessive enough to re-write the entire program every time a change is made and B) who do you think would pay for it to be done? My employer had better uses for me.

Newer systems can do even more. They can watch code as it's being executed and re-write it on the fly. Back before instruction timing became an exercise in complex mathematics (thanks to multiple pipelines), a conditional branch on many machines took significantly more time if it branched than if it didn't. A smart re-coder can move stuff around, flip the logic so that the most common case is the no-branch one and save nanoseconds by the bucketful. Again, for small-scale stuff, the overhead to manage all this is prohibitive, but for really heavy-duty stuff, it can more than pay for itself.

Much the same applies to ORM systems. In simple cases, the overhead is way too expensive, but in complex cases, benchmarks have given speed increases of 2 to 1.

And, incidentally, I date from a time when a database was itself a horrible waste of resources. Real Programmers used VSAM. Or ISAM. Or, by gosh darnit, BDAM. Dang kids! 
 
Prasad Saya
Ranch Foreman
Posts: 360
10
Java Notepad
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Technology was much easier to adapt to in the past. I had worked with COBOL on Novell LAN, on VAX/VMS, on Primos with ISAM, with DG/SQL on AOS/VS, OS/400 and DRAM/DEF on IBM mainframes. There were rarely any books (except manuals) and no internet or web or forums or blogs or tutorials.
 
Tim Holloway
Saloon Keeper
Posts: 19269
85
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Prasad Saya wrote:Technology was much easier to adapt to in the past. I had worked with COBOL on Novell LAN, on VAX/VMS, on Primos with ISAM, with DG/SQL on AOS/VS, OS/400 and DRAM/DEF on IBM mainframes. There were rarely any books (except manuals) and no internet or web or forums or blogs or tutorials.



Nostalgia. Approaching a 6-foot long stack of books and pulling staples/peeling glue backing so you can replace/insert documentation updates. IBM Redbooks. Prime Technical Updates.

On the plus side, phone support where you didn't grow old being told "Your call is very important to us", and support people who weren't just monkey-with-a-script. Amazing what you can get when you pay $5000 a month (in 1980 dollars) for just one software product.

And actual user groups. And companies that would spend money to send you to other cities to vendor-sponsored user conventions where you could talk to the people who designed and supported your product, network, and by-the-by, do a little sightseeing/partying. Getting trained as opposed to being expected to have been born knowing the exact version of a software product used by your employer or prospective employer.

And, of course, the idea that once hired, you didn't have to immediately start sending out résumés in anticipation of being laid off within 2 years or so.

But no JavaRanch.

There's a lot I like about the tools and options in modern-day software development and support, but a lot has been lost as well. Expectation of quality for one. You can often get better support for "free" software than you can for products by Fortune 50 corporations, and when I worked with IBM mainframes, IBM never asked us if we'd tried turning them off and back on again. OS/MVS was expected to keep running without interruption for weeks at a time.
 
Prasad Saya
Ranch Foreman
Posts: 360
10
Java Notepad
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

...but a lot has been lost as well. Expectation of quality for one.



Once in a ISO 9000 certification meeting for a small software consulting company, I mentioned : when I go out I always make sure I am combed well, clothed to impress etc. It is inherent to human nature, this quality, most of the time at least. But, its so difficult with the software. Good software, software 100% accurate and delivered on time, on the date (I have seen those heppening and more than once). Like any other product.  When I get my new car all shining and smiles on the day of the delivery. But, not my software.

My first real encounter with a relational database was with DG/SQL. There was SQL and there were queries. Back then too. And there was query optimization. Its there today too. Not much progress really, there is no automatic query optimization tool, which does on its own, on the fly. That would be intelligent software (may be its there, I don't know).

Nostalgia, yes. Actually its software engineering. I learnt my software doing flowcharts, Ed Yourdon's function decomposition diagrams and Tom DeMarco's data flow diagrams. They did analysis and design for me then. I occasionally still rely on them, just to think software...
 
Paul Clapham
Sheriff
Posts: 23451
46
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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



It's not like efficiency is nothing in Java. The thing is: beginners to Java have no way to tell what's more efficient than something else, and they have no way to tell whether it matters. Personally I would guess that creating an object of a JavaBean class is pretty much equivalent to creating an array (which is also an object). And creating an object of a class nowadays is measured in nanoseconds -- how much work do you want to do to cut your web response time down by less than a millisecond?

It's also true that Java experts can't reliably tell what's more efficient than something else. But (if they're doing it right) they do usually have ways of telling whether it matters. Measuring and profiling the execution of code is a well-known technique now, and it can point to code which might be worth optimizing. But even that -- there's not much point even doing that unless you're not satisfied with the performance of what you've already got.
 
Tim Holloway
Saloon Keeper
Posts: 19269
85
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's really all measurement and statistics these days.

Back when I worked on mainframes, we had a disassembler that could take a compiled module, print out the assembly-language equivalent of the object code with an instruction timing value beside it. Some other machines like the Prime computer had instructions that took 1, 2, or 3 clock cycles, so as long as you knew the clock speed, you could compute precisely how long things would take.

But modern-day CPUs run many pipes in parallel and even do trial branches which will be used or discarded based on other instructions, There can be 3, 4, 5, who knows how many instructions simultaneously in different stages of processing instead of the atomic one-instruction-at-a-time architecture of earlier years. My BIOS also allows me to deliberately inject "jitter" into instruction timing which is supposed to reduce radio noise or something.

And that's just the lower levels. Java defines what a JVM must do but not how to do it, and there are many ways to get things done. In 1985, the Amdahl 470/v6 I worked with had a memory capacity of 16 MB. That's for the OS, shared memory and applications programs, including the CICS telecommunications control program. Last time I checked, I think a JVM on my system required over 100MB of RAM. Just for that one JVM instance. You can hide a lot of variable functions in that much memory, even allowing for the fact that a lot of it is object space.

So computing resource usage in advance is pretty much an exercise in futility. The only way you can really tell is to measure.

Obviously there are some things you can do to avoid bottlenecks. Clean, simple code is almost always more efficient (although having the right algorithm for the job is important, too!) So is minimizing use of high-overhead or high-latency resources (like disk I/O and network traffic). Simple solutions are also easier to optimize. But the final adjudication requires measurement. And prioritization. Machine time is dirt cheap these days, but developer time is not. Wasting a lot of time optimizing things that run fine as they are won't make you a hero. As I once pointed out to a fellow (mainframe) employee, you can't save microseconds in a jar for a rainy day.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!