package db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;

import org.json.*;

public class CON {

	public static void main(String[] argv) throws JSONException {

		System.out.println("-------- PostgreSQL "
				+ "JDBC Connection Testing ------------");

		try {

			Class.forName("org.postgresql.Driver");

		} catch (ClassNotFoundException e) {

			System.out.println("Where is your PostgreSQL JDBC Driver? "
					+ "Include in your library path!");
			e.printStackTrace();
			return;

		}

		System.out.println("PostgreSQL JDBC Driver Registered!");

		Connection connection = null;
		ResultSet resultSet = null;

		try {

			connection = DriverManager.getConnection(
					"jdbc:postgresql://192.168.60.120/sandbox_timetable", "sandbox",
					"sandbox");

			Statement select = connection.createStatement();
            resultSet = select.executeQuery("SELECT * FROM trains");

            JSONArray jArray = new JSONArray();

              while (resultSet.next())
              {
            	  Integer json_id = resultSet.getInt("id");
    		      String json_number = resultSet.getString("number");
    		      String json_carrier = resultSet.getString("carrier");
    		      String json_category = resultSet.getString("category");
    		      String json_name = resultSet.getString("name");

    		      JSONObject jObj = new JSONObject();
    		      jObj.put("id", json_id);
    		      jObj.put("number", json_number);
    		      jObj.put("carrier", json_carrier);
    		      jObj.put("category", json_category);
    		      jObj.put("name", json_name);

    		      jArray.put(jObj);

              }

              JSONObject jObjDevice = new JSONObject();
              jObjDevice.put("device", jArray);
              JSONObject jObjDeviceList = new JSONObject();
              jObjDevice.put("devicelist", jObjDevice );


		} catch (SQLException e) {

			System.out.println("Connection Failed! Check output console");
			e.printStackTrace();
			return;

		}

		if (connection != null) {
			System.out.println("You made it, take control your database now!");
		} else {
			System.out.println("Failed to make connection!");
		}
	}

}

/*
public class CON {
	private Connection connect = null;
	private Statement statement = null;
	private ResultSet resultSet = null;

  public void readDataBase() throws Exception {
		    try {
		      Class.forName("com.mysql.jdbc.Driver");
		      connect = DriverManager
		          .getConnection("jdbc:mysql://192.168.60.210/sandbox_table?"
		              + "user=sandbox&password=sandbox");

		      statement = connect.createStatement();
		      resultSet = statement.executeQuery("SELECT * FROM trains");
		      writeResultSet(resultSet);

		    } catch (Exception e) {
		      throw e;
		    } finally {
		      close();
		    }

		  }

	  private void writeResultSet(ResultSet resultSet) throws SQLException {
		    while (resultSet.next()) {
		      String id = resultSet.getString("id");
		      String number = resultSet.getString("number");
		      String carrier = resultSet.getString("carrier");
		      Integer category = resultSet.getInt("category");
		      String name = resultSet.getString("name");
		      System.out.println("ID: " + id);
		      System.out.println("Number: " + number);
		      System.out.println("Carrier: " + carrier);
		      System.out.println("Category: " + category);
		      System.out.println("Name: " + name);
		    }
		  }

	  private void close() {
		    try {
		      if (resultSet != null) {
		        resultSet.close();
		      }

		      if (statement != null) {
		        statement.close();
		      }

		      if (connect != null) {
		        connect.close();
		      }
		    } catch (Exception e) {

		    }
		  }
}
 */