import java.io.*;
import java.sql.*;
import javax.xml.parsers.*;
import javax.xml.transform.*;
import javax.xml.transform.dom.*;
import javax.xml.transform.stream.*;
import org.w3c.dom.*;

public class AndmedValja{
  //linkide puu
  static void linkidepuu(Document d, Element ljuur){
    try {
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   Connection cn=DriverManager.getConnection("jdbc:odbc:baas1", "", "");
   Statement st=cn.createStatement();
   ResultSet link=st.executeQuery("SELECT * FROM Lingid");
   while(link.next()){
	   Element l=d.createElement("link");
	    ljuur.appendChild(l);
       Element l_id=d.createElement("lingi_id");
        l_id.appendChild(d.createTextNode(link.getString("ID")));
        l.appendChild(l_id);
       Element name=d.createElement("name");
        name.appendChild(d.createTextNode(link.getString("Link")));
        l.appendChild(name);
       Element url=d.createElement("url");
        url.appendChild(d.createTextNode(link.getString("aadress")));
        l.appendChild(url);
   }
   } catch(Exception e) {
     System.out.println("Viga!: "+e.getMessage());
     e.printStackTrace();
   }
  }
  //hinnete puu
  static void hinnetepuu(Document d, Element hjuur){
   try {
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   Connection cn=DriverManager.getConnection("jdbc:odbc:baas1", "", "");
   Statement st=cn.createStatement();
   ResultSet hinne=st.executeQuery("SELECT * FROM hinded");
   while(hinne.next()){
	   Element h=d.createElement("hinne");
	    hjuur.appendChild(h);
       Element h_id=d.createElement("hinde_id");
        h_id.appendChild(d.createTextNode(hinne.getString("id")));
        h.appendChild(h_id);
       Element l_id=d.createElement("lingi_id");
        l_id.appendChild(d.createTextNode(hinne.getString("lingi_id")));
        h.appendChild(l_id);
       Element hin=d.createElement("hinne");
        hin.appendChild(d.createTextNode(hinne.getString("hinne")));
        h.appendChild(hin);
   }
   } catch(Exception e) {
     System.out.println("Viga!: "+e.getMessage());
     e.printStackTrace();
   }
  }
  
  public static void main(String argumendid[]) throws Exception{
   Document d=DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument();
    Element juur=d.createElement("lingikogu");
    Element ljuur=d.createElement("lingid");
    Element hjuur=d.createElement("hinded");		
    d.appendChild(juur);
     juur.appendChild(ljuur);
     juur.appendChild(hjuur);
   linkidepuu(d, ljuur);
   hinnetepuu(d, hjuur);
    Transformer t=TransformerFactory.newInstance().newTransformer(); 
    t.transform(new DOMSource(d), new StreamResult(System.out));
  }
}