Java Connection to MySQL/MariaDB

MySQL and MariaDB are highly popular open source databases, used by developers all over the world. In this instruction we’ll show you how to connect your Java application, hosted within Jelastic PaaS, to these DB servers.

1. Log into your Jelastic account and create an environment with the MySQL (or MariaDB) database server (available within the SQL wizard section):

create java environment with mysql

We’ve also added Tomcat node to provide an example of database connection from application server.

2. Check your email inbox - it should contain a message with administration details for the created MySQL (or MariaDB) server:

email mysql node added
3. Switch back to the dashboard and click the Open in browser button for your MySQL / MariaDB node.

access mysql admin panel

Log into the opened admin panel using credentials from the above-mentioned email.

4. Switch to the Databases tab and Create a new database (for example, mysqldb).

mysql create new database
5. Return to dashboard and click the Config button next to the application server (Tomcat in our case) to access configuration file manager.

application server config button

6. Navigate to the /opt/tomcat/temp folder, create a new mydb.cfg file and add the following connection data:

host=jdbc:mysql://{host}/{db_name}
username={user}
password={password}
driver=com.mysql.jdbc.Driver
All the required info can be found within the MySQL / MariaDB node email:
  • {host} - link to your DB node without protocol part
  • {db_name} - name of the previously created database (mysqldb in our case)
  • {user} and {password} - database admin credentials (for production usage, it’s recommended to create a dedicated account with the appropriate permissions)

db access settings in mydb.cfg

In such a way, all connection settings are saved in a single file, which, subsequently, will be read by our application.

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

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. Deploy our example application to your Tomcat server using the following link:

https://download.jelastic.com/public.php?service=files&t=0755533b5d1432ba152abaa4cc15f892&download

application deployment via url
Note: Our example application already contains the jdbc-connector for MySQL / MariaDB database access. However, to connect your own project, you need to manually upload it to the webapps/{app_context}/WEB-INF/lib folder on your application server.
9. Next, you can click Open in browser next to your application server (Tomcat in our case) or within the after-deployment popup to access your application.

deployed application open in browser
10. In the new browser tab, you’ll see the Create table "example" in your database button - click it.

db connection application example
12. Now, in order to ensure everything works fine, return to the MySQL / MariaDB admin panel and navigate to the previously created mysqldb database.

verify table addition to database
You’ll see the new example table appeared in it, which means the DB has been successfully accessed and modified from your Java application. It’s that easy!

Useful to Know

Jelastic PaaS also lets you extend your MySQL / MariaDB servers functionality by 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

Video Tutorial on MySQL Connection to Java Application

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 PaaS.