CODEWITHSUNDEEP
javahibernate
2Big2BeSmall
2Big2BeSmall

Reputation: 1378

Replace subString in string with Hibernate select query

I have a table i would like to query with hibernate (java) for exmaple this table has 2 column

full_name |  age
---------------------
Mr Dony   |   30
Mrs Clark |   32
Doc Who   |   43

i would like to replace each value of Doc to Doctor show criteria result will look:

full_name |  age
---------------------
Mr Dony   |   30
Mrs Clark |   32
Doctor Who|   43

if that's matter my db's are PostgreSQL and vertica

Upvotes: 0

Views: 2914

Answers (1)

t7tran
t7tran

Reputation: 2136

Hibernate's Dialect is exactly for this job. What you can do is:

  1. Implement a subclass of org.hibernate.dialect.PostgreSQL81Dialect or any of its descendants depending on actual PostgreSQL version.
  2. Register a custom function (see PostgreSQL81Dialect's constructor) like below.
  3. Declare your dialect with Hibernate
  4. Then use it as you would with native query update table_name set full_name = replace(full_name, 'Doc', 'Doctor'). Note that this would replace all 'Doc' occurrences with 'Doctor'.

public class PostgreSQL81DialectEx extends PostgreSQL81Dialect { public PostgreSQL81DialectEx() { registerFunction( "replace", new SQLFunctionTemplate(StandardBasicTypes.STRING, "replace(?1, ?2, ?3)") ); } }

Upvotes: 1

Related Questions