Connect web application with mysql database while web hosting

Question

I am using jelastic.com to host spring mvc application with mysql database.For that i specified username and password for database of mysql in my application.Then I uploaded the war file . My application uploaded perfectly but it can't connect with database. So please help me what should I do to connect?

Answer 1

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.

Please, follow the step-by-step instruction below:

1. Log into your Jelastic account.

2. Create an environment with the Tomcat7 and MysQL database server (aviable within the SQL section).

3. Check your email inbox - it should contain a message from Jelastic's platform with administration credentials for the created MySQL server (access URL, login and password), for example:

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 above mentioned 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 example).

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.