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

public 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;
}
 

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;
}
 

/**
 * To create certificate management database.
 *
 * @return Datasource.
 * @throws SQLException SQL Exception.
 */
private DataSource createDatabase() throws SQLException {
    URL resourceURL = ClassLoader.getSystemResource("sql-scripts" + File.separator + "h2.sql");
    JdbcDataSource dataSource = new JdbcDataSource();
    dataSource.setURL("jdbc:h2:mem:cert;DB_CLOSE_DELAY=-1");
    dataSource.setUser("sa");
    dataSource.setPassword("sa");
    final String LOAD_DATA_QUERY = "RUNSCRIPT FROM '" + resourceURL.getPath() + "'";
    Connection conn = null;
    Statement statement = null;
    try {
        conn = dataSource.getConnection();
        statement = conn.createStatement();
        statement.execute(LOAD_DATA_QUERY);
    } finally {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {}
        }
        if (statement != null) {
            statement.close();
        }
    }
    return dataSource;
}
 

@Bean
public DataSource dataSource()
{
    try
    {
        // When running inside a container, use its resource directory.
        return (DataSource) new JndiTemplate().lookup("jdbc/spring_ds");
    }
    catch (NamingException e)
    {
        // When running on the command line, just create a temporary file DB (only needed for debug).
        System.out.println("JNDI datasource does not exist - falling back on hard coded DS");
        JdbcDataSource ds = new JdbcDataSource();
        ds.setURL("jdbc:h2:./target/TEST.db");
        ds.setUser("sa");
        ds.setPassword("sa");
        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;
}
 

@Before
public void createAndBind() throws Exception {
	JdbcDataSource dataSource = new JdbcDataSource();
    dataSource.setURL("jdbc:h2:mem:test");
    dataSource.setUser("sa");
    dataSource.setPassword("sa");
    connection = dataSource.getConnection();

    String createStatement = "CREATE TABLE CUSTOMER("
    	+ "SSN VARCHAR(11) PRIMARY KEY,"
    	+ "FIRSTNAME VARCHAR(50),"
    	+ "LASTNAME VARCHAR(50),"
    	+ "STREETADDRESS VARCHAR(255),"
    	+ "CITY VARCHAR(60),"
    	+ "STATE VARCHAR(2),"
    	+ "POSTALCODE VARCHAR(60),"
		+ "DOB DATE,"
    	+ "CHECKINGBALANCE DECIMAL(14,2),"
    	+ "SAVINGSBALANCE DECIMAL(14,2));";
    
    String insertCustomer = "INSERT INTO CUSTOMER VALUES "
    		+ "('755-55-5555', 'Joseph', 'Smith', '123 Street', 'Elm', 'NC', '27808', '1970-01-01', 14000.40, 22000.99);";

    connection.createStatement().executeUpdate("DROP TABLE IF EXISTS CUSTOMER");
    connection.createStatement().executeUpdate(createStatement);
    connection.createStatement().executeUpdate(insertCustomer);
    

    namingMixIn = new NamingMixIn();
    namingMixIn.initialize();
    bindDataSource(namingMixIn.getInitialContext(), "java:jboss/datasources/CustomerDS", dataSource);
}
 

private static DataSource getDataSource() {
	JdbcDataSource dataSource = new JdbcDataSource();
	dataSource.setURL(JDBC_URL);
	dataSource.setUser(USER);
	dataSource.setPassword(PASSWORD);
	return dataSource;
}
 

@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;
}
 

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();
}
 

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

public MessageDB() 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:9093/mem:rbp
    // Server.createTcpServer("-tcpPort", "9093", "-tcpAllowOthers").start();
}
 

public BrandingDB() throws SQLException {
    JdbcDataSource ds = new JdbcDataSource();
    ds.setURL("jdbc:h2:mem:rbp");
    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:9092/mem:rbp
    // Server.createTcpServer("-tcpPort", "9092", "-tcpAllowOthers").start();
}
 

public RoomDB() 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:9091/mem:rbp
    // Server server = Server.createTcpServer("-tcpPort", "9091", "-tcpAllowOthers").start();
}
 

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();
}
 

/**
 * 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;
}
 

private static DataSource dataSource() {
  JdbcDataSource jdbcDataSource = new JdbcDataSource();
  jdbcDataSource.setUrl("jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE");
  jdbcDataSource.setUser("sa");
  jdbcDataSource.setPassword("");
  return jdbcDataSource;
}
 

@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());
}
 

@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());
}
 

@Before
public void initDB() throws Exception {

	Class.forName("org.h2.Driver");
	
	int logMode = 1; //1-err,2-info,3-debug
	String url = "jdbc:h2:mem:db-"+randomSimpleId() + ";DB_CLOSE_DELAY=-1;TRACE_LEVEL_SYSTEM_OUT="+logMode;

	JdbcDataSource ds = new JdbcDataSource();
	ds.setUrl(url);
	ds.setUser("sa");
	ds.setPassword("sa");
	this.ds = ds;
	
	//create db
	try (Connection conn = ds.getConnection()){
		
		Statement st = conn.createStatement();
		st.execute("CREATE TABLE user (id INT, name VARCHAR(50)); " 
		+" CREATE SEQUENCE user_seq START WITH 1 INCREMENT BY 1;");
		st.close();
		
	}
	
	async = ExecutorsUtil.newSingleThreadExecutor("async");
	

}