Java Code Examples for javax.sql.DataSource

/** 
 * Opens a connection from JNDI based on a registered name. This assumes that there is a <code>jndi.properties</code> file with proper JNDI configuration in it.
 * @param jndiName name of a configured data source. 
 */
public void open(String jndiName){
  checkExistingConnection(dbName);
  try {
    Context ctx=new InitialContext();
    DataSource ds=(DataSource)ctx.lookup(jndiName);
    Connection connection=ds.getConnection();
    ConnectionsAccess.attach(dbName,connection);
  }
 catch (  Exception e) {
    throw new InitException(e);
  }
}
 

/** 
 * Test method.
 * @throws SQLException 
 * @throws InterruptedException 
 * @throws IllegalAccessException 
 * @throws IllegalArgumentException 
 * @throws NoSuchFieldException 
 * @throws SecurityException 
 */
@Test public void testGetConnectionViaDataSourceBean() throws SQLException, InterruptedException, IllegalArgumentException, IllegalAccessException, SecurityException, NoSuchFieldException {
  DataSource mockDataSource=createNiceMock(DataSource.class);
  expect(mockDataSource.getConnection()).andReturn(mockConnection).once();
  expect(mockConfig.getJdbcUrl()).andReturn("").once().andReturn("jdbc:mock:foo").once();
  expect(mockConfig.getUsername()).andReturn(null).anyTimes();
  expect(mockConfig.getDatasourceBean()).andReturn(mockDataSource).once().andReturn(null).once();
  replay(mockConfig,mockDataSource);
  testClass.obtainRawInternalConnection();
  testClass.obtainRawInternalConnection();
  verify(mockConfig,mockDataSource);
}
 

public static void main(String[] argv){
  try {
    String jndiName=argv[0];
    DataSource unpooled=DataSources.unpooledDataSource("jdbc:postgresql://localhost/test","swaldman","test");
    DataSource pooled=DataSources.pooledDataSource(unpooled);
    InitialContext ctx=new InitialContext();
    ctx.rebind(jndiName,pooled);
    System.out.println("DataSource bound to nameservice under the name \"" + jndiName + '\"');
  }
 catch (  Exception e) {
    e.printStackTrace();
  }
}
 

@Override public void open(BaseContext context) throws ServiceException {
  DataSource ds=context.get(TrackingContextKeys.EMBEDDED_TRACKING_DATASOURCE,DataSource.class,null);
  if (ds == null) {
    throw new ServiceException("DataSource can not be null");
  }
  ((JdbcDataSource)ds).setPassword("sa");
  ((JdbcDataSource)ds).setUser("sa");
  int maxcon=context.get(TrackingContextKeys.EMBEDDED_TRACKING_DATASOURCE_MAX_CONNECTIONS,int.class,50);
  this.poolMgr=new MiniConnectionPoolManager((ConnectionPoolDataSource)ds,maxcon);
  initTable();
}
 

@Test public void testSimpleInMemoryJDBCDynamicPropertySource() throws Throwable {
  DataSource ds=createDataConfigSource("MySiteConfiguration");
  JDBCConfigurationSource source=new JDBCConfigurationSource(ds,"select distinct property_key, property_value from MySiteProperties","property_key","property_value");
  FixedDelayPollingScheduler scheduler=new FixedDelayPollingScheduler(0,10,false);
  DynamicConfiguration configuration=new DynamicConfiguration(source,scheduler);
  DynamicPropertyFactory.initWithConfigurationSource(configuration);
  DynamicStringProperty defaultProp=DynamicPropertyFactory.getInstance().getStringProperty("this.prop.does.not.exist.use.default","default");
  assertEquals("default",defaultProp.get());
  DynamicStringProperty prop1=DynamicPropertyFactory.getInstance().getStringProperty("prop1","value1");
  assertEquals("value1",prop1.get());
}
 

@Test public void shouldBeAbleToIssueAsAdminCommand() throws Exception {
  Assert.assertNotNull("Verify that the asadmin CommandRunner resource is available",commandRunner);
  CommandResult result=commandRunner.run("create-jdbc-connection-pool","--datasourceclassname=org.apache.derby.jdbc.EmbeddedXADataSource","--restype=javax.sql.XADataSource","--property=portNumber=1527:password=APP:user=APP" + ":serverName=localhost:databaseName=my_database" + ":connectionAttributes=create\\=true","my_derby_pool");
  Assert.assertEquals("Verify 'create-jdbc-connection-pool' asadmin command",ExitStatus.SUCCESS,result.getExitStatus());
  result=commandRunner.run("create-jdbc-resource","--connectionpoolid","my_derby_pool","jdbc/my_database");
  Assert.assertEquals("Verify 'create-jdbc-resource' asadmin command",ExitStatus.SUCCESS,result.getExitStatus());
  result=commandRunner.run("ping-connection-pool","my_derby_pool");
  Assert.assertEquals("Verify asadmin command 'ping-connection-pool'",ExitStatus.SUCCESS,result.getExitStatus());
  Context ctx=new InitialContext();
  DataSource myDatabase=(DataSource)ctx.lookup("jdbc/my_database");
  Assert.assertNotNull(myDatabase);
}
 

/** 
 * Returns the database connection to use
 * @return the database connection to use
 * @throws SQLException if an unexpected error occurs
 */
public Connection getConnection() throws SQLException {
  if ((connection == null) || connection.isClosed()) {
    DataSource ds=getDataSource();
    if (ds != null) {
      connection=ds.getConnection();
    }
 else {
      throw new SQLException("Datasource is null");
    }
  }
  return connection;
}
 

private DataSource getDataSourceByKey(Class<?> daoClass,String key){
  if (applicationContext.containsBean(key)) {
    DataSource dataSource=(DataSource)applicationContext.getBean(key,DataSource.class);
    if (logger.isDebugEnabled()) {
      logger.debug("found dataSource: " + key + " for DAO "+ daoClass.getName());
    }
    return dataSource;
  }
  return null;
}
 

public OrderCreateTable(CamelContext camelContext){
  DataSource ds=camelContext.getRegistry().lookup("myDataSource",DataSource.class);
  JdbcTemplate jdbc=new JdbcTemplate(ds);
  try {
    jdbc.execute("drop table riders_order");
  }
 catch (  Exception e) {
  }
  jdbc.execute("create table riders_order " + "( customer_id varchar(10), ref_no varchar(10), part_id varchar(10), amount varchar(10) )");
}
 

public Database createNewDatabase() throws DatabaseCreationException {
  String databaseName=nextDatabaseIdentifier();
  DataSource internalDataSource=createNewInternalDataSource(databaseName);
  InternalDerbyDatabase internalDatabase=createNewInternalDatabase(internalDataSource);
  internalDatabase.setName(databaseName);
  return internalDatabase;
}
 

@Test @SuppressWarnings("PMD.CloseResource") public void createConnection_should_haveDataTypeFactory() throws SQLException {
  final DataSource dataSource=createMock(DataSource.class);
  final IDataTypeFactory dataTypeFactory=createMock(IDataTypeFactory.class);
  final Connection conn=EasyMock.createNiceMock(Connection.class);
  connectionFactory.setDataSource(dataSource);
  connectionFactory.setDataTypeFactory(dataTypeFactory);
  expect(dataSource.getConnection()).andReturn(conn);
  replayAll();
  final IDatabaseConnection connection=connectionFactory.createConnection();
  final IDataTypeFactory actual=(IDataTypeFactory)connection.getConfig().getProperty("http://www.dbunit.org/properties/datatypeFactory");
  assertThat(actual,is(dataTypeFactory));
  verifyAll();
}
 

/** 
 * Performs an SQL query.
 * @param sql the sql query
 */
protected void runSQL(String sql) throws SystemException {
  try {
    DataSource dataSource=pmBlockedUserPersistence.getDataSource();
    SqlUpdate sqlUpdate=SqlUpdateFactoryUtil.getSqlUpdate(dataSource,sql,new int[0]);
    sqlUpdate.update();
  }
 catch (  Exception e) {
    throw new SystemException(e);
  }
}
 

/** 
 * Performs an SQL query.
 * @param sql the sql query
 */
protected void runSQL(String sql) throws SystemException {
  try {
    DataSource dataSource=allocatedStoragePersistence.getDataSource();
    SqlUpdate sqlUpdate=SqlUpdateFactoryUtil.getSqlUpdate(dataSource,sql,new int[0]);
    sqlUpdate.update();
  }
 catch (  Exception e) {
    throw new SystemException(e);
  }
}
 

private void registerJndiDS(ServerConfig config) throws IOException, NamingException {
  DataSource dataSource=createDataSource(config);
  Context context;
  context=new InitialContext();
  Context compCtx=(Context)context.lookup("java:comp");
  Context envCtx=compCtx.createSubcontext("env");
  NamingUtil.bind(envCtx,"jdbc/eHourDS",dataSource);
}
 

public DataSource getDataSource(){
  DataSource ds=null;
  try {
    ds=(DataSource)getInitialContext().lookup(getContextName());
  }
 catch (  NamingException e) {
    e.printStackTrace();
  }
  return ds;
}
 

public void testLoadVersionItem() throws Throwable {
  DataSource dataSource=(DataSource)this.getApplicationContext().getBean("portDataSource");
  ConfigItemDAO configItemDAO=new ConfigItemDAO();
  configItemDAO.setDataSource(dataSource);
  String config=null;
  try {
    config=configItemDAO.loadVersionItem("test",SystemConstants.CONFIG_ITEM_LANGS);
  }
 catch (  Throwable e) {
    throw e;
  }
  int index=config.indexOf("<code>it</code>");
  assertTrue(index != -1);
}
 

/** 
 * Performs an SQL query.
 * @param sql the sql query
 */
protected void runSQL(String sql) throws SystemException {
  try {
    DataSource dataSource=grantRequestPersistence.getDataSource();
    SqlUpdate sqlUpdate=SqlUpdateFactoryUtil.getSqlUpdate(dataSource,sql,new int[0]);
    sqlUpdate.update();
  }
 catch (  Exception e) {
    throw new SystemException(e);
  }
}
 

/** 
 * Checks the result of the getCurrentSchema call.
 * @param useProxy Flag indicating whether to check it using a proxy user or not.
 */
private void checkCurrentSchema(boolean useProxy) throws Exception {
  Properties customProperties=getConnectionProperties();
  String user=customProperties.getProperty("oracle.user");
  String password=customProperties.getProperty("oracle.password");
  String url=customProperties.getProperty("oracle.url");
  String dataSourceUser=useProxy ? "flyway_proxy[" + user + "]" : user;
  DataSource dataSource=new DriverDataSource(DRIVER_CLASS,url,dataSourceUser,password);
  Connection connection=dataSource.getConnection();
  String currentSchema=new OracleDbSupport(connection).getCurrentSchema();
  connection.close();
  assertEquals(user.toUpperCase(),currentSchema);
}
 

/** 
 * Create a DataSource object.
 * @param props The properties that define the DataSource implementation tocreate and how the DataSource is configured
 * @return The configured DataSource
 * @throws SQLException
 * @see org.osgi.service.jdbc.DataSourceFactory#createDataSource(java.util.Properties)
 */
public DataSource createDataSource(Properties props) throws SQLException {
  if (props == null)   props=new Properties();
  if (props.get(DataSourceFactory.JDBC_URL) != null) {
    return new UrlBasedDriverDataSource(props,newJdbcDriver());
  }
 else {
    DataSource dataSource=newDataSource();
    setDataSourceProperties(dataSource,props);
    return dataSource;
  }
}
 

/** 
 * Create a DataSource object.
 * @param props The properties that define the DataSource implementation tocreate and how the DataSource is configured
 * @return The configured DataSource
 * @throws SQLException
 * @see org.osgi.service.jdbc.DataSourceFactory#createDataSource(java.util.Properties)
 */
public DataSource createDataSource(Properties props) throws SQLException {
  if (props == null)   props=new Properties();
  if (props.get(DataSourceFactory.JDBC_URL) != null) {
    return new UrlBasedDriverDataSource(props,newJdbcDriver());
  }
 else {
    DataSource dataSource=newDataSource();
    setDataSourceProperties(dataSource,props);
    return dataSource;
  }
}
 

/** 
 * init() is called once automatically by the servlet container (e.g. Tomcat) at servlet startup. We use it to initialise various things, namely: a) create the DatabaseDefn object which is the top level application object. The DatabaseDefn object will load the list of tables & reports into memory when it is constructed. It will also configure and load the object database b) create a DataSource object here to pass to the DatabaseDefn. This data source then acts as a pool of connections from which a connection to the relational database can be called up whenever needed.
 */
public void init() throws ServletException {
  logger.info("Initialising " + AppProperties.applicationName);
  ServletContext servletContext=getServletContext();
  this.webAppRoot=servletContext.getRealPath("/");
  DataSource relationalDataSource=null;
  InitialContext initialContext=null;
  try {
    initialContext=new InitialContext();
    relationalDataSource=(DataSource)initialContext.lookup("java:comp/env/jdbc/agileBaseData");
    if (relationalDataSource == null) {
      throw new ServletException("Can't get data source");
    }
    this.relationalDataSource=relationalDataSource;
    this.databaseDefn=new DatabaseDefn(relationalDataSource,this.webAppRoot);
    servletContext.setAttribute("com.gtwm.pb.servlets.databaseDefn",this.databaseDefn);
    servletContext.setAttribute("com.gtwm.pb.servlets.relationalDataSource",this.relationalDataSource);
  }
 catch (  NullPointerException npex) {
    ServletUtilMethods.logException(npex,"Error initialising controller servlet");
    throw new ServletException("Error initialising controller servlet",npex);
  }
catch (  SQLException sqlex) {
    ServletUtilMethods.logException(sqlex,"Database error loading schema");
    throw new ServletException("Database error loading schema",sqlex);
  }
catch (  NamingException neex) {
    ServletUtilMethods.logException(neex,"Can't get initial context");
    throw new ServletException("Can't get initial context");
  }
catch (  RuntimeException rtex) {
    ServletUtilMethods.logException(rtex,"Runtime initialisation error");
    throw new ServletException("Runtime initialisation error",rtex);
  }
catch (  Exception ex) {
    ServletUtilMethods.logException(ex,"General initialisation error");
    throw new ServletException("General initialisation error",ex);
  }
  logger.info("Application fully loaded");
}
 

private void createDatabaseConnection(){
  try {
    if (databaseConnectionProducer.get() != null && !databaseConnectionProducer.get().getConnection().isClosed()) {
      closeDatabaseConnection();
    }
    DataSource dataSource=dataSourceInstance.get();
    final String schema=dbUnitConfigurationInstance.get().getSchema();
    DatabaseConnection databaseConnection=null;
    if (schema != null && schema.length() > 0) {
      databaseConnection=new DatabaseConnection(dataSource.getConnection(),schema);
    }
 else {
      databaseConnection=new DatabaseConnection(dataSource.getConnection());
    }
    databaseConnectionProducer.set(databaseConnection);
    final DatabaseConfig dbUnitConfig=databaseConnection.getConfig();
    dbUnitConfig.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY,new DefaultDataTypeFactory());
    final Map<String,Object> properties=new DBUnitConfigurationPropertyMapper().map(dbUnitConfigurationInstance.get());
    for (    Entry<String,Object> property : properties.entrySet()) {
      dbUnitConfig.setProperty(property.getKey(),property.getValue());
    }
  }
 catch (  Exception e) {
    throw new DBUnitInitializationException("Unable to initialize database connection for DBUnit module.",e);
  }
}
 

public void startSample(String[] args) throws Exception {
  DataSource clientDataSource=null;
  Connection clientConn1=null;
  Connection clientConn2=null;
  try {
    System.out.println("Starting Sample client program ");
    loadDriver();
    clientConn1=getClientDriverManagerConnection();
    System.out.println("Got a client connection via the DriverManager.");
    javax.sql.DataSource myDataSource=getClientDataSource(DBNAME,null,null);
    clientConn2=getClientDataSourceConn(myDataSource);
    System.out.println("Got a client connection via a DataSource.");
    System.out.println("Testing the connection obtained via DriverManager by executing a sample query ");
    test(clientConn1);
    System.out.println("Testing the connection obtained via a DataSource by executing a sample query ");
    test(clientConn2);
    System.out.println("Goodbye!");
  }
 catch (  SQLException sqle) {
    System.out.println("Failure making connection: " + sqle);
    sqle.printStackTrace();
  }
 finally {
    if (clientConn1 != null)     clientConn1.close();
    if (clientConn2 != null)     clientConn2.close();
  }
}
 

public void createTables(String dsName,String script) throws NamingException, SQLException {
  InitialContext context=new InitialContext();
  DataSource ds=(DataSource)context.lookup(dsName);
  Connection conn=ds.getConnection();
  String sql="";
  try {
    String[] scripts=JDBCUtils.splitWithSQLDelimiter(script);
    for (    String scr : scripts) {
      String s=JDBCUtils.cleanWhitespaces(scr.trim());
      if (s.length() < 1)       continue;
      sql=s;
      if (LOG.isDebugEnabled())       LOG.debug("Execute script: \n[" + sql + "]");
      try {
        conn.setAutoCommit(false);
        conn.createStatement().executeUpdate(sql);
        conn.commit();
      }
 catch (      SQLException e) {
        conn.rollback();
        Matcher aeMatcher=pattern.matcher(e.getMessage().trim());
        if (!aeMatcher.matches())         throw e;
        if (LOG.isDebugEnabled())         LOG.debug(e.getMessage());
      }
    }
    LOG.info("DB schema of DataSource: '" + dsName + "' created succesfully. context "+ context);
  }
 catch (  SQLException e) {
    LOG.error("Could not create db schema of DataSource: '" + dsName + "'. Reason: "+ e.getMessage()+ "; "+ JDBCUtils.getFullMessage(e)+ ". Last command: "+ sql,e);
  }
 finally {
    conn.close();
  }
}
 

@Override public void configureMBeans(){
  bindConfig(binder()).annotatedWith(annotation).prefixedWith(propertyPrefix).to(H2EmbeddedDataSourceConfig.class);
  bind(DataSource.class).annotatedWith(annotation).toProvider(new H2EmbeddedDataSourceProvider(annotation)).in(Scopes.SINGLETON);
  export(DataSource.class).annotatedWith(annotation).withGeneratedName();
  Key<DataSource> key=Key.get(DataSource.class,annotation);
  for (  Class<? extends Annotation> alias : aliases) {
    bind(DataSource.class).annotatedWith(alias).to(key);
  }
}
 

@Override protected Object resolveResource(AnnotatedElement element) throws Exception {
  Object resolvedResource=null;
  Class<?> resourceType=null;
  if (Field.class.isAssignableFrom(element.getClass())) {
    resourceType=((Field)element).getType();
  }
 else   if (Method.class.isAssignableFrom(element.getClass())) {
    resourceType=((Method)element).getParameterTypes()[0];
  }
  if (resourceType == null) {
    throw new IllegalStateException("No type found for resource injection target " + element);
  }
  if (ResourceAdapter.class.isAssignableFrom(resourceType) || DataSource.class.isAssignableFrom(resourceType)) {
    Resource resourceAnnotation=element.getAnnotation(Resource.class);
    if (!resourceAnnotation.name().equals("")) {
      resolvedResource=lookup(RESOURCE_ADAPTER_LOOKUP_PREFIX + "/" + resourceAnnotation.name());
    }
 else     if (!resourceAnnotation.mappedName().equals("")) {
      resolvedResource=lookup(resourceAnnotation.mappedName());
    }
 else {
      resolvedResource=findResourceByType(resourceType);
    }
  }
 else   if (UserTransaction.class.isAssignableFrom(resourceType)) {
    resolvedResource=lookup("java:comp/UserTransaction");
  }
  return resolvedResource;
}
 

public Bootstrapper(DataSource datasource,PlatformTransactionManager transactionManager,Configuration configuration,SessionFactory sessionFactory,UserService userService,String workspaceManagerLogin,String workspaceManagerPassword){
  this.datasource=datasource;
  this.configuration=configuration;
  this.sessionFactory=sessionFactory;
  this.transactionManager=transactionManager;
  this.userService=userService;
  this.workspaceManagerLogin=workspaceManagerLogin;
  this.workspaceManagerPassword=workspaceManagerPassword;
}
 

/** 
 * Set-up a DBCP DataSource from a properties object. Uses a properties  key prefix to identify the properties associated with profile.  If  a database profile has a prefix of djatoka, the props object would  contain the following pairs: djatoka.url=jdbc:mysql://localhost/djatoka djatoka.driver=com.mysql.jdbc.Driver djatoka.login=root djatoka.pwd= djatoka.maxActive=50 djatoka.maxIdle=10
 * @param dbid database profile properties file prefix
 * @param props properties object containing relevant pairs
 */
public static DataSource setupDataSource(String dbid,Properties props) throws Exception {
  String url=props.getProperty(dbid + ".url");
  String driver=props.getProperty(dbid + ".driver");
  String login=props.getProperty(dbid + ".login");
  String pwd=props.getProperty(dbid + ".pwd");
  int maxActive=50;
  if (props.containsKey(dbid + ".maxActive"))   maxActive=Integer.parseInt(props.getProperty(dbid + ".maxActive"));
  int maxIdle=10;
  if (props.containsKey(dbid + ".maxIdle"))   maxIdle=Integer.parseInt(props.getProperty(dbid + ".maxIdle"));
  log.debug(url + ";" + driver+ ";"+ login+ ";"+ pwd+ ";"+ maxActive+ ";"+ maxIdle);
  return setupDataSource(url,driver,login,pwd,maxActive,maxIdle);
}
 

public DataSource getDatabase(String userName,String dbName){
  SimpleDataSource ds=new SimpleDataSource();
  ds.setServerName("localhost");
  ds.setPortNumber(port);
  ds.setDatabaseName(dbName);
  ds.setUser(userName);
  return ds;
}
 

@PostConstruct public void init(){
  try {
    if (jndi != null) {
      dataSource=JNDIUtils.lookup(jndi,DataSource.class);
    }
  }
 catch (  NamingException e) {
    throw new IllegalStateException("Could not retrieve DataSource from JNDI " + jndi,e);
  }
}
 

/** 
 * Factory method that returns the fully-initialized test data source. Useful when this class is used programatically instead of deployed as a Spring bean.
 * @return the data source
 */
public DataSource getDataSource(){
  if (dataSource == null) {
    initDataSource();
  }
  return dataSource;
}
 

public static SchemaHandler getHandler(DatabaseType dbType,DataSource dataSource){
  SchemaHandler handler=null;
switch (dbType) {
case DERBY:
    handler=new DerbySchemaHandler(dataSource);
  break;
case MYSQL:
handler=new MySQLSchemaHandler(dataSource);
break;
default :
throw new JdbcChannelException("Database " + dbType + " not supported yet");
}
return handler;
}
 

@Bean public DataSource dataSource(){
  if (cloudDataSource != null) {
    log.info("Using DataSource from environment: " + cloudDataSource);
    return cloudDataSource;
  }
  EmbeddedDatabaseFactory factory=new EmbeddedDatabaseFactory();
  factory.setDatabaseName("fuzzydb-sample-webapp");
  factory.setDatabaseType(EmbeddedDatabaseType.H2);
  factory.setDatabasePopulator(databasePopulator());
  return factory.getDatabase();
}
 

public void testProxy() throws Exception {
  bundleContext.registerService(new String[]{DataSource.class.getName(),Comparator.class.getName(),InitializingBean.class.getName(),Constants.class.getName()},new Service(),new Hashtable());
  ConfigurableApplicationContext ctx=getNestedContext();
  assertNotNull(ctx);
  Object proxy=ctx.getBean("service");
  assertNotNull(proxy);
  assertTrue(proxy instanceof DataSource);
  assertTrue(proxy instanceof Comparator);
  assertTrue(proxy instanceof Constants);
  assertTrue(proxy instanceof InitializingBean);
  ctx.close();
}