1. 2015-10-29 - Excecute SQL on Connection with H2; Tags: Excecute SQL on Connection with H2
    Loading...

    Excecute SQL on Connection with H2

    The H2 database allows to excecute SQL statement(s) within the connection. This comes handy if you are using the H2 database to test your Java persistence queries.

    String url = "jdbc:h2:mem:test;INIT=runscript from '~/create.sql'\\;runscript from '~/init.sql'";
    

    Please note the double backslash is only required in a Java or properties file. In a GUI, or in an XML file, only one backslash is required:

    <property name="url" value="jdbc:h2:mem:test;INIT=create schema if not exists test\;runscript from '~/sql/init.sql'"/>
    

    One undocumented feature is that you can also take the classpath for the file location. For instance you have in your Maven project in src/test/resources:

    h2.properties
    init.sql
    

    h2.properties contain the JDBC settings

    user=admin
    password=4321
    driver=org.h2.Driver
    url=jdbc:h2:~/demo;DB_CLOSE_DELAY=-1;AUTO_SERVER=TRUE;
    testurl=jdbc:h2:~/test;INIT=RUNSCRIPT FROM 'classpath:init.sql';DB_CLOSE_DELAY=-1;AUTO_SERVER=TRUE;
    

    In the testurl property, the init.sql is taken from the classpath. This can result in following example code:

    static final Connection connect(final String fileName, final boolean test) throws ClassNotFoundException, SQLException, IOException {
        Properties settings = new Properties();
        //establish database connection
        settings.load(new FileInputStream(fileName));
        Class.forName(settings.getProperty("driver"));
        String url = test ? settings.getProperty("testurl") : settings.getProperty("url");
        Connection conn = DriverManager.getConnection(url, settings.getProperty("user"), settings.getProperty("password"));
        return conn;
    }
    
  2. 2015-10-25 - Create datasource for H2 in JBoss; Tags: Create datasource for H2 in JBoss
    Loading...

    Create datasource for H2 in JBoss

    This post demonstrates how to create a datasource for the H2 database in JBoss. This recipe can easily applied to any other database like Oracle DB, MySQL and PostgreSQL.

    First, check if the h2 database driver is available:

    [standalone@localhost:9999 /] /subsystem=datasources:installed-drivers-list
    {
        "outcome" => "success",
        "result" => [{
            "driver-name" => "h2",
            "deployment-name" => undefined,
            "driver-module-name" => "com.h2database.h2",
            "module-slot" => "main",
            "driver-datasource-class-name" => "",
            "driver-xa-datasource-class-name" => "org.h2.jdbcx.JdbcDataSource",
            "driver-class-name" => "org.h2.Driver",
            "driver-major-version" => 1,
            "driver-minor-version" => 3,
            "jdbc-compliant" => true
        }]
    }
    

    We add a datasource by this template

    data-source add \
       --name=DATASOURCE_NAME \
       --jndi-name=JNDI_NAME \
       --driver-name=DRIVER_NAME \
       --connection-url=CONNECTION_URL
    

    Use H2 as file and add custom settings within the connection-url. After the add, don’t forget to enable the new datasource.

    #      _       _
    #   __| | __ _| |_ __ _ ___  ___  _   _ _ __ ___ ___
    #  / _` |/ _` | __/ _` / __|/ _ \| | | | '__/ __/ _ \
    # | (_| | (_| | || (_| \__ \ (_) | |_| | | | (_|  __/
    #  \__,_|\__,_|\__\__,_|___/\___/ \__,_|_|  \___\___|
    #
    data-source add \
        --name=demo \
        --driver-name=h2 \
        --connection-url="jdbc:h2:demo;INIT=RUNSCRIPT FROM '~/tables.sql';DB_CLOSE_DELAY=-1;AUTO_SERVER=TRUE" \
        --jndi-name=java:jboss/datasources/demo \
        --user-name=admin \
        --password=geheim \
        --check-valid-connection-sql="SELECT 1"
    data-source enable --name=demo
    

    This will result in console.log

    16:48:49,991 INFO  [org.jboss.as.connector.subsystems.datasources] (MSC service thread 1-7) JBAS010400: Bound data source [java:jboss/datasources/demo]
    

    This will generate this section in the standalone.xml.

    <datasource jndi-name="java:jboss/datasources/demo" pool-name="demo" enabled="true">
    	<connection-url>jdbc:h2:demo;INIT=RUNSCRIPT FROM '~/tables.sql';DB_CLOSE_DELAY=-1;AUTO_SERVER=TRUE</connection-url>
    	<driver>h2</driver>
    	<security>
    		<user-name>admin</user-name>
    <password>geheim</password>
    	</security>
    	<validation>
    		<check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
    	</validation>
    </datasource>
    

    check-valid-connection-sql is important! You provide the SQL statement used to validate the connection. The following is an example of how you might specify a SQL statement to validate a connection for Oracle:

    <check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>
    

    For MySQL or PostgreSQL, you might specify the following SQL statement. This also works for the H2 database.

    <check-valid-connection-sql>select 1</check-valid-connection-sql>