Little JSP help…

I was cleaning up the factory output stuff on wiretap, and I wanted to reorganize some of the data; the factories all output at the same time, so what I want to do is take this query

  SELECT  f.facility_oid, f.output_time, f.country, ...
   FROM  strat_factory_outputs f
   WHERE  output_time > ?
   ORDER  BY f.output_time DESC, f.facility_oid ASC

Generating

<factory-log>  <tick fid="4" time="2007-02-22 15:15:15.0" ... /><tick fid="7" time="2007-02-22 15:15:15.0" ... />  <tick fid="9" time="2007-02-22 15:15:15.0" ... />  ...
And split it into groupings by time:
<factory-log>  <tick time="2007-02-22 15:15:15.0">    <factory id="4" .../>    <factory id="7" .../>    <factory id="9" .../>  </tick>  <tick time="2007-02-22 15:00:15.0">    ...

And also (a summary version) which produces
<factory-log>
<tick time="2007-02-22 15:15:15.0" contributed="4,7,9"/>
<tick time="2007-02-22 15:00:15.0" contributed="4,9"/>
...

(without using a second query, I realize I could just use group_concat)

I pull the data with <sql:query var=’rows’> but I’m not especially familiar with array/map handling in jsp yet.

24 Comments

I had to look a bit to see what you meant by “”, quick question though: Why are you JSTL instead of just writing the Java?

I guess if I were doing it I would just use XOM (or other XML Library) and just run the Query through JDBC. I’m more comfortable with Java classes tho.

I would love to see the ERD for the database but that is more secret than Area 51 one suspects.

Fluth – Answer begins with “L” and ends with “aziness”. I’m not a Java programmer, haven’t had to be for years, never really got a real feel for the java mentality, which makes it difficult to advance in a language. Remember the caveat, I’m doing this to test the water. If we turn up some interesting leads out of this, I can take that to Playnet and maybe we can channel some resources into it – probably not my own tho.

Simon – maybe, maybe not, because at the end of the day there’s nothing special or fancy about our database… If anything, it would be shame that keeps it concealed :)

Been writing databases soooo long I was doing it when Pontius was still in flight school :/ I can’t help you with the Java/JScript for I am a disciple of Bill the Darkone. However you should be possible to use temporary tables etc without having to get into JScript to handle the data. An old trick I use is when testing such a solution is to bind it all in a transaction check the results and then commit the results once happy.

Do you have a development copy of the DB? I assume you must so test it all on there first. What are you using (I am assuming Oracle).

Hey simon, I’m pretty sure kfsone mentioned they use mssql.

The game databases are all currently MySQL; back in 2000 they were using Oracle, but they just weren’t pushing data hard or often enough to make Oracle practical, and they found that MySQL significantly better suited.

Myself, I’m a Postgres/Oracle guy, and I wanted to convert them when I got here :) We still have our Oracle licenses and two huge Sun boxes sitting in one of our machine rooms waiting to run a mirrored Oracle DB for us.

The game is still incredibly database light – its only stuff like this and CSR that start to challenge MySQL. We have some concepts in the pipeline for the next year that might tip that balance.

We do actually have a dedicated development game cluster, including database, as well as our beta and training clusters. The “wiretap” server actually has its own mirrored copy of the database so that none of my queries will impact performance elsewhere in the game cluster.

The data is actually already in the order I want it – grouped by event time – I just haven’t worked with JSP long enough to have tackled a nested loop problem like this before, and I thought some of my JSP-using readers might be eager to leap out and advise me on how best to do it :)

Well, I dunno how complicated you want stuff to be so maybe something like this.. ugly, but.. What the heck. I’m still practically a newbie with JSP/JSTL etc.. Since your data is ordered by the date..

(Include the taglib stuff only if you’ve not included it already on the jsp page, should be at the top of the page..)

If you know all this and are searching for an elegant solution.. sorry for wasting your time ;)

Ok, lets try that again. I hope you’re using the code plugin – if not, shoot me an email and I’ll reply with some stupid JSP/JSTL =)

(Include the taglib stuff only if you’ve not included it already on the jsp page, should be at the top of the page..)

If there’s no code visible.. It didn’t work.

I’m not familiar with JSP/JSTL .. but I know what you’re looking to do could easily be done in perl on the webserver side .. I did some dancing a while back with perl scripts that made themselves look like data sources for a javascript front end, but in fact, were database queries. Doubt this helps though :)

DARKKIS –

I’ve discovered that you cant use sideways carats and other code language on this site- it tries to interpret it, not display it. Use replacement characters and give a quick legend of the characters *should* mean. use text to describe what characters you would use. It wont display them in the legend either.

Well, yeah, the < and > characters are HTML demarcations, and some are allowed to be used.

What we need is to be able to use <pre> – that should keep the browsers or site software from trying to parse.

A preview function would also help. :)

Out of curiosity… a test of the preformatted tag…

This is inside a pre tag

this is inside a pre tag

Yeah, <pre> is ignored. There should have been an “also” in that second line.

Let’s see if this works. [] for smaller-than, larger-than.
[%@ taglib uri=”http://java.sun.com/jsp/jstl/core” prefix=”c” %]
[factory-log]
[c:set var=”prevtime” value=”0″/]
[c:forEach var=”data” items=”${sqlqueryname.rows}”]
[c:if test=”${prevtime!=data.output_time && prevtime!=0}”]
[/tick]
[/c:if]
[c:if test=”${prevtime!=data.output_time}”]
[tick time=”${data.output_time}”]
[c:set var=”prevtime” value=”${data.output_time}”/]
[/c:if]
[factory id=”${data.facility_oid}” …/]
[/c:forEach]
[/factory-log]

Like I said, not pretty.

Hehe, I’d already tried something like that, unfortunately the /tick and <tick> layout causes the Tomcat2 parser to barf :(

Thunder, a lot of the non-query stuff is generated by a variant of my dbframe script with “knows what to do” with a result set and some peripheral directives and turns them into xml and js output files :)

Tomcat2 ? =)

Try the c:out tag.

If the parser barfs on those, you could probably use the c:out as Darkkis suggests, or write the “lower-then” and “bigger-then” using their UTF-8 counterparts: > and <
so <tick> and </tick>

Not a friend of JSTL myself, or JSP for that matter, I usually use XML and XSL to create my HTML interfaces, or FreeMarker.

Salute!

You could use c:out as Darkkis suggests, or you might try to write the lower-then and bigger-then as their UTF-8 counter parts:
<tick> and </tick>

Not a friend of JSTL, or JSP for that matter, myself. I prefer XML/XSLT or FreeMarker to generate my HTML UIs.

Salute!

Sorry about the duplicate entry, I did not think it was being moderated, last time I wrote it was not, and I thought it was a problem with my entry formatting, so you can delete the duplicate :D.

S!

Ummm, that means my entry was just “eaten” by the parser… let’s try for the last time:
————-
You could use c:out as Darkkis suggests, or you might try to write the lower-then and bigger-then as their UTF-8 counter parts:
_ampelsand#60;tick_ampelsand#62; and _ampelsand#60;/tick_ampelsand#62;

Not a friend of JSTL, or JSP for that matter, myself. I prefer XML/XSLT or FreeMarker to generate my HTML UIs.

(Substitute _ampelsand with the real one)
Salute!
————-

Mi little utility BEview is getting useful (www.ampostata.org/BEview/BEview.zip). It displays citys, brigades on citys… everybody is asking me for more. It would help if you serve:

1.- facilities owner ONLY if it is different from their parent CP owner (ok, I know I can do it if I check all facilities in CPs with “contention” status “on”).

2.- Citys with AO

3.- Citys with EWS and/or different kind of EWS…

Even some crazy dudes are asking me about displaying pool on citys! LOL

Mi little utility BEview is getting useful (www.ampostata.org/BEview/BEview.zip). It displays citys, brigades on citys… everybody is asking me for more. It would help if you serve:

1.- facilities owner ONLY if it is different from their parent CP owner (ok, I know I can do it if I check all facilities in CPs with “contention” status “on”).

2.- Citys with AO

3.- Citys with EWS and/or different kind of EWS…

Even some crazy dudes are asking me about displaying pool on citys! LOL

BTW, do you know that “HC Status” has the same information as “HC Locations”?

None of (2) or (3) is currently stored in a database, so its not something I can expose without modifying the host code at the moment.

Yes, hc locations is basically a reduced version of hc status.

– Oliver

Leave a Reply

Name and email address are required. Your email address will not be published.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

You may use these HTML tags and attributes:

<a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <pre> <q cite=""> <s> <strike> <strong> 

%d bloggers like this: