DBConn.java

package se252.jan15.calvinandhobbes.project0;

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

public class DBConn {
	private static Connection conn = null;
	
	public static void initDBConn() {
		IIScCampusMapProperties prop = new IIScCampusMapProperties();
		String forName, host, username, password, database;
		forName = prop.getProperty("forName");
		host = prop.getProperty("host");
		username = prop.getProperty("username");
		password = prop.getProperty("password");
		database = prop.getProperty("database");
		try {
			Class.forName(forName);
			String url = "jdbc:sqlserver://"+host+";user="+username+";password="+password+";database="+database;
			conn = DriverManager.getConnection(url);
		}
		catch(Exception e) {
			System.out.println(e);
		}
	}
	
	public static LayerInfo[] getCategoryInfo(String category) {
		LayerInfo[] layerArray = null;
		ArrayList<LayerInfo> layers = new ArrayList<LayerInfo>();
		Connection sqlConn = conn;
		String queryString = "select * from PointOfInterest where CatId = (select catId from Category where CatName = '"+category+"')";
		try {
			Statement stmt = sqlConn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from Category where CatName = '"+category+"'");
			if(!rs.next()) {
				System.out.println("Invalide category "+ category +" asked");
				stmt.close();
				return null;
			}
			rs = stmt.executeQuery(queryString);
			while(rs.next()) {
				LayerInfo lf = new LayerInfo();
				lf.setName(rs.getString(1));
				lf.setCategory(category);
				lf.setLatitude(rs.getFloat(3));
				lf.setLongitude(rs.getFloat(4));
				lf.setAddress(rs.getString(5));
				lf.setDescription(rs.getString(6));
				layers.add(lf);
			}
			stmt.close();
		}
		catch(Exception e) {
			System.out.println(e);
		}
		layerArray = new LayerInfo[layers.size()];
		layers.toArray(layerArray);
		return layerArray;
	}
	
	public static LayerInfo[] getCategories() {
		LayerInfo[] layerArray = null;
		ArrayList<LayerInfo> layers = new ArrayList<LayerInfo>();
		Connection sqlConn = conn;
		String queryString = "select CatName from Category order by CatId";
		try {
			Statement stmt = sqlConn.createStatement();
			ResultSet rs = stmt.executeQuery(queryString);
			LayerInfo lf = new LayerInfo();
			lf.setName("Dining Outside");
			lf.setCategory("");
			lf.setLatitude(0);
			lf.setLongitude(0);
			lf.setDescription("");
			layers.add(lf);
			lf = new LayerInfo();
			lf.setName("Event");
			lf.setCategory("");
			lf.setLatitude(0);
			lf.setLongitude(0);
			lf.setDescription("");
			layers.add(lf);
			while(rs.next()) {
				lf = new LayerInfo();
				lf.setName(rs.getString(1));
				lf.setCategory("");
				lf.setLatitude(0);
				lf.setLongitude(0);
				lf.setDescription("");
				layers.add(lf);
			}
			stmt.close();
		}
		catch(Exception e) {
			System.out.println(e);
		}
		layerArray = new LayerInfo[layers.size()];
		layers.toArray(layerArray);
		return layerArray;
	}
	
	public static EventInfo[] getEvents() {
		EventInfo[] layerArray = null;
		ArrayList<EventInfo> layers = new ArrayList<EventInfo>();
		Connection sqlConn = conn;
		String queryString = "select * from Event E, Category C, PointOfInterest P\r\n" + 
				"  where E.CatId = P.CatId\r\n" + 
				"  and E.PlaceName = P.Name\r\n" + 
				"  and P.CatId = C.CatId";
		try {
			Statement stmt = sqlConn.createStatement();
			ResultSet rs = stmt.executeQuery(queryString);
			while(rs.next()) {
				EventInfo lf = new EventInfo();
				lf.setEventTitle(rs.getString(1));
				lf.setName(rs.getString(2));
				lf.setEventStart(rs.getString(4));
				lf.setEventEnd(rs.getString(5));
				lf.setEventDesc(rs.getString(6));
				lf.setCategory(rs.getString(8));
				lf.setLatitude(rs.getFloat(11));
				lf.setLongitude(rs.getFloat(12));
				lf.setAddress(rs.getString(13));
				lf.setDescription(rs.getString(14));
				layers.add(lf);
			}
			stmt.close();
		}
		catch(Exception e) {
			System.out.println(e);
		}
		layerArray = new EventInfo[layers.size()];
		layers.toArray(layerArray);
		return layerArray;
	}
	
	public static boolean insertLayer(LayerInfo layer) {
		boolean ret = true;
		String catId = null;
		String queryStr = "select CatId from Category where CatName = '"+layer.getCategory()+"'";
		
		Connection sqlConn = conn;
		try {
			Statement stmt = sqlConn.createStatement();
			ResultSet rs = stmt.executeQuery(queryStr);
			if(rs.next())
				catId = rs.getString(1);
			
			queryStr = "insert into PointOfInterest values ('";
			queryStr += layer.getName() + "', ";
			queryStr += catId + ", ";
			queryStr += layer.getLatitude() + ", ";
			queryStr += layer.getLongitude() + ", '";
			queryStr += layer.getAddress() + "', '";
			queryStr += layer.getDescription() + "')";
			
			stmt.execute(queryStr);
			stmt.close();
		}
		catch(Exception e) {
			System.out.println(queryStr);
			ret = false;
			System.out.println(e);
		}
		return ret;
	}
	
	public static boolean insertEvent(EventInfo layer) {
		boolean ret = true;
		String catId = null;
		String queryStr = "select CatId from Category where CatName = '"+layer.getCategory()+"'";
		
		Connection sqlConn = conn;
		try {
			Statement stmt = sqlConn.createStatement();
			ResultSet rs = stmt.executeQuery(queryStr);
			if(rs.next())
				catId = rs.getString(1);
			
			queryStr = "insert into Event values ('";
			queryStr += layer.getEventTitle() + "', '";
			queryStr += layer.getName() + "', ";
			queryStr += catId + ", '";
			queryStr += layer.getEventStart() + "', '";
			queryStr += layer.getEventEnd() + "', '";
			queryStr += layer.getEventDesc() + "')";
			
			stmt.execute(queryStr);
			stmt.close();
		}
		catch(Exception e) {
			System.out.println(queryStr);
			ret = false;
			System.out.println(e);
		}
		return ret;
	}
	
	public static boolean deleteLayer(LayerInfo layer) {
		boolean ret = true;
		String catId = null;
		String queryStr = "select CatId from Category where CatName = '"+layer.getCategory()+"'";
		
		Connection sqlConn = conn;
		try {
			Statement stmt = sqlConn.createStatement();
			ResultSet rs = stmt.executeQuery(queryStr);
			if(rs.next())
				catId = rs.getString(1);
			
			queryStr = "delete from PointOfInterest where Name = '"+layer.getName()+"' and CatId = "+catId;
			
			stmt.execute(queryStr);
			stmt.close();
		}
		catch(Exception e) {
			System.out.println(queryStr);
			ret = false;
			System.out.println(e);
		}
		return ret;
	}
	
	public static boolean updateLayer(LayerInfo layer, String oldName) {
		boolean ret = true;
		String catId = null;
		String queryStr = "select CatId from Category where CatName = '"+layer.getCategory()+"'";
		
		Connection sqlConn = conn;
		try {
			Statement stmt = sqlConn.createStatement();
			ResultSet rs = stmt.executeQuery(queryStr);
			if(rs.next())
				catId = rs.getString(1);
			
			queryStr = "update PointOfInterest set Name = '";
			queryStr += layer.getName() + "', CatId = ";
			queryStr += catId + ", Latitude = ";
			queryStr += layer.getLatitude() + ", Longitude = ";
			queryStr += layer.getLongitude() + ", Address = '";
			queryStr += layer.getAddress() + "', Description = '";
			queryStr += layer.getDescription() + "' where Name = '";
			queryStr += oldName + "' and CatId = " + catId;
			
			stmt.execute(queryStr);
			stmt.close();
		}
		catch(Exception e) {
			System.out.println(queryStr);
			ret = false;
			System.out.println(e);
		}
		return ret;
	}
	
	public static boolean insertData(LayerInfo layer) {
		boolean ret = true;
		String queryStr = "insert into PointOfInterest values ('";
		queryStr += layer.getName() + "', ";
		queryStr += layer.getCategory() + ", ";
		queryStr += layer.getLatitude() + ", ";
		queryStr += layer.getLongitude() + ", '";
		queryStr += layer.getAddress() + "', ' ')";
		
		Connection sqlConn = conn;
		try {
			Statement stmt = sqlConn.createStatement();
			stmt.execute(queryStr);
			stmt.close();
		}
		catch(Exception e) {
			System.out.println(queryStr);
			ret = false;
			System.out.println(e);
		}
		
		return ret;
	}
}