Class DataSource

public class DataSource {
	static {
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	public Connection getConnection() throws SQLException {
		return DriverManager.getConnection("jdbc:sqlserver://localhost\\\\KAKA-PC:1433;databaseName=hr", "sa", "123");
	}
}

Class Department

public class Department {
	private int id;
	private String name;
	
	public Department(int id, String name) {
		super();
		this.id = id;
		this.name = name;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}	
}

Class DepartmentRepository

public class DepartmentRepository {
	DataSource ds = new DataSource();
}

Method GetDepartments

public List<Department> getDepartments() throws SQLException{
	try(Connection connection = ds.getConnection()){
		try(Statement stmt = connection.createStatement()){
			try(ResultSet rs = stmt.executeQuery("EXEC GetDepartments")){
				List<Department> list = new LinkedList<>();
				while(rs.next()) {
					list.add(new Department(rs.getInt("DepartmentId"), rs.getString("DepartmentName")));
				}
				return list;
			}
			
		}
	}
}

Method Add Department

public int add(Department obj) throws SQLException {
	try(Connection connection = ds.getConnection()){
		String sql = "{CALL AddDepartment(?)}";
		try(CallableStatement cstmt = connection.prepareCall(sql)){
			cstmt.setString("@name", obj.getName());
			return cstmt.executeUpdate();
		}
	}
}

Method Add Department and Output Parameter

public int add(Department obj) throws SQLException {
	try(Connection connection = ds.getConnection()){
		try(CallableStatement cstmt = connection.prepareCall("EXEC AddDepartment2 ?, ?")){
			cstmt.registerOutParameter("@id", Types.INTEGER);
			cstmt.setString("@name", obj.getName());
			int ret = cstmt.executeUpdate();
			obj.setId(cstmt.getInt("@id"));
			return ret;
		}
	}
}

Class Member

public class Member {
	private int id;
	private String username;
	private String password;
	
	public Member(int id, String username, String password) {
		super();
		this.id = id;
		this.username = username;
		this.password = password;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}	
}

Class MemberRepository

public class MemberRepository {
	DataSource ds= new DataSource();
}

Method Add

public int add(Member obj) throws SQLException {
	try(Connection connection = ds.getConnection()){
		try(CallableStatement cstmt = connection.prepareCall("{? = CALL AddMember(?, ?, ?)}")){
			cstmt.registerOutParameter(1, Types.INTEGER);
			cstmt.registerOutParameter("@id", Types.INTEGER);
			cstmt.setString("@username", obj.getUsername());
			cstmt.setString("@password", obj.getPassword());
			
			if(cstmt.executeUpdate() > 0) {
				obj.setId(cstmt.getInt("@id"));
			}
			int ret = cstmt.getInt(1);
			
			return ret;
		}
	}
}