/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database.migration;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.TreeMap;

/******************************************************************************
 * SqlFactory (factory Pattern)
 * 
 * il fait essentiellement la même chose que DbConnection
 * mais étant donnée que les fichiers de proriété n'était pas documenté
 * et qu'aucun sample ne faisait partie du svn
 * j'ai importer temporairement le mien d'un autre projet
 *  
 * liste des dependances s'il necessaire
 * jdbc
 * java.util.Properties
 * java.util.TreeMap
 * java.util.HashList
 * 
 * Projet Orford
 *****************************************************************************
 *modification
 *nom date     explication
 ****************************************************************************/


/**
 *
 * @author martin
 */
public final class SqlFactory {

  public static String mysqlDriver = "jdbc:mysql",
                       javadbDriver = "jdbc:derby";
  
  public static Properties buildProperties(String user, String pass, String host, String database, String driver){
    Properties props = new Properties();
    
    props.setProperty("user", user);
    props.setProperty("password", pass);
    props.setProperty("url",driver+"://"+host+"/"+database);
            
    return props;
  }
  
  public static Properties loadProperties(String filename) throws FileNotFoundException, IOException{
    Properties prop = new Properties();
    prop.loadFromXML(new FileInputStream(filename));
    return prop;
  }
  
  public static void storeProperties(String filename, Properties prop) throws IOException{
    prop.storeToXML(new FileOutputStream(filename), filename);
  }
  
  public static Connection connect(final Properties props) throws SQLException{
    return DriverManager.getConnection(props.getProperty("url"), props);
  }
  
  private static Map<String,Object> makeMapFromRow(final ResultSet rs, final ResultSetMetaData md) throws SQLException{
    Map<String,Object> tmp = new TreeMap<String,Object>();
    for (int i=1;i<md.getColumnCount();i++){
      tmp.put(md.getColumnName(i),rs.getObject(i));
    }
    return tmp;
  }

  public static List<Map<String,Object>> query(final Connection con, final String req) throws SQLException{
      ResultSet rs = con.createStatement().executeQuery(req);
      ResultSetMetaData md = rs.getMetaData();
      List<Map<String,Object>> tmp = new ArrayList<Map<String,Object>>();
      
      while(rs.next()){
        tmp.add(makeMapFromRow(rs,md));
      }
      
      return tmp;
	}
  
  public static Object queryOne(final Connection con, final String req) throws SQLException{
    ResultSet rs = con.createStatement().executeQuery(req);
    rs.next();
    return rs.getObject(1);
  }
  
  public static void main(String[]args) throws IOException, ClassNotFoundException, SQLException{

    Properties prop = buildProperties("root", "root", "localhost", "baseball", mysqlDriver);
    
    prop.storeToXML(new FileOutputStream("baseballDB.xml"), "mysql baseball database");
    prop.loadFromXML(new FileInputStream("baseballDB.xml"));
    
    Connection con = connect(prop);
    
    ResultSet rs = con.createStatement().executeQuery("SELECT * FROM equipe;");
    ResultSetMetaData md = rs.getMetaData();
    while(rs.next()){
      for (int i=1;i<md.getColumnCount();i++){
        String name = md.getColumnName(i);
        String value = rs.getString(name);
        System.out.println(name+":"+value);  
      }
    }
    con.close();
    
  }
}