Springboot with Sybase server - Cannot load driver class: com.sybase.jdbc4.jdbc.SybDriver

Question

Facing difficulty in configuring Sybase server with a fresh Spring boot application. Not sure how to configure Sybase server with spring boot successfully. Getting error "Cannot load driver class: com.sybase.jdbc4.jdbc.SybDriver".

Basically, I am looking to fetch some data from Sybase database and post as RESTful service in JSON format. Any help would be appreciated.

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.rbccm.poc</groupId>
    <artifactId>springbootpoc1</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.0.RELEASE</version>
    </parent>

    <name>springbootpoc1</name>
    <url>http://maven.apache.org</url>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>com.sybase.jconnect</groupId>
            <artifactId>jconn4</artifactId>
            <version>7.07</version>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

        <dependency>
            <groupId>com.github.derjust</groupId>
            <artifactId>spring-data-dynamodb</artifactId>
            <version>5.0.2</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

application.properties

server.port=8090

spring.jpa.hibernate.ddl-auto=none
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
spring.jpa.databasePlatform=org.hibernate.dialect.SybaseDialect
spring.jpa.show-sql=true

# hikariCP
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.url=jdbc:sybase:Tds:server:port/dbname
spring.datasource.username=u_****
spring.datasource.password=****
spring.datasource.connectionTestQuery=SELECT 1
spring.datasource.poolName=SpringBootHikariCP
spring.datasource.maximumPoolSize=10
spring.datasource.connectionTimeout=60000
spring.datasource.driverClassName=com.sybase.jdbc4.jdbc.SybDriver

# Number of ms to wait before throwing an exception if no connection is available.
spring.datasource.tomcat.max-wait=10000
# Maximum number of active connections that can be allocated from this pool at the same time.
spring.datasource.tomcat.max-active=50
# Validate the connection before borrowing it from the pool.
spring.datasource.tomcat.test-on-borrow=true

#JDBC customize
spring.jdbc.template.max-rows=500

Maven build logs

2018-04-04 14:48:25.206 ERROR 21768 --- [           main] o.s.boot.SpringApplication               : Application run failed

org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaConfiguration': Unsatisfied dependency expressed through constructor parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource' defined in class path resource [org/springframework/boot/autoconfigure/jdbc/DataSourceConfiguration$Hikari.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [com.zaxxer.hikari.HikariDataSource]: Factory method 'dataSource' threw exception; nested exception is java.lang.IllegalStateException: Cannot load driver class: com.sybase.jdbc4.jdbc.SybDriver
    at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:729) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
    at org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:192) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]

Uncurl

why is the scope of the sybase driver set to provided? – Dotty

Answer 1

Resolved: In pom dependency, instead of jpa, I used jdbc. And had to add 'jconn4d.jar' in the classpath externally.

pom.xml

<dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency> -->

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency> 

        <!-- <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>6.0.5</version>
        </dependency> -->

        <!-- <dependency>
            <groupId>com.sybase.jconnect</groupId>
            <artifactId>jconn4</artifactId>
            <version>7.07</version>
            <scope>provided</scope>
        </dependency> -->

        <dependency>
    <groupId>jtds</groupId>
    <artifactId>jtds</artifactId>
    <version>1.2</version>
</dependency>

application.properties

spring.datasource.url=jdbc:sybase:Tds:<server>:<port>/<dbname>
spring.datasource.username=u_****
spring.datasource.password=*****
spring.datasource.driverClassName=com.sybase.jdbc4.jdbc.SybDriver

Uncurl

With the solution above, you don't need the dependency on jtds, since you are not using the JDBC driver that ships with it. You are using JConn's com.sybase.jdbc4.jdbc.SybDriver while the driver name that comes with jtds is net.sourceforge.jtds.jdbc.Driver – Filagree

Answer 2

You can use spring boot jpa with sybase following this steps:

1.-Download jconn4.jar from the sap marketplace

2.-Install the jar in your local repository:

mvn install:install-file -Dfile="path-to\jconn4.jar" -DgroupId=sybase -DartifactId=jconn -Dversion=4_RELEASE -Dpackaging=jar

3.-Add the dependency to your pom:

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

        <dependency>
            <groupId>sybase</groupId>
            <artifactId>jconn</artifactId>
            <version>4_RELEASE</version>
        </dependency>

4.-Add the database configuration to your application.properties:

spring.datasource.url=jdbc:sybase:Tds:HOST:PORT/DB_NAME
spring.datasource.username=USER
spring.datasource.password=PASS
spring.jpa.properties.hibernate.default_schema=SCHEMA

spring.datasource.driverClassName=com.sybase.jdbc4.jdbc.SybDriver
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.jpa.hibernate.ddl-auto=none
spring.jpa.databasePlatform=org.hibernate.dialect.SybaseDialect
spring.datasource.connectionTestQuery=select 1
spring.jpa.show-sql=true

Leastwise

Thanks, this helped! Also for people who have a different/newer version of Sybase, don't forget to change it in the pom.xml and application.properties. For example, for jconn2.jars, in application.properties, it should be spring.datasource.driverClassName=com.sybase.jdbc2.jdbc.SybDriver docs.oracle.com/cd/E19509-01/820-3497/agqkg/index.html – Tetartohedral

Answer 3

also because com.sybase.jdbc4.jdbc.SybDriver is not the name of the JDBC driver implementation provided by jtds. The correct name that comes with it is

net.sourceforge.jtds.jdbc.Driver

I believe jconn4d.jar is not on maven central, and the driver name that ships with this implementation is com.sybase.jdbc4.jdbc.SybDriver

for more details are on:

https://www.dbvis.com/features/sybase-ase-database-drivers/

http://jtds.sourceforge.net/faq.html#driverImplementation

Filagree

Answer 4

This is what I did. Spring JPA worked for me

1.Added the Jconn3.jar(you can even add latest version of it) in resources folder.

2.Modified connection-test query in application.properties

application.properties

spring.datasource.url=jdbc:sybase:Tds:<server>:<port>/<dbname>
spring.datasource.username=u_****
spring.datasource.password=*****
spring.datasource.driverClassName=com.sybase.jdbc4.jdbc.SybDriver

spring.datasource.hikari.connection-test-query=SELECT 1

Puberulent

Answer 5

This issue bugged me a little bit, but its solution is quite easy. First in your pom.XML add the dependency:

<dependency>
    <groupId>jtds</groupId>
    <artifactId>jtds</artifactId>
    <version>1.2</version>
</dependency>

then your URL definition should follow this format (in Application.properties or Application.yml)

jdbc:jtds:sybase://<Host>:<Port>/<database>

this solution should work well.

Intermarry