Class that returns Mysql Data?

[Goler McBain]

Hey there,

first of all I want to say hello as this is my first post in this great forum. I hope I can seek some help here as well as help others, if my knowledge is good enough!

I have sort of a logic problem with a class I wanted to develop. That class should get the Mysql statement as input, and return the Mysql data from the mysql database. And the returning part is where I have a problem.

Most of the time the Mysql data that I get from the database will be more then one row, one column. So how am I supposed to return the data?

Idea 1: return as multidimensional array.
Idea 2: make the class have 2 methods, one for making the mysql request, and one for giving out the data, returning one line through the .next command, as String(?) and then loop the method from the class that requests the data.
Idea 3: Don't go the way of a universal mysql data return class, and code the requests in the classes where I need them, providing me with better flexibility regarding to data types that get returned.
Idea 4: ???

I hope you guys can help me out because I am banging my head against this problem-wall for a day now and I could not find a solution anywhere (but I am sure I probably just used the wrong search terms because this problem should not be an exotic one, right?)

Thanks alot in advance!!

[Paul Clapham]

One common way of solving that problem is to define a class which represents one row of the query, and then to return a List containing objects of that class (one object per row). You would do that if you were writing an application for a specific purpose which used a predictable list of queries.

However if you don't actually have an application to write and you just want to write a generalized database query processor, then you should still return a List of objects as above, but you wouldn't be able to design a specific class. Instead you would have to design a class which looked at the metadata for the query and stored that along with the data, so that other parts of the application could access the columns and understand what they were meant to be.

Quite often people want to write the first type of application, but they don't want to spend the up-front time to design the database and the classes which represent its tables, so they decide to write their own generalized query processor. This is usually a mistake because (a) it's much harder to write a good generalized query processor and (b) it's necessary to write extra code to interpret the generalized outputs in terms of the actual application.

I notice you haven't said a lot about the application you want to write. Does it have a specific purpose, or do you want to write yet another generalized query processor?

[Goler McBain]

First of all thanks for your reply.

Well the SQL queries I intend to run are very, very easy, something like to just return a whole specified table - for now. I want to
a) store information from a form class and
b) read information from a form by another class that processes the information and applies some sort of analysis to it and gives me back the result of the analysis.

So the idea was to write a class that receives the sql statement and returns the resultset(I guess thats the best way to put it). I would process the resultset where I requested it. Is there a way to return the resultset object itself so that I can use commands like .next() on the returned object? I guess that would be the perfect solution, because it would not even matter how easy/complex the sql statement is then?

[Winston Gutkowski]

Goler McBain wrote:I have sort of a logic problem with a class I wanted to develop. That class should get the Mysql statement as input, and return the Mysql data from the mysql database. And the returning part is where I have a problem.

Paul's quite right; you're far better off making classes for specific types of query. The fact is that your generalized "database data" class already exists: it's called ResultSet.

Most of the time, you'll be returning things like Customers or Accounts or Billing information, so it's easiest to drive it from the other end and just set up a tailored Customer, Account or BillingInfo class that knows what its ResultSet is supposed to look like.

Quite a lot of programmers actually use them to store frequently used queries on the particular type too, so that they can use more Java-like syntax, such as:

List<Customer> overdrawn = Customer.getSpongers(myDbConnection);

Obviously, if you have millions of views or joins it gets a bit more hairy, but it's still basically a management problem, not a programming one (although you might want to look at your design if you do).

Winston

[Winston Gutkowski]

Goler McBain wrote:So the idea was to write a class that receives the sql statement and returns the resultset(I guess thats the best way to put it).

Already exists. It's called PreparedStatement.

I would process the resultset where I requested it.

I wouldn't advise it. It's a sure recipe for code bloat.

Is there a way to return the resultset object itself so that I can use commands like .next() on the returned object? I guess that would be the perfect solution, because it would not even matter how easy/complex the sql statement is then?

Have a look at PreparedStatement.executeQuery().

Winston