RFactorPopulator.java [tools/RFactorPopulator/src/rfactorpopulator] Revision: default Date:
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package rfactorpopulator;
import java.io.IOException;
import java.net.URL;
import java.net.URLConnection;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import org.w3c.dom.DOMException;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;
/**
*
* @author wlloyd
*/
public class RFactorPopulator {
private static String confString = "jdbc:postgresql://csip.engr.colostate.edu:5435/r2gis";
private static String error_msg;
private static Connection conn;
private static Statement statement;
/**
* @param args the command line arguments
*/
public static void main(String[] args) throws Exception {
String rFactor;
conn = getConnection();
statement = conn.createStatement();
String gisQuery="SELECT m.co_fips, r2_path, r2_name, m.ei_rang, r_factor FROM map_climates AS m WHERE r_factor is null order by m.co_fips;";
ResultSet results = statement.executeQuery(gisQuery);
while(results.next()){
String r2path = escapeSingleQuotes(results.getString("r2_path"));
String r2name = escapeSingleQuotes(results.getString("r2_name"));
String cofips = results.getString("co_fips");
String eirang = results.getString("ei_rang");
String urlName = getR2GISFileURL( results.getString("r2_path"), results.getString("r2_name") );
rFactor=getAndProcessXml(urlName);
String updateSql = "";
if (!rFactor.equals("-1"))
if (eirang == null)
updateSql = "UPDATE map_climates SET r_factor=" + rFactor + " WHERE " +
"co_fips = '" + cofips + "' AND " +
"ei_rang is null AND " +
"r2_path = '" + r2path + "' AND " +
"r2_name = '" + r2name + "';";
else
updateSql = "UPDATE map_climates SET r_factor=" + rFactor + " WHERE " +
"co_fips = '" + cofips + "' AND " +
"ei_rang = '" + eirang + "' AND " +
"r2_path = '" + r2path + "' AND " +
"r2_name = '" + r2name + "';";
System.out.println(updateSql);
}
}
public static synchronized Connection getConnection() throws Exception {
Connection ret_val = null;
if(confString == null || confString.isEmpty())
{
System.out.println("Connection string not provided. Please configure wqm.db configuration parameter.");
throw new Exception("Unable to connect to r2gis database. Please check the connection string configuration parameter");
}
ret_val = DriverManager.getConnection(confString, "postgres", "postgres");
return ret_val;
}
public static String getDatabaseHostname( ) throws Exception{
String ret_val = new URL(confString.replace("jdbc:postgresql", "http")).getHost();
return ret_val;
}
public static String getR2GISFileURL( String path, String name ) throws Exception{
return ( (("http://" + getDatabaseHostname() + ":81/r2/" + path + "/" + name + ".xml")).replace(" ","%20")).replace("\\","/");
}
public static String escapeSingleQuotes(String text)
{
String sFull = "";
sFull = text.replace("'", "''");
return sFull;
}
private static String getAndProcessXml(String url){
String ret_val = null;
try{
DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
DocumentBuilder db = dbf.newDocumentBuilder();
URL Url = new URL(url);
URLConnection urlConnection = Url.openConnection();
Document document = db.parse(urlConnection.getInputStream());
document.getDocumentElement().normalize();
Element root=document.getDocumentElement();
NodeList nList= document.getElementsByTagName("Flt");
for (int temp = 0; temp < nList.getLength(); temp++){
Node node = nList.item(temp);
if (node.getNodeType() == Node.ELEMENT_NODE){
Element eElement = (Element) node;
String Name=eElement.getElementsByTagName("Name").item(0).getTextContent();
if(Name.equals("R_FACTOR")){
ret_val = eElement.getElementsByTagName("Data").item(0).getTextContent();
}
}
}
}
catch( ParserConfigurationException | IOException | SAXException | DOMException ex ){
error_msg = "Cannot parse the returned XML: " + ex.getMessage();
System.err.println(error_msg );
return "-1";
}
return ret_val;
}
}