Go to the documentation of this file.00001 package myDataBases;
00002
00003
00004 import java.sql.Connection;
00005 import java.sql.DriverManager;
00006 import java.sql.ResultSet;
00007 import java.sql.Statement;
00008 import java.util.*;
00009
00010 import myShell.Interpreter;
00011 import myShell.LogWriter;
00012
00013
00014
00033 public class MySQLbase {
00034
00035
00036
00037
00038
00039
00043 private String adr;
00047 private String login;
00051 private String passwd;
00055 private Connection conSQL;
00060
00061 private String[] columnsHardDataSQL;
00066 private Boolean[] isStringHardData;
00070 private String queryHardDataStub;
00075
00076 private String[] columnsResultsSQL = { "axis", "idMedia1", "idMedia2", "greater", "nameMedia1", "nameMedia2" };
00080 private Boolean[] isStringResults = { true, false, false, false, true, true};
00084 private String queryResultsStub ;
00085
00086
00087
00088
00089
00090
00104 public MySQLbase( String adr, String login, String passwd, List<String> fieldsHardData,
00105 List<String> fieldsType)
00106 {
00107 this.adr = adr;
00108 this.login = login;
00109 this.passwd = passwd;
00110 this.columnsHardDataSQL = fieldsHardData.toArray(new String[0]) ;
00111
00112
00113 List<Boolean> isFieldAString = new ArrayList <Boolean>() ;
00114 for (String type : fieldsType)
00115 {
00116 if (type.equals("INT") || type.equals("FLOAT(23)") || type.equals("BIGINT") )
00117 isFieldAString.add(false);
00118 else
00119 isFieldAString.add(true);
00120 this.isStringHardData = isFieldAString.toArray(new Boolean[0]);
00121 }
00122
00123
00124 this.queryHardDataStub = "INSERT INTO hit (";
00125 for (int i=0; i<this.columnsHardDataSQL.length-1; i++)
00126 this.queryHardDataStub += this.columnsHardDataSQL[i]+", ";
00127 this.queryHardDataStub += this.columnsHardDataSQL [this.columnsHardDataSQL.length - 1] + ") VALUES (";
00128
00129
00130 this.queryResultsStub = "INSERT INTO results (";
00131 for (int i=0; i<this.columnsResultsSQL.length-1; i++)
00132 this.queryResultsStub += this.columnsResultsSQL[i]+", ";
00133 this.queryResultsStub += this.columnsResultsSQL [this.columnsResultsSQL.length - 1] + ") VALUES (";
00134
00135
00136 try
00137 {
00138 Class.forName("com.mysql.jdbc.Driver");
00139 this.conSQL = DriverManager.getConnection(this.adr,this.login, this.passwd);
00140 }
00141 catch(Exception e) { e.printStackTrace(); }
00142 }
00143
00144
00145
00146
00147
00148
00149
00162 public static Boolean testConnection(LogWriter log, String testPasswd)
00163 {
00164 Boolean connected = false;
00165 Map<String,String> config = Interpreter.getConfig();
00166 try
00167 {
00168 Class.forName("com.mysql.jdbc.Driver");
00169 DriverManager.getConnection( "jdbc:mysql://localhost:3306/" + config.get("sqlBase") ,
00170 config.get("sqlUser"), testPasswd);
00171 connected = true;
00172 }
00173 catch(Exception e)
00174 {
00175 e.printStackTrace();
00176 connected = false;
00177 }
00178 if (connected)
00179 log.append("connection with the database works.\n");
00180 else
00181 log.append("ERROR : A problem was encountered while connecting with the database.\n" +
00182 " Interrupting.");
00183 return connected;
00184 }
00185
00201 public boolean alreadyDone( int idMedia1, int idMedia2, String axis )
00202 {
00203 String query1 = "SELECT greater FROM results WHERE axis='"+axis;
00204 String query2 = "SELECT greater FROM results WHERE axis='"+axis;
00205 boolean result = false;
00206
00207 query1 += "' AND idMedia1="+idMedia1+" AND idMedia2="+idMedia2+" ;";
00208 query2 += "' AND idMedia1="+idMedia2+" AND idMedia2="+idMedia1+" ;";
00209
00210 try
00211 {
00212 Statement stmt = this.conSQL.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
00213 ResultSet rs;
00214 rs = stmt.executeQuery(query1);
00215 if (rs.next())
00216 result = true;
00217 else
00218 {
00219 rs = stmt.executeQuery(query2);
00220 if (rs.next())
00221 result = true;
00222 }
00223 rs.close();
00224 stmt.close();
00225 }
00226 catch(Exception e) { e.printStackTrace(); }
00227 return result;
00228 }
00229
00230
00243 public Boolean comparisonResult( int idMedia1, int idMedia2 , String axis)
00244 {
00245 String query1 = "SELECT greater FROM results WHERE axis='"+axis;
00246 String query2 = "SELECT greater FROM results WHERE axis='"+axis;
00247 boolean result = true;
00248
00249 query1 += "' AND idMedia1="+idMedia1+" AND idMedia2="+idMedia2+" ;";
00250 query2 += "' AND idMedia1="+idMedia2+" AND idMedia2="+idMedia1+" ;";
00251
00252 try
00253 {
00254 Statement stmt = this.conSQL.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
00255 ResultSet rs;
00256 rs = stmt.executeQuery(query1);
00257 if (rs.next())
00258 result = (rs.getInt("greater") == 1 );
00259 else
00260 {
00261 rs = stmt.executeQuery(query2);
00262 if (rs.next())
00263 result = (rs.getInt("greater") == 2 );
00264 }
00265 rs.close();
00266 stmt.close();
00267 }
00268 catch(Exception e) { e.printStackTrace(); }
00269 return result;
00270 }
00271
00272
00273
00287 public void insertHardDataInSQL( List<String[]> entries)
00288 {
00289 for (int i=0; i< entries.size(); i++)
00290 {
00291 String[] entry = entries.get(i);
00292 String query = this.queryHardDataStub;
00293 Integer endLoop;
00294
00295
00296 if (entry.length < this.columnsHardDataSQL.length )
00297 endLoop = entry.length;
00298 else
00299 endLoop = this.columnsHardDataSQL.length;
00300
00301
00302 for (int j=0; j<endLoop; j++)
00303 {
00304 if (this.isStringHardData[j])
00305 query += "'" + entry[j] + "'";
00306 else
00307 if (entry[j].length() == 0)
00308 query += "0";
00309 else
00310 query += entry[j];
00311 if (j == endLoop-1)
00312 query += ") ;\n";
00313 else
00314 query += ", ";
00315 }
00316 try
00317 {
00318 Statement stmt = this.conSQL.createStatement();
00319 stmt.executeUpdate(query);
00320 }
00321 catch(Exception e) { e.printStackTrace(); }
00322 }
00323 }
00324
00325
00326
00339 public void insertResultsInSQL( List<String[]> entries, MediaBase dbNames)
00340 {
00341 for (int i=0; i<entries.size(); i++)
00342 {
00343 String[] entry = entries.get(i);
00344 String query = this.queryResultsStub;
00345
00346 for (int j=0; j<entry.length-1; j++)
00347 if (this.isStringResults[j])
00348 query += "'" + entry[j] + "', ";
00349 else
00350 query += entry[j] + ", ";
00351
00352 query += entry[3] + ", " ;
00353
00354 query += "'" + dbNames.getMedia( Integer.parseInt(entry[1]) ) + "', '"
00355 + dbNames.getMedia( Integer.parseInt(entry[2]) ) + "') ;" ;
00356 try
00357 {
00358
00359 Statement stmt = this.conSQL.createStatement();
00360 stmt.executeUpdate(query);
00361 } catch(Exception e) { e.printStackTrace(); }
00362 }
00363 }
00364
00365
00366
00367
00376 public void generateDump()
00377 {
00378 try
00379 {
00380 Calendar cal = Calendar.getInstance();
00381 String dumpName = "DBcps_" + cal.get(Calendar.DATE) + "-" + cal.get(Calendar.MONTH)
00382 + "-" + cal.get(Calendar.YEAR) + ".sql";
00383 String [] cmd = { "../data/mysql/dump.sh",this.login,this.passwd, dumpName};
00384 Runtime.getRuntime().exec(cmd);
00385 }
00386 catch(Exception e) { e.printStackTrace(); }
00387 }
00388
00389 }