Setup MICROSOFT SQL SERVER Data source in JBoss EAP 7.1

In this post I will demonstrate how to setup Microsoft SQL Server Data source in JBoss EAP 7.1. I will use code base of my previous post "Passing spring application parameters in EAP 7.1"(Initial code base)

Prerequisites 

1. You should have install java 1.8 or above.
2. You should have Eclipse installed in your PC.
3. Your PC should setup Maven installed and configured.
4. Your PC should have installed MySQL and Server need to have user who have access to the configured database and user should able to log in to the database by user name/password. 

Install MSSQL driver in JBoss EAP

First lets download a JDBC Driver based on SQLServer version. You can download the JDBC Driver from Microsoft's site: https://docs.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server

I downloaded the "sqljdbc4-2.0.jar" which compatible with my SQL server version. Lets keep the downloaded jar in temporary folder. In my case I'll keep it on "G:/" drive. 

So lets use "JBoss-cli" in order to add the Driver and JNDI in to EAP. You can find the jboss-cli in <JBOSS_HOM>/bin directory.

You can start the EAP first by double click on standalone.bat and then start jboss-cli by double click jboss-cli.jar.

Then you can enter connect command in order to connect to EAP.

connect

Then you can enter following command

module add --name=sqlserver.jdbc --resources=@INSTALL_FOLDER@/libext/jtds-1.3.1.jar --dependencies=javax.api,javax.transaction.api
/subsystem=datasources/jdbc-driver=sqlserver:add(driver-module-name=sqlserver.jdbc,driver-name=sqlserver,driver-class-name=@JDBC_DRIVER@)
/subsystem=datasources/data-source=@DATASOURCENAME@:add(jndi-name=java:jboss/@JNDI_NAME@,enabled="true",use-java-context="true",driver-name=sqlserver,connection-url="@JDBC_URL@",user-name=@JDBC_USER@,password=@JDBC_PASSWORD@,validate-on-match=true,background-validation=true)

This is stranded command which you can replace with your parameters. So based on my parameter details Command would be something like below. I have highlighted the modifications which I have done.


module add --name=sqlserver.jdbc --resources=G:\sqljdbc4-2.0.jar --dependencies=javax.api,javax.transaction.api
/subsystem=datasources/jdbc-driver=sqlserver:add(driver-module-name=sqlserver.jdbc,driver-name=sqlserver,driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver)
/subsystem=datasources/data-source=MSSQLDATASOURCE:add(jndi-name=java:jboss/MSSQLJNDI,enabled="true",use-java-context="true",driver-name=sqlserver,connection-url="jdbc:sqlserver://localhost;databaseName=TestDB",user-name=nirmal,password=Test123_,validate-on-match=true,background-validation=true)

Once you run the command you should be able to see following output in jboss-cli.

Then also you can see following section has been added in to the standalone.xml

Lets configure Application to read JNDI

In order to read the DataSource details from JNDI just need to add JNDI string in to Spring Boot application.properties file. I have added spring.datasource.jndi-name=java:jboss/MSSQLJNDI@ application.properties file. (Note that I have commented previous datasource configuration details )

#==== connect to MSSQL ======#
#spring.jpa.hibernate.ddl-auto=update
#spring.datasource.url=jdbc:sqlserver://localhost;databaseName=TestDB
##spring.datasource.username=nirmal
##spring.datasource.password=Test123_
#spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
#spring.jpa.database-platform=org.hibernate.dialect.SQLServer2012Dialect
server.port = 8080
spring.datasource.jndi-name=java:jboss/MSSQLJNDI@

spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.use_sql_comments=false
spring.jpa.properties.hibernate.format_sql=true

#==== Logging configurations ======#
logging.level.root=WARN,INFO,ERROR
logging.level.com.baeldung=TRACE

Then lets deploy the war file. For that click on Back button on left top corner and then click on deployments. Then click "Add" button. Then you should be able to see similar figure to below.

Then click next button and then Click choose file and select the generated war file from target folder in the project. Then click next and then click finish. Once installation completed you should be able to see similar output on command line.

http://localhost:8080/RestApiSpringBoot-0.0.1-SNAPSHOT/findBook/1

Also you can perform any action which I mentioned in my previous blog post.

Project source can be downloaded from following GIT HUB URL.