ConnectionPools.java [src/csip/utils] Revision: default  Date:
/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package csip.utils;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import csip.Config;
import static csip.Config.CSIP_JDBC_CHECKVALID;
import csip.api.server.ServiceException;
import csip.SessionLogger;
import csip.Utils;
import csip.annotations.Resource;
import csip.annotations.ResourceType;
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.tomcat.jdbc.pool.DataSourceProxy;
import org.apache.tomcat.jdbc.pool.PoolProperties;

/**
 * Connection Pool Managements
 *
 * @author od
 */
public class ConnectionPools {

  public static final String DRIVER = "driverClassName";

  static abstract class ConnPool {

    DataSource datasource;
    String url;
    Map<String, String> env;


    static ConnPool create(String type, String url, Map<String, String> env) {
      switch (type.toLowerCase()) {
        case "hakiri":
          return new Hikari(url, env);
        case "tomcat":
        default:
          return new Tomcat(url, env);
      }
    }


    abstract void close();


    abstract DataSource createDatasource(String url, Map<String, String> env);


    ConnPool(String url, Map<String, String> env) {
      this.url = Utils.resolve(url);
      this.env = env;
      setDriver(url, env);
    }


    Connection getConnection(SessionLogger log) throws ServiceException {
      try {
        if (datasource == null)
          datasource = createDatasource(url, env);
        return datasource.getConnection();
      } catch (SQLException ex) {
        throw new ServiceException(ex);
      }
    }


    private void setDriver(String resolved_url, Map<String, String> env) {
      if (!env.containsKey(DRIVER)) {
        String rurl = resolved_url.toLowerCase();
        if (rurl.contains(":postgresql:")) {
          env.put(DRIVER, "org.postgresql.Driver");
        } else if (rurl.contains(":sqlserver:")) {
          env.put(DRIVER, "com.microsoft.sqlserver.jdbc.SQLServerDriver");
        } else if (rurl.contains(":mysql:")) {
          env.put(DRIVER, "com.mysql.jdbc.Driver");
        } else if (rurl.contains(":sqlite:")) {
          env.put(DRIVER, "org.sqlite.JDBC");
        } else if (rurl.contains(":oracle:")) {
          env.put(DRIVER, "oracle.jdbc.driver.OracleDriver");
        } else if (rurl.contains(":db2:")) {
          env.put(DRIVER, "com.ibm.db2.jcc.DB2Driver");
        } else if (rurl.contains(":sdm:")) {
          env.put(DRIVER, "csip.sdm.SDMDriver");
        }
      }
    }

    /**
     * Tomcat Connection Pool.
     */
    static class Tomcat extends ConnPool {

      Tomcat(String url, Map<String, String> env) {
        super(url, env);
      }


      @Override
      DataSource createDatasource(String res_url, Map<String, String> env) {

        // overwrite the defaults.
        Properties dbp = new Properties();
        Properties pr = new Properties(defaultProperties());
        pr.putAll(env);

        PoolProperties p = new PoolProperties();
        for (String key : pr.stringPropertyNames()) {
          String val = pr.getProperty(key);
          try {
            BeanUtils.setProperty(p, key, val);
//              info("Set JDBC pool property: " + key + ": " + val);
          } catch (IllegalAccessException | InvocationTargetException E) {
            dbp.setProperty(key, val);
//              logger.info("Set DB property: " + key + ": " + val);
          }
        }
        if (!dbp.isEmpty()) {
          p.setDbProperties(dbp);
        }

        p.setUrl(res_url);
        return new org.apache.tomcat.jdbc.pool.DataSource(p);
      }


//      void setConfig(Object cfg, Map<String, String> env) {
//        for (String key : env.keySet()) {
//          String val = env.get(key);
//          try {
//            BeanUtils.setProperty(cfg, key, val);
//            System.out.println("Set DB property: " + key + ": " + val);
//          } catch (IllegalAccessException | InvocationTargetException E) {
//            System.out.println("Cannot set config property: " + key + ": " + val);
//          }
//        }
//      }
      /**
       * set the default properties.
       *
       * @param p PoolProperties
       */
      private Properties defaultProperties() {
        Properties p = new Properties();
        p.put("defaultAutoCommit", false);
        p.put("jmxEnabled", false);
        p.put("testOnBorrow", true);
        p.put("validationQuery", "SELECT 1");
        p.put("testOnReturn", false);
        p.put("validationInterval", 30000);
        p.put("maxWait", 10000);
        p.put("removeAbandonedTimeout", 60);
        p.put("removeAbandoned", true);
        p.put("initialSize", 10);
        p.put("maxActive", 250);
        p.put("maxIdle", 100);
        p.put("minIdle", 10);
        p.put("suspectTimeout", 60);
        p.put("timeBetweenEvictionRunsMillis", 30000);
        p.put("minEvictableIdleTimeMillis", 60000);
        return p;
      }


      @Override
      void close() {
        if (datasource != null)
          ((DataSourceProxy) datasource).close(true);
      }
    }

    /**
     * HikariCP implementation.
     */
    static class Hikari extends ConnPool {

      Hikari(String url, Map<String, String> env) {
        super(url, env);
      }


      @Override
      void close() {
        if (datasource != null)
          ((HikariDataSource) datasource).close();
      }


      /**
       * set the default properties.
       *
       * @param p PoolProperties
       */
      private Properties defaultProperties() {
        Properties p = new Properties();
        p.put("autoCommit", false);
        p.put("connectionTestQuery", "SELECT 1");
        p.put("connectionTimeout", 3000);
        p.put("maximumPoolSize", 100);
        p.put("minimumIdle", 10);
        return p;

//        p.setTestOnBorrow(true);
//        p.setTestOnReturn(false);
//        p.setValidationInterval(30000);
//        p.setMaxWait(10000);
//        p.setRemoveAbandonedTimeout(60);
//        p.setRemoveAbandoned(true);
//        p.setInitialSize(10);
//        p.setMaxActive(250);
//        p.setMaxIdle(100);
//        p.setMinIdle(10);
//        p.setSuspectTimeout(60);
//        p.setTimeBetweenEvictionRunsMillis(30000);
//        p.setMinEvictableIdleTimeMillis(60000);
      }


      @Override
      DataSource createDatasource(String url, Map<String, String> env) {
        Properties p = new Properties(defaultProperties());
        p.putAll(env);
        HikariConfig cfg = new HikariConfig(p);
        cfg.setJdbcUrl(url);
        return new HikariDataSource(cfg);
      }
    }
  }

  //////////////////////////////////
  private final Map<String, ConnPool> jdbcPools = new HashMap<>();

  private static volatile ConnectionPools instance;


  public static ConnectionPools singleton() {
    ConnectionPools ref = instance;
    if (ref == null) {
      synchronized (ConnectionPools.class) {
        ref = instance;
        if (ref == null)
          instance = ref = new ConnectionPools();
      }
    }
    return ref;
  }


  public void shutdownJDBC() {
    for (ConnPool cp : jdbcPools.values()) {
      cp.close();
    }
    jdbcPools.clear();
  }


  /**
   * Add to JDBC pool
   *
   * @param id
   * @param url
   */
  public void addToJDBCPool(String id, String url) {
    jdbcPools.put(id, ConnPool.create("tomcat", url, new HashMap<>()));
  }


  /**
   * Add to JDBC pool
   *
   * @param id
   * @param url
   * @param env
   */
  public void addToJDBCPool(String id, String url, Map<String, String> env) {
    jdbcPools.put(id, ConnPool.create("tomcat", url, env));
  }


  /**
   * Get Connection.
   *
   * @param id
   * @param logger
   * @return
   * @throws ServiceException
   */
  public Connection getConnection(String id, SessionLogger logger) throws ServiceException {
    ConnPool p = jdbcPools.get(id);
    if (p == null)
      throw new ServiceException("No such resource: " + id);
    return p.getConnection(logger);
  }


  /**
   * Get the JDBC connection from a resource definition.
   *
   * @param c
   * @param id
   * @param LOG
   * @return
   * @throws ServiceException
   */
  synchronized public Connection getResourceJDBC(Class<?> c, String id, SessionLogger LOG)
      throws ServiceException {
    ConnPool p = jdbcPools.get(id);
    if (p == null) {
      Resource r = Binaries.getResourceById(c, id);
      if (r != null) {
        if (r.type() == ResourceType.JDBC) {
          String url = r.file();
          if (url != null && !url.isEmpty()) {
            Map<String, String> env = Binaries.parseEnv(r.env());
            // the 'args' value might specify the cp type 
            // 'tomcat' or 'hakiri', if left blank, default is tomcat.
            String cpType = r.args().isEmpty() ? "tomcat" : r.args();
            jdbcPools.put(id, p = ConnPool.create(cpType, url, env));
          } else {
            throw new ServiceException("No url connection string in 'file': " + id);
          }
        } else {
          throw new ServiceException("Not a JDBC resource: " + id);
        }
      } else {
        throw new ServiceException("No such resource: " + id);
      }
    }
    Connection con = p.getConnection(LOG);
    checkValid(con);
    return con;
  }


  static void checkValid(Connection con) throws ServiceException {
    int valid = Config.getInt(CSIP_JDBC_CHECKVALID, -1); // default: no checking
    if (valid <= -1)
      return;
    try {
      if (!con.isValid(valid))
        throw new ServiceException("Invalid connection: " + con.getMetaData().getURL());
    } catch (SQLException ex) {
      throw new ServiceException(ex);
    }
  }

}