Sql Query Hibernate

8 respostas
brucsantos

Boa tarde pessoal

Etou tentando fazer o seguinte select:

SELECT
        fraud.identity,
        fraud.logcase,
        fraud.fraudIn,
        userFraud.nickName,
        userRigged.nickName,
        keyword.name,
        bankInformation.accountHolderDeposit,
        bankInformation.agency,
        bankInformation.accountNumber,
        bank.bankName 
    FROM
        fraud fraud 
    LEFT JOIN
        bankInformation bankInformation 
            ON fraud.bankInformationIdentity = bankInformation.identity 
    INNER JOIN
        bank bank 
            ON bankInformation.bankNameIdentity = bank.identity 
    LEFT JOIN
        keyword keyword 
            ON fraud.keyWordIdentity = keyword.identity 
    INNER JOIN
        users userFraud 
            ON fraud.fraudsterIdentity = userFraud.identity 
    INNER JOIN
        users userRigged 
            ON fraud.riggedIdentity = userRigged.identity 
    INNER JOIN
        product product 
            ON fraud.productIdentity = product.identity 
    LEFT JOIN
        city citySendProduct 
            ON product.productCityIdentity = citySendProduct.identity 
    LEFT JOIN
        state stateSendProduct 
            ON citySendProduct.cityStateIdentity = stateSendProduct.identity 
    LEFT JOIN
        payment payment 
            ON product.paymentIdentity = payment.identity 
    LEFT JOIN
        city cityPayment 
            ON payment.paymentCityIdentity = cityPayment.identity 
    LEFT JOIN
        state statePayment 
            ON cityPayment.cityStateIdentity = statePayment.identity 
    WHERE
        fraud.statusFraud = 'C' 
        AND fraud.typeFraud = 'V' 
        AND userFraud.nickName LIKE '%%' 
        AND bankInformation.accountHolderDeposit LIKE '%%' 
        AND bankInformation.accountNumber LIKE '%%' 
        AND bankInformation.agency = 1945

Só que quando o hibernate me retorna os valores ele traz os nicks iguais “userFraud.nickName, userRigged.nickName”.

Alguem sabe como faz pra ele diferenciar os dois?

OBS: No BD funciona o sql

Abraços

8 Respostas

walacy

Já tentou usar um alias?

userFraud.nickName as A,
userRigged.nickName as B,

walacy

Seu tópico está duplicado…

Se puder remova 1…
http://www.guj.com.br/posts/list/207446.java

brucsantos

Com o alias da o erro:

Hibernate: 
    SELECT
        fraud.identity ,
        fraud.logcase,
        fraud.fraudIn,
        userFraud.nickName as userFraudNickName,
        userRigged.nickName as userRiggedNickName,
        keyword.name,
        bankInformation.accountHolderDeposit ,
        bankInformation.agency ,
        bankInformation.accountNumber ,
        bank.bankName 
    FROM
        fraud fraud 
    LEFT JOIN
        bankInformation bankInformation 
            ON fraud.bankInformationIdentity = bankInformation.identity  
    INNER JOIN
        bank bank 
            ON bankInformation.bankNameIdentity = bank.identity 
    LEFT JOIN
        keyword keyword 
            ON fraud.keyWordIdentity = keyword.identity  
    INNER JOIN
        users userFraud 
            ON fraud.fraudsterIdentity = userFraud.identity  
    INNER JOIN
        users userRigged 
            ON fraud.riggedIdentity = userRigged.identity  
    INNER JOIN
        product product  
            ON fraud.productIdentity = product.identity  
    LEFT JOIN
        city citySendProduct  
            ON product.productCityIdentity = citySendProduct.identity  
    LEFT JOIN
        state stateSendProduct 
            ON citySendProduct.cityStateIdentity = stateSendProduct.identity  
    LEFT JOIN
        payment payment  
            ON product.paymentIdentity = payment.identity  
    LEFT JOIN
        city cityPayment 
            ON payment.paymentCityIdentity = cityPayment.identity 
    LEFT JOIN
        state statePayment  
            ON cityPayment.cityStateIdentity = statePayment.identity  
    WHERE
        fraud.statusFraud = 'C' 
        AND fraud.typeFraud = 'V' 
        AND userFraud.nickName LIKE '%%' 
        AND bankInformation.accountHolderDeposit LIKE '%%' 
        AND bankInformation.accountNumber LIKE '%%' 
        AND bankInformation.agency = 1945


20/05/2010 14:31:02 org.hibernate.type.NullableType nullSafeGet
INFO: could not read column value from result set: nickName; Column 'nickName' not found.
20/05/2010 14:31:02 org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 0, SQLState: S0022
20/05/2010 14:31:03 org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: Column 'nickName' not found.
brucsantos

Pessoal alguma ideia???

Obrigado

pmlm
brucsantos:
userFraud.nickName as userFraudNickName,
        userRigged.nickName as userRiggedNickName,
        

INFO: could not read column value from result set: nickName; Column 'nickName' not found.
O teu result set não vai ter nenhuma coluna 'nickName' mas sim uma 'userFraudNickName' e uma 'userRiggedNickName'. Mas alguma coisa continua a tentar ler a coluna nickName...
brucsantos

Alguma ideia de como resolver isso?

Abraçoss

B

Bom dia brucsantos,

Poderia postar a classe responsável por chamar o select e pegar os resultados?

Att,

Guilherme Bernert

brucsantos

Posso sim, esse problema eu resolvi de outra forma… mais posto a forma que estava tentando.

package br.com.mercadolivre.fraudesshml.domain.repository;


public class FraudRepository extends AbstractRepository<Fraud> {
	@Override
	public Fraud save(Fraud fraud) {
		Consistencies.consistNotNull(fraud,"Fraud is required for save");

		PersistenceType type;
		if(fraud.getIdentity() == null) {
			type = PersistenceType.INCLUDE;
		} else {
			type = PersistenceType.UPDATE;
		}				
		
		return this.executeCommand(fraud, type);
	}	


	@Override
	public Collection<Fraud> save(Collection<Fraud> fraud) {
		for(Fraud f : fraud) {
			f = this.save(f);
		}

		return fraud;
	}

	@Override
	public Query<Fraud> findByIdentity(Long identity) {		
		return new ListCriteria<Fraud>(this.getSession()
				.createCriteria(Fraud.class).add(Restrictions.eq("identity", identity)));

	}

	@Override
	public boolean contains(Fraud fraud) {
		return new ListCriteria<Fraud>(this.getSession()
				.createCriteria(Fraud.class).add(
						Restrictions.eq("logCase", fraud.getLogCase()))								
		).count() >= 1;
	}

	@Override
	public void delete(Fraud fraud) {		
		this.executeCommand(fraud,PersistenceType.DELETE);		
	}

	@Override
	public void delete(Collection<Fraud> fraud) {
		for(Fraud f : fraud) {
			this.delete(f);
		}
	}

	@Override
	public Query<Fraud> findAll() {
		return new QueryList<Fraud>(this.getSession().createQuery("from Fraud"));
	}


	@Override
	public Query<Fraud> findDuplicates(Fraud Fraud) {
		throw new NotImplementedException();
	}


	@Override
	public Query<Fraud> findEquals(Fraud instance) {
		throw new NotImplementedException();
	}

	public Query<Integer> count() {
		return new ListCriteria<Integer>(
				this.getSession().createCriteria(Fraud.class)
						.setProjection(Projections.countDistinct("identity"))
		);	
	}

	public Query<Fraud> findAllNewCases() {
		
		Criteria criteria = this.getSession().createCriteria(Fraud.class);
		
		criteria.add(
				Restrictions.or(
					Restrictions.eq("typeFraud", Fraud.VENDCONT), 
					Restrictions.eq("typeFraud", Fraud.COMPCONT)
				)
		);			
		criteria.add(Restrictions.eq("statusFraud", Fraud.NEW));
		
		return new ListCriteria<Fraud>(criteria);		
		
	}


	public Query<Fraud> findInProcessByResponsible(Responsible responsible) {		
		return new ListCriteria<Fraud>(
				this.getSession().createCriteria(Fraud.class).add(
						Restrictions.and(
								Restrictions.eq("responsible", responsible),
								Restrictions.eq("statusFraud", Fraud.PROCESS)
						)
				)
		);	
	}
	
	public Query<Fraud> findAllUnknowmCases() {
		return new ListCriteria<Fraud>(
				this.getSession().createCriteria(Fraud.class).add(
						Restrictions.and(
								Restrictions.eq("typeFraud", Fraud.UNKNOWN),
								Restrictions.eq("statusFraud", Fraud.NEW)
						)			
				).setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
		);	
	}
	
	public Query<Fraud> findAllCompleteds() {
		return new ListCriteria<Fraud>(
				this.getSession().createCriteria(Fraud.class)
					.add(
							Restrictions.or(
									Restrictions.eq("statusFraud", Fraud.RESPONSE_CS),
									Restrictions.eq("statusFraud", Fraud.COMPLETED)
							)			
				        )
				    .add(
				    		Restrictions.not(Restrictions.eq("typeFraud", Fraud.UNKNOWN))
				    	).setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
		);	
	}
	//
	
	public Query<Fraud> findByLogCase(Integer logCase) {		
		return new ListCriteria<Fraud>(
				this.getSession().createCriteria(Fraud.class).add(						
						Restrictions.eq("logCase", logCase)				
				)
		);	
	}


	public Boolean findDuplicatesBySellerAndBuyer(Fraud fraud) {	
		
		String sql = Messages.getMessage("sqlQuery.findDuplicateFraudBySellerAndBuyer");
		sql = sql.replace("SELLER", fraud.getUsersFraudster().getNickName())
				 .replace("BUYER", fraud.getUsersRigged().getNickName());
		
		String count = new QueryList<BigInteger>(this.getSession().createSQLQuery(sql)).find().toString();
		
		return Integer.parseInt(count) > 0;
	}


	public QueryList<Fraud> findFraudToIncludeKeyword(SearchFraud search) {	
			
		StringBuilder sql = new StringBuilder()
				.append(Messages.getMessage("sqlQuery.findFraudToIncludeKeyword"))				
				.append(" ( ")
				.append("userFraud.nickName LIKE '%")
				.append(search.getNickName())
				.append("%' OR ")
				.append("userRigged.nickName LIKE '%")				
				.append(search.getNickName())
				.append("%' ) AND ")
				.append("bankInformation.accountHolderDeposit LIKE '%")
				.append(search.getAccountHolder())
				.append("%' AND ")
				.append("bankInformation.accountNumber LIKE '%")
				.append(search.getAccount())
				.append("%' AND ")
				.append("bank.bankName LIKE '%")
				.append(search.getBankName())
				.append("%'");
		
		if(search.getAgency() != null) {
			sql.append(" AND bankInformation.agency = ").append(search.getAgency());
		}					
		
		return new QueryList<Fraud>(this.getSession().createSQLQuery(sql.toString());
					
	}		
	
}

Eu mudei essa ultima linha e no sql eu coloquei Select Form {fraud.*}

return new QueryList<Fraud>(this.getSession().createSQLQuery(sql.toString())
					.addEntity("fraud", Fraud.class));

Assim eu consegui mais não é o eu queria.

Criado 20 de maio de 2010
Ultima resposta 28 de mai. de 2010
Respostas 8
Participantes 4