package com.javahonk.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.javahonk.di.bean.Person;
import com.javahonk.di.bean.PersonRowMapper;

public class PersonDAO implements IPersonDAO {

    private JdbcTemplate jdbcTemplate;
    
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
	this.jdbcTemplate = jdbcTemplate;
    }

    //Select all person with using custom row mapper and directly map 
    //all field value when calling method
    public List<Person> selectAllPerson() {
	
	System.out.println("\nList of person in the table\n");
	String selectAllPerson = "SELECT * FROM person";
	
	return jdbcTemplate.query(selectAllPerson, 
		new RowMapper<Person>(){

	    @Override
	    public Person mapRow(ResultSet rs, int rowNumber) 
		    throws SQLException {		
		Person person = new Person();
		person.setRowNumber(rowNumber);
		person.setId(rs.getInt("id"));
		person.setFirst_Name(rs.getString("First_Name"));
		person.setLast_Name(rs.getString("Last_Name"));
		person.setStreet_Name(rs.getString("Street_Name"));
		person.setCity(rs.getString("City"));
		person.setState(rs.getString("State"));
		person.setCountry(rs.getString("Country"));
		return person;
	    }
	    
	});	

    }
    
    @Override
    //Using BeanPropertyRowMapper
    public List <Person> selectPersonByName(String personName) {

	System.out.println("\nList of person in the table\n");
	String selectPersonByName = "SELECT * FROM person "
		+ "where First_Name = ?";
	List <Person> personList = (List<Person>) jdbcTemplate.
		query(selectPersonByName,
		new Object[] { personName },
		new BeanPropertyRowMapper<Person>(Person.class));

	return personList;
    }
    
    @Override
    //Using custom row mapper
    public List <Person> selectPersonByNameUsingCustomRowMapper(
	    String personName) {

	System.out.println("\nList of person in the table\n");
	String selectPersonByName = "SELECT * FROM person "
		+ "where First_Name = ?";
	List <Person> personList = (List<Person>) jdbcTemplate.
		query(selectPersonByName,
		new Object[] { personName },
		new PersonRowMapper());

	return personList;
    }
    
    @Override
    //Find total person
    public Integer findTotalPerson() {

	System.out.println("\nNo of person in the table\n");
	String totalNumber	 = "SELECT count(*) FROM person";
	return jdbcTemplate.queryForInt(totalNumber);
	
    }
    
    @Override
    //find total person using clause
    public Integer findTotalPersonUsingWhereClause() {

	System.out.println("\nNo of person in the table\n");
	String totalNumber	 = "SELECT count(*) FROM "
		+ "person where First_Name = ?";
	return jdbcTemplate.queryForInt(totalNumber, 
		new Object[] {"Java"});
	
    }   
    

}
