package DAO;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import beans.GameInfo;
import beans.ListOfTables;
import beans.SiteInfo;
import beans.TableInfo;
import Interface.TableDAOInterface;
public class JDBCTableDAO implements TableDAOInterface{
public DataSource getDataSource() throws NamingException{
Context namingContext = new InitialContext();
DataSource dataSource = (DataSource)namingContext.lookup ("java:comp/env/jdbc/testDatasource");
return dataSource;
}
public ArrayList<TableInfo> getListOfTables() {
return null;
}
public void loadTables() {
Connection con=null;
try{
con= getDataSource().getConnection();
DatabaseMetaData dmd = con.getMetaData();
ResultSet tables = dmd.getTables(con.getCatalog(),null,"%",null);//r�cup�ration des informations
ArrayList<TableInfo> listTables = new ArrayList<TableInfo>();
while(tables.next()){//affichage des informations
TableInfo table = new TableInfo();
for(int i=0; i<tables.getMetaData().getColumnCount();i++){
String nomColonne = tables.getMetaData().getColumnName(i+1);
if(nomColonne.equals("TABLE_NAME")){
Object valeurColonne = tables.getObject(i+1);
table.setNom(valeurColonne.toString());
//System.out.println(nomColonne+" = "+valeurColonne);
//r�cup�ration des informations
String nomDeLaTable = valeurColonne.toString();
ResultSet resultat = dmd.getColumns(con.getCatalog(),null,nomDeLaTable, "%");
ArrayList<String> colonnes = new ArrayList<String>();
//affichage des informations
ResultSetMetaData rsmd = resultat.getMetaData();
while(resultat.next()){
for(int j=0; j<rsmd.getColumnCount(); j++){
String col = rsmd.getColumnName(j+1);
// System.out.println("col : " + col + " val : " + resultat.getObject(j+1));
if(col.equals("COLUMN_NAME")){
Object val = resultat.getObject(j+1);
colonnes.add(val.toString());
//System.out.println(col+" = "+val);
}
}
}
table.setNomColonnes(colonnes);
}
}
listTables.add(table);
}
ListOfTables.setListOfTables(listTables);
}catch(Exception e){
System.out.println("probleme de connection");
e.printStackTrace();
}finally{
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
@Override
public void loadDatas() {
Connection con=null;
try{
con= getDataSource().getConnection();
Statement stmt=con.createStatement();
for(TableInfo ti : beans.ListOfTables.getListOfTables()){
String requete ="select";
ArrayList<ArrayList<String>> lignes = new ArrayList<ArrayList<String>>();
for(String col : ti.getNomColonnes()){
requete = requete + " " + col + ",";
}
requete = requete.substring(0, requete.length()-1);
requete = requete + " from " + ti.getNom();
System.out.println(requete);
ResultSet rs = stmt.executeQuery(requete);
while(rs.next()){
ArrayList<String> ligne=new ArrayList<String>();
ResultSetMetaData rsmd = rs.getMetaData();
int nbcol=rsmd.getColumnCount();
for(int i=1;i<=nbcol;i++){
ligne.add(rs.getString(i));
}
lignes.add(ligne);
}
ti.setLignes(lignes);
}
}catch(Exception e){
System.out.println("probleme de connection");
e.printStackTrace();
}finally{
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
@Override
public void faireRequete(String requete) {
Connection con=null;
try{
con= getDataSource().getConnection();
Statement stmt = con.createStatement();
stmt.executeUpdate(requete);
}catch(Exception e){
System.out.println("probleme de connection");
e.printStackTrace();
}finally{
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}