package spring.sample.dao.sample;

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

import javax.sql.DataSource;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.object.MappingSqlQuery;
import org.springframework.jdbc.object.SqlUpdate;

/**
 * JdbcSampleDAOはJDBC抽象化フレームワークを実装します。<br>
 * <br>
 * @author MBeans
 * @version 1.0
 */
public class JdbcSampleDAO extends JdbcDaoSupport implements SampleDAO {

	private FindPersonsQuery findPersonsQuery = null;
	private FindPersonQuery findPersonQuery = null;
	private UpdatePersonQuery updatePersonQuery = null;
	private DeletePersonQuery deletePersonQuery = null;
	private InsertPersonQuery insertPersonQuery = null;
	
	protected void initDao() throws Exception {
		this.findPersonsQuery = new FindPersonsQuery(getDataSource());
		this.findPersonQuery = new FindPersonQuery(getDataSource());
		this.updatePersonQuery = new UpdatePersonQuery(getDataSource());
		this.deletePersonQuery = new DeletePersonQuery(getDataSource());
		this.insertPersonQuery = new InsertPersonQuery(getDataSource());
	}

	public List findPersons() throws DataAccessException {
		return findPersonsQuery.execute();
	}

	public Person findPerson(String id) throws DataAccessException {
		return (Person) findPersonQuery.findObject(id);
	}
	
	public void addPerson(Person person) throws DataAccessException {
		insertPersonQuery.update(person);
	}

	public int deletePerson(Person person) throws DataAccessException {
		return deletePersonQuery.update(person);
	}

	public int updatePerson(Person person) throws DataAccessException {
		return updatePersonQuery.update(person);
	}
	
	private static class FindPersonsQuery extends MappingSqlQuery {
		private static final String QUERY_SQL = "SELECT ID, NAME, ADDRESS FROM PERSON";
		
		private FindPersonsQuery(DataSource ds) {
			super(ds, QUERY_SQL);
		}

		protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			String id = rs.getString("ID");
			String name = rs.getString("NAME");
			String address = rs.getString("ADDRESS");
			return new Person(id, name, address);
		}
	}
	
	private static class FindPersonQuery extends MappingSqlQuery {
		private static final String QUERY_SQL = "SELECT ID, NAME, ADDRESS FROM PERSON WHERE ID = ?";
		
		private FindPersonQuery(DataSource ds) {
			super(ds, QUERY_SQL);
			declareParameter(new SqlParameter(Types.VARCHAR));
			compile();
		}

		protected Object mapRow(ResultSet rs, int rownum) throws SQLException {
			String id = rs.getString("ID");
			String name = rs.getString("NAME");
			String address = rs.getString("ADDRESS");
			return new Person(id, name, address);
		}
	}
	
	private static class UpdatePersonQuery extends SqlUpdate {
		private static final String ADD_UPDATE_SQL = "UPDATE PERSON SET ADDRESS = ? WHERE ID = ?";
		
		private UpdatePersonQuery(DataSource ds) {
			super(ds, ADD_UPDATE_SQL);
			declareParameter(new SqlParameter(Types.VARCHAR));
			declareParameter(new SqlParameter(Types.VARCHAR));
			compile();		
		}
		
		private int update(Person person) {
			Object[] param = new Object[]{person.getAddress(),person.getId()};
			return super.update(param);
		}
	}
	
	private static class InsertPersonQuery extends SqlUpdate {
		private static final String ADD_UPDATE_SQL = "INSERT INTO PERSON VALUES(?,?,?)";
		
		private InsertPersonQuery(DataSource ds) {
			super(ds, ADD_UPDATE_SQL);
			declareParameter(new SqlParameter(Types.VARCHAR));
			declareParameter(new SqlParameter(Types.VARCHAR));
			declareParameter(new SqlParameter(Types.VARCHAR));			
			compile();		
		}
		
		private int update(Person person) {
			Object[] param = new Object[]{person.getId(), person.getName(), person.getAddress()};
			return super.update(param);
		}
	}
	
	
	private static class DeletePersonQuery extends SqlUpdate {
		private static final String DELETE_UPDATE_SQL = "DELETE FROM PERSON WHERE ID = ?";
		
		private DeletePersonQuery(DataSource ds) {
			super(ds, DELETE_UPDATE_SQL);
			declareParameter(new SqlParameter(Types.VARCHAR));
			compile(); 
		}
		
		private int update(Person person) {
			String param = person.getId();
			return super.update(param);
		}
	}
}
