does springboot jpa work with netezza database?

Question

I am trying to connect my springboot app to my netezza database but no luck. I am trying to use the tutorial at: https://www.callicoder.com/spring-boot-rest-api-tutorial-with-mysql-jpa-hibernate/

I replaced the spring.datasource.url with my netezza database url but no luck. How can I connect to netezza please?

Answer 1

Next to properly configuring spring.datasource.url, you also have to add the Netezza JDBC driver to your classpath. According to this article on the IBM Knowledge Center, you should be able to download the client tools for the right environment:

nz-*client-version.tar.z Netezza client installation packages for the supported client operating systems. The IBM Netezza client packages are available for a common group of operating system environments such as:

• nz-linuxclient-version.tar.gz for Linux on Intel/AMD, includes ODBC/JDBC and Netezza command line utilities
• ...

Additionally to this, you also have to properly configure the dialect. As far as I'm aware, Hibernate (the default JPA provider within Spring boot) has no support for the Netazza dialect. You can find a full list of supported dialects on their Javadoc.

However, since Netazza is based on PostgreSQL 7.x, you might be able to get it to work using one of the PostgreSQL dialects, though there are two remarks:

1. Hibernate no longer comes with a dialect supporting PostgreSQL 7.x
2. Netazza didn't maintain backwards compatibility, so it may not work

So to answer your question, it's not officially supported by Hibernate, so it probably won't work. The alternatives are:

1. Writing your own Hibernate dialect for Netezza. This requires some work, but will allow you to use your dialect for re-use in other projects.
2. Using the more low-level JdbcTemplate. This means you'll have to work with SQL itself and you may have to do things by yourself.

Answer 2

For the lazy ones like myself,
1) You'll need to install the jar on your machine first, this SO answer explains that.
2) Add the dependencies

<dependency>
    <groupId>postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>7.4.1-jdbc3</version>
</dependency>
<dependency>
    <groupId>net.sf.squirrel-sql.plugins</groupId>
    <artifactId>netezza</artifactId>
    <version>3.5.0</version>
</dependency>
<dependency>
    <groupId>org.netezza</groupId>
    <artifactId>netezza</artifactId>
    <version>1.0</version>
</dependency>

3) Netezza custom dialect. Please note, these are the data types that were required for my use case. For any additional data types, you'll have to add them explicitly.

import org.hibernate.dialect.PostgreSQLDialect;
import org.hibernate.type.StandardBasicTypes;

@SuppressWarnings("deprecation")
public class NetezzaDialect extends PostgreSQLDialect {
    public NetezzaDialect() {
        registerHibernateType(Types.CHAR, StandardBasicTypes.STRING.getName());
        registerHibernateType(Types.VARCHAR, StandardBasicTypes.STRING.getName());
        registerHibernateType(Types.NVARCHAR,  StandardBasicTypes.STRING.getName());
        registerHibernateType(Types.BIGINT, StandardBasicTypes.LONG.getName());
        registerHibernateType(Types.SMALLINT, StandardBasicTypes.SHORT.getName());
        registerHibernateType(Types.DATE, StandardBasicTypes.DATE.getName());
    }
}

4) The properties for spring boot

# Netezza properties
spring.datasource.url=jdbc:netezza://{host}:{port}/{db/ catalog}
spring.datasource.username=
spring.datasource.password=
spring.jpa.properties.hibernate.dialect=your.org.config.NetezzaDialect
spring.datasource.driver-class-name=org.netezza.Driver