[Java] StoredProcedure con più parametri di out con JPA

Linguaggi di programmazione: php, perl, python, C, bash e tutti gli altri.
Scrivi risposta
Avatar utente
Saruman
Entusiasta Emergente
Entusiasta Emergente
Messaggi: 2378
Iscrizione: venerdì 16 marzo 2007, 22:29

[Java] StoredProcedure con più parametri di out con JPA

Messaggio da Saruman »

Ciao ragazzi

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();
						
Ho testoto anche la stored procedure passandogli i parametri e funziona benissimo, commetto qualche errore io mi date una mano?

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) );
	}
}
Scrivi risposta

Ritorna a “Programmazione”

Chi c’è in linea

Visualizzano questa sezione: 0 utenti iscritti e 5 ospiti