How to check existing h2 database before creating a new one

I'm developing an student database web application using java servlets and h2 embedded database. I need to know how to find whether the typical student database exists or not. so that, if it is not there, it must be created or else the program should continue with the existing one.

Solution 1:

tl;dr

Add IFEXISTS=TRUE to your connection URL string.

H2 creates database, if absent

You asked:

I need to know how to find whether the typical student database exists or not. so that, if it is not there, it must be created or else the program should continue with the existing one.

By default, H2 automatically creates the database if not already in existence. So that part of your Question does not make sense.

Specify a file location as part of establishing a DataSource object. Here we use org.h2.jdbcx.JdbcDataSource as our implementation of javax.sql.DataSource.

private javax.sql.DataSource establishDataSource() {
    org.h2.jdbcx.JdbcDataSource ds = Objects.requireNonNull( new JdbcDataSource() );  // Implementation of `DataSource` bundled with H2. You may choose to use some other implementation. 
    ds.setURL( "jdbc:h2:/path/to/MyDatabase;" );
    ds.setUser( "scott" );
    ds.setPassword( "tiger" );
    ds.setDescription( "An example database." );
    return ds ;
}

Instantiate a DataSource object. Keep it around for use when you need access to the database.

DataSource dataSource = this.establishDataSource();
…

The DataSource object does not cause anything to happen. A DataSource object merely holds the pieces of information needed to locate and connect to a particular database.

Attempt to connect to database. This is when things start to happen.

try(
    Connection conn = dataSource.getConnection() ;
)
{ … }

The first call to DataSource#getConnection causes H2 to:

1. Establish a new database file at the specified location in the file system, if not already existing.
2. Open a connection to the database.

But to answer the title of your Question, read on.

IFEXISTS

You can specify in your connection attempt that a connection should only be completed if the requested database already exists. This feature uses IFEXISTS=TRUE syntax.

String url = "jdbc:h2:/path/to/MyDatabase;IFEXISTS=TRUE";

This causes H2 to:

• Look for existing database at given location.
  • If no existing database, throw exception.
  • If existing database found, open a connection.

Trap for the exception thrown when the database does not exist.

File

You check for the existence of the database file in the file system, assuming your database is persisted to storage (as opposed to in-memory database).

Solution 2:

package com.dora.databasecheck;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.SimpleDriverDataSource;
import org.springframework.jdbc.datasource.init.DatabasePopulator;
import org.springframework.jdbc.datasource.init.DatabasePopulatorUtils;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;

@Configuration
public class DataSourceInit {

    boolean dbExists = false;

    @Bean(name = "dataSource")
    public DataSource getDataSource(){
        DataSource dataSource = createDataSource();
        DatabasePopulatorUtils.execute(createDatabasePopulator(), dataSource);
        return dataSource;
    }

    private DatabasePopulator createDatabasePopulator() {
        ResourceDatabasePopulator databasePopulator = new ResourceDatabasePopulator();
        databasePopulator.setContinueOnError(true);
        if(!dbExists){
            databasePopulator.addScript(new ClassPathResource("db/sql/create-db.sql"));
            databasePopulator.addScript(new ClassPathResource("db/sql/insert-data.sql"));
        }
        return databasePopulator;
    }

    private SimpleDriverDataSource createDataSource() {
         SimpleDriverDataSource simpleDriverDataSource = new SimpleDriverDataSource();
         simpleDriverDataSource.setDriverClass(org.h2.Driver.class);
         simpleDriverDataSource.setUsername("sa");
         simpleDriverDataSource.setPassword("");
         Connection conn = null;
        try{
            conn = DriverManager.getConnection("jdbc:h2:~/doradb;IFEXISTS=TRUE","sa","");           
            this.dbExists =true;

         }
        catch(Exception e){         
             this.dbExists = false;  
        }

         simpleDriverDataSource.setUrl("jdbc:h2:~/doradb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE");

        return simpleDriverDataSource;      
    }
}