Hikaricp implementation with Microsoft SQL Server

Question

I am trying to find the best approach for using hikaricp (JDBC connection pool) with a Microsoft SQL server. From what I saw, it is recommended to use the DataSource parameter (as is the case for most connection pools that I have seen). However, I was not able to correctly form the connection to the sql database based on the examples that I saw - I wonder if anyone has a working example to which I can connect database information.

Answer 1

Make sure you complete the following steps:

If you are using maven, make sure you have the following dependency in your pom file (if using JDK7 / 8):

<dependency> <groupId>com.zaxxer</groupId> <artifactId>HikariCP</artifactId> <version>2.0.1</version> <scope>compile</scope> </dependency> 

If you are using another build tool, change the resource URL accordingly (or just download the jar file from the maven repository if you have no other option).

I believe you need the sqljdbc4.jar file in your pom file (I could be wrong about this requirement, so I can update the message after confirmation)

Import the following into your class along with other links:

  import com.zaxxer.hikari.HikariConfig; import com.zaxxer.hikari.HikariDataSource; 

Add the following final properties (or just load them from the configuration file):

 private final String url = "jdbc:sqlserver://"; private final String serverName= "xxx.xxx.xxx.xxx"; private final int portNumber = 1433; private final String databaseName= "ACTUALDBNAME"; private final String userName = "ACTUALUSERNAME"; private final String password = "ACTUALPASSWORD"; private final String selectMethod = "cursor"; 

You can get the connection URL as follows:

 public String getConnectionUrl() { return url+this.serverName+":"+this.portNumber+";databaseName="+this.databaseName+";user="+this.userName+";password="+this.password+";selectMethod="+this.selectMethod+";"; } 

The following should provide you with the necessary data source (to get the connection):

  public DataSource getDataSource() { final HikariDataSource ds = new HikariDataSource(); ds.setMaximumPoolSize(10); ds.setDataSourceClassName("com.microsoft.sqlserver.jdbc.SQLServerDataSource"); // ds.addDataSourceProperty("serverName", this.serverName); //ds.addDataSourceProperty("databaseName", this.databaseName); ds.addDataSourceProperty("url", this.getConnectionUrl()); ds.addDataSourceProperty("user", this.userName); ds.addDataSourceProperty("password", this.password); ds.setInitializationFailFast(true); ds.setPoolName("wmHikariCp"); return ds; } 

or

 public DataSource getDataSource() { HikariConfig config = new HikariConfig(); config.setMaximumPoolSize(10); config.setDataSourceClassName("com.microsoft.sqlserver.jdbc.SQLServerDataSource"); config.addDataSourceProperty("serverName", this.serverName); config.addDataSourceProperty("port", this.portNumber); config.addDataSourceProperty("databaseName", this.databaseName); config.addDataSourceProperty("user", this.userName); config.addDataSourceProperty("password", this.password); return new HikariDataSource(config); //pass in HikariConfig to HikariDataSource } 

The preferred route is to pass HikariConfig to the HikariDataSource constructor. You can also load the configuration from the properties file.

Then get a connection to the data source:

 Connection con = null; con = ds.getConnection(); //where ds is the dataSource retrieved from step 5