Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:
3.2.6ga

Mapping documents:
The relevant part is the primary key of article:

@Id
@Column(name = "ArtikelNr",
nullable = false,
length = 50,
columnDefinition =
"nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS")
private String articleNo;

and the corresponding mapping of an article withing an orderline

@OneToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "ArtikelNr",
columnDefinition =
"nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS")
private Article article;


Code between sessionFactory.openSession() and session.close():
Glassfish JPA

Full stack trace of any exception that occurs:
None

Name and version of the database you are using:
MS-SQL 8.0

The generated SQL (show_sql=true):
The SQL is working

Debug level Hibernate log excerpt:
None

Problem description
I am writing persistent beans for an existing legacy database (MS-SQL + Hibernate). In the articles table, the primary key is a case insensitive VARCHAR - stupid, but legacy. Every association is LAZY. In the order lines table, the articleno sometimes appears with a different case. If I do this SELECT:

SELECT DISTINCT o FROM Order o
LEFT JOIN FETCH o.orderLines AS orderLine
LEFT JOIN FETCH orderLine.article AS article
LEFT JOIN FETCH article.designations
LEFT JOIN FETCH o.customer
WHERE o.orderNo=:orderNo

then the articles of the order lines with a different case are not loaded which gives a Lazy..Exception upon access. The generated query returns all orderlines and all articles within the join, so for me it looks like Hibernate simply ignores results that do not match case.

My current hack now is this:

for (OrderLine orderLine : result.getOrderLines()) {
orderLine.getArticle().getDesignation(Language.GERMAN).getClass();
}

Does there is a way to tell Hibernate to ignore the case on primary keys?

Hi,

i am not sure whether I really understand your problem, especially the problem description. Is this SQL some manually entered SQL you are running? Maybe you can provide some debug log or the stacktrace of the lazy loading exception?

Couldn't you convert the db to use all lowercase or uppercase?

--Hardy

The SQL command for the select was made by me. If I execute the SQL that Hibernate generates, it returns all records necessary. I guess that Hibernate detects that the contents of the two articleno-columns in the resultset are not "equal()" and therefore refuses to accept this line and uses a lazyload-proxy instead.

Hand-made example of a resultset:



My workaround is to access each orderline object. If there are orderlines with different case in the primary keys, hibernate generates an additional SELECT to fetch this line separately upon access. The lines with matching case articlenumbers do not cause an additional SELECT.

I cannot convert the DB as it is a legacy Access application with MS-SQL server which is still in use. Article numbers are sometimes entered by hand which causes the case mismatch.

Hi,

I understand the problem better now :) Thanks a lot. However, how do you load the orderlines you are iterating over? Do you use HQL to get a list of order lines? And why do you expect the order lines to be loaded eagerly even though you map them as lazy loaded? If you are not using HQL and the fetch keyword I am actually suprised that no additonal query is issued for orderlines where the article number actually do match.
I think it would really help to see the sql log together with the code executed in the Hibernate Session.

--Hardy

Yes (see the named query above)



Because of JSR-220 Enterprise JavaBeans 3.0 section 4.4.5.3 Fetch Joins:





I am using HQL with the FETCH keyword. The additional SELECT is done if the article numbers do not match case.



Here you are: these are the SQL commands issued by Hibernate. The second and third do only appear if the case is different. Otherwise only the first statement is executed:

Hibernate: select distinct order0_.Bestellnr as Bestellnr67_0_, orderlines1_.id as id95_1_, article2_.ArtikelNr as ArtikelNr86_2_, designatio3_.id as id75_3_, customer4_.KundenNr as KundenNr90_4_, order0_.Bank_KTO as Bank2_67_0_, order0_.Bank_Inhaber as Bank3_67_0_, order0_.Bank_BLZ as Bank4_67_0_, order0_.Bank_Name as Bank5_67_0_, order0_.Bemerkung as Bemerkung67_0_, order0_.Datum_Anlage as Datum7_67_0_, order0_.CC_Nr as CC8_67_0_, order0_.CC_Name as CC9_67_0_, order0_.CC_Typ as CC10_67_0_, order0_.CC_Nr_Short as CC11_67_0_, order0_.CC_Valid as CC12_67_0_, order0_.KundenNr as KundenNr67_0_, order0_.Bemerkung_Kunde as Bemerkung13_67_0_, order0_.Deadline as Deadline67_0_, order0_.Versand_Ort as Versand15_67_0_, order0_.Versand_Land as Versand16_67_0_, order0_.Versand_Vorname as Versand17_67_0_, order0_.Versand_Nachname as Versand18_67_0_, order0_.Versand_Strasse as Versand19_67_0_, order0_.Versand_PLZ as Versand20_67_0_, order0_.Lieferart as Lieferart67_0_, order0_.Versandart as Versandart67_0_, order0_.FF_Kundennummer as FF23_67_0_, order0_.FF_Lieferart as FF24_67_0_, order0_.FF_Versandart as FF25_67_0_, order0_.FF_Rechnungsnummer as FF26_67_0_, order0_.FF_Auftragsnummer as FF27_67_0_, order0_.FF_Zahlungsart as FF28_67_0_, order0_.Rechnung_Ort as Rechnung29_67_0_, order0_.Rechnung_Land as Rechnung30_67_0_, order0_.Rechnung_Vorname as Rechnung31_67_0_, order0_.Rechnung_Nachname as Rechnung32_67_0_, order0_.Rechnung_Strasse as Rechnung33_67_0_, order0_.Rechnung_PLZ as Rechnung34_67_0_, order0_.IP as IP67_0_, order0_.Datum_Aenderung as Datum36_67_0_, order0_.Zahlungsart as Zahlung37_67_0_, order0_.Ref_Kundennr as Ref38_67_0_, order0_.Ref_PartnerID as Ref39_67_0_, orderlines1_.Menge as Menge95_1_, orderlines1_.ArtikelNr as ArtikelNr95_1_, orderlines1_.ArtikelBezeichnung as ArtikelB3_95_1_, orderlines1_.Datum_Anlage as Datum4_95_1_, orderlines1_.FF_Artikelbeschreibung as FF5_95_1_, orderlines1_.FF_Artikelnummer as FF6_95_1_, orderlines1_.FF_Preis as FF7_95_1_, orderlines1_.Datum_Aenderung as Datum8_95_1_, orderlines1_.Bemerk as Bemerk95_1_, orderlines1_.Bestellnr as Bestellnr95_1_, orderlines1_.Bestellnr_Sub as Bestellnr10_95_1_, orderlines1_.Preis as Preis95_1_, orderlines1_.Status as Status95_1_, orderlines1_.Mwst_Proz as Mwst13_95_1_, orderlines1_.Bestellnr as Bestellnr0__, orderlines1_.id as id0__, article2_.AGruppeNr as AGruppeNr86_2_, article2_.barcode as barcode86_2_, article2_.UmkartonMenge as Umkarton3_86_2_, article2_.UmkartonBarcode as Umkarton4_86_2_, article2_.IstBest as IstBest86_2_, article2_.Vollkarton as Vollkarton86_2_, article2_.export_id as export7_86_2_, article2_.Vermessung_H as Vermessung8_86_2_, article2_.Vermessung_L as Vermessung9_86_2_, article2_.Vermessungsstatus as Vermess10_86_2_, article2_.Vermessung_V as Vermessung11_86_2_, article2_.Vermessung_G as Vermessung12_86_2_, article2_.Vermessung_B as Vermessung13_86_2_, article2_.Bild as Bild86_2_, article2_.ArtikelNr_ersetzt as ArtikelNr22_86_2_, article2_.Preis_VK as Preis15_86_2_, article2_.Lager as Lager86_2_, article2_.Lagerort as Lagerort86_2_, article2_.Bandstatus as Bandstatus86_2_, article2_.Typ as Typ86_2_, article2_.Gewicht as Gewicht86_2_, designatio3_.ArtikelNr as ArtikelNr75_3_, designatio3_.Bemerkung as Bemerkung75_3_, designatio3_.Bezeichnung as Bezeichn3_75_3_, designatio3_.Sprache as Sprache75_3_, designatio3_.ArtikelNr as ArtikelNr1__, designatio3_.id as id1__, customer4_.Ort as Ort90_4_, customer4_.Land as Land90_4_, customer4_.EMail as EMail90_4_, customer4_.Vorname as Vorname90_4_, customer4_.id as id90_4_, customer4_.Nachname as Nachname90_4_, customer4_.Haendler as Haendler90_4_, customer4_.Handy as Handy90_4_, customer4_.History_GesUmsatz_net as History10_90_4_, customer4_.News_gesperrt as News11_90_4_, customer4_.PW as PW90_4_, customer4_.Strasse as Strasse90_4_, customer4_.PLZ as PLZ90_4_ from T_Bestellung order0_ left outer join T_Bestellung_Details orderlines1_ on order0_.Bestellnr=orderlines1_.Bestellnr left outer join T_Artikel article2_ on orderlines1_.ArtikelNr=article2_.ArtikelNr left outer join T_Artikel_Bemerkung designatio3_ on article2_.ArtikelNr=designatio3_.ArtikelNr left outer join T_Kunde customer4_ on order0_.KundenNr=customer4_.KundenNr where order0_.Bestellnr=?

Hibernate: select article0_.ArtikelNr as ArtikelNr86_0_, article0_.AGruppeNr as AGruppeNr86_0_, article0_.barcode as barcode86_0_, article0_.UmkartonMenge as Umkarton3_86_0_, article0_.UmkartonBarcode as Umkarton4_86_0_, article0_.IstBest as IstBest86_0_, article0_.Vollkarton as Vollkarton86_0_, article0_.export_id as export7_86_0_, article0_.Vermessung_H as Vermessung8_86_0_, article0_.Vermessung_L as Vermessung9_86_0_, article0_.Vermessungsstatus as Vermess10_86_0_, article0_.Vermessung_V as Vermessung11_86_0_, article0_.Vermessung_G as Vermessung12_86_0_, article0_.Vermessung_B as Vermessung13_86_0_, article0_.Bild as Bild86_0_, article0_.ArtikelNr_ersetzt as ArtikelNr22_86_0_, article0_.Preis_VK as Preis15_86_0_, article0_.Lager as Lager86_0_, article0_.Lagerort as Lagerort86_0_, article0_.Bandstatus as Bandstatus86_0_, article0_.Typ as Typ86_0_, article0_.Gewicht as Gewicht86_0_ from T_Artikel article0_ where article0_.ArtikelNr=?

Hibernate: select designatio0_.ArtikelNr as ArtikelNr1_, designatio0_.id as id1_, designatio0_.id as id75_0_, designatio0_.ArtikelNr as ArtikelNr75_0_, designatio0_.Bemerkung as Bemerkung75_0_, designatio0_.Bezeichnung as Bezeichn3_75_0_, designatio0_.Sprache as Sprache75_0_ from T_Artikel_Bemerkung designatio0_ where designatio0_.ArtikelNr=?