Skip to content

Latest commit

 

History

History
229 lines (174 loc) · 7.28 KB

README.md

File metadata and controls

229 lines (174 loc) · 7.28 KB
Raw

03C - Bind the application to a SQL database

This guide is part of the migrate Java EE app to Azure training

Bind the application to the petstore database in Azure SQL Database.

Configure SQL Database Data Source

There are 4 steps to configure a data source. These steps are similar to configuring data sources in any on premise Java EE app servers:

Step 1: Understand How to configure JBoss EAP

In App Service, each instance of an app server is stateless. Therefore, each instance must be configured on startup to support a JBoss EAP configuration needed by your application. You can configure at startup by supplying a startup Bash script that calls JBoss/WildFly CLI commands to setup data sources, messaging providers and any other dependencies. We will create a startup.sh script and place it in the /home directory of the Web app. The script will:

Install a JBoss EAP module:

# where resources point to JDBC driver for SQL Database
# and module xml points to module description, see below

module add --name=com.microsoft --resources=/home/site/libs/mssql-jdbc-7.2.1.jre8.jar --module-xml=/home/site/scripts/mssql-module.xml

Where mssql-module.xml describes the module:

<?xml version="1.0" ?>
<module xmlns="urn:jboss:module:1.1" name="com.microsoft">
  <resources>
	<resource-root path="/home/site/libs/mssql-jdbc-7.2.1.jre8.jar"/>
  </resources>
  <dependencies>
    <module name="javax.api"/>
    <module name="javax.transaction.api"/>
  </dependencies>
</module>

Add a JDBC driver for SQL Database:

/subsystem=datasources/jdbc-driver=sqlserver:add(driver-name="sqlserver",driver-module-name="com.microsoft",driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver,driver-datasource-class-name=com.microsoft.sqlserver.jdbc.SQLServerDataSource)

Install a data source by using the data-source shortcut command:

data-source add --name=sqlDS --jndi-name=java:jboss/datasources/sqlDS --driver-name=sqlserver --connection-url=${SQL_CONNECTION_URL,env.SQL_CONNECTION_URL:example} --validate-on-match=true --background-validation=false --valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker --exception-sorter-class-name=org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLExceptionSorter

A server reload may be required for the changes to take effect:

reload --use-current-server-config=true

These JBoss CLI commands, JDBC driver for SQL Database and module XML are available in initial-sql/agoncal-application-petstore-ee7/.scripts

Also, you can directly download JDBC driver for SQL Database. For example:

Step 2: Deploy multiple artifacts to App Service linux

Open pom.xml and update the deployment with the following configuration and run mvn azure-webapp:deploy to deploy.

<deployment>
  <resources>
    <resource>
      <type>war</type>
      <directory>${project.basedir}/target</directory>
      <includes>
        <include>*.war</include>
      </includes>
    </resource>
    <resource>
      <type>lib</type>
      <directory>${project.basedir}/.scripts/3C-sql</directory>
      <includes>
        <include>*.jar</include>
      </includes>
    </resource>
    <resource>
      <type>startup</type>
      <directory>${project.basedir}/.scripts/3C-sql</directory>
      <includes>
        <include>*.sh</include>
      </includes>
    </resource>
    <resource>
      <type>script</type>
      <directory>${project.basedir}/.scripts/3C-sql</directory>
      <includes>
        <include>*.cli</include>
        <include>*.xml</include>
      </includes>
    </resource>
  </resources>
</deployment>

Step 3: Set SQL Database connection info in the Web app environment

Use Azure CLI to set database connection info:

az webapp config appsettings set  \
    --resource-group ${RESOURCE_GROUP} \
    --name ${WEBAPP}  \
    --settings  SQL_CONNECTION_URL=${SQL_CONNECTION_URL}
[
  {
    "name": "WEBSITE_HTTPLOGGING_RETENTION_DAYS",
    "slotSetting": false,
    "value": "3"
  },
  {
    "name": "SQL_CONNECTION_URL",
    "slotSetting": false,
    "value": "======= MASKED ======="
  }
]

Step 4: Restart the remote JBoss EAP app server

Use Azure CLI to restart the remote JBoss EAP app server:

az webapp stop -g ${RESOURCE_GROUP} -n ${WEBAPP}
az webapp start -g ${RESOURCE_GROUP} -n ${WEBAPP}

For additional info, please refer to:

Build PetStore to interact with Azure SQL Database

# Use the Maven profile for Azure SQL Database to build from the project base directory
cd ../..
mvn package -Dmaven.test.skip=true -Ddb=sql

Note - the sql Maven profile is available here.

Deploy to App Service Linux

Deploy to JBoss EAP in App Service Linux:

mvn azure-webapp:deploy

Open Pet Store running on App Service Linux and interacting with Azure SQL Database

open https://${WEBAPP}.azurewebsites.net

Log into Azure SQL Database and Validate Tables were Created and Populated

# Show tables in SQL Database
sqlcmd -S ${SQL_SERVER_FULL_NAME} \
    -d ${SQL_DATABASE_NAME} \
    -U ${SQL_SERVER_ADMIN_FULL_NAME} \
    -P ${SQL_SERVER_ADMIN_PASSWORD} \
    -Q "SELECT name, id, crdate FROM SYSOBJECTS WHERE xtype = 'U'"

--------------------- ----------- -----------------------
Category                 18099105 2019-05-31 14:41:52.280
Country                  50099219 2019-05-31 14:41:52.320
Customer                 82099333 2019-05-31 14:41:52.330
Item                    114099447 2019-05-31 14:41:52.347
order_line              146099561 2019-05-31 14:41:52.373
Product                 194099732 2019-05-31 14:41:52.390
purchase_order          226099846 2019-05-31 14:41:52.427
t_order_order_line      258099960 2019-05-31 14:41:52.483

(8 rows affected)

# Show contents in category table

sqlcmd -S ${SQL_SERVER_FULL_NAME} \
    -d ${SQL_DATABASE_NAME} \
    -U ${SQL_SERVER_ADMIN_FULL_NAME} \
    -P ${SQL_SERVER_ADMIN_PASSWORD} \
    -Q "select name from category"

name
------------------------------
Fish
Dogs
Reptiles
Cats
Birds

(5 rows affected)

Troubleshoot Java EE application on Azure by viewing logs

Open Java Web app remote log stream from a local machine:

az webapp log tail --name ${WEBAPP} --resource-group ${RESOURCE_GROUP}

⬅️ Previous guide: 02 - Create a database

➡️ Next guide: 04 - Monitor Java EE application