jpa mysql 函数,在JPA查询中使用MySQL的LPAD字符串函数?

May I know how to convert the following MySQL SQL to JPA query?

SELECT * FROM ORDER

WHERE LPAD(BATCH, 2, ' ') < LPAD('X', 2, ' ') ;

What I couldn't figure out is the LPAD() string function in JPA.

Do you have any idea?

解决方案

I know that is a very old post, but i am always be questioned by some colleagues when they are building JPA queries how to do the same thing.

Almost never you will find a perfect fit function to do what do you want. But, using some logic you can get the same result, in full JPA.

A example of LPAD.

Expression numberWithZeroLeft = builder.concat("0000", root.get("myNumber")); //Return something like 000012

Expression expressionLength = builder.length(numberWithZeroLeft);

Expression numberLpad = builder.substring(numberWithZeroLeft, e.builder.sum(expressionLength, -3), e.builder.literal(4)); //Return something like 0012

query.select(numberLpad);

Think in something like:

I have a string X, and need a string formatted in 4 zeros.

So we will get 0000 + X => "0000X"

And now just get the 4 last characters using substring.

I hope that help someone.

Cheers. =]