DBC.java

package db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import code.Domain;
import db.Connect;

/**
 *
 * @author sqlitetutorial.net
 */
public class DBC {
	/**
	 * Connect to a sample database
	 */
	// static Connection conn = null;
	/*
	 * public static void connect() {
	 * 
	 * try { // db parameters String url = "jdbc:sqlite:DBPissirDomain.db"; create a
	 * connection to the database conn = DriverManager.getConnection(url);
	 * 
	 * System.out.println("Connection to SQLite has been established.");
	 * 
	 * System.out.println("OIOI"); } catch (SQLException e) {
	 * System.out.println(e.getMessage()); } finally { try { if (conn != null) {
	 * conn.close(); } } catch (SQLException ex) {
	 * System.out.println(ex.getMessage()); } } }
	 */
//	public static ArrayList<Dominio> domainsGet() throws SQLException {
//		// connect();
//
//		/**
//		 * Connect to a sample database
//		 */
//		// public static Connection conn = null;
//
//		Connection conn = null;
//		try {
//			// db parameters
//			String url = "jdbc:sqlite:DBPissirDomain.db";
//			// create a connection to the database
//			conn = DriverManager.getConnection(url);
//
//			System.out.println("Connection to SQLite has been established.");
//			/*
//			 * String sql= "SELECT * FROM Utenti"; Statement statement
//			 * =conn.createStatement(); ResultSet result =statement.executeQuery(sql); while
//			 * (result.next()) { String name=result.getString("User");
//			 * 
//			 * String desc=result.getString("Desc"); System.out.println(name+desc); }
//			 */
//
//			String sql = "SELECT * FROM Domini";
//			Statement statement = conn.createStatement();
//			ResultSet result = statement.executeQuery(sql);
//			System.out.println("57");
//			ArrayList<Dominio> res = new ArrayList<Dominio>();
//			while (result.next()) {
//				System.out.println("60");
//				String dom = result.getString("Domain");
//				String desc = result.getString("Desc");
//
//				// String users=result.getString("Users");
//				ArrayList<String> users = new ArrayList<String>(Arrays.asList(result.getString("Users").split(",")));
//				ArrayList<String> reso = new ArrayList<String>(Arrays.asList(result.getString("Resources").split(",")));
//				// String reso=result.getString("Resources");
//
//				String state = result.getString("State");
//				ArrayList<String> serv = new ArrayList<String>(Arrays.asList(result.getString("Services").split(",")));
//				// String serv=result.getString("Services");
//				Dominio d = new Dominio(dom, desc, users, reso, state, serv);
//				res.add(d);
//				System.out.println("74");
//
//			}
//			return res;
//
//		} catch (SQLException e) {
//			System.out.println(e.getMessage());
//		} finally {
//			try {
//				if (conn != null) {
//					conn.close();
//				}
//			} catch (SQLException ex) {
//				System.out.println(ex.getMessage());
//			}
//		}
//		return null;
//
//	}

	public static Dominio getDom(String dm) throws SQLException {
		// connect();
		// Statement statement =conn.createStatement();

		Connection conn = null;
		try {
			// db parameters
			String url = "jdbc:sqlite:DBPissirDomain.db";
			// create a connection to the database
			conn = DriverManager.getConnection(url);

			System.out.println("Connection to SQLite has been established: getDom");
			/*
			 * String sql= "SELECT * FROM Utenti"; Statement statement
			 * =conn.createStatement(); ResultSet result =statement.executeQuery(sql); while
			 * (result.next()) { String name=result.getString("User");
			 * 
			 * String desc=result.getString("Desc"); System.out.println(name+desc); }
			 */
			
			
			PreparedStatement prepared = conn.prepareStatement("SELECT * FROM Domini WHERE Domain = ?");
			prepared.setString(1, dm);
			ResultSet result = prepared.executeQuery();
			System.out.println("DBCISONO1");
			
			// String sql= "SELECT * FROM Domini WHERE Domain = ?";

			// ResultSet result =statement.executeQuery(sql);
			// Dominio res=new Dominio();
			
			while (result.next()) {
				System.out.println("DBCISONO2");
				String dom = result.getString("Domain");
				String desc = result.getString("Desc");

				// String users=result.getString("Users");
				//ArrayList<String> users = new ArrayList<String>(Arrays.asList(result.getString("Users").split(",")));
				//ArrayList<String> reso = new ArrayList<String>(Arrays.asList(result.getString("Resources").split(",")));
				// String reso=result.getString("Resources");

				Integer state = result.getInt("State");
				//ArrayList<String> serv = new ArrayList<String>(Arrays.asList(result.getString("Services").split(",")));
				// String serv=result.getString("Services");
				Dominio d = new Dominio(dom, desc, state);//users, reso,, serv
				// res.add(d);
				System.out.println(d.toString());
				return d;
			}
			//return null;

		} catch (SQLException e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				System.out.println(ex.getMessage());
			}
		}
		return null;

	}

	public static void delDom(String dm) throws SQLException {

		Connection conn = null;
		try {
			// db parameters
			String url = "jdbc:sqlite:DBPissirDomain.db";
			// create a connection to the database
			conn = DriverManager.getConnection(url);

			System.out.println("Connection to SQLite has been established: delDom");

			PreparedStatement prepared = conn.prepareStatement("DELETE FROM Domini WHERE Domain = ?");
			prepared.setString(1, dm);
			int result = prepared.executeUpdate();

		} catch (SQLException e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				System.out.println(ex.getMessage());
			}
		}
		// return false;

	}
	
	
	public static void insertDom(String j) throws SQLException, JSONException {
		// connect();
		// Statement statement =conn.createStatement();

		Connection conn = null;
		try {
			// db parameters
			String url = "jdbc:sqlite:DBPissirDomain.db";
			// create a connection to the database
			conn = DriverManager.getConnection(url);

			System.out.println("Connection to SQLite has been established.");
			/*
			 * String sql= "SELECT * FROM Utenti"; Statement statement
			 * =conn.createStatement(); ResultSet result =statement.executeQuery(sql); while
			 * (result.next()) { String name=result.getString("User");
			 * 
			 * String desc=result.getString("Desc"); System.out.println(name+desc); }
			 */
/*
			String dom=j.getString("domain");
			String desc=j.getString("desc");
			String usr=j.getString("users");
			String rsc=j.getString("resources");
			String srv=j.getString("services");
			
			String sql = "INSERT INTO Domini (Domain, Desc, Users, Resources, State, Services) VALUES ("+dom+","+desc+","+usr+","+rsc+","+"N/A"+","+srv+")";
			Statement statement = conn.createStatement();
			ResultSet result = statement.executeQuery(sql);
			
	*/		
			
			PreparedStatement prepared = conn.prepareStatement("INSERT INTO Domini (Domain, State) VALUES (?1,?2)");
			// Users, Resources, , Services ,?4,?5,?6
//			System.out.println(j.getString("domain"));
//			System.out.println(j.getString("desc"));
			//System.out.println(j.getString("users"));
			//System.out.println(j.getString("services"));
			//System.out.println(j.getString("resources"));
			
			
			prepared.setString(1, j);
			prepared.setInt(2, 0);
			//prepared.setString(3, j.getString("users").toString().trim());
			//prepared.setString(4, j.getString("resources").toString().trim());
			//prepared.setInt(3, 0);
			//prepared.setString(6, j.getString("services").toString().trim());
			int result = prepared.executeUpdate();
			
			//System.out.println(result.toString());

			// String sql= "SELECT * FROM Domini WHERE Domain = ?";

			// ResultSet result =statement.executeQuery(sql);
			// Dominio res=new Dominio();
			
			
			/*
			while (result.next()) {

				String dom = result.getString("Domain");
				String desc = result.getString("Desc");

				// String users=result.getString("Users");
				ArrayList<String> users = new ArrayList<String>(Arrays.asList(result.getString("Users").split(",")));
				ArrayList<String> reso = new ArrayList<String>(Arrays.asList(result.getString("Resources").split(",")));
				// String reso=result.getString("Resources");

				String state = result.getString("State");
				ArrayList<String> serv = new ArrayList<String>(Arrays.asList(result.getString("Services").split(",")));
				// String serv=result.getString("Services");
				Dominio d = new Dominio(dom, desc, users, reso, state, serv);
				// res.add(d);
				
			}
			
	*/
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				System.out.println(ex.getMessage());
			}
		}
		

	}
	
	
	public static ArrayList<String> getDomainsAdmin(String user) throws SQLException, JSONException {
		// connect();
		// Statement statement =conn.createStatement();

		Connection conn = null;
		try {
			// db parameters
			String url = "jdbc:sqlite:DBPissirDomain.db";
			// create a connection to the database
			conn = DriverManager.getConnection(url);

			System.out.println("Connection to SQLite has been established: getDomains");
			
			PreparedStatement prepared = conn.prepareStatement("SELECT * FROM Amministra WHERE User = ?");
			prepared.setString(1, user);
			ResultSet result = prepared.executeQuery();
			System.out.println("DOMSADMIN1");
			
			ArrayList<String> res=new ArrayList<String>();
			JSONArray jsnarr=new JSONArray();
			while (result.next()) {
				System.out.println("DOMSADMIN12");
				String dom = result.getString("Domain");
//				String desc= result.getString("Desc");
//				Integer state = result.getInt("State");
//				
//				JSONObject j=new JSONObject("{"+dom+":"+state+"}"); 
//				jsnarr.put(j);
//				
//				Dominio d=new Dominio(dom,desc,state);
				res.add(dom);
				
			}
			//return jsnarr;
			return res;

		} catch (SQLException e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				System.out.println(ex.getMessage());
			}
		}
		return null;

	}
	
	
	
	
	public static ArrayList<String> getDomainsUser(String user) throws SQLException, JSONException {
		// connect();
		// Statement statement =conn.createStatement();

		Connection conn = null;
		try {
			// db parameters
			String url = "jdbc:sqlite:DBPissirDomain.db";
			// create a connection to the database
			conn = DriverManager.getConnection(url);

			System.out.println("Connection to SQLite has been established: getDomains");
			
			PreparedStatement prepared = conn.prepareStatement("SELECT * FROM Usa WHERE User = ?");
			prepared.setString(1, user);
			ResultSet result = prepared.executeQuery();
			System.out.println("DOMSADMIN1");
			
			ArrayList<String> res=new ArrayList<String>();
			JSONArray jsnarr=new JSONArray();
			while (result.next()) {
				System.out.println("DOMSADMIN12");
				String dom = result.getString("Domain");
//				String desc= result.getString("Desc");
//				Integer state = result.getInt("State");
//				
//				JSONObject j=new JSONObject("{"+dom+":"+state+"}"); 
//				jsnarr.put(j);
//				
//				Dominio d=new Dominio(dom,desc,state);
				res.add(dom);
				
			}
			//return jsnarr;
			return res;

		} catch (SQLException e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				System.out.println(ex.getMessage());
			}
		}
		return null;

	}
	
	public static Dominio getDomain(String domain) throws SQLException, JSONException {
		// connect();
		// Statement statement =conn.createStatement();

		Connection conn = null;
		try {
			// db parameters
			String url = "jdbc:sqlite:DBPissirDomain.db";
			// create a connection to the database
			conn = DriverManager.getConnection(url);

			System.out.println("Connection to SQLite has been established: getDomains");
			
			PreparedStatement prepared = conn.prepareStatement("SELECT * FROM Domini WHERE Domain = ?");
			prepared.setString(1, domain);
			ResultSet result = prepared.executeQuery();
			System.out.println("DOMSADMIN1");
			
			//ArrayList<String> res=new ArrayList<String>();
			//JSONArray jsnarr=new JSONArray();
			while (result.next()) {
				System.out.println("DOMSADMIN12");
				String dom = result.getString("Domain");
				String desc= result.getString("Desc");
				Integer state = result.getInt("State");
//				
//				JSONObject j=new JSONObject("{"+dom+":"+state+"}"); 
//				jsnarr.put(j);
//				
				Dominio d=new Dominio(dom,desc,state);
				//res.add(dom);
				return d;
			}
			//return jsnarr;
			

		} catch (SQLException e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				System.out.println(ex.getMessage());
			}
		}
		return null;

	}
	
	
	
	
	

	public static void fillModules(String a, String b, String c) throws SQLException, JSONException {
		// connect();
		// Statement statement =conn.createStatement();

		Connection conn = null;
		try {
			// db parameters
			String url = "jdbc:sqlite:../DBPissirDomain.db";
			// create a connection to the database
			conn = DriverManager.getConnection(url);

			System.out.println("Connection to SQLite has been established: ---fillModules---");
			
			PreparedStatement prepared = conn.prepareStatement("INSERT INTO Moduli (Module, Requirements, Path) VALUES (?,?,?);");
			
			prepared.setString(1, a);
			prepared.setString(2, b);
			
			
			//prepared.setString(3, j.getString("users").toString().trim());
			//prepared.setString(4, j.getString("resources").toString().trim());
			
			
			prepared.setString(3, c);
			
			//prepared.setString(6, j.getString("services").toString().trim());
//			System.out.println(prepared.toString());
			int result = prepared.executeUpdate();	
			

		} catch (SQLException e) {
			if(!e.getMessage().contains("SQLITE_CONSTRAINT_PRIMARYKEY")) {
				System.out.println(e.getMessage());
			}
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				System.out.println(ex.getMessage());
			}
		}
		//return null;

	}
	
	public static ArrayList<String> getModules() throws SQLException, JSONException {
		// connect();
		// Statement statement =conn.createStatement();

		Connection conn = null;
		try {
			// db parameters
			String url = "jdbc:sqlite:DBPissirDomain.db";
			// create a connection to the database
			conn = DriverManager.getConnection(url);

			System.out.println("Connection to SQLite has been established: getDomains");
			
			PreparedStatement prepared = conn.prepareStatement("SELECT * FROM Moduli");
			//prepared.setString(1, user);
			ResultSet result = prepared.executeQuery();
			System.out.println("DOMSADMIN1");
			
			ArrayList<String> res=new ArrayList<String>();
			JSONArray jsnarr=new JSONArray();
			while (result.next()) {
				System.out.println("GETMODULI2");
				String mod = result.getString("Module");
				//String req= result.getString("Requirements");
				
//				Integer state = result.getInt("State");
//				
//				JSONObject j=new JSONObject("{"+dom+":"+state+"}"); 
//				jsnarr.put(j);
//				
//				Dominio d=new Dominio(dom,desc,state);
				res.add(mod);
				
			}
			//return jsnarr;
			return res;

		} catch (SQLException e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				System.out.println(ex.getMessage());
			}
		}
		return null;

	}
	
	public static void insertAmministra(String user, String dom) throws SQLException, JSONException {
		// connect();
		// Statement statement =conn.createStatement();

		Connection conn = null;
		try {
			// db parameters
			String url = "jdbc:sqlite:DBPissirDomain.db";
			// create a connection to the database
			conn = DriverManager.getConnection(url);
			System.out.println("Connection to SQLite has been established.");
			PreparedStatement prepared = conn.prepareStatement("INSERT INTO Amministra (User, Domain) VALUES (?1,?2)");
			prepared.setString(1, user);
			prepared.setString(2, dom);
			//prepared.setString(6, j.getString("services").toString().trim());
			int result = prepared.executeUpdate();	
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				System.out.println(ex.getMessage());
			}
		}
	}
	
	public static void insertUsa(String user, String dom) throws SQLException, JSONException {
		// connect();
		// Statement statement =conn.createStatement();

		Connection conn = null;
		try {
			// db parameters
			String url = "jdbc:sqlite:DBPissirDomain.db";
			// create a connection to the database
			conn = DriverManager.getConnection(url);
			System.out.println("Connection to SQLite has been established.");
			PreparedStatement prepared = conn.prepareStatement("INSERT INTO Usa (User, Domain) VALUES (?1,?2)");
			prepared.setString(1, user);
			prepared.setString(2, dom);
			//prepared.setString(6, j.getString("services").toString().trim());
			int result = prepared.executeUpdate();	
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				System.out.println(ex.getMessage());
			}
		}
	}

	
	
	public static void insertService(String a, String b, String c) throws SQLException, JSONException {
		// connect();
		// Statement statement =conn.createStatement();

		Connection conn = null;
		try {
			// db parameters
			String url = "jdbc:sqlite:DBPissirDomain.db";
			// create a connection to the database
			conn = DriverManager.getConnection(url);

			System.out.println("Connection to SQLite has been established: ---fillModules---");
			
			PreparedStatement prepared = conn.prepareStatement("INSERT INTO Servizi (Domain, Resource, Module) VALUES (?1,?2,?3)");
			// Users, Resources, , Services ,?4,?5,?6
			//System.out.println(j.getString("domain"));
			//System.out.println(j.getString("desc"));
			//System.out.println(j.getString("users"));
			//System.out.println(j.getString("services"));
			//System.out.println(j.getString("resources"));
			
			
			prepared.setString(1, a);
			prepared.setString(2, b);
			//prepared.setString(3, j.getString("users").toString().trim());
			//prepared.setString(4, j.getString("resources").toString().trim());
			prepared.setString(3, c);
			//prepared.setString(6, j.getString("services").toString().trim());
			int result = prepared.executeUpdate();
			

		} catch (SQLException e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				System.out.println(ex.getMessage());
			}
		}
		//return null;

	}
	
	public static JSONArray getServices(String domain) throws SQLException, JSONException {
		// connect();
		// Statement statement =conn.createStatement();

		Connection conn = null;
		try {
			// db parameters
			String url = "jdbc:sqlite:DBPissirDomain.db";
			// create a connection to the database
			conn = DriverManager.getConnection(url);

			System.out.println("Connection to SQLite has been established: getDomains");
			
			PreparedStatement prepared = conn.prepareStatement("SELECT Module FROM Servizi WHERE Domain = ?");
			prepared.setString(1, domain);
			ResultSet result = prepared.executeQuery();
			System.out.println("GETSERVICES");
			
			//ArrayList<String> res=new ArrayList<String>();
			//JSONArray jsnarr=new JSONArray();
			ArrayList<String> arr=new ArrayList<String>();
			JSONArray res=new JSONArray();
			while (result.next()) {
				System.out.println("GETSERVICESWHILE");
				//JSONObject j= result;
				
				if(!arr.contains(result.getString("Module"))) {
					arr.add(result.getString("Module"));
					res.put(result.getString("Module"));
					}
				
				System.out.println(result.getString("Module"));
				//String dom = result.getString("Domain");
				//String desc= result.getString("Desc");
				//Integer state = result.getInt("State");
//				
//				JSONObject j=new JSONObject("{"+dom+":"+state+"}"); 
//				jsnarr.put(j);
//				
				//Dominio d=new Dominio(dom,desc,state);
				//res.add(dom);
				
			}
			
	
			return res;
			

		} catch (SQLException e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException ex) {
				System.out.println(ex.getMessage());
			}
		}
		return null;

	}
	
	

	/**
	 * @param args the command line arguments
	 */
	// public static void main(String[] args) {
	// connect();
	// }
}