JOEP

package dal;

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

import model.Student;

public class Dal {



	private PreparedStatement stmt;
	private Connection conn;

	public Connection getConnection() throws SQLException {
		try {
			DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());

			Connection conn = DriverManager.getConnection(
					"jdbc:sqlserver://localhost:1433;" + "databaseName=uppgift1;user=sa;password=stest123;");
			return conn;
		} catch (Exception e) {
			System.out.println(e);
		}

		return null;
	}

	public Student getStudentByName(String name) {

		Student student = null;

		try {
			conn = getConnection();

			String sql = "select * from student where sname = ?";

			stmt = conn.prepareStatement(sql);

			stmt.setString(1, name);

			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				String spnr = rs.getString("spnr");
				String sname = rs.getString("sname");
				String sadress = rs.getString("sadress");
				String stel = rs.getString("stel");

				student = new Student(spnr, sname, sadress, stel);

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

		return student;
	}

	public ArrayList<Student> getAllStudents() {

		ArrayList<Student> students = new ArrayList<Student>();
		try {
			conn = getConnection();
			String sql = "select * from student";

			stmt = conn.prepareStatement(sql);

			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				String spnr = rs.getString("spnr");
				String sname = rs.getString("sname");
				String sadress = rs.getString("sadress");
				String stel = rs.getString("stel");

				Student student = new Student(spnr, sname, sadress, stel);
				students.add(student);

			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return students;
	}

}
// try {
// // Ladda Oracle JDBC driver
// DriverManager.registerDriver(new
// com.microsoft.sqlserver.jdbc.SQLServerDriver());
// } catch (Exception e) {
// System.out.println("Kan inte database driver class: " + e);
// }
// try {
// // syntax : <host>:<port>:<sid>.
// Connection conn = DriverManager.getConnection(
// "jdbc:sqlserver://localhost:1433;" +
// "databaseName=uppgift1;user=sa;password=stest123;");
// if (conn == null)
// System.out.println("No driver found!");
// else
// System.out.println("Connection Established - SUCCESS!");
// Statement stmt = conn.createStatement();
// ResultSet rs = stmt.executeQuery("SELECT sname FROM student");
// while (rs.next()) {
// String sname = rs.getString("sname");
// System.out.println(sname);
// conn.close();
// }
// } catch (Exception e) {
// System.out.println("Kaaaaaaaan inte hitta database driver class: " + e);
// }