Java Connection to MySQL

MySQL is a highly popular open source database, used by developers all over the world. In this instruction we’ll show you how to connect your Java application, hosted within Jelastic Cloud, to this DB server.

You can use the video tutorial version to see how to create a new environment, configure a MySQL database server, connect to it from your application, and finally, deploy the application to the Jelastic Cloud.



Or, follow the step-by-step guide below.

1. Log into your Jelastic account.

2. Create an environment with the MySQL database server (available within the SQL wizard section):

3. Check your email inbox - it should contain a message from Robot@jelastic with administration details for the created MySQL server (access URL, login and password):

4. Switch back to the dashboard and click the Open in browser button for your MySQL node. Log in to the opened admin panel using the abovementioned credentials and create a new database (for example, mysqlconnection).

5. Then, click the Config button next to the application server (Tomcat 7 in our case) in the expandable environment’s nodes list.

6. In the opened tab, create a new mydb.cfg file inside the home folder and add the following MySQL connection strings there:

host=jdbc:mysql://mysql{node_id}-{your_env_name}.{hoster_domain}/{db_name}
username={get it in the email from Jelastic}
password={get it in the email from Jelastic}
driver=com.mysql.jdbc.Driver

where {node_id} - ID of the container with MySQL server you want to receive the access to. It can be seen at the dashboard (277134 in our case).

In such a way, the required settings for the mysql environment, used in this instruction, will look like at the image below.

Note: To connect the MySQL database to your project, you can mention all the required connecting settings in your code (application) apparently. In the given example we put all the settings to the file, which is read by our application.

7. As an example, here you can see the code of our application, which connects to our MySQL node.

DbManager.java :
package connection;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DbManager {

    private final static String createTable = "CREATE TABLE `example` (id INT, data VARCHAR(100))";

    public Connection createConnection() throws IOException, ClassNotFoundException, SQLException {

        Connection connection;
        
        Properties prop = new Properties();
        System.out.println("test");
        prop.load(new FileInputStream(System.getProperty("user.home") + "/mydb.cfg"));
        System.out.println("user.home: "+System.getProperty("user.home"));
        String host = prop.getProperty("host").toString();
        String username = prop.getProperty("username").toString();
        String password = prop.getProperty("password").toString();
        String driver = prop.getProperty("driver").toString();

        System.out.println("host: " + host + "\username: " + username + "\password: " + password + "\ndriver: " + driver);

        Class.forName(driver);
        System.out.println("--------------------------");
        System.out.println("DRIVER: " + driver);
        connection = DriverManager.getConnection(host, username, password);
        System.out.println("CONNECTION: " + connection);

        return connection;
    }

    public void runSqlStatement() {
        try {
            Statement statement = createConnection().createStatement();
            boolean rs = statement.execute(createTable);

        } catch (IOException ex) {
            Logger.getLogger(DbManager.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DbManager.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
}

8. Next, upload the .war file of your project to the Jelastic Deployment Manager. As an example, we’ll use the dbconnexample.war file (click to download it) which contains the appropriate jdbc-connector.

To connect your own project to a MySQL node, you need to upload the jdbc-connector jar file  to the webapps/{app_context}/WEB-INF/lib folder of your Jelastic environment with your application deployed.

Click the dbconnexample link to download the package with the sources of our project.

9. Deploy the uploaded WAR file to the environment.

10. Now you can click Open in browser next to your application server (Tomcat 7 in our case). You’ll see a new window with the Create table "example" in your database button opened. Click this button.

11. In order to ensure everything works fine, click Open in browser next to your MySQL node and navigate to the previously created mysqlconnection database in the opened admin panel. You’ll see the new example table appeared in it, which means the DB has been successfully accessed from the deployed Java application.

It’s that easy!

Useful to Know

Jelastic Cloud also lets you to extend your MySQL server’s functionality by means of following the appropriate instructions in our documentation:

  • set up the preferred database replication type – master-slave or master-master – in order to get the increased DB performance and data loss protection
  • adjust Backups Scheduling for ensuring safety of the information inside your DBs in the case of unexpected server failure
  • see the Remote Access instruction and learn how to access your database remotely via the preferred desktop MySQL client
  • use the Dump Files Import/Export guide to find out how to manually backup and restore your data from the previously created dumps