Avatar billede fredand Forsker
13. september 2012 - 11:31 Der er 7 kommentarer og
1 løsning

Suddenly I need to concat my sHQL instead of using setParameter??

Hello guys!

I ran into something strange yesterday that I have not solved in a pleasant way.

Some code that used to work stopped working.

As you see below I have comment-away the lines that used to work, and replacet it with one ugly concat row.

Then it worked, but of course I can not understand way and I do not like it.

public Payment getPaymentForReferens(final String referens) throws PaymentNotFoundException
{
 
  //final Query tPaymentQuery = em.createQuery("SELECT b FROM Payment b WHERE b.referens = :referens");
  //tPaymentQuery.setParameter("referens", referens);
 
  final Query tPaymentQuery = em.createQuery("SELECT b FROM Payment b WHERE b.referens = " + referens);
 
  Payment payment = null;
  final List<?> results = tPaymentQuery.getResultList();
  if(!results.isEmpty()) {
  payment = (Payment)results.get(0);
  }

  if(payment == null) {
  throw new PaymentNotFoundException(PaymentNotFoundException.PAYMENT_WITH_THIS_REFERENS_CAN_NOT_BE_FOUND + referens);
  }

  return payment;

}

All suggestions, comments and ideas what might have gone wrong is most welcome! 

The field/column in the database for 'referens' is char(36) in a Oracle.

Best regards
Fredrik
Avatar billede arne_v Ekspert
13. september 2012 - 15:09 #1
If:

final Query tPaymentQuery = em.createQuery("SELECT b FROM Payment b WHERE b.referens = " + referens);
 
works for a CHAR field, then referens variable must contain single quotes - otherwise you would get a syntax error.

If referens contains single quotes then:

final Query tPaymentQuery = em.createQuery("SELECT b FROM Payment b WHERE b.referens = :referens");
tPaymentQuery.setParameter("referens", referens);

will treat the single quotes as being part of the value.

Possible cause??
Avatar billede fredand Forsker
13. september 2012 - 15:44 #2
Hello!

Thanks for your reply, Arne!

Acctually the value referens does not contain any singel qoutes.
(In this case it is a string from System.currentTimeMill...)

Doesn't that sounds very strange as well!

When I run the SQL in Toad as I guess it should be:
select from payment b where b.referens = 13457519644437

...Toad does not complain about any missing single qoutes but it returns no rows.

But like:
select from payment b where b.referens = '13457519644437'
Toad returns the row.

To me it sounds there is something fishy with the setParameter method.

How ever strange it is..

Best regards
Fredrik
Avatar billede arne_v Ekspert
13. september 2012 - 20:33 #3
Have you tried specifying 3 arguments to setParameter?
Avatar billede fredand Forsker
14. september 2012 - 08:30 #4
Hello Arne!

I was thinking about that as well.
How ever I was surprised that I did not found any method like:

setParameter(java.lang.String name, java.lang.Object value, TemporalType temporalType)

...just for Calendar och Date instead of Object.

Or do you think of any other method that I miss:
I looked at the api at:
http://docs.oracle.com/javaee/6/api/javax/persistence/Query.html

Best regards
Fredrik
Avatar billede arne_v Ekspert
14. september 2012 - 15:15 #5
is it JPA?

I thougth it was Hibernate

:-)
Avatar billede fredand Forsker
19. september 2012 - 12:24 #6
Hello Amigo!

Yes, my misstake, I wrote HQL in the topic but it should have bean JPQL.

How ever (and I think you were pointing me in this direction) there was something fishy about the char(36) field.

It turned out that there was a change in the lenght of varible 'referens'.
Before it was 36-chars, now it suddenly it was 21-chars.
That resulted that a the column referens got values with 15 empty chars at the end.
(I guess a varchar works in a diffrent way?)
Of course then my sql-select did not worked since I compared like 'aaa' = 'aaa  '.

I hope I made this clear and if it is ok with you I would like to give you the points Arne!

So please just leave a 'svar', and thank you very much for the help and input.

Best regards amigo!
/Fredrik
Avatar billede arne_v Ekspert
20. september 2012 - 01:38 #7
no thanks this time
Avatar billede fredand Forsker
20. september 2012 - 08:20 #8
Ok!

Thanks any way!

Best regards
Fredrik
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Kurser inden for grundlæggende programmering

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester