package com.phoenix.controller;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;

import org.json.JSONObject;

public class DBHelper
{
	public static Connection openConnection()
	{
		try{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection cn = DriverManager.getConnection("jdbc:mysql://localhost:3306/CargoTracking","root","123");
			return cn;
			}
		catch(Exception e)
		{
			System.out.println("[DBhelper:openConnection]:"+e);
			return null;
		}
	}
	public static boolean executeUpdate(Connection cn, String query)
	{
		try
		{
			Statement smt = cn.createStatement();
			smt.executeUpdate(query);
			return true;
		}
		catch(Exception e)
		{
			System.out.println("[DbHelper:executeUpdate]"+e);
			return false;
		}
	}
	public static ResultSet executeQuery(Connection cn, String query)
	{
		try
		{
			Statement smt = cn.createStatement();
			ResultSet rs = smt.executeQuery(query);
			return rs;
		}
		catch(Exception e)
		{
			System.out.println("[DBHelper:executeQuery]"+e);
			return null;
		}
	}

	public static ArrayList<JSONObject> JsonEngine(ResultSet rs)
	{
		ArrayList<JSONObject> resList = new ArrayList<JSONObject>();
		try
		{
			//Getting column names//
			ResultSetMetaData rsMeta = rs.getMetaData();
			int columnCount = rsMeta.getColumnCount();
			ArrayList<String> columnNames = new ArrayList<String>();
			for(int i=1;i<=columnCount;i++)
			{
				columnNames.add(rsMeta.getColumnName(i).toUpperCase());
			}
			//Convert the JSON Object to human readable form//
			while(rs.next())
			{
				JSONObject obj = new JSONObject();
				for(int i=1;i<=columnCount;i++)
				{
					String key = columnNames.get(i-1);
					String value = rs.getString(i);
					obj.put(key,value);
				}
				resList.add(obj);
			}
		}
			catch(Exception e)
			{
				e.printStackTrace();
			}
			finally
			{
				try
				{
					rs.close();
				}
				catch(Exception e)
				{
					e.printStackTrace();
				}

			}
			return resList;

	}

}