package com.knalum.Dao;

import com.knalum.Entity.Student;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;

/**
 * Created by knalum on 8/22/16.
 */

@Repository
@Qualifier("MySQLData")
public class MySQLStudentDaoImpl implements StudentDao {

    JdbcTemplate jdbcTemplate;
    DriverManagerDataSource dataSource;

    MySQLStudentDaoImpl(){
        dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/Student");
        dataSource.setUsername("client");
        dataSource.setPassword("client");
        jdbcTemplate = new JdbcTemplate(dataSource);

    }

    @Override
    public Collection<Student> getAllStudents() {
        String sql = "SELECT * FROM Student";
        List<Student> students = jdbcTemplate.query(sql, new RowMapper<Student>() {
            @Override
            public Student mapRow(ResultSet resultSet, int i) throws SQLException {
                Student student = new Student();
                student.setId(resultSet.getInt(1));
                student.setName(resultSet.getString(2));
                student.setCourse(resultSet.getString(3));
                return student;
            }
        });

        return students;
    }

    @Override
    public Student getStudentById(int id) {
        String sql = "SELECT * FROM Student WHERE id=?";

        return jdbcTemplate.queryForObject(sql,new Object[]{id}, new RowMapper<Student>() {
            @Override
            public Student mapRow(ResultSet resultSet, int i) throws SQLException {
                Student student = new Student();
                student.setId(resultSet.getInt(1));
                student.setName(resultSet.getString(2));
                student.setCourse(resultSet.getString(3));

                return student;
            }
        });
    }

    @Override
    public void removeStudentById(int id) {
        String sql = "DELETE FROM Student WHERE id=?";
        jdbcTemplate.update(sql,id);

    }

    @Override
    public void updateStudent(Student student) {
        String sql = "UPDATE Student SET name=?,course=? WHERE id=?";
        jdbcTemplate.update(sql,student.getName(),student.getCourse(),student.getId());
    }

    @Override
    public void insertStudent(Student student) {
        String sql = "INSERT INTO Student(name,course) VALUES(?,?)";
        try {
            PreparedStatement preparedStatement = dataSource.getConnection().prepareStatement(sql);
            preparedStatement.setString(1,student.getName());
            preparedStatement.setString(2,student.getCourse());
            preparedStatement.executeUpdate();
            preparedStatement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}