aspose file tools*
The moose likes JDBC and the fly likes How to pretty print ResultSet in a text table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to pretty print ResultSet in a text table" Watch "How to pretty print ResultSet in a text table" New topic
Author

How to pretty print ResultSet in a text table

Marco Ehrentreich
best scout
Bartender

Joined: Mar 07, 2007
Posts: 1282

Hi folks,

I have an application which executes SQL statements on different databases and I want to send the returned results via email. Everything works fine so far and my only "problem" is how I can transform the returned ResultSets to nice looking text tables which I can include into the emails. Of course this is not a very hard task but I'm pretty sure there are some ready-to-use library routines which can do the work for me I know this is not directly related to JDBC but I guess it's often a requirement in this context.

And I don't need very fancy table designs. I'd just like to have a simple output in table form similar to that of the console clients like mysql or psql!

Thanks in advance!


Marco
Leandro Coutinho
Ranch Hand

Joined: Mar 04, 2009
Posts: 417
this is simple to do. just iterate like

<%
out.println("<table>");
for(...)
out.println("<tr><td>.."+obj.getFieldOne() + "</td></tr>");
out.println("</table>");
%>

I don't know if jsf, struts or other frameworks have this feature. You can search on google =D.

But you are right, it is used a lot. I think I'll create a method in my utility class. :]
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18672
    
    8

I think Leandro is on the right track. You should send your e-mail as HTML and not as text. That's because whoever reads your e-mail is likely to be using a proportional font, where "W" is wider than "I". (Not because they chose to do that, but because it's the normal and default way to read text in an e-mail.) Your MySql console app writes to the console, which uses a fixed-width font where all the characters are the same size.
Marco Ehrentreich
best scout
Bartender

Joined: Mar 07, 2007
Posts: 1282

Thanks, Paul and Leandro!

HTML would indeed be a nice and easy solution but probably I can't use HTML to prevent that these mails get easier caught by spam filters!

I'd still prefer to use plain text and because you are of course right that it looks stupid in mail clients without fixed width fonts, I think I'm going to attach the result as a text file. So I'm still looking for a solution to create the said text tables. And perhaps I should mention that I don't know before how the database tables look like so the text tables have to be created dynamically.


Marco
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18672
    
    8

Marco Ehrentreich wrote:HTML would indeed be a nice and easy solution but probably I can't use HTML to prevent that these mails get easier caught by spam filters!

I'd still prefer to use plain text and because you are of course right that it looks stupid in mail clients without fixed width fonts, I think I'm going to attach the result as a text file. So I'm still looking for a solution to create the said text tables. And perhaps I should mention that I don't know before how the database tables look like so the text tables have to be created dynamically.

And some e-mail clients are bad at rendering HTML or just plain prejudiced against it.

Have a look at java.sql.ResultSetMetaData (I think I spelled that right).
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

I can't use HTML to prevent that these mails get easier caught by spam filters!
Would they?
If your HTML only contains formatting tags, and no URLs (and no spam), they should pass.
Creating dynamical formatting in plain text or in html is comparable complex.


OCUP UML fundamental and ITIL foundation
youtube channel
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

And some e-mail clients are bad at rendering HTML or just plain prejudiced against it.
Yes that's true. That is a valid argument.
Although most graphical email readers support a simple html table.

It will be hard to meet everyone's situation.
If you use plain text, and the font is disproportional, you have problems.
If you use HTML, and the browser does not like it, you have problems.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18672
    
    8

I think it may be true that spam filters put a small negative black mark against HTML, but usually there needs to be more spammy behaviour to make them reject a message entirely.

Although I don't know why this is. Maybe a couple of years ago spam was more likely to include HTML, but most of what I get in my junk folder these days is plain text messages with a link inviting me to click here.
Leandro Coutinho
Ranch Hand

Joined: Mar 04, 2009
Posts: 417
hello!

for plain text you can use the class Formatter.
eg:

Formatter f = new Formatter();
out.print(f.format("%20s", "Leandro"));
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to pretty print ResultSet in a text table