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;
?????}
????}));
??
?}

}