org.h2.jdbcx.JdbcDataSource Java Examples

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

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

}
 

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

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

@BeforeAll
public void before() {
    JdbcDataSource ds = new JdbcDataSource();
    ds.setURL("jdbc:h2:mem:test1;DB_CLOSE_DELAY=-1;MODE=PostgreSQL");
    DBI dbi = new DBI(ds);

    this.jsondb = new SqlJsonDB(dbi, null,
        Arrays.asList(
            new Index("/pair", "key"),
            new Index("/users", "name"),
            new Index("/users", "age")
        )
    );

    try {
        this.jsondb.dropTables();
    } catch (Exception e) {
    }
    this.jsondb.createTables();
}
 

private void createSimpleDataSource()
{
    String url = DatabaseConfig.buildJdbcUrl(config);

    // By default, H2 closes database when all of the connections are closed. When database
    // is closed, data is gone with in-memory mode. It's unexpected. However, if here disables
    // that such behavior using DB_CLOSE_DELAY=-1 option, there're no methods to close the
    // database explicitly. Only way to close is to not disable shutdown hook of H2 database
    // (DB_CLOSE_ON_EXIT=TRUE). But this also causes unexpected behavior when PreDestroy is
    // triggered in a shutdown hook. Therefore, here needs to rely on injector to take care of
    // dependencies so that the database is closed after calling all other PreDestroy methods
    // that depend on this DataSourceProvider.
    // To solve this issue, here holds one Connection until PreDestroy.
    JdbcDataSource ds = new JdbcDataSource();
    ds.setUrl(url + ";DB_CLOSE_ON_EXIT=FALSE");

    logger.debug("Using database URL {}", url);

    try {
        this.closer = ds.getConnection();
    }
    catch (SQLException ex) {
        throw Throwables.propagate(ex);
    }
    this.ds = ds;
}
 

public static void main(String[] args) 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);
    
    userService.messages("caterpillar")
               .forEach(message -> {
                   System.out.printf("%s\t%s%n",
                       message.getLocalDateTime(),
                       message.getBlabla());
               });

}
 

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

public static JdbcDataSource getJdbcDataSource(String initScriptLocation) {
    Validate.notEmpty(initScriptLocation, "initScriptLocation is empty");

    String mavenRelativePath = "src/main/resources/" + initScriptLocation;
    String mavenRootRelativePath = "camel-cookbook-transactions/" + mavenRelativePath;

    // check that we can load the init script
    FileLocator locator = new FileLocator().with(initScriptLocation).with(mavenRelativePath).with(mavenRootRelativePath);
    File file = locator.find();
    Validate.notNull(file, locator.getErrorMessage());
    FileSystemResource script = new FileSystemResource(file);

    JdbcDataSource dataSource = new JdbcDataSource();
    dataSource.setURL("jdbc:h2:mem:db1;DB_CLOSE_DELAY=-1");
    dataSource.setUser("sa");
    dataSource.setPassword("");

    DataSourceInitializer.initializeDataSource(dataSource, script);

    return dataSource;
}
 

private static DataSource initDataSource() {
    JdbcDataSource dataSource = new JdbcDataSource();
    String dbUrl = DatabaseCreator.nextUrl();
    dataSource.setURL(dbUrl);

    String jndiName = "jdbc/RxDS";

    try {
        Context context = new InitialContext();
        context.rebind(jndiName, dataSource);
    } catch (NamingException e) {
        throw new RuntimeException(e);
    }

    return dataSource;
}
 

@Override
public DataSource createDataSource(Path path, Properties properties) {
    String url = properties.getProperty(H2Configs.DATASOURCE_URL);
    if (StringUtils.isBlank(url)) {
        url = String.format("jdbc:h2:file:%s/data;AUTO_SERVER=TRUE;MVCC=TRUE;LOCK_TIMEOUT=30000", path.toString());
    }
    JdbcDataSource datasource = new JdbcDataSource();
    datasource.setURL(url);
    return datasource;
}
 

public void setup() throws SQLException {
    h2DataSource = new JdbcDataSource();
    h2DataSource.setURL("jdbc:h2:mem:db1;DB_CLOSE_DELAY=-1");

    try (Connection conn = h2DataSource.getConnection()) {
        conn.createStatement().execute("create table PERSONS (ID int, NAME varchar);");
    }
}
 

@Test
public void testSetXaDataSourceInstance() throws SQLException, XAException {
    try (final BasicManagedDataSource basicManagedDataSource = new BasicManagedDataSource()) {
        basicManagedDataSource.setTransactionManager(new TransactionManagerImpl());
        basicManagedDataSource.setDriverClassName("org.apache.commons.dbcp2.TesterDriver");
        basicManagedDataSource.setUrl("jdbc:apache:commons:testdriver");
        basicManagedDataSource.setUsername("userName");
        basicManagedDataSource.setPassword("password");
        basicManagedDataSource.setMaxIdle(1);
        basicManagedDataSource.setXaDataSourceInstance(new JdbcDataSource());
        assertNotNull(basicManagedDataSource.createConnectionFactory());
    }
}
 

@Test
public void testCreateXaDataSourceNewInstance() throws SQLException, XAException {
    try (final BasicManagedDataSource basicManagedDataSource = new BasicManagedDataSource()) {
        basicManagedDataSource.setXADataSource(JdbcDataSource.class.getCanonicalName());
        basicManagedDataSource.setDriverClassName(Driver.class.getName());
        basicManagedDataSource.setTransactionManager(new TransactionManagerImpl());
        assertNotNull(basicManagedDataSource.createConnectionFactory());
    }
}
 

@Test
public void testXaDataSourceInstance() throws SQLException {
    try (final BasicManagedDataSource basicManagedDataSource = new BasicManagedDataSource()) {
        final XADataSource ds = new JdbcDataSource();
        basicManagedDataSource.setXaDataSourceInstance(ds);
        assertEquals(ds, basicManagedDataSource.getXaDataSourceInstance());
    }
}
 

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

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

@Test
public void testSimple() throws SQLException {
	// Prepare
	SimonDataSource simonDataSource = new SimonDataSource();
	simonDataSource.setUrl("jdbc:h2:mem:SimonDataSourceTest");
	simonDataSource.setRealDataSourceClassName(JdbcDataSource.class.getName());
	simonDataSource.setUser("sa");
	// Act
	Connection connection = simonDataSource.getConnection();
	// Verify
	assertEquals(connection.getMetaData().getDatabaseProductName(), "H2");
	assertEquals(connection.getMetaData().getUserName(), "SA");
}
 

private DataSource createDataSource(Config config) throws ClassNotFoundException {

        String url = config.getParameter(ExtConfig.JDBC_URL);
        String username = config.getParameter(ExtConfig.JDBC_USERNAME);
        String password = config.getParameter(ExtConfig.JDBC_PASSWORD);

        JdbcDataSource dataSource = new JdbcDataSource();
        dataSource.setUrl(url);
        dataSource.setUser(username);
        dataSource.setPassword(password);

        return dataSource;
    }
 

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

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

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

/**
 * @throws Exception If failed.
 */
@Test
public void testCacheConfiguration() throws Exception {
    try (Ignite ignite = Ignition.start("modules/spring/src/test/config/node.xml")) {
        try (Ignite ignite1 = Ignition.start("modules/spring/src/test/config/node1.xml")) {
            try (IgniteCache<Integer, String> cache = ignite.getOrCreateCache(cacheConfiguration())) {
                try (IgniteCache<Integer, String> cache1 = ignite1.getOrCreateCache(cacheConfiguration())) {
                    checkStore(cache, JdbcDataSource.class);

                    checkStore(cache1, DummyDataSource.class);
                }
            }
        }
    }
}
 

/**
 * @throws Exception If failed.
 */
@Test
public void testXmlConfiguration() throws Exception {
    try (Ignite ignite = Ignition.start("modules/spring/src/test/config/store-cache.xml")) {
        try (Ignite ignite1 = Ignition.start("modules/spring/src/test/config/store-cache1.xml")) {
            checkStore(ignite.<Integer, String>cache(CACHE_NAME), JdbcDataSource.class);

            checkStore(ignite1.<Integer, String>cache(CACHE_NAME), DummyDataSource.class);
        }
    }
}
 

/**
 * @throws Exception If failed.
 */
@Test
public void testSerializable() throws Exception {
    try (Ignite ignite = Ignition.start("modules/spring/src/test/config/node.xml")) {
        try (IgniteCache<Integer, String> cache = ignite.getOrCreateCache(cacheConfigurationH2Dialect())) {
            checkStore(cache, JdbcDataSource.class);
        }
    }
}
 

/**
 * @throws Exception If failed.
 */
@Test
public void testCacheConfiguration() throws Exception {
    try (Ignite ignite = Ignition.start("modules/spring/src/test/config/node.xml")) {
        try (Ignite ignite1 = Ignition.start("modules/spring/src/test/config/node1.xml")) {
            try (IgniteCache<Integer, String> cache = ignite.getOrCreateCache(cacheConfiguration())) {
                try (IgniteCache<Integer, String> cache1 = ignite1.getOrCreateCache(cacheConfiguration())) {
                    checkStore(cache, JdbcDataSource.class);

                    checkStore(cache1, CacheJdbcBlobStoreFactorySelfTest.DummyDataSource.class);
                }
            }
        }
    }
}
 

@Override
public void start() {
  final JdbcDataSource dataSource = new JdbcDataSource();
  dataSource.setUrl(getUrl() + ";DB_CLOSE_DELAY=-1");
  try (Connection conn = dataSource.getConnection()) {
    RunScript.execute(conn, new StringReader("SELECT 1"));
  } catch (SQLException x) {
    throw new RuntimeException(x);
  }
}
 

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

@Test
@Ignore("DataSource#setLogWriter() has no effect in H2, it uses its own internal logging and an SLF4J bridge.")
public void testDataSource_setLogWriter() throws SQLException {
    final JdbcDataSource dataSource = new JdbcDataSource();
    dataSource.setUrl(H2_URL);
    dataSource.setUser(USER_ID);
    dataSource.setPassword(PASSWORD);
    dataSource.setLogWriter(createLoggerPrintWriter());
    // dataSource.setLogWriter(new PrintWriter(new OutputStreamWriter(System.out)));
    try (final Connection conn = dataSource.getConnection()) {
        conn.prepareCall("select 1");
    }
    Assert.assertTrue(this.getListAppender().getMessages().size() > 0);
}