Enable PostgreSQL Extensions in JDBC

Question

I am using Spring + Tomcat8 along with PostgreSQL 9.2. I installed the Trigram extension in the database to improve the search in tables.

When I connect to my database manually, the following expression is executed:

SELECT id, name, similarity(name, 'a') FROM dev.customer WHERE name % 'a' ORDER BY similarity ;

However, when I try to do this in Spring using jdbcTemplate.query(), I get an error message:

PSQLException: ERROR: function similarity(character varying, character varying) does not exist

When I delete a function similarity()and use only the operator %, I get the following exception:

ERROR: operator does not exist: character varying % character varying

It seems that the postgres jdbc driver needs to be configured to support custom syntax: https://jdbc.postgresql.org/documentation/81/ext.html

server.xml of my tomcat installation contains the following resource:

<Resource name="jdbc/NasPostgresDB" auth="Container" type="javax.sql.DataSource"
          username="usr" password="pwd"
          url="jdbc:postgresql://127.0.0.1/dbname"
          driverClassName="org.postgresql.Driver"
          initialSize="5" maxWait="5000"
          maxActive="120" maxIdle="5"
          validationQuery="select 1"
          poolPreparedStatements="true"/>

: https://jdbc.postgresql.org/documentation/81/load.html

, ?

Answer 1

PostgreSQL ( , ) . , :

SET SCHEMA 'dev'; CREATE EXTENSION pg_trgm;

, dev shema.

, JDBC :

SELECT id, name, dev.similarity(name, 'a') as similarity FROM dev.customer 
    WHERE name % 'a' ORDER BY similarity ;