service builder to external DB

[Marco Codeluppi, modificado hace 8 años.]

Hi all,
I have a problem that i can't solve by myself.
I make a new custom portlet with service builder connected to external db.
I follow a post for configure the ext-spring.xml file.
I make the service.xml and generate all classes. On jsp I 've used the search-container. Everything work well except the fact that the data showed on the screen are incongruent with the data in the db.
The number of row are correct but the the lines showed are always the same row!!!

I have launched the hibernate query on other editor e the result is fine.
I wrtite a log on the serviceImpl end the data logged seems incorrect. Where I'm wrong?

On the db i have a view not a table, but I think that this isn't the problem.

The external db is DB2 on AS400, the liferay version is 6.2 GA5 in a tomcat bundle on mySql DB.

Can some help me?

Thanks in advance

MArco

[Olaf Kock, modificado hace 8 años.]

Marco Codeluppi:

Where I'm wrong?

Please show the code. So far you've described that you did everything right, but it doesn't work. It's easier to just see the code. Please make it a minimal version - and use the formatting that you have in the messageboard editor to make it easy to read.

[Marco Codeluppi, modificado hace 8 años.]

Hi, thank for reply.

this is service.xml

	<entity name="dspsf03v" local-service="true" remote-service="true" data-source="transcoopDS" session-factory="transcoopSessionFactory" tx-manager="transcoopTransactionManager">
		<column name="codice_cliente_sigep" type="long" primary="true" />
		<column name="targa_automezzo" type="String" />
		<column name="stato_spedizione" type="String" />
		<column name="data_inizio_spedizione" type="long" />
		<column name="ora_inizio_spedizione" type="long" />
		<column name="data_fine_spedizione" type="long" />
		<column name="ora_fine_spedizione" type="long" />
		<column name="indirizzo_consegna" type="String" />
		<column name="localita_consegna" type="String" />
		<column name="itinerario_viaggio" type="String" />
		<column name="url_posizione_automezzo" type="String" />
		<column name="sensori_automezzo" type="String" />
		<column name="telefono_vettore" type="String" />
		<column name="cellulare_vettore" type="String" />
		
		<!-- Order by -->
		<order by="desc">
			<order-column name="data_inizio_spedizione" />
		</order>
	</entity>

this is the dynamyc query on service.impl, metho called by search-container
find_SITCONS_RF and countByfind_SITCONS_RF. In the find method i put the log to see if the problem was on the service imple or in the jsp

protected DynamicQuery buildConsegneDynamicQuery(long codForSigep, long daData, long aData, String targa) {

		_log.debug("[dspvi09vLocalServiceImpl::buildConsegneDynamicQuery] Begin");
		_log.debug("[dspvi09vLocalServiceImpl::buildConsegneDynamicQuery] codForSigep = " + codForSigep);
		_log.debug("[dspvi09vLocalServiceImpl::buildConsegneDynamicQuery] daData = " + daData);
		_log.debug("[dspvi09vLocalServiceImpl::buildConsegneDynamicQuery] aData = " + aData);
		_log.debug("[dspvi09vLocalServiceImpl::buildConsegneDynamicQuery] targa = " + targa);
		
		//DynamicQuery dinQuery = dynamicQuery();
		DynamicQuery dinQuery = DynamicQueryFactoryUtil.forClass(dspvi09v.class, getClassLoader());
		
		if(Validator.isNotNull(codForSigep)) {
			Property property = PropertyFactoryUtil.forName("codice_fornitore_sigep");
			dinQuery.add(property.eq(codForSigep));
		}
		
		if(Validator.isNotNull(daData) && Validator.isNotNull(aData)) {
			Property property = PropertyFactoryUtil.forName("data_inizio_spedizione");
			dinQuery.add(property.between(daData, aData));
		}
		
		if(Validator.isNotNull(targa)) {
			Property property = PropertyFactoryUtil.forName("targa_automezzo");
			dinQuery.add(property.eq(targa));
		}
		
		Order defaultOrder = OrderFactoryUtil.desc("data_inizio_spedizione");
		dinQuery.addOrder(defaultOrder);
		
		return dinQuery;
		
	}
	
	protected DynamicQuery buildConsegneDynamicCountQuery(long codForSigep, long daData, long aData, String targa) {

		_log.debug("[dspvi09vLocalServiceImpl::buildConsegneDynamicQuery] Begin");
		_log.debug("[dspvi09vLocalServiceImpl::buildConsegneDynamicQuery] codForSigep = " + codForSigep);
		_log.debug("[dspvi09vLocalServiceImpl::buildConsegneDynamicQuery] daData = " + daData);
		_log.debug("[dspvi09vLocalServiceImpl::buildConsegneDynamicQuery] aData = " + aData);
		_log.debug("[dspvi09vLocalServiceImpl::buildConsegneDynamicQuery] targa = " + targa);
		
		//DynamicQuery dinQuery = dynamicQuery();
		DynamicQuery dinQuery = DynamicQueryFactoryUtil.forClass(dspvi09v.class, getClassLoader());
		
		if(Validator.isNotNull(codForSigep)) {
			Property property = PropertyFactoryUtil.forName("codice_fornitore_sigep");
			dinQuery.add(property.eq(codForSigep));
		}
		
		if(Validator.isNotNull(daData) && Validator.isNotNull(aData)) {
			Property property = PropertyFactoryUtil.forName("data_inizio_spedizione");
			dinQuery.add(property.between(daData, aData));
		}
		
		if(Validator.isNotNull(targa)) {
			Property property = PropertyFactoryUtil.forName("targa_automezzo");
			dinQuery.add(property.eq(targa));
		}
		
		return dinQuery;
		
	}
	
	public List<dspvi09v> find_SITCONS_RF(long codForSigep, String daData, String aData, String targa, int start, int end, OrderByComparator orderByComparator) {
		try {
			_log.debug("[dspvi09vLocalServiceImpl::find_SITCONS_RF] Begin");
			_log.debug("[dspvi09vLocalServiceImpl::find_SITCONS_RF] codForSigep = " + codForSigep);
			_log.debug("[dspvi09vLocalServiceImpl::find_SITCONS_RF] daData = " + daData);
			_log.debug("[dspvi09vLocalServiceImpl::find_SITCONS_RF] aData = " + aData);
			_log.debug("[dspvi09vLocalServiceImpl::find_SITCONS_RF] targa = " + targa);
			
			long da, a;
			if (Validator.isNotNull(daData)) {
				da = new Long(dbFormatter.format(formatter.parse(daData)));
			} else {
				da = new Long(dbFormatter.format(new Date()));
			}
			if (Validator.isNotNull(aData)) {
				a = new Long(dbFormatter.format(formatter.parse(aData)));
			} else {
				a = new Long(dbFormatter.format(new Date()));
			}
			
			DynamicQuery dynamicQuery = buildConsegneDynamicQuery(codForSigep, da, a, targa);
			
			List<dspvi09v> lista = dspvi09vLocalServiceUtil.dynamicQuery(dynamicQuery, start, end, orderByComparator);
			
			for (dspvi09v dspvi09v : lista) {
				_log.debug("[dspvi09vLocalServiceImpl::find_SITCONS_RF] cliente fatturazione = " + dspvi09v.getCliente_fatturazione());
			}
			
			return lista;
		} catch (SystemException e) {
			_log.error("[dspvi09vLocalServiceImpl::find_SITCONS_RC] SystemException!! " , e);
			e.printStackTrace();
			return null;
		} catch (NumberFormatException e) {
			_log.error("[dspvi09vLocalServiceImpl::find_SITCONS_RC] NumberFormatException!! " , e);
			e.printStackTrace();
			return null;
		} catch (ParseException e) {
			_log.error("[dspvi09vLocalServiceImpl::find_SITCONS_RC] ParseException!! " , e);
			e.printStackTrace();
			return null;
		} 
	}
	
	public int countByfind_SITCONS_RF(long codForSigep, String daData, String aData, String targa) {
		try {
			_log.debug("[dspvi09vLocalServiceImpl::countByfind_SITCONS_RF] Begin");
			_log.debug("[dspvi09vLocalServiceImpl::countByfind_SITCONS_RF] codForSigep = " + codForSigep);
			_log.debug("[dspvi09vLocalServiceImpl::countByfind_SITCONS_RF] daData = " + daData);
			_log.debug("[dspvi09vLocalServiceImpl::countByfind_SITCONS_RF] aData = " + aData);
			_log.debug("[dspvi09vLocalServiceImpl::countByfind_SITCONS_RF] targa = " + targa);
			
			long da, a;
			if (Validator.isNotNull(daData)) {
				da = new Long(dbFormatter.format(formatter.parse(daData)));
			} else {
				da = new Long(dbFormatter.format(new Date()));
			}
			if (Validator.isNotNull(aData)) {
				a = new Long(dbFormatter.format(formatter.parse(aData)));
			} else {
				a = new Long(dbFormatter.format(new Date()));
			}
			
			DynamicQuery dynamicQuery = buildConsegneDynamicCountQuery(codForSigep, da, a, targa);
			return (int)dspvi09vLocalServiceUtil.dynamicQueryCount(dynamicQuery);
			
		} catch (SystemException e) {
			_log.error("[dspvi09vLocalServiceImpl::countByfind_SITCONS_RF] SystemException!! " , e);
			e.printStackTrace();
			return 0;
		} catch (NumberFormatException e) {
			_log.error("[dspvi09vLocalServiceImpl::countByfind_SITCONS_RF] NumberFormatException!! " , e);
			e.printStackTrace();
			return 0;
		} catch (ParseException e) {
			_log.error("[dspvi09vLocalServiceImpl::countByfind_SITCONS_RF] ParseException!! " , e);
			e.printStackTrace();
			return 0;
		}
</dspvi09v></dspvi09v>

and thi is the jsp:

	<liferay-ui:search-container displayterms="<%= new DisplayTerms(renderRequest) %>" delta="10" deltaconfigurable="true" iteratorurl="<%= iteratorURL %>" emptyresultsmessage="no-consegne">
	
		<liferay-ui:search-form page="/html/consegne_search.jsp" servletContext="<%= application %>" />
	
		&lt;%--liferay-ui:search-container-results results="&lt;%= dspvi09vLocalServiceUtil.find_SITCONS_RF(1893, searchContainer.getStart(), searchContainer.getEnd()) %&gt;" total="&lt;%= dspvi09vLocalServiceUtil.countByfind_SITCONS_RF(1893) %&gt;"/ --%&gt;
		<liferay-ui:search-container-results>
			&lt;%
				DisplayTerms displayTerms = searchContainer.getDisplayTerms();
				total = dspvi09vLocalServiceUtil.countByfind_SITCONS_RF(codCliSigep, startDate, endDate, targa); 
				searchContainer.setTotal(total);
				searchContainer.setResults(dspvi09vLocalServiceUtil.find_SITCONS_RF(codCliSigep, startDate, endDate, targa, searchContainer.getStart(), searchContainer.getEnd(), searchContainer.getOrderByComparator()));
			%&gt;
		</liferay-ui:search-container-results>
	    <liferay-ui:search-container-row classname="it.transcoop.portlet.consegne.model.dspvi09v" modelvar="aDspvi09v">
		
			<liferay-ui:search-container-column-text value="<%=aDspvi09v.getTarga_automezzo() %>" name="Targa automezzo" />
			<liferay-ui:search-container-column-text value="<%=aDspvi09v.getStato_spedizione() %>" name="Stato spedizione" />
			<liferay-ui:search-container-column-text value="<%=aDspvi09v.getStato_viaggio() %>" name="Stato viaggio" />
			<liferay-ui:search-container-column-text value="<%=aDspvi09v.getCliente_fatturazione() %>" name="Cliente fatturazione" />
			<liferay-ui:search-container-column-text value="<%=aDspvi09v.getCliente_divisione() %>" name="Cliente divisione" />
			<liferay-ui:search-container-column-text value="<%=String.valueOf(aDspvi09v.getData_inizio_spedizione()) %>" name="Data inizio spedizione" />
			<liferay-ui:search-container-column-text value="<%=String.valueOf(aDspvi09v.getOra_inizio_spedizione()) %>" name="Ora inizio spedizione" />
			<liferay-ui:search-container-column-text value="<%=aDspvi09v.getIndirizzo_consegna() %>" name="Indirizzo consegna" />
			<liferay-ui:search-container-column-text value="<%=aDspvi09v.getLocalita_consegna() %>" name="Localita consegna" />
			<liferay-ui:search-container-column-text value="<%=aDspvi09v.getItinerario_viaggio() %>" name="Itinerario viaggio" />
			<liferay-ui:search-container-column-text value="<%=aDspvi09v.getSensori_automezzo() %>" name="Sensori automezzo" />
			<liferay-ui:search-container-column-text value="tragitto" href="<%=aDspvi09v.getUrl_posizione_automezzo() %>" name="Mappa" />
	
		</liferay-ui:search-container-row>

		&lt;%-- liferay-ui:search-iterator / --%&gt;		
		<liferay-ui:search-iterator />

	</liferay-ui:search-container>

need some other snippet of code?

Thanks

MArco

[Marco Codeluppi, modificado hace 8 años.]

Hi All,

can anyone help me?

A little upgrade, during the deploy of the custom portlet i notice this message:

17:50:17,711 INFO [localhost-startStop-2][SettingsFactory:125] Database ->_ name : DB2 UDB for AS/400_ version : 07.01.0000 V7R1m0_ major : 7_
minor : 1 [Sanitized]
17:50:17,713 INFO [localhost-startStop-2][SettingsFactory:125] Driver ->_ name : AS/400 Toolbox for Java JDBC Driver_ version : 9.11_ major : 9_
minor : 11 [Sanitized]

Is possible that the driver give me the problem?

Thanks in advance

MArco

[Marco Codeluppi, modificado hace 8 años.]

Hi All,

the problem is due to the fact that the view exposed by DB does not has a primary key nor a key field.

So, I think, when Liferay put the data from resultset to the model it use only the first row.
This because the field used in service.xml for indicate the primary key has always the same value because is a "principal" field af the where condition.

In fact the portlet show the delivery of a the customers of a transport company.

I've launched the SB and the write a search container on an entity with primary key and all is doing well!

Thaks all!!

Bye bye

MArco

[Olaf Kock, modificado hace 8 años.]

Good that you've found it yourself. I didn't find anything suspicious and was hoping for someone else to chime in.

With regards to "can it be that the DB2-driver ...?": One of the worst memories in my IT life involves the downgrade by 3 patchlevels of the DB2 driver (or rather: full client installation) in order to fix a total JVM crash on executing a simple (and I really mean: simple) SQL command. Took half a day to identify the downgrade as possible fix and the other half of the day (and violation of all policies) to get the package through the firewall of a bank. Of course, when we now were three patchlevels behind, IBM support refused to even talk to us - even though there was a good reason for us to do so.

I promised to not lay my hands on DB2 for 5 years back then. That time is over now - it's ancient memory, and it still surfaces every single time I'm dealing with that db.