JDBC lookup to cloud database in OpenShift

In the previous article ,we have seen how to create a custom java application and deploy it to the OpenShift. In this article we will see how we can create a Database applications and use MySql that comes with OpenShift.

Lets create the same dbtest application

[root@vx111a funtest]# rhc app create -a dbtest -t jbossas-7

Application Options

-------------------

Domain:     fundomain

Cartridges: jbossas-7

Gear Size:  default

Scaling:    no

Creating application 'dbtest' ... done

Waiting for your DNS name to be available ... done

Initialized empty Git repository in /soa/openShift/funtest/dbtest/.git/

Warning: Permanently added the RSA host key for IP address '54.234.230.143' to the list of known hosts.

Your application 'dbtest' is now available.

  URL:        http://dbtest-fundomain.rhcloud.com/

  SSH to:     537f4c0f50044652c1000265@dbtest-fundomain.rhcloud.com

  Git remote: ssh://537f4c0f50044652c1000265@dbtest-fundomain.rhcloud.com/~/git/dbtest.git/

Run 'rhc show-app dbtest' for more details about your app.

For the demo purpose , i have created the demo application. Copy the war file to the deployments folder and create a file as Sample-db-app.war.dodeploy

One more thing we need to remember is that since this was a database application we need to deploy the necessary jar files for mySql.

Download the jar file mysql-connector-java-5.0.8-bin.jar and copy the jar files to the deployment directory in the dbtest application.

Once we have the war file , jar file and dodeploy file ,run the rysnc to push the deployments to the remote location

[root@vx111a funtest]# rsync -avz /soa/openShift/funtest/dbtest/deployments/ 537f4c0f50044652c1000265@dbtest-fundomain.rhcloud.com:/var/lib/openshift/537f4c0f50044652c1000265/app-deployments/2014-05-23_09-24-40.189/repo/deployments

sending incremental file list

./

.gitkeep

Sample-db-app.war

Sample-db-app.war.dodeploy

mysql-connector-java-5.0.8-bin.jar

sent 1046513 bytes  received 91 bytes  99676.57 bytes/sec

total size is 1066058  speedup is 1.02

Now once the deploy is done , we need to create a cartridge for the mySql. That is we need to add my Sql to the application and we can do that using

[root@vx111a funtest]# rhc app cartridge add -a dbtest -c mysql-5.1

Warning: This command is deprecated. Please use 'rhc cartridge-add' instead.

Adding mysql-5.1 to application 'dbtest' ... done

mysql-5.1 (MySQL 5.1)

---------------------

  Gears:          Located with jbossas-7

  Connection URL: mysql://$OPENSHIFT_MYSQL_DB_HOST:$OPENSHIFT_MYSQL_DB_PORT/

  Database Name:  dbtest

  Password:       hYMq5isWcWnL

  Username:       adminyfeUvyP

MySQL 5.1 database added.  Please make note of these credentials:

   Root User: XXXXXXXXXXXXXXX

   Root Password: XXXXXXXXXXX

   Database Name: dbtest

Connection URL: mysql://$OPENSHIFT_MYSQL_DB_HOST:$OPENSHIFT_MYSQL_DB_PORT/

You can manage your new MySQL database by also embedding phpmyadmin.

The phpmyadmin username and password will be the same as the MySQL credentials above.

If we see , mySql is added to the application dbtest.

We can check whether the MySql was added by

ssh 537f4c0f50044652c1000265@dbtest-fundomain.rhcloud.com

Help menu: The following commands are available to help control your openshift

application and environment.

gear            control your application (start, stop, restart, etc)

                or deps with --cart      (gear start --cart mysql-5.1)

tail_all        tail all log files

export          list available environment variables

rm              remove files / directories

ls              list files / directories

ps              list running applications

kill            kill running applications

mysql           interactive MySQL shell

mongo           interactive MongoDB shell

psql            interactive PostgreSQL shell

quota           list disk usage

Deprecated:

ctl_app         control your application (start, stop, restart, etc)

ctl_all         control application and deps like mysql in one command

[dbtest-fundomain.rhcloud.com 537f4c0f50044652c1000265]\> mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, 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> show database;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| dbtest             |

| mysql              |

+--------------------+

3 rows in set (0.01 sec)

We can see 3 database available in these.Once we create the Cartridge mysql will be added to the dbtest application and the JNDI name will be available to the appliation. The JNDI name is created runtime by OpenShift.

Now lets access the application using,