using crudrepository with fixed length column in Oracle 11g

Question

I am trying to write a spring boot that queries a Oracle 11g database.

was a very simple select * from item where item_code = 'abc' command.

But because the database uses CHAR(10) for that id column, I found that I can only use like in the where clause. Basically all the derived query of crudRepository failed. I have had to jump hoops to find that only like works.

below is what I used in the interface class

//itemCode = 'AB1234' in all cases below

public Item findByIdItemCode(String itemCode); //return 0 result

@Query("select r from Item r where r.id.itemCode = 'AB1234'") 
public Item findcustomfix(); //return 1 result

@Query("select r from Item r where r.id.itemCode = :itemCode")
public Item findcustom(@Param("itemCode") String itemCode); //return 0 result

@Query("select r from Item r where r.id.itemCode like %:itemCode%")
public Item findcustomLike(@Param("itemCode") String itemCode); //return 1 result

//application.properties
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.OracleDialect

//class itemKey
@Column(name="ITEM_CODE", columnDefinition="CHAR", length = 10)
//@Column(name="ITEM_CODE", columnDefinition="CHAR(10)") //also does not work
private String itemCode;

I have added the length definition but this still seems not enough for hibernate to generate a where ITEM_CODE = 'AB1234 ' clause that considers the fixed width column

I cannot afford to keep using the like approach as it cannot discern between AB123 and AB1234.

Is there a proper way to do this?

UPDATE:

log:

Hibernate: select i1_0.com, i1_0.item_code from stock i1_0 where i1_0.item_code=?
org.hibernate.orm.jdbc.bind: binding parameter [1] as [ **VARCHAR** ] - [AB1234]

traced the stack that finally this VARCHAR is returned from

org.hibernate.type.descriptor.jdbc.VarcharJdbcType.getJdbcTypeCode() which way up in the stack from JdbcParameterBindings ==> jdbcValueBinder

perhaps I have to force it to find as CHAR instead of VARCHAR? I am still looking for how to do this

Answer 1

I don't know tools you use.

In Oracle, char datatype does what you experienced: right-pads value with spaces up to max column length. That's OK if you really have such values, for example

• Boolean datatype "simulation" (as at SQL level Oracle doesn't support it) so you'd store Y/N, 0/1 or something like that - that's always CHAR(1)
• social security numbers (or similar), which have fixed length of "n" characters
• and so on

But, for strings whose length varies, char is rarely appropriate because ... well, you know why - issues while working with them.

Here are some options you might want to consider (strictly Oracle-related).

SQL> create table test (val char(10));

Table created.

SQL> insert into test (val) values ('ABC');

1 row created.

SQL> select val, length(val) len from test;

VAL               LEN
---------- ----------
ABC                10

Use actual value, right-padded with spaces:

SQL> select * from test where val = 'ABC       ';

VAL
----------
ABC

Trim it:

SQL> select * from test where trim(val) = 'ABC';

VAL
----------
ABC

Like it:

SQL> select * from test where val like 'ABC%';

VAL
----------
ABC

Rpad it:

SQL> select * from test where val = rpad('ABC', 10, ' ');

VAL
----------
ABC

However: if you can afford it, modify column's datatype:

SQL> alter table test modify val varchar2(10);

Table altered.

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 VAL                                                VARCHAR2(10)

Column's value is, though, still 10 characters in length (spaces are kept):

SQL> select val, length(val) len from test;

VAL               LEN
---------- ----------
ABC                10

Update it:

SQL> update test set val = trim(val);

1 row updated.

SQL> select val, length(val) len from test;

VAL               LEN
---------- ----------
ABC                 3

Now you don't have to use any other functions or operators to access value as is:

SQL> select * from test where val = 'ABC';

VAL
----------
ABC