ID3/src/com/DBpackage/MyDatabase.java

package com.DBpackage;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.Properties;

import com.AI.*;

class myProperties
{
	Properties loadConfig() throws IOException
	{
		FileInputStream fis = new FileInputStream("properties.txt");
		Properties prop = new Properties();
		prop.load(fis);
		return prop;
	}
}

public class MyDatabase
{




	// JDBC driver name and database URL
	//private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
	private static final String DB_URL = "jdbc:mysql://localhost/ID3?autoReconnect=true&useSSL=false";

	//  Database credentials
	private static final String USER = "id3";
	private static final String PASS = "id3";

	private Properties prop = new Properties();

	private Connection conn = null;
	private Statement stmt = null;

	private PreparedStatement preparedStatement;


	public MyDatabase()
	{
		try
		{
			System.out.println("Loading properties file");
			myProperties mp = new myProperties();
			this.prop = mp.loadConfig();
		}
		catch(IOException e)
		{
			e.printStackTrace();
		}
	}

	public void connectDatabase()
	{
		try
		{
			//STEP 2: Register JDBC driver
			Class.forName("com.mysql.jdbc.Driver");

			//STEP 3: Open a connection
			System.out.println("Connecting to selected database...");
			conn = DriverManager.getConnection(DB_URL, USER, PASS);
			System.out.println("Connected to database successfully...");

		}
		catch(SQLException se)
		{
			//Handle errors for JDBC
			se.printStackTrace();
			System.exit(1);
		}
		catch(Exception e)
		{
			//Handle errors for Class.forName
			e.printStackTrace();
			System.exit(2);
		}
	}

    /*public void storeValues()
    {
        String sql = prop.getProperty("display");
        System.out.println("Displaying Table");
        try
        {
            preparedStatement = conn.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery(sql);
            ResultSetMetaData rsmd = resultSet.getMetaData();
            int columnsNumber = rsmd.getColumnCount();
            while (resultSet.next())
            {
                for (int i = 1; i <= columnsNumber; i++)
                {
                    String columnValue = resultSet.getString(i);
                    System.out.print(columnValue + " " + rsmd.getAttributeName(i));

                }
                System.out.println("");
            }
        }
        catch(SQLException se)
        {
            se.printStackTrace();
        }
    }*/

	public void  insertValues(int r,String fn, String ln, int m)
	{
		//String sql = "insert into Registration values (?,?,?,?)";
		String sql = prop.getProperty("insert");
		System.out.println("Inserting records into the table...");
		try
		{
            /*
            stmt = conn.createStatement();
            stmt.executeUpdate(str);
            */
			preparedStatement = conn.prepareStatement(sql);
			preparedStatement.setInt(1,r);
			preparedStatement.setString(2,fn);
			preparedStatement.setString(3,ln);
			preparedStatement.setInt(4,m);

			int nodesAffected = preparedStatement.executeUpdate();

			System.out.println("Number of nodes affected:" + nodesAffected);
		}
		catch(Exception e)
		{
			e.printStackTrace();
			System.exit(2);
		}
		System.out.println("Records inserted successfully");
	}

	public void deleteTree()
	{
		String sql = prop.getProperty("deleteAll");
		System.out.println("Deleting all rows from table");
		try{
			preparedStatement = conn.prepareStatement(sql);
			int deletedRows = preparedStatement.executeUpdate();

			if(deletedRows > 0)
				System.out.println("All rows deleted");
			else
				System.out.println("Table already empty");
		}
		catch(Exception e)
		{
			e.printStackTrace();
			System.exit(2);
		}
		sql = prop.getProperty("resetAutoIncrement");
		try {
			preparedStatement = conn.prepareStatement(sql);
			preparedStatement.executeUpdate();
		}
		catch(Exception e)
		{
			e.printStackTrace();
			System.exit(2);
		}
	}

	public void insertTree( String parent, String edge, String child)
	{
		String sql = prop.getProperty("insertTree");
//		System.out.println("Inserting tree records into the table...");
		try
		{
            /*
            stmt = conn.createStatement();
            stmt.executeUpdate(str);
            */
			preparedStatement = conn.prepareStatement(sql);
			preparedStatement.setString(1,parent);
			preparedStatement.setString(2,edge);
			preparedStatement.setString(3,child);

			preparedStatement.executeUpdate();

//			System.out.println("Number of nodes affected:" + rowsAffected);
		}
		catch(Exception e)
		{
			e.printStackTrace();
			System.exit(2);
		}
	}

	public void deleteValues(int id)
	{
		String sql = prop.getProperty("delete");
		System.out.println("Deleting value from table");
		try
		{
			preparedStatement = conn.prepareStatement(sql);
			//preparedStatement.setInt(1,"id");
			preparedStatement.setInt(1,id);
			System.out.println("SQL command = " +  preparedStatement);
			int nodesAffected = preparedStatement.executeUpdate();
			System.out.println("Number of nodes affected:" + nodesAffected);
		}
		catch(SQLException se)
		{
			se.printStackTrace();
		}


	}
	public void updateValues(String fname_new, int id)
	{
		String sql = prop.getProperty("update");
		System.out.println("Updating value fname with id = " + id );
		try
		{
			preparedStatement = conn.prepareStatement(sql);
			//preparedStatement.setInt(1,"id");
			preparedStatement.setString(1,fname_new);
			preparedStatement.setInt(2,id);
			System.out.println("SQL command = " +  preparedStatement);
			int nodesAffected = preparedStatement.executeUpdate();
			System.out.println("Number of nodes affected:" + nodesAffected);
		}
		catch(SQLException se)
		{
			se.printStackTrace();
		}

	}

	public void displayValues()
	{
		String sql = prop.getProperty("display");
		System.out.println("Displaying Table");
		try
		{
			preparedStatement = conn.prepareStatement(sql);
			ResultSet resultSet = preparedStatement.executeQuery(sql);
			ResultSetMetaData rsmd = resultSet.getMetaData();
			int columnsNumber = rsmd.getColumnCount();
			while (resultSet.next())
			{
				for (int i = 1, j = 0; i <= columnsNumber ; i++,j++)
				{
					if (i > 1) System.out.print(",  ");
					String columnValue = resultSet.getString(i);
					System.out.print(columnValue + " " + rsmd.getColumnName(i));
				}
				System.out.println();
			}
		}
		catch(SQLException se)
		{
			se.printStackTrace();
		}
	}


	public ArrayList<Row> storeValues() throws SQLException
	{
		String sql = prop.getProperty("display");
		PreparedStatement preparedStatement = conn.prepareStatement(sql);
		System.out.println("Storing Table");

		ResultSet resultSet;
		resultSet = preparedStatement.executeQuery(sql);
		ResultSetMetaData rsmd = resultSet.getMetaData();
		int columnCount = rsmd.getColumnCount();

//		ArrayList<String> hotelResultList = new ArrayList<>(columnCount);
//		while (results.next()) {
//			int i = 1;
//			while(i <= columnCount) {
//				hotelResultList.add(results.getString(i++));

		ArrayList<Row> tableData = new ArrayList<>(columnCount);
		while (resultSet.next()) {
			int i = 1;
			Row row = new Row();
			while(i<= columnCount)
			{
				row.setRow(rsmd.getColumnName(i),resultSet.getString(rsmd.getColumnName(i++)));
			}
			tableData.add(row);
		}
		return tableData;
	}
	public void closeConnection()
	{

		try
		{
			if(stmt!=null)
				conn.close();
		}
		catch(SQLException se)
		{
		}// do nothing
		try
		{
			if(conn!=null)
				conn.close();
		}
		catch(SQLException se)
		{
			se.printStackTrace();
		}
		System.out.println("Database connection closed successfully");
	}

}