package collectdata;
import java.net.*;
import java.sql.*;
import java.util.List;
//import java.util.Scanner;
import org.dom4j.io.SAXReader;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Node;
public class DataUpload {
public String uploadData()
{
URL myURL = null;
SAXReader xmlReader = new SAXReader();
/*connecting to the database*/
Connection conn = null;
try
{
System.out.println("Connecting to Database");
String userName="root";
String password = "manutd";
String url = "jdbc:mysql://localhost/surya";
//System.out.println(userName+password);
Class.forName ("com.mysql.jdbc.Driver").newInstance ();
conn = DriverManager.getConnection (url, userName, password);
System.out.println ("Database connection established");
System.out.println("Creating Language Table\n");
Statement lang=conn.createStatement();
lang.executeUpdate ("DROP TABLE IF EXISTS language;");
lang.execute("CREATE TABLE language (lang_code VARCHAR(10) NOT NULL,PRIMARY KEY(lang_code),languages VARCHAR(15));");
lang.execute("insert into language (lang_code,languages) values ('AR','Arabic'),('EN','English'),('FR','French'),('ES','Spanish'),('RU','Russian'),('ZH','Chinese');");
System.out.println("Language Table Created");
}
catch (Exception e)
{
System.err.println (e+"Cannot connect to database server");
}
/****************************************************************************************************************************************/
String addr = "http://www.fao.org/countryprofiles/geoinfo/ws/allCountries/EN/";
try
{
myURL = new URL(addr);
}
catch (MalformedURLException e)
{
e.printStackTrace();
}
Document feed = null;
try
{
feed = xmlReader.read(myURL);
System.out.println("Entered");
}
catch (DocumentException e)
{
e.printStackTrace();
}
String xpathExpression = "/Data/self_governing";
// Get the list of nodes on given xPath
List<? extends Node> nodes = feed.selectNodes(xpathExpression);
Statement s = null;
try
{
System.out.println("Creating country table" );
s = conn.createStatement ();
s.executeUpdate ("DROP TABLE IF EXISTS country_details");
s.executeUpdate ("CREATE TABLE country_details(codeISO3 VARCHAR(40) NOT NULL,PRIMARY KEY(codeISO3),codeISO2 VARCHAR(40),Full_name VARCHAR(50),Off_Name VARCHAR(50),Short_Name VARCHAR(50),FAO_Members VARCHAR(50),codeUN VARCHAR(10),codeFAOTERM VARCHAR(10),codeAGROVOC VARCHAR(10),codeFAOSTAT VARCHAR(10),codeUNDP VARCHAR(10),codeGAUL VARCHAR(10),codeDBPediaID VARCHAR(50),codeURI VARCHAR(50),border_countries VARCHAR(60) DEFAULT '',MaxLat FLOAT,MinLat FLOAT,MaxLong FLOAT,MinLong FLOAT,capital VARCHAR(60),population VARCHAR(10),area VARCHAR(10),HDI VARCHAR(10),GDP VARCHAR(10));");
System.out.println("Country table created");
//System.out.println(s+" Created\n");
}
catch (SQLException e1)
{
// TODO Auto-generated catch block
e1.printStackTrace();
System.out.println("Not created\n");
}
int y=1;
for (Node node : nodes)
{
Node codeISO3= node.selectSingleNode("codeISO3");
Node codeISO2 = node.selectSingleNode("codeISO2");
Node off_name = node.selectSingleNode("nameOfficialEN");
Node shortname = node.selectSingleNode("nameShortEN");
Node namelist = node.selectSingleNode("nameListEN");
Node fao = node.selectSingleNode("FAO_MEMBERS");
try
{
s=conn.createStatement();
System.out.println("Going for the "+y+"country\n");
System.out.println(codeISO3.getText());
System.out.println("INSERT INTO country_details VALUES ('"+codeISO3.getText()+"','"+codeISO2.getText()+"','"+off_name.getText()+"','"+shortname.getText()+"','"+namelist.getText()+"','"+fao.getText()+"');");
int count = s.executeUpdate ("INSERT INTO country_details (codeISO3,codeISO2,Full_name,Off_Name,Short_Name,FAO_Members) VALUES ('"+codeISO3.getText()+"','"+codeISO2.getText()+"','"+off_name.getText()+"','"+shortname.getText()+"','"+namelist.getText()+"','"+fao.getText()+"');");
System.out.println (count + " rows were inserted");
y++;
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
try
{
s.close ();
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
String temp="Updated";
return temp;
}
}
LinkBack URL
About LinkBacks
Problem with my jsp code .Need Help Please!!!
Reply With Quote
Related threads:
