• Post Reply Bookmark Topic Watch Topic
  • New Topic

Best Way to Turn Query to String[][]  RSS feed

 
Al Johnston
Ranch Hand
Posts: 99
Flex Java Postgres Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I'm trying to build a socket that saves time when users query on any of our web sites. The way it works is to hold an array of data in memory until the user closes the browser (or migrates away). When the user first hits our site, we go through a series of checks (does the user have a message, has the user retrieved the message...). All of those are different pages so, behind the scenes I run a query and get the data the user is eventually going to see into an array. Then the array is held in memory in case the user does some other stuff and then comes back.

The point here is to try to make the delivery of information fast to the user. No need to do a query over and over again when the user hits a page that should show static ResultSet data.

But, this is where it breaks down for me...



Here is what I want to do in the most efficient way possible (We've gotten 450,000 hits in the last week so I want to optimize both the speed and the memory of the app in the best possible trade off)...

I want to store the results of this query in a String[][] (as well as others) in a Hashtable<Integer, String[][]> where a SessionID object singleton manages it all for me while the user is live. But, I don't know the best way to get the data into a String[][] object and I want to avoid looping ResultSet objects as much as possible...

Can someone help me determine the best way to get this query into a String[][] where:

String[0 through n] = CPA_Category.CategoryID and String [][0 through n] = CPACategory.CategoryName?

 
Vijitha Kumara
Bartender
Posts: 4001
42
Chrome Fedora Hibernate
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
But, I don't know the best way to get the data into a String[][] object and I want to avoid looping ResultSet objects as much as possible...

you should, at least for the first time you retrieve the records.
Can someone help me determine the best way to get this query into a String[][] where:
String[0 through n] = CPA_Category.CategoryID and String [][0 through n] = CPACategory.CategoryName?

I don't know the type of data structure/kind of data involved here. But is this related to different users' data you are trying to store or .... ?


 
Al Johnston
Ranch Hand
Posts: 99
Flex Java Postgres Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
For this query, it's fairly static. In other words, each pub has a limited number of iframes (most have one) and each iframe only has one category query. However, for the users, it's a different story. Each time the user completes an offer, I don't show it to him again. So the individual sessions will have varying degrees of difference in terms of the data that is returned. For instance, publisher A and B will have different category ResultSets but each user within those publishers will have the same category ResultSet. However, users within each publisher will each have different offer ResultSets based on what they have done in the past, the country they are coming from, their demo information, and a few other factors.

I'm trying not to over-complicate it but at the same time, make the speed of data presentation as fast as possible. The way the system works is to create a socket which a ColdFusion page calls. When it calls the socket, I open a connection and keep it alive. I then pass the ColdFusion page the requisite information based on which page is being displayed. In essence, I control that as well such that ColdFusion calls and says, <cfset url = ClientSocket.getData("sessionID", "userID", "userIP", "startpage"...)> Once that happens I pass back the page that CF should send the user to where there is a new call to my socket and at the same time I create all the queries and hold them in Hashtables as arrays of varying types. Then, each page that is accessed calls me again with some other instruction and I pass back the proper data in the form of array until the browser closes and I set everything to null and close the input/ouput streams.
 
Rob Spoor
Sheriff
Posts: 21117
85
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Go for a List<String[]> instead. You can use rs.getMetaData() to retrieve an instance of ResultSetMetaData, with which you can retrieve the number of columns and everything else you need. Then simply add a new String[] to the List<String[]>; that way you don't need to know the number of rows at the start.

What you may like even more is a List<Map<String,String>> - instead of a String[] where you have to guess which index matches a column, the Map uses the column names (from the ResultSetMetaData) as keys and the values from the ResultSet as the values.
 
Al Johnston
Ranch Hand
Posts: 99
Flex Java Postgres Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the help. You've spurred a few questions...

If I'm going to convert it to a String[][], it would seem that ResultSetMetaData does not help me. I already know the number of columns. What I don't know is the number of rows and I don't see a method there that gives me that. Can you help me see what I'm missing? However, I do understand your other suggestion but have a question about it too. First what I've done so far...



This will allow the CF developer to loop the array and find the category names one down from the categoryID. Then, if the user selects a category and wants to sort on it, the CF developer will send me that through the Socket and I'll send back the corresponding String[][] offerListArray. However, this is where your map suggestion seems to start making sense to me. To do that, why would I need a list? I could just retrieve the CategoryID (as my key) and the CategoryName (as my value) as so:



I like this solution for precisely what you stated, I now know exactly what I'm looking for if the CF developer calls me and says, "Give me all offers with Category (x)" Instead of the String[][] I could keep this Map in memory and could more efficiently find the values I'm being asked for (without looping later on).

But, I still have to convert this Map to a String[][] for consumption on the other end. So, now would I use an Iterator to do that and is that the most efficient means of handling this requirement. Also, if I've missed something (a very real possibility), please point it out.

Best,
Al
 
Rob Spoor
Sheriff
Posts: 21117
85
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I said List<String[]> - not List<String[][]>:
You can then convert this to a String[][] as follows:
Your two-dimensional array is quite unncessary in its current form - there is only one element, so a simple String would work just as fine.

The Map code I meant looks a lot like my above code:
Your Map code looks just fine as well though, as long as the category IDs will be unique; if not you will overwrite values.

One other solution you may want to explore is create a custom class for your categories:
This way, your ID and name are already available to your consumers without having to parse or convert anything.
 
Al Johnston
Ranch Hand
Posts: 99
Flex Java Postgres Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That right there is why I love JavaRanch. I posted here a few years ago when I started learning Java via Head First Books. Thanks for the clarification. This class is in fact class "Category". I'm going to modify the code to use a List and the getters and setters. Thanks so much!
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!