Creating a Datasource on JBoss-WildFly using a batch script

Installing a Datasource on JBoss AS 7 and WildFly as module can be tedious as it requires three commands to complete. However by creating a batch script you can easily automate the process and install it on every distribution with as little as a shell command. Here is a sample script to create a Datasource to be used for a MySQL database:

connect 127.0.0.1

batch

module add --name=com.mysql --resources=/home/jboss/Downloads/mysql-connector-java-5.1.24-bin.jar --dependencies=javax.api,javax.transaction.api
 
/subsystem=datasources/jdbc-driver=mysql:add(driver-name=mysql,driver-module-name=com.mysql)

data-source add --jndi-name=java:/jboss/MySQLDS --name=MySQLPool --connection-url=jdbc:mysql://localhost:3306/as7development --driver-name=mysql --user-name=jboss --password=jboss

run-batch

As you can see, a batch script needs just a batch command to start the batch process and the run-batch command to execute the list of commands. the only thing you have to customize are the Datasource settings and the location where your JDBC Driver is to be found (We have assumed it’s on /home/jboss/Downloads)

Save the above script in a file, let’s say dbscrpit.cli and execute it with:

$ ./jboss-cli.sh --file=dbscript.cli

Troubleshooting:

if you fail to activate the Datasource due to the following error:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Access denied for user 'jboss'@'localhost' to database 'as7development'
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) [rt.jar:1.7.0_67]
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) [rt.jar:1.7.0_67]
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) [rt.jar:1.7.0_67]
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526) [rt.jar:1.7.0_67]
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)

It’s a problem with your user settings. You should check out that your user has been granted privileges to connect to the specified host.

Make sure you have granted privileges to your user, for example:

CREATE USER 'jboss'@'localhost' IDENTIFIED BY 'jboss';

GRANT ALL PRIVILEGES ON *.* to 'jboss'@'localhost' with GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)