XML to SQLite Data Import

/*
    XML to SQLite import.
    Application Program.

Author: Jatin Thakur
        coderbots.blogspot.com

        30/5/14
*/


import java.io.BufferedReader;
import java.io.File;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import org.w3c.dom.Document;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

public class CRUD {

    public static final String driver = "org.sqlite.JDBC";
    static Statement stmt;
    static BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
    static String tableName = "MyContacts";

    //READ
    public static void readContact(){
        System.out.println("\nEnter the Contact Name to query: ");
        String name = null;
        try {
            name = br.readLine();
        } catch (IOException ex) {
            System.out.println("Could not read input");
        }
        try{
            ResultSet res = stmt.executeQuery("SELECT * FROM "+tableName+" WHERE Name = '"+name+"';");
            displayResult(res);
        }catch(SQLException e){
            System.out.println("Error reading contact information "+e.toString());
        }
    }


    //UPDATE

    public static void addContact(){

        String name = null, address = null, phoneNo = null;
        int age = 0;
        try {
            System.out.println("\nContact's Name: ");
            name = br.readLine();
            System.out.println("\nAge: ");
            age = Integer.parseInt(br.readLine());
            System.out.println("\nAddress: ");
            address = br.readLine();
            System.out.println("\nPhone: ");
            phoneNo = br.readLine();
        } catch (IOException ex) {
            Logger.getLogger(CRUD.class.getName()).log(Level.SEVERE, null, ex);
        }
        try{
            //if age=0 insert null instead and not 0
            if(age==0){stmt.execute("INSERT INTO "+tableName+" (Name, Address, PhoneNo) VALUES ('"+name+"', '"+address+"', '"+phoneNo+"');");}
            else
            stmt.execute("INSERT INTO "+tableName+" (Name, Age, Address, PhoneNo) VALUES ('"+name+"', "+age+", '"+address+"', '"+phoneNo+"');");
        }catch(SQLException e){System.out.println("Error while adding Contact"+e.toString());}
    }

    public static void modifyContact(){
        System.out.println("\nEnter the Contact's Name: ");
        String name = null;
        try {
            name = br.readLine();
        } catch (IOException ex) {
            Logger.getLogger(CRUD.class.getName()).log(Level.SEVERE, null, ex);
        }
        ResultSet res=null;
        try {
            res = stmt.executeQuery("SELECT * FROM "+tableName+" WHERE Name = '"+name+"';");
        } catch (SQLException ex) {
            Logger.getLogger(CRUD.class.getName()).log(Level.SEVERE, null, ex);
        }

        try {
            int count=0;
            while(res.next()){count++;}


        if(count>0){
                res = stmt.executeQuery("SELECT * FROM "+tableName+" WHERE Name = '"+name+"';");
                System.out.println("Results found: ");
                displayResult(res);
                System.out.println("\nEnter the ID to modify: ");
                String id = br.readLine();
                System.out.println("\nColumn to modify: ");
                String col = br.readLine();
                System.out.println("\nEnter new column value: ");
                String colVal = br.readLine();

                if(col.matches("Age")){
                    int ageA = Integer.parseInt(colVal);
                    if(ageA==0){stmt.executeUpdate("UPDATE "+tableName+" SET "+col+" = NULL WHERE ID = "+id+";");}
                    else{
                        stmt.executeUpdate("UPDATE "+tableName+" SET "+col+" = "+ageA+" WHERE ID = "+id+";");
                    }
                }else{
                stmt.executeUpdate("UPDATE "+tableName+" SET "+col+" = '"+colVal+"' WHERE ID = "+id+";");
                }
                System.out.println("Contact Updated!");
            }
        } catch (SQLException ex) {
                Logger.getLogger(CRUD.class.getName()).log(Level.SEVERE, null, ex);
            } catch (IOException ex) {
                Logger.getLogger(CRUD.class.getName()).log(Level.SEVERE, null, ex);
         }
    }

    //DELETE
    public static void deleteContact(){
                System.out.println("\nEnter the Contact's Name: ");
        String name = null;
        try {
            name = br.readLine();
        } catch (IOException ex) {
            Logger.getLogger(CRUD.class.getName()).log(Level.SEVERE, null, ex);
        }
        ResultSet res=null;
        try {
            res = stmt.executeQuery("SELECT * FROM "+tableName+" WHERE Name = '"+name+"';");
        } catch (SQLException ex) {
            Logger.getLogger(CRUD.class.getName()).log(Level.SEVERE, null, ex);
        }

        try {
            int count=0;
            while(res.next()){count++;}

            res = stmt.executeQuery("SELECT * FROM "+tableName+" WHERE Name = '"+name+"';");
        if(count>0){
                System.out.println("\nResults found: ");
                displayResult(res);
                System.out.println("\nEnter the ID to delete: ");
                String id = br.readLine();
                stmt.execute("DELETE FROM "+tableName+" WHERE ID = "+id+";");
                System.out.println("Contact Deleted!");
        }
        } catch (SQLException ex) {
            Logger.getLogger(CRUD.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(CRUD.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static void deleteTable() {
        System.out.println("Do you want to delete the table(y/n): ");

        String input = null;
        try {
            input = br.readLine();
        } catch (IOException ex) {
            System.out.println("Could not read input");
        }
        if(input.equalsIgnoreCase("yes")||input.equalsIgnoreCase("y")){
            try {
                stmt.execute("DROP TABLE "+tableName+" ;");
            } catch (SQLException ex) {
               System.out.println("Table could not be deleted");
            }
            System.out.println("ContactBook "+tableName+" deleted successfully");
        }
    }

    //Reading XML into SQLite Database
    public static void readXMLintoDatabase() throws SAXException, IOException, ParserConfigurationException, SQLException{
            System.out.println("\nSpecify the XML file path to read Contacts From: ");
            String path = br.readLine();
            System.out.println("\nImporting Contacts...");
            Pattern p = Pattern.compile(".*\\.xml$");
            Matcher m = p.matcher(path);
            if(!m.find()){
                path+=".xml";
            }

            String xmlDocPath = path;
            DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
            DocumentBuilder db = dbf.newDocumentBuilder();
            Document doc = db.parse(new File(xmlDocPath));

            NodeList nameList, ageList, addressList, phoneNumberList;
            try{
                nameList = doc.getElementsByTagName("Name");
                ageList  = doc.getElementsByTagName("Age");
                addressList = doc.getElementsByTagName("Address");
                phoneNumberList = doc.getElementsByTagName("PhoneNo");
            }catch(Exception e){
                System.out.println("Error reading Input XML");
                return;
            }

            for(int i =0;i<nameList.getLength();++i){
            String name = nameList.item(i).getFirstChild().getNodeValue();
            int age = Integer.parseInt(ageList.item(i).getFirstChild().getNodeValue());
            String address = addressList.item(i).getFirstChild().getNodeValue();
            String phoneNumber = phoneNumberList.item(i).getFirstChild().getNodeValue();


            try{
            stmt.execute("INSERT INTO "+tableName+" (Name, Age, Address, PhoneNo) VALUES ('"+name+"', "+age+", '"+
                    address+"', '"+phoneNumber+"')");
            }catch(SQLException e){
                System.out.println("Skipping Duplicate...");
            }
         }
    }

    //Display Results
    public static void displayResult(ResultSet res) {

        try{
        if(res==null){
            res = stmt.executeQuery("SELECT * FROM "+tableName+";");
        }

        System.out.println("\nID  |  Name |  Age  |  Address  |  PhoneNo");

        while(res.next()){
            String id=res.getString("id");
            String name = res.getString("Name");
            int age=res.getInt("Age");
            String Address = res.getString("Address");
            String PhoneNumber = res.getString("PhoneNo");
            System.out.println(id+"\t"+name+"\t"+age+"\t"+Address+"\t"+PhoneNumber);
        }
        }catch(SQLException e){
            System.out.println("Error while fetching data: "+e.toString());}
    }

    public static void main(String[] args) throws SQLException{
        try{
            Driver d = (Driver)Class.forName(driver).newInstance();
            DriverManager.registerDriver(d);
        }catch(Exception e){
            System.out.println("Error loading Database Driver: "+e.toString());
        }

        Connection con = null;
        try{
            System.out.println("---XML to SQLite---");
            System.out.println("\nEnter database file URL: ");
            String path = null;
            try{path=br.readLine();}catch(IOException e){System.out.println("Error reading Input");}

            //In case user doesn't add .db extension to the filename
            Pattern p = Pattern.compile(".*\\.db$");
            Matcher m = p.matcher(path);
            if(!m.find()){
                path+=".db";
            }
            String url="jdbc:sqlite:/"+path;
            con = DriverManager.getConnection(url);
        }catch (SQLException e) {
            System.out.println("Error creating connection: " + e.toString());
        }

        stmt = con.createStatement();
        String instruction;

        stmt.execute("CREATE TABLE IF NOT EXISTS "+tableName+"("
                + "ID INTEGER PRIMARY KEY AUTOINCREMENT,"
                + "Name TEXT,"
                + "Age INT,"
                + "Address TEXT,"
                + "PhoneNo TEXT UNIQUE"
                + ");");

        //Read XML File
        try{
        readXMLintoDatabase();
        System.out.println("\nContacts Added Successfully!");
        }catch(IOException | ParserConfigurationException | SAXException e){}


        boolean more = true;
        while(more){

        System.out.println("\nChoose an operation to perform on table "+tableName+": [\"quit\"] to exit");
        System.out.println("1. Add Contact");
        System.out.println("2. Read Contact");
        System.out.println("3. Modify Contact");
        System.out.println("4. Delete Contact");
        System.out.println("5. View Phonebook Contents");
        System.out.println("6. Delete Phonebook");

        int choice = -1;
        String choiceUser = "";
        try{choiceUser = br.readLine(); if(choiceUser.equalsIgnoreCase("exit")||choiceUser.equalsIgnoreCase("quit")) {return;}
        else{choice = Integer.parseInt(choiceUser);}

        }catch(IOException e){System.out.println("Error reading user's choice!");}

        try{
        switch(choice){
            case 1: addContact();displayResult(null);System.out.println("\nPress any key to continue...");br.readLine();break;
            case 2: readContact();System.out.println("\nPress any key to continue...");br.readLine();break;
            case 3: modifyContact();displayResult(null);System.out.println("\nPress any key to continue...");br.readLine();break;
            case 4: deleteContact();displayResult(null);System.out.println("\nPress any key to continue...");br.readLine();break;
            case 5: displayResult(null);System.out.println("\nPress any key to continue...");br.readLine();break;
            case 6: deleteTable();return;
            default: System.out.println("\nInvalid operation selected!");
        }
        }catch(Exception e){}
    }
  }
}