Skip to content

Latest commit

 

History

History
258 lines (202 loc) · 8.1 KB

README.md

File metadata and controls

258 lines (202 loc) · 8.1 KB

03B - Bind the application to a MySQL database

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

Bind the application to the petstore database in Azure Database for MySQL.

Configure MySQL 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 MySQL
# and module xml points to module description, see below

module add --name=com.mysql --resources=/home/site/libs/mysql-connector-java-8.0.13.jar --module-xml=/home/site/scripts/mysql-module.xml

Where mysql-module.xml describes the module:

<?xml version="1.0" ?>
<module xmlns="urn:jboss:module:1.1" name="com.mysql">
    <resources>
     <!-- ***** IMPORTANT : REPLACE THIS PLACEHOLDER *******-->
       <resource-root path="/home/site/libs/mysql-connector-java-8.0.13.jar" />
    </resources>
    <dependencies>
        <module name="javax.api"/>
        <module name="javax.transaction.api"/>
    </dependencies>
</module>

Add a JDBC driver for MySQL:

/subsystem=datasources/jdbc-driver=mysql:add(driver-name=mysql,driver-module-name=com.mysql,driver-class-name=com.mysql.cj.jdbc.Driver)

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

data-source add --name=mysqlDS --jndi-name=java:jboss/datasources/mysqlDS --connection-url=${MYSQL_CONNECTION_URL,env.MYSQL_CONNECTION_URL:jdbc:mysql://db:3306/petstore} --driver-name=mysql --user-name=${MYSQL_SERVER_ADMIN_FULL_NAME,env.MYSQL_SERVER_ADMIN_FULL_NAME:mysql} --password=${MYSQL_SERVER_ADMIN_PASSWORD,env.MYSQL_SERVER_ADMIN_PASSWORD:example} --use-ccm=true --max-pool-size=5 --blocking-timeout-wait-millis=5000 --enabled=true --driver-class=com.mysql.cj.jdbc.Driver --jta=true --use-java-context=true --exception-sorter-class-name=com.mysql.cj.jdbc.integration.jboss.ExtendedMysqlExceptionSorter

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 MySQL and module XML are available in initial-mysql/agoncal-application-petstore-ee7/.scripts

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

wget -q "http://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.13/mysql-connector-java-8.0.13.jar" -O mysql-connector-java-8.0.13.jar

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/3B-mysql</directory>
      <includes>
        <include>*.jar</include>
      </includes>
    </resource>
    <resource>
      <type>startup</type>
      <directory>${project.basedir}/.scripts/3B-mysql</directory>
      <includes>
        <include>*.sh</include>
      </includes>
    </resource>
    <resource>
      <type>script</type>
      <directory>${project.basedir}/.scripts/3B-mysql</directory>
      <includes>
        <include>*.cli</include>
        <include>*.xml</include>
      </includes>
    </resource>
  </resources>
</deployment>

Step 3: Set MySQL 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 \
    MYSQL_CONNECTION_URL=${MYSQL_CONNECTION_URL} \
    MYSQL_SERVER_ADMIN_PASSWORD=${MYSQL_SERVER_ADMIN_PASSWORD} \
    MYSQL_SERVER_ADMIN_FULL_NAME=${MYSQL_SERVER_ADMIN_FULL_NAME}
[
 {
   "name": "WEBSITE_HTTPLOGGING_RETENTION_DAYS",
   "slotSetting": false,
   "value": "3"
 },
 {
   "name": "MYSQL_CONNECTION_URL",
   "slotSetting": false,
   "value": "jdbc:mysql://petstore-db1221.mysql.database.azure.com:3306/petstore?ssl=true"
 },
 {
   "name": "MYSQL_SERVER_ADMIN_PASSWORD",
   "slotSetting": false,
   "value": "======= MASKED ======="
 },
 {
   "name": "MYSQL_SERVER_ADMIN_FULL_NAME",
   "slotSetting": false,
   "value": "selvasingh@petstore-db1221"
 }
]

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 Database for MySQL

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

Note - the mysql 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 Database for MySQL

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

Log into Azure Database for MySQL and validate tables were created and populated

mysql -u ${MYSQL_SERVER_ADMIN_FULL_NAME}  -h ${MYSQL_SERVER_FULL_NAME} -P 3306 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 64167
Server version: 5.6.39.0 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use petstore;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------------+
| Tables_in_petstore |
+--------------------+
| category           |
| country            |
| customer           |
| hibernate_sequence |
| item               |
| order_line         |
| product            |
| purchase_order     |
| t_order_order_line |
+--------------------+
9 rows in set (0.03 sec)

mysql> select name from category;
+----------+
| name     |
+----------+
| Fish     |
| Dogs     |
| Reptiles |
| Cats     |
| Birds    |
+----------+
5 rows in set (0.03 sec)

mysql> quit
Bye

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