Cant connect to Phoenix using JDBC in spring boot

I have a spring-boot application where I am trying to configure phoenix DataSource but getting "no suitable Driver" found error.

@Bean(name="phoenixDataSource")
@DependsOn(value = "placeholderConfigurer")
public DataSource phoenixDataSource() {
        SimpleDriverDataSource phoenixDataSource = new SimpleDriverDataSource();
        phoenixDataSource.setUrl( "jdbc:phoenix:localhost" );
        try {
            Class<?> driverClass = this.getClass().getClassLoader().loadClass("org.apache.phoenix.jdbc.PhoenixDriver");
            phoenixDataSource.setDriverClass((Class<? extends Driver>) driverClass);
        } catch( ClassNotFoundException e ) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

       );
        return phoenixDataSource;
    }

    @Bean(name = "phoenixJdbcTemplate")
    public JdbcTemplate phoenixJdbcTemplate(@Qualifier("phoenixDataSource") DataSource ds) {
        return new JdbcTemplate(ds);
    }

Solution

First step you need to find wether you have access to connect or not Connect to sqline using /usr/hdp/current/phoenix-client/bin/sqlline.py

 /usr/hdp/current/phoenix-client/bin/sqlline.py <Zoo-keeper-url>:2181:/hbase-unsecure

If your Habse is not set unsecure so you need to find wether its Kerberos or HBase protected by Ranger authorization ,you can find required information in your logs.

Now you have following three options to connect

Zookeper URL with non secure

  "jdbc:phoenix:<Zookeeper_host_name> :<port_number> : /hbase-unsecure"); //With No password

Zookeper URL with secure

  "jdbc:phoenix:<Zookeeper_host_name>:<port_number>:<secured_Zookeeper_node>:<user_name> "

With URL

   jdbc:phoenix:thin:url=<scheme>://<server-hostname>:<port>;authentication=vaquarkhan

Default zookeeper port =2181.

following code you can use for setup connection make sure you already added dependancy into POM file

POM :

      <dependency>
            <groupId>org.apache.hbase</groupId>
            <artifactId>hbase-client</artifactId>
            <version>1.3.1</version>
            <exclusions>
                <exclusion>
                    <groupId>log4j</groupId>
                    <artifactId>log4j</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.slf4j</groupId>
                    <artifactId>slf4j-log4j12</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        
        <dependency>
            <groupId>jdk.tools</groupId>
            <artifactId>jdk.tools</artifactId>
            <version>1.8.0_05</version>
            <scope>system</scope>
            <systemPath>${JAVA_HOME}/lib/tools.jar</systemPath>
        </dependency>
        <dependency>
            <groupId>org.apache.phoenix</groupId>
            <artifactId>phoenix-core</artifactId>
            <version>4.7.0-HBase-1.1</version>
            <exclusions>
                <exclusion>
                    <groupId>log4j</groupId>
                    <artifactId>log4j</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.slf4j</groupId>
                    <artifactId>slf4j-log4j12</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>sqlline</groupId>
            <artifactId>sqlline</artifactId>
            <version>1.1.9</version>
        </dependency>

JavaCode:

package com.khan.vaquar.config;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.datasource.SimpleDriverDataSource;

/**
 * Database Configures for Phoenix database.
 */

@Configuration
public class DBConfig {
    @Bean
    public DataSource dataSource() {
        return new SimpleDriverDataSource(new org.apache.phoenix.jdbc.PhoenixDriver(),
                "jdbc:phoenix:<Zookeeper-URL> :<PORT_NO> : /hbase-unsecure"); 
                

    }

@Bean
   public NamedParameterJdbcTemplate databasePhoenixJdbcTemplate() {
        JdbcTemplate template = new JdbcTemplate(this.dataSource());
        template.setQueryTimeout("1500");
    return new NamedParameterJdbcTemplate(template);
   }
    
}

Inside your repo use it for connection

 @Autowired
 private NamedParameterJdbcTemplate databasePhoenixJdbcTemplate;

Few useful links :