Java DataSource.getConnection方法代碼示例

import org.apache.tomcat.jdbc.pool.DataSource; //導入方法依賴的package包/類
/**
 * Connects to specified database.
 */
private void connectToDb() {
    this.loadProperties();
    PoolProperties p = new PoolProperties();
    p.setUrl(this.prs.getProperty("url"));
    p.setDriverClassName(SQL_DRIVER);
    p.setUsername(this.prs.getProperty("user"));
    p.setPassword(this.prs.getProperty("password"));
    DataSource datasource = new DataSource();
    datasource.setPoolProperties(p);
    try {
        this.conn = datasource.getConnection();
    } catch (SQLException e) {
        LOG.error(e.getMessage(), e);
    }
}
 

import org.apache.tomcat.jdbc.pool.DataSource; //導入方法依賴的package包/類
/**
 * Connects to specified database.
 */
private void connectToDb() {
    this.loadProperties();

    PoolProperties p = new PoolProperties();
    DataSource datasource = new DataSource();
    p.setUrl(this.prs.getProperty("url"));
    p.setDriverClassName(SQL_DRIVER);
    p.setUsername(this.prs.getProperty("user"));
    p.setPassword(this.prs.getProperty("password"));
    datasource.setPoolProperties(p);
    try {
        this.conn = datasource.getConnection();
    } catch (SQLException e) {
        LOG.error(e.getMessage(), e);
    }
    this.createTable();
}
 

import org.apache.tomcat.jdbc.pool.DataSource; //導入方法依賴的package包/類
/**
 * Connects to specified database.
 */
private void connectToDb() {
    this.loadProperties();
    PoolProperties p = new PoolProperties();
    p.setUrl(this.prs.getProperty("url"));
    p.setDriverClassName(SQL_DRIVER);
    p.setUsername(this.prs.getProperty("user"));
    p.setPassword(this.prs.getProperty("password"));
    DataSource datasource = new DataSource();
    datasource.setPoolProperties(p);
    try {
        this.conn = datasource.getConnection();
        System.out.println(this.conn);
    } catch (SQLException e) {
        LOG.error(e.getMessage(), e);
    }
    this.createTable();
}
 

import org.apache.tomcat.jdbc.pool.DataSource; //導入方法依賴的package包/類
/**
 * For wiping the test database before running tests, so we know they are atomic
 * @throws SQLException
 * @throws IOException 
 */
protected static void NestWipeDB() throws SQLException, IOException {
    // Wipe the DB so we know the tests are atomic.
    
    // First read the repeatable SQL DB schema into memory.
    String dbSchema = null;
    try (Scanner scanner = new Scanner(new File(DBCREATESCRIPT_PATH));) {
        dbSchema = scanner.useDelimiter("\\A").next();
    }
    if (dbSchema == null) {
        throw new IOException("Unable to load repeatable database schema");
    }
    
    // Next wipe the db
    DataSource dsTest = Common.getNestDS(DBCONFIGPATH_TEST);
    try (
        Connection conn = dsTest.getConnection();
        Statement st = conn.createStatement();        
    ) {
        boolean hasResults = st.execute(dbSchema);
    }
    dsTest.close();
}
 

import org.apache.tomcat.jdbc.pool.DataSource; //導入方法依賴的package包/類
/**
 * Remove the created entities directly via the db so we don't worry about bugs in DELETE.
 * @param entitiesList These should be names of tables in the database!
 * @param ids This should have keys for each value in entitiesList!
 * @return
 * @throws SQLException
 * @throws IOException 
 */
protected static boolean dbDeleteEntities(List<String> entitiesList, Map<String,Long> ids) throws SQLException, IOException {
    DataSource dsTest = Common.getNestDS(DBCONFIGPATH_TEST);
    
    for(int i = entitiesList.size()-1; i >= 0; i--) {
        
        // Get the entities to delete
        final String tablename = entitiesList.get(i);
        Long id = ids.get(tablename);
        // It's late, okay!?
        final String columnname = (!tablename.equals("users"))? tablename+"_id" : "user_id";
        
        try (
            Connection conn = dsTest.getConnection();
            Statement st = conn.createStatement();        
        ) {
            boolean hasResults = st.execute("DELETE FROM "+tablename+" WHERE "+columnname+" = "+id+";");
        }
    }
    dsTest.close();
    return true;
}
 

import org.apache.tomcat.jdbc.pool.DataSource; //導入方法依賴的package包/類
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) {}
    }
}
 

import org.apache.tomcat.jdbc.pool.DataSource; //導入方法依賴的package包/類
/**
 * Connects to specified database.
 */
private void connectToDb() {
    PoolProperties p = new PoolProperties();
    p.setUrl(this.prs.getProperty("url"));
    p.setDriverClassName(SQL_DRIVER);
    p.setUsername(this.prs.getProperty("user"));
    p.setPassword(this.prs.getProperty("password"));
    DataSource datasource = new DataSource();
    datasource.setPoolProperties(p);
    try {
        this.conn = datasource.getConnection();
    } catch (SQLException e) {
        LOG.error(e.getMessage(), e);
    }
}
 

import org.apache.tomcat.jdbc.pool.DataSource; //導入方法依賴的package包/類
/**
 * returns the default read-only connection for this role 
 */
public static Connection getConnection(String role) throws SQLException, UnknownRoleException {
    Properties props = null;
    try {
    	role = getRealRole(role);
    	if(!usedRoles.contains(role))
    		usedRoles.add(role);
        String user, schema;
        if (defaultUsers.containsKey(role) && 
            defaultSchemas.containsKey(role)) {
            user = defaultUsers.get(role);
            schema = defaultSchemas.get(role);
        } else {
            props = getPropertiesForRole(role);
            user = props.getProperty("user");
            schema = props.getProperty("schema");
            defaultUsers.put(role,user);
            defaultSchemas.put(role,schema);
        }
        String key = role + user + schema;
        if (!sources.containsKey(key)) {
            if (props == null)
                props = getPropertiesForRole(role);
            addDataSource(key,props);
        }
        DataSource src = sources.get(key);            
        Connection cxn = src.getConnection();
        cxnSource.put(cxn,src);
        return cxn;
    } catch (IOException ex) {
        throw new RuntimeException("Couldn't read properties for " + role,ex);
    }
}
 

import org.apache.tomcat.jdbc.pool.DataSource; //導入方法依賴的package包/類
public static void migrate(DataSource dataSource) throws SQLException, IOException {
    try (Connection conn = dataSource.getConnection()) { //pool.getConnection()) {
        conn.setAutoCommit(false);
        DatabaseMetaData meta = conn.getMetaData();

        if (!meta.supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE)) {
            LOG.error("Current database ({}) does not support required isolation level ({})",
                    dataSource.getUrl(), Connection.TRANSACTION_SERIALIZABLE);
            throw new RuntimeException("Current database does not support serializable");
        }
        maybeCreateTables(conn);
        conn.commit();
    }
}
 

import org.apache.tomcat.jdbc.pool.DataSource; //導入方法依賴的package包/類
/**
 * Test that the handler can connect to the test db server using the Common method
 * @throws IOException
 * @throws SQLException 
 */
@Test
public void TestDatabaseConnects() throws IOException, SQLException {
    // Load the db config properties
    DataSource dsTest = Common.getNestDS(dbConfigPathTest);
    Connection conn = dsTest.getConnection();
    Statement st = conn.createStatement();
    ResultSet rsh = st.executeQuery("SELECT 1;");
    
    assertTrue(rsh.isBeforeFirst());
    rsh.close();
    st.close();
    conn.close();
    dsTest.close();
}
 

import org.apache.tomcat.jdbc.pool.DataSource; //導入方法依賴的package包/類
/**
 * Test that the handler can connect to the prod db server using the Common method
 * @throws IOException
 * @throws SQLException 
 */
@Test
public void ProdDatabaseConnects() throws IOException, SQLException {
    // Load the db config properties
    DataSource dsProd = Common.getNestDS(dbConfigPathProd);
    Connection conn = dsProd.getConnection();
    Statement st = conn.createStatement();
    ResultSet rsh = st.executeQuery("SELECT 1;");

    assertTrue(rsh.isBeforeFirst());
    rsh.close();
    st.close();
    conn.close();
    dsProd.close();
}
 

import org.apache.tomcat.jdbc.pool.DataSource; //導入方法依賴的package包/類
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) {}
    }  
}
 

import org.apache.tomcat.jdbc.pool.DataSource; //導入方法依賴的package包/類
@Test
public void initial() throws Exception {
    PoolProperties p = new PoolProperties();
    p.setUrl("jdbc:h2:mem");
    p.setDriverClassName("org.h2.Driver");
    p.setUsername("sa");
    p.setPassword("sa");
    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.setLogAbandoned(true);
    p.setRemoveAbandoned(true);
    p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"
            + "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
    DataSource dataSource = new DataSource();
    dataSource.setPoolProperties(p);
    try (Connection connection = dataSource.getConnection(); 
            Statement statement = connection.createStatement(); 
            ResultSet resultSet = statement.executeQuery("select 1")) {
        while (resultSet.next()) {
            logger.info("{}", resultSet.getObject(1));
        }
    }          
}
 

import org.apache.tomcat.jdbc.pool.DataSource; //導入方法依賴的package包/類
/**
 * Test that any mapped dataset files have parsable parameter types.
 * @throws IOException if the dataset mappings, or any datasets are unaccessible
 * @throws MalformedJsonException if a dataset cannot be parsed into JSON
 * @throws java.sql.SQLException
 * @throws java.text.ParseException
 * @throws java.lang.TypeNotPresentException
 */
@Test
public void MappedDatasetsHaveValidParams() throws TypeNotPresentException, IOException, MalformedJsonException, SQLException, ParseException {
    
    // Unfortunately we need a db connection to test this (not actually queried, but necessary)
    String dbConfigPathTest = DBCONFIG_PATH + ".dev".replace("/", SEP);
    
    DataSource dsTest = Common.getNestDS(dbConfigPathTest);
    try (
        Connection conn = dsTest.getConnection();
    ) {

        Properties prop = new Properties();
        try (InputStream input = new FileInputStream(DATASETS_PATH)) {
            prop.load(input);
        }
        for(Entry<Object, Object> e : prop.entrySet()) {
            File f = new File((SERVLET_CONTEXT_PATH + "/web" + e.getValue().toString().replace("/", SEP)));
            assertTrue(f.exists());

            // Get the dataset json string from file
            final String datasetPath = f.toString();
            byte[] encoded = Files.readAllBytes(Paths.get(datasetPath));
            final String datasetJSON = new String(encoded, StandardCharsets.UTF_8);

            // Decode from JSON clean enclosing quotes and newlines then return
            JsonObject jObject = new JsonParser().parse(datasetJSON).getAsJsonObject();
            Set<Map.Entry<String, JsonElement>> entries = jObject.entrySet();//will return members of your object
            for (Map.Entry<String, JsonElement> entry: entries) {
                String datasetSQL = entry.getValue().getAsString();
                
                // Remove the returned quotes as the SQL is a string, but only if its not null-length already.
                datasetSQL = (datasetSQL.length() > 2) ? datasetSQL.substring(1, datasetSQL.length()-1) : "";
                datasetSQL = datasetSQL.replace("\\r\\n", "\n").replace("\\n", "\n");
                
                // Parse the dataset parameters out of the dataset
                Map<String, String> datasetParams = new HashMap<>();
                List<String> datasetParamOrder = new ArrayList<>();
                Common.parseDatasetParameters(datasetSQL, datasetParams, datasetParamOrder);
                
                final String cleanSQL = datasetSQL.replaceAll(Common.DATASETPARAM_REGEX, "?");
                System.out.println(cleanSQL);
                
                // An exception will be thrown here if dataset parameters exists with unsupported types
                try (PreparedStatement st = conn.prepareStatement(cleanSQL);) {
                    Common.bindDynamicParameters(st, datasetParams, datasetParamOrder);
                }
            }
        }
        // All mapped datasets have been resolved by the JSON parser.
        assertTrue(true);
    }
    Common.closeNestDS();
}
 

import org.apache.tomcat.jdbc.pool.DataSource; //導入方法依賴的package包/類
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, AutotuneConfigIndex.class,
        convertCamelCaseToUnderscore(AutotuneConfigIndex.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()));
  } catch (Exception e) {
    throw new RuntimeException(e);
  }
  builder = new SqlQueryBuilder(entityMappingHolder);

  genericResultSetMapper = new GenericResultSetMapper(entityMappingHolder);
}