Java Code Examples for org.h2.jdbcx.JdbcDataSource#setURL()

public DatabaseManager(String subPath, String username, String password) throws SQLException {
	// Setup database connection
	JdbcDataSource ds = new JdbcDataSource();
	ds.setURL("jdbc:h2:" + subPath);
	ds.setUser(username);
	ds.setPassword(password);
	conn = ds.getConnection();

	createTables();

	prepareStatements();

	// Make sure to release the database at shutdown. lose connection
	Runtime.getRuntime().addShutdownHook(new Thread(() -> {
		try {
			if (!conn.isClosed()) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}));

}
 

private static Connection getH2Connection() {
    try {
        String url = "jdbc:h2:./h2db";
        String name = "tully";
        String pass = "tully";

        JdbcDataSource ds = new JdbcDataSource();
        ds.setURL(url);
        ds.setUser(name);
        ds.setPassword(pass);

        Connection connection = DriverManager.getConnection(url, name, pass);
        return connection;
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return null;
}
 

/**
 * Creates a h2 in memory datasource for the tests.
 */
public DataSource createDatasource(){
  JdbcDataSource dataSource = new JdbcDataSource();
  dataSource.setURL("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1");
  dataSource.setUser("sa");
  dataSource.setPassword("");
  return dataSource;
}
 

private Connection getConnectionFor(String dbFilePath) throws SQLException {
    String username = config.get(DatabaseSettings.H2_USER);
    String password = config.get(DatabaseSettings.H2_PASS);

    JdbcDataSource jdbcDataSource = new JdbcDataSource();
    jdbcDataSource.setURL("jdbc:h2:file:" + dbFilePath + ";mode=MySQL;DATABASE_TO_UPPER=false");
    jdbcDataSource.setUser(username);
    jdbcDataSource.setPassword(password);

    return jdbcDataSource.getConnection();
}
 

@Bean(name = "dataSource1")
public DataSource dataSource1(@Value("${db.user}") String user) {
	System.err.println("user: " + user);
	JdbcDataSource ds = new JdbcDataSource();
	ds.setURL("jdbc:h2:˜/test");
	ds.setUser(user);
	return ds;
}
 

public BookingDB() throws SQLException {
    JdbcDataSource ds = new JdbcDataSource();
    ds.setURL("jdbc:h2:mem:rbp;MODE=MySQL");
    ds.setUser("user");
    ds.setPassword("password");
    connection = ds.getConnection();

    // If you would like to access the DB for this API locally. Uncomment the line below and
    // use a SQL client to access jdbc:h2:tcp://localhost:9090/mem:rbp
    // Server server = Server.createTcpServer("-tcpPort", "9090", "-tcpAllowOthers").start();
}
 

@Override
protected DataSource getDataSource() {
    JdbcDataSource dataSource = new JdbcDataSource();
    dataSource.setURL(JDBC_URL);
    dataSource.setUser(USER);
    dataSource.setPassword(PASSWORD);
    return dataSource;
}
 

@Before
public void before() {
    JdbcDataSource ds = new JdbcDataSource();
    ds.setURL("jdbc:h2:mem:t;DB_CLOSE_DELAY=-1;MODE=PostgreSQL");
    this.dbi = new DBI(ds);
    this.jsondb = new SqlJsonDB(dbi, null);
    this.jsondb.createTables();

    client = mock(KubernetesClient.class);
    when(client.getConfiguration()).thenReturn(new ConfigBuilder().withMasterUrl("http://master").build());
}
 

public static DataSource getDataSource() {
    if (dataSource == null) {
        JdbcDataSource ds = new JdbcDataSource();
        ds.setURL("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1");
        ds.setUser("sa");
        ds.setPassword("sa");
        dataSource = ds;
    }
    return dataSource;
}
 

private DataSource createDataSource(String jdbcUrl, String jdbcUsername, String jdbcPassword) {
    JdbcDataSource ds = new JdbcDataSource();
    ds.setURL(jdbcUrl);
    ds.setUser(jdbcUsername);
    ds.setPassword(jdbcPassword);
    return ds;
}
 

private JdbcTemplate buildTemplate() {
	log.debug("Building JDBC template with data souruce: " + dataSourceUrl);
	JdbcDataSource ds = new JdbcDataSource();
	ds.setURL(dataSourceUrl);
	ds.setUser(dataUserName);
	ds.setPassword(dataPassword);
	JdbcTemplate template = new JdbcTemplate(ds);
	return template;
}
 

/**
 * To create the database tables for the particular device-type based on the scripts
 *
 * @param databaseName Name of the Database
 * @param scriptFilePath Path of the SQL script File
 * @throws IOException  IO Exception
 * @throws SQLException SQL Exception.
 */
public static DataSource createDataTables(String databaseName, String scriptFilePath) throws IOException,
        SQLException {
    JdbcDataSource dataSource = new JdbcDataSource();
    dataSource.setURL("jdbc:h2:mem:" + databaseName + ";DB_CLOSE_DELAY=-1");
    dataSource.setUser("sa");
    dataSource.setPassword("sa");

    File file = new File(scriptFilePath);

    final String LOAD_DATA_QUERY = "RUNSCRIPT FROM '" + file.getCanonicalPath() + "'";

    Connection connection = null;
    try {
        connection = dataSource.getConnection();
        Statement statement = connection.createStatement();
        statement.execute(LOAD_DATA_QUERY);
    } finally {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
            }
        }
    }
    return dataSource;
}
 

public static UserService getUserService() throws IOException {
	Properties prop = new Properties();
	prop.load(Main.class.getClassLoader().getResourceAsStream("jdbc.properties"));		
	
	JdbcDataSource dataSource = new JdbcDataSource();
       dataSource.setURL(prop.getProperty("cc.openhome.jdbcUrl"));
       dataSource.setUser(prop.getProperty("cc.openhome.user"));
       dataSource.setPassword(prop.getProperty("cc.openhome.password"));
       
       AccountDAO acctDAO = new AccountDAOJdbcImpl(dataSource);
       MessageDAO messageDAO = new MessageDAOJdbcImpl(dataSource);
       
       UserService userService = new UserService(acctDAO, messageDAO);
	return userService;
}
 

@Bean
public DataSource getDataSource() {
    JdbcDataSource dataSource = new JdbcDataSource();
    dataSource.setURL(jdbcUrl);
    dataSource.setUser(user);
    dataSource.setPassword(password);
    return dataSource;
}
 

private DataSource createDataSource(String jdbcUrl, String jdbcUsername, String jdbcPassword) {
  JdbcDataSource ds = new JdbcDataSource();
  ds.setURL(jdbcUrl);
  ds.setUser(jdbcUsername);
  ds.setPassword(jdbcPassword);
  return ds;
}
 

@Inject
public H2DaoProvider(DbResourceGroupConfig config)
{
    JdbcDataSource ds = new JdbcDataSource();
    ds.setURL(requireNonNull(config.getConfigDbUrl(), "resource-groups.config-db-url is null"));
    // TODO: this should use onDemand()
    this.dao = Jdbi.create(ds)
            .installPlugin(new SqlObjectPlugin())
            .open()
            .attach(H2ResourceGroupsDao.class);
}
 

public static DataSource getDataSource() {
  JdbcDataSource ds = new JdbcDataSource();
  ds.setURL(H2_URL);
  return ds;
}
 

@Test
public void testExecute_withRealDataSource() throws Exception {

	// Create a H2 data source
	JdbcDataSource ds = new JdbcDataSource();
	File dataFile = this.folder.newFile();
	ds.setURL( "jdbc:h2:" + dataFile.getAbsolutePath());
	ds.setUser( "roboconf" );
	ds.setPassword( "roboconf" );

	// Prepare the mock
	ServiceReference<DataSource> sr = Mockito.mock( ServiceReference.class );

	PruneHistoryCommand pruneHistoryCmd = new PruneHistoryCommand();
	pruneHistoryCmd.bundleContext = Mockito.mock( BundleContext.class );
	Mockito.when( pruneHistoryCmd.bundleContext.getServiceReferences(
			DataSource.class,
			"(dataSourceName=roboconf-dm-db)" )).thenReturn( Arrays.asList( sr ));

	Mockito.when( pruneHistoryCmd.bundleContext.getService( sr )).thenReturn( ds );

	// Populate the database
	Manager manager = Mockito.mock( Manager.class );
	Mockito.when( manager.getDataSource()).thenReturn( ds );
	CommandsMngrImpl cmdMngr = new CommandsMngrImpl( manager ) ;

	TestApplication app = new TestApplication();
	app.setDirectory( this.folder.newFolder());

	String cmdName = "my-command";
	String line = "rename /tomcat-vm as tomcat-vm-copy";
	cmdMngr.createOrUpdateCommand( app, cmdName, line );
	final int count = 5;
	for( int i=0; i<count; i++ ) {
		try {
			cmdMngr.execute( app, cmdName, CommandHistoryItem.ORIGIN_REST_API, "some source" );

		} catch( Exception e ) {
			// nothing
		}
	}

	List<CommandHistoryItem> historyItems = cmdMngr.getHistory( 0, 10, null, null, app.getName());
	Assert.assertEquals( count, historyItems.size());

	// Invalid argument? => Nothing is deleted.
	ByteArrayOutputStream os = new ByteArrayOutputStream();
	pruneHistoryCmd.out = new PrintStream( os, true, "UTF-8" );

	pruneHistoryCmd.daysToKeep = -10;
	pruneHistoryCmd.execute();
	historyItems = cmdMngr.getHistory( 0, 10, null, null, app.getName());
	Assert.assertEquals( count, historyItems.size());
	Assert.assertEquals( "[ WARNING ] The daysToKeep argument must be equal or greater than 0. Operation cancelled.", os.toString( "UTF-8" ).trim());

	// Remove entries older than two days. => Here, nothing will be deleted.
	os = new ByteArrayOutputStream();
	pruneHistoryCmd.out = new PrintStream( os, true, "UTF-8" );

	pruneHistoryCmd.daysToKeep = 2;
	pruneHistoryCmd.execute();
	historyItems = cmdMngr.getHistory( 0, 10, null, null, app.getName());
	Assert.assertEquals( count, historyItems.size());
	Assert.assertEquals( "Pruning the commands history.\nOnly the last 2 days will be kept.\nPruning done.", os.toString( "UTF-8" ).trim());

	// Remove entries older than two days. => Here, nothing will be deleted.
	os = new ByteArrayOutputStream();
	pruneHistoryCmd.out = new PrintStream( os, true, "UTF-8" );

	pruneHistoryCmd.daysToKeep = 1;
	pruneHistoryCmd.execute();
	historyItems = cmdMngr.getHistory( 0, 10, null, null, app.getName());
	Assert.assertEquals( count, historyItems.size());
	Assert.assertEquals( "Pruning the commands history.\nOnly the last 1 day will be kept.\nPruning done.", os.toString( "UTF-8" ).trim());

	// Remove all the entries
	os = new ByteArrayOutputStream();
	pruneHistoryCmd.out = new PrintStream( os, true, "UTF-8" );

	pruneHistoryCmd.daysToKeep = 0;
	pruneHistoryCmd.execute();
	historyItems = cmdMngr.getHistory( 0, 10, null, null, app.getName());
	Assert.assertEquals( 0, historyItems.size());
	Assert.assertEquals( "Pruning the commands history.\nAll the entries will be deleted.\nPruning done.", os.toString( "UTF-8" ).trim());
}
 

/**
 * Test database.
 *
 * @throws Exception when a serious error occurs.
 */
@Test
public void testJpaDatabase() throws Exception {
    System.getProperties().put("java.naming.factory.initial", "cloud.piranha.jndi.memory.DefaultInitialContextFactory");
    InitialContext initialContext = new InitialContext();
    JdbcDataSource dataSource = new JdbcDataSource();
    dataSource.setURL("jdbc:h2:./target/JPADatabaseTest2");
    initialContext.bind("java:/JPADatabaseTest2", dataSource);
    JdbcDataSource dataSource2 = new JdbcDataSource();
    dataSource2.setURL("jdbc:h2:./target/JPADatabaseTest3");
    initialContext.bind("java:/JPADatabaseTest3", dataSource2);
    DefaultTransactionManager transactionManager = new DefaultTransactionManager();
    initialContext.rebind("java:/TransactionManager", transactionManager);
    EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPADatabaseTest2");
    EntityManagerFactory emf2 = Persistence.createEntityManagerFactory("JPADatabaseTest3");
    transactionManager.begin();
    EntityManager em = emf.createEntityManager();
    JPATest jpaTest = new JPATest();
    jpaTest.setId(1);
    em.persist(jpaTest);
    em.flush();
    Transaction transaction = transactionManager.getTransaction();
    assertEquals(transaction.getStatus(), 0);
    EntityManager em2 = emf2.createEntityManager();
    JPATest jpaTest2 = new JPATest();
    jpaTest2.setId(2);
    em2.persist(jpaTest2);
    em2.flush();
    Transaction transaction2 = transactionManager.getTransaction();
    assertEquals(transaction2.getStatus(), 0);
    transactionManager.commit();
    
    assertEquals(transaction.getStatus(), 3);
    em = emf.createEntityManager();
    JPATest jpaTestb = em.find(JPATest.class, 1);
    assertNotNull(jpaTestb);
    assertEquals(jpaTest.getId(), jpaTestb.getId());
    assertEquals(transaction2.getStatus(), 3);
    em2 = emf2.createEntityManager();
    JPATest jpaTest2b = em2.find(JPATest.class, 2);
    assertNotNull(jpaTest2b);
    assertEquals(jpaTest2b.getId(), jpaTest2.getId());
    
    em.close();
    emf.close();
    em2.close();
    emf2.close();
}
 

@Test
public void testWithHistory_realDataSource() throws Exception {

	// Create a H2 data source
	JdbcDataSource ds = new JdbcDataSource();
	File dataFile = this.folder.newFile();
	ds.setURL( "jdbc:h2:" + dataFile.getAbsolutePath());
	ds.setUser( "roboconf" );
	ds.setPassword( "roboconf" );

	// Prepare the mock
	Mockito.when( this.manager.getDataSource()).thenReturn( ds );

	// Execute a command once
	String cmdName = "my-command";
	String line = "rename /tomcat-vm as tomcat-vm-copy";
	this.cmdMngr.createOrUpdateCommand( this.app, cmdName, line );

	Assert.assertEquals( 0, this.cmdMngr.getHistoryNumberOfPages( 10, null ));
	Assert.assertEquals( 0, this.cmdMngr.getHistory( 0, 10, null, null, null ).size());
	this.cmdMngr.execute( this.app, cmdName, CommandHistoryItem.ORIGIN_REST_API, "some source" );

	// Verify things were updated
	Assert.assertEquals( 1, this.cmdMngr.getHistoryNumberOfPages( 10, null ));
	Assert.assertEquals( 1, this.cmdMngr.getHistory( 0, 10, null, null, null ).size());

	// Execute it again
	final int repeatCount = 15;
	for( int i=0; i<repeatCount; i++ ) {
		try {
			this.cmdMngr.execute( this.app, cmdName, CommandHistoryItem.ORIGIN_SCHEDULER, "some source 2" );
			Assert.fail( "An error was expected, the instance to rename does not exist anymore." );

		} catch( CommandException e ) {
			// nothing
		}
	}

	Assert.assertEquals( 2, this.cmdMngr.getHistoryNumberOfPages( 10, null ));

	List<CommandHistoryItem> items = this.cmdMngr.getHistory( 0, 10, null, null, null );
	Assert.assertEquals( 10, items.size());

	items = this.cmdMngr.getHistory( 0, 20, null, null, null );
	Assert.assertEquals( repeatCount + 1, items.size());
	CommandHistoryItem item = items.get( 0 );

	Assert.assertEquals( this.app.getName(), item.getApplicationName());
	Assert.assertEquals( "my-command", item.getCommandName());
	Assert.assertEquals( "some source", item.getOriginDetails());
	Assert.assertEquals( CommandHistoryItem.ORIGIN_REST_API, item.getOrigin());
	Assert.assertEquals( CommandHistoryItem.EXECUTION_OK, item.getExecutionResult());
	Assert.assertTrue( item.getDuration() > 0L );
	Assert.assertTrue( item.getStart() > 0L );

	for( int i=0; i<repeatCount; i++ ) {
		item = items.get( i + 1 );

		Assert.assertEquals( this.app.getName(), item.getApplicationName());
		Assert.assertEquals( "my-command", item.getCommandName());
		Assert.assertEquals( "some source 2", item.getOriginDetails());
		Assert.assertEquals( CommandHistoryItem.ORIGIN_SCHEDULER, item.getOrigin());
		Assert.assertEquals( CommandHistoryItem.EXECUTION_ERROR, item.getExecutionResult());
		Assert.assertTrue( item.getDuration() > 0L );
		Assert.assertTrue( item.getStart() > 0L );
	}

	// Filter by application name
	Assert.assertEquals( 0, this.cmdMngr.getHistoryNumberOfPages( 10, "inexisting app" ));
	Assert.assertEquals( 0, this.cmdMngr.getHistory( 0, 10, null, null, "inexisting app" ).size());

	Assert.assertEquals( 2, this.cmdMngr.getHistoryNumberOfPages( 10, this.app.getName()));
	Assert.assertEquals( 1, this.cmdMngr.getHistoryNumberOfPages( -1, this.app.getName()));

	Assert.assertEquals( repeatCount + 1, this.cmdMngr.getHistory( 0, 20, "result", null, this.app.getName()).size());
	Assert.assertEquals( repeatCount + 1, this.cmdMngr.getHistory( -1, -1, "result", null, this.app.getName()).size());

	// Verify sorting: the first in history was the only successful one
	items = this.cmdMngr.getHistory( -1, -1, "start", "asc", this.app.getName());
	Assert.assertEquals( CommandHistoryItem.EXECUTION_OK, items.get( 0 ).getExecutionResult());
	Assert.assertEquals( "some source", items.get( 0 ).getOriginDetails());
	Assert.assertEquals( CommandHistoryItem.ORIGIN_REST_API, items.get( 0 ).getOrigin());

	items = this.cmdMngr.getHistory( -1, -1, null, "asc", this.app.getName());
	Assert.assertEquals( CommandHistoryItem.EXECUTION_OK, items.get( 0 ).getExecutionResult());
	Assert.assertEquals( "some source", items.get( 0 ).getOriginDetails());
	Assert.assertEquals( CommandHistoryItem.ORIGIN_REST_API, items.get( 0 ).getOrigin());

	items = this.cmdMngr.getHistory( -1, -1, "result", "desc", this.app.getName());
	Assert.assertEquals( CommandHistoryItem.EXECUTION_ERROR, items.get( 0 ).getExecutionResult());
	Assert.assertEquals( CommandHistoryItem.ORIGIN_SCHEDULER, items.get( 0 ).getOrigin());

	Assert.assertEquals( CommandHistoryItem.EXECUTION_OK, items.get( items.size() - 1 ).getExecutionResult());
	Assert.assertEquals( "some source", items.get( items.size() - 1 ).getOriginDetails());
	Assert.assertEquals( CommandHistoryItem.ORIGIN_REST_API, items.get( items.size() - 1 ).getOrigin());

	// Verify extra-pagination (start from item n° 20 and get at most 20 items)
	items = this.cmdMngr.getHistory( 20, 20, null, null, this.app.getName());
	Assert.assertEquals( 0, items.size());
}