Java Code Examples for org.apache.tomcat.jdbc.pool.DataSource#getConnection()

/**
 * This method gets the dimension filters for the given dataset from the presto data source,
 * and returns them as map of dimension name to values
 * @param dataset
 * @return dimension filters map
 */
public Map<String, List<String>> getDimensionFilters(String dataset) throws Exception {
  LOG.info("Getting dimension filters for " + dataset);
  DatasetConfigDTO datasetConfig = ThirdEyeUtils.getDatasetConfigFromName(dataset);

  String sourceName = dataset.split("\\.")[0];
  String tableName = SqlUtils.computeSqlTableName(dataset);
  DataSource dataSource = getDataSourceFromDataset(dataset);

  Map<String, List<String>> dimensionFilters = new HashMap<>();

  for (String dimension: datasetConfig.getDimensions()) {
    dimensionFilters.put(dimension, new ArrayList<>());
    try (Connection conn = dataSource.getConnection();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(SqlUtils.getDimensionFiltersSQL(dimension, tableName, sourceName));) {
      while (rs.next()) {
        dimensionFilters.get(dimension).add(rs.getString(1));
      }
    }
    catch (Exception e) {
        throw e;
    }
  }
  return dimensionFilters;
}
 

/**
 * Insert a table to SQL database, currently only used by H2, that can be read by ThirdEye
 *
 * @param ds DataSource object
 * @param tableName table name
 * @param timeColumn time column name
 * @param metrics list of metrics
 * @param dimensions list of dimensions
 * @throws SQLException SQL exception if SQL failed
 */
public static void createTableOverride(DataSource ds, String tableName,
    String timeColumn, List<String> metrics, List<String> dimensions) throws SQLException {
  StringBuilder sb = new StringBuilder();
  sb.append("drop table if exists ").append(tableName).append(";");
  sb.append("create table ").append(tableName).append(" (");

  for (String metric: metrics) {
    sb.append(metric).append(" decimal(50,3), ");
  }
  for (String dimension: dimensions) {
    sb.append(dimension).append(" varchar(50), ");
  }
  sb.append(timeColumn).append(" varchar(50) ) ENGINE=InnoDB;");

  String sql = sb.toString();

  LOG.info("Creating H2 table: " + sql);

  try (Connection connection = ds.getConnection();
      Statement statement = connection.createStatement()){
    statement.execute(sql);
  }
}
 

/**
 * Run SQL query to insert a row in a CSV file to a datasource, for now only used by H2 initialization
 *
 * @param tableName table name
 * @param columnNames column names in CSV, separated by ,
 * @param items row items
 * @throws SQLException
 */
public static void insertCSVRow(DataSource ds, String tableName, String columnNames, String[] items) throws SQLException {
  // Put quotes around values that contains spaces
  StringBuilder sb = new StringBuilder();
  String prefix = "";
  for (String item: items) {
    sb.append(prefix);
    prefix = ",";
    if (!StringUtils.isNumeric(item)) {
      sb.append('\'').append(item).append('\'');
    } else {
      sb.append(item);
    }
  }

  String sql = String.format("INSERT INTO %s(%s) VALUES(%s)", tableName, columnNames, sb.toString());
  try (Connection connection = ds.getConnection();
      Statement statement = connection.createStatement()){
    statement.execute(sql);
  }
}
 

@Test
public void testPool() throws SQLException, InterruptedException {
    PoolProperties poolProperties = new DefaultProperties();
    poolProperties.setMinIdle(0);
    poolProperties.setInitialSize(0);
    poolProperties.setMaxActive(1);
    poolProperties.setMaxWait(5000);
    poolProperties.setMaxAge(100);
    poolProperties.setRemoveAbandoned(false);

    final DataSource ds = new DataSource(poolProperties);
    Connection con;
    Connection actual1;
    Connection actual2;

    con = ds.getConnection();
    actual1 = ((PooledConnection)con).getConnection();
    con.close();
    con = ds.getConnection();
    actual2 = ((PooledConnection)con).getConnection();
    Assert.assertSame(actual1, actual2);
    con.close();
    Thread.sleep(150);
    con = ds.getConnection();
    actual2 = ((PooledConnection)con).getConnection();
    Assert.assertNotSame(actual1, actual2);
    con.close();
}
 

public static void main(String[] args) throws Exception {
    PoolConfiguration p = new PoolProperties();
    p.setUrl("jdbc:mysql://localhost:3306/mysql?autoReconnect=true");
    p.setDriverClassName("com.mysql.jdbc.Driver");
    p.setUsername("root");
    p.setPassword("password");
    p.setJmxEnabled(true);
    p.setTestWhileIdle(false);
    p.setTestOnBorrow(true);
    p.setValidationQuery("SELECT 1");
    p.setTestOnReturn(false);
    p.setValidationInterval(30000);
    p.setTimeBetweenEvictionRunsMillis(30000);
    p.setMaxActive(100);
    p.setInitialSize(10);
    p.setMaxWait(10000);
    p.setRemoveAbandonedTimeout(60);
    p.setMinEvictableIdleTimeMillis(30000);
    p.setMinIdle(10);
    p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
    p.setLogAbandoned(true);
    p.setRemoveAbandoned(true);
    DataSource datasource = new DataSource();
    datasource.setPoolProperties(p);

    Connection con = null;
    try {
      con = datasource.getConnection();
      Statement st = con.createStatement();
      ResultSet rs = st.executeQuery("select * from user");
      int cnt = 1;
      while (rs.next()) {
          System.out.println((cnt++)+". Host:" +rs.getString("Host")+" User:"+rs.getString("User")+" Password:"+rs.getString("Password"));
      }
      rs.close();
      st.close();
    } finally {
      if (con!=null) try {con.close();}catch (Exception ignore) {}
    }
}
 

public static void main(String[] args) throws Exception {
    PoolConfiguration p = new PoolProperties();
    p.setUrl("jdbc:mysql://localhost:3306/mysql?autoReconnect=true");
    p.setDriverClassName("com.mysql.jdbc.Driver");
    p.setUsername("root");
    p.setPassword("password");
    p.setJmxEnabled(true);
    p.setTestWhileIdle(false);
    p.setTestOnBorrow(true);
    p.setValidationQuery("SELECT 1");
    p.setTestOnReturn(false);
    p.setValidationInterval(30000);
    p.setTimeBetweenEvictionRunsMillis(30000);
    p.setMaxActive(100);
    p.setInitialSize(10);
    p.setMaxWait(10000);
    p.setRemoveAbandonedTimeout(60);
    p.setMinEvictableIdleTimeMillis(30000);
    p.setMinIdle(10);
    p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
    p.setLogAbandoned(true);
    p.setRemoveAbandoned(true);
    DataSource datasource = new DataSource();
    datasource.setPoolProperties(p);

    Connection con = null;
    try {
      con = datasource.getConnection();
      Statement st = con.createStatement();
      ResultSet rs = st.executeQuery("select * from user");
      int cnt = 1;
      while (rs.next()) {
          System.out.println((cnt++)+". Host:" +rs.getString("Host")+" User:"+rs.getString("User")+" Password:"+rs.getString("Password"));
      }
      rs.close();
      st.close();
    } finally {
      if (con!=null) try {con.close();}catch (Exception ignore) {}
    }
}
 

/**
 * Returns the max time in millis for dataset in presto
 * @param dataset
 * @return max date time in millis
 */
public long getMaxDataTime(String dataset) throws Exception {
  LOG.info("Getting max data time for " + dataset);
  DatasetConfigDTO datasetConfig = ThirdEyeUtils.getDatasetConfigFromName(dataset);
  TimeSpec timeSpec = ThirdEyeUtils.getTimestampTimeSpecFromDatasetConfig(datasetConfig);
  DateTimeZone timeZone = Utils.getDataTimeZone(dataset);
  long maxTime = 0;

  String sourceName = dataset.split("\\.")[0];
  String tableName = SqlUtils.computeSqlTableName(dataset);
  DataSource dataSource = getDataSourceFromDataset(dataset);

  try (Connection conn = dataSource.getConnection();
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery(SqlUtils.getMaxDataTimeSQL(timeSpec.getColumnName(), tableName, sourceName))) {
    if (rs.next()) {
      String maxTimeString = rs.getString(1);
      if (maxTimeString.indexOf('.') >= 0) {
        maxTimeString = maxTimeString.substring(0, maxTimeString.indexOf('.'));
      }

      String timeFormat = timeSpec.getFormat();

      if (StringUtils.isBlank(timeFormat) || TimeSpec.SINCE_EPOCH_FORMAT.equals(timeFormat)) {
        maxTime = timeSpec.getDataGranularity().toMillis(Long.valueOf(maxTimeString) - 1, timeZone);
      } else {
        DateTimeFormatter inputDataDateTimeFormatter =
            DateTimeFormat.forPattern(timeFormat).withZone(timeZone);
        DateTime endDateTime = DateTime.parse(maxTimeString, inputDataDateTimeFormatter);
        Period oneBucket = datasetConfig.bucketTimeGranularity().toPeriod();
        maxTime = endDateTime.plus(oneBucket).getMillis() - 1;
      }
    }
  } catch (Exception e) {
    throw e;
  }
  return maxTime;
}
 

@Override
public ThirdEyeResultSetGroup load(SqlQuery SQLQuery) throws Exception {
  String sourceName = SQLQuery.getSourceName();
  DataSource dataSource = null;
  if (sourceName.equals(PRESTO)) {
    dataSource = prestoDBNameToDataSourceMap.get(SQLQuery.getDbName());
  } else if (sourceName.equals(MYSQL)) {
    dataSource = mysqlDBNameToDataSourceMap.get(SQLQuery.getDbName());
  } else if (sourceName.equals(VERTICA)) {
    dataSource = verticaDBNameToDataSourceMap.get(SQLQuery.getDbName());
  } else {
    dataSource = h2DataSource;
  }

  String sqlQuery = SQLQuery.getQuery();
  LOG.info("Running SQL: " + sqlQuery);
  try (Connection conn = dataSource.getConnection();
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery(sqlQuery)) {

    ThirdEyeResultSet resultSet =  fromSQLResultSet(rs, SQLQuery.getMetric(), SQLQuery.getGroupByKeys(), SQLQuery.getGranularity(),
        SQLQuery.getTimeSpec());

    List<ThirdEyeResultSet> thirdEyeResultSets = new ArrayList<>();
    thirdEyeResultSets.add(resultSet);
    return new ThirdEyeResultSetGroup(thirdEyeResultSets);
  } catch (Exception e) {
    throw e;
  }
}
 

public static void main(String[] args) throws Exception {
    PoolConfiguration p = new PoolProperties();
    p.setUrl("jdbc:mysql://localhost:3306/mysql?autoReconnect=true");
    p.setDriverClassName("com.mysql.jdbc.Driver");
    p.setUsername("root");
    p.setPassword("password");
    p.setJmxEnabled(true);
    p.setTestWhileIdle(false);
    p.setTestOnBorrow(true);
    p.setValidationQuery("SELECT 1");
    p.setTestOnReturn(false);
    p.setValidationInterval(30000);
    p.setTimeBetweenEvictionRunsMillis(30000);
    p.setMaxActive(100);
    p.setInitialSize(10);
    p.setMaxWait(10000);
    p.setRemoveAbandonedTimeout(60);
    p.setMinEvictableIdleTimeMillis(30000);
    p.setMinIdle(10);
    p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
    p.setLogAbandoned(true);
    p.setRemoveAbandoned(true);
    DataSource datasource = new DataSource();
    datasource.setPoolProperties(p);

    Connection con = null;
    try {
        con = datasource.getConnection();
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery("select * from user");
        int cnt = 1;
        while (rs.next()) {
            System.out.println((cnt++)+". Host:" +rs.getString("Host")+" User:"+rs.getString("User")+" Password:"+rs.getString("Password"));
        }
        rs.close();
        st.close();
    } finally {
        if (con!=null) {
            try {
                con.close();
            } catch (Exception ignore) {
                // Ignore
            }
        }
    }
}
 

protected int initPool(DFDbCfg cfg) throws Throwable{
	lockDbRead.lock();
	try{
		Integer idExist = mapDbId.get(cfg.strId);
		if(idExist != null){ //exist
			return idExist;
		}
	}finally{
		lockDbRead.unlock();
	}
	//
	DataSource dbSrc = DFDbUtil.createMysqlDbSource(cfg.getUrl(), cfg.getUser(), cfg.getPwd(), 
			cfg.getInitSize(), cfg.getMaxActive(), cfg.getMaxWait(), cfg.getMaxIdle(), cfg.getMinIdle());
	//conn test
	boolean testFail = false;
	Connection conn = null;
	Throwable eOut = null;
	try{
		conn = dbSrc.getConnection();
		Statement stmt = conn.createStatement();
		stmt.executeQuery("select 1");
	}catch(Throwable e){
		testFail = true;
		eOut = e;
	}finally{
		if(conn != null){
			conn.close();
		}
		if(testFail){
			if(dbSrc != null){
				dbSrc.close(); dbSrc = null;
			}
		}
	}
	if(eOut != null){
		throw eOut;
	}
	//
	DbPoolWrap wrap = new DbPoolWrap(dbSrc, cfg.strId);
	lockDbWrite.lock();
	try{
		int curId = idCount;
		mapDb.put(curId, wrap);
		mapDbId.put(cfg.strId, curId);
		if(idCount >= Integer.MAX_VALUE){
			idCount = 1;
		}else{
			++idCount;
		}
		return curId;
	}finally{
		lockDbWrite.unlock();
	}
}
 

DataSourceModule(DataSource dataSource) {
  this.dataSource = dataSource;
  entityMappingHolder = new EntityMappingHolder();
  try (Connection conn = dataSource.getConnection()) {
    entityMappingHolder.register(conn, GenericJsonEntity.class,
        convertCamelCaseToUnderscore(GenericJsonEntity.class.getSimpleName()));
    entityMappingHolder.register(conn, AnomalyFeedbackIndex.class,
        convertCamelCaseToUnderscore(AnomalyFeedbackIndex.class.getSimpleName()));
    entityMappingHolder.register(conn, AnomalyFunctionIndex.class,
        convertCamelCaseToUnderscore(AnomalyFunctionIndex.class.getSimpleName()));
    entityMappingHolder.register(conn, JobIndex.class,
        convertCamelCaseToUnderscore(JobIndex.class.getSimpleName()));
    entityMappingHolder.register(conn, MergedAnomalyResultIndex.class,
        convertCamelCaseToUnderscore(MergedAnomalyResultIndex.class.getSimpleName()));
    entityMappingHolder.register(conn, RawAnomalyResultIndex.class,
        convertCamelCaseToUnderscore(RawAnomalyResultIndex.class.getSimpleName()));
    entityMappingHolder.register(conn, TaskIndex.class,
        convertCamelCaseToUnderscore(TaskIndex.class.getSimpleName()));
    entityMappingHolder.register(conn, DatasetConfigIndex.class,
        convertCamelCaseToUnderscore(DatasetConfigIndex.class.getSimpleName()));
    entityMappingHolder.register(conn, MetricConfigIndex.class,
        convertCamelCaseToUnderscore(MetricConfigIndex.class.getSimpleName()));
    entityMappingHolder.register(conn, OverrideConfigIndex.class,
        convertCamelCaseToUnderscore(OverrideConfigIndex.class.getSimpleName()));
    entityMappingHolder.register(conn, AlertConfigIndex.class,
        convertCamelCaseToUnderscore(AlertConfigIndex.class.getSimpleName()));
    entityMappingHolder.register(conn, DataCompletenessConfigIndex.class,
        convertCamelCaseToUnderscore(DataCompletenessConfigIndex.class.getSimpleName()));
    entityMappingHolder.register(conn, EventIndex.class,
        convertCamelCaseToUnderscore(EventIndex.class.getSimpleName()));
    entityMappingHolder.register(conn, DetectionStatusIndex.class,
        convertCamelCaseToUnderscore(DetectionStatusIndex.class.getSimpleName()));
    entityMappingHolder.register(conn, ClassificationConfigIndex.class,
        convertCamelCaseToUnderscore(ClassificationConfigIndex.class.getSimpleName()));
    entityMappingHolder.register(conn, EntityToEntityMappingIndex.class,
        convertCamelCaseToUnderscore(EntityToEntityMappingIndex.class.getSimpleName()));
    entityMappingHolder.register(conn, GroupedAnomalyResultsIndex.class,
        convertCamelCaseToUnderscore(GroupedAnomalyResultsIndex.class.getSimpleName()));
    entityMappingHolder.register(conn, OnboardDatasetMetricIndex.class,
        convertCamelCaseToUnderscore(OnboardDatasetMetricIndex.class.getSimpleName()));
    entityMappingHolder.register(conn, ConfigIndex.class,
        convertCamelCaseToUnderscore(ConfigIndex.class.getSimpleName()));
    entityMappingHolder.register(conn, ApplicationIndex.class,
        convertCamelCaseToUnderscore(ApplicationIndex.class.getSimpleName()));
    entityMappingHolder.register(conn, AlertSnapshotIndex.class,
        convertCamelCaseToUnderscore(AlertSnapshotIndex.class.getSimpleName()));
    entityMappingHolder.register(conn, RootcauseSessionIndex.class,
        convertCamelCaseToUnderscore(RootcauseSessionIndex.class.getSimpleName()));
    entityMappingHolder.register(conn, SessionIndex.class,
        convertCamelCaseToUnderscore(SessionIndex.class.getSimpleName()));
    entityMappingHolder.register(conn, DetectionConfigIndex.class,
        convertCamelCaseToUnderscore(DetectionConfigIndex.class.getSimpleName()));
    entityMappingHolder.register(conn, DetectionAlertConfigIndex.class,
        convertCamelCaseToUnderscore(DetectionAlertConfigIndex.class.getSimpleName()));
    entityMappingHolder.register(conn, EvaluationIndex.class,
        convertCamelCaseToUnderscore(EvaluationIndex.class.getSimpleName()));
  } catch (Exception e) {
    throw new RuntimeException(e);
  }
  builder = new SqlQueryBuilder(entityMappingHolder);

  genericResultSetMapper = new GenericResultSetMapper(entityMappingHolder);
}