a

package dbsettings; 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; public class db { 	Connection con = null; 	 	//Setup 	public db(){ 		try { 			Class.forName("org.sqlite.JDBC"); 		} catch (ClassNotFoundException e) { 			e.printStackTrace(); 		} 		try { 			con = DriverManager.getConnection("jdbc:sqlite:database/forums.db"); 		} catch (SQLException e) { 			e.printStackTrace(); 		} 	} 	//Create content 	public void createTables() throws SQLException{ //lai izveidotu jaunu tabulu db 		Statement stat = con.createStatement(); 		stat.execute("CREATE TABLE reply(ID_reply INTEGER PRIMARY KEY AUTOINCREMENT, ID_topic INTEGER, ID_user INTEGER, date TEXT, replycontent TEXT)");		 		} 	 	public void createtrigger() throws SQLException{ 		PreparedStatement st = con.prepareStatement("CREATE TRIGGER delete_reply AFTER DELETE ON topic BEGIN DELETE FROM reply WHERE ID_topic=OLD.ID_topic; END;"); 		int rowsEffected = st.executeUpdate(); 		if(rowsEffected != 0) 		System.out.println("Trigger added"); 	} 	//Testing 	public void getallusers() throws SQLException { 	Statement stat = con.createStatement(); 	ResultSet rezultati = stat.executeQuery("SELECT * FROM userlist"); 	while(rezultati.next()){ 		int idNolasitais = rezultati.getInt("ID_user"); 		String readusername = rezultati.getString("username"); 		String readpassword = rezultati.getString("password"); 		System.out.println("---"); 		System.out.println("ID:" + idNolasitais + " username:" + readusername + " pw:" + readpassword); 	} 	 } 	public void getalluserlevels() throws SQLException { 		Statement stat = con.createStatement(); 		ResultSet rezultati = stat.executeQuery("SELECT * FROM userlevel;"); 		while(rezultati.next()){ 			String readusername = rezultati.getString("username"); 			String readuselevel = rezultati.getString("userlevel"); 			System.out.println("---"); 			System.out.println("username:" + readusername + " level:" + readuselevel); 		} 	} 	public ArrayList<String> getallcategory() throws SQLException{ 		ArrayList<String> result = new ArrayList<>(); 		PreparedStatement st = con.prepareStatement("SELECT * FROM category;"); 		ResultSet rezultati = st.executeQuery(); 		while(rezultati.next()) { 			int tempid = rezultati.getInt("ID_category"); 			String tempname = rezultati.getString("name"); 			String tempuserlevel = rezultati.getString("userlevel"); 			result.add("ID:" + tempid + ",Name:" + tempname + ",Userlevel:" + tempuserlevel); 		} 		return result; 	} 	public void printallreply() throws SQLException{ //Trigera testam 		Statement stat = con.createStatement(); 		ResultSet rezultati = stat.executeQuery("SELECT * FROM reply"); 		while(rezultati.next()){ 			int idTopic = rezultati.getInt("ID_topic"); 			int idReply = rezultati.getInt("ID_reply"); 			System.out.println("Topic id: " + idTopic + " Reply id: "+ idReply); 		} 	} 	//User management 	public boolean adduser(String username, String password) throws SQLException { 		boolean status = false; 		PreparedStatement stcheck = con.prepareStatement("SELECT username FROM userlist WHERE username=?;"); 		stcheck.setString(1, username); 		ResultSet result = stcheck.executeQuery(); 		if(!result.isBeforeFirst()) { 		PreparedStatement st = con.prepareStatement("INSERT INTO userlist(username, password)VALUES(?, ?);"); 		st.setString(1, username); 		st.setString(2, password); 		int rowsEffected = st.executeUpdate(); 		if(rowsEffected != 0) 			status = true; 		else 			status = false; 		return status; 	} 		else 			return status; 	} 	public boolean deluser(int id, String username) throws SQLException { 		boolean status = false; 		PreparedStatement st2 = con.prepareStatement("DELETE FROM userlevel WHERE username=?;"); 		st2.setString(1, username); 		st2.executeUpdate(); 		PreparedStatement st = con.prepareStatement("DELETE FROM userlist WHERE ID_user=?;"); 		st.setInt(1, id); 		int rowsEffected = st.executeUpdate(); 		if(rowsEffected != 0) 			status = true; 		else 			status = false; 		return status; 	} 	public boolean adduserlevel(String username, String userlevel) throws SQLException { 		boolean status = false; 		PreparedStatement lvlcheck = con.prepareStatement("SELECT username FROM userlevel WHERE username=?;"); 		lvlcheck.setString(1, username); 		ResultSet lvlresult = lvlcheck.executeQuery(); 		if(!lvlresult.isBeforeFirst()) { 		PreparedStatement st = con.prepareStatement("INSERT INTO userlevel(username, userlevel)VALUES(?, ?);"); 		st.setString(1, username); 		st.setString(2, userlevel); 		int rowsEffected = st.executeUpdate(); 		if(rowsEffected != 0) 			status = true; 		else 			status = false; 		return status; 	} 		else { 			PreparedStatement st = con.prepareStatement("UPDATE userlevel SET userlevel=? WHERE username=?;"); 			st.setString(1, userlevel); 			st.setString(2, username); 			int rowsEffected = st.executeUpdate(); 			if(rowsEffected != 0) 				status = true; 			else 				status = false; 			return status; 		} 	} 	public boolean changename(String username, String newusername) throws SQLException { 		boolean status = checkIfUserExists(username); 		if(status) { 			PreparedStatement st = con.prepareStatement("UPDATE userlist SET username=? WHERE username=?;"); 			st.setString(1, newusername); 			st.setString(2, username); 			int rowsEffected = st.executeUpdate(); 			if(rowsEffected != 0) { 				String temp = getuserlevel(username); 				if(!temp.equals("")) { 				PreparedStatement st2 = con.prepareStatement("UPDATE userlevel SET username=? WHERE username=?;"); 				st2.setString(1, newusername); 				st2.setString(2, username); 				st2.executeUpdate(); 				} 				return true; 			} 			else 				return false; 		} 		else { 			return false; 		} 	} 	public boolean changepassword(int ID_user, String newpassword) throws SQLException{ 		PreparedStatement st = con.prepareStatement("UPDATE userlist SET password=? WHERE ID_user=?"); 		st.setString(1, newpassword); 		st.setInt(2, ID_user); 		int rowsEffected = st.executeUpdate(); 		if(rowsEffected != 0) 		return true; 		else 		return false; 	} 	public boolean login(String username, String password) throws SQLException{ 		boolean status = false; 		PreparedStatement check = con.prepareStatement("SELECT username, password FROM userlist WHERE username=? and password=?;"); 		check.setString(1, username); 		check.setString(2, password); 		ResultSet result = check.executeQuery(); 		if(result.next()) 			status = true; 		else 			status = false; 		return status; 	} 	//User functions 	public boolean checkIfUserExists(String username) throws SQLException{ 		boolean status = true; 		PreparedStatement check = con.prepareStatement("SELECT username FROM userlist WHERE username=?;"); 		check.setString(1, username); 		ResultSet result = check.executeQuery(); 		if(result.next()) 			status = true; 		else 			status = false; 		return status; 	} 	public String getuserlevel(String username) throws SQLException{ 		PreparedStatement get = con.prepareStatement("SELECT userlevel FROM userlevel WHERE username=?;"); 		get.setString(1, username); 		ResultSet result = get.executeQuery(); 		String userlevelresult = ""; 		while(result.next()) { 			userlevelresult = result.getString("userlevel"); 		} 		return userlevelresult; 	} 	public String getusernamefromid(int ID_user) throws SQLException{ 		PreparedStatement st = con.prepareStatement("SELECT username FROM userlist WHERE ID_user=?"); 		st.setInt(1, ID_user); 		ResultSet rezultati = st.executeQuery(); 		String username = ""; 		while(rezultati.next()) { 			username = rezultati.getString("username"); 		} 		return username; 	} 	public int getuserid(String username) throws SQLException{ 		PreparedStatement st = con.prepareStatement("SELECT ID_user FROM userlist WHERE username=?;"); 		st.setString(1, username); 		ResultSet rezultati = st.executeQuery(); 		int idNolasitais = 0; 		while(rezultati.next()){ 			idNolasitais = rezultati.getInt("ID_user"); 		} 		return idNolasitais; 	} 	//Category functions 	public boolean addcategory(String name, String userlevel) throws SQLException{ 		PreparedStatement st = con.prepareStatement("INSERT INTO category(name, userlevel) VALUES(?, ?)"); 		st.setString(1, name); 		st.setString(2, userlevel); 		int rowsEffected = st.executeUpdate(); 		if(rowsEffected != 0) 		return true; 		else 		return false; 	} 	public boolean deletecategory(String name) throws SQLException{ 		PreparedStatement st = con.prepareStatement("DELETE FROM category WHERE name=?"); 		st.setString(1, name); 		int rowsEffected = st.executeUpdate(); 		if(rowsEffected != 0) 		return true; 		else 		return false; 	} 	public boolean updatecategory(String name, String newname, String userlevel) throws SQLException{ 		PreparedStatement st = con.prepareStatement("UPDATE category SET name=?, userlevel=? WHERE name=?"); 		st.setString(1, newname); 		st.setString(2, userlevel); 		st.setString(3, name); 		int rowsEffected = st.executeUpdate(); 		if(rowsEffected != 0) 		return true; 		else 		return false; 	} 	public ArrayList<Integer> getallcategoryid() throws SQLException{ 		ArrayList<Integer> result = new ArrayList<>(); 		PreparedStatement st = con.prepareStatement("SELECT ID_category FROM category ORDER BY ID_category;"); 		ResultSet rezultati = st.executeQuery(); 		while(rezultati.next()) { 			int tempid = rezultati.getInt("ID_category"); 			result.add(tempid); 		} 		return result; 	} 	public ArrayList<String> getallcategoryname() throws SQLException{ 		ArrayList<String> result = new ArrayList<>(); 		PreparedStatement st = con.prepareStatement("SELECT name FROM category ORDER BY ID_category;"); 		ResultSet rezultati = st.executeQuery(); 		while(rezultati.next()) { 			String tempname = rezultati.getString("name"); 			result.add(tempname); 		} 		return result; 	} 	public ArrayList<String> getallcategoryuserlevel() throws SQLException{ 		ArrayList<String> result = new ArrayList<>(); 		PreparedStatement st = con.prepareStatement("SELECT userlevel FROM category ORDER BY ID_category;"); 		ResultSet rezultati = st.executeQuery(); 		while(rezultati.next()) { 			String tempname = rezultati.getString("userlevel"); 			result.add(tempname); 		} 		return result; 	} 	public String getcategoryname(int ID_category) throws SQLException{ 		PreparedStatement st = con.prepareStatement("SELECT name FROM category WHERE ID_category=?"); 		st.setInt(1, ID_category); 		ResultSet rezultati = st.executeQuery(); 		String tempname = ""; 		while(rezultati.next()) { 			tempname = rezultati.getString("name"); 		} 		return tempname; 	} 	public int getcategoryid(String categoryname) throws SQLException{ 		PreparedStatement st = con.prepareStatement("SELECT ID_category FROM category WHERE name=?"); 		st.setString(1, categoryname); 		ResultSet rezultati = st.executeQuery(); 		int id = 0; 		while(rezultati.next()) { 			id = rezultati.getInt("ID_category"); 		} 		return id; 	} 	public int getcategoryid(int ID_topic) throws SQLException{ 		PreparedStatement st = con.prepareStatement("SELECT ID_category FROM topic WHERE ID_topic=?"); 		st.setInt(1, ID_topic); 		ResultSet rezultati = st.executeQuery(); 		int id = 0; 		while(rezultati.next()){ 			id = rezultati.getInt("ID_category"); 		} 		return id; 	} 	public ArrayList<String> getallcategorynameforuserlevel(String userlevel) throws SQLException{ 		ArrayList<String> result = new ArrayList<>(); 		ResultSet rezultati = null; 		if(userlevel.equals("")){ 			PreparedStatement st = con.prepareStatement("SELECT name FROM category WHERE userlevel='' ORDER BY ID_category;"); 			rezultati = st.executeQuery(); 		} 		else if(userlevel.equals("mod")) { 			PreparedStatement st = con.prepareStatement("SELECT name FROM category WHERE userlevel='mod' or userlevel='' ORDER BY ID_category;"); 			rezultati = st.executeQuery(); 		} 		else if(userlevel.equals("admin")) { 			PreparedStatement st = con.prepareStatement("SELECT name FROM category ORDER BY ID_category;"); 			rezultati = st.executeQuery(); 		} 		while(rezultati.next()) { 			String tempname = rezultati.getString("name"); 			result.add(tempname); 		} 		return result; 	} 	//Topic functions 	public boolean addtopic(int ID_category, int ID_user, String date, int priority, String topicname, String topiccontent) throws SQLException{ 		PreparedStatement st = con.prepareStatement("INSERT INTO topic(ID_category, ID_user, date, priority, topicname, topiccontent) VALUES(?, ?, ?, ?, ?, ?)"); 		st.setInt(1, ID_category); 		st.setInt(2, ID_user); 		st.setString(3, date); 		st.setInt(4, priority); 		st.setString(5, topicname); 		st.setString(6, topiccontent); 		int rowsEffected = st.executeUpdate(); 		if(rowsEffected != 0) 		return true; 		else 		return false; 	} 	public boolean deltopic(int ID_topic) throws SQLException{ 		PreparedStatement st = con.prepareStatement("DELETE FROM topic WHERE ID_topic=?"); 		st.setInt(1, ID_topic); 		int rowsEffected = st.executeUpdate(); 		if(rowsEffected != 0) 			return true; 			else 			return false; 	} 	public ArrayList<String> gettopicnames(int ID_category, String type, int ID_user, ArrayList<Integer> list) throws SQLException{ 		ArrayList<String> result = new ArrayList<>(); 		PreparedStatement st = null; 		ResultSet rezultati = null; 		switch(type) { 		case "profiletopics": 			st = con.prepareStatement("SELECT topicname FROM topic WHERE ID_user=? ORDER BY ID_topic DESC"); 			st.setInt(1, ID_user); 			rezultati = st.executeQuery(); 			while(rezultati.next()) { 				String tempname = rezultati.getString("topicname"); 				result.add(tempname); 			} 		break; 		case "profilereply": 			ArrayList<Integer> topics = new ArrayList<>(); 			PreparedStatement st2 = con.prepareStatement("SELECT ID_topic FROM reply WHERE ID_user=? ORDER BY ID_topic DESC"); 			st2.setInt(1, ID_user); 			ResultSet rezultati2 = st2.executeQuery(); 			PreparedStatement st3 = con.prepareStatement("SELECT topicname FROM topic WHERE ID_topic=? ORDER BY ID_topic DESC"); 			while(rezultati2.next()) { 				int id = rezultati2.getInt("ID_topic"); 				st3.setInt(1, id); 				boolean verify = false; 				for(int i = 0; i < topics.size(); i++) { 					if(topics.get(i) == id) { 						verify = true; 						break; 					} 				} 				if(!verify) { 				topics.add(id); 				rezultati = st3.executeQuery(); 				while(rezultati.next()) { 					String tempname = rezultati.getString("topicname"); 					result.add(tempname); 				} 			} 			} 		break; 		case "normal": 			st = con.prepareStatement("SELECT topicname FROM topic WHERE ID_category=? ORDER BY ID_topic DESC"); 			st.setInt(1, ID_category); 			rezultati = st.executeQuery(); 			while(rezultati.next()) { 				String tempname = rezultati.getString("topicname"); 				result.add(tempname); 			} 		break; 		case "search": 			for(int i = 0; i < list.size(); i++) { 			st = con.prepareStatement("SELECT topicname FROM topic WHERE ID_topic=? ORDER BY ID_topic DESC"); 			st.setInt(1, list.get(i)); 			rezultati = st.executeQuery(); 			while(rezultati.next()) { 				String tempname = rezultati.getString("topicname"); 				result.add(tempname); 			} 			} 		break; 		default: 			st = con.prepareStatement("SELECT topicname FROM topic WHERE ID_category=? ORDER BY ID_topic DESC"); 			st.setInt(1, ID_category); 			rezultati = st.executeQuery(); 			while(rezultati.next()) { 				String tempname = rezultati.getString("topicname"); 				result.add(tempname); 			} 		break; 		} 		return result; 	} 	public ArrayList<String> gettopicauthors(int ID_category, String type, int ID_user, ArrayList<Integer> list) throws SQLException{ 		ArrayList<String> result = new ArrayList<>(); 		PreparedStatement st = null; 		ResultSet rezultati = null; 		switch(type) { 		case "profiletopics": 			st = con.prepareStatement("SELECT ID_user FROM topic WHERE ID_user=? ORDER BY ID_topic DESC"); 			st.setInt(1, ID_user); 			rezultati = st.executeQuery(); 			while(rezultati.next()) { 				int tempnameid = rezultati.getInt("ID_user"); 				String tempname = getusernamefromid(tempnameid); 				result.add(tempname); 			} 		break; 		case "profilereply": 			ArrayList<Integer> topics = new ArrayList<>(); 			PreparedStatement st2 = con.prepareStatement("SELECT ID_topic FROM reply WHERE ID_user=? ORDER BY ID_topic DESC"); 			st2.setInt(1, ID_user); 			ResultSet rezultati2 = st2.executeQuery(); 			PreparedStatement st3 = con.prepareStatement("SELECT ID_user FROM topic WHERE ID_topic=? ORDER BY ID_topic DESC"); 			while(rezultati2.next()) { 				int id = rezultati2.getInt("ID_topic"); 				st3.setInt(1, id); 				boolean verify = false; 				for(int i = 0; i < topics.size(); i++) { 					if(topics.get(i) == id) { 						verify = true; 						break; 					} 				} 				if(!verify) { 				topics.add(id); 				rezultati = st3.executeQuery(); 				while(rezultati.next()) { 					int tempnameid = rezultati.getInt("ID_user"); 					String tempname = getusernamefromid(tempnameid); 					result.add(tempname); 				} 			} 			} 		break; 		case "normal": 			st = con.prepareStatement("SELECT ID_user FROM topic WHERE ID_category=? ORDER BY ID_topic DESC"); 			st.setInt(1, ID_category); 			rezultati = st.executeQuery(); 			while(rezultati.next()) { 				int tempnameid = rezultati.getInt("ID_user"); 				String tempname = getusernamefromid(tempnameid); 				result.add(tempname); 			} 		break; 		case "search": 			for(int i = 0; i < list.size(); i++) { 			st = con.prepareStatement("SELECT ID_user FROM topic WHERE ID_topic=? ORDER BY ID_topic DESC"); 			st.setInt(1, list.get(i)); 			rezultati = st.executeQuery(); 			while(rezultati.next()) { 				int tempnameid = rezultati.getInt("ID_user"); 				String tempname = getusernamefromid(tempnameid); 				result.add(tempname); 			} 			} 		break; 		default: 			st = con.prepareStatement("SELECT ID_user FROM topic WHERE ID_category=? ORDER BY ID_topic DESC"); 			st.setInt(1, ID_category); 			rezultati = st.executeQuery(); 			while(rezultati.next()) { 				int tempnameid = rezultati.getInt("ID_user"); 				String tempname = getusernamefromid(tempnameid); 				result.add(tempname); 			} 		break; 		} 		return result; 	} 	public ArrayList<String> gettopicdates(int ID_category, String type, int ID_user, ArrayList<Integer> list) throws SQLException{ 		ArrayList<String> result = new ArrayList<>(); 		ResultSet rezultati = null; 		PreparedStatement st = null; 		switch(type) { 		case "profiletopics": 			st = con.prepareStatement("SELECT date FROM topic WHERE ID_user=? ORDER BY ID_topic DESC"); 			st.setInt(1, ID_user); 			rezultati = st.executeQuery(); 			while(rezultati.next()) { 				String tempname = rezultati.getString("date"); 				result.add(tempname); 			} 		break; 		case "profilereply": 			ArrayList<Integer> topics = new ArrayList<>(); 			PreparedStatement st2 = con.prepareStatement("SELECT ID_topic FROM reply WHERE ID_user=? ORDER BY ID_topic DESC"); 			st2.setInt(1, ID_user); 			ResultSet rezultati2 = st2.executeQuery(); 			PreparedStatement st3 = con.prepareStatement("SELECT date FROM topic WHERE ID_topic=? ORDER BY ID_topic DESC"); 			while(rezultati2.next()) { 				int id = rezultati2.getInt("ID_topic"); 				st3.setInt(1, id); 				boolean verify = false; 				for(int i = 0; i < topics.size(); i++) { 					if(topics.get(i) == id) { 						verify = true; 						break; 					} 				} 				if(!verify) { 				topics.add(id); 				rezultati = st3.executeQuery(); 				while(rezultati.next()) { 					String tempname = rezultati.getString("date"); 					result.add(tempname); 				} 			} 			} 		break; 		case "normal": 			st = con.prepareStatement("SELECT date FROM topic WHERE ID_category=? ORDER BY ID_topic DESC"); 			st.setInt(1, ID_category); 			rezultati = st.executeQuery(); 			while(rezultati.next()) { 				String tempname = rezultati.getString("date"); 				result.add(tempname); 			} 		break; 		case "search": 			for(int i = 0; i < list.size(); i++) { 			st = con.prepareStatement("SELECT date FROM topic WHERE ID_topic=? ORDER BY ID_topic DESC"); 			st.setInt(1, list.get(i)); 			rezultati = st.executeQuery(); 			while(rezultati.next()) { 				String tempname = rezultati.getString("date"); 				result.add(tempname); 			} 			} 		break; 		default: 			st = con.prepareStatement("SELECT date FROM topic WHERE ID_category=? ORDER BY ID_topic DESC"); 			st.setInt(1, ID_category); 			rezultati = st.executeQuery(); 			while(rezultati.next()) { 				String tempname = rezultati.getString("date"); 				result.add(tempname); 			} 		break; 		} 		 		return result; 	} 	public ArrayList<Integer> gettopicid(int ID_category, String type, int ID_user, ArrayList<Integer> list) throws SQLException{ 		ArrayList<Integer> result = new ArrayList<>(); 		PreparedStatement st = null; 		ResultSet rezultati = null; 		switch(type) { 		case "profiletopics": 			st = con.prepareStatement("SELECT ID_topic FROM topic WHERE ID_user=? ORDER BY ID_topic DESC"); 			st.setInt(1, ID_user); 			rezultati = st.executeQuery(); 			while(rezultati.next()) { 				int tempid = rezultati.getInt("ID_topic"); 				result.add(tempid); 			} 		break; 		case "profilereply": 			ArrayList<Integer> topics = new ArrayList<>(); 			PreparedStatement st2 = con.prepareStatement("SELECT ID_topic FROM reply WHERE ID_user=? ORDER BY ID_topic DESC"); 			st2.setInt(1, ID_user); 			ResultSet rezultati2 = st2.executeQuery(); 			PreparedStatement st3 = con.prepareStatement("SELECT ID_topic FROM topic WHERE ID_topic=? ORDER BY ID_topic DESC"); 			while(rezultati2.next()) { 				int id = rezultati2.getInt("ID_topic"); 				st3.setInt(1, id); 				boolean verify = false; 				for(int i = 0; i < topics.size(); i++) { 					if(topics.get(i) == id) { 						verify = true; 						break; 					} 				} 				if(!verify) { 				topics.add(id); 				rezultati = st3.executeQuery(); 				while(rezultati.next()) { 					int tempid = rezultati.getInt("ID_topic"); 					result.add(tempid); 				} 			} 			} 		break; 		case "normal": 			st = con.prepareStatement("SELECT ID_topic FROM topic WHERE ID_category=? ORDER BY ID_topic DESC"); 			st.setInt(1, ID_category); 			rezultati = st.executeQuery(); 			while(rezultati.next()) { 				int tempid = rezultati.getInt("ID_topic"); 				result.add(tempid); 			} 		break; 		case "search": 			for(int i = 0; i < list.size(); i++) { 			st = con.prepareStatement("SELECT ID_topic FROM topic WHERE ID_topic=? ORDER BY ID_topic DESC"); 			st.setInt(1, list.get(i)); 			rezultati = st.executeQuery(); 			while(rezultati.next()) { 				int tempid = rezultati.getInt("ID_topic"); 				result.add(tempid); 			} 			} 		break; 		default: 			st = con.prepareStatement("SELECT ID_topic FROM topic WHERE ID_category=? ORDER BY ID_topic DESC"); 			st.setInt(1, ID_category); 			rezultati = st.executeQuery(); 			while(rezultati.next()) { 				int tempid = rezultati.getInt("ID_topic"); 				result.add(tempid); 			} 		break; 		} 		 		return result; 	} 	public ArrayList<String> getalltopiccategory(String type, int ID_user, ArrayList<Integer> list) throws SQLException{ 		ArrayList<String> result = new ArrayList<>(); 		ResultSet rezultati = null; 		PreparedStatement st = null; 		switch(type) { 		case "profiletopics": 			st = con.prepareStatement("SELECT ID_category FROM topic WHERE ID_user=? ORDER BY ID_topic DESC"); 			st.setInt(1, ID_user); 			rezultati = st.executeQuery(); 			while(rezultati.next()) { 				int catId = rezultati.getInt("ID_category"); 				String tempname = getcategoryname(catId); 				result.add(tempname); 			} 		break; 		case "profilereply": 			ArrayList<Integer> topics = new ArrayList<>(); 			PreparedStatement st2 = con.prepareStatement("SELECT ID_topic FROM reply WHERE ID_user=? ORDER BY ID_topic DESC"); 			st2.setInt(1, ID_user); 			ResultSet rezultati2 = st2.executeQuery(); 			PreparedStatement st3 = con.prepareStatement("SELECT ID_category FROM topic WHERE ID_topic=? ORDER BY ID_topic DESC"); 			while(rezultati2.next()) { 				int id = rezultati2.getInt("ID_topic"); 				st3.setInt(1, id); 				boolean verify = false; 				for(int i = 0; i < topics.size(); i++) { 					if(topics.get(i) == id) { 						verify = true; 						break; 					} 				} 				if(!verify) { 				topics.add(id); 				rezultati = st3.executeQuery(); 				while(rezultati.next()) { 					int catId = rezultati.getInt("ID_category"); 					String tempname = getcategoryname(catId); 					result.add(tempname); 				} 			} 			} 		break; 		case "search": 			for(int i = 0; i < list.size(); i++) { 			st = con.prepareStatement("SELECT ID_category FROM topic WHERE ID_topic=? ORDER BY ID_topic DESC"); 			st.setInt(1, list.get(i)); 			rezultati = st.executeQuery(); 			while(rezultati.next()) { 				int catId = rezultati.getInt("ID_category"); 				String tempname = getcategoryname(catId); 				result.add(tempname); 			} 			} 		break; 		default: 			for(int i = 0; i < list.size(); i++) { 			st = con.prepareStatement("SELECT ID_category FROM topic WHERE ID_topic=? ORDER BY ID_topic DESC"); 			st.setInt(1, list.get(i)); 			rezultati = st.executeQuery(); 			while(rezultati.next()) { 				int catId = rezultati.getInt("ID_category"); 				String tempname = getcategoryname(catId); 				result.add(tempname); 			} 			} 		break; 		} 		return result; 	} 	public String gettopicname(int ID_topic) throws SQLException{ 		PreparedStatement st = con.prepareStatement("SELECT topicname FROM topic WHERE ID_topic=?"); 		st.setInt(1, ID_topic); 		ResultSet rezultati = st.executeQuery(); 		String name = ""; 		while(rezultati.next()) { 			name = rezultati.getString("topicname"); 		} 		return name; 	} 	public String gettopicmessage(int ID_topic) throws SQLException{ 		PreparedStatement st = con.prepareStatement("SELECT topiccontent FROM topic WHERE ID_topic=?"); 		st.setInt(1, ID_topic); 		ResultSet rezultati = st.executeQuery(); 		String msgcontent = ""; 		while(rezultati.next()) { 			msgcontent = rezultati.getString("topiccontent"); 		} 		return msgcontent; 	} 	public String gettopicauthor(int ID_topic) throws SQLException{ 		PreparedStatement st = con.prepareStatement("SELECT ID_user FROM topic WHERE ID_topic=?"); 		st.setInt(1, ID_topic); 		ResultSet rezultati = st.executeQuery(); 		int id = 0; 		while(rezultati.next()) { 			id = rezultati.getInt("ID_user"); 		} 		String author = getusernamefromid(id); 		return author; 	} 	public String gettopicdate(int ID_topic) throws SQLException{ 		PreparedStatement st = con.prepareStatement("SELECT date FROM topic WHERE ID_topic=?"); 		st.setInt(1, ID_topic); 		ResultSet rezultati = st.executeQuery(); 		String date = ""; 		while(rezultati.next()) { 			date = rezultati.getString("date"); 		} 		return date; 	} 	public int gettopicpriority(int ID_topic) throws SQLException{ 		PreparedStatement st = con.prepareStatement("SELECT priority FROM topic WHERE ID_topic=?"); 		st.setInt(1, ID_topic); 		ResultSet rezultati = st.executeQuery(); 		int priority = 0; 		while(rezultati.next()) { 			priority = rezultati.getInt("priority"); 		} 		return priority; 	} 	public int gettopicidbynameandcontent(String topicname, String date) throws SQLException{ 		PreparedStatement st = con.prepareStatement("SELECT ID_topic FROM topic WHERE topicname=? AND date=?"); 		st.setString(1, topicname); 		st.setString(2, date); 		int id = 0; 		ResultSet rezultati = st.executeQuery(); 		while(rezultati.next()) { 			id = rezultati.getInt("ID_topic"); 		} 		return id; 	} 	public boolean changetopicpriority(int ID_topic, int priority) throws SQLException { 		PreparedStatement st = con.prepareStatement("UPDATE topic SET priority=? WHERE ID_topic=?"); 		st.setInt(1, priority); 		st.setInt(2, ID_topic); 		int rowsEffected = st.executeUpdate(); 		if(rowsEffected != 0) 		return true; 		else 		return false; 	} 	public boolean edittopiccontent(int ID_topic, String newcontent) throws SQLException{ 		PreparedStatement st = con.prepareStatement("UPDATE topic SET topiccontent=? WHERE ID_topic=?"); 		st.setString(1, newcontent); 		st.setInt(2, ID_topic); 		int rowsEffected = st.executeUpdate(); 		if(rowsEffected != 0) 		return true; 		else 		return false; 	} 	//Reply functions 	public boolean addreply(int ID_topic, int ID_user, String date, String replycontent) throws SQLException { 		PreparedStatement st = con.prepareStatement("INSERT INTO reply(ID_topic, ID_user, date, replycontent) VALUES(?, ?, ?, ?)"); 		st.setInt(1, ID_topic); 		st.setInt(2, ID_user); 		st.setString(3, date); 		st.setString(4, replycontent); 		int rowsEffected = st.executeUpdate(); 		if(rowsEffected != 0) 		return true; 		else 		return false; 	}e 	public boolean deletereply(int ID_reply) throws SQLException{ 		PreparedStatement st = con.prepareStatement("DELETE FROM reply WHERE ID_reply=?"); 		st.setInt(1, ID_reply); 		int rowsEffected = st.executeUpdate(); 		if(rowsEffected != 0) 			return true; 			else 			return false; 	} 	public boolean editreplycontent(int ID_reply, String newcontent) throws SQLException{ 		PreparedStatement st = con.prepareStatement("UPDATE reply SET replycontent=? WHERE ID_reply=?"); 		st.setString(1, newcontent); 		st.setInt(2, ID_reply); 		int rowsEffected = st.executeUpdate(); 		if(rowsEffected != 0) 		return true; 		else 		return false; 	} 	public ArrayList<Integer> getallreplyid(int ID_topic) throws SQLException{ 		ArrayList<Integer> ids = new ArrayList<>(); 		PreparedStatement st = con.prepareStatement("SELECT ID_reply FROM reply WHERE ID_topic=?"); 		st.setInt(1, ID_topic); 		ResultSet rezultati = st.executeQuery(); 		while(rezultati.next()) { 			ids.add(rezultati.getInt("ID_reply")); 		} 		return ids; 	} 	public String getreplyauthor(int ID_reply) throws SQLException{ 		PreparedStatement st = con.prepareStatement("SELECT ID_user FROM reply WHERE ID_reply=?"); 		st.setInt(1, ID_reply); 		ResultSet rezultati = st.executeQuery(); 		int id = 0; 		while(rezultati.next()) { 			id = rezultati.getInt("ID_user"); 		} 		String username = ""; 		username = getusernamefromid(id); 		return username; 	} 	public String getreplycontent(int ID_reply) throws SQLException{ 		PreparedStatement st = con.prepareStatement("SELECT replycontent FROM reply WHERE ID_reply=?"); 		st.setInt(1, ID_reply); 		ResultSet rezultati = st.executeQuery(); 		String content = ""; 		while(rezultati.next()) { 			content = rezultati.getString("replycontent"); 		} 		return content; 	} 	public String getreplydate(int ID_reply) throws SQLException{ 		PreparedStatement st = con.prepareStatement("SELECT date FROM reply WHERE ID_reply=?"); 		st.setInt(1, ID_reply); 		ResultSet rezultati = st.executeQuery(); 		String date = ""; 		while(rezultati.next()) { 			date = rezultati.getString("date"); 		} 		return date; 	} 	//Search function 	public ArrayList<Integer> searchtopics(String searchstring, String userlevel) throws SQLException{ 		ArrayList<Integer> validtopicids = new ArrayList<>(); 		ArrayList<Integer> categoryids = new ArrayList<>(); 		ArrayList<String> categorynames = new ArrayList<>(); 		int possiblenameid = getuserid(searchstring); 		categorynames = getallcategorynameforuserlevel(userlevel); 		for(int i = 0; i < categorynames.size(); i++) { 			categoryids.add(getcategoryid(categorynames.get(i))); 		} 		for(int i = 0; i < categoryids.size(); i++) { 				PreparedStatement st = con.prepareStatement("SELECT DISTINCT ID_topic FROM topic WHERE ID_category=? AND (topicname LIKE ? OR topiccontent LIKE ? OR ID_user=?) ORDER BY ID_topic DESC;"); 				st.setInt(1, categoryids.get(i)); 				st.setString(2, "%" + searchstring + "%"); 				st.setString(3, "%" + searchstring + "%"); 				st.setInt(4, possiblenameid); 				ResultSet rezultati = st.executeQuery(); 				while(rezultati.next()) { 					boolean verify = false; 					int id = rezultati.getInt("ID_topic"); 					for(int j = 0; j < validtopicids.size(); j++) { 						if(id == validtopicids.get(j)) { 							verify = true; 							break; 						} 					} 					if(!verify) { 						validtopicids.add(id); 					} 				} 		} 		return validtopicids; 	} }