java 用 microsoft sql server 实现 hikaricp

implementing hikaricp with microsoft sql server

提问by ali haider

I am trying to figure out the best approach for using hikaricp (JDBC connection pool) with microsoft sql server. From what I saw, the DataSource option is recommended (as is the case for most connection pools I've seen). However, I was not able to form a connection correctly with the sql server database based on the examples I've seen - wondering if anyone has a working example to which I can plug my DB info into.

我试图找出在 microsoft sql server 中使用 hikaricp（JDBC 连接池）的最佳方法。从我所见，推荐使用 DataSource 选项（就像我见过的大多数连接池一样）。但是，根据我看到的示例，我无法与 sql server 数据库正确建立连接 - 想知道是否有人有一个可以插入我的数据库信息的工作示例。

回答by ali haider

Make sure you have taken the following steps:

确保您已采取以下步骤：

1. If using maven, make sure that 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 using another build tool, change the resource URL accordingly (or just download the jar file from the maven repository if there is no other option for you).

   I believe you need the sqljdbc4.jar file in your pom file as well (I could be wrong about this requirement so I may update the post once I reconfirm)

2. Import the following in your class along with other references:

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

3. Add the following final properties (or simply load them from config 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 retrieve the connection URL like this:

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

   }

1. 如果使用 maven，请确保您的 pom 文件中具有以下依赖项（如果使用 JDK7/8）：

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

   如果使用其他构建工具，请相应地更改资源 URL（如果没有其他选项，则只需从 Maven 存储库下载 jar 文件）。

   我相信你的 pom 文件中也需要 sqljdbc4.jar 文件（我可能对这个要求有误，所以我重新确认后可能会更新帖子）

2. 在您的课程中导入以下内容以及其他参考资料：

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

3. 添加以下最终属性（或简单地从配置文件加载它们）：

    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"; 
   

   您可以像这样检索连接 URL：

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

   }

Then, the following should give you the DataSource you need in order to get a 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 the HikariConfig to the HikariDataSource constructor. You can also load the config from a properties file.

首选路线是将 HikariConfig 传递给 HikariDataSource 构造函数。您还可以从属性文件加载配置。

Then get connection from the datasource:

然后从数据源获取连接：

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