Home > Software design >  Select ordering works differently on windows and in container
Select ordering works differently on windows and in container

Time:12-21

I'm facing problem with ordering database records. I'm using jOOQ and DSLContext in SpringBoot application to select data from configured Oracle database. Everything works fine locally on my Windows device. After deploying application to Openshift container platform, the same select orders records differently. Database contains text values in Slovak language with accents and special characters as you can see in result tables.

Select:

var companies = DSLContext.select().from(Company.COMPANY)
                                   .orderBy(Company.NAME)
                                   .fetch()
                                   .stream()
                                   .map(this::mapCompany)
                                   .collect(Collectors.toList());

Result on Windows: (good result)

ID NAME
1 Aaaa
2 Áááá
3 Bbbb
4 "Bcbb"
5 "Ccccc"

Result on deployed app: (wrong result)

ID NAME
1 "Bcbb"
2 "Ccccc"
3 Aaaa
4 Áááá
5 Bbbb

I think that problem is caused by different locale or encoding on windows and container(unix). The application is deploying using dockerfile. Can anyone provide some solution, how to achieve the same behavior in container as in Windows?

CodePudding user response:

The jOOQ API supports collations, which is the SQL way of specifying the sort order for different character sets and locales. You could write:

var companies = DSLContext.select().from(Company.COMPANY)
                                   .orderBy(Company.NAME.collate(myCollation))
                                   .fetch(this::mapCompany)

But this is only really a good idea if you're not in control of your database collations. Otherwise, better specify them with your table definitions, or when you set up your database. Please refer to your database manual for details on how to deal with collations.

  • Related