Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- public class Database {
- private Connection connection = null;
- private String[] list;
- private String name;
- public Database(){
- }
- public void connection(){
- String dbname = "m_16_1102394m";
- String username = "m_16_1102394m";
- String password = "1102394m";
- try
- {
- connection = DriverManager.getConnection("jdbc:postgresql://yacata.dcs.gla.ac.uk:5432/"+dbname,username,password);
- }
- catch(SQLException e)
- {
- System.err.println("Connection Failed!");
- e.printStackTrace();
- return;
- }
- if(connection != null)
- {
- System.out.println("Connection successful");
- }else
- {
- System.err.println("Failed to make connection");
- }
- }
- public String[] getGymCourse()
- {
- Statement stmt = null;
- //String query = "UPDATE instructors SET fname='finding', lname='nemo' WHERE instructorid=2";
- String query = "SELECT * FROM gymcourse";
- try{
- stmt = connection.createStatement();
- ResultSet rs = stmt.executeQuery(query);
- int i=0;
- list = new String[10];
- //the next method of ResultSet allows you to iterate through the results
- while (rs.next())
- {
- // the getString method of the ResultSet object allows you to access the value for
- ///the given column name for the current row in the result set as a String. If the
- //value is an integer you can use getInt(“col_name”)
- name = "course";
- String course_name = rs.getString("name");
- list[i] = course_name;
- i++;
- }
- }
- catch (SQLException e )
- {
- e.printStackTrace();
- System.err.println("error executing query " + query);
- }
- return list;
- }
- public int getBookings(String course)
- {
- Statement stmt = null;
- int count=0;
- //String query = "UPDATE instructors SET fname='finding', lname='nemo' WHERE instructorid=2";
- String query = "select * from gymcourse as g inner join coursebookings as c on g.courseid = c.course where name='"+course+"'";
- try{
- stmt = connection.createStatement();
- ResultSet rs = stmt.executeQuery(query);
- //the next method of ResultSet allows you to iterate through the results
- while (rs.next())
- {
- int book = rs.getInt("member");
- count+=1;
- }
- }
- catch (SQLException e )
- {
- e.printStackTrace();
- System.err.println("error executing query " + query);
- }
- return count;
- }
- public int getCapacity(String course)
- {
- Statement stmt = null;
- int c=-1;
- //String query = "UPDATE instructors SET fname='finding', lname='nemo' WHERE instructorid=2";
- String query = "SELECT capacity FROM gymcourse WHERE name='"+course+"'";;
- try{
- stmt = connection.createStatement();
- ResultSet rs = stmt.executeQuery(query);
- //the next method of ResultSet allows you to iterate through the results
- while (rs.next())
- {
- c = rs.getInt("capacity");
- }
- }
- catch (SQLException e )
- {
- e.printStackTrace();
- System.err.println("error executing query " + query);
- }
- return c;
- }
- public String[][] viewMembers(String course)
- {
- String[][] bookings = new String[40][2];
- Statement stmt = null;
- int count = 0;
- //String query = "UPDATE instructors SET fname='finding', lname='nemo' WHERE instructorid=2";
- 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+"'";;
- try{
- stmt = connection.createStatement();
- ResultSet rs = stmt.executeQuery(query);
- //the next method of ResultSet allows you to iterate through the results
- while (rs.next())
- {
- String member_name = rs.getString("fname");
- String member_lname = rs.getString("lname");
- String member = member_name+" "+member_lname;
- int id = rs.getInt("memberid");
- bookings[count][0]= member;
- bookings[count][1]=""+id;
- System.out.println(member);
- count++;
- }
- }
- catch (SQLException e )
- {
- e.printStackTrace();
- System.err.println("error executing query " + query);
- }
- return bookings;
- }
- public int getCourseID(String course)
- {
- Statement stmt = null;
- int courseid = -1;
- //String query = "UPDATE instructors SET fname='finding', lname='nemo' WHERE instructorid=2";
- String query = "SELECT * FROM gymcourse WHERE name='"+course+"'";;
- try{
- stmt = connection.createStatement();
- ResultSet rs = stmt.executeQuery(query);
- //the next method of ResultSet allows you to iterate through the results
- while (rs.next())
- {
- courseid = rs.getInt("courseid");
- }
- }
- catch (SQLException e )
- {
- e.printStackTrace();
- System.err.println("error executing query " + query);
- }
- return courseid;
- }
- public void addBooking(int courseid, String id)
- {
- int memid = Integer.valueOf(id);
- Statement stmt = null;
- String instructor ="";
- //String query = "UPDATE instructors SET fname='finding', lname='nemo' WHERE instructorid=2";
- String update = "INSERT INTO coursebookings VALUES("+memid+", "+courseid+")";
- try{
- stmt = connection.createStatement();
- int rs = stmt.executeUpdate(update);
- //the next method of ResultSet allows you to iterate through the results
- }
- catch (SQLException e )
- {
- e.printStackTrace();
- System.err.println("error executing update " + update);
- }
- }
- public boolean checkCapacity(String course)
- {
- Statement stmt = null;
- int count = 0;
- //String query = "UPDATE instructors SET fname='finding', lname='nemo' WHERE instructorid=2";
- 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+"'";
- try{
- stmt = connection.createStatement();
- ResultSet rs = stmt.executeQuery(query);
- //the next method of ResultSet allows you to iterate through the results
- while (rs.next())
- {
- count+=1;
- }
- }
- catch (SQLException e )
- {
- e.printStackTrace();
- System.err.println("error executing query " + query);
- }
- if(count == getCapacity(course))
- return false;
- else
- return true;
- }
- public String getInstructor(String course)
- {
- Statement stmt = null;
- String instructor ="";
- //String query = "UPDATE instructors SET fname='finding', lname='nemo' WHERE instructorid=2";
- String query = "SELECT fname, lname FROM instructors AS i INNER JOIN gymcourse AS g ON i.instructorid=g.instructor WHERE name='"+course+"'";
- try{
- stmt = connection.createStatement();
- ResultSet rs = stmt.executeQuery(query);
- //the next method of ResultSet allows you to iterate through the results
- while (rs.next())
- {
- String instructor_name = rs.getString("fname");
- String instructor_lname = rs.getString("lname");
- instructor = instructor_name+" "+instructor_lname;
- }
- }
- catch (SQLException e )
- {
- e.printStackTrace();
- System.err.println("error executing query " + query);
- }
- return instructor;
- }
- public String getname(){
- return name;
- }
- public String[] getThings(){
- return list;
- }
- public void close()
- {
- try
- {
- connection.close();
- System.out.println("Connection closed");
- }
- catch(SQLException e)
- {
- e.printStackTrace();
- System.out.println("Connection could not be closed - SQL exception");
- }
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement