package org.springframework.samples;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.ListableBeanFactory;
import org.springframework.beans.factory.generic.GenericBeanFactoryAccessor;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowCountCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.StatementCallback;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.samples.petclinic.Person;

/**
 *
 * @author worldheart
 *
 */
public class MainTestForJdbcTemplate {

 private static final Log log = LogFactory.getLog(MainTestForJdbcTemplate.class);
 
 public static void main(String[] args) {  
  ListableBeanFactory cbf = new ClassPathXmlApplicationContext("ac1.xml");  
  GenericBeanFactoryAccessor gbfa = new GenericBeanFactoryAccessor(cbf);
  
  JdbcTemplate jt = gbfa.getBean("jdbcTemplate");
  
  jt.execute(new ConnectionCallback(){
   public Object doInConnection(Connection con) throws SQLException, DataAccessException {
    System.out.println(con.getMetaData().getDriverName());
    return null;
   }
  });
  
  List nameList = (List)jt.execute(new StatementCallback(){
   public Object doInStatement(Statement stmt) throws SQLException, DataAccessException {
    System.out.println(stmt.getConnection().getMetaData().getDatabaseProductVersion());
    List<String> nameList = new ArrayList<String>();
    ResultSet rs = null;
    try{
     rs = stmt.executeQuery("select name from types");
     while(rs.next()){
      nameList.add(rs.getString("name"));
     }
    }finally{
     JdbcUtils.closeResultSet(rs);
    }
    return nameList;
   }
  });
  System.out.println(nameList);
  
  List perList = (List)jt.query("select * from vets",
    new ResultSetExtractor(){
      public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
       List<Person> personList = new ArrayList<Person>();
       while(rs.next()){
        Person per = new Person();
        per.setId(rs.getInt("id"));
        per.setFirstName(rs.getString("first_name"));
        per.setLastName(rs.getString("last_name"));
        personList.add(per);
       }
       return personList;
    }
  });
  for(Iterator iterator = perList.iterator(); iterator.hasNext();){
   Person person = (Person)iterator.next();
   System.out.println(person.getId() + "," + person.getFirstName() + "," + person.getLastName());
  }

  final List<Person> pSonList = new ArrayList<Person>();
  jt.query("select * from vets", new RowCallbackHandler(){
   public void processRow(ResultSet rs) throws SQLException {
    Person per = new Person();
    per.setId(rs.getInt("id"));
    per.setFirstName(rs.getString("first_name"));
    per.setLastName(rs.getString("last_name"));
    pSonList.add(per);
   }
  });
  for(Person pSon: pSonList){
   System.out.println(pSon.getId() + "," + pSon.getFirstName() + "," + pSon.getLastName());
  }
  
  RowCountCallbackHandler rcch = new RowCountCallbackHandler();
  jt.query("select * from vets", rcch);
  for(String colName: rcch.getColumnNames())
   System.out.println(colName);
  for(int colType: rcch.getColumnTypes())
   System.out.println(colType);
  System.out.println(rcch.getColumnCount());
  System.out.println(rcch.getRowCount());
  
  List vetsList = (List)jt.query("select * from vets",
    new RowMapper(){
     public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
      Person pers = new Person();
      pers.setId(rs.getInt("id"));
      pers.setFirstName(rs.getString("first_name"));
      pers.setLastName(rs.getString("last_name"));
      return pers;
     }
  });
  System.out.println(vetsList);
  
  ColumnMapRowMapper cmrw = new ColumnMapRowMapper();
  List vList = (List)jt.query("select * from vets", cmrw);
  System.out.println(vList);
  
  System.out.println(jt.queryForInt("select count(*) from vets where id = ?",
    new Object[]{3}));
      
  jt.execute("update owners set address = 'GuangZhou' where telephone = ?",
    new PreparedStatementCallback(){
     public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
      ps.setString(1, "16068008");
      ps.addBatch();
      ps.setString(1, "6085555487");
      ps.addBatch();
      return ps.executeBatch();
     }
  });
  
  System.out.println(jt.query("select address from owners where first_name = ? and last_name = ?",
    new PreparedStatementSetter(){
     public void setValues(PreparedStatement ps) throws SQLException {
      ps.setString(1, "Jeff");
      ps.setString(2, "Black");
     }
    },
    new RowMapper(){
     public Object mapRow(ResultSet rs, int rowNum) throws SQLException {      
      return rs.getString("address");
     }
    }));
  
  System.out.println(jt.execute(
    new PreparedStatementCreator(){
     public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
      return con.prepareStatement("select address from owners");
     }
    },
    new PreparedStatementCallback(){
     public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
      List<String> list = new ArrayList<String>();
      ResultSet rs = null;
      try{
       rs = ps.executeQuery();
       while(rs.next()){
        list.add(rs.getString("address"));
       }
      }finally{
       JdbcUtils.closeResultSet(rs);
      }
      return list;
     }
    }));
  
 }

}