package DatabaseAccesslayer;

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


import connector.ConnectDb;
import model.*;

public class DAL {

	private String sqlString;
	private Student student;
	private String sPnr;
	private String sName;
	private String sAdress;
	private Course course;
	private String cCode;
	private String cName;
	private int cPoints;

	// Find a specific student with sPnr********************************
	public Student findStudent(String sPnr) throws SQLException {

		Connection con = ConnectDb.startConnection();

		sqlString = "select * from student where spnr = '" + sPnr + "'";
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sqlString);
		rs.next();
		sPnr = rs.getString(1);
		sName = rs.getString(2);
		sAdress = rs.getString(3);

		student = new Student(sPnr, sName, sAdress);

		return student;

	}

	// Find a specific course with cCode ****************************
	public Course findCourse(String cCode) throws SQLException {

		Connection con = ConnectDb.startConnection();

		sqlString = "select * from course where ccode = '" + cCode + "'";
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sqlString);
		rs.next();
		cCode = rs.getString(1);
		cName = rs.getString(2);
		cPoints = rs.getInt(3);

		course = new Course(cCode, cName, cPoints);

		return course;
	}

	// Add student
	public void addStudent(String sPnr, String sName, String sAdress) throws SQLException {
		Connection con = ConnectDb.startConnection();

		Statement stmt = con.createStatement();

		String sqlString = "insert into student values('" + sPnr + "','" + sName + "','" + sAdress + "')";
		stmt.executeUpdate(sqlString);
		stmt.close();
	}

	// Add course
	public void addCourse(String cCode, String cName, int cPoints) throws SQLException {
		Connection con = ConnectDb.startConnection();

		Statement stmt = con.createStatement();

		String sqlString = "insert into course values('" + cCode + "','" + cName + "','" + cPoints + "')";
		stmt.executeUpdate(sqlString);
		stmt.close();
	}

	public populateStudent() throws SQLException{

	try{
		Connection con = ConnectDb.startConnection();
		Statement stmt = con.createStatement();
		String sqlString ="select * from student";
		ResultSet rs = stmt.executeQuery(sqlString);
		Default

		ResultSetMetaData rsmd = rs.getMetaData();
		int columns = rsmd.getColumnCount();


	} catch(SQLException e) {
		e.printStackTrace();
	}


	}

}



package controller;

import java.sql.*;
import model.*;
import DatabaseAccesslayer.*;

public class Controller {

	private Student student;
	private Course course;
	private DAL dal;


	public Controller(){
		dal = new DAL();
	}

	public Student findStudent(String sPnr) throws SQLException{
		student = dal.findStudent(sPnr);
		return student;
	}

	public void addStudent (String sPnr, String sName, String sAdress) throws SQLException{
		dal.addStudent(sPnr, sName, sAdress);
	}

	public Course findCourse(String cCode) throws SQLException{
		course = dal.findCourse(cCode);
		return course;
	}

	public void addCourse (String cCode, String cName, int cPoints) throws SQLException{
		dal.addCourse(cCode, cName, cPoints);
	}





}


package connector;

import java.sql.*;

public class ConnectDb {

	public static Connection startConnection() {
		Connection con = null;
		try {
			DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
		} catch (Exception e) {
			System.out.println("Kan inte database driver class: " + e);
		}
		try {
			con = DriverManager.getConnection(
					"jdbc:sqlserver://localhost:1433;" + "databaseName=Uppgift1;user=sa;password=kalle;");
			if (con == null)
				System.out.println("No driver found!");
			else
				System.out.println("Connection Established - SUCCESS!");

		} catch (Exception e) {
			System.out.println("Kaaaaaaaan inte hitta database driver class: " + e);
		}
		return con;

	}

}


package view;

import java.awt.EventQueue;

import javax.swing.JFrame;
import javax.swing.JTabbedPane;
import javax.swing.JLayeredPane;
import javax.swing.JLabel;
import java.awt.Font;
import javax.swing.JTextField;
import javax.swing.table.DefaultTableModel;

import controller.Controller;

import javax.swing.JTable;
import javax.swing.JScrollPane;
import javax.swing.JComboBox;
import javax.swing.DefaultComboBoxModel;
import javax.swing.JButton;
import javax.swing.SwingConstants;
import java.awt.event.ActionListener;
import java.sql.SQLException;
import java.awt.event.ActionEvent;

public class DBStartView {

	Controller controller = new Controller();
	private JFrame frameSchool;
	private JTextField textField_SName;
	private JTextField textField_SPnr;
	private JTextField textField_SAdress;
	private JTable table_Student;
	private JTable table_ThisStudent;
	private JTextField textField_CAdress;
	private JTextField textField_CPoints;
	private JTextField textField_CName;
	private JTextField textField_CCode;
	private JTable table_Course;
	private JTable table_ThisCourse;
	private JTextField textField_HNewName;
	private JTextField textField_HNewAdress;
	private JTextField textField_NewPnr;
	private JTextField textField_TotalPoints;
	private JTextField textField_HPoints;

	/**
	 * Launch the application.
	 */
	public static void main(String[] args) {
		EventQueue.invokeLater(new Runnable() {
			public void run() {
				try {
					DBStartView window = new DBStartView();
					window.frameSchool.setVisible(true);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}

	/**
	 * Create the application.
	 */
	public DBStartView() {
		initialize();
	}

	/**
	 * Initialize the contents of the frame.
	 */
	private void initialize() {
		frameSchool = new JFrame();
		frameSchool.setTitle("School");
		frameSchool.setBounds(100, 100, 818, 835);
		frameSchool.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		frameSchool.getContentPane().setLayout(null);

		JTabbedPane tabbedPane = new JTabbedPane(JTabbedPane.TOP);
		tabbedPane.setBounds(15, 30, 754, 696);
		frameSchool.getContentPane().add(tabbedPane);

		JLayeredPane tab_Home = new JLayeredPane();
		tabbedPane.addTab("Home", null, tab_Home, null);

		JLabel lblCreateNew = new JLabel("Create new ");
		lblCreateNew.setFont(new Font("Tahoma", Font.PLAIN, 20));
		lblCreateNew.setBounds(15, 15, 145, 42);
		tab_Home.add(lblCreateNew);

		JLabel lblMessage = new JLabel("Message:");
		lblMessage.setBounds(15, 742, 754, 20);
		frameSchool.getContentPane().add(lblMessage);

		JComboBox<String> comboBox_New = new JComboBox<String>();
		comboBox_New.setFont(new Font("Tahoma", Font.PLAIN, 20));
		comboBox_New.setModel(new DefaultComboBoxModel<String>(new String[] { "Student", "Course" }));
		comboBox_New.setBounds(170, 20, 120, 30);
		tab_Home.add(comboBox_New);

		JLabel lblNewName = new JLabel("Name");
		lblNewName.setBounds(25, 100, 115, 20);
		tab_Home.add(lblNewName);

		textField_HNewName = new JTextField();
		textField_HNewName.setBounds(145, 100, 146, 26);
		tab_Home.add(textField_HNewName);
		textField_HNewName.setColumns(10);

		JLabel lblNewAdress = new JLabel("Adress");
		lblNewAdress.setBounds(25, 140, 115, 20);
		tab_Home.add(lblNewAdress);

		textField_HNewAdress = new JTextField();
		textField_HNewAdress.setBounds(145, 140, 146, 26);
		tab_Home.add(textField_HNewAdress);
		textField_HNewAdress.setColumns(10);

		JLabel lblNewPnr = new JLabel("Pnr");
		lblNewPnr.setBounds(25, 60, 115, 20);
		tab_Home.add(lblNewPnr);

		textField_NewPnr = new JTextField();
		textField_NewPnr.setBounds(145, 60, 146, 26);
		tab_Home.add(textField_NewPnr);
		textField_NewPnr.setColumns(10);

		JButton btnSave = new JButton("Save");
		btnSave.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {

				String sPnr = textField_NewPnr.getText();
				String sName = textField_HNewName.getText();
				String sAdress = textField_HNewAdress.getText();

				try {
					controller.addStudent(sPnr, sName, sAdress);
					lblMessage.setText("Wooohooo fucking hooo");
				} catch (SQLException e1) {
					e1.printStackTrace();
					lblMessage.setText("Student not added");

				}
			}
		});
		btnSave.setBounds(170, 244, 120, 29);
		tab_Home.add(btnSave);

		JLabel lblHPoints = new JLabel("Points");
		lblHPoints.setBounds(25, 180, 69, 20);
		tab_Home.add(lblHPoints);

		textField_HPoints = new JTextField();
		textField_HPoints.setBounds(145, 180, 146, 26);
		tab_Home.add(textField_HPoints);
		textField_HPoints.setColumns(10);

		JLayeredPane tab_Student = new JLayeredPane();
		tabbedPane.addTab("Student", null, tab_Student, null);

		JLabel lblSPnr = new JLabel("Pnr");
		lblSPnr.setBounds(25, 60, 115, 20);
		tab_Student.add(lblSPnr);

		JLabel lblSAdress = new JLabel("Adress");
		lblSAdress.setBounds(25, 140, 115, 20);
		tab_Student.add(lblSAdress);

		JLabel lblSName = new JLabel("Name");
		lblSName.setBounds(25, 100, 115, 20);
		tab_Student.add(lblSName);

		textField_SName = new JTextField();
		textField_SName.setColumns(10);
		textField_SName.setBounds(145, 100, 146, 26);
		tab_Student.add(textField_SName);

		JLabel lblSInformation = new JLabel("Information Student");
		lblSInformation.setFont(new Font("Tahoma", Font.PLAIN, 20));
		lblSInformation.setBounds(15, 15, 199, 42);
		tab_Student.add(lblSInformation);

		textField_SPnr = new JTextField();
		textField_SPnr.setBounds(145, 60, 146, 26);
		tab_Student.add(textField_SPnr);
		textField_SPnr.setColumns(10);

		textField_SAdress = new JTextField();
		textField_SAdress.setBounds(145, 140, 146, 26);
		tab_Student.add(textField_SAdress);
		textField_SAdress.setColumns(10);

		JScrollPane scrollPane_ThisStudent = new JScrollPane();
		scrollPane_ThisStudent.setBounds(15, 470, 276, 174);
		tab_Student.add(scrollPane_ThisStudent);

		JLayeredPane tab_Course = new JLayeredPane();
		tabbedPane.addTab("Course", null, tab_Course, null);

		JLabel lblCInformation = new JLabel("Information Course");
		lblCInformation.setFont(new Font("Tahoma", Font.PLAIN, 20));
		lblCInformation.setBounds(15, 15, 276, 42);
		tab_Course.add(lblCInformation);

		JLabel lblCourseCode = new JLabel("Course Code");
		lblCourseCode.setBounds(25, 60, 115, 20);
		tab_Course.add(lblCourseCode);

		JLabel lblCourseName = new JLabel("Course Name");
		lblCourseName.setBounds(25, 100, 115, 20);
		tab_Course.add(lblCourseName);

		JLabel lblPoints = new JLabel("Points");
		lblPoints.setBounds(25, 180, 115, 20);
		tab_Course.add(lblPoints);

		JLabel lblCAdress = new JLabel("Adress");
		lblCAdress.setBounds(25, 140, 115, 20);
		tab_Course.add(lblCAdress);

		textField_CAdress = new JTextField();
		textField_CAdress.setBounds(145, 140, 146, 26);
		tab_Course.add(textField_CAdress);
		textField_CAdress.setColumns(10);

		textField_CPoints = new JTextField();
		textField_CPoints.setText("");
		textField_CPoints.setBounds(145, 180, 146, 26);
		tab_Course.add(textField_CPoints);
		textField_CPoints.setColumns(10);

		textField_CName = new JTextField();
		textField_CName.setText("");
		textField_CName.setBounds(145, 100, 146, 26);
		tab_Course.add(textField_CName);
		textField_CName.setColumns(10);

		textField_CCode = new JTextField();
		textField_CCode.setText("");
		textField_CCode.setBounds(145, 60, 146, 26);
		tab_Course.add(textField_CCode);
		textField_CCode.setColumns(10);

		table_ThisStudent = new JTable();
		scrollPane_ThisStudent.setViewportView(table_ThisStudent);

		JScrollPane scrollPane_Student = new JScrollPane();
		scrollPane_Student.setBounds(315, 60, 410, 585);
		tab_Student.add(scrollPane_Student);

		table_Student = new JTable();
		scrollPane_Student.setColumnHeaderView(table_Student);

		JScrollPane scrollPane_ThisCourse = new JScrollPane();
		scrollPane_ThisCourse.setViewportView(table_ThisCourse);

		scrollPane_ThisCourse.setBounds(15, 470, 276, 174);
		tab_Course.add(scrollPane_ThisCourse);

		table_ThisCourse = new JTable();
		scrollPane_ThisCourse.setColumnHeaderView(table_ThisCourse);

		JScrollPane scrollPane_Course = new JScrollPane();
		scrollPane_Course.setBounds(315, 60, 410, 585);
		tab_Course.add(scrollPane_Course);

		table_Course = new JTable();
		scrollPane_Course.setColumnHeaderView(table_Course);

		DefaultTableModel Dmt_Student = new DefaultTableModel();
		String colum_s[] = { "Pnr", "Name" };
		table_Student = new JTable(Dmt_Student);
		scrollPane_Student.setViewportView(table_Student);
		Dmt_Student.setColumnIdentifiers(colum_s);

		DefaultTableModel Dmt_ThisStudent = new DefaultTableModel();
		String colum_ts[] = { "Code", "Name", "Grade" };
		table_ThisStudent = new JTable(Dmt_ThisStudent);
		scrollPane_ThisStudent.setViewportView(table_ThisStudent);

		JComboBox<?> comboBox_sortThisStudent = new JComboBox();
		comboBox_sortThisStudent.setModel(new DefaultComboBoxModel(new String[] { "All", "Current", "Completed" }));
		comboBox_sortThisStudent.setBounds(145, 430, 146, 25);
		tab_Student.add(comboBox_sortThisStudent);

		JLabel lblSSortBy = new JLabel("Sort by ");
		lblSSortBy.setHorizontalAlignment(SwingConstants.RIGHT);
		lblSSortBy.setBounds(60, 430, 80, 25);
		tab_Student.add(lblSSortBy);

		JLabel lblSNumberOfCourses = new JLabel("Number of courses:");
		lblSNumberOfCourses.setBounds(25, 220, 266, 20);
		tab_Student.add(lblSNumberOfCourses);

		JLabel lblTotalPoints = new JLabel("Total points ");
		lblTotalPoints.setBounds(25, 180, 115, 20);
		tab_Student.add(lblTotalPoints);

		textField_TotalPoints = new JTextField();
		textField_TotalPoints.setBounds(145, 180, 146, 26);
		tab_Student.add(textField_TotalPoints);
		textField_TotalPoints.setColumns(10);

		JLabel lblSGradeStatistics = new JLabel("Grade statistics (%)");
		lblSGradeStatistics.setBounds(25, 260, 150, 20);
		tab_Student.add(lblSGradeStatistics);

		JLabel lblSGradeA = new JLabel("A");
		lblSGradeA.setHorizontalAlignment(SwingConstants.RIGHT);
		lblSGradeA.setBounds(222, 260, 69, 20);
		tab_Student.add(lblSGradeA);

		JLabel lblSGradeB = new JLabel("B");
		lblSGradeB.setHorizontalAlignment(SwingConstants.RIGHT);
		lblSGradeB.setBounds(222, 285, 69, 20);
		tab_Student.add(lblSGradeB);

		JLabel lblSGradeC = new JLabel("C");
		lblSGradeC.setHorizontalAlignment(SwingConstants.RIGHT);
		lblSGradeC.setBounds(222, 310, 69, 20);
		tab_Student.add(lblSGradeC);

		JLabel lblSGradeD = new JLabel("D");
		lblSGradeD.setHorizontalAlignment(SwingConstants.RIGHT);
		lblSGradeD.setBounds(222, 335, 69, 20);
		tab_Student.add(lblSGradeD);

		JLabel lblSGradeE = new JLabel("E");
		lblSGradeE.setHorizontalAlignment(SwingConstants.RIGHT);
		lblSGradeE.setBounds(222, 360, 69, 20);
		tab_Student.add(lblSGradeE);

		JLabel lblSGradeU = new JLabel("U");
		lblSGradeU.setHorizontalAlignment(SwingConstants.RIGHT);
		lblSGradeU.setBounds(222, 385, 69, 20);
		tab_Student.add(lblSGradeU);
		Dmt_ThisStudent.setColumnIdentifiers(colum_ts);

		DefaultTableModel Dmt_Course = new DefaultTableModel();
		String colum_c[] = { "Code", "Name" };
		table_Course = new JTable(Dmt_Course);
		scrollPane_Course.setViewportView(table_Course);
		Dmt_Course.setColumnIdentifiers(colum_c);

		JButton btnSUpdate = new JButton("Update");
		btnSUpdate.setBounds(199, 24, 92, 29);
		tab_Student.add(btnSUpdate);

		JButton btnCUpdate = new JButton("Update");
		btnCUpdate.setBounds(199, 24, 92, 29);
		tab_Course.add(btnCUpdate);

		DefaultTableModel Dmt_ThisCourse = new DefaultTableModel();
		String colum_tc[] = { "Pnr", "Name", "Grade" };
		table_ThisCourse = new JTable(Dmt_ThisCourse);
		scrollPane_ThisCourse.setViewportView(table_ThisCourse);
		Dmt_ThisCourse.setColumnIdentifiers(colum_tc);

		JComboBox<?> comboBox_sortThisCourse = new JComboBox();
		comboBox_sortThisCourse.setModel(new DefaultComboBoxModel(new String[] { "All", "Current", "Completed" }));
		comboBox_sortThisCourse.setBounds(145, 430, 146, 25);
		tab_Course.add(comboBox_sortThisCourse);

		JLabel lblCSortBy = new JLabel("Sort by ");
		lblCSortBy.setHorizontalAlignment(SwingConstants.RIGHT);
		lblCSortBy.setBounds(60, 430, 80, 25);
		tab_Course.add(lblCSortBy);

		JLabel lblCNumberOfStudents = new JLabel("Number of students:");
		lblCNumberOfStudents.setBounds(25, 220, 266, 20);
		tab_Course.add(lblCNumberOfStudents);

		JLabel lblCGradeStatistics = new JLabel("Grade statistics (%)");
		lblCGradeStatistics.setBounds(25, 260, 150, 20);
		tab_Course.add(lblCGradeStatistics);

		JLabel lblCGradeA = new JLabel("A");
		lblCGradeA.setHorizontalAlignment(SwingConstants.RIGHT);
		lblCGradeA.setBounds(222, 260, 69, 20);
		tab_Course.add(lblCGradeA);

		JLabel lblCGradeB = new JLabel("B");
		lblCGradeB.setHorizontalAlignment(SwingConstants.RIGHT);
		lblCGradeB.setBounds(222, 285, 69, 20);
		tab_Course.add(lblCGradeB);

		JLabel lblCGradeC = new JLabel("C");
		lblCGradeC.setHorizontalAlignment(SwingConstants.RIGHT);
		lblCGradeC.setBounds(222, 310, 69, 20);
		tab_Course.add(lblCGradeC);

		JLabel lblCGradeD = new JLabel("D");
		lblCGradeD.setHorizontalAlignment(SwingConstants.RIGHT);
		lblCGradeD.setBounds(222, 335, 69, 20);
		tab_Course.add(lblCGradeD);

		JLabel lblCGradeE = new JLabel("E");
		lblCGradeE.setHorizontalAlignment(SwingConstants.RIGHT);
		lblCGradeE.setBounds(222, 360, 69, 20);
		tab_Course.add(lblCGradeE);

		JLabel lblCGradeU = new JLabel("U");
		lblCGradeU.setHorizontalAlignment(SwingConstants.RIGHT);
		lblCGradeU.setBounds(222, 385, 69, 20);
		tab_Course.add(lblCGradeU);

	}
}