package oracletestapplication.services;
import oracletestapplication.tests.ConnectionData;
import oracletestapplication.ui.tables.ExecutionPlanRow;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.StringTokenizer;
public class DatabaseService {
private DatabaseService() {
// Az oszt�lyb�l nem lehet p�ld�nyt l�trehozni.
}
public static Connection createConnection(ConnectionData connectionData) {
try {
Class.forName(connectionData.getDriverName());
Connection connection = DriverManager.getConnection(connectionData.getHost(), connectionData
.getDatabaseName(), connectionData.getDatabasePassword());
connection.setAutoCommit(false);
return connection;
} catch (ClassNotFoundException e) {
e.printStackTrace();
return null;
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
public static void closeConnection(Connection connection) {
try {
if (!connection.isClosed()) {
connection.commit();
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static ArrayList<ExecutionPlanRow> getExecutionPlan(ConnectionData connectionData, String statement) {
try {
Connection connection = createConnection(connectionData);
PreparedStatement stmnt = connection.prepareStatement("explain plan for " + statement);
stmnt.execute();
// A dbms_xplan.display() elj�r�s form�zott eredm�ny�t haszn�ljuk
// fel. Ez az elj�r�s t�bb sor Stringet ad vissza, melyb�l ki fogjuk
// sz�rni a nek�nk sz�ks�geseket.
stmnt = connection
.prepareStatement("select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'))");
ResultSet rs = stmnt.executeQuery();
ArrayList<ExecutionPlanRow> result = new ArrayList<ExecutionPlanRow>();
// Az els� �t sorban sz�munkra l�nyegtelen �rt�kek vannak.
for (int i = 0; i < 5; i++) {
rs.next();
}
while (rs.next()) {
String line = rs.getString(1);
if (line.endsWith("-")) {
break;
}
// Az egyes �rt�keket egy | jel v�laszja el egym�st�l, ezek
// ment�n sz�tv�gjuk a Stringet.
StringTokenizer tokenizer = new StringTokenizer(line, "|");
ExecutionPlanRow row = new ExecutionPlanRow();
row.setId(tokenizer.nextToken());
row.setOperation(tokenizer.nextToken());
row.setName(tokenizer.nextToken());
row.setRows(tokenizer.nextToken());
row.setBytes(tokenizer.nextToken());
row.setCost(tokenizer.nextToken());
row.setTime(tokenizer.nextToken());
// Egyes esetekben l�tezik TmpSpc nev� oszlop is, ezt kisz�rj�k,
// mert nincs r� sz�ks�g�nk.
if (tokenizer.hasMoreTokens()) {
row.setCost(row.getTime());
row.setTime(tokenizer.nextToken());
}
result.add(row);
}
rs.close();
stmnt.close();
closeConnection(connection);
return result;
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
public static void flushSharedPool(ConnectionData connectionData) {
try {
Connection connection = createConnection(connectionData);
PreparedStatement stmnt = connection.prepareStatement("alter system flush shared_pool");
stmnt.execute();
closeConnection(connection);
} catch (SQLException e) {
e.printStackTrace();
}
}
}