Resin MySQL and RDS setup with Amazon RDS

This is a continuance of our earlier tutorial on Resin cloud deploy on Amazon.

Install MySQL on local box

First let's install MySQL create a user called bloguser with a password. Then let's create a database called blogdb.

$ sudo apt-get mysql-server

Startup MySQL client tool to create a new user and database:

$ mysql -u root -P

Next create a user called bloguser with the pass roofoo.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'bloguser'@'%' IDENTIFIED BY 'roofoo' WITH GRANT OPTION;

Output:
Query OK, 0 rows affected (0.00 sec)

Next create a database called blogdb.

mysql> create database blogdb;

Output:
Query OK, 1 row affected (0.00 sec)

Exit mysql.

mysql> exit;

Output:
Bye

Use Roo to switch to MySQL

In the examples folder start up roo and switch to using MySQL.

$ roo

roo> jpa setup --database MYSQL --provider HIBERNATE --databaseName blogdb

Output:
Updated SPRING_CONFIG_ROOT/database.properties
Please update your database details in src/main/resources/META-INF/spring/database.properties.
Updated SPRING_CONFIG_ROOT/applicationContext.xml
Updated ROOT/src/main/resources/META-INF/persistence.xml
Updated ROOT/pom.xml [added dependency mysql:mysql-connector-java:5.1.18; removed dependency com.h2database:h2:1.3.161]

Find and modify the database properties for this application. (Later we will set this up in JNDI of Resin, but for now just modify the properties file).

$ find . -name "database.properties"

Output
./src/main/resources/META-INF/spring/database.properties

$ emacs ./src/main/resources/META-INF/spring/database.properties

Add the user name and password that we setup earlier.

#Updated at Thu Dec 01 12:57:30 PST 2011
#Thu Dec 01 12:57:30 PST 2011
database.password=roofoo
database.url=jdbc\:mysql\://localhost\:3306/blogdb
database.username=bloguser
database.driverClassName=com.mysql.jdbc.Driver

Deploy and test what we have done so far

Now let's test out what we have so far. Build the project and deploy it to resin.

$ mvn package


Output:
...
[INFO] BUILD SUCCESS

$ resinctl deploy ./target/blog-0.1.0.BUILD-SNAPSHOT.war -name blogm

Output
Deployed production/webapp/default/blogm from ./target/blog-0.1.0.BUILD-SNAPSHOT.war to hmux://127.0.0.1:6800

Now startup the blogm app and add a blog entry.

$ firefox http://localhost:8080/blogm/

If you like, you can fire up the mysql client and see if your new entry and blog table is in MySQL.

$ mysql -u root -p
Enter password: 

mysql> use blogdb;

mysql> show tables;
+------------------+
| Tables_in_blogdb |
+------------------+
| blog             |
+------------------+
1 row in set (0.00 sec)

mysql> select * from blog;
+----+---------+-------+---------+
| id | message | title | version |
+----+---------+-------+---------+
|  1 | sdf     | sadf  |       0 |
+----+---------+-------+---------+
1 row in set (0.00 sec)

mysql> exit

Now change Resin and Roo to use JNDI instead of Spring configured datasource

Resin can monitor and manage database connection better than Spring alone. In order to do this you need to JNDI. It also good to enable JNDI for production so that a select few in operations can control the user name and password of the production system databases. Having JNDI allows your dev box and your prod box to have different usernames and passwords as well without rebuilding a special war file for each.

In this tutorial our dev box is Ubuntu and our prod box is Amazon AMI in the sky.

First tell Roo to use JNDI as follows:

$ roo
    ____  ____  ____  
   / __ \/ __ \/ __ \ 
  / /_/ / / / / / / / 
 / _, _/ /_/ / /_/ /  
/_/ |_|\____/\____/    1.2.0.RC1 [rev dcaa483]

...

roo> jpa setup --database MYSQL --provider HIBERNATE --databaseName blogdb --jndiDataSource jdbc/blogdb
Output:
Updated SPRING_CONFIG_ROOT/applicationContext.xml

roo> exit

Notice the addition of --jndiDataSource jdbc/blogdb.

Let's see the change it made.

$ find . -name applicationContext.xml
./src/main/resources/META-INF/spring/applicationContext.xml

$ grep -A 5 -B 5 jdbc ./src/main/resources/META-INF/spring/applicationContext.xml 
    </context:component-scan>
    <bean class="org.springframework.orm.jpa.JpaTransactionManager" id="transactionManager">
        <property name="entityManagerFactory" ref="entityManagerFactory"/>
    </bean>
    <tx:annotation-driven mode="aspectj" transaction-manager="transactionManager"/>
    <jee:jndi-lookup id="dataSource" jndi-name="jdbc/blogdb"/>
    <bean class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" id="entityManagerFactory">
        <property name="persistenceUnitName" value="persistenceUnit"/>
        <property name="dataSource" ref="dataSource"/>
    </bean>
</beans>

Now we need to add a JNDI entry for our MySQL database.

Create the file /etc/resin/local.d/database.xml

$ sudo emacs /etc/resin/local.d/database.xml

And add the contents as follows:

<cluster xmlns="http://caucho.com/ns/resin">
<database jndi-name="jdbc/blogdb">
        <driver type="com.mysql.jdbc.Driver">
                 <url>jdbc:mysql://localhost:3306/blogdb</url>
                 <user>bloguser</user>
                 <password>roofoo</password>
         </driver>
</database>
</cluster>