Buy Support Online

Using MySQL with Payara Server

18 Mar 2018

MySQL is an open-source relational database you may wish to use with Payara Server, that excels at fast reads and is commonly used as storage for a CMS. This blog will show you how to set up MySQL on Ubuntu and connect to it from Payara Server.

 

To install MySQL on Debian or Ubuntu based distributions running the following command in the terminal:

        apt-get install mysql-server mysql-client

When it has finished downloading you will be asked to enter a root password. For the purposes of the tutorial I will be using password as a placeholder. Remember to use a strong password in a production environment.

 

untitled1.png

 

MySQL should start automatically.

You can test whether MySQl has started by running the login command

mysql -u root -ppassword

If you get:

 

                      ERROR 2002 (HY000): Can't connect to local MySQL server through socket                        '/var/run/mysqld/mysqld.sock' (2)

 

This means that MySQL isn’t running. You can start it with:

        sudo service mysql start

Once you have logged in to the database if you run the command

	SHOW DATABASES;

untitled2.png

 

You will see the above default databases. They are required for MySQL to work. For this tutorial I will create an example project and database. To create a new database

	CREATE DATABASE amazon;
To create a user with the username amazonas and the password 
river on the computer:
	CREATE USER ‘amazonas’@’localhost’ IDENTIFIED BY ‘river’;
	GRANT ALL PRIVILEGES ON amazon.* TO ‘amazonas’@’localhost’;

Download the MySQL JDBC driver from https://dev.mysql.com/downloads/connector/j/, then add the jar to Payara Server by running the asadmin command:

asadmin add-library /path/to/mysql-connector-java-bin.jar

Then go to the administration console at http://localhost:4848. Go to Resources → JDBC → JDBC Connection Pools.

 

You will notice there are two pools already. These are the default server pools and are not recommended for production usage. Click the New buttton.

 

untitled3.png

 

The pool name is how the connection pool is to be referred to when using it as a JNDI resource, which here I will set to mysqlpool. Set the resource type to javax.sql.DataSource and the database driver vendor is MySql. Then click Next.

 

untitled4.png

 

On the next page scroll down to the bottom where there is additional properties. There you must set the following properties:

  • serverName – this is the location of the MySQL server. In this case, if the database is located on the same machine then this will be localhost.
  • User – the username for the database.
  • password – the password for the specified user
  • databaseName – The name of the database you want to connect to

untitled5.png

 

You can test the connection by clicking the ping button once the pool has been created. For more information on connection pools see http://blog.payara.fish/an-introduction-to-connection-pools-in-payara-server

 

untitled6.png

 

Then go to Resources → JDBC → JDBC Resources and click on New.

Enter the JNDI name that you wish to use and set the pool name to be 'mysqlpool', as set up in the previous step.

 

untitled7.png

Now, you can inject a reference to a datasource for the MySQL database inside a managed component like this:

         @Resource(lookup = "jdbc/mysqlpool")
         DataSource ds;

Note that you can only use resources in a web container or a managed bean (i.e. CDI beans, EJBs etc.).

Comments

Subscribe

Themes

see all