DAL TISDAG

// DAL ------------------------------------------------

package DB2package;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
//import java.util.ArrayList;
import java.sql.SQLException;
import java.sql.Statement;

public class DAL {

    private Course course;
    private String ccode;
    private String cname;
    private String caddress;
    private Student student;
    private String pnr;
    private String sname;
    private String saddress;
    private Studied studied;
    private String grade;
    private Studies studies;
    private String SQL;

    // ------------------Skapa koppling -----------------//
    public static Connection getConnection() {
        Connection con = null;
        try {
            String host = "jdbc:sqlserver://localhost;databaseName=IS";
            String uName = "sa";
            String uPass = "molTaz36";
            con = DriverManager.getConnection(host, uName, uPass);
        } catch (Exception e) {
            System.out.println("Kunde inte koppla" + e);
        }
        return con;
    }

    // -----------------Skapa -------------------------//

    public void createStudent(String pnr, String sname, String saddress) throws SQLException {

            Connection con = getConnection();
            Statement stmt  = con.createStatement();

            SQL = "insert into student values ('" + pnr + "' ,'" + sname + "','" + saddress + "');";

            stmt.executeUpdate(SQL);
            stmt.close();

    }

    public Course createCourse(String ccode, String cname, String caddress) {

        try {
            Connection con = getConnection();
            Statement stmt = con.createStatement();

            String SQL = "insert into course values ('" + ccode + "', '" + cname + "', '" + caddress + "');";

            stmt.executeUpdate(SQL);
            stmt.close();
            con.close();

        }

        catch (Exception e) {
            System.out.println(e);
        }
        return course;
    }

    // -------------------Söka ------------------------ //
    public Student findStudentByPnr(String pnr) {

        try {
            Connection con = getConnection();
            String SQL = "select * from student where pnr = ?";
            PreparedStatement stmt = con.prepareStatement(SQL);
            stmt.setString(1, pnr);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                pnr = rs.getString("pnr");
                sname = rs.getString("sname");
                saddress = rs.getString("saddress");
                student = new Student();
                student.setSname(sname);
                student.setSaddress(saddress);
            }
            stmt.close();
            con.close();
            return student;
        } catch (Exception e) {
            System.out.println(e);
        }
        return student;
    }

    public Course findCourseByccode(String ccode) {
        try {
            Connection con = getConnection();
            String SQL = "select * from course where ccode = ?";
            PreparedStatement stmt = con.prepareStatement(SQL);
            stmt.setString(1, ccode); // använd detta
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                ccode = rs.getString("ccode");
                cname = rs.getString("cname");
                caddress = rs.getString("caddress");
                System.out.println(ccode + cname + caddress);
                course = new Course();
                course.setCcode(ccode);
                course.setCaddress(caddress);
                course.setCname(cname);
            }

            stmt.close();
            con.close();
            return course;
        } catch (Exception e) {
            System.out.println(e);
        }
        return course;


    }

    public Studied getCourseResults(String ccode) { // Alla studenters betyg
                                                    // från en kurs
        try {
            Connection con = getConnection();
            SQL = "select grade from studied where ccode = ?";
            PreparedStatement stmt = con.prepareStatement(SQL);
            ResultSet rs = stmt.executeQuery();
            stmt.setString(1, ccode);
            while (rs.next()) {
                ccode = rs.getString("ccode");
                cname = rs.getString("cname");
                caddress = rs.getString("caddress");
                System.out.println(ccode + cname + caddress);
            }
            stmt.close();
            con.close();
        } catch (Exception e) {
            System.out.println(e);
        }
        return studied;
    }

    public Studied getStudentResult(String pnr, String ccode) { // En students
                                                                // betyg för en
                                                                // viss kurs
        try {
            Connection con = getConnection();
            SQL = "select grade from studied where pnr = ? and ccode = ?";
            PreparedStatement stmt = con.prepareStatement(SQL);
            ResultSet rs = stmt.executeQuery();
            stmt.setString(1, pnr);
            stmt.setString(2, ccode);
            while (rs.next()) {
                grade = rs.getString("grade");
                pnr = rs.getString("pnr");
                ccode = rs.getString("ccode");
                System.out.println(grade + pnr + ccode);
            }
            stmt.close();
            con.close();
        } catch (Exception e) {

        }
        return studied;
    }

    public Student findAllStudentsByPnr(String pnr) {
        try {
            Connection con = getConnection();
            SQL = "select * from student order by pnr ";
            PreparedStatement stmt = con.prepareStatement(SQL);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                pnr = rs.getString("pnr");
                sname = rs.getString("sname");
                saddress = rs.getString("saddress");
                System.out.println(pnr + sname + saddress);
            }
            stmt.close();
            con.close();
        } catch (Exception e) {
            System.out.println(e);
        }
        return student;
    }

    public Course findAllCourseByccode(String ccode) {
        try {
            Connection con = getConnection();
            SQL = "select * from course order by cname";
            PreparedStatement stmt = con.prepareStatement(SQL);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                ccode = rs.getString("ccode");
                cname = rs.getString("cname");
                caddress = rs.getString("caddress");
            }
            stmt.close();
            con.close();
        } catch (Exception e) {
            System.out.println(e);
        }
        return course;
    }

    // -------------Registera --------------///
    public void addStudentToStudied(String pnr, String ccode, String grade) {
        try {
            Connection con = getConnection();
            SQL = "insert into studied values (?, ?, ?)"; // pnr, ccode, grade
//                    + "delete from studies where pnr = ?" // pnr
//                    + "and ccode = ? "; // ccode
            PreparedStatement stmt = con.prepareStatement(SQL);
            stmt.setString(1, pnr);
            stmt.setString(2, ccode);
            stmt.setString(3, grade);
//            stmt.setString(4, pnr);
//            stmt.setString(5, ccode);
            stmt.executeUpdate();


            stmt.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println(e);
        }
    }


    public void addStudentToStudies(String pnr, String ccode) {
        try {
            Connection con = getConnection();
            SQL = "insert into studies values ('" + pnr + "','" + ccode +"');";
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(SQL);
            while(rs.next()){
                pnr = rs.getString("pnr");
                ccode = rs.getString("ccode");
                System.out.println(pnr + ccode);
            }
            stmt.close();
            con.close();
        } catch (Exception e) {
            System.out.println(e);

        }

    }
}