Stored Procedure Java per Database Oracle: operare correttamente con XMLType


Attraverso il linguaggio JAVA è possibile scrivere delle semplici “Stored Procedure”, il cui ruolo fondamentale è quello di estrarre secondo opportuni criteri le informazioni presenti in alcune tabelle sorgenti, elaborarle ed infine caricarle in apposite tabelle target.

Di seguito viene proposta una soluzione tecnologica alternativa rispetto all’utilizzo del classico Oracle DB-Link integrato all’interno di procedure PL/SQL. Attraverso il driver JDBC è infatti altrettanto possibile stabilire una connessione con un database remoto e compiervi sia operazioni “DDL” che “DML“.

Lo scopo dell’applicazione proposta è quello di effettuare un trasferimento a blocchi dei record presenti in una tabella sorgente residente su un DB Oracle, costantemente popolata da nuovi dati e la cui istanza è attiva su una macchina dedicata, memorizzandoli su una tabella di destinazione, residente su un un DB posto in una macchina differente, ma nell’ambito della stessa rete locale.

Di seguito vengono brevemente descritti gli step funzionali nei quali l’applicazione in oggetto è coinvolta:

1. Download dalla tabella sorgente dei record con flag = ‘N’;
2. Caricamento dei suddetti record nella tabella target;
3. Marcatura del flag a ‘Y’ nella tabella sorgente, in corrispondenza dei record scaricati.

La tabella sorgente di riferimento contiene i seguenti campi:

[sourcecode language=”sql”]
ID NUMBER
XML XMLTYPE
FLAG VARCHAR2 (1 BYTE)
[/sourcecode]

ed eccone un esempio di contenuto:

 

 

 

 

 

 

La tabella target di riferimento ha invece la seguente struttura:

[sourcecode language=”sql”]
ID NUMBER
XML XMLTYPE
[/sourcecode]

ed eccone un esempio di contenuto:

 

 

 

 

 

 

Ecco il risultato atteso che si evince dal contenuto della tabella sorgente dopo l’esecuzione dell’applicazione:

 

 

 

 

 

 

Al termine dell’esecuzione dell’applicazione, alla tabella target verranno aggiunti i record che erano precedentemente marcati con flag = ‘N’ nella tabella sorgente.

Lo sviluppo dell’applicazione è stato condotto nei seguenti contesti tecnologici di riferimento:

Contesto 1:

JVM: Java 1.5.0_22
DBMS: Oracle 11g Enterprise Edition Release 11.2.0.2.0 – 64bit

Driver JDBC di compentenza: ojdbc5.jar

Contesto 2:

JVM: Java 1.6.0_25
DBMS: Oracle 11g Enterprise Edition Release 11.2.0.2.0 – 64bit

Driver JDBC di compentenza: ojdbc6.jar

Ecco di seguito il codice relativo alla classe RecordManager.java, la quale implementa l’applicazione descritta sopra.

[sourcecode language=”java”]
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.OracleResultSet;

public class RecordManager {

private String testMachineA;
private String testMachineB;
private String machineAPort;
private String machineBPort;
private String userIDMachineA;
private String userIDMachineB;
private String passwordMachineA;
private String passwordMachineB;
private String SID_A;
private String SID_B;

private OracleConnection connA = null;
private OracleConnection connB = null;
private String urlA = null;
private String urlB = null;
private OraclePreparedStatement stmtA = null;
private OraclePreparedStatement stmtB = null;
private OracleResultSet resultSet = null;

public OracleConnection getConnA() {
return connA;
}

public OracleConnection getConnB() {
return connB;
}

public RecordManager() {}

public RecordManager(String testMachineA, String testMachineB, String machineAPort, String machineBPort, String userIDMachineA,
String userIDMachineB, String passwordMachineA, String passwordMachineB, String SID_A, String SID_B) {
this.testMachineA = testMachineA;
this.testMachineB = testMachineB;
this.machineAPort = machineAPort;
this.machineBPort = machineBPort;
this.userIDMachineA = userIDMachineA;
this.userIDMachineB = userIDMachineB;
this.passwordMachineA = passwordMachineA;
this.passwordMachineB = passwordMachineB;
this.SID_A = SID_A;
this.SID_B = SID_B;
}

public void makeConnection () {
try {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
urlA = “jdbc:oracle:thin:@” + testMachineA + “:”+ machineAPort + “:” + SID_A;
connA = (OracleConnection) DriverManager.getConnection (urlA, userIDMachineA, passwordMachineA);
urlB = “jdbc:oracle:thin:@” + testMachineB + “:”+ machineBPort + “:” + SID_B;
connB = (OracleConnection) DriverManager.getConnection (urlB, userIDMachineB, passwordMachineB);
} catch(SQLException e) {
e.printStackTrace();
}
}

public int executeQuery (OracleConnection connA, OracleConnection connB) {

String SQL_A = “INSERT INTO TARGET (ID, XML) VALUES (?, xmltype(?))”;
String SQL_B = “SELECT t.id, t.xml.getStringVal() FROM SOURCE t WHERE flag = 'N' order by id asc”;

int lastId = 0;

//Download records from SOURCE TABLE and load into TARGET TABLE
try {
stmtA = (OraclePreparedStatement) connA.prepareStatement(SQL_A);
stmtB = (OraclePreparedStatement) connB.prepareStatement(SQL_B);
resultSet = (OracleResultSet) stmtB.executeQuery();
while (resultSet.next() && resultSet.getObject(2) != null) {
lastId = resultSet.getInt(1);
stmtA.setInt(1, lastId);
stmtA.setStringForClob(2, resultSet.getString(2));
stmtA.executeUpdate();
}
} catch (SQLException e) {
e.printStackTrace();
}
return lastId;
}
//Update source table, marking to 'Y' the downloaded records
public void updateSource(int lastId, OracleConnection connB) {
String SQL = “UPDATE SOURCE SET flag = 'Y' WHERE ID <= ?"; try { stmtB = (OraclePreparedStatement) connB.prepareStatement(SQL); stmtB.setInt(1, lastId); stmtB.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } } public void endConnection() { try { resultSet.close(); stmtA.close(); stmtB.close(); connA.close(); connB.close(); } catch (SQLException e) { e.printStackTrace(); } } public static void main (String args[]) { final String testMachineA = "ora-grid11-scan.it.sedc.internal.yyy.com"; //hostname of TARGET DB machine final String testMachineB = "ora-grid11-scan.it.sedc.internal.xxx.com"; //hostname of SOURCE DB machine final String machineAPort = "1521"; final String machineBPort = "1521"; final String userIDMachineA = "USERNAME"; final String userIDMachineB = "USERNAME"; final String passwordMachineA = "PASSWORD"; final String passwordMachineB = "PASSWORD"; final String SID_A = "SID_A"; final String SID_B = "SID_B"; RecordManager recordManager = new RecordManager(testMachineA, testMachineB, machineAPort, machineBPort, userIDMachineA, userIDMachineB, passwordMachineA, passwordMachineB, SID_A, SID_B); //make DB connection recordManager.makeConnection(); System.out.println("Connection with " + testMachineA + " and " + testMachineB + " establishedn"); //execute download/load query int lastId = recordManager.executeQuery(recordManager.getConnA(), recordManager.getConnB()); //update source table recordManager.updateSource(lastId, recordManager.getConnB()); //end connection recordManager.endConnection(); } } [/sourcecode] E' possibile compilare il codice e generare il relativo bytecode con il seguente comando: [sourcecode language="bash"] javac -classpath ojdbc5.jar RecordManager.java [/sourcecode] Per quanto riguarda il secondo contesto di riferimento, nel quale viene impiegato Java SE 1.6, occorre compilare il codice sorgente associandogli il corretto driver JDBC:

[sourcecode language=”bash”]
javac -classpath ojdbc6.jar RecordManager.java
[/sourcecode]

Una volta ottenuto il bytecode, è possibile lanciarlo in qualità di applicazione stand alone, su una macchina locale, oppure caricarlo su un database Oracle di riferimento, integrandolo all’interno di una funzione scritta in PL/SQL.

 

Se questo post di MondoMobileWeb ti è piaciuto, condividilo sui social network! Diventa Fan di MondoMobileWeb su Facebook, seguici su Twitter, Google+ e iscriviti gratis nel nostro canale @mondomobileweb dell'App Telegram per ricevere tante informazioni e consigli utili in tempo reale.

Aspettiamo sempre i vostri commenti condividendo le vostre esperienze (rispettando il regolamento e il buon senso)! Grazie in anticipo a tutti i visitatori di MondoMobileWeb.it che ci informano aiutando così il consumatore.



Leggi il Regolamento prima di pubblicare un commento nella piattaforma Disqus