• 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

How to handle resultset without knowing column names?  RSS feed

 
Ranch Hand
Posts: 96
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How should I go about(or is it even possible?) running a SQL query based on the id ( I have a SQL query in the table which I plan to run and an id  associated with it). I want to return the sql query result in a JSON response
but here's a problem:

Each of the SQL query associated with an id column in the table is different and will return different columns.

Since, there can be 40-50 SQL queries, I am planning to write only one webservice and return JSON response.

In a typical webservice, whenever I have written GET based web service call, I have done something like the following and displayed it in JSON format using Objectmapper, jackson api (for example) :

 

But since, the SQL query is going to be different based on the id, how would I know what would go inside while(rs.next()) { } since I am not aware of the column names and column names are dependent on the type of SQL query (this is dependent on the id) ?

Here's how I attempted to start this this:

My controller class:

   


Here's my DAO Interface:





 
And, here's how my implementation of DAO interface looks like :
 
 


Let me know if I can answer any questions. Thanks !
 
Saloon Keeper
Posts: 20510
115
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can access resultset values by column number, not just by column name. NOTE THAT column numbers start with 1, and not 0!

Also, you can obtain the resultset's metadata which will allow you to determine how many columns are in each row and their names. In the event that the query has a computed value, the database will have synthesized a column name. Or you can make your SQL query return alias column names using the "AS" option like so:

SELECT acctno AS account_number, amt_paid / avg(amg_paid) AS average_amount_paid_ratio ...

 
Jack Tauson
Ranch Hand
Posts: 96
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:You can access resultset values by column number, not just by column name. NOTE THAT column numbers start with 1, and not 0!

Also, you can obtain the resultset's metadata which will allow you to determine how many columns are in each row and their names. In the event that the query has a computed value, the database will have synthesized a column name. Or you can make your SQL query return alias column names using the "AS" option like so:

SELECT acctno AS account_number, amt_paid / avg(amg_paid) AS average_amount_paid_ratio ...



Thanks for your response. One more question.

So, based on your explanation, it looks like I could get a number of column and names. If I have to display the response in JSON, could you tell me how would I go about creating objects (a separate class where I would have getter and setters and I could set the column names)just like I've been doing in this step using  EmployeeList empList = new EmployeeList(); :

 
Sheriff
Posts: 13174
219
Android Debian Eclipse IDE IntelliJ IDE Java Linux Mac Spring Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why would you name an object "EmployeeList" if it only represents one employee?

There are several ways to go about this but here are the main problems you have:

1. Figuring out which columns are included in the query and the order they will be in
2. Mapping the query columns to the appropriate setter() method on the object

For #1, you'll need metadata or you'll need to parse the text between "SELECT" and "FROM" of the query

For #2, for maximum flexibility, you'll have to use the Reflection API. Another option, although more tedious, would be to specify a "mapper" object for each query. That means you would potentially have to define as many mapper objects as you have queries. A mapper object will essentially have for its main logic whatever code you have in your while (rs.next()) loop.
 
Tim Holloway
Saloon Keeper
Posts: 20510
115
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
While Junilu has a point (EmployeeListItem would be a better name), there are basically 2 ways to do that. One is really brutal and that's to construct a Class completely in Java code using the Class meta-methods and their friends and relatives. The other is to construct Java source code (well, you could construct raw bytecodes, but that's harder to debug). and run the code through the Java compiler, then use a classloader to cause the current runtime to adopt this new class.

OR you could simply forget about making custom classes and return a Map, which is what most people do. It's a lot less work.
 
Jack Tauson
Ranch Hand
Posts: 96
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:While Junilu has a point (EmployeeListItem would be a better name), there are basically 2 ways to do that. One is really brutal and that's to construct a Class completely in Java code using the Class meta-methods and their friends and relatives. The other is to construct Java source code (well, you could construct raw bytecodes, but that's harder to debug). and run the code through the Java compiler, then use a classloader to cause the current runtime to adopt this new class.

OR you could simply forget about making custom classes and return a Map, which is what most people do. It's a lot less work.



Could you please elaborate on this OR you could simply forget about making custom classes and return a Map, which is what most people do. It's a lot less work?
 
Jack Tauson
Ranch Hand
Posts: 96
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Junilu Lacar wrote:Why would you name an object "EmployeeList" if it only represents one employee?

There are several ways to go about this but here are the main problems you have:

1. Figuring out which columns are included in the query and the order they will be in
2. Mapping the query columns to the appropriate setter() method on the object

For #1, you'll need metadata or you'll need to parse the text between "SELECT" and "FROM" of the query

For #2, for maximum flexibility, you'll have to use the Reflection API. Another option, although more tedious, would be to specify a "mapper" object for each query. That means you would potentially have to define as many mapper objects as you have queries. A mapper object will essentially have for its main logic whatever code you have in your while (rs.next()) loop.



Thanks. #1 : I was able to get the name of columns using ResultSetMetaData interface.


For #2:

Could you please elaborate on a) Reflection API usage here  and the b) mapper object technique you mentioned.

                 
 
Rancher
Posts: 3931
45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'd be surprised if there wasn't already some package that can do this for you.
 
Tim Holloway
Saloon Keeper
Posts: 20510
115
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm not sure, but I think that Junilu was expecting that you had ready-made class definitions and you just wanted to construct instances of those classes and have your generic code populate their values. Like ORMs do and JAXB does. For example, the Apache Digester.

That's not trivial, but there are things to make it easier. You can use the Apache beanutils to leverage Java's introspection services. All the cool apps do that - Tomcat's configuration compiler, popular JPA implementations, and so forth.

However, if you're hoping to build up a custom class field by field on the fly, that's much messier. Aside from everything else, Java's internal security mechanisms deliberately discourage rogue apps from "sabotaging" internal class structure,

The Map option is much easier. Basically, you construct a Map<String, Object>, then iterate the metadata of the resultset. For each returned column, put() a name/value pair into the Map, where the name is the column name and the value is the object returned by doing a "get" value for that column from the ResultSet.
 
Jack Tauson
Ranch Hand
Posts: 96
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:I'm not sure, but I think that Junilu was expecting that you had ready-made class definitions and you just wanted to construct instances of those classes and have your generic code populate their values. Like ORMs do and JAXB does. For example, the Apache Digester.

That's not trivial, but there are things to make it easier. You can use the Apache beanutils to leverage Java's introspection services. All the cool apps do that - Tomcat's configuration compiler, popular JPA implementations, and so forth.

However, if you're hoping to build up a custom class field by field on the fly, that's much messier. Aside from everything else, Java's internal security mechanisms deliberately discourage rogue apps from "sabotaging" internal class structure,

The Map option is much easier. Basically, you construct a Map<String, Object>, then iterate the metadata of the resultset. For each returned column, put() a name/value pair into the Map, where the name is the column name and the value is the object returned by doing a "get" value for that column from the ResultSet.




Thanks for elaborating. I would like to clarify more on what I am looking for :

Let's take a look at the DAO implementation (GetSQLDaoImpl) of the interface (GetSQLDAO) as shown in my first post above

Inside the EmployeeList class, I've the getter and setters defined, which I am making use of in the try block, as shown below :


Using ResultSetMetaData interface, I was able to get the column names and the type of column in the following manner :



Now, inside the above try block, inside while(rsNew.next()){} , depending upon the column name and type (VARCHAR2 or DATE or NUMBER),

1) I was thinking of creating setter and getter methods inside the EmployeeList class (if this sounds like a good approach and not a messier approach). But I am still finding it difficult to understand, how would I be defining
something like empList.setEmpName(rs.getString("EMPNAME")); or empList.setEmpId(rs.getInt("EMPID")); dynamically.

Please let me know if I can answer more questions. Thanks !

 
 
Jack Tauson
Ranch Hand
Posts: 96
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:I'd be surprised if there wasn't already some package that can do this for you.



Could you please suggest which package would suit best based on the problem description given above? Thanks !
 
Dave Tolls
Rancher
Posts: 3931
45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Not off the top of my head, but there must be something on github or similar.
Do a search on Java ResultSet to JSON, or something like that.
 
Jack Tauson
Ranch Hand
Posts: 96
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:Not off the top of my head, but there must be something on github or similar.
Do a search on Java ResultSet to JSON, or something like that.



Great. I used the JSONArray approach to convert the Resultset into a JSON as mentioned here :

https://stackoverflow.com/questions/6514876/most-efficient-conversion-of-resultset-to-json


And I don't even need to deal with using Reflection API stuff for generating getter and setter methods at runtime. Were you also referring to something similar package ? Also, any problems with the above approach?
 
Dave Tolls
Rancher
Posts: 3931
45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, that's a long thread there that probably covers most things we could cover here.
Depends whether you've taken all the suggestions on-board (though, as one of the replies says, don't worry too much about performance or memory unless you're fairly sure you'll hit an issue).
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!