DAO.java

package aei020gre002.info233Oblig4.io;

import java.sql.*;
import java.util.Properties;

/**
 * Created by Anders on 27/04/2015.
 * @author Anders Eide
 */
@SuppressWarnings("JpaQueryApiInspection")
public class DAO {

    private static DAO instance = new DAO();
    private Connection connection;

    private PreparedStatement coursesFromNameStatement;
    private PreparedStatement studentsFromCourseAndSemesterStatement;
    private PreparedStatement studentsFromGradeAndSemesterStatement;

    private String coursesFromNameQueryString =
                "SELECT FirstName, LastName, CourseID"
            +   "FROM `COURSE_RESULTS`, `STUDENTS`"
            +   "WHERE STUDENTS.StudentID = COURSE_RESULTS.StudentID"
            +   "AND STUDENTS.FirstName = ?"
            +   "AND STUDENTS.LastName = ?";

    private String studentsFromCourseAndSemesterQueryString =
                "SELECT FirstName, LastName, STUDENTS.StudentID"
            +   "FROM `COURSE_RESULTS`, `STUDENTS`"
            +   "WHERE STUDENTS.StudentID = COURSE_RESULTS.StudentID"
            +   "AND CourseID = ?"
            +   "AND Semester = ?";

    private String studentsFromGradeAndSemesterQueryString =
                "SELECT FirstName, LastName, CourseID"
            +   "FROM  `COURSE_RESULTS` ,  `STUDENTS`"
            +   "WHERE COURSE_RESULTS.StudentID = STUDENTS.StudentID"
            +   "AND Grade =  ?"
            +   "AND Semester =  ?";


    private DAO(){
        initializeStatements();
        connect();
    }

    private void initializeStatements() {
        //TODO Set up prepared statements
    }

    private void connect() {
        try{
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("Driver class not found, fix your shit mayne");
        }

        String host = "bigfoot.uib.no";
        String dbName = "gr1db";
        int port = 3306;
        String mySqlUrl = "jdbc:mysql://" + host + ":" + port + "/" + dbName;
        System.out.println(mySqlUrl);
        Properties userInfo = new Properties();
        userInfo.put("user", "gr1user");
        userInfo.put("password", "ugr1s");

        String mySqlUrlUnfuckpls = "jdbc:mysql://" + host + ":" + port + "/" + dbName + "?" +
                "user=gr1user&password=ugr1s";
        System.out.println(mySqlUrlUnfuckpls);


        try{
            connection = DriverManager.getConnection(mySqlUrl, userInfo);
            coursesFromNameStatement = connection.prepareStatement(coursesFromNameQueryString);
            studentsFromCourseAndSemesterStatement = connection.prepareStatement(studentsFromCourseAndSemesterQueryString);
            studentsFromGradeAndSemesterStatement = connection.prepareStatement(studentsFromGradeAndSemesterQueryString);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public ResultSet getStudent(String firstName, String lastName){
        try {
            coursesFromNameStatement.setString(1, firstName);
            coursesFromNameStatement.setString(2, lastName);
            return coursesFromNameStatement.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static DAO getInstance(){
        return instance;
    }
}