aspose file tools*
The moose likes JSP and the fly likes Read data to the xml file from the database? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » JSP
Bookmark "Read data to the xml file from the database?" Watch "Read data to the xml file from the database?" New topic
Author

Read data to the xml file from the database?

Yashnoo lyo
Ranch Hand

Joined: Feb 17, 2003
Posts: 152
I want to read data to a XML file from the database and display it using XSL.But I don't know how to it if I use Sql Server or MySQL.Should I insert new data to the XML file if client post a new topic?Or insert the data to the database directly and don't throughout the XML?But if this is right and how to synchronize the XML and the database ?


When I face C# and Java I choose Java.
Simon Harvey
Ranch Hand

Joined: Jan 26, 2003
Posts: 79
Hi Yashnoo,
Well to answer your first question regarding how to work with databases and xml this is something I have just wresteled with. You have a couple of options at least:
SQL Server has an added package called SQLXML 3.0 and with that you can query the database with sql and then the data on the way out gets changed into xml on the fly. Now, I'm sure a lot of people think thats really good, or perhaps they are just using it "smarter" than me because I think its pretty crap really. Theres three modes that you can configure the server to use when outputting xml. I thought the first two were quite silly, and the last was just way to complex.
So, option two:
I'v just made a fairly simple component that queries the database as normal. When the data comes back, i just use the resultset that comes back to make the xml myself. Its really easy.
You use the ResultsetMetaData to find out the names of columns etc, and then the actual data can be written out as xml just as easily. To do that I suppose you could use the built in xml packages that come with java, but i couldnt figure them out so I made a few methods like:
createElement(String elementName)
closeElement()
createAttribute(String attributeName)
And so on.
And finally, you really need to think, now why would I want two sources of the same information flying around my application? I cant think why you would want that, so keep your data in the database, and query the database when you want information. Simple, then you dont need to give a second thought to synchronising sources because there will only be one - a much better design as well.
If you need any more advice, just give a post back. I cant claim to be all knowledgeable in this area (especially not with SQLXML) but I have just done the same stuff as your wanting and remember, if you use a java helper class you can use any database you please - you wont be using a Microsoft only feature to write xml - you'll be writing the xml yourself
Good luck
Simon
Yashnoo lyo
Ranch Hand

Joined: Feb 17, 2003
Posts: 152
Thank you Simon!
It will better than I query the database directly if I do that in this way?I means whether I can get better performance if I use XML?I want to decrease the times connecting the database because you know that connecting the database will take more time.I want to buffer the data that be used frequently and get them whenever I use.
More,
It maybe increase the pressure of web client.(webbrowser need to resolve XML file when the client browse the web page).But more important things is that it can decrease the pressure of the Web server if I use XML with database?Any good idea? :roll:
Simon Harvey
Ranch Hand

Joined: Jan 26, 2003
Posts: 79
Hi Yashnoo

It maybe increase the pressure of web client.(webbrowser need to resolve XML file when the client browse the web page).But more important things is that it can decrease the pressure of the Web server if I use XML with database?Any good idea?

I'm not 100% sure what you are needing here but I think I maybe know. In which case, you may find the following useful. I think most of it is correct but maybe someone else can chip in or correct me otherwise....
The reason why we want to make sure that all data is kept in the database, is to make sure we only have one store of data. It would be very very hard and complicated trying to keep lots of xml files and a relational database sync'd and in aggreement.
Now, you also seem interested in how the presentation to the client would work. You can definately offload the transformation proccess off to the client - This is like you give it the xslt doc, and the xml from the database, send them to the client and tell it to build the page itself! Thats fine.
The other way is the way that I cose to do it and that is to have the servlet container (tomcat) combine the xslt page and the xml and then send the result to the client.
The reason why I chose the later was because we have a reasonably ok web server. It can handle hits on jsp pages at somewhere between 500 - 1000 requests per second. Thats running a 1.3GHz Athalon (about the same as maybe a Intel P4: 1.8Ghz
500 hits a second is an awful lot unless you are someone like dell or ibm or something.
In our current site, I would be happy if we saw anything over 10 hits per second!
Just as another point.
Do you know how you are going to store the xslt information? I was looking at somehow storing them on the file system but then though that could be really really messy so I've put the xslt text into the database as well and so I get the xml out of the database and the xslt text straight out of the database and combine them on the tomcat server. It works well.
The only other thing I would say is that only get involved in xslt if you really need that sort of functionality and you'll alsways have someone available who can support the work that you need doing. For example, I have pretty much decided that for our company, XSLT is far to complicated. I am the only person who even knows what it is and i cant even use the stuff with out Altova's Stylevision project (its cool! It takes any html and turns it into the needed xslt in seconds!) Thats great but most people dont care what xml is and dont know how to make simple webpages never mind having to learn god knows how many languages just to make each single page. Hopefully xslt will get simpler. Or more realistically the tools for making it will get better!
Good luck my friend. Let me know how you get on!
Simon
Simon Harvey
Ranch Hand

Joined: Jan 26, 2003
Posts: 79
I forgot to mention something before:
Remember that with a database and jdbc, you can cache returned information. This could be the cache provided by jdbc in which you would have access to the relational information that came back from the database, or if you want to store the xml AFTER you have applied the xslt, then you could surely make your own cache (although that maybe complicated)
At the very least you can cache data from the database, which means you'll have reduced your requests to the db
Simon
Yashnoo lyo
Ranch Hand

Joined: Feb 17, 2003
Posts: 152
Thank you for your good reply
Sorry,My English is very pool.I understand your meaning most.I means I want to decrease the count that the client connecting to the database.The general flow is:1.client query;2.connect to the database;3.create xml file and read data from database to the xml file;4.display the xml file to client.That means it must connect to the database when client query per time(one time).Connecting database too many times if I do this in this way.So I want to cache the data in xml file that be used usually.Can I do this or you have any idea? :roll:
Yashnoo lyo
Ranch Hand

Joined: Feb 17, 2003
Posts: 152
Hi Simon:
I find that it become easy if I use JDOM to do this work.I write the logic in a servlet:
/////////////////////////the servlet code is:////////////////////////////////////////////
public void displayXml(){
SAXBuilder build=new SAXBuilder();
try{
Document doc=build.build(new FileInputStream("C:\\tomcat\\webapps\\ROOT\\xmlbook\\mysql.xml"));
Element root=doc.getRootElement();
List books=root.getChildren();
out.println("debug... ...");
while(rs.next()){
Element id=root.getChild("id");
id.setText(rs.getString("id"));
Element name=root.getChild("name");
name.setText(rs.getString("name"));
Element title=root.getChild("title");
title.setText(rs.getString("title"));
Element content=root.getChild("content");
content.setText(rs.getString("content"));
Element time=root.getChild("time");
time.setText(rs.getString("time"));
}
XMLOutputter xmlout=new XMLOutputter("",true,"GBK");
xmlout.output(doc,new FileOutputStream("C:\\tomcat\\webapps\\ROOT\\xmlbook\\mysqlnew.xml"));
}catch(Exception e){
out.println(e.toString());
System.out.println(e.toString());
}
}
//////////////////////////////index.html/////////////////////////////////////////////////////
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Enter the title of your XHTML document here</title>
<link style="html/css" href="style.css" rel="stylesheet"/>
</head>
<body>
<h2>welcome to here</h2>
<form action="mainpageurl" method="post">
<p><input type="submit" value="CliCk To EnTEr" /></p>
</form>
</body>
</html>
//////////////////////////mysql.xml////////////////////////////////////////////////////
<?xml version="1.0" encoding="ISO-8859-1"?>
<?xml-stylesheet href='mysqlxsl.xsl' type='text/xsl'?>
<rootpro>
<id/>
<name/>
<title/>
<content/>
<time/>
</rootpro>
/////////////////////////////End code///////////////////////////////////////////////////
I add a hyperlink to the xml in a web page.It will go to the xml page when user click the button.
But there is a problem puzzle me.The xml page will not display when the user first to come this page.The browser report error:"Can't display this page.The xml file must have a top element".It can display after I refresh this xml page more than ten times.Why?But the xml page already have a top element!~.I means the client must refresh the xml page more than ten times for display it.You know why? :roll:
Simon Harvey
Ranch Hand

Joined: Jan 26, 2003
Posts: 79
Well Done!
That exactly what I was meaning. Your English can't be that bad. I'm impressed!
OK, as for this other problem I can't say why this might happen. I don't fully understand what you are doing in the code because I don't know what some of the classes are for. You seem to be reading in one xml file and then outputting it as a new one?
I don't see how you are sending the xml to the client and I'm not sure where your database code is and what you do with it.
Hopefully this general advice will help though:
It seems that for whatever reason the xml is broken when the client goes to that page. Hard to say why, but we can definately try and find out whats going on.
I have a couple of suggestions:
Download netbeans (www.netbeans.org) if you need a debugger (you may already have one?). This debugger lets you step through the code AS THE SERVLET DOES IT!
This is incredibley powerful because rather than trying to find out whats gone wrong AFTER the problem occured, you can watch the problem occur AS IT HAPPENS. Imagine if detectives/police could always see what happend at the scene of the crime, rather than having to use clues to find out
The other option is you could write the xml to a file, as well as sending it to the client. This way, when it doesnt work, you can look at the xml that was sent and see what it looks like. That might help you see where your code is going wrong.
I hope that helps a bit more. Well done again on getting that code working!
Simon
Yashnoo lyo
Ranch Hand

Joined: Feb 17, 2003
Posts: 152
Hi Simon:
I found a more useful package and it is org.jdom.contrib.There is a useful class naming ResultSetBuilder.It is very easy to use it.Using the code:"
ResultSetBuilder rsbuilder=new ResultSetBuilder(rs);
doc=rsbuilder.build();"
I will get a output xml file:"
- <result>
- <entry>
<id>1</id>
<name>lyo</name>
<title>test</title>
<content>Testing successful?</content>
<time>2003</time>
</entry>
- <entry>
<id>2</id>
<name>li</name>
<title>content meaning</title>
<content>how many charactors</content>
<time>2003</time>
</entry>
- <entry>
<id>2</id>
<name>li</name>
<title>about the problem</title>
<content>Have anyone has idea?</content>
<time>2003</time>
</entry>
</result>
"
Now,I think I should describe the all procedure.I have four files:mysql.xml,mysqlnew.xml,index.html,mainPage.java(a servlet). The mysqlnew.xml is the output file after execute the servlet.
1.The first page client will see is the index.html:
/////////////////////index.html//////
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Enter the title of your XHTML document here</title>
<link style="html/css" href="style.css" rel="stylesheet"/>
</head>
<body>
<h2>welcome to here</h2>
<form action="mainpageurl" method="post">
<p><input type="submit" value="CliCk To EnTEr" /></p>
</form>
////////////////End index/////////////////
The page will send a request to the servlet after user click the button.Then the servlet will execute:
///////////////////////mainPage.java////////
public class mainPage extends HttpServlet{
private String sql="select * from problem";
private String url="jdbc:mysql://localhost:3306/test";
ResultSet rs=null;
Statement stm=null;
Connection conn=null;
PrintWriter out=null;
public void doPost(HttpServletRequest req,HttpServletResponse resp){
try{
out=resp.getWriter();
resp.setContentType("text/html");
}catch(IOException oe){
out.println(oe.toString());
}
try{
Class.forName("org.gjt.mm.mysql.Driver");
conn=DriverManager.getConnection(url);
stm=conn.createStatement();
rs=stm.executeQuery(sql);
this.displayXml();
rs.close();
stm.close();
conn.close();
resp.sendRedirect("mysqlnew.xml");<--The page will go to the output file:mysqlnew.xml
}catch(Exception se){
out.println(se.toString());
}
}
public void displayXml(){
SAXBuilder build=new SAXBuilder();
try{
ProcessingInstruction pi=new ProcessingInstruction("xml-stylesheet","type='text/xsl' href='C:\\tomcat\\webapps\\ROOT\\xmlbook\\mysqlxsl.xsl'");
Document doc=build.build(new FileInputStream("C:\\tomcat\\webapps\\ROOT\\xmlbook\\mysql.xml"));
Element root=doc.getRootElement();
List books=root.getChildren();
ResultSetBuilder rsbuilder=new ResultSetBuilder(rs);
doc=rsbuilder.build();
doc.addProcessingInstruction(pi);
XMLOutputter xmlout=new XMLOutputter("",true,"GBK");
xmlout.output(doc,new FileOutputStream("C:\\tomcat\\webapps\\ROOT\\xmlbook\\mysqlnew.xml"));
/////////////////////////////End servlet//////////////////////////////////////////////////////
The sentence:"resp.sendRedirect("mysqlnew.xml");" will redirect the page to the mysqlnew.xml which is the output file(It is xmlout.output(doc,new FileOutputStream("C:\\tomcat\\webapps\\ROOT\\xmlbook\\mysqlnew.xml")) ).The mysql.xml is the input file that JDOM require and the mysqlnew.xml is the output file after the mainPage servlet execute.It is the time that the problem occure:the page display a error page and report:"can't display xml page.Can't find xml input using stylesheet.please click the refresh button after correct the error or try again later.
xml document must has a top element.error occure when process resource ''http://localhost:3030/xmlbook/mysqlnew.xml' '". In the client browser address bar,it display the url:"http://localhost:3030/xmlbook/mysqlnew.xml".
I must click the refresh button more than ten times(even more) if I want to this page display.But I found that the mysqlnew.xml file is correct and it has a top element.
oh,my mysqlxsl.xsl is:
//////////////////////////////////mysqlxsl.xsl//////////////////////////////////////////////
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format">
<xsl utput method="html"/>
<xsl:template match="/">
<html>
<head>
<title>WelcomE To thE MainPagE</title>
</head>
<body>
<table border="1" bgcolor="#eecc00">
<tr>
<td>User Information</td>
<td><xsl:apply-templates select="result/entry"/></td>
</tr>
</table>
</body>
</html>
</xsl:template>
<xsl:template match="entry">
<li><xsl:value-of select="ID"/></li>
<li><xsl:value-of select="Name"/></li>
<li><xsl:value-of select="Title"/></li>
<li><xsl:value-of select="Content"/></li>
<li><xsl:value-of select="Time"/></li>
</xsl:template>
</xsl:stylesheet>
I want to transform the xml file using the xsl.
I use IntelIdea to debug several minites ago but there isn't any error.I will try NetBeans later. :roll:
Simon Harvey
Ranch Hand

Joined: Jan 26, 2003
Posts: 79
Hi there,
This is getting a little bit to much out of my experience. I am relatively new to this as well and I basically decided that xslt to was far more trouble than it was worth.
But here's a couple of thoughts:
1. If you want your application to be scalable, I wouldnt write the xml to a file at all. You have it in memory and xslt processors will allow you to hand the xml directly to them. I can't remember how to do that but ask a new question on the forum and let me know if you find out.
One possible way as I recal, is that xslt can take a load of sax events as its input. I think that might be an option but I'm not sure.
Perhaps even better than that though is that I'm sure you can give the xslt parser just some form of io stream. Again, i dont know the specifics, but I really really think you want to be getting away from taking your xml from the database, writing it onto the file system, just to read it back in again! That would be very slow, and what will you do when you want to server more than one client at a time? That would be hard to make sure that you don't overwrite a file thats being used and your hard disk will get battered neadlessly.
2. The other thing that occured to me is that in your servlet, you set the page content type to text/html, which is fine. But then you said that the address in the browser is .../.../.../my XML.xml
Why is the file an xml file? If you are putting it through a translation with xslt - to turn it into an html page, then what the client should eventually receive is the output of the transformation which is *HTML*
I can't remember what you do in your servlet, but what you want to do is hold the output from the tranformation in a variable (it will all be html if everything went well. Then you want to put that variable in the right place with a:
out.println(transformation_results);
The other important thing to remember is that you need to make sure that your xslt doesnt add another set of <html><header> etc etc tags, otherwise, if you also have that being printed in the servlet, then you will have 4 html elements instead of the two that's allowed
Keep me posted with your progress
Good luck. I'm sure you'll get there soon
Out of curiosity, are you absolutley sure that you need to use xslt. What is it that you are using it for. There are other, easier ways to get content/presentation seperation
Simon
Yashnoo lyo
Ranch Hand

Joined: Feb 17, 2003
Posts: 152
Hi Simon:
I understand your meanings.1:The transform protocol is wrong.You are right!I test it in the weblogic6.There is more important information in the weblogic console.The web page display:
//////////////////////////////////////////browser////////////////////////////////////////////////
"Error 500--Internal Server Error
From RFC 2068 Hypertext Transfer Protocol -- HTTP/1.1:
10.5.1 500 Internal Server Error
The server encountered an unexpected condition which prevented it from fulfilling the request."
/////////////////////////////////////////End/////////////////////////////////////////////////
And the console display error is:
///////////////////////////////////////////////////////////////////////////////////////////////
<2003-7-19 afternoon> <Error> <HTTP> <Servlet execution in servlet contex
t "WebAppServletContext(3385924,DefaultWebApp,/DefaultWebApp)" failed, java.net.
ProtocolException: Didn't meet stated Content-Length, wrote: '982' bytes instead
of stated: '0' bytes.
java.net.ProtocolException: Didn't meet stated Content-Length, wrote: '982' byte
s instead of stated: '0' bytes.
at weblogic.servlet.internal.ServletOutputStreamImpl.finish(ServletOutpu
tStreamImpl.java:413)
at weblogic.servlet.internal.ServletResponseImpl.send(ServletResponseImp
l.java:974)
at weblogic.servlet.internal.ServletRequestImpl.execute(ServletRequestIm
pl.java:1964)
at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:137)
at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:120)
////////////////////////////End weblogic exception////////////////////////////////////////
The xml page also can display after I refresh more than three times.But it display a text page instead of xml pattern(Note:I don't use xsl this time).In the browser:
1 lyo Testing successful? 2003-22-53-12 CST 2 li How many charactors 2003-6-29 23-01-31 CST 2 li there are many people. 2003-6-29 23-02-17 CST
The result indicate that the protocol in my servlet(text/html) is incorrect.You says it shouldn't read the data to a xml file from the database.Other than read the data to memory and transform them using XSLT.
Now,I have two method and I don't know which is best.
first, I download the xalan2.0:
I correct my servlet and using xslt transform xml data to the client's browser.
////////////////////////////////////mainPage.java/////////servlet/////////////////////////////
public void doPost(HttpServletRequest req,HttpServletResponse resp){
resp.setContentType("text/html");
Class.forName("org.gjt.mm.mysql.Driver");
conn=DriverManager.getConnection(url);
stm=conn.createStatement();
rs=stm.executeQuery(sql);
this.createXml();
rs.close();
stm.close();
conn.close();
this.transform(req,resp);
}
public void createXml(){
SAXBuilder build=new SAXBuilder();
Document doc=build.build(new FileInputStream("C:\\tomcat\\webapps\\ROOT\\xmlbook\\mysql.xml"));

Element root=doc.getRootElement();
List books=root.getChildren();
System.out.println("debug... ...");
ResultSetBuilder rsbuilder=new ResultSetBuilder(rs);
doc=rsbuilder.build();
XMLOutputter xmlout=new XMLOutputter("",true,"GBK");
xmlout.output(doc,new FileOutputStream("C:\\tomcat\\webapps\\ROOT\\xmlbook\\mysqlnew.xml"));
}
public void transform(HttpServletRequest re,HttpServletResponse res){
final String hostname="localhost"; //why?? private don't right?
final String filename="/xmlbook/mysqlnew.xml";
final String stylesheet="C:\\tomcat\\webapps\\ROOT\\xmlbook\\mysqlxsl.xsl";
final int portnumber=3030;
URL urlbook=new URL("http",hostname,portnumber,filename);
InputStream in=urlbook.openStream();
XSLTProcessor process=XSLTProcessorFactory.getProcessor();
process.process(new XSLTInputSource(in),
new XSLTInputSource(new FileInputStream(stylesheet)),
new XSLTResultTarget(res.getOutputStream()));
}
I delete all the exception for simple the code.The most important method is the transform method.I use it transform xml data to the client browser.But it remain has to read data to a xml file first and read it to client next.I come across a exception while I use this method.When I execute the servlet the Tomcat report:
//////////////////////////////error//////////////////////////////////////////////////
[Fatal Error] :1:1: Premature end of file.
javax.xml.transform.TransformerException: SAX Exception
//////////////////////////////////////////////////////////////////////////////////////////////
I debug it and the error happen when I execute the "process.process(new XSLTInputSource(in),"
The first method failed and I don't understand why?

The second:
I want to use a javabean to store all the data that read from the database.All the data are stored in memory as your say.
The procedure is:
1.Write a javabean and it has getter and setter method:
public class lyobean{
private String id;
private String name;
private String title;
... ..... .......
public void setId(String id){
this.id=id; }
public String getId(){
return this.id;}
....... ........ ......
2.Read the data to javabean from database:
while(is.next()){
lyobean lyo=new lyobean();
lyo.setId(rs.getString("id"));
lyo.setName(rs.getString("name"));
lyo.setTitle(rs.getTitle("title"));
........ .......... ...........
}
3.using the DOM to create a XML tree in memory and place all the javabean data to the xml:
Element product = document.createElement( "id" );
Element temp = document.createElement( "id" );
temp.appendChild( document.createTextNode( getId() ) );
product.appendChild( temp );
temp = document.createElement( "title" );
temp.appendChild( document.createTextNode( getTitle() )
product.appendChild( temp );
....... ......... .......... .......... ...........
4.Finally transform all the data that in memory to the client using "xalan"
(I write a xsl file before I transform it)
response.setContentType( "text/html" );
PrintWriter out = response.getWriter();
InputStream xslStream =getServletContext().getResourceAsStream("/bookstore.xsl" );
transform( messageDocument, xslStream, out );
private void transform( Document document,
InputStream xslStream, PrintWriter output )
{
try {
Source xmlSource = new DOMSource( document );
Source xslSource =new StreamSource( xslStream );
Result result = new StreamResult( output );
TransformerFactory transformerFactory =TransformerFactory.newInstance();
Transformer transformer =
transformerFactory.newTransformer( xslSourcetransformer.transform( xmlSource, result );
}
You think which is better?And I don't want to use DOM because it is more complex than JDOM.Any idea? :roll:
Yashnoo lyo
Ranch Hand

Joined: Feb 17, 2003
Posts: 152
Hi Simon:
This site's design is what I want to. http://expert.csdn.net You can login to try using the name:lyo password:53505 It will output a temp xml file after you post a new topic.I don't know whether it is the effect that you say.I think the site read data as temp xml from the database first and display it to client(using xsl).I can find the xml file from:"G:\Documents and Settings\Administrator\Local Settings\Temporary Internet Files" and this xml is exactly you have browsed.That means the site output xml files when user click a hyperlink or post a new topic.Can you tell me what I say is right?Do you know how does this web site do it?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Read data to the xml file from the database?