Javaz

/*
 * 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 java2;

import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Result;
import javax.xml.transform.Source;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerConfigurationException;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import org.w3c.dom.Document;
import org.w3c.dom.Element;

/**
 *
 * @author Gregersen
 */
public class Opgave1 {

    static Connection conn = null;
    static ResultSet rs = null;

     public static void main(String[] args) throws SQLException, ParserConfigurationException, TransformerConfigurationException, TransformerException{

         System.out.println("\nOutputting Query Results \n\n" + sqlToConsole());

         sqlToXML();

     }

    public static Connection getConnection()
    {
        if(conn != null) return conn;

        return getConnection("AdventureWorks2014", "sa", "Bibi1ana");

    }

    private static Connection getConnection(String dbName, String username, String password)
    {
        try
        {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            conn = DriverManager.getConnection("jdbc:sqlserver://localhost;databaseName=" + dbName + ";user=" + username + ";password=" + password + ";");
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }

        return conn;
    }

    private static boolean closeConnection() throws SQLException
    {
        try
        {
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        }
        catch(SQLException ex)
        {
            ex.printStackTrace();
        }

        return conn.isClosed();
    }

    private static ResultSet getResultSet(String sqlQuery) throws SQLException
    {
        conn = getConnection();
        Statement stmt = null;

        if(!conn.isClosed())
        {
            System.out.println("Database connection open.");
        }

        try
        {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sqlQuery);
        }
        catch(SQLException ex)
        {
            System.out.println("An error occured while getting results: " + ex.getMessage());
        }

        return rs;

    }

    private static void sqlToXML() throws SQLException, ParserConfigurationException, TransformerConfigurationException, TransformerException
    {
        try
        {
             Document doc = convertToXML(getResultSet("Select ProductID, Name, ProductNumber, Color FROM Production.Product WHERE Color = 'Black' ORDER BY ProductID ASC"));

             printXML(doc, System.out);

             Transformer transformer = TransformerFactory.newInstance().newTransformer();
             Result output = new StreamResult(new File("XMLOutput.xml"));
             Source input = new DOMSource(doc);

             transformer.transform(input, output);

             System.out.println("XML saved to file succesfully.");
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            if (closeConnection())
            {
                System.out.println("Database connection closed.");
            }
            else
            {
                System.out.println("Failed to close database connection.");
            }
        }



    }

    private static String sqlToConsole() throws SQLException
    {
        String output = "";

        try{
            ResultSet rs =  getResultSet("SELECT BusinessEntityID, PersonType, FirstName, LastName FROM Person.Person WHERE FirstName='Michael' ORDER BY BusinessEntityID ASC");

            System.out.println("Retrieving database results.");

            if (!rs.next())
            {
                return "No records found";
            }
            else
            {
                do
                {
                    int businessID = rs.getInt("BusinessEntityID");
                    String personType = rs.getString("PersonType");
                    String firstName = rs.getString("FirstName");
                    String lastName = rs.getString("LastName");

                    output += businessID + "\t" + personType + "\t" + firstName + " " + lastName + "\n";
                }
                while(rs.next());

            }

            rs.close();

        }
        catch(Exception e)
        {
            return "An error occured while retrieving data: " + e.getMessage();
        }
        finally
        {
            if (closeConnection())
            {
                System.out.println("Database connection closed.");
                conn = null;
            }
            else
            {
                System.out.println("Failed to close database connection.");
            }
        }

        return output;
    }

    public static void printXML(Document doc, OutputStream out) throws IOException, TransformerException
    {
        System.out.println("\nOutputting results in XML format\n");

        TransformerFactory transformerFactory = TransformerFactory.newInstance();
        Transformer transformer = transformerFactory.newTransformer();
        transformer.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "no");
        transformer.setOutputProperty(OutputKeys.METHOD, "xml");
        transformer.setOutputProperty(OutputKeys.INDENT, "yes");
        transformer.setOutputProperty(OutputKeys.ENCODING, "UTF-8");
        transformer.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "4");

        transformer.transform(new DOMSource(doc), new StreamResult(new OutputStreamWriter(out, "UTF-8")));

    }

    public static Document convertToXML(ResultSet rs) throws ParserConfigurationException, SQLException
    {
        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        DocumentBuilder builder = factory.newDocumentBuilder();
        Document doc = builder.newDocument();

        Element results = doc.createElement("Results");
        doc.appendChild(results);

        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();

        while(rs.next())
        {
            Element row = doc.createElement("Row");
            results.appendChild(row);

            for(int i = 1; i <= columnCount; i++)
            {
                String columnName = rsmd.getColumnName(i);
                Object value = rs.getObject(i);

                Element node = doc.createElement(columnName);
                node.appendChild(doc.createTextNode(value.toString()));
                row.appendChild(node);
            }
        }

        rs.close();

        return doc;

    }

}