Advertisement
Guest User

Database

a guest
Nov 24th, 2016
91
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 7.52 KB | None | 0 0
  1. import java.sql.Connection;
  2. import java.sql.DriverManager;
  3. import java.sql.ResultSet;
  4. import java.sql.SQLException;
  5. import java.sql.Statement;
  6.  
  7.  
  8.  
  9. public class Database {
  10.  
  11. private Connection connection = null;
  12. private String[] list;
  13. private String name;
  14.  
  15. public Database(){
  16.  
  17. }
  18.  
  19. public void connection(){
  20. String dbname = "m_16_1102394m";
  21. String username = "m_16_1102394m";
  22. String password = "1102394m";
  23. try
  24. {
  25. connection = DriverManager.getConnection("jdbc:postgresql://yacata.dcs.gla.ac.uk:5432/"+dbname,username,password);
  26.  
  27. }
  28. catch(SQLException e)
  29. {
  30. System.err.println("Connection Failed!");
  31. e.printStackTrace();
  32. return;
  33. }
  34. if(connection != null)
  35. {
  36. System.out.println("Connection successful");
  37. }else
  38. {
  39. System.err.println("Failed to make connection");
  40. }
  41. }
  42.  
  43.  
  44. public String[] getGymCourse()
  45. {
  46.  
  47. Statement stmt = null;
  48. //String query = "UPDATE instructors SET fname='finding', lname='nemo' WHERE instructorid=2";
  49. String query = "SELECT * FROM gymcourse";
  50. try{
  51. stmt = connection.createStatement();
  52. ResultSet rs = stmt.executeQuery(query);
  53.  
  54. int i=0;
  55. list = new String[10];
  56. //the next method of ResultSet allows you to iterate through the results
  57. while (rs.next())
  58. {
  59. // the getString method of the ResultSet object allows you to access the value for
  60. ///the given column name for the current row in the result set as a String. If the
  61. //value is an integer you can use getInt(“col_name”)
  62. name = "course";
  63.  
  64.  
  65. String course_name = rs.getString("name");
  66. list[i] = course_name;
  67. i++;
  68. }
  69. }
  70.  
  71. catch (SQLException e )
  72. {
  73. e.printStackTrace();
  74. System.err.println("error executing query " + query);
  75. }
  76. return list;
  77. }
  78.  
  79. public int getBookings(String course)
  80. {
  81. Statement stmt = null;
  82. int count=0;
  83. //String query = "UPDATE instructors SET fname='finding', lname='nemo' WHERE instructorid=2";
  84. String query = "select * from gymcourse as g inner join coursebookings as c on g.courseid = c.course where name='"+course+"'";
  85. try{
  86. stmt = connection.createStatement();
  87. ResultSet rs = stmt.executeQuery(query);
  88.  
  89. //the next method of ResultSet allows you to iterate through the results
  90. while (rs.next())
  91. {
  92. int book = rs.getInt("member");
  93. count+=1;
  94. }
  95. }
  96.  
  97. catch (SQLException e )
  98. {
  99. e.printStackTrace();
  100. System.err.println("error executing query " + query);
  101. }
  102. return count;
  103. }
  104.  
  105. public int getCapacity(String course)
  106. {
  107. Statement stmt = null;
  108. int c=-1;
  109. //String query = "UPDATE instructors SET fname='finding', lname='nemo' WHERE instructorid=2";
  110. String query = "SELECT capacity FROM gymcourse WHERE name='"+course+"'";;
  111. try{
  112. stmt = connection.createStatement();
  113. ResultSet rs = stmt.executeQuery(query);
  114.  
  115. //the next method of ResultSet allows you to iterate through the results
  116. while (rs.next())
  117. {
  118. c = rs.getInt("capacity");
  119. }
  120. }
  121.  
  122. catch (SQLException e )
  123. {
  124. e.printStackTrace();
  125. System.err.println("error executing query " + query);
  126. }
  127. return c;
  128. }
  129.  
  130. public String[][] viewMembers(String course)
  131. {
  132.  
  133. String[][] bookings = new String[40][2];
  134. Statement stmt = null;
  135. int count = 0;
  136. //String query = "UPDATE instructors SET fname='finding', lname='nemo' WHERE instructorid=2";
  137. String query = "SELECT * FROM gymcourse AS g INNER JOIN coursebookings AS c ON c.course=g.courseid INNER JOIN members AS m ON c.member=m.memberid WHERE name='"+course+"'";;
  138. try{
  139. stmt = connection.createStatement();
  140. ResultSet rs = stmt.executeQuery(query);
  141.  
  142. //the next method of ResultSet allows you to iterate through the results
  143. while (rs.next())
  144. {
  145. String member_name = rs.getString("fname");
  146. String member_lname = rs.getString("lname");
  147. String member = member_name+" "+member_lname;
  148. int id = rs.getInt("memberid");
  149. bookings[count][0]= member;
  150. bookings[count][1]=""+id;
  151. System.out.println(member);
  152. count++;
  153.  
  154. }
  155. }
  156.  
  157. catch (SQLException e )
  158. {
  159. e.printStackTrace();
  160. System.err.println("error executing query " + query);
  161. }
  162. return bookings;
  163.  
  164. }
  165.  
  166. public int getCourseID(String course)
  167. {
  168. Statement stmt = null;
  169. int courseid = -1;
  170. //String query = "UPDATE instructors SET fname='finding', lname='nemo' WHERE instructorid=2";
  171. String query = "SELECT * FROM gymcourse WHERE name='"+course+"'";;
  172. try{
  173. stmt = connection.createStatement();
  174. ResultSet rs = stmt.executeQuery(query);
  175.  
  176. //the next method of ResultSet allows you to iterate through the results
  177. while (rs.next())
  178. {
  179. courseid = rs.getInt("courseid");
  180.  
  181. }
  182. }
  183.  
  184. catch (SQLException e )
  185. {
  186. e.printStackTrace();
  187. System.err.println("error executing query " + query);
  188. }
  189. return courseid;
  190. }
  191.  
  192. public void addBooking(int courseid, String id)
  193. {
  194. int memid = Integer.valueOf(id);
  195. Statement stmt = null;
  196. String instructor ="";
  197. //String query = "UPDATE instructors SET fname='finding', lname='nemo' WHERE instructorid=2";
  198. String update = "INSERT INTO coursebookings VALUES("+memid+", "+courseid+")";
  199. try{
  200. stmt = connection.createStatement();
  201. int rs = stmt.executeUpdate(update);
  202.  
  203. //the next method of ResultSet allows you to iterate through the results
  204.  
  205. }
  206. catch (SQLException e )
  207. {
  208. e.printStackTrace();
  209. System.err.println("error executing update " + update);
  210. }
  211.  
  212. }
  213.  
  214. public boolean checkCapacity(String course)
  215. {
  216. Statement stmt = null;
  217. int count = 0;
  218. //String query = "UPDATE instructors SET fname='finding', lname='nemo' WHERE instructorid=2";
  219. String query = "SELECT member FROM gymcourse AS g INNER JOIN coursebookings AS c ON c.course=g.courseid INNER JOIN members AS m ON c.member=m.memberid WHERE name='"+course+"'";
  220. try{
  221. stmt = connection.createStatement();
  222. ResultSet rs = stmt.executeQuery(query);
  223.  
  224. //the next method of ResultSet allows you to iterate through the results
  225. while (rs.next())
  226. {
  227. count+=1;
  228.  
  229.  
  230. }
  231. }
  232.  
  233. catch (SQLException e )
  234. {
  235. e.printStackTrace();
  236. System.err.println("error executing query " + query);
  237. }
  238. if(count == getCapacity(course))
  239. return false;
  240. else
  241. return true;
  242. }
  243.  
  244.  
  245.  
  246.  
  247. public String getInstructor(String course)
  248. {
  249. Statement stmt = null;
  250. String instructor ="";
  251. //String query = "UPDATE instructors SET fname='finding', lname='nemo' WHERE instructorid=2";
  252. String query = "SELECT fname, lname FROM instructors AS i INNER JOIN gymcourse AS g ON i.instructorid=g.instructor WHERE name='"+course+"'";
  253. try{
  254. stmt = connection.createStatement();
  255. ResultSet rs = stmt.executeQuery(query);
  256.  
  257. //the next method of ResultSet allows you to iterate through the results
  258. while (rs.next())
  259. {
  260. String instructor_name = rs.getString("fname");
  261. String instructor_lname = rs.getString("lname");
  262. instructor = instructor_name+" "+instructor_lname;
  263. }
  264. }
  265.  
  266. catch (SQLException e )
  267. {
  268. e.printStackTrace();
  269. System.err.println("error executing query " + query);
  270. }
  271. return instructor;
  272. }
  273. public String getname(){
  274. return name;
  275. }
  276.  
  277. public String[] getThings(){
  278. return list;
  279. }
  280. public void close()
  281. {
  282. try
  283. {
  284. connection.close();
  285. System.out.println("Connection closed");
  286. }
  287. catch(SQLException e)
  288. {
  289. e.printStackTrace();
  290. System.out.println("Connection could not be closed - SQL exception");
  291. }
  292. }
  293. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement