Sono alle prese con una storedProcedure da convertire in JPA, la mia grossa difficoltà in questo caso è la presenza di più parametri di out. Ho seguito questa guida
http://www.yenlo.nl/calling-oracle-stor ... arameters/ ed il mio codice compila, ma non produce risultati: o meglio legge sempre null
Codice: Seleziona tutto
int max=0;
if(record.get("P_TAGMAX")!=null)
max =((BigDecimal)record.get("P_TAGMAX")).intValue();
if(record.get("P_ALBUMCOUNT")!=null)
sAlbumCount = "" + ((BigDecimal)record.get("P_ALBUMCOUNT")).intValue();
if(record.get("P_DOCUMENTCOUNT") !=null)
sDocumentCount = "" + ((BigDecimal)record.get("P_DOCUMENTCOUNT")).intValue();
if(record.get("P_FOTOCOUNT")!=null)
sFotoCount = "" + ((BigDecimal)record.get("P_FOTOCOUNT")).intValue();
if(record.get("P_VIDEOCOUNT")!=null)
sVideoCount = "" + ((BigDecimal)record.get("P_VIDEOCOUNT")).intValue();
Codice Originale
Codice: Seleziona tutto
package it.coopitalia.vivido.coopstream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import oracle.jdbc.OracleTypes;
import org.apache.log4j.Logger;
public class CSTagCloudVecchio {
private List<CSTag> tagClouds;
private String sTagCloudNumber = "";
private String sHandle = "";
private boolean bInArchive = false;
private String sAlbumCount = "0";
private String sDocumentCount = "0";
private String sFotoCount = "0";
private String sVideoCount = "0";
private String sHandleTmp= "";
Logger logger = Logger.getLogger(CSTagCloudVecchio.class.getName());
public String getTagCloudNumber(){
getTagClouds();
if(sTagCloudNumber == "") sTagCloudNumber = "0";
return sTagCloudNumber;
}
public void setHandle(String val){
sHandle = val;
}
public void setInArchive(boolean val){
bInArchive = val;
}
public List<CSTag> getTagClouds() {
tagClouds = new ArrayList<CSTag>();
Connection conn = null;
CallableStatement stmt = null;
ResultSet rs = null;
try{
String sIsAdmin = "N";
String sPrgRuo = "0";
String sPrgtMkt = "0";
String sContext = "ALL";
String sScope = "ATTIVI";
java.util.Map paramsS = javax.faces.context.FacesContext.getCurrentInstance().getExternalContext().getSessionMap();
if(paramsS.containsKey("HANDLE"))
sHandle = paramsS.get("HANDLE").toString();
if(paramsS.containsKey("ISMASTER")){
if(Boolean.parseBoolean(paramsS.get("ISMASTER").toString()))
sIsAdmin = "S";
}
if(paramsS.containsKey("PRGRUO"))
sPrgRuo = paramsS.get("PRGRUO").toString();
if(paramsS.containsKey("SETMKT"))
{
//sPrgtMkt = paramsS.get("SETMKT").toString();
sPrgtMkt = ""+((SetMkt)paramsS.get("SETMKT")).getNumPrgMkt();
}
if(paramsS.containsKey("sContext"))
{
sContext = paramsS.get("sContext").toString();
}
if(paramsS.containsKey("sScope"))
{
sScope = paramsS.get("sScope").toString();
bInArchive = sScope.contains("ARCHIVIO");
}
Context initCtx = new InitialContext();
DataSource ds = (DataSource)initCtx.lookup("java:comp/env/dsJNDI");
conn = ds.getConnection();
//20120224 Da richiamare con Entity Manager ------------------------------------------------------------------------------
stmt = conn.prepareCall("BEGIN coopstrp.KSTR_CORE.CPS_Get_TagCloud_And_Counts(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); END;");
stmt.setString(1, sPrgRuo);
stmt.setString(2, sPrgtMkt);
stmt.setString(3, sIsAdmin);
if(bInArchive) stmt.setString(4, "S");
else stmt.setString(4, "N");
stmt.registerOutParameter(5, OracleTypes.CURSOR); //REF CURSOR
stmt.registerOutParameter(6, OracleTypes.INTEGER);//Tag count
stmt.registerOutParameter(7, OracleTypes.INTEGER);//Tag min
stmt.registerOutParameter(8, OracleTypes.INTEGER);//Tag max
stmt.registerOutParameter(9, OracleTypes.INTEGER);//Album count
stmt.registerOutParameter(10, OracleTypes.INTEGER);//Document Count
stmt.registerOutParameter(11, OracleTypes.INTEGER);//Foto Count
stmt.registerOutParameter(12, OracleTypes.INTEGER);//Video Count
stmt.execute();
rs =(ResultSet)((CallableStatement)stmt).getObject(5);
// int count = ((CallableStatement)stmt).getInt(5);
int max = ((CallableStatement)stmt).getInt(8);
sAlbumCount = "" + ((CallableStatement)stmt).getInt(9);
sDocumentCount = "" + ((CallableStatement)stmt).getInt(10);
sFotoCount = "" + ((CallableStatement)stmt).getInt(11);
sVideoCount = "" + ((CallableStatement)stmt).getInt(12);
int ciclo = 0;
while (rs.next())
{
if(ciclo > 30) continue;
//Calcolo la percentuale di dimensione
int n = rs.getInt("Numero");
double d = ((double)(n/max) * 100);
String tag = rs.getString("Tag");
int dim = 5;
if(d >= 99)
{
dim = 0;
}
else if(d >= 70)
{
dim = 1;
}
else if (d >= 40)
{
dim = 2;
}
else if(d>= 20)
{
dim = 3;
}
else if(d>=3)
{
dim = 4;
}
CSTag _tag = new CSTag();
_tag.setTagCSS("cloud"+dim);
_tag.setTagName(tag);
tagClouds.add(_tag);
ciclo++;
}
sTagCloudNumber = "" + ciclo;
}catch(Exception e){
logger.error("COOPStream. Errore Su CSTagCloud: " + e.getMessage());
}finally{
try{
rs.close();
stmt.close();
if(conn != null) conn.close();
}catch(Exception ex){
logger.error("COOPStream. Errore Su disallocazione CSTagCloud: " + ex.getMessage());
}finally{
rs = null;
stmt = null;
conn = null;
}
}
return tagClouds;
}
public String getSAlbumCount() {
return sAlbumCount;
}
public String getSDocumentCount() {
return sDocumentCount;
}
public String getSFotoCount() {
return sFotoCount;
}
public String getSVideoCount() {
return sVideoCount;
}
public String getSObjectsCount(){
return "" + (Integer.parseInt(sAlbumCount) +
Integer.parseInt(sDocumentCount) +
Integer.parseInt(sFotoCount) +
Integer.parseInt(sVideoCount) );
}
}
Mio codice JPA
Codice: Seleziona tutto
package it.coopitalia.vivido.coopstream;
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;
import oracle.jdbc.OracleTypes;
import org.apache.log4j.Logger;
import org.eclipse.persistence.jpa.JpaEntityManager;
import org.eclipse.persistence.jpa.JpaHelper;
import org.eclipse.persistence.jpa.PersistenceProvider;
import org.eclipse.persistence.sessions.*;
import org.eclipse.persistence.queries.DataReadQuery;
import org.eclipse.persistence.queries.StoredProcedureCall;
public class CSTagCloud {
private List<CSTag> tagClouds;
private String sTagCloudNumber = "";
private String sHandle = "";
private boolean bInArchive = false;
private String sAlbumCount = "0";
private String sDocumentCount = "0";
private String sFotoCount = "0";
private String sVideoCount = "0";
private String sHandleTmp= "";
Logger logger = Logger.getLogger(CSTagCloud.class.getName());
public String getTagCloudNumber(){
getTagClouds();
if(sTagCloudNumber == "") sTagCloudNumber = "0";
return sTagCloudNumber;
}
public void setHandle(String val){
sHandle = val;
}
public void setInArchive(boolean val){
bInArchive = val;
}
@SuppressWarnings("unchecked")
public List<CSTag> getTagClouds() {
tagClouds = new ArrayList<CSTag>();
EntityManagerFactory enManFac= null;
JpaEntityManager jpaEM = null;
StoredProcedureCall storedProcedureCall = null;
DataReadQuery dataReadQuery = null;
ResultSet rs = null;
try{
String sIsAdmin = "N";
String sPrgRuo = "0";
String sPrgtMkt = "0";
String sContext = "ALL";
String sScope = "ATTIVI";
java.util.Map paramsS = javax.faces.context.FacesContext.getCurrentInstance().getExternalContext().getSessionMap();
if(paramsS.containsKey("HANDLE"))
sHandle = paramsS.get("HANDLE").toString();
if(paramsS.containsKey("ISMASTER")){
if(Boolean.parseBoolean(paramsS.get("ISMASTER").toString()))
sIsAdmin = "S";
}
if(paramsS.containsKey("PRGRUO"))
sPrgRuo = paramsS.get("PRGRUO").toString();
if(paramsS.containsKey("SETMKT"))
{
//sPrgtMkt = paramsS.get("SETMKT").toString();
sPrgtMkt = ""+((SetMkt)paramsS.get("SETMKT")).getNumPrgMkt();
}
if(paramsS.containsKey("sContext"))
{
sContext = paramsS.get("sContext").toString();
}
if(paramsS.containsKey("sScope"))
{
sScope = paramsS.get("sScope").toString();
bInArchive = sScope.contains("ARCHIVIO");
}
/***************************************************************************************
* Stored procedure convertita in JPA *
* CPS_GET_TAGCLOUD_AND_COUNTS restituisce diversi parametri di out. Testare il corret *
* to funzionamento *
***************************************************************************************/
enManFac = new PersistenceProvider().createEntityManagerFactory("CoopStreamJPA", null);
jpaEM = JpaHelper.getEntityManager(enManFac.createEntityManager());
storedProcedureCall = new StoredProcedureCall();
dataReadQuery = new DataReadQuery();
logger.info("Inizializzo la storedProcedure coopstrp.KSTR_CORE.CPS_Get_TagCloud_And_Counts");
storedProcedureCall.setProcedureName("COOPSTRP.KSTR_CORE.CPS_GET_TAGCLOUD_AND_COUNTS");
storedProcedureCall.addNamedArgument("P_NUMPRGRUOAPP"); //1
storedProcedureCall.addNamedArgument("P_NUMPRGMKTAPP"); //2
storedProcedureCall.addNamedArgument("P_ISADMIN"); //3
String inArchivie;
if(bInArchive){
storedProcedureCall.addNamedArgument("P_INARCHIVE"); //4
inArchivie="S";
}else {
storedProcedureCall.addNamedArgument("P_INARCHIVE"); //4
inArchivie="N";
}
logger.debug("P_NUMPRGRUOAPP ?"+sPrgRuo);
logger.debug("P_NUMPRGMKTAPP ?"+sPrgtMkt);
logger.debug("P_ISADMIN ?"+sIsAdmin);
logger.debug("P_INARCHIVE ?"+inArchivie);
storedProcedureCall.useNamedCursorOutputAsResultSet("P_CURSOR"); //5 Ref CURSOR
storedProcedureCall.addNamedOutputArgument("P_TAGCOUNT"); //6 Tag count
storedProcedureCall.addNamedOutputArgument("P_TAGMIN"); //7 Tag min
storedProcedureCall.addNamedOutputArgument("P_TAGMAX"); //8 Tag max
storedProcedureCall.addNamedOutputArgument("P_ALBUMCOUNT"); //9 Album count
storedProcedureCall.addNamedOutputArgument("P_DOCUMENTCOUNT"); //10 Document Count
storedProcedureCall.addNamedOutputArgument("P_FOTOCOUNT"); //11 Foto Count
storedProcedureCall.addNamedOutputArgument("P_VIDEOCOUNT"); //12 Video Count
dataReadQuery.setCall(storedProcedureCall);
dataReadQuery.addArgument("P_NUMPRGRUOAPP");
dataReadQuery.addArgument("P_NUMPRGMKTAPP");
dataReadQuery.addArgument("P_ISADMIN");
dataReadQuery.addArgument("P_INARCHIVE");
List args = new ArrayList();
args.add(sPrgRuo);
args.add(sPrgtMkt);
args.add(sIsAdmin);
args.add(inArchivie);
logger.info("Lancio la storedProcedure COOPSTRP.KSTR_CORE.CPS_GET_TAGCLOUD_AND_COUNTS");
List results = (List)jpaEM.getActiveSession().executeQuery(dataReadQuery, args);
logger.info("StoredProcedure COOPSTRP.KSTR_CORE.CPS_GET_TAGCLOUD_AND_COUNTS terminata correttamente");
if(results !=null && results.size() >0){
logger.debug("Elaborazione dati storedProcedure GET_TAGCLOUD_AND_COUNTS");
logger.debug("results.size() ?"+results.size());
for(int i=0; i< results.size();i++){
logger.debug("?"+((CSTagCloud) results.get(i)).getSDocumentCount().toString());
}
DatabaseRecord record = (DatabaseRecord)results.get(0);
rs =(ResultSet) record.get("P_CURSOR");
int max=0;
if(record.get("P_TAGMAX")!=null)
max =((BigDecimal)record.get("P_TAGMAX")).intValue();
if(record.get("P_ALBUMCOUNT")!=null)
sAlbumCount = "" + ((BigDecimal)record.get("P_ALBUMCOUNT")).intValue();
if(record.get("P_DOCUMENTCOUNT") !=null)
sDocumentCount = "" + ((BigDecimal)record.get("P_DOCUMENTCOUNT")).intValue();
if(record.get("P_FOTOCOUNT")!=null)
sFotoCount = "" + ((BigDecimal)record.get("P_FOTOCOUNT")).intValue();
if(record.get("P_VIDEOCOUNT")!=null)
sVideoCount = "" + ((BigDecimal)record.get("P_VIDEOCOUNT")).intValue();
logger.debug("max ?"+max);
logger.debug("sAlbumCount ?"+sAlbumCount);
logger.debug("sDocumentCount ?"+sDocumentCount);
logger.debug("sFotoCount ?"+sFotoCount);
logger.debug("sVideoCount ?"+sVideoCount);
int ciclo = 0;
if (rs != null){
while (rs.next())
{
if(ciclo > 30) continue;
//Calcolo la percentuale di dimensione
int n = rs.getInt("Numero");
double d = ((double)(n/max) * 100);
String tag = rs.getString("Tag");
int dim = 5;
if(d >= 99)
{
dim = 0;
}
else if(d >= 70)
{
dim = 1;
}
else if (d >= 40)
{
dim = 2;
}
else if(d>= 20)
{
dim = 3;
}
else if(d>=3)
{
dim = 4;
}
CSTag _tag = new CSTag();
_tag.setTagCSS("cloud"+dim);
_tag.setTagName(tag);
tagClouds.add(_tag);
ciclo++;
}
}else{
logger.debug("Cursore ?0");
}
sTagCloudNumber = "" + ciclo;
}else{
logger.debug("La storedProcedure non ah prodotto risultati da elaborare");
}
}catch(Exception e){
logger.error("COOPStream. Errore Su CSTagCloud: " + e.getMessage());
e.printStackTrace();
}finally{
// try{
// rs.close();
// stmt.close();
// if(conn != null) conn.close();
// }catch(Exception ex){
// logger.error("COOPStream. Errore Su disallocazione CSTagCloud: " + ex.getMessage());
// }finally{
// rs = null;
// stmt = null;
// conn = null;
// }
}
return tagClouds;
}
public String getSAlbumCount() {
return sAlbumCount;
}
public String getSDocumentCount() {
return sDocumentCount;
}
public String getSFotoCount() {
return sFotoCount;
}
public String getSVideoCount() {
return sVideoCount;
}
public String getSObjectsCount(){
return "" + (Integer.parseInt(sAlbumCount) +
Integer.parseInt(sDocumentCount) +
Integer.parseInt(sFotoCount) +
Integer.parseInt(sVideoCount) );
}
}