How to use java to create an XML document from an SQL database?
1 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   Louise_Moorcock
Posted On:   Wednesday, August 7, 2002 03:55 AM

Hi, I'm a complete novice at XML and have only recently started programming in Java. I'm currently trying to develop a package in Java 1.1.8 which requires a set of very specifically formatted XML documents. These documents would need to be updated regularly by people with no knowledge of Java and I would like to make it as simple as possible. Since the data will already be in an SQL database, I thought it might be possible to generate the XML documents from the data using a small Java application, but I'm not too sure how to go about this or if this is even possible! Any help or pointers in the right direction would be very much appreciated. Louise    More>>

Hi,


I'm a complete novice at XML and have only recently started programming in Java.


I'm currently trying to develop a package in Java 1.1.8 which requires a set of very specifically formatted XML documents. These documents would need to be updated regularly by people with no knowledge of Java and I would like to make it as simple as possible.


Since the data will already be in an SQL database, I thought it might be possible to generate the XML documents from the data using a small Java application, but I'm not too sure how to go about this or if this is even possible! Any help or pointers in the right direction would be very much appreciated.


Louise

   <<Less

Re: How to use java to create an XML document from an SQL database?

Posted By:   Roeland_Lengers  
Posted On:   Wednesday, August 7, 2002 12:34 PM

Don't flame me for this code, I've written it 4 or more years ago. Anyway, what this piece of code does is open a connection to an odbc database (I used an MS-Access database at that time for testing). It then executes a query, reads in the resultset and then output the elements wrapped in nice XML-tags.

Hope this is clear enough.... Please ignore the stuff with the first letter of the name and the handling of email-address and generating the accompanying link to an image. That's all fluff.



Hope this helps




import java.sql.*;
import java.io.*;

public class WriteNamesXml{

String dsn ="jdbc:odbc:FinanceDB";
String depQuery;
String fnameQuery;

public WriteNamesXml(){

//Query to retrieve employees sorted by department
depQuery = "SELECT * from ListByDepartments";

//Query to retrieve employees sorted by first name
fnameQuery = "SELECT * from ListByFirstNames";
}

public static void main(String args[]) {
WriteNamesXml wnx = new WriteNamesXml();
wnx.writeFirstNames();
}

private void writeFirstNames(){

ResultSet rs;
Connection con;
Statement stmt;
FileWriter fw;

String previousLetter="";
String newLetter="";
String fname="";
String lname="";
String imagesrc="";
String email="";
String image="";
String department="";
String name="";
int counter=0;

try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}catch (java.lang.ClassNotFoundException e){
System.err.println("SQLException: " + e.getMessage());
}

try{
con = DriverManager.getConnection(dsn, "", "");
stmt = con.createStatement();

rs = stmt.executeQuery(fnameQuery);

fw = new FileWriter("ListByFirstName.xml");
fw.write("
");
fw.write("
");
fw.write("
");
fw.flush();

while(rs.next()){
//clear out the variables then refill them
lname="";
name="";
fname="";
email="";
imagesrc="";
lname=rs.getString("mensname");
fname=rs.getString("callname");
email=rs.getString("email");

if(email != null){
int i = email.indexOf("@");
imagesrc = "images/" + email.substring(0,i) + ".jpg";
}else{
imagesrc=null;
}

department=rs.getString("dep_description");
name = fname + " " + lname;
newLetter = fname.substring(0,1).toUpperCase();


//don't want to close page tag first time through
if(counter==0){
previousLetter = newLetter;
fw.write(" " + newLetter + "
");
}

if(!newLetter.equalsIgnoreCase(previousLetter)){
fw.write("


");
fw.write(" " + newLetter + "
");
fw.flush();
//new letter is now old letter
previousLetter = newLetter;
}

fw.write("
");
fw.write(" " + name + "
");
fw.write(" " + email + "
");
fw.write(" " + imagesrc + "
");
fw.write(" " + department + "
");
fw.write("

");
fw.flush();
counter ++;
}

fw.write("

");
fw.write("
");
fw.flush();
fw.close();

}catch (java.sql.SQLException ex){
System.err.print("SQL Exception: ");
System.err.println(ex.getMessage());
}catch(java.io.IOException ie){
System.err.println(ie);
}catch(Exception exc){
System.err.println(exc);
}
}
}
About | Sitemap | Contact