MySQLbase.java

Go to the documentation of this file.

package myDataBases;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;

import myShell.Interpreter;
import myShell.LogWriter;



public class MySQLbase {

  00036   // Fields
  00038 

  private String adr;
  private String login;
  private String passwd;
  private Connection conSQL;
// concerning hard data
  private String[] columnsHardDataSQL;
  private Boolean[] isStringHardData;
  private String queryHardDataStub;
// concerning refined results 
  private String[] columnsResultsSQL = { "axis", "idMedia1", "idMedia2", "greater", "nameMedia1", "nameMedia2" };
  private Boolean[] isStringResults = { true, false, false, false, true, true};
  private String queryResultsStub ;
  
  
  00088   // Constructors
  00090 
  public MySQLbase( String adr, String login, String passwd, List<String> fieldsHardData,
              List<String> fieldsType) 
  {
        this.adr = adr;
        this.login = login;
        this.passwd = passwd;
        this.columnsHardDataSQL = fieldsHardData.toArray(new String[0]) ;
        
        // Creation of the isStringHarData array
        List<Boolean> isFieldAString = new ArrayList <Boolean>() ; 
        for (String type : fieldsType)
        {
              if (type.equals("INT") || type.equals("FLOAT(23)") || type.equals("BIGINT") )
                    isFieldAString.add(false);
              else
                    isFieldAString.add(true);
              this.isStringHardData = isFieldAString.toArray(new Boolean[0]);
        }
        
        // Creation of the Hard Data query stub
        this.queryHardDataStub = "INSERT INTO hit (";
        for (int i=0; i<this.columnsHardDataSQL.length-1; i++)
              this.queryHardDataStub += this.columnsHardDataSQL[i]+", ";
        this.queryHardDataStub += this.columnsHardDataSQL [this.columnsHardDataSQL.length - 1] + ") VALUES (";
        
        // Creation of the Results query stub
        this.queryResultsStub = "INSERT INTO results (";
        for (int i=0; i<this.columnsResultsSQL.length-1; i++)
              this.queryResultsStub += this.columnsResultsSQL[i]+", ";
        this.queryResultsStub += this.columnsResultsSQL [this.columnsResultsSQL.length - 1] + ") VALUES (";
        
        // connection to the MySQL base
        try
        {
            Class.forName("com.mysql.jdbc.Driver");   //Register the JDBC driver for MySQL.
            this.conSQL = DriverManager.getConnection(this.adr,this.login, this.passwd);
        }
        catch(Exception e)  { e.printStackTrace(); }
  }
  
  
  00146   // Methods
  00148 

  public static Boolean testConnection(LogWriter log, String testPasswd)
  {
        Boolean connected = false;
        Map<String,String> config = Interpreter.getConfig();
        try
        {
            Class.forName("com.mysql.jdbc.Driver");   //Register the JDBC driver for MySQL.
            DriverManager.getConnection( "jdbc:mysql://localhost:3306/" + config.get("sqlBase") ,
                    config.get("sqlUser"), testPasswd);
            connected = true;
        }
        catch(Exception e)
        {
              e.printStackTrace();
              connected = false;
        }
        if (connected)
                  log.append("connection with the database works.\n");
        else
                  log.append("ERROR : A problem was encountered while connecting with the database.\n" +
                  " Interrupting.");
        return connected;
  }

  public boolean alreadyDone( int idMedia1, int idMedia2, String axis )
  {
        String query1 = "SELECT greater FROM results WHERE axis='"+axis;
        String query2 = "SELECT greater FROM results WHERE axis='"+axis;
        boolean result = false;
        
        query1 += "' AND idMedia1="+idMedia1+" AND idMedia2="+idMedia2+" ;";
        query2 += "' AND idMedia1="+idMedia2+" AND idMedia2="+idMedia1+" ;";
        
        try
        {
              Statement stmt = this.conSQL.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
              ResultSet rs;
              rs = stmt.executeQuery(query1);
              if (rs.next())
                    result = true;
              else
              {
                    rs = stmt.executeQuery(query2);
                    if (rs.next())
                          result = true;
              }
              rs.close();
              stmt.close();
        }
        catch(Exception e)  { e.printStackTrace(); }
        return result;
  }


  public Boolean comparisonResult( int idMedia1, int idMedia2 , String axis)
  {
        String query1 = "SELECT greater FROM results WHERE axis='"+axis;
        String query2 = "SELECT greater FROM results WHERE axis='"+axis;
        boolean result = true;
        
        query1 += "' AND idMedia1="+idMedia1+" AND idMedia2="+idMedia2+" ;";
        query2 += "' AND idMedia1="+idMedia2+" AND idMedia2="+idMedia1+" ;";
        
        try
        {
              Statement stmt = this.conSQL.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
              ResultSet rs;
              rs = stmt.executeQuery(query1);
              if (rs.next())
                    result = (rs.getInt("greater") == 1 );
              else
              {
                    rs = stmt.executeQuery(query2);
                    if (rs.next())
                          result = (rs.getInt("greater") == 2 );
              }
              rs.close();
              stmt.close();
        }
        catch(Exception e)  { e.printStackTrace(); }
        return result;
  }


  
  public void insertHardDataInSQL( List<String[]> entries)
  {  
        for (int i=0; i< entries.size(); i++)
        {
              String[] entry = entries.get(i);
              String query = this.queryHardDataStub;
              Integer endLoop;
              
              // Determining the number of fields to write in the SQL request
              if (entry.length < this.columnsHardDataSQL.length )
                    endLoop = entry.length;
              else
                    endLoop = this.columnsHardDataSQL.length;
              
            // Writing each field but the last one
              for (int j=0; j<endLoop; j++)
              {
                    if (this.isStringHardData[j])     // if it is a string, we print it between "'"
                          query += "'" + entry[j] + "'";
                    else      // if it is a number
                          if (entry[j].length() == 0) // if it is null, we write 0
                                query += "0";
                          else      // else we write it
                                query += entry[j];
                    if (j == endLoop-1)   // are we at the end of the request ?
                          query += ") ;\n";     // if yes, we close it with a parenthesis and a semicolon
                    else
                          query += ", ";  // otherwise, we just a put a comma
              }
              try
              {
                    Statement stmt = this.conSQL.createStatement();
                    stmt.executeUpdate(query);
              }
              catch(Exception e)  { e.printStackTrace(); }
        }
  }


  
  public void insertResultsInSQL( List<String[]> entries, MediaBase dbNames)
  {  
        for (int i=0; i<entries.size(); i++)
        {
              String[] entry = entries.get(i);
              String query = this.queryResultsStub;
              // adding the content of the entry in the query
              for (int j=0; j<entry.length-1; j++)
                    if (this.isStringResults[j])
                          query += "'" + entry[j] + "', ";
                    else
                          query += entry[j] + ", ";
              // adding the "greater" entry
              query += entry[3] + ", " ;
              // adding the names of the media considered
              query += "'" + dbNames.getMedia( Integer.parseInt(entry[1]) ) + "', '"
                              + dbNames.getMedia( Integer.parseInt(entry[2]) )  + "') ;" ;
              try
              {
                    //System.out.println(query);
                    Statement stmt = this.conSQL.createStatement();
                    stmt.executeUpdate(query);
              } catch(Exception e)  { e.printStackTrace(); }
        }
  }
  
  
  

  public void generateDump()
  {
        try
        {
              Calendar cal = Calendar.getInstance();
              String dumpName = "DBcps_" + cal.get(Calendar.DATE) + "-" + cal.get(Calendar.MONTH)
                                          + "-" + cal.get(Calendar.YEAR) + ".sql";
              String [] cmd = { "../data/mysql/dump.sh",this.login,this.passwd, dumpName};
              Runtime.getRuntime().exec(cmd);
        }
        catch(Exception e)  { e.printStackTrace(); }
  }

}