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

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

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

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

}
 

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

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

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

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

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 Connection setupDataSource(File file) throws SQLException, IOException {
	JdbcDataSource dataSource = new JdbcDataSource();
	dataSource.setUrl("jdbc:h2:mem:test");
	String script = FileUtils.readAsString(file);
	Connection connection = dataSource.getConnection();
	SqlUtils.executeSql(connection, script, ";");
	return connection;

}
 

public static CloseableJsonDB create(Collection<Index> indexes) {
    JdbcDataSource ds = new JdbcDataSource();
    DBI dbi = new DBI(ds);
    ds.setURL("jdbc:h2:mem:"+ KeyGenerator.createKey()+";MODE=PostgreSQL");
    try {
        Connection keepsDBOpen = ds.getConnection();
        ClosableSqlJsonDB result = new ClosableSqlJsonDB(keepsDBOpen, dbi, indexes);
        result.createTables();
        return result;
    } catch (SQLException e) {
        throw new JsonDBException(e);
    }
}
 

private Connection setupEmptySource() throws SQLException {
	JdbcDataSource dataSource = new JdbcDataSource();
	dataSource.setUrl("jdbc:h2:mem:empty");
	return dataSource.getConnection();
}
 

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

}